mysql索引创建规则、联合与一般索引、执行计划、索引选择，索引重建与下推_联合索引执行计划

 

数据库索引怎么建,什么时候用到

索引优缺点:为主键外建where子句建立索引可以加速数据库查询,但是索引占用内存,同时update和insert的时候需要同步修改;索引的实现通常使用其变种B+树。

建立索引方式:create index 索引名 on 表名(列名)；

细节问题:

1. )如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命性的结果下降,每次查找一条特定的数据都会进行一次全表扫描
2. ）防止过度索引,比如性别只有两个值,此时对该列建立索引不仅没有什么优势,反而会影响更新插入等操作的速度.

     3.)如果where条件进行了联合查询,则可以使用复合索引,如:

Select * from users where area=”beijing” and age=22;此时可以对area和age建立联合主键，而不是单单对area做索引，因为联合索引更加迅速

如果我们创建了(area,age,salary)复合主键,此时其实相当于创建了(area,age,salary),(area,age),(area)三个索引,这被称为最佳左前缀;因此在建立主键的时候应该将最常用的结果并且字段类型比较小的字段放在前面.

1. 索引不会含有null值得列,如果复合索引中只要有一列含有null值,那么这一列在复合索引中就是无效的.
2. Mysql查询中只使用一个索引,如果where子句中已经使用了索引,那么order by中的列是不会使用索引的.如果排序使用了多个列,可以考虑复合索引
3. 对于like “%a%”不会使用索引,但是类似 like “aa%”可以使用索引

 

哪些字段适合添加索引：

1、表的主键、外键必须有索引；

2、数据量超过300的表应该有索引；

3、经常与其他表进行连接的表，在连接字段上应该建立索引；

4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

5、索引应该建在选择性高的字段上；

6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

F、如果(a，b，c)是联合索引，where a='a' and b>'b' and c='c',那么c实际不会走索引。

8、频繁进行数据操作的表，不要建立太多的索引；

9、删除无用的索引，避免对执行计划造成负面影响；

 

上面说明了联合索引和多个单字段索引分别在什么情况下使用：

1. 复合索引的几个字段是否经常同时以AND方式出现在Where子句中
2. 单字段查询是否极少甚至没有

如果上面两个答案都是‘是’则使用联合索引

对字符串建索引的注意点：

如果字符串长度比较长，会导致索引占用内存比较大，但是如果只去字段的一部分作为索引，又会降低索引区分度。矛盾了

1前(后)缀索引
1.1会降低区分度，可以使用类似count(distinct(left(table_name,5))) as l5来统计
1.2导致覆盖索引无法正常使用
2增加字段的hash值作为一列，然后使用该列建索引,无法使用等值查询

执行计划

如果建了索引之后不确定效果，可以使用mysql自带的执行计划看下索引效果，下面介绍执行计划的使用

Sql写法：

explain tbl_name

explain [extended] select select_options

前者可以得出一个表的字段结构等等，后者主要是给出相关的一些索引信息，而今天要讲述的重点是后者。

Using filesort：如果mysql在排序的时候没有使用到索引那么就会输出using filesort。sql中order by/group by都会要求数据的排序
Using Temporary：数据需要排序，就要先取出来，取出来放到磁盘临时文件或者内存中。MySQL根据sort_buffer_size来判断是否使用磁盘临时文件，如果需要排序的数据能放入sort_buffer_size则无需使用磁盘临时文件，仅仅使用内存就可以了，此时explain只会输出using filesort 否则需要使用磁盘临时文件explain会输出using temporary;using filesort；这里需要指出：如果使用了磁盘临时文件，那么就需要磁盘IO(很慢)，肯定会拉慢sql的执行效率，需要避免。

可选索引有多个时，怎么选择？

主键索引优先于一般索引
选择索引基数大，扫描行数小的索引

索引基数怎么预估？扫描行数怎么预估？
统计总页数，抽样n个页获取每个页的平均基数
根据基数来预估扫描行数
主键索引优先于一般索引

为什么需要索引重建

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

这道题目，我给你的“参考答案”是：

偶尔重建非主键索引 k的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。

索引下推

联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

你已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。当然，这还不错，总比全表扫描要好。

然后呢？

当然是判断其他条件是否满足。

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

下面是无索引下推，会回表四次，如下图

有索引下推，仅仅回表两次，会先把age为30和20的过滤掉，如下图

 

说明：索引下推 借鉴于极客时间 mysql实战45讲。