|
|
51CTO旗下网站
|
|
移动端

盘点开发中那些常用的MySQL优化

很多时候数据库的性能是由于不合适(是指效率不高,可能会导致锁表等)的SQL语句造成,本篇博文只是介绍简单的SQL优化

作者:佚名来源:Java知音|2020-05-20 13:24

 1、大批量插入数据优化

(1)对于MyISAM存储引擎的表,可以使用:DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新。

  1. ALTER TABLE tbl_name DISABLE KEYS;  
  2. loading the data  
  3. ALTER TABLE tbl_name ENABLE KEYS; 

(2)对于InnoDB引擎,有以下几种优化措施:

① 导入的数据按照主键的顺序保存:这是因为InnoDB引擎表示按照主键顺序保存的,如果能将插入的数据提前按照排序好自然能省去很多时间。

比如bulk_insert.txt文件是以表user主键的顺序存储的,导入的时间为15.23秒

  1. mysql> load data infile 'mysql/bulk_insert.txt' into table user;  
  2. Query OK, 126732 rows affected (15.23 sec)  
  3. Records: 126732 Deleted: 0 Skipped: 0 Warnings: 0 

没有按照主键排序的话,时间为:26.54秒

  1. mysql> load data infile 'mysql/bulk_insert.txt' into table user;  
  2. Query OK, 126732 rows affected (26.54 sec)  
  3. Records: 126732 Deleted: 0 Skipped: 0 Warnings: 0 

② 导入数据前执行SET UNIQUE_CHECKS=0,关闭唯一性校验,带导入之后再打开设置为1:校验会消耗时间,在数据量大的情况下需要考虑。

③ 导入前设置SET AUTOCOMMIT=0,关闭自动提交,导入后结束再设置为1:这是因为自动提交会消耗部分时间与资源,虽然消耗不是很大,但是在数据量大的情况下还是得考虑。

2、INSERT的优化

(1)尽量使用多个值表的 INSERT 语句,这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗。(同一客户的情况下),即:

  1. INSERT INTO tablename values(1,2),(1,3),(1,4) 

实验:插入8条数据到user表中(使用navicat客户端工具)

  1. insert into user values(1,'test',replace(uuid(),'-',''));  
  2. insert into user values(2,'test',replace(uuid(),'-',''));  
  3. insert into user values(3,'test',replace(uuid(),'-',''));  
  4. insert into user values(4,'test',replace(uuid(),'-',''));  
  5. insert into user values(5,'test',replace(uuid(),'-',''));  
  6. insert into user values(6,'test',replace(uuid(),'-',''));  
  7. insert into user values(7,'test',replace(uuid(),'-','')); 
  8. insert into user values(8,'test',replace(uuid(),'-','')); 

得到反馈:

  1. [SQL] insert into user values(1,'test',replace(uuid(),'-',''));  
  2. 受影响的行: 1  
  3. 时间: 0.033s  
  4. [SQL]   
  5. insert into user values(2,'test',replace(uuid(),'-',''));  
  6. 受影响的行: 1  
  7. 时间: 0.034s  
  8. [SQL]   
  9. insert into user values(3,'test',replace(uuid(),'-',''));  
  10. 受影响的行: 1  
  11. 时间: 0.056s  
  12. [SQL]   
  13. insert into user values(4,'test',replace(uuid(),'-',''));  
  14. 受影响的行: 1  
  15. 时间: 0.008s  
  16. [SQL]   
  17. insert into user values(5,'test',replace(uuid(),'-',''));  
  18. 受影响的行: 1  
  19. 时间: 0.008s  
  20. [SQL]   
  21. insert into user values(6,'test',replace(uuid(),'-',''));  
  22. 受影响的行: 1  
  23. 时间: 0.024s  
  24. [SQL]   
  25. insert into user values(7,'test',replace(uuid(),'-',''));  
  26. 受影响的行: 1  
  27. 时间: 0.004s  
  28. [SQL]   
  29. insert into user values(8,'test',replace(uuid(),'-',''));  
  30. 受影响的行: 1  
  31. 时间: 0.004s 

