MySQL系列(七)--SQL优化的步骤

　　前面讲了如何设计数据库表结构、存储引擎、索引优化等内存，这篇文章会讲述如何进行SQL优化，也是面试中关于数据库肯定会被问到的，

这些内容不仅仅是为了面试，更重要的是付诸实践，最终用到工作当中

　　之前的MySQL内存地址：MySQL系列内容

如何获取存在性能的SQL：

1、通过生产环境用户、测试人员反馈的应用响应速度较慢，可能就是SQL性能较差导致的

2、通过慢查询日志获取

3、实时获取存在性能问题的SQL

MySQL慢查日志：

参数：

　　1、slow_query_log　　是否启动慢查询日志，默认不开启，on/off，动态参数，运行时通过set global slow_query_log=on设置，也可以

通过脚本定时开关

　　2、slow_query_log_file　　日志存储和数据存储的文件名和路径，最好是自己设置，而不是默认，日志和数据文件要区分开

　　3、long_query_time　　慢查询日志SQL执行时间的阈值，单位s，默认10s，超过这个执行时间的SQL都会被记录下来，无论是查询还是修改，

还是记录已经回滚的SQL，最大精确到微妙ms，可以设置为1s比较合适

　　4、log_queries_not_using_indexes　　是否记录未使用索引的SQL

设置参数：

　　1、my.cnf，永久生效

　　2、通过SET GLOBAL设置参数，例如SET GLOBAL log_queries_not_using_indexes = ON。MySQL重启之后参数就会被重置

查看当前session慢SQL的条数：

　　SHOW STATUS LIKE '%slow_queries%'; 只记录DDL中的慢查询SQL

慢查询日志常用分析工具：

1、MySQLdumpslow 官方推荐

　　汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出

mysqldumpslow -s r -t 10 mysql-slow.log;

-s order(c,t,l,r,at,al,ar)

输出结果排序方式：

c count查询总次数

t time总时间

l lock查询锁的时间

r row总数据行

加上a，就是取平均数

-t top指定取前几条作为结果输出

Count是指执行次数为1063，time执行时间，lock锁的时间，rows单次查询返回的行数，共106300

2、pt-query-digest：分析慢查询日志、二进制日志等

pt-query-digest --explain h=127.0.0.1,u=root,p=pasword mysql-slow.log

--explain是指是否包含SQL的执行计划

u和p是指在哪台服务器执行计划

参数也可以直接省略

建议在从服务器生成慢查询日志

相比MySQLdumpslow，pt-query-digest多了SQL执行计划，所以更推荐pt-query-digest

3、实时获取慢查询SQL：

SELECT ID,`user`,`HOST`,DB,COMMAND,TIME,STATE,INFO FROM information_schema.`PROCESSLIST` WHERE TIME >=60;

通过上面的SQL可以实时获取执行时间满足条件的SQL

4、直接查看慢查询log文件

那么SQL查询速度慢的原因？需要了解SQL处理的过程

SQL处理查询请求的过程：

　　1、客户端发送SQL给服务器

　　2、服务器检查是否在查询缓存中命中该SQL，通过一个对大小写敏感的hash查找实现，只能是全值匹配，如果命中，查询用户权限

　　3、服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划

　　4、然后存储引擎根据查询计划来查询数据

　　5、将结果返回给客户端

查询缓存参数：

query_cache_type 设置查询缓存是否可用，on/off/demand（设置demand通过查询语句中的SQL_CACHE和SQL_NO_CACHE控制是否使用查询缓存）

query_cache_size 设置查询缓存内存大小，为1024整数倍

query_cache_limit 设置查询缓存可用存储的最大值，超过这个值就不会被缓存

query_cache_wlock_invalidate 设置如果表被锁住是否返回缓存中的数据，默认no

query_cache_min_res_unit 设置查询缓存分配的内存块最小单位

PS：

　　如果数据库读写十分频繁，建议关闭查询缓存，因为对缓存查询的时候，需要对缓存加锁，query_cache_type设置为off，query_cache_size

设置为0

