[MySQL]查询的性能优化分析（一）：概述

问题：性能下降SQL语句执行慢

问题表现：执行时间长、等待时间长

分析方法

1. 观察语句长时间的执行情况
2. 开启慢查询日志，设置阈值，将慢SQL抓取出来
3. 通过Explain对慢SQL尽心分析
4. 通过Show profile查看慢SQL执行的资源消耗情况

分析可能原因

• 查询语句书写问题
• 索引失效：创建索引但未使用
• 关联查询，使用太多的JOIN子句或者缺少索引
• SQL服务器各个参数设置问题¹

简单处理方案

性能分析专项可以查看后续博客内容

SQL执行顺序

在书写查询语句时，首先要明确SQL语句在执行时的顺序，适当的将筛选条件进行合理安排，可以有效地减少数据检索范围，提高检索速度。
开发人员设计的SQL语句结构一般如下：

SELECT DISTINCT
    <select_list>
FROM
    <left_table>
<join_type> JOIN <right_table> ON <join_condition>
WHERE
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
ORDER BY
    <order_by_condition>
LIMIT <limit_number>

MySQL执行语句时的顺序如下：

1. FROM <left_table>
2. ON <join_condition>
3. <join_type > JOIN < right_table>
4. WHERE <where_condition>
5. ORDER BY <order_by_condition>
6. HAVING <having_condition>
7. SELECT
8. DISTINCT <select_list>
9. ORDER BY <order_by_condition>
10. LIMIT <limit_number>

使用Explain查看SQL语句执行计划可以有助于优化SQL语句设计：Explain的使用

索引的使用

索引的定义

• MySQL官方对索引的定义：索引（Index）是帮助MySQL高效获取数据的数据结构。
• 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这种数据结构通过实现高级查找算法引用（指向）数据，比如二叉树查找，而这种数据结构就是索引。
• 开发人员平时所说的索引，如果没有特别指明，都是指B-Tree（B树，多路搜索树，并不一定是二叉的）结构组织的索引。
• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

索引的作用

索引可以在数据表中对添加索引的列值进行排序，形成类比字典索引的作用，这样比如在某列中查询“mysql”这个词，就可以先定位到字母m，然后再在所有字母m开头的值中找到第二位为字母y的值，以此类推，不断缩小检索范围，使得检索效率提高。所以，索引也可以理解成“排好序的快速查找数据结构”。

索引的优势

• 提高数据检索的效率，降低数据库的IO成本
• 降低数据排序的成本，降低CPU的消耗

索引的劣势

• 索引占用空间：索引实际上是一张保存了主键与索引字段的表，该表指向实体表的记录
• 在对表数据进行INSERT/UPDATE/DELETE时，MySQL不仅要跟新数据，还要对索引文件中添加了索引列的字段因为更新造成的索引信息键值变化做出调整
• 索引只是提高效率的一个因素，如果存在大量数据的表，就需要花费大量时间研究建立最优化的索引，才能实现索引提高检索效率的作用

索引的分类

• 单值索引：一个索引只包含一个列，一个表可以有多个单值索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：一个索引包含多个列

基本语法

-- 创建
CREATE [UNIQUE] INDEX indexName ON tableName(column_list);
ALTER tableName ADD [UNIQUE] INDEX [indexName] ON (column_list);

-- 删除
DROP INDEX [indexName] ON tableName;

-- 查看
SHOW INDEX FROM tableName;

索引的使用

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段（如外键）应该建立索引
4. 相比于单值索引，更倾向于复合索引
5. 查询中排序的字段若与索引相符（字段顺序），可以有效提高排序速度
6. 查询中统计或者分组字段²
7. 频繁更新的字段不适合创建索引
8. where子句里用不到的字段不创建索引
9. 表记录太少（百万级以下）的数据表可以不创建
10. 数据重复且分布平均的表字段不创建索引（如性别字段），索引的选择性³越接近于1，索引的效率越高

SQL中的JOIN查询

图1 SQL JOIN连接

其中，MySQL中不支持“FULL OUTER JOIN”的语法，可以通过下面的代码实现这两种情况的效果：

SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key

SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL

---

1. 这部分原因产生的问题一般由运维经理或者DBA负责。 ↩︎

2. 分组前必然先排序 ↩︎

3. 索引的选择性是指索引列中不同值的数目与表中记录数的比值 ↩︎