Mysql replace 与 insert on duplicate效率分析

导读

我们在向数据库里批量插入数据的时候,会遇到要将原有主键或者unique索引所在记录更新的情况,而如果没有主键或者unique索引冲突的时候,直接执行插入操作。

这种情况下,有三种方式执行:

直接

直接每条select, 判断, 然后insert,毫无疑问,这是最笨的方法了,不断的查询判断,有主键或索引冲突,执行update,否则执行insert. 数据量稍微大一点这种方式就不行了。

稍微高级一些的方式。

replace

这是mysql自身的一个语法,使用 replace 的时候。其语法为:

replace into tablename (f1, f2, f3) values(vf1, vf2, vf3),(vvf1, vvf2, vvf3)

这中语法会自动查询主键或索引冲突,如有冲突,他会先删除原有的数据记录,然后执行插入新的数据。

insert on duplicate key.

这也是一种方式,mysql的insert操作中也给了一种方式,语法如下:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

在insert时判断是否已有主键或索引重复,如果有,一句update后面的表达式执行更新,否则,执行插入。

第一种方式不说了,replace和insert on duplicate key这两种方式,哪中效率更高一些呢,毕竟,我们的执行sql,追求的就是高效。

分析

在最终实践结果中,得到接过如下:
在数据库数据量很少的时候, 这两种方式都很快,无论是直接的插入还是有冲突时的更新,都不错,但在数据库表的内容数量比较大(如百万级)的时候,两种方式就不太一样了,

首先是直接的插入操作,两种的插入效率都略低, 比如直接向表里插入1000条数据(百万级的表(innodb引擎)),二者都差不多需要5,6甚至十几秒。究其原因,我的主机性能是一方面,但在向大数据表批量插入数据的时候,每次的插入都要维护索引的, 索引固然可以提高查询的效率,但在更新表尤其是大表的时候,索引就成了一个不得不考虑的问题了。

其次是更新表,这里的更新的时候是带主键值的(因为我是从另一个表获取数据再插入,要求主键不能变) 同样直接更新1000条数据, replace的操作要比insert on duplicate的操作低太多太多, 当insert瞬间完成(感觉)的时候,replace要7,8s, replace慢的原因我是知道的,在更新数据的时候,要先删除旧的,然后插入新的,在这个过程中,还要重新维护索引,所以速度慢,但为何insert on duplicate的更新却那么快呢。 在向老大请教后,终于知道,insert on duplicate 的更新操作虽然也会更新数据,但其对主键的索引却不会有改变,也就是说,insert on duplicate 更新对主键索引没有影响.因此对索引的维护成本就低了一些(如果更新的字段不包括主键,那就要另说了)。

题外话:

在向数据量大的表里批量插入更新数据的时候,随着插入的数量越来越多,会导致越来越慢,这种情况下,因为我们用的innodb表,可以开启事务, 每次批量执行一批数据更新后提交, 再重新开事务处理下批数据,这样会有效增加效率

还有说明一下: 当我们执行数据库的插入和更新操作很慢的时候,不仅仅是语句,主机性能也很重要, 比如内存和cpu, 如果是虚拟机要相应适当调整, 如果在各种优化了之后效率还是很低, 但cpu和内存的占用却不高,那么就很可能是磁盘的IO性能了,这也会导致数据的更新速度慢。

[转]老生常谈,HashMap的死循环

由于HashMap并非是线程安全的,所以在高并发的情况下必然会出现问题,这是一个普遍的问题,虽然网上分析的文章很多,还是觉得有必须写一篇文章,让关注我公众号的同学能够意识到这个问题,并了解这个死循环是如何产生的。

如果是在单线程下使用HashMap,自然是没有问题的,如果后期由于代码优化,这段逻辑引入了多线程并发执行,在一个未知的时间点,会发现CPU占用100%,居高不下,通过查看堆栈,你会惊讶的发现,线程都Hang在hashMap的get()方法上,服务重启之后,问题消失,过段时间可能又复现了。

这是为什么?

原因分析

在了解来龙去脉之前,我们先看看HashMap的数据结构。

在内部,HashMap使用一个Entry数组保存key、value数据,当一对key、value被加入时,会通过一个hash算法得到数组的下标index,算法很简单,根据key的hash值,对数组的大小取模 hash & (length-1),并把结果插入数组该位置,如果该位置上已经有元素了,就说明存在hash冲突,这样会在index位置生成链表。

如果存在hash冲突,最惨的情况,就是所有元素都定位到同一个位置,形成一个长长的链表,这样get一个值时,最坏情况需要遍历所有节点,性能变成了O(n),所以元素的hash值算法和HashMap的初始化大小很重要。

当插入一个新的节点时,如果不存在相同的key,则会判断当前内部元素是否已经达到阈值(默认是数组大小的0.75),如果已经达到阈值,会对数组进行扩容,也会对链表中的元素进行rehash。

