MySQL索引

一、索引概述

1、为什么用索引？

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。（--引自菜鸟教程）在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

（1）顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

（2）索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。例如，在学生基本信息表 tb_students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表。当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

总结来说，不使用索引，MySQL 就必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。

2、什么是索引？

索引是存储引擎用于快速找到记录的一种数据结构。每一种存储引擎都会有不同的索引，不同的索引其底层数据结构也不同。索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表。创建索引并不会改变表中的数据，它只是创建了一个数据结构指向数据表；索引是一个文件，需要给它分配磁盘空间和维护索引表。

3、索引的优缺点

索引的优点

  • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
  • 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。

索引的缺点

  • 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
  • 空间方面：索引需要占物理空间。
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

4、索引使用场景

（1）where

img

上图中，根据id查询记录，因为id字段仅建立了主键索引，因此此SQL执行可选的索引只有主键索引，如果有多个，最终会选一个较优的作为检索的依据。

增加一个没有建立索引的字段

alter table innodb1 add sex char(1) not null comment'性别' after Guid;

按sex检索时可选的索引为null

img（2）order by

当我们使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更改读到内存之后的排序。

但是如果我们对该字段建立索引alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）

（3）join

对join语句匹配关系（on）涉及的字段建立索引能够提高效率

（4）索引覆盖

如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。这里值得注意的是不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。

5、索引的基本原理

索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。索引的原理很简单，就是把无序的数据变成有序的查询

  • 把创建了索引的列的内容进行排序
  • 对排序结果生成倒排表
  • 在倒排表内容上拼上数据地址链
  • 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

二、索引类型

为了测试索引，建立了一个测试表bank_account，通过下面命令查看表结构：

命令：DESC 数据表名称
mysql> DESC bank_account;
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| Id    | int(11)       | NO   | PRI | NULL    | auto_increment |
| Age   | int(3)        | YES  |     | NULL    |                |
| Name  | varchar(50)   | YES  |     | NULL    |                |
| Money | decimal(10,0) | YES  |     | NULL    |                |
+-------+---------------+------+-----+---------+----------------+
4 rows in set

mysql>

其中Extra提供了与关联操作有关的信息，没有则什么都不写。

可以使用下面命令查看索引详情：

SHOW INDEX FROM table_name
mysql> show index from bank_account;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| bank_account |          0 | PRIMARY  |            1 | Id          | A         |           2 | NULL     | NULL   |      | BTREE      |         |               | YES     | NULL       |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set

这里对上面的字段的意义进行解释

  • Table:创建索引的表

  • Non_unique：列值是否唯一， 0-唯一  1-不唯一

  • Key_name：索引名称

  • Seq_in_index 表示该字段在索引中的位置，单列索引的话该值为1，组合索引为每个字段在索引定义中的顺序

  • Column_name：表示定义索引的列字段

  • Collation  列以什么方式存储在索引中，大概意思就是字符序。A-升序或NULL-无分类。   

  • Cardinality  基数的意思，表示索引中唯一值的数目的估计值。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。我们知道某个字段的重复值越少越适合建索引，所以我们一般都是根据Cardinality来判断索引是否具有高选择性，如果这个值非常小，那就需要重新评估这个字段是否适合建立索引。因为MySQL数据库中有各种不同的存储引擎，而每种存储引擎对于B+树索引的实现又各不相同。所以对Cardinality统计时放在存储引擎层进行的，至于它是如何统计更新的在这里就不再做更深入的介绍了。

  • Packed指示关键字如何被压缩。如果没有被压缩，则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案。 

  • Sub_part：前置索引的意思，如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

  • Null：表示该字段是否能为空值

  • Index_type：表示索引类型

  • Comment ：注释

  • Index_comment  ：注释

1、主键索引

数据列不允许重复，不允许为NULL，一个表只能有一个主键。

2、唯一索引

 数据列不允许重复，必须唯一，允许为NULL值，一个表允许多个列创建唯一索引，如果是组合索引，则列值的组合必须唯一。

ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
mysql> alter table bank_account add unique(name);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from bank_account;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| bank_account |          0 | PRIMARY  |            1 | Id          | A         |           2 | NULL     | NULL   |      | BTREE      |         |               | YES     | NULL       |
| bank_account |          0 | Name     |            1 | Name        | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set

3、普通索引

