开发中常用的MySQL优化技巧

45次阅读

共计 11414 个字符，预计需要花费 29 分钟才能阅读完成。

人生有六个字，前面三个是“不害怕”，后面还有三个是“不后悔”，不管怎样，努力去做吧。

1、大批量插入数据优化

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

ALTERTABLE tbl_name DISABLEKEYS;  
loading the data  
ALTERTABLE tbl_name ENABLEKEYS;

（2）对于 InnoDB 引擎，有以下几种优化措施：

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

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

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

没有按照主键排序的话，时间为：26.54 秒

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

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

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

2、INSERT 的优化

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

INSERTINTO tablename values(1,2),(1,3),(1,4)

实验：插入 8 条数据到 user 表中（使用 navicat 客户端工具）

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

得到反馈：

[SQL] insert into user values(1,'test',replace(uuid(),'-',''));  
受影响的行: 1  
时间: 0.033s  
[SQL]   
insert into user values(2,'test',replace(uuid(),'-',''));  
受影响的行: 1  
时间: 0.034s  
[SQL]   
insert into user values(3,'test',replace(uuid(),'-',''));  
受影响的行: 1  
时间: 0.056s  
[SQL]   
insert into user values(4,'test',replace(uuid(),'-',''));  
受影响的行: 1  
时间: 0.008s  
[SQL]   
insert into user values(5,'test',replace(uuid(),'-',''));  
受影响的行: 1  
时间: 0.008s  
[SQL]   
insert into user values(6,'test',replace(uuid(),'-',''));  
受影响的行: 1  
时间: 0.024s  
[SQL]   
insert into user values(7,'test',replace(uuid(),'-',''));  
受影响的行: 1  
时间: 0.004s  
[SQL]   
insert into user values(8,'test',replace(uuid(),'-',''));  
受影响的行: 1  
时间: 0.004s

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

insert into user values  
(9,'test',replace(uuid(),'-','')),  
(10,'test',replace(uuid(),'-','')),  
(11,'test',replace(uuid(),'-','')),  
(12,'test',replace(uuid(),'-','')),  
(13,'test',replace(uuid(),'-','')),  
(14,'test',replace(uuid(),'-','')),  
(15,'test',replace(uuid(),'-','')),  
(16,'test',replace(uuid(),'-',''));

得到反馈：

[SQL] insert into user values  
(9,'test',replace(uuid(),'-','')),  
(10,'test',replace(uuid(),'-','')),  
(11,'test',replace(uuid(),'-','')),  
(12,'test',replace(uuid(),'-','')),  
(13,'test',replace(uuid(),'-','')),  
(14,'test',replace(uuid(),'-','')),  
(15,'test',replace(uuid(),'-','')),  
(16,'test',replace(uuid(),'-',''));  
受影响的行: 8  
时间: 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 排好序的）

mysql> select*from user_1;  
+----+----------+-------+  
| id | name     | money |  
+----+----------+-------+  
|  1 | Zhangsan |    32 |  
|  2 | Lisi     |    65 |  
|  3 | Wangwu   |    44 |  
|  4 | Lijian   |   100 |  
+----+----------+-------+  
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

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

（2）而且 ORDER BY 顺序和索引顺序相同：

SELECT*FROM user ORDER BY key_part1, key_part2;

（3）并且要么都是升序要么都是降序：

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

但以下几种情况则不使用索引：
（1）ORDER BY 中混合 ASC 和 DESC：

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

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

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

（3）ORDER BY 对不同的关键字使用，即 ORDER BY 后的关键字不相同

SELECT*FROM user ORDER BY key1, key2;

5、OR 的优化

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

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

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

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

mysql> explain select*from user_info where user_id=1or idcard='222222';  
+----+-------------+-----------+------------+-------------
|id|select_type|   table   |partitions|    type    |        possible_keys        |         key         |key_len|  ref  | rows | filtered | Extra                                              |  
|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 |  
1 row in set

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

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

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

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

6、优化嵌套查询

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

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

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

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 属性下参考的索引值

mysql> explain select* from user_info use index(id_index,ind_name_id) where user_id>0;+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+| id | select_type | table     | partitions | type | possible_keys        | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | user_info | NULL       | ALL  | ind_name_id,id_index | NULL | NULL    | NULL |    4 |      100 | Using where |+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+1 row in setmysql> explain select* from user_info use index(id_index) where user_id>0;+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | user_info | NULL       | ALL  | id_index      | NULL | NULL    | NULL |    4 |      100 | Using where |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set

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

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

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

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

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

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

总结

（1）很多时候数据库的性能是由于不合适（是指效率不高，可能会导致锁表等）的 SQL 语句造成，本篇博文只是介绍简单的 SQL 优化
（2）其中有些优化在真正开发中是用不到的，但是一旦出问题性能下降的时候需要去一一分析。

正文完
数据库
发表至： 数据库
2020-06-05
 0
关于数据库:5分钟搞定-MySQL-到-ElasticSearch-迁移同步CloudCanal实战
关于数据库:TDSQL-PG版节省30磁盘空间的同时如何保障数据安全｜DB洞见
关于数据库:什么是真正的HTAP一背景篇
关于数据库:数据库SQL高级查询分页查询