实现

HashMap的put方法实现:

1、判断key是否已经存在

public V put(K key, V value) {
    if (key == null)
        return putForNullKey(value);
    int hash = hash(key);
    int i = indexFor(hash, table.length);
    // 如果key已经存在,则替换value,并返回旧值
    for (Entry<K,V> e = table[i]; e != null; e = e.next) {
        Object k;
        if (e.hash == hash && ((k = e.key) == key || key.equals(k))) {
            V oldValue = e.value;
            e.value = value;
            e.recordAccess(this);
            return oldValue;
        }
    }

    modCount++;
    // key不存在,则插入新的元素
    addEntry(hash, key, value, i);
    return null;
}

2、检查容量是否达到阈值threshold

void addEntry(int hash, K key, V value, int bucketIndex) {
    if ((size >= threshold) && (null != table[bucketIndex])) {
        resize(2 * table.length);
        hash = (null != key) ? hash(key) : 0;
        bucketIndex = indexFor(hash, table.length);
    }

    createEntry(hash, key, value, bucketIndex);
}

如果元素个数已经达到阈值,则扩容,并把原来的元素移动过去。

3、扩容实现

void resize(int newCapacity) {
    Entry[] oldTable = table;
    int oldCapacity = oldTable.length;
    ...

    Entry[] newTable = new Entry[newCapacity];
    ...
    transfer(newTable, rehash);
    table = newTable;
    threshold = (int)Math.min(newCapacity * loadFactor, MAXIMUM_CAPACITY + 1);
}

这里会新建一个更大的数组,并通过transfer方法,移动元素。

void transfer(Entry[] newTable, boolean rehash) {
    int newCapacity = newTable.length;
    for (Entry<K,V> e : table) {
        while(null != e) {
            Entry<K,V> next = e.next;
            if (rehash) {
                e.hash = null == e.key ? 0 : hash(e.key);
            }
            int i = indexFor(e.hash, newCapacity);
            e.next = newTable[i];
            newTable[i] = e;
            e = next;
        }
    }
}

移动的逻辑也很清晰,遍历原来table中每个位置的链表,并对每个元素进行重新hash,在新的newTable找到归宿,并插入。

案例分析

假设HashMap初始化大小为4,插入个3节点,不巧的是,这3个节点都hash到同一个位置,如果按照默认的负载因子的话,插入第3个节点就会扩容,为了验证效果,假设负载因子是1.

void transfer(Entry[] newTable, boolean rehash) {
    int newCapacity = newTable.length;
    for (Entry<K,V> e : table) {
        while(null != e) {
            Entry<K,V> next = e.next;
            if (rehash) {
                e.hash = null == e.key ? 0 : hash(e.key);
            }
            int i = indexFor(e.hash, newCapacity);
            e.next = newTable[i];
            newTable[i] = e;
            e = next;
        }
    }
}

以上是节点移动的相关逻辑。


插入第4个节点时,发生rehash,假设现在有两个线程同时进行,线程1和线程2,两个线程都会新建新的数组。


假设 线程2 在执行到Entry<K,V> next = e.next;之后,cpu时间片用完了,这时变量e指向节点a,变量next指向节点b。

线程1继续执行,很不巧,a、b、c节点rehash之后又是在同一个位置7,开始移动节点

第一步,移动节点a


第二步,移动节点b


注意,这里的顺序是反过来的,继续移动节点c


这个时候 线程1 的时间片用完,内部的table还没有设置成新的newTable, 线程2 开始执行,这时内部的引用关系如下:


这时,在 线程2 中,变量e指向节点a,变量next指向节点b,开始执行循环体的剩余逻辑。

Entry<K,V> next = e.next;
int i = indexFor(e.hash, newCapacity);
e.next = newTable[i];
newTable[i] = e;
e = next;

执行之后的引用关系如下图


执行后,变量e指向节点b,因为e不是null,则继续执行循环体,执行后的引用关系


变量e又重新指回节点a,只能继续执行循环体,这里仔细分析下: 1、执行完Entry<K,V> next = e.next;,目前节点a没有next,所以变量next指向null; 2、e.next = newTable[i]; 其中 newTable[i] 指向节点b,那就是把a的next指向了节点b,这样a和b就相互引用了,形成了一个环; 3、newTable[i] = e 把节点a放到了数组i位置; 4、e = next; 把变量e赋值为null,因为第一步中变量next就是指向null;

所以最终的引用关系是这样的:


节点a和b互相引用,形成了一个环,当在数组该位置get寻找对应的key时,就发生了死循环。

另外,如果线程2把newTable设置成到内部的table,节点c的数据就丢了,看来还有数据遗失的问题。

总结

