慢查询实操

面试的时候问的多了，实操经验太少了，没有底气，这里具体的做一次

数据库中设置MySQL慢查询

在my.ini中添加几行
//定义查询多少秒的查询算是慢查询
long_query_time=2
//配置慢查询记录文件
slow-query-log=On
slow_query_log_file=“msql_slow_query_log”
//记录下没有使用索引的query
log-query-not-using-indexes

或者通过mysql数据库开启慢查询

分析mysql慢查询日志

直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句

select_type:所使用的的查询类型
(1)DEPENDENT SUBQUERY：子查询内层的第一个SELECT，依赖于外部查询的结果集。
(2)DEPENDENT UNION：子查询中的UNION，且为UNION中从第二个SELECT开始的后面所有SELECT，同样依赖于外部查询的结果集。
(3)PRIMARY：子查询中的最外层查询，注意并不是主键查询。
(4)SIMPLE：除子查询或UNION之外的其他查询。
(5)SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集。
(6)UNCACHEABLE SUBQUERY：结果集无法缓存的子查询。
(7)UNION：UNION语句中第二个SELECT开始后面的所有SELECT，第一个SELECT为PRIMARY。
(8)UNION RESULT：UNION 中的合并结果。

table:显示这一步所访问的数据库中的表的名称
partitions:分区表命中的分区情况。非分区表该字段为空（null）。
type:对表使用的访问方式
1)all：全表扫描。
(2)const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。
(3)eq_ref：最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。
(4)fulltext：进行全文索引检索。
(5)index：全索引扫描。
(6)index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行合并（merge），再读取表数据。
(7)index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。
(8)rang：索引范围扫描。
(9)ref：Join语句中被驱动表索引引用的查询。
(10)ref_or_null：与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。
(11)system：系统表，表中只有一行数据。
(12)unique_subquery：子查询中的返回结果字段组合是主键或唯一约束。

possible_keys:该查询可以利用的索引，用来优化很重要！！！
key:选择使用的索引
key_len:被选中使用索引的索引键长度
ref:列出是通过常量（const），还是某个表的某个字段（如果是join）来过滤（通过key）的。
rows:通过系统收集的统计信息估算出来的结果集记录条数。
filtered:表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。
Extra:查询中每一步实现的额外细节

常见的慢查询优化

索引没起作用的情况

• 使用like关键字查询：在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。
• 使用多列索引的查询语句：MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。最左前缀匹配原则

优化数据库结构

• 将字段很多的表分解成多个表： 对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
• 建立中间表：对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

分解关联查询

将一个大的查询分解为多个小查询是很有必要的。很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效
SELECT * FROM tag
JOIN tag_post ON tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = ‘mysql’;

分解为：
SELECT * FROM tag WHERE tag = ‘mysql’;
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567);

优化limit分页

在系统中需要分页的操作通常会使用limit加上偏移量的方法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。
偏移量太大的话，查询100020条记录，但是只要20条,前面的都不要，代价太高，优化方法最简单的就是尽可能使用索引覆盖,而不是查询所有的列
对于下列的查询： select id,title from collect limit 90000,10;
该语句存在的最大问题在于limit M,N中偏移量M太大（我们暂不考虑筛选字段上要不要添加索引的影响），导致每次查询都要先从整个表中找到满足条件 的前M条记录，之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。 试想，如我们下一次的查询能从前一次查询结束后标记的位置开始查找，找到满足条件的100条记录，并记下下一次查询应该开始的位置，以便于下一次查询能直接从该位置 开始，这样就不必每次查询都先从整个表中先找到满足条件的前M条记录，舍弃，在从M+1开始再找到100条满足条件的记录了。

我觉得比较好的方法有
关联延迟： Select news.id, news.description from news inner join (select id from news order by title limit 50000,5) as myNew using(id);
建立复合索引：select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0,10

分析具体的SQL语句

• exists有什么弊端
  由exists执行原理可知，a表(外表)使用不了索引，必须全表扫描，因为是拿a表的数据到b表查。而且必须得使用a表的数据到b表中查（外表到里表中），顺序是固定死的。

• 如何优化：
  建索引。但是由上面分析可知，要建索引只能在b表的id字段建，不能在a表的id上，mysql利用不上。
  由于exists查询它的执行计划只能拿着a表的数据到b表查（外表到里表中），虽然可以在b表的id字段建索引来提高查询效率。
  但是并不能反过来拿着b表的数据到a表查，exists子查询的查询顺序是固定死的
  可以把查询修改成innerjoin：select * from a inner join b on a.id=b.id;

• 为什么innerjoin可以left和right不可以
  inner join中的两张表，如： a inner join b，但实际执行的顺序是跟写法的顺序没有半毛钱关系的，最终执行也可能会是b连接a，顺序不是固定死的。如果on条件字段有索引的情况下，同样可以使用上索引。
  在inner join的连接中,mysql会自己评估使用a表查b表的效率高还是b表查a表高，如果两个表都建有索引的情况下，mysql同样会评估使用a表条件字段上的索引效率高还是b表的。
  而我们要做的就是：把两个表的连接条件的两个字段都各自建立上索引，然后explain 一下，查看执行计划，看mysql到底利用了哪个索引，最后再把没有使用索引的表的字段索引给去掉就行了。