10. 索引的设计和使用

10.1 索引概述

数据库索引：是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据表中数据。索引的实现通常使用B树及其变种B+树。

MySQL索引：索引用于快速查找具有特定列值的行。没有索引，MySQL必须从第一行开始，然后通读整个表以找到相关的行。

索引的优点

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 加快数据的检索速度。
• 减少磁盘I/O次数
• 加速表和表之间的连接。
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引并不是万金油，增加索引也有许多不利的方面。

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
• 对表中的数据进行增删改的时候，索引也需要动态的维护，降低写操作性能，减缓表的修改速度。

10.2 数据库索引的分类

可以在数据库设计器中创建五种类型的索引：主键索引、唯一索引、普通索引、复合索引、全文索引。其中主键索引也可以叫做聚集索引，非主键索引称为非聚集索引。

主键索引

数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。

在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引，唯一性，可以为空。

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(name)上创建了唯一索引，则任何两个员工都不能同姓。

普通索引

用表中的普通列构建的索引，没有任何限制

复合索引

用多个列组合构建的索引，这多个列中的值不允许有空值，遵循最左匹配原则。如组合索引index(col1,col2,col3),使用col2和col3的查询是不会使用索引的。

全文索引

主要用于大文本信息建立索引

10.3 聚集索引和非聚集索引

聚集索引的理解：

我对索引的的理解是这样的：

如果一个表没有加索引，数据按顺序一条一条的在磁盘上按插入顺序存储着。

如果一张表一旦加了索引，比如加了自增主键，那么表中的数据在磁盘中就不是按顺序排列的，而是变成了树状结构，也就是我们常说的平衡树，换句话说就是整个表都变成了一个索引树，也就是所谓的聚簇索引。这也是为什么一个表中只有一个主键（可以是多个字段，联合主键），因为自增主键的根就是根据一定算法把表的数据按照一定格式转换成平衡树存放在磁盘上的。

聚集索引：表数据存放的物理顺序与索引顺序一致。

非聚集索引：表数据存放的物理顺序与索引顺序不一致

相当于顺序表和链表之间的差异。

根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

10.3.1 聚集索引

• 聚集索引的叶子结点就是数据结点。
• 聚簇索引的数据表和主键索引存储在一起。

聚集索引中表记录的排列顺序和索引的排列顺序保持一致，所以查询效率相当快。只要找到第一个索引记录的值，其余的连续性的记录也一定是连续存放的。

假设执行一个SQL为select * from where id=50，查找流程就是平衡树关键字的查找

很多存储引擎在B+Tree的基础上进行了优化，添加了指向相邻叶节点的指针，形成了带有顺序访问指针的B+Tree，这样做是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

下面更好的解释了这个概念

10.3.2 非聚集索引

• 非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

• 非聚簇索引的数据表和索引表是分开存储的。

非聚集索引这棵树中所有的节点都来自于表中二级索引（非聚集索引）字段。假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ，那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。

每次给字段建一个新索引，字段中的数据就会被复制一份出来，用于生成索引。因此，给表添加索引，会增加表的体积，占用磁盘存储空间。

非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值 ，再使用主键的值通过聚集索引查找到需要的数据（回表查询），如下图

聚集和非聚集索引不管以任何方式查询表，最终都会利用主键通过聚集索引来定位到数据，聚集索引（主键）是通往真实数据所在的唯一路径。

不过，有一种例外可以不使用聚集索引就能查询出所需要的数据这种非主流的方法称之为覆盖索引查询，也就是平时所说的复合索引或者多字段索引查询。

索引参考：

• https://baijiahao.baidu.com/s?id=1645514817836645220&wfr=spider&for=pc
• https://blog.csdn.net/mysteryhaohao/article/details/51719871
• https://blog.csdn.net/tongdanping/article/details/79878302

10.4 索引的设计原则

(1) 在经常搜索的列上建立索引，可以加快搜索的速度。 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。

(2) 使用唯一索引

(3) 使用段索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个 CHAR(200)列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10个或20个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘 IO 较少，较短的值比较起来更快。

(3) 利用最左前缀匹配原则

(4) 不要过度索引。不要以为索引“越多越好”，什么东西都用索引是错误的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。

10.5 索引的使用

1、建表时创建索引

create table tb_name (
	col_name 数据类型 [完整性约束条件],
	...,
	[unique | fulltext | spatial] index|key [索引名](字段名1[(长度)] [ASC]|[DESC]) [using 索		引方法]
);

参数：

• unique：唯一性索引
• fulltext：全文索引
• spatial：空间索引
• index和key：用于指定字段为索引，两者作用是一样的，二选一。
• 索引名：可选，创建索引时指定名称。
• 字段名1:指定索引对应的字段的名称，该字段必须是前面定义好的字段。

