目录

1、为什么要使用索引

2、索引分类

3、存储结构

4、索引类型

4.1、普通索引(INDEX)

4.2、唯一索引(UNIQUE INDEX)

4.3、主键索引(PRIMARY KEY)

4.4、复合索引(组合索引)

4.5、全文索引

5、最左前缀原则

6、最左前缀失效

7、创建索引时需要考虑哪些因素

8、为什么不能乱用索引

9、B树和B+树

10、MySQL为什么使用B+树，而不是B-树、Hash、二叉树、红黑树

11、B+树3层最多存多少数据

12、聚簇索引和非聚簇索引

12.1、 聚簇索引

12.2、辅助索引

12.3、非聚簇索引

13、索引失效场景

14、索引分析和优化

14.1、Explain

14.1.1、Select_type

14.1.2、Type

14.1.3、Possible_keys

14.1.4、Key

14.1.5、Key_len

14.1.6、Rows

14.1.7、Extra

14.2、回表

14.3、覆盖索引

14.4、最左前缀

14.5、MySQL在使用like模糊查询时，索引能不能起作用？

14.6、如果MySQL表的某一列含有NULL值，那么包含该列的索引是否有效？

14.7、索引与排序

14.7.1、Filesort排序算法

14.7.2、Index方式排序场景(最左前缀)

14.7.3、Filesort方式排序场景(where和order by)

15、索引调优

1、为什么要使用索引

        将一个节点（索引的值）的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

        数据存放在硬盘上，查询数据时会大量进行IO操作，性能降低。使用索引，把相关数据保存在一起，查询时根据索引一次性找出相关数据，会减少与硬盘的IO交互。

        索引是一种数据结构，包括哈希索引和BTree索引，一般为B-Tree（有序的），索引包含一个表中所有列的值，并将这些值存储在数据结构中，同时索引还存储了表中相应行的指针，但是并不存储这个表中其他列的值。目的主要是为了提高数据的检索速度。

1. 减少存储引擎需要检索的数据量
2. 唯一索引可以保证数据的唯一性
3. 在使用分组或排序进行数据检索时，可以减少分组和排序的时间
4. 索引可以将随机的I/O转为顺序I/O

2、索引分类

• 从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
• 从应用层次划分：普通索引、唯一索引、主键索引、复合索引(组合索引)
• 从索引键值类型划分：主键索引、辅助索引（二级索引）
• 从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

删除索引：drop index 索引名称 on tableName;

查看索引：show index from tableName;

3、存储结构

• FullText

        全文索引，只有MyISAM支持。

        为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

• Hash

        无序，根据hash函数映射数据位置，存储哈希值和指针； 

        一次定位，高效，适用于”=”、”in”条件，无法排序，在范围查询、排序等情况下效率低下。

• BTREE

        树形结构，二分查找，B+树，磁盘IO少，效率高。

        有序，按照索引值进行排序的数据结构；

        可用于等值、范围过滤以及排序等操作；

• RTREE

        仅支持geometry数据类型，RTREE的优势在于范围查找。

4、索引类型

4.1、普通索引(INDEX)

基于普通字段建立的索引，没有任何限制。

• CREATE INDEX <索引的名字> ON tablename (字段名);
• ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
• CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) )。

4.2、唯一索引(UNIQUE INDEX)

索引字段的值必须唯一，但允许有空值。

创建唯一索引的方法如下：

• CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
• ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
• CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名)。

4.3、主键索引(PRIMARY KEY)

特殊的唯一索引，不允许有空值。每个表只能有一个主键。

• CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
• ALTER TABLE tablename ADD PRIMARY KEY (字段名);

4.4、复合索引(组合索引)

        用户可以在多个列上建立索引，这种索引叫做复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

        复合索引有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比宽索引更有效。

• CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
• ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
• CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

复合索引使用注意事项：

如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

4.5、全文索引

        查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。

• CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
• ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
• CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

和常用的like模糊查询不同，全文索引有自己的语法格式，使用match和against关键字，比如：

select * from user where match(name) against('aaa');

全文索引使用注意事项：

• 全文索引必须在字符串、文本字段上建立；
• 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）；

   字段值大小必须在范围内，才会创建全文索引。

        比如：select * from user where match(name) against('a'); 值‘a’长度为1，不会创建全文索引，所以查不到数据。

• 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa；

        Syntax字符如下：

