mysql索引工作原理、分类等知识点讲解

一、概述

在mysql中，索引（index）又叫键（key），它是存储引擎用于快速找到所需记录的一种数据结构。在越来越大的表中，索引是对查询性能优化最有效的手段，索引对性能影响非常关键。另外，mysql的索引是在存储引擎层实现，而不是在服务器层。

二、索引的工作原理

我们知道，在看一本书某章的时候，首先我们会查找目录索引，找到对应的页码然后快速找到相应的内容。mysql索引也一样，存储引擎利用类似的方法使用索引，先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行，然后返回结果。

例如，我们想在一个10w条记录表 table 中查询name等于“张三”的数据行，select * from table where name ='张三'。那么在没有对name字段建立索引的情况下，我们需要扫描全表也就是扫描10w条数据来找到这条数据；如果我们为name字段建立索引，我们只需要查找索引，然后根据索引找到对应的数据行，只需要查找一条记录，性能会得到很大的提高。

三、索引分类

索引按照实现方式不同可以分为 b-tree索引、hash索引、空间数据索引以及全文索引等。如果没有特别指明，多半用的是b-tree索引，b-tree 对索引列是顺序存储的，因此很适合查找范围数据。它能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据。

四、索引类型

索引主要分为：单列索引（普通索引、主键索引、唯一索引）和组合索引。

普通索引：

create index name_index on `table`(`name`);

alter table table add index name_index(`name`)

唯一索引：

create unique index id_unique_index on `table`(`id`);

主键索引：主键索引和唯一索引类似，唯一索引允许有空值，而主键索引不允许。

组合索引：通俗的说，组合索引就是一个表中一个索引包括多个字段，一个表中多个单列索引并不是组合索引。

例如：

create index nickname_account_createdtime_index on `award`(`nickname`, `account`, `created_time`);

五、组合索引的查询规则（什么情况下有效，什么情况下无效）

b-tree 索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀查找。我们建立表user（id，last_name,first_name, age ,birthday,sex）,建立组合索引 key（last_name, first_name, birthday）,那么它实际上包括三个索引（last_name），（last_name，first_name），（last_name，first_name，birthday）。

下面我们来分析组合索引有效以及无效的情况，mysql在使用组合索引查询的时候需要遵循“最左前缀”规则，什么是“最左前缀”规则呢，就是在使用组合索引查询，where的条件要按照从左到右的顺序，last_name first_name birthday，可以是只有last_name，或者包括last_name、first_name，或者last_name、first_name、birthday，这个从左到右的顺序不能变，也不能跳过；如果是直接first_name='ruby' 组合索引不生效，或者跳过first_name，last_name=‘allon’ and birthday = ‘2012’，组合索引只有last_name生效，后面的所有不生效。

例如：1、全键值匹配：select * from user where last_name=‘allon’ and first_name='java' and birthday=‘2017-12'是生效的，如果 select * from user where first_name='java' and birthday=‘2017-12'组合索引是不生效的，因为没有key(first_name,birthday)的索引。

2、键前缀查找：select * from user where last_name=‘allon’；这个索引存在，也是有效的，但不能select * from user where first_name='java'，这样不生效。

3、like模糊查询：比如只匹配组合索引第一列的值的开头部分，查询last_name姓张的人，select * from user where last_name like ‘张%’；但是不能select * from user where last_name like ‘%张’；组合索引也无法查找以张结尾的人。 再如 select * from user where last_name='allon' and first_name like '三%' and birthday = '2012-11-06'，因为first_name用了like这个范围查询条件，那么查询只用到了组合索引的前两列，范围查询右面的列birthday无法用索引优化查询。

如果查询中有某个列的范围查询，则该列右边的所有列都无法使用索引优化查找

4、匹配范围值：select * from user where last_name between ’allon‘ and 'clitton';这里只使用了组合索引的第一列，是生效的。

六、索引的优缺点

优点：1、建立索引后，在查询的时候合理利用索引能够提高性能；

2、主键索引 唯一索引能保证表中每一条数据的唯一性

3、减少分组和排序的时间

4、在表连接的连接条件上使用索引，可以加速表与表之间的相连。

缺点：1、创建索引和维护索引需要时间消耗；

2、索引文件占用物理空间

3、当对表的数据进行insert update delete时候需要维护索引，会降低数据的维护数据。