基本的索引类型，没有唯一性的限制，允许为NULL值。组合索引 INDEX：即一个索引包含多个列，多用于避免回表查询。

ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引
mysql> alter table bank_account add index name_age(name,age);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from bank_account;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| bank_account |          0 | PRIMARY  |            1 | Id          | A         |           2 | NULL     | NULL   |      | BTREE      |         |               | YES     | NULL       |
| bank_account |          0 | Name     |            1 | Name        | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| bank_account |          1 | name_age |            1 | Name        | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| bank_account |          1 | name_age |            2 | Age         | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set

4、全文索引

全文索引 FULLTEXT：也称全文检索，是目前搜索引擎使用的一种关键技术。

ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引
mysql> alter table bank_account add fulltext(name);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 1

mysql> show index from bank_account;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| bank_account |          0 | PRIMARY  |            1 | Id          | A         |           2 | NULL     | NULL   |      | BTREE      |         |               | YES     | NULL       |
| bank_account |          1 | Name     |            1 | Name        | NULL      |           2 | NULL     | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set

索引一经创建不能修改，只能删除重建。

DROP INDEX index_name ON table_name;删除索引。

三、聚集索引和非聚集索引

聚集索引以及非聚集索引用的是B+树索引

1、聚集索引

聚集索引：将数据存储与索引放到了一块，叶子节点中存放的即为整张表的行记录数据，找到索引也就找到了数据，避免了回表查询。

（1）主键索引就是聚集索引吗？

在mysql数据库innodb引擎里面，主键的确就是聚集索引。但是myisam引擎里面主键不是聚集索引。另外在sql server中还可以显示的指定聚集索引。聚集索引决定了数据库的物理存储结构，而主键只是确定表格逻辑组织方式。这两者不可混淆！

  • MyISAM存储引擎在创建表的时候会在硬盘上生成以.frm.MYD.MYI结尾的三个文件，frm结尾的是表结构，MYD结尾的是数据文件，MYI结尾的就是索引文件，也就是说数据存储和索引是分开的。
  • 对于InnoDB引擎，创建一个表，在硬盘上会生成.frm.idb结尾的两个文件，它的索引和数据都在.idb结尾的文件里面，表在磁盘上的存储结构是树状结构，整个表就变成了一个索引，这个索引就是聚集索引。

（2）对于Innodb，主键毫无疑问是一个聚集索引，但是当一个表没有主键，或者没有一个索引，Innodb会如何处理呢。请看如下规则：

  • 如果一个主键被定义了，那么这个主键就是作为聚集索引
  • 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引
  • 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。
  • 自增主键会把数据自动向后插入，避免了插入过程中的聚集索引排序问题。聚集索引的排序，必然会带来大范围的数据的物理移动，这里面带来的磁盘IO性能损耗是非常大的。 而如果聚集索引上的值可以改动的话，那么也会触发物理磁盘上的移动，于是就可能出现page分裂，表碎片横生。所以不应该修改聚集索引。

2、非聚集索引

将数据存储与索引分开存储，索引结构的叶子节点指向了数据的对应行地址，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据。

  • 非聚集索引=辅助索引=二级索引=普通索引，可以理解为同一个东西，不同的叫法
  • 唯一索引、普通索引、前缀索引等都是二级索引。　

3、什么时候使用聚集索引和非聚集索引

img

 

四、三种常见的索引算法

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有哈希表，有序数组以及搜索树。

1、哈希表

哈希表是一种常见的数据结构，哈希表由一个Key和Value组成，通过Key可以O(1)的复杂度快速的找到其对应的Value。实现原理上，当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置。

img

如果多个Key算出的Hash值一样，即发生了hash冲突，那么可以通过一个链表来进行维护。但是，如果hash冲突较多，链表太长的时候也会影响到查找速度，所以JDK8之后做了一些改进措施，当链表长度大于8的时候，会在链表的后边维护一个红黑树来进一步提高查询速度。 Hash表底层由数组+链表+红黑树的结构实现，数据并不是有序的，所以只适合做等值查询，例如=,<=>操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。但是哈希索引的区间查询的效率很低。

2、有序数组：

有序数组在等值查询和区间查询的时候效率都很高，但是只适合于静态的数据，如果由更新数据的需求，则成本太高。

3、搜索树：

树可以有二叉，也可以有多叉。二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是索引不止存在内存中，还要写到磁盘上，为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。为了每次查找数据时把磁盘IO次数控制在一个很小的数量级，B+树应运而生（B+树是通过二叉查找树，再由平衡二叉树，B树演化而来）。B+Tree是最常用的mysql数据库索引算法，也是Innodb默认的索引算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量， 例如：