　　如果没有开启查询缓存或者缓存缓存未命中，需要把SQL解析为查询计划，然后查询计划与存储引擎进行交互，这个过程包括：解析SQL，预处

理，优化SQL执行计划

解析SQL，预处理：

　　1、检查语法是否使用了正确的关键字

　　2、关键字的顺序是否正确

　　3、检查表和数据列是否存在以及名称和别名是否存在歧义等

上面的检查通过之后，查询优化器就可以生成查询计划了，会对可以使用的索引进行比较，如果可以使用的索引过多，会对查询效率造成影响

造成优化器生成错误执行计划的原因：

　　1、统计信息不准确，例如InnoDB提供的统计信息是抽样的，可能是不正确的

　　2、执行计划的成本估算不等于实际执行计划的成本，因为MySQL不知道哪些页存在内存中，哪些页面数据是顺序读取

　　3、执行优化器认为的最优可能和开发人员认为的最优不同，我们想要只是SQL时间最短，而MySQL基于其成本模型选择最优的执行计划

　　4、优化器不考虑并发查询，涉及到锁的问题

　　5、优化器不考虑不受控制的成本，例如存储过程，自定义函数

举个栗子：

　　EXPLAIN SELECT count(id) FROM role ;我们想要走id的primary key，但是实际上是user_id的索引，此时可以强制使用主键索引

EXPLAIN SELECT count(id) FROM role FORCE INDEX(PRIMARY);

如果不是上面的原因，是因为SQL本身或者表结构的问题

查看SQL执行计划：

　　使用explain参数，关于explain的使用大家应该都知道，我们需要尽可能走索引，或者修改SQL语句

可优化的SQL类型：

　　1、重新定义表的关联顺序

　　2、将外连接转化为内连接，where条件和库表结构等都会影响

　　3、使用等价交换规则，例如where 1=1 and 1>0，直接被改写成where 1>0

　　4、对优化count() min() max()，例如如果InnoDB的B-tree索引，数据是有序的。如果使用了这个优化，在执行计划中显示：select tables

optimized away，意思：优化器从执行计划中移除这个表，以一个常数取代

　　5、将表达式转化为常数

　　6、子查询优化，转化为关联查询

　　7、满足某个条件，就会提前终止查询。例如肯定不满足的条件，例如Id int(11) unsigned ，使用where id = -1，就会直接返回null

　　8、对in()条件进行优化

如何确定处理的每个阶段所消耗的时间：

1、使用profile：

　　1).set profiling=1 来启动profile，session级别配置

　　2).执行查询

　　3).show profiles 查询SQL总的消耗时间

　　4).show profile for N 查看各个阶段消耗时间，N是指query_id

命令：show profiles

命令：show profile for query N

命令：show profile cpu for query N

2、使用performance_schema显示每个线程执行SQL的各个阶段消耗时间

特定的SQL查询优化：

1、对大表数据的修改最好分批次处理，例如：1000万行记录的表中删除100w条数据，一次删除5000条数据，每次删除之后暂停几秒，留给主从复制的时间

DELIMITER $$

USE mysql $$

DROP PROCEDURE IF EXISTS p_delete_rows $$

CREATE DEFINER=root@127.0.0.1 PROCEDURE p_delete_rows()

BEGIN

	DECLARE v_rows INT;

	SET v_rows=1;

	WHILE v_rows > 0

	DO

		DELETE FROM sbtest WHERE id >= 90000 AND id <=390000 LIMIT 5000;

		SELECT ROW_COUNT() INTO v_rows;

		SELECT SLEEP(5);

	END WHILE;

END $$

DELIMITER;

2、如何修改大表的表结构

　　1).现在从服务器上修改，然后主从切换，然后在原主服务器修改，但是有风险

　　2).

过程：

　　主服务器创建一个新表，就是想要修改后的表结构

　　然后将老表的数据复制到新表

　　通过触发器将两个表的数据进行同步

　　当数据一致的时候，在老表加排它锁，重命名新表，删除老表

可以通过pu-online-schema-change这个工具实现上面的工作

3、优化not in和<>

例如把not in，改写成left join避免多次子查询