• 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*；

        select * from user where match(name) against('a*' in boolean mode);

5、最左前缀原则

        对于组合索引（col1,col2,col3）,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高;    select * from table1 where col1=A AND col2=B AND col3=D

        如果使用 where col2=B AND col1=A  或者  where col2=B  将不会使用索引

• 组合索引中字段的顺序，选择性越高的字段排在最前面
• 组合索引的字段不要过多，如果超过4个字段，一般需要考虑拆分成多个单列索引或更为简单的组合索引
• 如果where条件中是OR关系，加索引不起作用
• 避免使用“OR”，否定查询，模糊查询，not in，<>等操作
• 选择 where,on,group by,order by 中出现的列

举例说明

        比如，联合索引为(a，b，c，d)

        select * from user where a=xx and b=xx 索引有效

        select * from user where b=xx and a=xx 索引有效，但查询引擎会优化顺序为联合索引的顺序，增加额外的时间开销

        select * from user where a=xx and c=xx 索引无效

        select * from user where b=xx 索引无效

        向右匹配直到遇到范围查询(>、<、between、like)就停止匹配

        联合索引为(a，b，c，d)

        select * from user where a=xx and b=xx and c>3 and d=4 d索引无效

        联合索引为(a，b，d，c) “=”查询时，索引列的顺序可以调整

        select * from user where a=xx and b=xx and c>3 and d=4 索引有效

6、最左前缀失效

• 回表

        对于普通索引查询时，需要根据索引的索引树(非聚簇索引)找到叶子节点对应的主键，再通过主键去主键索引树查询一遍，才能得到真实的数据，此过程就叫做回表。

• 索引覆盖

        在用索引查询时，使它的索引树，查询到的叶子节点上的数据可以覆盖到你查询的所有字段，这样就可以避免回表。

• 举例说明

        现有组合索引index（a，b，c）

        查询语句为select a,b,c from table1 where c = 'xxx'。根据最左匹配原则，该SQL查询不会走组合索引index，但实际执行计划如下(走了组合索引)。

         这是因为查询字段为a,b,c，三个字段的值均可在组合索引index中找到，通过组合索引查询，可避免回表，所以虽然查询条件不符合组合索引的最左匹配原则，但是MySQL执行器还是走了index组合索引。

7、创建索引时需要考虑哪些因素

        慢查询->explain->执行计划->全表扫描->对查询条件建立索引。

8、为什么不能乱用索引

1. 创建和维护索引需要耗费额外的时间，且随着数据量的增加而增加
2. 索引需要占用额外的物理空间
3. 当堆表中的数据进行新增、修改、删除操作时，需要消耗额外的时间进行索引的维护，降低了数据的更新效率

9、B树和B+树

• BTree

1. 索引值和data数据分布在整棵树结构中
2. 每个节点可以存放多个索引值及对应的data数据
3. 树节点中的多个索引值从左到右升序排列
4. 所有节点都带有指向记录的指针，在内部节点出现的索引项不会出现在叶子节点中；

        B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。

• B+Tree

1. 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
2. 叶子节点包含了所有的索引值和data数据
3. 叶子节点用指针连接，提高区间的访问性能
4. 非叶子节点不存放数据，所以相同磁盘块能存放的节点数更多，能存放更多元素，从而减少磁盘访问次数。

        B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

10、MySQL为什么使用B+树，而不是B-树、Hash、二叉树、红黑树

IO：将索引从硬盘加载到内存。

Hash：可快速定位，但是没有顺序，IO复杂度高；

二叉树：树高不均匀，查找效率和数据有关(树高)；

红黑树：树的高度随着数据量的增加而增加，IO代价高；

B-树：每一层都会存储数据

• B+树查询时，从根节点开始进行二分查找，无需进行全表扫描。
• B+树的数据集中在叶子节点，分支节点只存放索引，B+树的层高小于B树，平均IO更小。
• B+树的数据都存放在叶子节点，查询效率稳定。
• B+树的数据在叶子节点有序存放，更适合范围查询。

相对于B树，B+树非叶子节点不存储数据，占用空间小，所以树结构更加矮胖，层级更低，IO次数更少。

而B树，虽然提高了磁盘IO性能，但并没有解决元素遍历低下(中序遍历)的问题。