所以在并发的情况,发生扩容时,可能会产生循环链表,在执行get的时候,会触发死循环,引起CPU的100%问题,所以一定要避免在并发环境下使用HashMap。

曾经有人把这个问题报给了Sun,不过Sun不认为这是一个bug,因为在HashMap本来就不支持多线程使用,要并发就用ConcurrentHashmap。

作者:占小狼
链接:https://juejin.im/post/5a66a08d5188253dc3321da0
来源:掘金
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

[转]Long类型转json时前端js丢失精度解决方案

一、问题背景

Java后端开发过程中,尤其是id字段,因数值太大,通过json形式传输到前端后,在js解析时,会丢失精度。

如果对精度丢失没有什么概念,可以看一个知乎的帖子,来感受一下:https://www.zhihu.com/question/34564427?sort=created

二、解决思路

将id字段序列化为json时,转换为字符串类型,前端传输到后端,反序列化时,再重新转换为Long。

三、具体实现

在dto所在项目中,新建一个helper包(名字自定义,也可以放现有包里)。PS:为什么要建到dto项目中?因为,这个包最后可能会给其他组使用,这样以来,所有的处理规则逻辑都是统一的,方便对接。

在包里添加类LongJsonSerializer,代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
/**
 * Long 类型字段序列化时转为字符串,避免js丢失精度
 *
 */
public class LongJsonSerializer extends JsonSerializer<Long> {
    @Override
    public void serialize(Long value, JsonGenerator jsonGenerator, SerializerProvider serializerProvider) throws IOException, JsonProcessingException {
        String text = (value == null null : String.valueOf(value));
        if (text != null) {
            jsonGenerator.writeString(text);
        }
    }
}

然后在包里再添加类LongJsonDeserializer,代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/**
 * 将字符串转为Long
 *
 */
public class LongJsonDeserializer extends JsonDeserializer<Long> {
    private static final Logger logger = LoggerFactory.getLogger(LongJsonDeserializer.class);
 
