MySQL性能分析、优化/实战(超详细)

SQL性能下降的原因

执行时间长、等待时间长

1. 查询语句写的有问题
2. 使用函数、特殊运算符（* from 这种不太聪明的做法应该都清楚）导致索引失效
3. 关联查询太多join（设计缺陷或者不得已的需求）
4. 服务器调优及其各个参数设置（缓冲、线程数等）

索引的简介

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据结构，可以得到索引的本质：索引即数据结构。（简单理解）

优势：

1. 提高数据检索的效率，降低数据库的IO成本

2. 通过索引树对数据进行排序，降低数据排序的成本，降低CPU的消耗

劣势：

1. 实际上索引是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
2. 虽然索引大大提升查询速度，同时也会降低更新表的速度（insert、delete、update）
3. 时间成本，大量的表数据，就需要花费时间去研究最优秀的索引，大部分真实的案例都是删了建，建了删，以此往复…

为什么需要建索引？

1. 索引的目的在于提高查询效率，类比于查询字典。 如果要查’index’这个单词，我肯定需要先找到 ‘I’ 这个字母, 然后再往下找
   ‘N’，再去找剩下的， 以此类推… 如果没有索引，那么就需要从A…Z，如果我们想找到Java的单词？或者Oracle的单词？
   是不是再没有索引的情况下变得举步维艰。
2. 数据本身之外，数据库还维护着一个特定满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以再这些数据结构的基础上面實現高级的查找算法，这种数据结构就是索引

一般索引本身也很大，不可能全部存储再内存中，因此索引往往以索引文件的形式存储再磁盘上

索引的分类：
单值索引：

即一个索引 只包含单个列，一个表就可以有多个单列索引。（mysql推荐单个表五个索引以内，否则优化器会自行选择已有的且最优的两三个）

唯一索引：

索引列的值必须唯一，允许有空值

复合索引：

即一个索引包含多个列

基础语法：

-- [UNIQUE|FULLTEXT|SPATIAL] 什么类型的索引  唯一、全文、空间
-- index_name 索引名称
-- [index_type] 索引结构 二叉树、哈希、第三个不清楚 默认BTREE
-- tbl_name 表名称
-- index_col_name 列名称
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_type]

index_col_name:
col_name [(length)] [ASC | DESC]

-- [index_type] 二叉树、哈希、第三个不清楚
index_type:
USING {BTREE | HASH | RTREE}

-- 另一种新增索引的方式
-- column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。
alter table tbl_name add index index_name (column_list) ;

-- 删除索引
Drop index_col_name on tbl_name 

索引结构：
推荐博客：https://www.jianshu.com/p/1775b4ff123a

索引失效原则
推荐博客：https://blog.csdn.net/Yh_yh_new_Yh/article/details/115550552

哪些情况需要创建索引

1. 主键 自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. where条件里用不到的字段不创建索引
5. 单键/组合索引的选择问题。（在高并发下倾向创建组合索引，多个字段能够再一个索引中体现，降低IO消耗，节省的资源总量）为什么要有复合索引？
6. 查询中排序的字段，排序字段若通过索引去访问将大大提升排序速度 查询中统计或者分组

哪些情况不建议创建索引

1. 表记录很少
2. 频繁更新的字段不适合创建索引（因为不单单更新字段，索引也要进行更新）
3. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引

性能分析

Explain（查询执行计划）

使用Explain关键字可以模拟优化执行的SQL语句，从而知道mysql是如何处理你得sql语句的，分析你的查询语句或者表结构的性能瓶颈
语法：
Explain + SQL语句

能做什么？

1. 表的读取顺序
2. 数据读取操作的操作类型
3. 那些索引可以被使用了
4. 那些索引被实际使用了
5. 表之间的引用
6. 每张表有多少行被优化器查询

概要描述：

EXPLAIN SELECT id from tb_student as a LIMIT 1;

• id: 选择标识符

1. id相同时，执行顺序由上至下

2. 如果是子查询，id的序号会递增（最里层的级别最高），id值越大优先级越高，越先被执行

3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

• select_type: 表示查询的类型。

(1) SIMPLE (简单SELECT，不使用UNION或子查询等)

