MySQL常见瓶颈
- CPU
CPU满负载，一般发生在数据装入内存活从磁盘读取数据时 - 磁盘IO
磁盘I/O瓶颈一般发生在装入数据远大于内存容量时 - 服务器硬件性能：
机器配置比较低，可通过命令：top、free、iostat、vmstat查看系统性能状态
explain关键字
explain可以模拟优化器执行SQL查询语句，从而知道是如何处理SQL语句的，分析查询语句或者是表结构的性能瓶颈。
作用
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引实际被使用
- 表之间的引用关系
- 每张表有多少行被优化器查询
使用
explain + [SQL语句]
结果
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
|---|---|---|---|---|---|---|---|---|---|
| * | * | * | * | * | * | * | * | * | * |
名词解释
-
id
a. id相同，执行顺序由上往下
select查询的序列号，包含一组数字，表示查询中执行select紫玉活操作表的顺序。它的值有三种情况：
b. id不相同，如果是子查询，id的序号会递增，id值越大执行优先级越高id相同id不相同
c. id相同和不同的情况同时存在混合存在的情况 select_type
查询类型一般包含simple、primary、subquery、derived、union、union result这六种类型。主要由于区别普通查询、联合查询、子查询等复杂的查询情况。
a. simple：简单的select查询语句，查询中不包含子查询和union
b. primary：查询中如果包含任何复杂的子部分，最外层的查询则会被标记为primary
c. subquery：在select或where列表中包含了子查询
d. derived：在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放在临时表里
e. union：如果第二个select出现在union之后，则被标记为union；如果union包含在from字句的子查询中，外层的select将被标记为derived
f.union result：从union表获取结果的selecttable
type
type表示访问类型，是SQL的重要指标，它有system、const、eq_red、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、all；一日常工作中一般包含all、index、range、ref、eq_ref、const、system、null这八种类型，执行效率从高到低依次是：system>const>eq_ref>ref>range>index>all
百万条数据以上，如果查询类型是all，则需要进行查询优化，all走的是全表扫描。一般来说需要保证查询至少达到range级别，尽可能达到ref；
system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现
const：表示通过索引一次就找到了，const常用于比较primary key或者unique索引，因为只匹配一行数据，所以很快；如将主键置于where条件列表中，MySQL就能将该查询转换为一个常量
eq_ref：执行的是唯一性索引扫描。对于每个索键，表中只有一条记录与之匹配。常见于主键活唯一索引扫描
ref：执行的是非唯一性所以扫描，返回匹配，某个单独值对应的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然后它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
range：只检索给定范围的行，使用一个索引来选择行。可显示使用了哪个索引，一般就是在你的where语句中出现了between、>、<、in等查询语句，这种返回扫描比全表扫描要好，因为它只需要检索开始于索引的某一点和结束语的另外一点。
index：全称为full index scan（全索引扫描），index与all的区别为index只遍历索引树，index是从索引中读取，而all是从磁盘中的数据表读取，index通常比all的全表扫描快，因为索引文件通常比数据文件小；
all：full table scan，全表扫描，将会遍历全表以找到匹配的行
一般情况下，尽量保证查询达到range级别，数据量百万以上之后能达到ref级别possible_keys
显示可能应用在这张表中的一个或多个索引，查询涉及到的字段若存在索引，则该索引将会被列出，但不一定会被实际查询使用key
实际查询中使用到的索引，如果为null，则表示没有建索引或者建索引了没有被使用，查询中如果使用了覆盖索引，则改缩影会出现在key列表中（覆盖索引（也叫索引覆盖）：官方解释是说select的数据列支用从索引中就能获得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，简而言之就是查询的字段要被复合索引的字段所覆盖，select user_name ,user_age from t_user; create index idx_name_age on t_user(user_name,user_age);）key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引长度；在不损失精确性的情况下，长度越短越好，ken_len显示的值为索引的最大可能长度，并非实际使用长度，即key_len是根据表定义而计算出来的，不是通过表内检索出来的ref
显示索引的那一列被使用了，如果可能的话，还可以是一个常量，表示哪些列或者常量被用于查询索引列上的值rows
根据表统计信息以及索引选用情况，大致估算出找到所需记录需要读取的行数extra
包含不适合在其他列中显示但十分重要的额外信息；一般包含using filesort、using temporary、using index、using where、using join buffer、impossible where、select tables optimized away、distinct这八这种场景（比较重要的有：using filesort、using temporary、using index）；
using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成的排序操作成为“文件排序”。extra出现using filesort的时候，SQL基本不符合性能要求，需要尽可能优化;九死一生sql
using temporary：查询使用了临时表保存中间结果，MYSQL在对查询结果排序时使用临时表，查询结束后再将临时表删除；常见于order by和分组查询group by的场景；出现using temporary这种情况SQL必须优化，group by时，索引的列必须跟group by 后面的列保持一致
using index：表示相应的select操作中使用了覆盖索引covering index，避免访问了表的数据行，效率不错；如果同时出现using where，表示索引被用来执行索引键值查找；如果没有同时出现using where ，表示索引用来读取数据而不是执行查找动作；
using where：表示查询使用了where过滤
using join buffer：表示查询使用了连接缓存（如果查询语句中join比较多，可适当调整mysql配置文件的join buffer配置）
impossible where：where字句的值总是false，不能用来获取任何元素
select tables optimized away：在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完结优化不常见忙也不常用
distinct：优化distinct操作，在找到第一匹配的原始之后就停止查找相同值的操作
索引分析
单表
create index idx_xxx on t_test(xxx,xxxx);
where后面有范围比较会使得范围字段后面的索引失效问题
example：
select * from t_test where user_name='aaa' and user_age >10 order by city_code desc limit 1;
建name 、age、code索引之后，出现using filesort，由于user_age字段进行了比较，导致后面的city_code未生效，索引未达到要求
重建索引
drop index idx_nac on t_test ;
create index idx_nac on t_test(user_name,city_code);
双表
如果有left join查询，则索引应建立在右表，也就是a left jon b里的b表；
如果有right join，则索引应该建立在左表，也就是a right join b里的a表
多表
多表的情况下索引最好建立在需要经常查询的字段中
join语句优化
- 尽可能减少join语句中的嵌套循环总次数，
永远用小结果集驱动大的结果集——小表驱动大表，小表会走全表扫描； - 优先优化嵌套循环的内循环
- 保证join语句中被驱动表上join条件字段已经被索引
- 当无法保证被驱动表的join条件字段被索引且内存充足的前提下，可以优先增加MySQL的join buffer配置
索引失效分析
索引原则
- 全值匹配的索引效率最优
- 索引匹配原则为：最佳左前缀法则（如果索引配置了多列，查询的时候要遵循最左法则，因为查询是从索引的最左边列开始并且不跳过中间的列）
- 不在索引列上做任何操作（计算、函数、手动或自动类型转换，），这些操作会导致索引失效而转向全表扫描
- 不能使用索引中最左边的列作为范围条件（范围条件查询会导致索引失效）
- 尽量使用覆盖索引（只访问索引列对应的查询），减少select *
- mysql 在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
- is null、is not null 无法使用索引
- like一通配符开头（'%abs...'）mysql索引会失效会变成全表扫描，
通配符%在右边会走索引，覆盖索引可以使通配符%aaa%生效 - 字符串不加单引号会导致索引失效（select * from
t_test where user_name= '250' ; select * from t_test where user_name=250） - 少用or，用它连接时可能会导致索引失效（不会失效：select * form t_test where user_name='aa' or user_name='cc';失效：select * from t_test where user_name='aa' or user_age=12）
- group by 大部分场景都会进行排序，如果排序的时候出现filesort则可能会出现temporary table，导致性能直线下降
- 一般order by都是给定范围进行排序，order by 使用索引左前缀原则
总结五句：
火车不能没有头；
火车中间不能断；
索引列上不计算；
like开头跟常量；
范围之后全失效
建议
- 对于单值索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引时，当前query中过滤性最好的字段在索引字段排序中，位置越靠左越好
- 在选择组合索引是，尽量选择可以包含当前query中where子句中更多字段的索引（索引覆盖原则）
- 尽可能通过分析统计信息和调整query的写法来达到选择适合索引的目的
- 尽可能在索引列上完成排序查询
MYSQL查询优化
- 在保证数据完整性不受影响的情况下，
永远使用小表驱动大表查询，因为小驱动大，连接次数少；大驱动小，链接次数大（类似嵌套for循环）
查询语句1：select * from A where id in (select id from B)
等价于
for sleect id from b
for select * from A where A.id=B.id
查询语句1也可些成：select * from A where exists(select 1 from B where B.id=A.id)
等价于
for select * from A
for select * from B where B.id=A.id
所以可得出，当A数据量小B数据量时，exists比in效率高 - order by查询优化
- group by查询优化
order by查询优化
- order by排序时，尽可能不适用select *，只查自己需要的部分字段；因为当select的字段大小总和小于max_length_for_sort_data而排序字段不是TEXT|BLOB类型时，会用4.3.1版本后改进的单路排序算法，若是大于max_length_for_sort_data则会用老MySQL版本的多路排序算法。同时查询字段太多可能会导致另外一种情况——就是使用两种算法查到的数据结果都有可能超过sort_buffer的容量，超过 之后会创建tmp文件进行合并排序，导致多次磁盘IO，但是使用单路排序算法的风险会更大一些，所以这时候可以适当提高sort_buffer_size配置
- 适当提高sort_buffer_size，提高此参数配置对单路、多路排序算法有会提高效率，配置值不是无限大，要根据系统配置能力适当设置
- 适当提高max_length_for_sort_data，提高这个参数会增加改进算法的概率，单如果提高太多，数据总容量超出sort_buffer_size的概率就会增大，会导致明显的磁盘IO和CPU使用率降低
order by正常使用索引的场景
索引：idx_xxx(a,b,c);
order by a;
order by a,b;
order by a,b,c;
order by a desc ,b desc ,c desc;
如果where语句使用索引的左前缀定义为常量，则order by以下场景能使用索引
where a= const order by b,c;
where a= const1 and b=const2 order by c;
where a= const1 and b>const2 order by b,c
order by不能使用索引场景（ 可能产生 filesort）
order by a asc,b desc,c desc;#排序不一致
where g=const order by b,c;#丢失a索引
where a=const order by c;#中间丢失b索引
where a=const order by a,d;#d不是索引的一部分
where a in (......) order by b,c;#范围查找之后索引全失效了
group by查询优化
- group by 实质是先排序后进行分组，按照索引最佳左前缀原则
- 当无法使用索引列时，适当增大max_length_for_sort_data参数配置和增大sort_buffer_size配置
- where性能高于having，能在where限定的条件尽量不要写在having里面
慢查询日志分析
MySQL慢查询日志是mysql提供的一种日志记录，它用来记录在mysql中响应时间超过阀值的语句，具体指的是运行时间超过long_query_time的SQL语句，超过阀值的会被记录在慢查询日志中。默认值为10秒，默认情况下不被开启，如果没有调优需要时也不建议开启，因为开启会增加日志文件写入带来性能影响。
a. 查看开启状态：show variables like '%slow_query_log%';
b. 当前session开启开关：set global slow_query_log =1;
c. 当前session关闭开关：set global slow_query_log =0;
d. 永久性开启关闭开关需要在my.conf配置
slow_query_log=1
slow_query_log_file=日志路径
e. 查看慢查询SQL时间配置：show variables like 'long_query_time';
修改慢查询SQL时间后需要重新打开连接才能显示已生效，在原有session不会显示已生效的值；
f. 查看系统慢SQL条数：show global status like ‘%Slow_queries%’;
g. 查看慢SQL文件位置：show variables like '%slow_query_log_file%';
慢SQL分析工具
分析语法：
s：表示SQL查询排序方式
c：访问次数
l：锁定时间
r：返回记录数量
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：返回当前前面多少条慢SQL记录
g：后边搭配一个正则匹配模式（大小写不敏感）
例子：
得到返回记录集最多的10个慢SQL
mysqldumpslow -s r -t 10 /var/..../slow.log
得到访问次数最多的慢SQL
mysqldumpslow -s -c -t 10 /var/..../slow.log
得到找按照时间排序的前10条慢SQL里包含左连接的SQL
mysqldumpslow -s t -t 10 -g "left join" /var/....../slow.log
使用命令时，建议结合more，否则可能出现爆屏情况
mysqldumpslow -s t -t 10 /var....../slow.log | more
show profile分析
show profile用于分析SQL在mysql中执行时资源消耗详细情况，包括每个执行细节的资源开销信息
基础语法
#查看配置
show variables like '%profil%';
#profile默认是关闭的，需要打开profile配置
set profiling =on;
#设置统计SQL的数量
set profiling_history_size= 500;
#查看统计的SQL结果
show profiles;
#查看执行id为30的SQL语句资源详细情况，包含CPU和磁盘IO
show profile cpu ,block io for query 30;
#查看执行id为30的SQL语句的所有资源详细情况
show profile all for query 30;
#关闭profile统计
set profiling =off;
#恢复默认值15条
set profiling_history_size =15;
当show profile *** for query n得到的资源详情里的status列包含一下四种情形之一时，SQL语句问题比较大，必须优化
- convert heap to myisam：查询结果太大，内存不够用且忘磁盘上搬了
- create tmp table：创建临时表，创建临时表会拷贝数据和删除临时表操作
- copying to tmp table on disk：把内存中的临时表复制到磁盘，高危操作
- lock：锁表
show profile分析步骤
- 查看当前mysql版本是否支持show profile
- 开启show profile功能，并适当调大profiling_history_size数量
- 运行SQL语句
- 查看show profile统计结果，show profiles
- 通过show profile CPU, block io for query [queryId]诊断SQL，
- 根据诊断结果定位问题；（判断是否包含上面的四种情形，如果有必须优化）
全局查询日志分析
打开全局日志之后，mysql会记录所有的SQL操作
全局查询日志开启有两种方式
- my.conf里开启配置
//开启
general_log=1
//全局日志文件路径
general_log_file=/path/logfile
//输出格式
log_output=FILE - 编码开启配置
set global general_log=1;
set global log_output='TABLE';
开启之后，所写的SQL语句都会记录到mysql的general_log表中，可以通过select查看表数据 不要在生产环境开启全局日志配置
mysql性能分析总结
- 开启慢查询，捕获慢查询SQL
- explain分析慢查询SQL语句
- show profile 查询SQL在木mysql服务器里的执行细节和生命周期情况
- MYSQL数据服务器参数调优
- 全局查询日志优化
