MySQL 调优方法

优化思路

定位问题：硬件-》系统-》应用-》数据库-》架构（高可用，读写分离，分库分表）

绝大部分慢查询问题可通过索引优化；

其他优化方向：

MySQL参数、系统参数、硬件、架构

 

---

1、工具

explain、show profiles、show index from table_name;

Show profiles：

# 语法
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]

type:
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS

来自 <http://wingyumin.com/2016/02/02/MySQL%E7%9A%84SHOW-PROFILE%E8%AF%A6%E8%A7%A3/>

show index from table_name：返回索引列详解

Table 表名
Non_unique 索引唯一为0，否则为1
Key_name 索引名
Seq_in_index 索引中列的序列号
Column_name 列名称
Collation 列存储在索引中的方式（A:升序，NULL:无分类）
Cardinality 索引中唯一值数目的估计值
Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目；如果整列被编入索引，则为NULL
Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL
Null 索引的列中含有NULL。含有NULL则为YES；如果没有，则这里显示为空
Index_type  索引的类型（BTREE, FULLTEXT, HASH, RTREE）
Comment
Index_comment

 

analyze table table_name : 
统计索引分布信息，并将结果持久化存储；刷新Cardinality值（随机取样计算得出）

---

2、索引列选择

根据索引选择性（越高越好）：
SELECT count(DISTINCT(column1))/count(*) AS Selectivity FROM tableX;
SELECT count(DISTINCT(concat(column1, column2)))/count(*) AS Selectivity FROM tableX;


explain [extended|partitions] 详解（查询语句分析）
Id 执行编号；
select_type select类型；
SUBQUERY 子查询（不在from子句中）
DERIVED 子查询（在from子句中，生成临时表（派生表）存放结果）
UNION union中第二和随后的select
UNION RESULT 从union匿名临时表检索结果的select
Table 正在访问的表名；
Type 关联类型（访问类型）：
ALL 全表扫描
Index 全索引扫描（按索引次序扫描表；extra 中 using index 代表使用覆盖索引，只扫描索引的数据）
Range 范围扫描（有限制的索引扫描，始于索引某点返回匹配值域的行；between或where中带有>的查询）
Ref 索引访问（匹配某个单个值的行；使用非唯一性索引或唯一性索引的非唯一性前缀时发生；其变体 ref_or_null 表示需二次查找NULL条目）
eq_ref 索引查找（使用主键或唯一性查找时发生，与某个值比较返回最多一条记录）
Const，system （能优化转换为常量的查询）
Null （在优化阶段分解查询语句，执行阶段可能不再访问库表或索引）
Possible_keys 查询可以使用哪些索引；（哪个索引能有助于高效查询）
Key 查询使用哪个索引；（哪个索引可以最小化查询成本）
Key_len 索引里使用的字节数；
Ref 在Key列索引查找值所用的的列或常量；
Rows 为找到所需行估计要读的行数；
Filtered 针对表符合某个条件的记录数的百分比做一个悲观估算；（All、Index、Range、Index_merge访问方法）
Extra 额外信息
Using index 使用覆盖索引以避免访问表；
Using where 在存储引擎检索行后再进行过滤；
Using temporary 对查询结果排序时使用临时表；
Using filesort 对结果使用外部索引排序；
Range check for each record(index map: N) 没有好用的索引，新的索引将在联接的每一行上重新估算；


key_len 作用：计算索引使用了哪些列
（
 变长字段需要额外的2个字节，固定长度字段不需要额外的字节。而null都需要1个字节的额外空间
来自 <https://www.cnblogs.com/zhoujinyi/p/3784450.html>
）

---

3、索引类别说明

聚集（聚簇）索引：（数据存储方式）同一结构下存储着B-Tree索引和数据行

回表查询：通过二级索引定位到聚集索引，再通过聚集索引定位到记录
覆盖索引：能直接在索引树上获取所需的列数据，无需回表查询

多列索引/复合索引/联合索引：多个字段按照一定顺序组织的索引

普通索引：InnoDB中非聚集索引
唯一索引：在插入或修改时校验索引是否存在
主键索引：特殊的唯一索引，不能有空值
联合索引：将多列的值按申明时顺序拼接后构建索引
外键索引：为某个外键字段定义了一个外键约束条件 而定义的一个内部索引

来自 <https://blog.csdn.net/leyangjun/article/details/45869373>

全文索引：快速查询大文本中某些单词

前缀索引：
联合索引前缀（最左前缀规则）、like 前缀、字符串前缀
三星索引：满足三个条件的索引
• 取出所有的等值谓词的列 （WHERE COL=…） 作为索引开头的列；
• 将 ORDER BY 中的列加入到索引中；
• 将查询语句中剩余的列加入到索引中，将易变得列放到***以降低更新成本；

来自 <https://database.51cto.com/art/201906/597382.htm>

---

4、执行顺序

1. 先连接from后的数据源(若有join，则先执行on后条件，再连接数据源)。
2. 执行where条件
3. 执行group by
4.执行having
5.执行order by
6.输出结果。

参考：https://blog.csdn.net/mine_song/article/details/70185744