    @Override
    public Long deserialize(JsonParser jsonParser, DeserializationContext deserializationContext) throws IOException, JsonProcessingException {
        String value = jsonParser.getText();
        try {
            return value == null null : Long.parseLong(value);
        catch (NumberFormatException e) {
            logger.error("解析长整形错误", e);
            return null;
        }
    }
}

 

好了,接下来是使用这两个类。

在需要处理的id字段上,加上注解。比如如下代码:

1
2
3
4
5
6
/**
 * id
 */
@JsonSerialize(using = LongJsonSerializer.class)
@JsonDeserialize(using = LongJsonDeserializer.class)
private Long id;

PHP字符串压缩存入数据库

网上流传这样方法存入压缩数据到mysql:

$data = array();//需要压缩存入数据库的数据

$eventData = addslashes( gzdeflate( json_encode( $data ), 9 ) ); //压缩数据存入数据库

$logData //数据库存入的压缩数据

$eventData = json_decode( gzinflate( $logData ), true );//获取压缩的数据 从数据库读取

实际使用中发现:
$str = “testsaaaaaaddddddd”;
$str1 = gzcompress($str);
$str2 = utf8_encode((gzdeflate($str)));

需要utf8_encode才能存入mysql的规范!

 

[转]MySQL前缀索引和索引选择性

有时候需要索引很长的字符列,这会让索引变得大且慢。通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指不重复的索引值(也称为基数,cardinality)和数据表的记录总数的比值,范围从1/#T到1之间。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。

一般情况下某个前缀的选择性也是足够高的,足以满足查询性能。对于BLOB,TEXT,或者很长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长(以便节约空间)。前缀应该足够长,以使得前缀索引的选择性接近于索引的整个列。换句话说,前缀的”基数“应该接近于完整的列的”基数“。

为了决定前缀的合适长度,需要找到最常见的值的列表,然后和最常见的前缀列表进行比较。下面的示例是mysql官方提供的示例数据库

下载地址如下:

http://downloads.mysql.com/docs/sakila-db.zip

在示例数据库sakila中并没有合适的例子,所以从表city中生成一个示例表,这样就有足够数据进行演示:

复制代码
mysql> select database();                                                           
+------------+
| database() |
+------------+
| sakila     |
+------------+
1 row in set (0.00 sec)

mysql> create table city_demo (city varchar(50) not null);                          
Query OK, 0 rows affected (0.02 sec)

mysql> insert into city_demo (city) select city from city;                          
Query OK, 600 rows affected (0.08 sec)
Records: 600  Duplicates: 0  Warnings: 0

mysql> insert into city_demo (city) select city from city_demo;
Query OK, 600 rows affected (0.07 sec)
Records: 600  Duplicates: 0  Warnings: 0

mysql> update city_demo set city = ( select city from city order by rand() limit 1);
Query OK, 1199 rows affected (0.95 sec)
Rows matched: 1200  Changed: 1199  Warnings: 0

mysql>
复制代码

因为这里使用了rand()函数,所以你的数据会与我的不同,当然那不影响聪明的你。

首先找到最常见的城市列表:

复制代码
mysql> select count(*) as cnt, city from city_demo group by city order by cnt desc limit 10;               
+-----+--------------+
| cnt | city         |
+-----+--------------+
|   8 | Garden Grove |
|   7 | Escobar      |
|   7 | Emeishan     |
|   6 | Amroha       |
|   6 | Tegal        |
|   6 | Lancaster    |
|   6 | Jelets       |
|   6 | Ambattur     |
|   6 | Yingkou      |
|   6 | Monclova     |
+-----+--------------+
10 rows in set (0.01 sec)

mysql>
复制代码

注意到查询结果,上面每个值都出现了6-8次。现在查找到频繁出现的城市前缀。先从3个前缀字母开始,然后4个,5个,6个:

复制代码
mysql> select count(*) as cnt,left(city,3) as pref from city_demo group by pref order by cnt desc limit 10;
+-----+------+
| cnt | pref |
+-----+------+
|  25 | San  |
|  15 | Cha  |
|  12 | Bat  |
|  12 | Tan  |
|  11 | al-  |
|  11 | Gar  |
|  11 | Yin  |
|  10 | Kan  |
|  10 | Sou  |
|  10 | Bra  |
+-----+------+
10 rows in set (0.00 sec)

mysql> select count(*) as cnt,left(city,4) as pref from city_demo group by pref order by cnt desc limit 10; 
+-----+------+
| cnt | pref |
+-----+------+
|  12 | San  |
|  10 | Sout |
|   8 | Chan |
|   8 | Sant |
|   8 | Gard |
|   7 | Emei |
|   7 | Esco |
|   6 | Ying |
|   6 | Amro |
|   6 | Lanc |
+-----+------+
10 rows in set (0.01 sec)

mysql> select count(*) as cnt,left(city,5) as pref from city_demo group by pref order by cnt desc limit 10; 
+-----+-------+
| cnt | pref  |
+-----+-------+
|  10 | South |
|   8 | Garde |
|   7 | Emeis |
|   7 | Escob |
|   6 | Amroh |
|   6 | Yingk |
|   6 | Moncl |
|   6 | Lanca |
|   6 | Jelet |
|   6 | Tegal |
+-----+-------+
10 rows in set (0.01 sec)
复制代码
复制代码
mysql> select count(*) as cnt,left(city,6) as pref from city_demo group by pref order by cnt desc limit 10; 
+-----+--------+
| cnt | pref   |
+-----+--------+
|   8 | Garden |
|   7 | Emeish |
|   7 | Escoba |
|   6 | Amroha |
|   6 | Yingko |
|   6 | Lancas |
|   6 | Jelets |
|   6 | Tegal  |
|   6 | Monclo |
|   6 | Ambatt |
+-----+--------+
10 rows in set (0.00 sec)

mysql>
复制代码

通过上面改变不同前缀长度发现,当前缀长度为6时,这个前缀的选择性就接近完整咧的选择性了。甚至是一样的。

当然还有另外更方便的方法,那就是计算完整列的选择性,并使其前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性:

复制代码
mysql> select count(distinct city) / count(*) from city_demo;
+---------------------------------+
| count(distinct city) / count(*) |
+---------------------------------+
|                          0.4283 |
+---------------------------------+
1 row in set (0.05 sec)

mysql>
复制代码

可以在一个查询中针对不同前缀长度的选择性进行计算,这对于大表非常有用,下面给出如何在同一个查询中计算不同前缀长度的选择性:

复制代码
mysql> select count(distinct left(city,3))/count(*) as sel3,
    -> count(distinct left(city,4))/count(*) as sel4,
    -> count(distinct left(city,5))/count(*) as sel5, 
    -> count(distinct left(city,6))/count(*) as sel6 
    -> from city_demo;
+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   |
+--------+--------+--------+--------+
| 0.3367 | 0.4075 | 0.4208 | 0.4267 |
+--------+--------+--------+--------+
1 row in set (0.01 sec)

mysql>
复制代码

可以看见当索引前缀为6时的基数是0.4267,已经接近完整列选择性0.4283。

在上面的示例中,已经找到了合适的前缀长度,下面创建前缀索引:

mysql> alter table city_demo add key (city(6));
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
复制代码
mysql> explain select * from city_demo where city like 'Jinch%';
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | city_demo | range | city          | city | 20      | NULL |    2 | Using where |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>
复制代码

可以看见正确使用刚创建的索引。

前缀索引是一种能使索引更小,更快的有效办法,但另一方面也有其缺点:

mysql无法使用其前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描。