共计 12684 个字符，预计需要花费 32 分钟才能阅读完成。
本篇文章给大家分享的是有关开发中那些常用的 MySQL 优化有哪些，丸趣 TV 小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着丸趣 TV 小编一起来看看吧。
1、大批量插入数据优化
（1）对于 MyISAM 存储引擎的表，可以使用：DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新。
ALTER TABLE tbl_name DISABLE KEYS; loading the data ALTER TABLE tbl_name ENABLE KEYS;（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 语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗。（同一客户的情况下），即：
INSERT INTO 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 hellip; 所以我们在后面跟上具有相同列（与 GROUP BY 后出现的 col1,col2,col3 hellip; 相同）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 = lsquo;xxx rsquo; 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之后创建 ind_name_id(user_id, name) 复合索引、id_index(id_index) 独立索引，idcard 主键索引三个索引。
mysql show index from user_info; +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user_info | 0 | PRIMARY | 1 | idcard | A | 4 | NULL | NULL | | BTREE | | | | user_info | 1 | ind_name_id | 1 | user_id | A | 4 | NULL | NULL | | BTREE | | | | user_info | 1 | ind_name_id | 2 | name | A | 4 | NULL | NULL | YES | BTREE | | | | user_info | 1 | id_index | 1 | user_id | A | 4 | NULL | NULL | | BTREE | | | +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set测试一：OR 连接两个有单独索引的字段，整个 SQL 查询才会用到索引 (index_merge)，并且我们知道 OR 实际上是把每个结果最后 UNION 一起的。
mysql explain select*from user_info where user_id=1 or 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 set6、优化嵌套查询
使用嵌套查询有时候可以使用更有效的 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 +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+ | 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 mysql explain select* from user_info use index(id_index) where user_id +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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 +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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 +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+ | 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 +----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 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很多时候数据库的性能是由于不合适（是指效率不高，可能会导致锁表等）的 SQL 语句造成，其中有些优化在真正开发中是用不到的，但是一旦出问题性能下降的时候需要去一一分析。
以上就是开发中那些常用的 MySQL 优化有哪些，丸趣 TV 小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注丸趣 TV 行业资讯频道。