总共的时间为0.171秒,接下来使用多值表形式:

  1. insert into user values  
  2. (9,'test',replace(uuid(),'-','')),  
  3. (10,'test',replace(uuid(),'-','')),  
  4. (11,'test',replace(uuid(),'-','')),  
  5. (12,'test',replace(uuid(),'-','')),  
  6. (13,'test',replace(uuid(),'-','')),  
  7. (14,'test',replace(uuid(),'-','')),  
  8. (15,'test',replace(uuid(),'-','')),  
  9. (16,'test',replace(uuid(),'-','')); 

得到反馈:

  1. [SQL] insert into user values  
  2. (9,'test',replace(uuid(),'-','')),  
  3. (10,'test',replace(uuid(),'-','')),  
  4. (11,'test',replace(uuid(),'-','')),  
  5. (12,'test',replace(uuid(),'-','')),  
  6. (13,'test',replace(uuid(),'-','')),  
  7. (14,'test',replace(uuid(),'-','')),  
  8. (15,'test',replace(uuid(),'-','')),  
  9. (16,'test',replace(uuid(),'-',''));  
  10. 受影响的行: 8  
  11. 时间: 0.038s 

得到时间为0.038,这样一来可以很明显节约时间优化SQL

(2)如果在不同客户端插入很多行,可使用INSERT DELAYED语句得到更高的速度,DELLAYED含义是让INSERT语句马上执行,其实数据都被放在内存的队列中。并没有真正写入磁盘。LOW_PRIORITY刚好相反。

(3)将索引文件和数据文件分在不同的磁盘上存放(InnoDB引擎是在同一个表空间的)。

(4)如果批量插入,则可以增加bluk_insert_buffer_size变量值提供速度(只对MyISAM有用)

(5)当从一个文本文件装载一个表时,使用LOAD DATA INFILE,通常比INSERT语句快20倍。

3、GROUP BY的优化

在默认情况下,MySQL中的GROUP BY语句会对其后出现的字段进行默认排序(非主键情况),就好比我们使用ORDER BY col1,col2,col3…所以我们在后面跟上具有相同列(与GROUP BY后出现的col1,col2,col3…相同)ORDER BY子句并没有影响该SQL的实际执行性能。

那么就会有这样的情况出现,我们对查询到的结果是否已经排序不在乎时,可以使用ORDER BY NULL禁止排序达到优化目的。下面使用EXPLAIN命令分析SQL。Java知音公众号内回复“面试题聚合”,送你一份面试题宝典

在user_1中执行select id, sum(money) form user_1 group by name时,会默认排序(注意group by后的column是非index才会体现group by的排序,如果是primary key,那之前说过了InnoDB默认是按照主键index排好序的)

  1. mysql> select*from user_1;  
  2. +----+----------+-------+  
  3. | id | name     | money |  
  4. +----+----------+-------+  
  5. |  1 | Zhangsan |    32 |  
  6. |  2 | Lisi     |    65 |  
  7. |  3 | Wangwu   |    44 |  
  8. |  4 | Lijian   |   100 |  
  9. +----+----------+-------+  
  10. 4 rows in set 

不禁止排序,即不使用ORDER BY NULL时:有明显的Using filesort。

当使用ORDER BY NULL禁止排序后,Using filesort不存在

4、ORDER BY 的优化

MySQL可以使用一个索引来满足ORDER BY 子句的排序,而不需要额外的排序,但是需要满足以下几个条件:

(1)WHERE 条件和OREDR BY 使用相同的索引:即key_part1与key_part2是复合索引,where中使用复合索引中的key_part1

  1. SELECT*FROM user WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC; 

(2)而且ORDER BY顺序和索引顺序相同:

  1. SELECT*FROM user ORDER BY key_part1, key_part2; 