11、B+树3层最多存多少数据

        页(Page)是InnoDb的最小存储单元，一个页的大小是16k。

        数据表中的数据都是存储在页中的，假设1行数据的大小是1k，则1页就有16行。

        指针在InnoDb中的大小为6字节，非叶子节点为指针，叶子节点为数据。

        非叶子节点的指针数据，有指针(6字节)和主键(8字节)。

        则一个非叶子节点，可存储16*1024/(6+8) = 1170个指针数据。

        则三层B+树结构：

        根节点1170，第二层1170*1170 = 1368900个指针数据。

        每个指针指向一个page，每个page可存储16行数据：

        则第三层可存储数据 = 1368900 * 16 = 21902400行数据。

        一次页的查找即为一次IO，那么B+树一般只需要1-3次IO即可查询到数据。

12、聚簇索引和非聚簇索引

• 聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。
• 主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。
• 在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。

12.1、 聚簇索引

        InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。

        数据存储和索引放在一块，在B+Tree结构中体现在叶子节点为索引和数据。

InnoDB的表要求必须要有聚簇索引：

• 如果表定义了主键，则主键索引就是聚簇索引
• 如果表没有定义主键，则第一个非空unique列作为聚簇索引
• 否则InnoDB会从建一个隐藏的row-id作为聚簇索引

 12.2、辅助索引

        InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多， 通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。

        索引叶节点data域存储相应记录主键的值而不是地址。(查询时需要回表)

 12.3、非聚簇索引

        非聚簇索引都是辅助索引，数据存储和索引分开存放，在B+Tree结构中体现在叶子节点为索引和指向数据的指针。

        MyISAM引擎使用B+Tree作为索引结构，为非聚簇索引，索引和数据存放在不同文件中，索引叶节点的data域存放的是数据记录的地址。

        MyISAM将索引缓存到内存中，访问数据时，直接在内存中搜索索引，找到索引后再根据数据指针找到磁盘上相应的数据。

        InnoDB引擎使用B+Tree作为索引结构，为聚簇索引，索引和数据存放在同一个文件中，索引叶节点的data域存放的是行数据。由于聚簇索引是将数据和索引放到一块，所以一个表只有一个聚簇索引。

        而在聚簇索引之上的是辅助索引(普通索引)，辅助索引的子节点的data域存放的是主键值。查询时，首先根据辅助索引找到主键值，然后利用主键值执行第二次查找找到行数据。

        聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

13、索引失效场景

• 条件中包含OR运算符
• like查询以%开头
• 条件中含有函数或者表达式
• 索引列上使用内置函数
• 对索引列进行运算
• 如果MySQL执行器认定索引比全表扫描慢，则不会使用索引
• 组合索引不满足最左匹配原则

14、索引分析和优化

14.1、Explain

14.1.1、Select_type

查询类型：

1. SIMPLE ： 表示查询语句不包含子查询或union
2. PRIMARY：表示此查询是最外层的查询
3. UNION：表示此查询是UNION的第二个或后续的查询
4. DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
5. UNION RESULT：UNION的结果
6. SUBQUERY：SELECT子查询语句
7. DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果

14.1.2、Type

存储引擎查询数据时采用的方式：

1. ALL：表示全表扫描，性能最差。
2. index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
3. range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
4. ref：表示使用非唯一索引进行单值查询。
5. eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
6. const：表示使用主键或唯一索引做等值查询，常量查询。
7. NULL：表示不用访问表，速度最快。

14.1.3、Possible_keys

        表示查询时可能使用到的索引。

14.1.4、Key

        查询时真正使用到的索引。

14.1.5、Key_len

        表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。

• 字符串类型

        字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4

        char(n)：n*字符集长度

        varchar(n)：n * 字符集长度 + 2字节

• 数值类型

        TINYINT：1个字节        SMALLINT：2个字节        MEDIUMINT：3个字节

        INT、FLOAT：4个字节        BIGINT、DOUBLE：8个字节

• 时间类型

        DATE：3个字节        TIMESTAMP：4个字节        DATETIME：8个字节

• 字段属性

        NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项

14.1.6、Rows

        SQL查询扫描的行数，行数越小越好。MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。

14.1.7、Extra

额外信息。

• Using where：表示查询需要通过索引回表查询数据。
• Using index：表示查询需要通过索引，索引就可以满足所需数据。
• Using filesort：表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化。
• Using temprorary：查询使用到了临时表，一般出现于去重、分组等操作。

14.2、回表

        聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。

        辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。

        通过索引查询主键值，然后再去聚簇索引查询记录信息。

