MySQL常见瓶颈
1、cpu
SQL中对大量数据进行比较、关联、排序、分组
2、IO
a、实例内存满足不了缓存数据或排序等需要，导致产生大量物理 IO。
b、查询执行效率低，扫描过多数据行。
3、锁
a、不适宜的锁的设置，导致线程阻塞，性能下降。
b、死锁，线程之间交叉调用资源，导致死锁，程序卡住。
4、服务器硬件的性能瓶颈
top,free, iostat和vmstat来查看系统的性能状态
Explain(执行计划)
概念
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。
能干嘛
- 表的读取顺序
- 哪些索引可以使用
- 数据读取操作的操作类型
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
使用方法
Explain + SQL语句
explain select * from tb1_emp;
字段解释
id
select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
示例：
三种情况：
- id相同，执行顺序由上至下-----id相同，执行顺序由上至下 此例中 先执行where 后的第一条语句 t1.id = t2.id 通过 t1.id 关联 t2.id 。 而 t2.id 的结果建立在 t2.id=t3.id 的基础之上。
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行----- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id相同不同，同时存在----- id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行 衍生表 = derived2 --> derived + 2 （2 表示由 id =2 的查询衍生出来的表。type 肯定是 all ，因为衍生的表没有建立索引）
select_type
查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
常用类型：
SIMPLE
简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY
查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
DERIVED
在FROM列表中包含的子查询被标记为DERIVED(衍生).MySQL会递归执行这些子查询, 把结果放在临时表里。 -----DERIVED 既查询通过子查询查出来的 临时表
SUBQUERY
在SELECT或WHERE列表中包含了子查询
DEPENDENT SUBQUERY
在SELECT或WHERE列表中包含了子查询,子查询基于外层-----dependent subquery 与 subquery 的区别依赖子查询 ： 子查询结果为多值子查询：查询结果为单值
UNCACHEABLE SUBQUREY
无法被缓存的子查询
UNION
若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED-----UNION RESULT 两个语句执行完后的结果
UNION RESULT
从UNION表获取结果的SELECT从UNION表获取结果的SELECT
table
显示这一行的数据是关于哪张表的
type
显示查询使用了何种类型，是较为重要的一个指标，结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > ALL
从最好到最差依次是(通常记下面这个)：
system>const>eq_ref>ref>range>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
possible_keys
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key
实际使用的索引。如果为NULL，则没有使用索引。
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。能够帮你检查是否充分的利用上了索引。在不损失精度的情况下，越短越好。
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
rows
rows列显示MySQL认为它执行查询时必须检查的行数。
Extra
包含不适合在其他列中显示但十分重要的额外信息。
索引优化
索引失效
避免索引失效则是查询优化
1、全值匹配我最爱
当建立复合索引时。比如a、b、c三个字段，最好查询的时候带上a、b、c，并且a一定要有，带头大哥不能死，否则全表扫描。(结合下面的最佳左前缀法则)
2、最佳左前缀法则
如果索引了多列(复合索引或叫联合索引)，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。-----中间兄弟不能断
3、不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。-----索引列上无计算
4、存储引擎不能使用索引中范围条件右边的列-----若有索引则能使用到索引，范围条件右边的索引会失效(范围条件右边与范围条件使用的同一个组合索引，右边的才会失效。若是不同索引则不会失效)-----范围之后全失效
对于上例，age以及pos的索引都不能使用。
5、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *。
6、mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
7、is null、is not null 也无法使用索引
8、like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作-----%写在右边可以避免全表扫描。或者使用覆盖索引(比如name、age建了索引，只查询这两列的值出来，并且顺序也要一样)
9、字符串不加单引号索引失效 ----- 底层进行转换使索引失效，使用了函数造成索引失效(隐式转换)
10、少用or,用它来连接时会索引失效
案例总结：
一般性建议
1、对于单键索引，尽量选择针对当前query过滤性更好的索引
2、在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠左越好。(避免索引过滤性好的索引失效)
3、在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
查询优化
永远小表驱动大表
即小的数据集驱动大的数据集。
order by关键字排序优化
1、ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序
MySQL支持二种方式的排序，FileSort和Index，Index效率高.它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
ORDER BY满足两情况，会使用Index方式排序:
- a、ORDER BY 语句使用索引最左前列
- b、使用Where子句与Order BY子句条件列组合满足索引最左前列
注：where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效。
2、尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
group by关键字排序优化
1、group by实质是先排序后进行分组，遵照索引建的最佳左前缀
2、当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
3、 where高于having，能写在where限定的条件就不要去having限定了。
慢查询日志
概念
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。 具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。
使用方式
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件
默认：
SHOW VARIABLES LIKE '%slow_query_log%';
开启：
set global slow_query_log=1;
如何判断是慢sql
这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，命令：SHOW VARIABLES LIKE 'long_query_time%';可以使用命令修改，也可以在my.cnf参数里面修改。假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。
查询多少条慢sql
show global status like '%Slow_queries%';
mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
mysqldumpslow --help