数据库索引优化

一、 SQL性能下降原因

性能下降SQL慢
执行时间长
等待时间长

1. 查询语句写的烂
2. 索引失效
3. 关联查询太多join（设计缺陷或不得已需求）
4. 服务器调优以及各个参数设置（缓冲，线程数等）

二、七种Jion

三、 索引

1. 创建索引

# 普通索引
ALTER TABLE table_name ADD INDEX index_name (column_list)

# 唯一索引
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_lsit)

# 主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column)

2. 查看索引

SHOW INDEX FROM table_name

3. 删除索引

DROP INDEX [index_name] ON table_name

# 或者
ALTER TABLE table_name DROP INDEX index_name

# eg:删除主键
ALTER TABLE table_name DROP PRIMARY KEY

四、哪些情况需要建立索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该建立索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 单值索引与组合索引的选择：高并发下倾向于组合索引
5. 查询中排序的字段，若通过索引去访问将大大提高排序速度
6. 查询中统计或分组的段

五、哪些情况不要建立索引

1. 表记录过少
2. 经常增删改的表
   why: 为了提高查询速度，同时却会降低更新表的速度。因为更新表时，不仅要保存数据，还要更新索引文件。
3. 数据重复且分布均匀的表字段，因此应该只为最经常查询和排序的数据列建立索引
4. 频繁更新的字段不适合创建索引
   更新索引，加重了IO负担
5. where条件里用不到的字段不创建索引

六、增加索引带来的问题

1. 创建索引和维护索引需要耗费时间，并且随着数据量的增加所耗费的时间也会增加
2. 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
3. 当对表中的数据进行增删改是，索引也需要动态的维护，这样就降低了数据的维护速度。

七、性能分析

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的，从而进行优化

1. 具体能干嘛

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

2. 具体怎么用

EXPLAIN + SQL语句

返回的信息：

八、 EXPLAIN 各个名词的解释

1. id

select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

四种情况

• id 相同： 执行顺序由上至下
• id 不同：id 值越大，优先级越高，越先被执行
• id 有相同也有不同，首先执行 id 值大的语句，id 值相同的，由上至下顺序执行
• null 表示用来合并结果集，在SQL中使用 UNION 关键字合并结果集就会出现它

2. select_type

指定所使用的 SELECT 查询类型，主要用于区别普通查询、联合查询、子查询等复杂查询

2.1 SIMPLE

简单的 SELECT 查询，查询不包含子查询或者UNION

2.2 PRIMARY

查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）

2.3 SUBQUERY

在 SELECT 或 WHERE 中包含了子查询中的首个 SELECT (如果有多个子查询存在)

2.4 DEPENDENT SUBQUERY

子查询中首个 SELECT，但依赖于外层的表（如果有多个子查询存在）

重点解释
子查询的查询方式依赖于外面的查询结果.用这个例子就是,先进行子查询外部的查询,得到一个结果集,.然后这个结果的每一行在跟select子查询的结果集进行匹配,也就是说,外部结果集的每一行都要关联内部结果集一次

2.5 DERIVED

在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表里。

2.6 UNION

若第二个 SELECT 出现在 UNION 之后，则会被标记为UNION；若UNION包含在 FROM 子句的子查询中，外层的 SELECT 将会被标记为 DERIVED

2.7 UNIOIN RESULT

UNION操作的结果，id值通常为NULL

3.table

显示这一行的数据是关于哪一张表的

4.type

表示访问类型，通俗解释就是MySQL查找数据列的方式。

下边从最优到最差的顺序分别介绍

4.1 system

表中只有一条数据. 这个类型是特殊的 const 类型

4.2 const

表示通过索引一次就找到了数据列，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以查询速度很快。
比如将主键当做 WHERE 条件去查询，MySQL可以将这个查询转换为一个常量。

4.3 eq_ref

唯一性索引扫描，对于每一个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

4.4 ref

针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询，返回匹配某个单独值的所有行。
本质上也是一种索引访问，它返回所有匹配某个单独值的行，但是它可能会找到多个符合条件的 行，所以它应该属于查找和扫描的混合体。