14.3、覆盖索引

• explain的输出结果Extra字段为Using index时，能够触发索引覆盖。
• 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。
• 实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

14.4、最左前缀

        最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

14.5、MySQL在使用like模糊查询时，索引能不能起作用？

        MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。

14.6、如果MySQL表的某一列含有NULL值，那么包含该列的索引是否有效？

有效。

• 空值不占空间，NULL值占空间。当字段不为NULL时，也可以插入空值
• 当使用 IS NOT NULL 或者 IS NULL 时，只能查出字段中没有不为NULL的或者为 NULL 的，不能查出空值。
• 判断NULL 用IS NULL 或者 is not null,SQL 语句函数中可以使用IFNULL()函数来进行处理，判断空字符用 =’‘或者<>’'来进行处理。
• 在进行count()统计某列的记录数的时候，如果采用的NULL值，会别系统自动忽略掉，但是空值是会进行统计到其中的。

在对某列设置索引时，最好将该列设置为NOT NULL。

14.7、索引与排序

MySQL查询支持filesort和index两种方式的排序：

Explain分析SQL，结果中Extra属性显示Using filesort，表示使用了filesort排序方式，需要优化。

如果Extra属性显示Using index时，表示覆盖索引，所有操作在索引上完成，可以使用index排序方式，建议大家尽可能采用覆盖索引。

1. filesort是先把结果查出，然后在缓存或磁盘进行排序操作，效率较低。
2. index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。

14.7.1、Filesort排序算法

• 双路排序

        两次磁盘扫描，第一次取出排序字段->排序->第二次取出其他数据。

• 单路排序

        一次磁盘扫描，取出所有列数据->排序。

如果查询数据超出缓存大小->多次磁盘读取操作->多次IO->降低性能。

解决方案：少使用select *；增加sort_buffer_size容量和max_length_for_sort_data容量。

14.7.2、Index方式排序场景(最左前缀)

• ORDER BY 子句索引列组合满足索引最左前列

• WHERE子句+ORDER BY子句索引列组合满足索引最左前列

14.7.3、Filesort方式排序场景(where和order by)

• 对索引列同时使用了ASC和DESC

•  WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询（例如>、<、in等）

•  ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列

•  使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引

•  WHERE子句与ORDER BY子句，使用了不同的索引

• WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式

15、索引调优

• 索引之所以能提升查询速度，在于他在插入时对数据进行了排序。
• B+树索引，通常为3~4层，能存放千万到上亿的数据。所以，从千万或上亿数据里查询一条数据，只需要3-4次IO，而现在固态硬盘每秒能执行至少10000次IO。
• B+树索引存储数量：一个页大小16K，一个键值对大小 = 键值(8) + 指针(6)个字节。

        每页能存储的键值对 = 16 * 1024 / 14 = 1170

        如果为三层树结构：(每条数据大小为1K，则1页可存储16条数据。)

        第一层存储1170个键值对

        第二层存储1170*1170 = 1368900个键值对

        第三层存储1368900 * 16 = 21902400行数据。

• 可以通过查询表sys.schema_unused_indexes查看哪些索引一直未被使用过。

        在删除索引前，可对索引设置不可见(Invisible)功能，如果对业务没影响再删除。

• 数据存储方式：堆表、索引组织表。

        堆表：数据无序存放，数据和索引分开存储，排序完全依赖于索引。当数据位置改变时，需要修改所有索引中存储的地址。（索引全是二级索引，每次索引查询都要回表）

        索引组织表：数据根据主键排序存放在索引中。二级索引的根节点存储主键id，数据位置发生改变时，只需要更新主键索引存储的地址，其余二级索引不用修改。

• 可以为表达式创建函数索引，优化业务SQL性能。
• SQL查询条件中函数必须写在等式右边而不能写在左边。
• Nested Loop Join

        驱动表R，关联表S。使用索引进行表关联。表R中通过where条件过滤的数据会在表S对应的索引上进行一一查询。小表驱动大表。

• Hash Join

        用于两张表之间连接条件没有索引(有索引优化器却不走索引)的情况。

        首先会在扫描驱动表的过程中创建一张哈希表；

        扫描第二张表时，会在哈希表中搜索每条关联的记录。

以上内容为个人学习理解，如有问题，欢迎在评论区指出。

部分内容截取自网络，如有侵权，联系作者删除。