SQL性能下降原因
原因
- 查询语句写的不好：存在不必要的连接和子查询等
- 索引失效
- 单值
- 复合索引（遵循最佳左前缀法则，最左侧索引不能少，中间索引不能断，索引上不做操作）
- 关联查询太多join（设计缺陷或者有不得已的需求）
- 服务器调优以及各个参数设置（缓冲，线程数等）
常见通用的Join查询
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>
机写
机器会先去确定表和条件，再去查询处理。 从FROM开始
1 FROM <left_table>
2 ON <join_condition>
3 <join_type> JOIN <right_table>
4 WHERE <where_condition>
5 GROUP BY <group_by_list>
6 HAVING <having_condition>
7 SELECT
8 DISTINCT <select_list>
9 ORDER BY <order_by_condition>
10 LIMIT <limit_number>
七种JOIN理论
七种join关系
七种JOIN的SQL编写
建表sql
sql.txt
建立tbl_dept和tbl_emp两个表
sql编写
1.内连接
mysql> select * from tbl_dept d inner join tbl_emp e on d.id = e.id;
+----+----------+--------+----+------+--------+
| id | deptName | locAdd | id | NAME | deptId |
+----+----------+--------+----+------+--------+
| 1 | RD | 11 | 1 | z3 | 1 |
| 2 | HR | 12 | 2 | z4 | 1 |
| 3 | MK | 13 | 3 | z5 | 1 |
| 4 | MIS | 14 | 4 | w5 | 2 |
| 5 | FD | 15 | 5 | w6 | 2 |
+----+----------+--------+----+------+--------+
5 rows in set (0.00 sec)
2.左连接
mysql> select * from tbl_dept d left join tbl_emp e on d.id = e.id;
+----+----------+--------+------+------+--------+
| id | deptName | locAdd | id | NAME | deptId |
+----+----------+--------+------+------+--------+
| 1 | RD | 11 | 1 | z3 | 1 |
| 2 | HR | 12 | 2 | z4 | 1 |
| 3 | MK | 13 | 3 | z5 | 1 |
| 4 | MIS | 14 | 4 | w5 | 2 |
| 5 | FD | 15 | 5 | w6 | 2 |
+----+----------+--------+------+------+--------+
5 rows in set (0.00 sec)
3.右连接
mysql> select * from tbl_dept d right join tbl_emp e on d.id = e.id;
+------+----------+--------+----+------+--------+
| id | deptName | locAdd | id | NAME | deptId |
+------+----------+--------+----+------+--------+
| 1 | RD | 11 | 1 | z3 | 1 |
| 2 | HR | 12 | 2 | z4 | 1 |
| 3 | MK | 13 | 3 | z5 | 1 |
| 4 | MIS | 14 | 4 | w5 | 2 |
| 5 | FD | 15 | 5 | w6 | 2 |
| NULL | NULL | NULL | 6 | s7 | 3 |
| NULL | NULL | NULL | 7 | s8 | 4 |
| NULL | NULL | NULL | 8 | s9 | 51 |
+------+----------+--------+----+------+--------+
8 rows in set (0.00 sec)
4.左连接2
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+------+----------+--------+
| 8 | s9 | 51 | NULL | NULL | NULL |
+----+------+--------+------+----------+--------+
1 row in set (0.00 sec)
5.右连接2
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.id is null;
+------+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+----+----------+--------+
1 row in set (0.00 sec)
6.全连接
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
-> union
-> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
+------+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+------+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| 8 | s9 | 51 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+------+----------+--------+
9 rows in set (0.00 sec)
7.全连接2
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null union select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
+------+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+------+----------+--------+
| 8 | s9 | 51 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+------+----------+--------+
2 rows in set (0.00 sec)
索引简介
索引是什么
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。这可以得到索引的本质：索引是数据结构。
使用索引的目的：在于提高查询效率，可以类比字典。按照字典的规则去找字可要比在一大堆字里盲目的去找效率高多了。
索引可以简单理解为“排好序的快速查找数据结构”。
- 详解
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。
下图就是一种可能的索引方式示例：
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。
对上述数据结构的解释：为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
结论：
- 数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
- 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
- 我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。
其他：
- 关于逻辑删除
为什么要使用逻辑删除？
- 防止数据误删除，不能找回数据；
- 这些数据还具有一定的商业价值，比如用户的注册信息；
- 虽然这些数据可以删除，但是这些数据还有关联数据，这些关联数据不能删除
逻辑删除应用场景？
回收站，历史记录等
逻辑删除的实现方式？
逻辑删除可能有几种实现方式，第一种是在表中加一个字段deleted字段，然后在删除时标记，所有查询时判断。另一种是，直接将删除的内容放到另外的位置，原始数据直接删除。
DBA重建索引
大量删除数据后，应该至少要做这两个操作 1、对表进行收缩，减小表实际占用的数据块，其目的是扫描更少的块。 2、重建索引 2.1）也是减小数据块的数量。 2.2）对索引结构进行整理，保证树结构的平衡。
索引优劣势
优势
- 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。
- 不会再从头到尾逐个寻找，而是用索引进行优化查找（B+树）
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
劣势
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
- 虽然索引大大提高了查询速度，但同时却会降低更新表的速度。如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
- 索引只是提高效率的一个因素，如果你的MysQL有大数据量的表，就需要花时间研究建立最优秀的索引或优化查询。(比如根据用户的查询习惯来优化索引)
索引分类和建索引命令语句
MySQL索引分类
- 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
- 唯一索引：索引列的值必须唯一，但允许有空值。(银行卡号)
- 复合索引：即一个索引包含多个列。
基本语法
```sql 创建索引 [UNIQUE]确定是否是唯一索引 (columnName(length))确定字段，若字段数为1则是单值索引，若是多个则为符合索引 CREATE [UNIQUE] INDEX indexName ON mytable(columnName(length));
添加索引 ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnName(length));
//CREATE和ALTER创建索引的不同： 1、CREATE INDEX必须提供索引名，对于ALTER TABLE，将会自动创建，如果你不提供； 2、CREATE INDEX一个语句一次只能建立一个索引，ALTER TABLE可以在一个语句建立多个，如： ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);
3、只有ALTER TABLE 才能创建主键
删除 DROP INDEX [indexName] ON mytable;
查看 SHOW INDEX FROM tableName;
使用alter命令 - 有四种方式来添加数据表的索引 ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。 ALTER TABLE tbl name ADD UNIQUE index_name (column_list);：这条语句创建索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)。 ALTER TABLE tbl_name ADD INDEX index_name (column_list);：添加普通索引，索引值可出现多次。 ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);：该语句指定了索引为FULLTEXT，用于全文索引。
---
<a name="gsWmE"></a>
## 索引结构与检索原理
<a name="Oa7b7"></a>
### MySQL索引结构
1. **BTree索引（重点）**
1. Hash索引
1. full-text全文索引
1. R-Tree索引
BTree索引检索原理：<br />
<a name="ZP0Jn"></a>
#### 初始化介绍
上图是一颗B+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）。<br />如磁盘块1包含数据项17和35，包含指针P1、P2、P3。P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。<br />真实的数据存在于叶子节点**，**即3、5、9、10、13、15、28、29、36、60、75、79、90、99。<br />非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
<a name="gIb1u"></a>
#### 查找过程
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO。在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。<br />在实际应用场景中，3层的B+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
---
<a name="xmGze"></a>
## 哪些情况适合建索引
1. 主键自动建立唯一索引
1. 频繁作为查询条件的字段应该创建索引（银行账号，手机号 ）
1. 查询中与其它表关联的字段，外键关系建立索引
1. 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
1. Where条件里用不到的字段不创建索引
1. 单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
1. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
1. 查询中统计或者分组字段
---
<a name="wkZJ3"></a>
## 哪些情况不适合建索引
1. 表记录太少：没必要建
1. 经常增删改的表：建了之后会降低更新表的速度。频繁的curd会让mysql在保存数据之后还要更新索引
3. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
计算公式：<br />假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。<br />索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。
---
<a name="voZH8"></a>
# 性能分析
<a name="hN1nP"></a>
## MySQL Query Optimizer
Mysql中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（**他认为最优的数据检索方式，但不见得是DBA认为是最优的**,这部分最耗费时间）
详细过程：当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query 中的 Hint信息(如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint 或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。
<a name="BqvoE"></a>
## MySQL常见瓶颈
1. CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
1. IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
1. 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态
---
<a name="J1wgj"></a>
## explain关键字
<a name="Y5f7H"></a>
### 是什么
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL具体是如何处理你的SQL语句的。从而可以分析的查询语句或是表结构的性能瓶颈。
<a name="ByEBx"></a>
### 能干嘛
1. 获取表的读取顺序：id
1. 数据读取操作的操作类型：type
1. 哪些索引可以使用：possible_keys
1. 哪些索引被实际使用：key
1. 表之间的引用：ref
1. 每张表有多少行被优化器查询：rows
<a name="fyKfr"></a>
### 怎么用
1. explain + sql语句，获取信息
2. 由获取的信息，分析sql执行的具体过程
1. 各个字段
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---
<a name="pqMsw"></a>
### 字段解析
<a name="wm5wo"></a>
#### id
1. select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
1. id有三种情况：
- id相同，执行顺序由上至下
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- 有相同id，有不同id。 执行顺序是从大到小执行，遇到相同id按顺序执行
3. 举例
- id相同，由上至下顺序执行
- id不同，按id从大至小执行
- 存在相同id，也存在不同id
---
<a name="VlCOK"></a>
#### select_type
数据查询的类型，主要是用于区别：普通查询、联合查询、子查询等的复杂查询。
1. SIMPLE：简单的SELECT查询，查询中不包含子查询或者UNION 。
1. PRIMARY：查询中如果包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
1. SUBQUERY：在SELECT或者WHERE子句中包含了子查询。
1. DERIVED：**在FROM子句中包含的子查询被标记为DERIVED(衍生)**，MySQL会递归执行这些子查询，把结果放在**临时表**中。当另一查询使用到临时表时，其table字段会标记为<derivedX>.
1. UNION：如果第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。
1. UNION RESULT：从UNION表获取结果的SELECT。
---
<a name="XyKhc"></a>
#### table
显示这一行的数据是关于哪张表的。
---
<a name="ZJRBL"></a>
#### type
type显示的是访问类型，是较为重要的一个指标。
1. 类型介绍
结果值从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL。<br />但是这个精度过于详细，可以简化为下面的精度：system>const>eq_ref>ref>range>index>ALL<br />在实际使用中也不必要达到最高级，**一般来说，得保证查询至少达到range级别，最好能达到ref**。
2. 详细说明
- system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
- const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
- eq_ref：唯一性索引扫描，对于每个索引键，**表中只有一条记录与之匹配。**常见于主键或唯一索引扫描。此时rows==1。使用场景：选择本地图片。
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
- range：只检索给定范围的行,使用一个索引来选择行。
- 这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
- index：Full Index Scan，index与ALL区别为：index类型只遍历索引树，这通常比ALL快。因为索引文件通常比数据文件小（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）。
- **all**：Full Table Scan，将遍历全表以找到匹配的行。最普通的type。
**备注：一般来说，得保证访问type至少达到range级别，最好能达到ref。**
工作场景：当一条sql查询慢，使用explain查询后发现其type是all，说明是全表扫描。这就要把这条写的不怎么样的sql优化，优化后type为range，这就加快了mysql的查询速度。
---
<a name="BS2hD"></a>
#### possible_keys和key
二者类比请客吃饭中是应到多少人和实到多少人的关系
1. **possible_keys**：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，**但不一定被查询实际使用。**
1. **key**
- 实际使用的索引。如果为NULL，则没有使用索引
- 查询中若使用了覆盖索引（查的字段和之前建立的索引刚好吻合），则该索引仅出现在key列表中
---
<a name="mkNU3"></a>
#### key_len
1. 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
1. key_len显示的值为索引字段的最大可能长度，**并非实际使用长度**，即key_len是根据表定义计算而得，不是通过表内检索出的
---
<a name="dYWL1"></a>
#### ref
- 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
- 由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ‘ac’。
- 查询中与其它表关联的字段，外键关系建立索引。
---
<a name="BbNha"></a>
#### rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。<br />
- 从之前的640行优化为现在的142行
---
<a name="icqfO"></a>
#### Extra
包含不适合在其他列中显示但十分重要的额外信息。
1. **Using filesort**
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件排序"<br />
2. **Using temporary**
使了用临时表保存中间结果，MysQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。<br />
3. **Using index**
表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！<br />如果同时出现using where，表明索引被用来执行索引键值的查找；<br />如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。<br /><br />如果同时出现using where，表明索引被用来执行索引键值的查找；<br /><br />如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
4. **Using where**
表明使用了where过滤。
5. **Using join buffer**
使用了连接缓存。
6. **impossible where**
where子句的值总是false，不能用来获取任何元组。<br />
7. **select tables optimized away**
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
8. **distinct**
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
---
<a name="bKYQ7"></a>
### 索引覆盖
**在Extra---Using index中涉及**
1. 覆盖索引（Covering Index）,一说为索引覆盖。
1. **理解方式**：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
1. 注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
<a name="nJJie"></a>
### 热身Case
1. 给出sql语句并分析
2. 具体过程：执行顺序是id从大到小，若相等则按顺序。那么为什么有这个执行顺序呢？换句话说id是怎么得到的？**这就归因于mysql的机读顺序。**
- 第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name… 】
- 第二行（执行顺序2）：id为3，是整个查询中的一个子查询，并不和id=4和id=2是相同级别。或者说是第二个查询的一部分，因查询包含在from中，所以为derived。【select id,name from t1 where other_column=’’】
- 第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】
- 第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】
- 第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】
---
<a name="xTpq6"></a>
# 索引优化
<a name="Bdx75"></a>
## 索引分析
<a name="u0ecx"></a>
### 单表优化
<a name="BF2hA"></a>
#### 建表sql
创建article表，并插入数据
```sql
CREATE TABLE IF NOT EXISTS article(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
author_id INT(10) UNSIGNED NOT NULL,
category_id INT(10) UNSIGNED NOT NULL,
views INT(10) UNSIGNED NOT NULL,
comments INT(10) UNSIGNED NOT NULL,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL
);
INSERT INTO article(author_id,category_id,views,comments,title,content)
VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');
案例
查询category_id为1且comments 大于1的情况下，views最多的article_id。
- 对应的sql语句如下：
mysql> SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
- 对应的sql语句如下：
explain进行sql语句分析
mysql> explain select id as article_id from article where category_id = 1 and comments > 1 order by views desc limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: article partitions: NULL type: ALL //出现了全表扫描 possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 filtered: 33.33 Extra: Using where; Using filesort //出现了文件内排序 1 row in set, 1 warning (0.00 sec)由于出现全表扫描和文件内排序，所以必须进行sql优化——添加索引。
ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );分析优化后的结果
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: article partitions: NULL type: range //由之前的all优化为range possible_keys: idx_article_ccv key: idx_article_ccv key_len: 8 ref: NULL rows: 1 filtered: 100.00 Extra: Using index condition; Using filesort //依然存在文件内排序 1 row in set, 1 warning (0.00 sec)- type由all转为type：之前未建索引导致全表扫描，建立索引： idx_article_ccv ( ‘category_id , ‘comments’, ‘views’ ); 之后在查找此三列时就有了顺序。
- 按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments,如果遇到相同的comments 则再排序views。
- 当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(所谓range)，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。（views索引失效）
- type由all转为type：之前未建索引导致全表扫描，建立索引： idx_article_ccv ( ‘category_id , ‘comments’, ‘views’ ); 之后在查找此三列时就有了顺序。
- 虽然解决了全表扫描，但是还存在着文件内排序： Using filesort，因为comment之后的索引失效了。所以必须再次继续进行sql优化。
- 删除之前的索引，再进行下面优化 ```sql drop index idx_article_ccv on article;
create index idx_article_cv on article(category_id,views); //只加id和views两个索引列
- 优化后BTree先排序category_id,再排序views，这样category_id和views索引都用上了
- explain分析查询后的结果：type=ref；ref=const；rows=2；优化了文件内排序
<br />**回表**<br />**注：解释为什么两字段索引在解决view文件内排序的同时又避免了comment字段的排序？**
- 重点是using where ：表示优化器需要通过索引回表查询数据。categrory_id通过左前缀索引进行查询，中间夹断了的comment>1,不走索引，而是回表查，回表查完了，排序再回来走view的索引，所以不会出现ut和uf。**这样查询和排序都用到了索引，解决了之前在order by中的文件排序问题**
---
<a name="lyx6E"></a>
### 两表优化
<a name="FHKlP"></a>
#### 建表sql
[sql.txt](https://www.yuque.com/attachments/yuque/0/2021/txt/22137958/1635565901100-efc2b5c5-62f5-4fc6-a1f2-e45b566dd729.txt?_lake_card=%7B%22src%22%3A%22https%3A%2F%2Fwww.yuque.com%2Fattachments%2Fyuque%2F0%2F2021%2Ftxt%2F22137958%2F1635565901100-efc2b5c5-62f5-4fc6-a1f2-e45b566dd729.txt%22%2C%22name%22%3A%22sql.txt%22%2C%22size%22%3A2526%2C%22type%22%3A%22text%2Fplain%22%2C%22ext%22%3A%22txt%22%2C%22status%22%3A%22done%22%2C%22taskId%22%3A%22u6b002d06-616d-4c97-9f47-81b7f5751c1%22%2C%22taskType%22%3A%22upload%22%2C%22id%22%3A%22uf3f7a927%22%2C%22card%22%3A%22file%22%7D)
<a name="VpxoM"></a>
#### 案例
主外键索引优化
1. 左连接查询，在没有建立索引时，用explain可以看到type皆为all，可能查询的行数皆为20。
2. 建立索引：alter table book add index y（card）; 优化之后用explain查看发现type从all转为ref，说明用到了索引y，且book可能查询到的行数从20变为了1。
1. 优化比较明显，这是由于左连接的特性决定的：LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以**右边是我们的关键点，一定需要在右表建立索引**。
1.  
1. 从上面图例可以看出，左连接时，左表信息一定都有，重点在于匹配右表中的信息，所以索引要加在右侧表息
3. 下面进行验证：若在左连接中，索引建立在了左表上，会出现说明优化效果？
1. 从下面分析看出，sql并没有优化。
4. 同理，在进行右连接时，为了优化搜索，索引应该建立在左表上。
<a name="UiLDM"></a>
#### 总结
索引两表优化，左连接右表建索引，右连接左表建索引。
---
<a name="ZIiMO"></a>
### 三表优化
<a name="yrjMy"></a>
#### 建表sql
[sql.txt](https://www.yuque.com/attachments/yuque/0/2021/txt/22137958/1635566978366-8c92510e-0008-4d1a-b675-8e8423ab1610.txt?_lake_card=%7B%22src%22%3A%22https%3A%2F%2Fwww.yuque.com%2Fattachments%2Fyuque%2F0%2F2021%2Ftxt%2F22137958%2F1635566978366-8c92510e-0008-4d1a-b675-8e8423ab1610.txt%22%2C%22name%22%3A%22sql.txt%22%2C%22size%22%3A1263%2C%22type%22%3A%22text%2Fplain%22%2C%22ext%22%3A%22txt%22%2C%22status%22%3A%22done%22%2C%22taskId%22%3A%22u07ef7a61-bb5e-4b09-aa4e-8ad848e29ee%22%2C%22taskType%22%3A%22upload%22%2C%22id%22%3A%22u039e68a0%22%2C%22card%22%3A%22file%22%7D) 新建phone表并插入数据
<a name="tfVSm"></a>
#### 案例
1. 先移除前例中在class表和book表中建立的索引
1. 在book表和phone表中建立索引
```sql
alter table `phone` add index z(`card`);
alter table `book` add index y(`card`);
- 建立索引后，用explain分析sql语句。发现起到了优化效果。这是由于在查询字段（book和phone）上加了索引。
结论
关于Join语句的优化
最佳情况是全值匹配
- 第三种sql查询就是全值匹配，前两种索引虽然没有用全，但是也算用到了索引
- 最佳左前缀法则 ：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过复合索引中间列。
- 头部索引不能少：建立的索引顺序是name-age-position，所以name的索引必须使用到，若name的索引未使用到，那么其后的age，position索引皆会失效
- 中间索引不能断：
- 头部索引有效，但是从ref为单个const，且key_len仍未74，说明第三个索引失效了。导致失效的原因是：第二个索引并没有使用上
- 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。
- 因为使用到了left函数计算了name的长度，导致索引失效
- 存储引擎不能使用索引中范围条件右边的列。
- 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *的使用。
- mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。
- is null, is not null 也无法使用索引。
- like以通配符开头（’%abc…’），mysql索引失效会变成全表扫描的操作。
- 当使用%july%，%july时都会导致索引失效；当使用july%时，索引不会失效
- 如何解决%july%导致索引失效？
- 建立新表插入数据: sql.txt 用于测试
- 用覆盖索引防止全表扫描 ```sql 未建立索引情况 EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE ‘%aa%’; //type = all
EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE ‘%aa%’; //type = all EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE ‘%aa%’; //type = all EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE ‘%aa%’; //type = all
EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE ‘%aa%’; //type = all EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE ‘%aa%’; //type = all EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE ‘%aa%’; //type = all
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE ‘%aa%’; //type = all EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE ‘%aa%’; //type = all
建立索引：CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);
建立索引之后： EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE ‘%aa%’; //未失效，索引全覆盖 EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE ‘%aa%’; //未失效，name被索引覆盖 EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE ‘%aa%’; //未失效，age被索引覆盖
EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE ‘%aa%’; //id是自带主键索引，所以用到了索引？？？？ EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE ‘%aa%’; //未失效？？？？ EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE ‘%aa%’; //未失效？？？？？
破坏了覆盖索引： EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE ‘%aa%’; //索引失效 EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE ‘%aa%’; //索引失效 EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE ‘%aa%’; //索引失效
//此处是覆盖索引，要与最左前缀原则区别。 //最左前缀原则针对的是where后面的内容。以上的sql语句，where后面的查询字段已固定为name。 //覆盖索引针对的是select之后的字段，只要索引范围大于等于select之后的查询字段，那么可以覆盖
9. 字符串不加单引号索引失效。
<br />
- 上述两条sql都会查出结果且相同，这是因为mysql会进行隐式的类型转换，这就违反了第三条：在mysql上使用了类型转换。这样就导致了索引的失效
10. 少用or，用它来连接时会索引失效。
<a name="yMt9K"></a>
### 总结
<a name="Eyn24"></a>
### 面试题讲解
<a name="Cpbyu"></a>
#### 建表sql
[sql.txt](https://www.yuque.com/attachments/yuque/0/2021/txt/22137958/1635662607367-3bcd48fe-e7c3-450f-a945-0097108c942f.txt?_lake_card=%7B%22src%22%3A%22https%3A%2F%2Fwww.yuque.com%2Fattachments%2Fyuque%2F0%2F2021%2Ftxt%2F22137958%2F1635662607367-3bcd48fe-e7c3-450f-a945-0097108c942f.txt%22%2C%22name%22%3A%22sql.txt%22%2C%22size%22%3A578%2C%22type%22%3A%22text%2Fplain%22%2C%22ext%22%3A%22txt%22%2C%22status%22%3A%22done%22%2C%22taskId%22%3A%22u04f706dd-91a0-4a59-bdb4-136b85ddd8a%22%2C%22taskType%22%3A%22upload%22%2C%22id%22%3A%22u4f7ac718%22%2C%22card%22%3A%22file%22%7D) 建立test03表，并插入数据，并建立索引
<a name="qmVY5"></a>
#### 关于左前缀法则和优化器调整
1. 按下列四种sql进行测试，结果是都用到了索引，且最后一组是全值匹配.
2. 按照where后字段为：1234和1243进行测试，结果也都用到了索引，且为全值匹配
- 第二条sql被优化器优化，调整了顺序
3. 按照4321顺序进行测试，也是全值匹配，四个索引字段都用上了
4. 常量索引，mysq底层的优化，顺序的调整，和之前不同的是：前面左匹配原则中where后条件有缺失，现在只是顺序不一样。顺序会调整，如果中间断了，那么后面的列将无法索引到。
- 下图中第一条sql：c1，c2，c3聚齐，只是顺序不同，mysql会调整其顺序使sql用到索引
- 第二条只存在：c1，c3，c4，**缺失**了c2，索引查询仅仅用到了第一个字段
5. 之前的左优先原则，当建立的是针对name的索引，sql中把name字段放在了后面，explain分析后仍使用到了索引。这就是因为mysql的优化器 ：optimizer
6. 查询条件中存在范围，则索引的使用终止到范围条件
1. 下面两条sql是：第一个使用3个索引字段，第二个使用了四个
<a name="e6a9z"></a>
#### 关于order by
1. 普通情况
- c3用于排序没有用于查找，所以c3没有统计到explain中
- 第三条sql，当索引从c3处断裂，那么关于c4的排序就没有索引可以使用，这样就导致了文件排序
2. order by 内部没有排序
```sql
/* 10.用到了c1一个字段，c1用于查找，c3和c2两个字段索引失效，产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c3`, `c2`;
/*
13.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 没有产生Using filesort
因为之前c2这个字段已经确定了是'a2'了，这是一个常量，再去ORDER BY c3,c2 这时候c2已经不用排序了！
所以没有产生Using filesort 和(10)进行对比学习！
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c3, c2;
关于group by
GROUP BY分组之前必排序，索引优化几乎和ORDER BY一致，但是GROUP BY会有临时表的产生。
/* GROUP BY 表面上是叫做分组，但是分组之前必定排序。 */
/* 14.用到c1 c2 c3三个字段，c1用于查找，c2 c3用于排序，c4失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c2`,`c3`;
/* 15.用到c1这一个字段，c4失效，c2和c3排序失效产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c3`,`c2`;
一般性建议
- 对于单键索引，尽量选择针对当前query过滤性更好的索引。
- 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
- 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引。
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。
总结
优化总结口诀
全值匹配我最爱， 最左前缀要遵守；
带头大哥不能死， 中间兄弟不能断；
索引列上少计算， 范围之后全失效；
LIKE 百分写最右， 覆盖索引不写 *；
不等空值还有 OR， 索引影响要注意；
VAR 引号不可丢， SQL 优化有诀窍。
若有收获，就点个赞吧
0 人点赞