4.5 range

只检索给定范围的行，使用一个索引来选择行。 key 列显示使用了哪个索引。
一般是在 WHERE 语句中出现了 between < > in 等的查询。
这种范围扫描索引比全表扫描索引要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

4.6 index

表示全索引扫描，index 与 ALL 的区别在于 index 类型只遍历索引树。

4.7 ALL

全表扫描，没有用到任何的index，效率最差。

总结

从最好到最差依次是：
system > const > eq_ref > ref > range > index > ALL

一般来说，得保证查询至少达到 range 级别，最好能达到 ref

5. possible_keys

显示可能应用在这张表中的索引，一个或多个。
查询涉及的字段若存在索引，则改索引将被列出，但不一定被查询实际用到

6. key

实际中用到的索引，如果为 NULL 则表示没有用到索引
如查询中用到了覆盖索引，则该索引和查询的 SELECT 字段重叠

覆盖索引：在本例中：我在 addr 和 email 上建立聚合索引，在查询时也查询的是 addr 和 email 这两个字段，所以刚好和索引匹配了。
当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在 Extra 列可以看到 Using index 的信息

7. key_len

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

8. ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。
常见的值有 const, func, NULL, 具体字段名。当 key 列为 NULL ，即不使用索引时 。如果值是 func，则使用的值是某个函数的结果

9. rows

根据表统计信息及索引使用情况，大致估算出找到所需的记录所需要读取的行数。
简单且重要，数值越大越不好，说明没有用好索引

10. Extra

该列包含 MySQL 查询的详细信息。

10.1 Using filesort (出现这个表示效果不好)

说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL 中无法利用索引完成的排序操作称为“文件排序”

10.2 Using temporary (出现这个表示效果不好)

使用了临时表保存中间结果，MySQL 在对查询结果进行排序是使用临时表。
常见于排序 ORDER BY 和分组查询 GROUP BY

10.3 Using index (出现这个表示效果好)

表示相应的 SELECT 操作中使用了覆盖索引，避免访问了表的数据行，效率不错。
如果同时出现 Using where 表示索引被用来执行索引键值的查找
如果没有出现 Using where 表示索引用来读取数据而非执行查找动作。

10.4 Using where

表示使用了 WHERE 过滤

10.5 Using join buffer

使用了连接缓存

10.6 Impossible where

WHERE 子句的值总是 False ， 不能用来获取任何数据列。

九、避免索引失效

1. 全值匹配

对于联合索引，在 WHERE 子查询是尽量使用所有索引列来过滤查询

2. 最左前缀原则

联合索引下，查询从索引的最左前列开始并且不能跳过索引中间的列

3. 不在索引列上做任何操作

不要在索引列上进行 计算、函数、（自动或手动）类型转换，否则会导致索引失效而转向全表扫描。

4. WHERE 条件中不使用 = 进行索引列，那么该索引会失效，同时其右边的索引也失效

解释：
当我们四个索引都使用时， key_len 为 143，在使用前两个索引时， key_len 值为 70，前三个索引 key_len 为106，单独一个索引 key_len 值为35.
此时，我们在 WHERE 子查询过滤条件中使用了范围条件，可以看到不使用 = 的索引列以及其右边的索引也都失效了。

疑惑
当我在第一个索引列使用范围查询时，所有的索引却都没有失效。

5. 尽量使用覆盖索引，减少 SELECT * 的使用

6. 使用不等于(!= ) 会导致索引失效

7. is null, is not null 也会导致索引失效

8. LIKE 以通配符开头 ('%字符串') 会导致索引失效

解决办法
可以使用以通配符结尾来检索 '字符串%'

9. 字符串不加引号会导致索引失效

解释
第 3 条里说道，不要自动或手动做类型转换。
这里 20000 为字符串，当我们在查询时不加引号的话，那么 MySQL 会自动做类型转换，导致索引失效

10. 少用 OR，会导致索引失效