MySQL的索引

1、什么叫索引

索引是一种数据结构，帮助MySQL高效获取数据的数据结构；

MySql官方对索引的定义为：索引是帮助MySql高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

个人对于MySql索引的理解：在数据之外，数据库系统还维护着满足特定查找算法的数据结构，包括B+树或者Hash表。由于存储引擎表示的是数据在磁盘上面的不同的组织形式，所以索引底层采用哪种数据结构是跟数据库的存储引擎相关的。如果是MyIsam或者是InnoDB存储引擎，那么对应的底层的数据结构为B+树，如果是Memory存储引擎，那么对应的底层的数据结构为Hash表。采用B+树的最根本的原因是由于二叉树的树太高，树太高则直接影响到磁盘IO的次数，影响数据查询的效率，采用B+树的数据结构，可以在某个数据节点里面尽可能多的存储数据，使树的高度尽量的变低，提高效率。日常开发过程中，遇到的比较多的可能就是聚簇索引和联合索引，里面又涉及到了覆盖索引，最左匹配，回表，索引下推等各方面的知识点，在编写SQL语句的时候，我们就可以利用这些点来进行优化，提高数据的查询效率。

2、索引的优势劣势

优势

• 类似于书籍的目录索引,提高数据检索的效率,降低数据库的IO成本。
• 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗。

劣势

• 实际上索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所以索引列也是要占用空间的。
• 虽然索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行 INSERT、 UPDATE、 DELETE。因为更新表时,MSQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。

3、索引的数据结构区别：

B树与B+树之间的差别

答：B树与B+树之间最大区别，一个是聚簇索引，一个是非聚簇索引；

聚簇索引底层是B树结构，每个叶子节点存放的是数据本身，所以他的每条存储的内存占用较大；IO的次数相对比较多，树的高度也就比较高

非聚簇索引底层采用的是B+树结构，每个叶子节点存放的是主键ID，非叶子节点则存放的是key信息，占用的内存较小，IO次数相对比较小，所以B+树的结构是又矮又胖

4、为什么IO会有差别

IO是到磁盘上读取数据；

因为B树的各个叶子节点存放的是一整条的数据，而B+树的叶子节点存放的是主键ID，比如每次IO的话，可以抓取4k的数据；这时数据量一共有400条数据；

对于B树来说，每次抓取可能只能抓取到40条数据（4k），可能需要抓取10次才能找到对应的数据，这个时候就需要IO十次；

而B+树每个叶子节点存放的是主键ID，ID相比较整条数据来说，他所占的内存小很多，所以每次抓取的时候，就可以抓取到200条数据（4k），所以可能只需要抓取2次，加上回表查询的一次，可能只需要3次就可以，所以他的IO次数就会相对比较少

所以B+树的IO少，查询起来速度也就越快

5、主键索引，非主键索引，联合索引，覆盖索引

主键索引：

        聚簇索引，每个叶子节点存放的是一条的数据，非叶子节点存放主键ID

非主键索引：

        非聚簇索引，也就是通过name、age等这些属性进行索引；这些索引来说，每个叶子节点存放的是主键ID，而非叶子节点存放的是key的信息（name、age等内容），查询到主键ID后，再回表进行查询

联合索引：

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`name`,`uid`)
);

        两个或两个以上字段联合组成一个索引。使用时需要注意满足最左匹配原则！

最左前缀原则

        最左前缀原则指的是，查询从联合索引的最左列开始，并且不跳过索引中的列。如下：

select * from user where name=xx and city=xx ;

可以命中索引这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

尽量使用联合索引，而少使用单列索引

创建联合索引

create index idx_name_sta_address on table(name,status,address);

相当于创建了三个索引：

1. name;

2. name + status;

3. name + status + address;

覆盖索引：

        覆盖索引就是指索引包含了所有需要查询的字段。

select name,uid from cq_user

上面这条sql语句使用了联合索引 key(name,uid),并且只需查找 name,uid两个字段，所以使用了覆盖索引。覆盖索引有什么好处呢？

如果我们只需查询(name,uid)两个字段的话，从索引树就能得到我们需要查的数据，不需要回表。

覆盖索引好处
1.避免了对主键索引(聚簇)的二次查询
2.由于不需要回表查询(从表数据文件)所以大大提升了Mysql缓存的负载

总之大大提升了读取数据的性能

6、哪些字段加上索引

        一般来说，是经常使用到查询的字段加上索引

        联表字段，例如student表中的dept_id与dept表中的id这种的字段加上索引，也可以大大加快联表查询的速度（但是这一定要保证两个字段的数据类型是一样的）

7、索引失效

索引失效的情况

• 范围查询右边的列，不能使用索引

select * from t where name ='test' and status >'1' and address='北京市'

前面的两个字段name，status查询是走索引的，都是最后一个条件address没有用到索引

• 不要在索引列上进行运算操作，索引将失效

select * from t where substring(name,3,2)='科技'

• 字符串不加单引号，造成索引失效

select * from t where name ='test' and status =1

• 用or分隔的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。

select * from t where name ='test' or createtime='2020-04-05 12:00:00'

name是索引列，createtime不是索引列，之间or进行连接，那么会导致name列也不走索引

• 以%开头的like模糊查询，索引失效

如果仅仅是尾部的模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效，但是如果使用覆盖索引，那么索引仍然会生效

select name from t where name like '%test'

• 如果MySql评估使用索引比全表扫描更慢，则不使用索引
• is null，is not null 有时索引失效

is null，如果数据库中，该字段为空值的记录数更多，那么MySql评估使用索引比全表扫描更慢，则不使用索引

is not null 如果数据库中，该字段不为空值的记录数更多，那么MySql评估使用索引比全表扫描更慢，则不使用索引

• in 走索引，not in 索引失效
• 使用不等于(!=或者<>)的时候，索引失效，会导致全表扫描

select name from t where name != 'test'

• MYSQL针对函数或存储过程中传递进的参数,如果是varchar类型时则默认会进行转换字符集校对规则与数据库保持一致，这个时候如果数据库编码和表编码不一致时（比如utf8和utf8mb4），就会出现索引失效的情况

8、最左匹配原则

        

假如有个联合索引 key (col1,col2)。那么以下查询是索引无效的

select * from test where col2 = 3;

select * from test where col1 like '%3';

对于最左匹配原则，大家想一下B+树的叶子节点的关联就差不多知道为啥需要最左匹配原则了，因为B+的叶子结点，从左到右以链表的形式关联的，索引我们查询的时候要么范围查询，要么有明确的左边一个开始的索引值，不能跳过或者不明确如 like '%XYZ'这种查询。

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 col2= xx and col1 ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的