搞懂MySQL索引和数据库调优实操

2020-09-03 03:24:19 字数 2247 阅读 542

## 一、索引的本质
* 索引：在数据库管理系统中，从海量数据中高效获取数据的一种数据结构，使用索引可以减少数据库扫描的行数，从而提高数据检索效率。
* 索引的价值是**快速定位**。
* 在 MySQL 中采用的是 B+ 树，B+ 树的非叶子节点不会存储数据，每次只有访问到叶子节点才能找到对应的数据，因此B+ 树在查询性能上更稳定，在磁盘页大小相同的情况下，树的构造更加矮胖，所需要进行的磁盘 I/O 次数更少。
 
## 二、索引的主要种类
##### 按照功能逻辑划分
1. **普通索引**：基础的索引，没有任何约束，主要用于提高查询效率。
2. **唯一索引**：在普通索引的基础上增加了数据唯一性的约束，在一张数据表里可以有多个唯一索引。
3. **主键索引**：唯一索引的基础上增加了不为空的约束，一张表里最多只有一个主键索引。这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。
4. **全文索引**：用的不多，MySQL 自带的全文索引只支持英文。我们通常可以采用专门的全文搜索引擎，比如 ES(ElasticSearch) 和 Solr。
* 效率：主键索引>唯一索引>普通索引
 
##### 按照物理实现划分
1. **聚集索引**：即主键索引，一张表里只有一个聚集索引。
2. **聚集索引**：除聚集索引以外的索引，在数据库系统会有单独的存储空间存放，这些索引项是按照顺序存储的，但索引项指向的内容是随机存储的。也称为二级索引或者辅助索引。
 
##### 按照字段个数划分
1. **单一索引**：索引列为一列时为单一索引。
1. **联合索引**：多个列组合在一起创建的索引叫做联合索引。
 
##### 联合索引的最左原则
* 按照最左优先的方式进行索引的匹配。
* 索引是一种顺序结构，我们按照什么顺序创建索引，就只能按照这个顺序使用索引。
* **使用联合索引时，若不包含第一个索引字段，则联合索引无效，联合索引的字段中有使用OR时联合索引失效**。
 
## 三、索引的使用前提
* 除了聚集索引（主键索引）外，其他的索引都是有自己的物理存储空间的，而且索引中只存储数据位置并不存储数据内容，数据库在使用索引检索数据时，需要先去加载索引数据到内存，然后再根据索引的地址去加载对应的数据行。所以**当数据量较小时，不需要建立索引，一般数据量大于1000才需要建立索引**。
* **当数据重复率较高时，不需要建立索引，一般数据重复率小于10%才需要建立索引**。
 
## 四、数据调优的方法
#### 1、优化逻辑查询
* 逻辑查询优化就是通过改变 SQL 语句的内容让 SQL 执行效率更高效，采用的方式是对 SQL 语句进行等价变换，对查询进行重写。
* SQL 的查询重写包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等。
* 使用EXISTS 子查询和 IN 子查询的时候，小表驱动大表用EXISTS，大表驱动小表用IN。
* 任何地方都不要使用 select * from t 
 
 
 
#### 2、优化物理查询
* 对查询进行优化，应尽量避免全表扫描，首先应考虑在where、group by、order by 涉及的列上建立索引。
* 一般数据量少于1000不需要建立索引，但是被其他表链接，可以适量进行添加索引。
* 使用联合索引时，第一个索引字段必须要包含，否则无效，另外要注意联合索引的顺序，要按照MySQL的执行顺序制定对应的顺序索引。MySQL的执行顺序如下：
```
FROM  > JOIN > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT
```
* 在SQL语句之前加上EXPLAIN，可以查看SQL语句执行过程，通过【key】使用了哪些索引，【rows】扫描的行数来进行调试。
 