(3)并且要么都是升序要么都是降序:

  1. SELECT*FROM user ORDER BY key_part1 DESC, key_part2 DESC; 

但以下几种情况则不使用索引:

(1)ORDER BY中混合ASC和DESC:

  1. SELECT*FROM user ORDER BY key_part1 DESC, key_part2 ASC; 

(2)查询行的关键字与ORDER BY所使用的不相同,即WHERE 后的字段与ORDER BY 后的字段是不一样的

  1. SELECT*FROM user WHERE key2 = ‘xxx’ ORDER BY key1; 

(3)ORDER BY对不同的关键字使用,即ORDER BY后的关键字不相同

  1. SELECT*FROM user ORDER BY key1, key2; 

5、OR的优化

当MySQL使用OR查询时,如果要利用索引的话,必须每个条件列都使独立索引,而不是复合索引(多列索引),才能保证使用到查询的时候使用到索引。

比如我们新建一张用户信息表user_info

  1. mysql> select*from user_info;  
  2. +---------+--------+----------+-----------+  
  3. | user_id | idcard | name     | address    |  
  4. +---------+--------+----------+-----------+  
  5. |       1 | 111111 | Zhangsan | Kunming   |  
  6. |       2 | 222222 | Lisi     | Beijing   |  
  7. |       3 | 333333 | Wangwu   | Shanghai  |  
  8. |       4 | 444444 | Lijian   | Guangzhou |  
  9. +---------+--------+----------+-----------+  
  10. 4 rows in set 

之后创建ind_name_id(user_id, name)复合索引、id_index(id_index)独立索引,idcard主键索引三个索引。

  1. mysql> show index from user_info;  
  2. +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
  3. | Table     | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | 
  4. +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
  5. | user_info |          0 | PRIMARY     |            1 | idcard      | A         |           4 | NULL     | NULL   |      | BTREE      |         |               | 
  6. | user_info |          1 | ind_name_id |            1 | user_id     | A         |           4 | NULL     | NULL   |      | BTREE      |         |               | 
  7. | user_info |          1 | ind_name_id |            2 | name        | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               | 
  8. | user_info |          1 | id_index    |            1 | user_id     | A         |           4 | NULL     | NULL   |      | BTREE      |         |               | 
  9. +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
  10. 4 rows in set 

测试一:OR连接两个有单独索引的字段,整个SQL查询才会用到索引(index_merge),并且我们知道OR实际上是把每个结果最后UNION一起的。

  1. mysql> explain select*from user_info where user_id=1 or idcard='222222' 
  2. +----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+ 
  3. | id | select_type | table     | partitions | type        | possible_keys                | key                 | key_len | ref  | rows | filtered | Extra                                              | 
  4. +----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+ 
  5. |  1 | SIMPLE      | user_info | NULL       | index_merge | PRIMARY,ind_name_id,id_index | ind_name_id,PRIMARY | 4,62    | NULL |    2 |      100 | Using sort_union(ind_name_id,PRIMARY); Using where | 
  6. +----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+ 
  7. 1 row in set 

测试二:OR使用复合索引的字段name,与没有索引的address,整个SQL都是ALL全表扫描的

  1. mysql> explain select*from user_info where name='Zhangsan' or address='Beijing' 
  2. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  3. | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |  
  4. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  5. |  1 | SIMPLE      | user_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    43.75 | Using where |  
  6. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  7. 1 row in set 

交换OR位置并且使用另外的复合索引的列,也是ALL全表扫描:

  1. mysql> explain select*from user_info where address='Beijing' or user_id=1 
  2. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  3. | id | select_type | table     | partitions | type | possible_keys        | key  | key_len | ref  | rows | filtered | Extra       |  
  4. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+ 
  5. |  1 | SIMPLE      | user_info | NULL       | ALL  | ind_name_id,id_index | NULL | NULL    | NULL |    4 |    43.75 | Using where |  
  6. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  7. 1 row in set 

6、优化嵌套查询