-- 只要它的查询条件是一个不以通配符开头的常量
select * from user where name like 'jack%'; 
-- 如果一通配符开头，或者没有使用常量，则不会使用索引，例如： 
select * from user where name like '%jack';

如上图，是一颗b+树，最上层是树根，中间的是树枝，最下面是叶子节点。这里只说一些重点，浅蓝色的块我们称之为一个磁盘块或者叫做一个block块，这是操作系统一次IO往内存中读的内容，一个块对应四个扇区，可以看到每个磁盘块包含几个数据项（深蓝色所示，一个磁盘块里面包含多少数据，一个深蓝色的块表示一个数据，其实不是数据，后面有解释）和指针（黄色所示，看最上面一个，p1表示比上面深蓝色的那个17小的数据的位置在哪，看它指针指向的左边那个块，里面的数据都比17小，p2指向的是比17大比35小的磁盘块），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

（1）B+树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。除了叶子节点，其他的树根啊树枝啊保存的就是数据的索引，他们是为你建立这种数据之间的关系而存在的。

（2）B+树性质

  • 索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h或者说层级，这个高度或者层级就是你每次查询数据的IO次数，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

​ 所以我们需要将树建的越低越好，因为每个磁盘块的大小是一定的，那么意味着我们单个数据库里面的单个数据的大小越大越好还是越小越好，你想啊，你现在叶子节点的磁盘块，两个数据就沾满了，你数据要是更大的话，你这一个磁盘块就只能放一个数据了，这样随着你数据量的增大，你的树就越高啊，我们应该想办法让树的层数低下来，效率才高啊，所以我们应该让每个数据的大小尽可能的小，那就意味着，你每个磁盘块存的数据就越多，你树的层级就越少啊，树就越低啊，对不对。并且数据的数量越大，你需要的磁盘块越多，磁盘块越多，你需要的树的层级就越高，所以我们应该尽可能的用更少的磁盘块来装更多的数据项，这样树的高度才能降下来，怎么才能装更多的数据项啊，当然是你的数据项越小，你的磁盘块盛放的数据量就越多了，所以如果一张表中有很多的字段，我们应该用什么字段来建立索引啊，如果你有id字段、name字段、描述信息字段等等的，你应该用哪个来建立索引啊，当然是id字段了，你想想对不对，因为id是个数字，占用空间最少啊。

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

（3）B树和B+树的区别

  • 在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。

  • B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。

（4）使用B树的好处 

B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

（5）使用B+树的好处

由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间。

总结如下：

  • 所有叶子节点形成有序链表，便于范围查找和遍历
  • 查询的IO次数更少
  • 所有查询都要查找到叶子节点，查询性能稳定

（6）Hash索引和B+树所有有什么区别或者说优劣呢?

首先要知道Hash索引和B+树索引的底层实现原理：hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

那么可以看出他们有以下的不同：

  • hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。

  • hash索引不支持使用索引进行排序，原理同上。

  • hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。

  • hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。

  • hash索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

（7）数据库为什么使用B+树而不是B树

  • B树只适合随机检索，而B+树同时支持随机检索和顺序检索。

  • B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；

  • B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当稳定。

  • 便于遍历和范围查询。B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。

  • 增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

五、索引注意事项

1、索引设计的原则

  • 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  • 较频繁作为查询条件的字段才去创建索引
  • 更新频繁字段不适合创建索引
  • 适合索引的列是出现在where子句中的列，或者连接子句中指定的列
  • 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
  • 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
  • 定义有外键的数据列一定要建立索引。
  • 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
  • 对于定义为text、image和bit的数据类型的列不要建立索引。
  • 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
  • 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

2、索引的命令

（1）创建索引的三种方式

  • 方式一：创建表CREATE TABLE时创建索引
DROP TABLE IF EXISTS `bank_account`;
CREATE TABLE bank_account(
    Id INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    Age INT(5) DEFAULT 0 COMMENT '年龄',
    Name VARCHAR(20) DEFAULT NULL COMMENT '名称',
    Money VARCHAR(20) DEFAULT NULL COMMENT '金额',
    INDEX ageIndex(Age),-- 普通索引
    UNIQUE INDEX uniqName(Name),-- 唯一索引
    PRIMARY KEY(Id),-- 主键索引
    FULLTEXT KEY fullMoney (`Money`) -- 全文索引
)ENGINE=INNODB auto_increment=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

查看新建表中的索引：

