【MySql学习笔记】Sql优化
优化Sql步骤
一、查看Sql执行频率
# 查询全局的执行情况 show global status like 'Com_______'; # 查询InnoDB的执行情况 show global status like 'Innodb_rows_%';
二、定位低效率的Sql
慢查询日志：设置执行时间，将超过时间的Sql记录在日志中。
show processlist：查看实时的Sql语句执行情况。
三、explain分析执行计划
explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;
四、show profile分析Sql
# 查看MySql是否支持profile select @@have_profiling; # 查看profiling状态,0表示未开启 select @@profiling # 开启profiling set profiling = 1; # 查看指令耗费的时间 show profiles; # 查看时间耗费在了哪个阶段,query_id就是上面查询出来的语句的序号 show profile for query `query_id`
五、trace分析优化器执行计划
打开trace，设置格式为JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。
SET optimizer_trace="enabled=on",end_markers_in_json=on; set optimizer_trace_max_mem_size=1000000;
执行Sql语句
select * from tb_item where id < 4;
检查information_schema.optimizer_trace就能够知道MySql是如何执行Sql的
select * from information_schema.optimizer_trace\G;
Sql优化
插入大量数据[InnoDB存储引擎]
- 主键顺序插入：导入的数据按照主键的顺序排列可以提高导入数据的效率，如果没有主键，系统将自动创建一个内部列作为主键，如果可以给表创建一个主键，就可以提交导入数据的效率。
-
关闭唯一性校验：set unique_checks = 0，在导入数据结束之后执行set unique_checks = 1，恢复唯一性校验。
- 手动提交事务：set autocommit = 0，导入数据后set autocommit = 1。
优化insert语句
-
需要插入多行数据的时候，使用多个值表的insert语句。insert into test values(1, 'Tom'),(2, 'Cat'),(3, 'Jerry');
-
在事务中进行插入
-
start transaction; insert into test values(1,'Tom'),(2,'Cat')，(3,'Jerry'); commit;
-
数据按照顺序插入
优化order by语句
-
两种排序方式
-
通过对返回的数据进行排序（filesort排序）：不是通过索引直接返回排序结果的排序都是filesort排序。
-
通过有序索引扫描直接返回有序数据（using index）：不需要额外排序，操作效率高，查询的时候返回的字段是覆盖索引。
-
多字段排序：要么全升序，要么全降序。查询的时候字段出现的顺序要和索引中出现的顺序相同。
-
尽量减少额外的排序，通过索引直接返回有序数据，order by的顺序需要和索引顺序相同，order by的字段要么都是升序，要么都是降序，否则就会出现额外的排序。
-
filesort排序的优化
-
两次扫描算法：MySql4.1之前使用这种方式。根据条件取出字段和指针信息，然后在排序区sort buffer中排序，如果sort buffer不够，那么就在临时表temprorary table中存储排序结果，完成排序之后根据行指针回表读取记录，该操作会导致大量随机I/O操作。
-
一次扫描算法：一次性取出满足条件的所有字段，在排序区sort buffer中排序后直接输出结果集，排序的时候内存开销大，但是排序的效率比较高。
-
MySql通过比较系统变量max_length_for_sort_data的大小和Query语句取出的字段总大小，来判定使用哪种排序算法，如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种。
-
可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序的效率。
-
优化group by语句
-
order by实际上也会进行排序操作，与order by相比，group by主要是多了排序之后的分组操作。如果分组的时候还使用了一些聚合函数，那么还需要聚合函数的计算，所以在group by的实现过程中，与order by一样可以利用索引。
-
如果查询包含group by，但是用户想要避免排序结果的消耗，那么就可以使用order by null禁止排序。
-
explain select age,count(*) from emp group by age order by null;
# 第一个语句需要filesort，而order by null则不需要filesort
优化嵌套查询
- 使用多表链接查询来替换子查询
优化or条件
-
or所连接的字段，每个字段都需要使用索引，不能使用复合索引。
-
一般不常使用索引，通常使用union来替换or。
优化分页查询
-
分页查询的时候，创建覆盖索引能够更好的提高性能。常见问题：limit 2000000,10，此时要排序前2000010条记录，然后返回2000000 - 2000010的记录，其他的记录丢弃，这样一来查询排序的代价就会很大。
-
优化思路一：在索引上完成分页操作，最后根据主键关联回表查询所需要的其他列。
-
explain select * from tb_item t, (select id from tb_item order by id limit 20000, 10) a
where t.id = a.id; -
优化思路二：如果表中数据是主键自增的。
-
select * from tb_item id > 20000 limit 10;
使用Sql提示
-
优化数据库的一种手段，通过人为的方式告诉数据库使用哪一个索引。
-
use index：查询语句中表名的后面添加use index（索引名称）来提供索引列表，这里只是提供参考，不强制使用索引。
-
ignore index：如果用户想让MySql忽略一个或者多个索引，可以在表名后面调价ignore index（索引名称）。
-
force index：强制MySql使用一个特定的索引，查询的时候在表名后面添加force index。