• 长度:可选。指索引的长度，必须是字符串类型才可以使用。
• ASC:可选。表示升序排列。
• DESC:可选。表示降序排列。

索引示例：

mysql> create table t (
    -> id int comment 'ID标识',
    -> name varchar(20) comment '姓名',
    -> age int comment '年龄',
    -> addr varchar(100),
    -> primary key PK_ID (id),
    -> index index_name (name)
    -> );

2、建表后创建约束

alter table tb_name add  [UNIQUE | FULLTEXT | SPATIAL]  INDEX | KEY  [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法];
或者
CREATE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX  索引名 ON  表名(字段名) [USING 索引方法]；

示例：

mysql>  alter table t add unique index (addr);

3、查看已创建的索引

show index from tb_name;

【注】索引默认是BTREE索引

4、索引的删除

drop index 索引名 on tb_name;
alter table tb_name drop index 索引名;

5、查看SQL语句对索引的使用情况

select语句之前添加explain即可

请注意第二个和第三个查询，一个用到了索引，一个没有用到索引。

BTREE索引触发原则：

当使用 >、<、>= 、BETWEEN、!=、<>或者LIKE ’pattern'(其中patter不以通配符开始)操作符时，都可以使用相关列上的索引。

explain查询参数：

id：SELECT识别符。这是SELECT的查询序列号。

select_type：SELECT类型。

1. SIMPLE： 简单SELECT(不使用UNION或子查询)
2. PRIMARY： 最外面的SELECT
3. UNION：UNION中的第二个或后面的SELECT语句
4. DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
5. UNION RESULT：UNION的结果
6. SUBQUERY：子查询中的第一个SELECT
7. DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
8. DERIVED：导出表的SELECT(FROM子句的子查询)

table：表名

type：联接类型。是SQL性能的非常重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

一般来说，得保证查询至少达到range级别。

1. system：表仅有一行(=系统表)。这是const联接类型的一个特例。
2. const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
3. eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
4. ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY(换句话说，如果联接不能基于关键字选择单个行的话)，则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。
5. ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
6. index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
7. unique_subquery：该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。
8. index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)
9. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range
10. index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。
11. all：对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。

possible_keys：possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

key：key列显示MySQL实际决定使用的键(索引)。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len：key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

ref：ref列显示使用哪个列或常数与key一起从表中选择行。

rows：rows列显示MySQL认为它执行查询时必须检查的行数。

Extra：该列包含MySQL解决查询的详细信息。

explain命令解释

10.6 索引命中

1、通配符在前，模糊查询不使用索引

2、union、in、or 都能够命中索引，建议使用 in

3、联合索引最左前缀原则(又叫最左侧查询)

如果在(a,b,c)三个字段上建立联合索引，那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。

4、范围列可以用到索引

• 范围条件有：<、<=、>、>=、between等。
• 范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)

范围查询不一定用到索引

如果索引列数据量大的情况下，那么如果使用索引列查询不及普通查询的话，也有可能用不到索引。

10.7 最左匹配原则的解释

创建表

create` `table` `test(
a ``int` `,
b ``int``,
c ``int``,
d ``int``,
key` `index_abc(a,b,c)
)engine=InnoDB ``default` `charset=utf8;

插入10000条数据，当我在执行的时候，以为会很快执行，但没想到这10000条数据往磁盘写花费了接近2min，

不明白为什么？

DROP` `PROCEDURE` `IF EXISTS proc_initData;
DELIMITER $
CREATE` `PROCEDURE` `proc_initData()
BEGIN
DECLARE` `i ``INT` `DEFAULT` `1;
WHILE i<=10000 DO
  ``INSERT` `INTO` `test(a,b,c,d) ``VALUES``(i,i,i,i);
  ``SET` `i = i+1;
END` `WHILE;
END` `$
CALL proc_initData();

explain select * from test where a<10 ;

explain select * from test where a<10 and b <10;

explain select * from test where a<10 and b <10 and c<10;

上述查询都显示用到了组合索引，但是考虑下面这种情况：

explain select * from test where b<10 and a <10;

explain select * from test where b<10 and a <10 and c<10;

explain显示查询也用到了索引，不是最左匹配吗？

mysql会自动优化这些条件的顺序，以匹配尽可能多的索引列。

其实，mysql查询优化器帮我们解析纠正这条语句以什么样的顺序执行效率最高，有点自动校正的意思。

但是如果执行

explain select * from test where b<10 and c <10;

explain select * from test where a<10 and c <10;

会发现b<10 and c <10,没有用到索引？而 a<10 and c <10用到了？

因为B+树的数据项是复合的数据结构，比如建立组合索引(name,age,sex),B+树是从左到右来建立搜索树的，比如查询(张三,20,F)，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。