慢sql优化的学习和思考

mysql

优化思路
1、确认慢sql是否有使用索引。
2、确认索引是否生效（例如使用in索引是生效的，但是当in中的数据占总数据量过大份额时，索引可能不生效）。
3、确认是否可以通过添加查询索引。
4、确认mysql服务器参数配置是否有优化空间，例如线程数，缓存读等。
5、数据量过大时，分库分表，通过垂直和水平拆分解决效率问题。
6、扩充服务器资源，做读写分离。

。。。

explain时的部分常用参数含义

项目	SELECT识别符。这是SELECT的查询序列号
select_type	SELECT类型,可以为以下任何一种: 1. SIMPLE:简单SELECT(不使用UNION或子查询) 2.PRIMARY:最外面的SELECT 3.UNION:UNION中的第二个或后面的SELECT语句 4.DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询 5.UNION RESULT:UNION 的结果 6.SUBQUERY:子查询中的第一个SELECT 7.DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 8.DERIVED:导出表的SELECT(FROM子句的子查询)
table	输出的行所引用的表
type	联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序: 1、 system:表仅有一行(=系统表)。这是const联接类型的一个特例。 2、 const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次! 3、 eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。 4、 ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。 5、 ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。 6、 index_merge:该联接类型表示使用了索引合并优化方法。 7、 unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。 8、 index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr) 9、 range:只检索给定范围的行,使用一个索引来选择行。 10、 index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。 11、 ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。
possible_keys	指出MySQL能使用哪个索引在该表中找到行
key	显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_len	显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
ref	显示使用哪个列或常数与key一起从表中选择行。
rows	显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
filtered	显示了通过条件过滤出的行数的百分比估计值。
Extra	该列包含MySQL解决查询的详细信息 1、Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。 2、Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。 3、range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。 4、Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。 5、Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。 6、Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。 7、Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。 8、Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。 9、Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

mysql查询慢sql

1、MySQL的慢查询日志：
　　MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的SQL，就会被记录到慢查询日志中（但是如果不需要调优的话，一般建议不启动该参数，开启慢查询日志会带来一些性能上的影响）。
2、慢查询日志的操作：
　　 1）查看是否开启：show variables like ‘%slow_query_log%’;
　 　2）开启慢查询日志：set global slow_query_log=1;（重启后失效）；
　 　3）设置超时阈值：set global long_query_time=3; 设置后需要重新连接或新开一个会话才能看到修改值或者使用命令 show global variables like ‘long_query_time’;

小贴士：有的公司会使用sql监控工具，统计sql执行情况，只需要登录浏览器的客户端就能查看sql执行情况。参数很具体，执行时间、执行结果、sql语句等等。
有的工具是侵入式的，给予mysql本身的log日志，这种还是比较影响性能的，但是准确度就比较高。有的工具是基于第三方插件的，比如监测mybatis，分析sql，对于mysql本身影响比较小，准确度上就有一定牺牲了。自己搞搞就好，生产慎重操作啊！！

使用索引需要注意可能不生效的特殊情况：

1、索引字段上有函数操作，或者运算。
2、使用like模糊查询是以%开头。
3、单列索引无法储null值，复合索引无法储全为null的值，is null 无法使用索引。
4、经过mysql的查询优化器，如果mysql觉得全表扫描更快时（数据少）。
5、如果列类型是字符串，需要在条件中将数据使用引号引用起来,否则不使用索引。
6、not in、!=、or不使用索引。
7、列数据区分性差，如性别，索引可能不生效，尤其使用in时需要注意。
8、join操作中，MYSQL只有在主键和外键的数据类型相同时才能使用索引。
9、连续between操作。