mysql> SHOW INDEX from bank_account;
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| bank_account | 0 | PRIMARY | 1 | Id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| bank_account | 0 | uniqName | 1 | Name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| bank_account | 1 | ageIndex | 1 | Age | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| bank_account | 1 | fullMoney | 1 | Money | NULL | 0 | NULL | NULL | YES | FULLTEXT | | | YES | NULL |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set

mysql>

  • 方式二：使用ALTER TABLE命令去增加索引
ALTER TABLE bank_account ADD INDEX ageName (Age);

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。

  • 方式三：使用CREATE INDEX命令创建
CREATE INDEX index_name ON table_name (column_list);

CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）

（2）删除索引

根据索引名删除普通索引、唯一索引、全文索引：

alter table 表名 drop KEY 索引名

删除主键索引：alter table 表名 drop primary key（因为主键只有一个）。这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引）：

 

需要取消自增长再行删除：

mysql> alter table bank_account MODIFY Id int, drop PRIMARY KEY;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

但通常不会删除主键，因为设计主键一定与业务逻辑无关。

3、使用索引查询一定能提高查询的性能吗

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢，所以使用索引查询不一定能提高查询性能。

4、百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件，所以当我们对数据的增加、修改、删除,都会产生额外的对索引文件的操作，这些操作需要消耗额外的IO，会降低增/改/删的执行效率。所以在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。删除步骤如下：

（1）想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）

（2）然后删除其中无用数据（此过程需要不到两分钟）

（3）删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。

（4）与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

5、前缀索引

  • 语法：index(field(10))，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引。
  • 前提：前缀的标识度高。比如密码就适合建立前缀索引，因为密码几乎各不相同。
  • 实操的难度：在于前缀截取的长度。
  • 我们可以利用select count(*)/count(distinct left(password,prefixLen));，通过从调整prefixLen的值（从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了（表示一个密码的前prefixLen个字符几乎能确定唯一一条记录）

6、什么是最左前缀原则？什么是最左匹配原则

  • 顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
  • 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  • =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

7、B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据

  • 当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。
  • 如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。
  • 总结：由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

8、非聚簇索引一定会回表查询吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，即满足覆盖索引原则，那么就不必再进行回表查询。举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

9、联合索引是什么？为什么需要注意联合索引中的顺序？

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。具体原因为:

MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整

10、回表

在INNODB中，主键索引比普通索引得查询速度快，那么为什么会快呢？因为普通索引树上并没有完整得数据，找到主键后，需要返回到主键索引树上获取所需得数据，这个操作被称为回表。

我们来看一个例子，分别使用主键索引和普通索引：

  • 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
  • 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。

既然说到了回表，有没有聪明的小伙伴想到，如果我没有主键或者我把主键给删除了，是不是就不可以回表了？在InnoDB中，当你没有设主键或者主键突然被删除时，会自动建立一个主键rowid，保证回表等功能的正常运行。

11、 创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?

MySQL提供了explain命令来查看语句的执行计划，MySQL在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息。可以通过其中和索引有关的信息来分析是否命中了索引，例如possilbe_key、key、key_len等字段，分别说明了此语句可能会使用的索引，实际使用的索引以及使用的索引长度。

12、MySQL在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?

  • 使用不等于查询

  • 列参与了数学运算或者函数

  • 在字符串like时左边是通配符，类似于'%aaa'

  • 当mysql分析全表扫描比使用索引快的时候不使用索引

  • 当使用联合索引，前面一个条件为范围查询，后面的即使符合最左前缀原则，也无法使用索引

六、使用索引的建议和规范

1、主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。

  • 说明：pk_ 即primary key；uk_ 即 unique key；idx_ 即index的简称。

2、业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

  • 说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

3、在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

  • 说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

4、页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

  • 说明：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

5、如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。

  • 正例：where a=? and b=? order by c; 索引：a_b_c
  • 反例：索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引a_b无法排序。

6、利用覆盖索引来进行查询操作，避免回表。

  • 说明：如果一本书需要知道第11章是什么标题，会翻开第11章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。
  • 正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用explain的结果，extra列会出现：using index。

7、建组合索引的时候，区分度最高的在最左边。

  • 正例：如果where a=? and b=? ，a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。
  • 说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where a>? and b=? 那么即使a的区分度更高，也必须把b放在索引的最前列。

8、防止因字段类型不同造成的隐式转换，导致索引失效。

9、创建索引时避免有如下极端误解：

  • 宁滥勿缺。认为一个查询就需要建一个索引。
  • 宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。
  • 抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

 

posted @ 2020-12-21 17:37  搬砖滴  阅读(122)  评论(0)  编辑  收藏  举报