(2) PRIMARY (子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

(3) UNION (使用UNION关键字， UNION中的第二个或后面的SELECT语句)

(4) **DEPENDENT UNION ** (UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT (UNION的结果，union语句中第二个select开始后面所有select)

(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

(8) DERIVED (派生表的SELECT, FROM子句的子查询，他的结果会被放在临时表里)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

• table: 输出结果集的表
• partitions: 匹配的分区
• type: 表示表的连接类型

从左至右为，从差到最好
一般来说，最基础得保证达到range级别,最好能达到ref

• ALL: Full Table Scan , 全表扫描
• index：Full Index Scan, Index于ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比文件小。（也就是说虽然ALL和Index都是读全表，但是Index从索引中读取的，而ALL是从硬盘中读取的）
• range：只检索给定范围的行，使用索引来选择行，key列显示使用了那个索引，一般就是在你的where使用between、<、>、in 等查询，这种范围扫描索引比全表扫描要好一点
• ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问。它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描
• const：表示通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行，所以很快。如将主键置于where列表中，MySQL能将查询转换为一个常量
• system: 表中有一行数据，这是const得特列，平时不会出现，可以忽略不记

• possible_keys: 表示查询时，可能使用的索引

显示可能应用在这张表的索引，一个或者多个，（推测可以使用的索引）
查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用

• key: 表示实际使用的索引

实际使用的索引，如果为NULL，则没有使用索引
查询中若使用覆盖索引，则该索引仅出现在key列表中

• 覆盖索引（大白话就是查询时只查了索引数据）
  非聚集索引的B+树节点存储的是索引列和主键，假如想要拿到完整数据的话还得根据主键去主键索引树回表，这样性能不好，如果我们要查询得到的数据就是索引列和主键中的数据，就不要回表。这样只需要在一棵索引树上就能获取SQL所需的所有列数据无需回表的索引称为覆盖索引。由于覆盖索引可以减少树的搜索次数， 显著提升查询性能， 所以使用覆盖索引是一个常用的性能优化手段。

• key_len: 索引字段的长度

• 表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。再不损失精确性的情况下，长度越短越好。
• key_len显示的val为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

• ref: 列与索引的比较

• 如果是使用常熟的等值查询，这里会显示const。
• 如果是连接查询则是关联表的字段
• 如果使用表达式或者函数。列可能会发生隐式转换，可能会显示为func
• 显示索引的那一列被使用了（where、group by…），如果可能的话，最好是一个常数(const)，那些列或者常熟被用于查找索引列上的值
  

• rows: 每张表被优化器扫描出的行数(估算的行数)
• filtered: 按表条件过滤的行百分比
• Extra: 执行情况的描述和说明

• Using filesort： 说明mysql对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”
• using index：出现这个说明mysql使用了覆盖索引，避免访问了表的数据行，效率不错！
• using where：这说明服务器在存储引擎收到行后将进行过滤。有些where中的条件会有属于索引的列，当它读取使用索引的时候，就会被过滤，所以会出现有些where语句并没有在extra列中出现using where这么一个说明。
• using temporary：这意味着mysql对查询结果进行排序的时候使用了一张临时表。（临时表很伤性能。如何不使用到临时表？切记分组的字段和建立索引的字段相符）

索引优化

• 单表优化

主要根据EXPLAIN关键字 执行sql ，再然后去分析
这里age非索引
排序就出现了最坏的情况 type== ALL,
Extra = Using filesort 添加索引 提升至type = ref或者以上

EXPLAIN SELECT id, login_name from hc_person where login_name = '张三' and age> 18 order by age desc limit 1

• 多表优化

多表优化建议：

• 尽可能减少join语句中的NestedLoop的循环次数；“永远用小表的结果集去驱动大表的结果集”
• 优先优化NestedLoop的内层循环；
• 保证Join语句中被驱动表的Join条件字段已经被索引；
• 当无法保证被驱动表的Join条件字段被索引且内存资源充足的情况下，建议使用JoinBuffer的设置

• order by优化

  • order by 字句中，尽量使用Index方式排序，避免使用FileSort方式排序；
  • 尽可能在索引列上面完成排序操作，遵照索引键的最左前缀
  • 如果不在索引列上面，FileSort有两种算法:(File Sort、Index Sort),MySQL就要启动双路排序和单路排序
  • 优化策略
    单路排序是4.6版本后续出的，性能是优于双路的，但是也存在问题。如果sort_buffer的大小不够，任然会多路，这个时候我们就需要：
    增大sort_buffer_size参数的配置
    增大max_length_for_sort_data参数的配置

• group by优化

  • group by 与order by 基本类似，同上
  • group by 实质是先排序后在分组，遵照索引键的最佳前缀
  • 当无法使用索引列，增大sort_buffer_size、max_length_for_sort_data参数的配置
  • where 高于 having，能写在where条件后面就尽量不要写在having后面

索引失效、索引使用建议

1. 尽量写等值查询（where name = ‘张三’…）
2. 最佳左前缀法则（如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引的列）
3. 不要再索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
4. 存储引擎不能使用索引中范围条件右边的列
5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列已知）），减少select *
6. mysql在使用不等于（!= 或者 < >）的时候无法使用索引会导致全表扫描
7. is null ， is not null 也无法使用索引
8. like以通配符开头（‘%议员%’） mysql 索引失效会变成全表扫描/扫描索引(type=ALL/Index)的操作；建议使用（‘议员%’），索引不会失效噢（type=range）
9. 字符串不加单引号索引失效（类型转换导致索引失效）
10. 少用or，用它来连接时会索引失效
11. 子查询的时候，永远小表驱动大表，即小的数据集驱动大的数据集（连接过程申请、释放，假设以小表为主只需要五次。大表为主则需要一千次。）
12. 外层查询表小于子查询表，则用exists，外层查询表大于子查询表，则用in，如果外层和子查询表差不多，则爱用哪个用哪个。（in && exists 的解释）

口诀：
VAR引号不可丢，SQL优化有诀窍。
不等空值还有OR，索引影响要注意；
LIKE百分写最右，覆盖索引不写*；
索引列上少计算，范围之后全失效；
带头大哥不能死，中间兄弟不能断；
全值匹配我最爱，最左前缀要遵守；

实战分析

1、观察，至少跑一天，看看生产的慢sql的情况。
2、开启慢sql日志，设置阙值，比如超过5秒的就是慢sql，并抓取

• mysql默认情况下是不开启的状态，需要手动设置这个参数
  • 当然如果不需要进行调优的话，一般不建议启动这个参数，因为开启日志对性能会有影响。慢sql支持将sql写入文件
  • 默认 SHOW VARIABLES LIKE ‘%slow_query_log%’；
  • 开启 set global show_query_log = 1;(重启失效)
  • 查看多少秒算慢的sql SHOW VARIABLES LIKE ‘long_query_time%’;
  • 设置算慢的阙值时间 set global long_query_time = 3;(需要重新连接mysql或者建立一个新的会话才能看到修改的阙值)
  • MySQL数据库慢查询日志存储路径 log-slow-queries ：旧版（5.6以下版本）slow-query-log-file：新版（5.6及以上版本）
  • 分析

3、explain + 慢sql分析
4、show profile 查询sql在Msql服务器里面的执行细节和生命周期情况
5、运营经理 or DBA，进行sql数据库服务器的参数调优。

经验建议：

## where 后跟条件会导致遍历索引数、并且按照范围查询，数据量大的时候效率极低
SELECT
	count( * ) 
FROM
	tb_student
where age > 18

## 建议如果需要待条件查询count时，在count里面写if，虽然也是遍历的索引树，但是使用到了索引覆盖
SELECT
	count( IF ( age > 18 ), 1, NULL ) ) 
FROM
	tb_student

## 如果某些需求需要联查多表的数据时，尽量已主表为主，只查一个表的数据，少内联。
## 如果因为某一个表导致查询很慢，建议分开查询。
## 慢sql多使用EXPLAIN关键字对其进行分析。
## 小细节要注意好，不查不必要的字段
## 联查字段尽量都建立索引
## 等值查询/范围查询，

其他细节：

1、不要使用 count(列名) 或 count(常量) 来替代 count(*)，count(*) 是
SQL92定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

说明：count(*) 会统计值为 NULL 的行，而count(列名) 不会统计此列为 NULL 值的行。

2、count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1 , col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。

3、当某一列的值全是 NULL 时，count(col) 的返回结果为 0；但 sum(col) 的返回结果为 NULL，因此使用sum() 时需注意 NPE 问题。

可以使用如下方式来避免 sum 的 NPE 问题：SELECT IFNULL(SUM(column) , 0) FROM table;

4、使用 ISNULL() 来判断是否为 NULL 值