使用嵌套查询有时候可以使用更有效的JOIN连接代替,这是因为MySQL中不需要在内存中创建临时表完成SELECT子查询与主查询两部分查询工作。但是并不是所有的时候都成立,最好是在on关键字后面的列有索引的话,效果会更好!

比如在表major中major_id是有索引的:

  1. select * from student u left join major m on u.major_id=m.major_id where m.major_id is null; 

而通过嵌套查询时,在内存中创建临时表完成SELECT子查询与主查询两部分查询工作,会有一定的消耗

  1. select * from student u where major_id not in (select major_id from major); 

7、使用SQL提示

SQL提示(SQL HINT)是优化数据库的一个重要手段,就是往SQL语句中加入一些人为的提示来达到优化目的。下面是一些常用的SQL提示:

(1)USE INDEX:使用USE INDEX是希望MySQL去参考索引列表,就可以让MySQL不需要考虑其他可用索引,其实也就是possible_keys属性下参考的索引值

  1. mysql> explain select* from user_info use index(id_index,ind_name_id) where user_id>0;  
  2. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  3. | id | select_type | table     | partitions | type | possible_keys        | key  | key_len | ref  | rows | filtered | Extra       |  
  4. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  5. |  1 | SIMPLE      | user_info | NULL       | ALL  | ind_name_id,id_index | NULL | NULL    | NULL |    4 |      100 | Using where |  
  6. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  7. 1 row in set  
  8. mysql> explain select* from user_info use index(id_index) where user_id>0;  
  9. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  10. | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |  
  11. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  12. |  1 | SIMPLE      | user_info | NULL       | ALL  | id_index      | NULL | NULL    | NULL |    4 |      100 | Using where |  
  13. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  14. 1 row in set 

(2)IGNORE INDEX忽略索引

我们使用user_id判断,用不到其他索引时,可以忽略索引。即与USE INDEX相反,从possible_keys中减去不需要的索引,但是实际环境中很少使用。

  1. mysql> explain select* from user_info ignore index(primary,ind_name_id,id_index) where user_id>0;  
  2. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  3. | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |  
  4. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  5. |  1 | SIMPLE      | user_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where |  
  6. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  7. 1 row in set 

(3)FORCE INDEX强制索引

比如where user_id > 0,但是user_id在表中都是大于0的,自然就会进行ALL全表搜索,但是使用FORCE INDEX虽然执行效率不是最高(where user_id > 0条件决定的)但MySQL还是使用索引。

  1. mysql> explain select* from user_info where user_id>0;  
  2. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  3. | id | select_type | table     | partitions | type | possible_keys        | key  | key_len | ref  | rows | filtered | Extra       |  
  4. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  5. |  1 | SIMPLE      | user_info | NULL       | ALL  | ind_name_id,id_index | NULL | NULL    | NULL |    4 |      100 | Using where |  
  6. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  7. 1 row in set 

之后强制使用独立索引id_index(user_id):

  1. mysql> explain select* from user_info force index(id_index) where user_id>0;  
  2. +----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+  
  3. | id | select_type | table     | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |  
  4. +----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+  
  5. |  1 | SIMPLE      | user_info | NULL       | range | id_index      | id_index | 4       | NULL |    4 |      100 | Using index condition |  
  6. +----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+  
  7. 1 row in set 

总结

(1)很多时候数据库的性能是由于不合适(是指效率不高,可能会导致锁表等)的SQL语句造成,本篇博文只是介绍简单的SQL优化

(2)其中有些优化在真正开发中是用不到的,但是一旦出问题性能下降的时候需要去一一分析。

【编辑推荐】

  1. MySQL 8 新特性之降序索引底层实现
  2. MySQL如何实现Excel分组排序功能?
  3. 详解Oracle数据库LRU算法--LRU链、脏块与脏LRU链
  4. 基于 Prometheus 的云上 MySQL 监控实践
  5. 设计MySQL的大叔为何偏爱ref?
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微