目录
5、mysql在使用不等于（!=、<、>）的时候无法使用索引会导致全表扫描
6、is null 、is not null 也无法使用索引
1、尽量使用Index方式排序，避免使用FileSort方式排序，
第七章、索引优化
（一）索引分析
1、单表
（1）建表
（2）查询category_id为1且comments大于1的情况下，views最多的article_id
结论：很显然type是ALL，Extra还出现了usingfilesort要优化
a、创建索引
create index idx_article_ccv on article (category_id,comments,views)
show INDEX from article
索引已经排好序了，再查一遍
为什么？范围会导致索引失效，comments>1这个会导致索引失效
结论：
- type变成了range，这个是可以忍受的，但是extra这里使用了Using filesort仍是无法接收的
- 但是我们已将建立了索引没用，这是因为按照BTree索引的工作原理
- 先排序category_id
- 如果遇到相同的category_id，则再排序comments，如果遇到相同的comments，在排序views
- 党comments字段在联合索引处于中间位置时
- 因comments>1条件是一个范围值（所谓的range）
- MySQL无法利用索引再对后面的views部分进行检索，即染个类型查询字段后面的索引无效
b、删除索引，建立新索引(comments字段是一个范围，因此家索引是不加上这个字段即可)
删除 DROP INDEX idx_article_ccx on article
新建 create index idx_article_cv on article (category_id,views)
再查一次 EXPLAIN SELECT id,arthor_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views desc
2、俩表
（1）建表,class表和book表都有20条记录，且都有card字段，join表时都用上啊card字段
class表
book表
(2)优化分析
EXPLIAN SELECT * FROM class left join book on class.card = book.card
结论：
a、尝试建索引
alter table book add index Y(card)
book 表建立了索引Y，用在card字段
在查一次
简单结论：A表left join B ,索引加在B表的字段上
drop index Y on book 删除加载class试试
alter table class add index Y(card)
再查一次
虽然用上了索引，但是效果没那么好
所以结论如下
左连接特性：LEFT JOIN条件用于确定如何从右表搜索行，左边一定有，所以右边是我们的关键点，一定需要建立索引
右连接同理，索引加在左表上
3、三表
（1）建表
在俩表的基础上多加一个phone表
（2）索引建立优化
EXPLAIN SELECT * FROM class left join book on class.card = book.card left join phone on book.card=phone.card;
根据sql，应该现在book表和phone表上的card字段加索引
alter table book add index Y(card);
alter table phone ass index Z(card);
添加后在查询
【结论】
join语句优化
- 尽可能减少join语句的NestedLoop（嵌套循环）的循环总次数，永远小结果集驱动大的结果集
- 优先优化NestedLoop的内层循环
- 保证join语句中被驱动表上join条件字段已经被索引；
- 当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝惜joinbuffer的设置
（二）索引失效及优化
建表
1、最佳左前缀法则
如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列
添加一个复合索引
ALTER TABLE staffs add INDEX idx_staffs_nameAgePos(name,age,pos);
show INDEX FROM staffs;
EXPLAIN SELECT * FROM staffs WHERE name='July'能用到索引
EXPLAIN SELECT * FROM staffs WHERE name='July' AND age=23 也能用到索引
EXPLAIN SELECT * FROM staffs WHERE name='July' AND age=23 AND pos='dev' 也能用到索引
但是以下这种情况，没有用到索引
EXPLAIN SELECT * FROM staffs WHERE AND age=23 AND pos='dev'
EXPLAIN SELECT * FROM staffs WHERE pos='dev' 也没有用到索引
如果把索引的3个字段中间拆掉
EXPLAIN SELECT * FROM staffs WHERE name='July' AND pos='dev'
也用到了索引，不过只是部分
2、不要在索引列上做任何操作
不在索引列上做任何操作（计算、函数、自动或手动类型转换），会导致索引失效而转向全表扫描
eg：
原来的name加上left(name,4)之后，索引就失效了
3、存储引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT * FROM staffs WHERE name='July' AND age=23 AND pos='dev'
如果把age=23变成age>23
EXPLAIN SELECT * FROM staffs WHERE name='July' AND age>23 AND pos='dev'
4、减少select * 的使用
尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少select
select后面接的字段如果和使用的索引的字段一致的话，是有可能using index的
5、mysql在使用不等于（!=、<、>）的时候无法使用索引会导致全表扫描
首先 EXPLAIN SELECT * FROM staffs WHERE name='July'
能用到索引，但是修改一下
EXPLAIN SELECT * FROM staffs WHERE name!='July'
6、is null 、is not null 也无法使用索引
7、like以通配符开头，索引会失效
like以通配符开头（%abc）mysql索引会失效变成全表扫描的操作
EXPLAIN SELECT * FROM staffs WHERE name like '%July%'
EXPLAIN SELECT * FROM staffs WHERE name like '%July'
都是全表扫描
EXPLAIN SELECT * FROM staffs WHERE name like 'July%'
type为range了，说明通配符只有写在右边才不会失效
问题：解决like ‘%字符串%’是索引不被使用的方法
查询的字段必须是主键或者是建立了索引的字段，才不会导致索引失效，全表扫面
8、字符串不加单引号索引失效
字符串不加单引号索引失效，会引起数据类型的转换而导致全表扫描
9、少用or，用它来连接是会索引失效
（三）一般性建议
- 定值、范围还是排序，一般order by是给个范围
- group by基本上都需要进行排序，会有临时表产生
- 对于单间索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
- 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字段的索引
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
（四）总结口诀
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；
不等空值还有OR，索引失效要少用；
VAR引号不能丢，SQL高级也不难！
（五）小表驱动大表（IN or EXISTS）
优化原则：小表驱动大表，即小的数据集驱动大的数据集
1、in和exists的区别
2、exists解析
SELECT ........ FROM table WHERE EXISTS (subquery)
将主查询的数据，放到子查询中做条件验证，根据验证结果（TURE or FALSE）来决定主查询的数据结果是否得以保留。
注意：
- EXISTS(subquery)只返回TRUEorFALSE，因此子查询中的select * 也可以是select 1 或其他，官方说法是实际上会忽略SELECT清单，因此没有区别
- EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可信性实际检验以确定是否有效率问题
- EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最右需要具体问题具体分析
（六）order by和group by 关键字优化
1、尽量使用Index方式排序，避免使用FileSort方式排序，
尽可能在索引列上完成排序操作，遵照索引引建的最佳左前缀
(1)建表
（2）索引 CREATE INDEX idc_A_ageBirth ON tbla(age,birth);
（3）EXPLAIN SELECT * FROM tbla WHERE age>20 ORDER BY age
（4）EXPLAIN SELECT * FROM tbla WHERE age>20 ORDER BY age，birth;
（5）EXPLAIN SELECT * FROM tbla WHERE age>20 ORDER BY birth;
出现了Using filesort
（6）EXPLAIN SELECT * FROM tbla WHERE age>20 ORDER BY birth，age;
同样有filesort的问题
2、SQL支持俩方式的排序，FileSort和Index
Index效率高，值MySQL扫描索引本身完成排序。fileSort方式效率较低
（1）ORDER BY 满足俩种情况就会使用Index方式排序
- ORDER BY语句使用索引最左前列
- 使用Where子句与ORDER BU子句条件列满足索引最左前列
（2）如果不在索引列上，filesort有俩种算法：双路排序和单路排序
a、双路排序
MySQL4.1之前使用双路排序，字面意思就是俩次扫描磁盘，最终得到数据，去取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
从磁盘中读取排序字段，在buffer进行排序，再从磁盘取其他字段
去一批数据，要对磁盘进行俩次扫描，众所周知，I\O是很耗时的，所以MySQL4.1后，出现了第二种改进的算法，就是单路排序
b、单路排序
从磁盘读取查询所需的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出。
它的效率更快一些，避免了第二次读取数据。并且把随机IO变成顺序IO，但是他会使用更多的空间，因为他把每一行都保存在了内存中了
c、结论及问题
单路是后出的，总体而言还是好过双路的
但是单路也会有它的问题，sort_buffer大小问题
在sort_buffer中，方法B比方法A要多占很多空间，因为方法B是把所有字段都取出来，所以可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能去sort_buffer容量大小的数据，进行排序（常见tmp文件，多路合并），排完再取去sort_buffer容量大小，再排。。。从而多次I\O操作，反而得不偿失
解决方案：
增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置
3、提高order by速度
（1）Order By是select * 是一个大忌值query需要的字段，这点十分重要，在这里影响的是：
- 当query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型是，会改进后的算法—单路排序，否则用老算法——双路排序
- 两种算法的数据都有可能超过sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size.
（2）尝试提高sort_buffer_size
不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个线程的
（3）尝试提高max_length_for_sort_data
提高这个参数，会增加用改进算法的概率，但是如果设的太高，数据总量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率
4、group by
- group by实质是排序后进行分组，遵照索引最佳左前缀
- 当无法使用索引列，增大max_length_for_sort_data参数设置+增大sort_buffer_size参数的设置
- where高于having，能写在where限定的条件就不要去having限定了
第八章：慢查询日志
（一）、概述
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体运行时间超过long_query_time值得SQL，则会被记录到慢查询日志中。
具体指运行时间超过long_query_time值得SQL，则会被记录到慢查询日志中，long_query_time的默认值为10，意思是运行10秒以上的语句
由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒，我们就算慢查询，希望能收集超过5秒的sql，结合之前的explain进行全面分析
说明：默认情况下，MySQL数据可没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
（二）操作
1、查看是否开启
show VARIABLES LIKE '%slow_query_log%'
默认为OFF，可用过设置slow_query_log的值来开启
2、开启
set global slow_query_log=1;
需要注意的是使用set global slow_query_log=1;开启慢查询日志知识对当前数据库生效，如果MySQL重启后则会失效
一般不建议永久开启慢查询日志，如果一定要做，只能修改配置文件
要永久生效，就必须修改配置文件my.cfg
修改my.cfg文件[mysqld]下添加或修改参数slow_query_log和slow_query_log_file后，然后重启MySQL服务器，也即将如下两行配置写入my.cfg文件
slow_query_log=1
slow_query_log_file=/var/lib/mysql/xiong-slow.log
关于慢查询参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log(如果没有指定的话)
3、设置其他参数
查看当前多少秒算慢
show VARIABLES LIKE '%long_query_time%'
long_query_time默认时间是10秒，也就是大约10秒的sql会记录在慢查询日志中，非大于等于
设置时间
set global long_query_time=3; 需改阈值到3秒钟的就是慢sql
设置后需要从新连接或新开一个回话才能看到修改值
show global variables like 'long_query_time';
4、测试
select sleep(4);
查询日志
show GLOBAL STATUS LIKE '%Slow_queries%'
5、永久生效的方式（配置文件）
【mysqld】下配置
slow_query_log=1;
slow_query_log_file=/var/lib/mysql/xiong-slow.log;
long_query_time=3;
log_output=FILE;
（三）日志分析工具mysqldumpslow
在生产环境中，如果要手动分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow
1、参数：
-s:是表示按照何种方式排序
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
-t：返回前面多少条数据
-g：后面代培一个正则匹配模式，大小写不敏感
2、参考
得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/xiong-slow.log
得到访问次数最多的十个sql
mysqldumpslow -s c -t 10 /var/lib/mysql/xiong-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/xiong-slow.log
另外建议在使用这些命令是结合|和more使用，否则有可能出现爆屏情况
mysqldumpslow -s c -t 10 /var/lib/mysql/xiong-slow.log | more
（四）show profile 进行sql分析
1、是什么
是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于sql的调优
默认情况下。参数处于关闭状态，并保存最近15次的运行结果
2、操作
Show variables like 'profiling'
如果是OFF，那么 set profiling=on;
（1）empl表中已经有250万条数据
SELECT * FROM empl GROUP BY id%10 limit 1600000; 用了11.9s
SELECT * FORM EMPL GROUP BY id%20 ORDER BY 5; 用了4.6s
（2）先用show profiles;
找到目标query的query id
（3）show profile cpu,block io for query 44;
3、show profile后面还可以有其他参数
ALL :显示多有的开销信息
BLOCK IO :显示快IO相关开销
CONTEXT SWITCHES :上下文切换相关开销
CPU :显示CPU相关开销信息
IPC：显示发送和接受相关开销信息
MEMORY：显示内存相关开销信息
PAGE FAULTS：显示页面错误相关开销信息
SOURCE：显示和Source_funcrion,Source_file,Source_line相关的开销信息
SWAPS：显示交换次数相关开销的信息
4、如何查看查询出的结果
如果在status出现了一下内容，证明sql可能有问题
- converting HEAP to MyISAM 查询结果太大，内存都不都用了网磁盘上搬了
- Creating tmp table：创建了临时表，拷贝数据到临时表，用完了再删除
- Copying to tmp table on disk 把内存中临时表复制到磁盘
- locked
（五）全局查询日志
生产环境中不要开启这个功能
1、启动
set global general_log=1;
set global log_output='TABLE';
此后，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面命令查看
select * from mysql.general_log
（六）总结
- 慢查询的开启并捕获
- explian + 慢sql分析
- show profile 查询sql在MySQL服务器里面的执行细节和生命周期情况
- sql数据库服务参数的调优
545
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