![](https://upload-images.jianshu.io/upload_images/21643577-624de104f5562b23.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/800)
 
 
* 要注意多个索引对索引使用的影响。索引不是越多越好，因为每个索引都需要存储空间，索引多也就意味着需要更多的存储空间。此外，过多的索引也会导致优化器在进行评估的时候增加了筛选出索引的计算时间，影响评估的效率。
* 删除数据重复度高的索引，使用SHOW INDEX命令，删除结果集中基数（cardinality）小于1000的索引。
```
SHOW INDEX FROM 表名
```
* 索引字段尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
* 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
 
## 五、索引失效的几种情况
* WHERE 子句中对索引字段进行了表达式的计算和操作
* WHERE 子句中对索引字段进行 null 值判断
* WHERE 子句中对索引字段使用!=或<>操作符
* WHERE 子句中对索引字段使用函数，例：find_in_set
* WHERE 子句中对索引字段使用like，例：‘%李%’
* WHERE 子句中对索引字段使用参数，例：num=@num
* 联合索引的字段中有使用OR时联合索引无效
* 联合索引的字段中第一个索引字段没有使用时联合索引无效

## 一、索引的本质
* 索引：在数据库管理系统中，从海量数据中高效获取数据的一种数据结构，使用索引可以减少数据库扫描的行数，从而提高数据检索效率。
* 索引的价值是**快速定位**。
* 在 MySQL 中采用的是 B+ 树，B+ 树的非叶子节点不会存储数据，每次只有访问到叶子节点才能找到对应的数据，因此B+ 树在查询性能上更稳定，在磁盘页大小相同的情况下，树的构造更加矮胖，所需要进行的磁盘 I/O 次数更少。

## 二、索引的主要种类
##### 按照功能逻辑划分
1. **普通索引**：基础的索引，没有任何约束，主要用于提高查询效率。
2. **唯一索引**：在普通索引的基础上增加了数据唯一性的约束，在一张数据表里可以有多个唯一索引。
3. **主键索引**：唯一索引的基础上增加了不为空的约束，一张表里最多只有一个主键索引。这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。
4. **全文索引**：用的不多，MySQL 自带的全文索引只支持英文。我们通常可以采用专门的全文搜索引擎，比如 ES(ElasticSearch) 和 Solr。
* 效率：主键索引>唯一索引>普通索引

##### 按照物理实现划分
1. **聚集索引**：即主键索引，一张表里只有一个聚集索引。
2. **聚集索引**：除聚集索引以外的索引，在数据库系统会有单独的存储空间存放，这些索引项是按照顺序存储的，但索引项指向的内容是随机存储的。也称为二级索引或者辅助索引。

##### 按照字段个数划分
1. **单一索引**：索引列为一列时为单一索引。
1. **联合索引**：多个列组合在一起创建的索引叫做联合索引。

##### 联合索引的最左原则
* 按照最左优先的方式进行索引的匹配。
* 索引是一种顺序结构，我们按照什么顺序创建索引，就只能按照这个顺序使用索引。
* **使用联合索引时，若不包含第一个索引字段，则联合索引无效，联合索引的字段中有使用OR时联合索引失效**。

## 三、索引的使用前提
* 除了聚集索引（主键索引）外，其他的索引都是有自己的物理存储空间的，而且索引中只存储数据位置并不存储数据内容，数据库在使用索引检索数据时，需要先去加载索引数据到内存，然后再根据索引的地址去加载对应的数据行。所以**当数据量较小时，不需要建立索引，一般数据量大于1000才需要建立索引**。
* **当数据重复率较高时，不需要建立索引，一般数据重复率小于10%才需要建立索引**。

## 四、数据调优的方法
#### 1、优化逻辑查询
* 逻辑查询优化就是通过改变 SQL 语句的内容让 SQL 执行效率更高效，采用的方式是对 SQL 语句进行等价变换，对查询进行重写。
* SQL 的查询重写包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等。
* 使用EXISTS 子查询和 IN 子查询的时候，小表驱动大表用EXISTS，大表驱动小表用IN。
* 任何地方都不要使用 select * from t

#### 2、优化物理查询
* 对查询进行优化，应尽量避免全表扫描，首先应考虑在where、group by、order by 涉及的列上建立索引。
* 一般数据量少于1000不需要建立索引，但是被其他表链接，可以适量进行添加索引。
* 使用联合索引时，第一个索引字段必须要包含，否则无效，另外要注意联合索引的顺序，要按照MySQL的执行顺序制定对应的顺序索引。MySQL的执行顺序如下：
```
FROM  > JOIN > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT
```
* 在SQL语句之前加上EXPLAIN，可以查看SQL语句执行过程，通过【key】使用了哪些索引，【rows】扫描的行数来进行调试。

![](https://upload-images.jianshu.io/upload_images/21643577-624de104f5562b23.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/800)

* 要注意多个索引对索引使用的影响。索引不是越多越好，因为每个索引都需要存储空间，索引多也就意味着需要更多的存储空间。此外，过多的索引也会导致优化器在进行评估的时候增加了筛选出索引的计算时间，影响评估的效率。
* 删除数据重复度高的索引，使用SHOW INDEX命令，删除结果集中基数（cardinality）小于1000的索引。
```
SHOW INDEX FROM 表名
```
* 索引字段尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
* 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

## 五、索引失效的几种情况
* WHERE 子句中对索引字段进行了表达式的计算和操作
* WHERE 子句中对索引字段进行 null 值判断
* WHERE 子句中对索引字段使用!=或<>操作符
* WHERE 子句中对索引字段使用函数，例：find_in_set
* WHERE 子句中对索引字段使用like，例：‘%李%’
* WHERE 子句中对索引字段使用参数，例：num=@num
* 联合索引的字段中有使用OR时联合索引无效
* 联合索引的字段中第一个索引字段没有使用时联合索引无效

一、索引的本质

索引：在数据库管理系统中，从海量数据中高效获取数据的一种数据结构，使用索引可以减少数据库扫描的行数，从而提高数据检索效率。
索引的价值是快速定位。
在 MySQL 中采用的是 B+ 树，B+ 树的非叶子节点不会存储数据，每次只有访问到叶子节点才能找到对应的数据，因此B+ 树在查询性能上更稳定，在磁盘页大小相同的情况下，树的构造更加矮胖，所需要进行的磁盘 I/O 次数更少。

二、索引的主要种类

按照功能逻辑划分

普通索引：基础的索引，没有任何约束，主要用于提高查询效率。
唯一索引：在普通索引的基础上增加了数据唯一性的约束，在一张数据表里可以有多个唯一索引。
主键索引：唯一索引的基础上增加了不为空的约束，一张表里最多只有一个主键索引。这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。
全文索引：用的不多，MySQL 自带的全文索引只支持英文。我们通常可以采用专门的全文搜索引擎，比如 ES(ElasticSearch) 和 Solr。

效率：主键索引>唯一索引>普通索引

按照物理实现划分

聚集索引：即主键索引，一张表里只有一个聚集索引。
聚集索引：除聚集索引以外的索引，在数据库系统会有单独的存储空间存放，这些索引项是按照顺序存储的，但索引项指向的内容是随机存储的。也称为二级索引或者辅助索引。

按照字段个数划分

单一索引：索引列为一列时为单一索引。
联合索引：多个列组合在一起创建的索引叫做联合索引。

联合索引的最左原则

按照最左优先的方式进行索引的匹配。
索引是一种顺序结构，我们按照什么顺序创建索引，就只能按照这个顺序使用索引。
使用联合索引时，若不包含第一个索引字段，则联合索引无效，联合索引的字段中有使用OR时联合索引失效。

三、索引的使用前提

除了聚集索引（主键索引）外，其他的索引都是有自己的物理存储空间的，而且索引中只存储数据位置并不存储数据内容，数据库在使用索引检索数据时，需要先去加载索引数据到内存，然后再根据索引的地址去加载对应的数据行。所以当数据量较小时，不需要建立索引，一般数据量大于1000才需要建立索引。
当数据重复率较高时，不需要建立索引，一般数据重复率小于10%才需要建立索引。

四、数据调优的方法

1、优化逻辑查询

逻辑查询优化就是通过改变 SQL 语句的内容让 SQL 执行效率更高效，采用的方式是对 SQL 语句进行等价变换，对查询进行重写。
SQL 的查询重写包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等。
使用EXISTS 子查询和 IN 子查询的时候，小表驱动大表用EXISTS，大表驱动小表用IN。
任何地方都不要使用 select * from t

2、优化物理查询

对查询进行优化，应尽量避免全表扫描，首先应考虑在where、group by、order by 涉及的列上建立索引。
一般数据量少于1000不需要建立索引，但是被其他表链接，可以适量进行添加索引。
使用联合索引时，第一个索引字段必须要包含，否则无效，另外要注意联合索引的顺序，要按照MySQL的执行顺序制定对应的顺序索引。MySQL的执行顺序如下：

FROM  > JOIN > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT

在SQL语句之前加上EXPLAIN，可以查看SQL语句执行过程，通过【key】使用了哪些索引，【rows】扫描的行数来进行调试。

要注意多个索引对索引使用的影响。索引不是越多越好，因为每个索引都需要存储空间，索引多也就意味着需要更多的存储空间。此外，过多的索引也会导致优化器在进行评估的时候增加了筛选出索引的计算时间，影响评估的效率。
删除数据重复度高的索引，使用SHOW INDEX命令，删除结果集中基数（cardinality）小于1000的索引。

SHOW INDEX FROM 表名

索引字段尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

五、索引失效的几种情况

WHERE 子句中对索引字段进行了表达式的计算和操作
WHERE 子句中对索引字段进行 null 值判断
WHERE 子句中对索引字段使用!=或<>操作符
WHERE 子句中对索引字段使用函数，例：find_in_set
WHERE 子句中对索引字段使用like，例：‘%李%’
WHERE 子句中对索引字段使用参数，例：num=@num
联合索引的字段中有使用OR时联合索引无效
联合索引的字段中第一个索引字段没有使用时联合索引无效