MySQL索引

【MySQL索引】

https://cloud.tencent.com/developer/article/1125452

https://www.jianshu.com/p/7c0709976f40

[1] 什么是MySQL索引？

索引（Index）的建立是为了优化数据库查询性能而建立的数据结构。

[2] B+树相对于B-树的优势？

数据库的索引结构是B+树.

1. 相比于其他查找树（B-树），B+树单一节点存储更多的关键字，也就是说B+树更加矮胖，使得查询时的平均IO次数更少，查询效率更高；
2. B+树的关键字信息全部存储在叶子结点中，非叶子结点只存储索引地址.所以查询都要从根节点查找到叶子节点，也就是说查询路径长度相同，查询性能稳定；
3. B+树的所有叶子节点形成有序链表，便于范围查询.

[3] MySQL索引实现

1. MyISAM索引实现（非聚集索引）

MyISAM引擎使用B+Tree作为索引结构，叶节点data域存放数据记录的地址。MyISAM的索引文件仅仅保存数据记录的地址。

2. InnoDB索引实现（聚集索引）

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同，第一个重大区别是InnoDB的数据文件本身就是索引文件。

InnoDB为什么必须要求表必须有主键？且是单调自增的？

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择

什么情况下可以用到B树索引

(1) 定义有主键的列一定要建立索引。因为主键可以加速定位到表中的某行

(2) 定义有外键的列一定要建立索引。外键列通常用于表与表之间的连接，在其上创建索引可以加快表间的连接

(3) 对于经常查询的数据列最好建立索引。

① 对于需要在指定范围内快速或频繁查询的数据列，因为索引已经排序，其指定的范围是连续的，查询可以利用索引的排序，加快查询的时间

② 经常用在 where子句中的数据列，将索引建立在where子句的集合过程中，对于需要加速或频繁检索的数据列，可以让这些经常参与查询的数据列按照索引的排序进行查询，加快查询的时间

[4] 聚簇索引（非聚簇索引）

聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法.特点是存储数据的顺序和索引顺序一致.
聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针.

聚集索引：一个表中只能有一个，聚集索引的顺序与数据真实的物理存储顺序一致.查询速度贼快，聚集索引的叶子节点上是该行的所有数据 ，数据索引能加快范围查询(聚集索引的顺序和数据存放的逻辑顺序一致).主键!=聚集索引.

辅助索引(非聚集索引)：一个表中可以有多个，叶子节点存放的不是一整行数据，而是键值，叶子节点的索引行中还包含了一个’书签’，这个书签就是指向聚簇索引的一个指针，从而在聚簇索引树中找到一整行数据.

聚集索引与辅助索引的区别：叶子节点是否存放的为一整行数据

[6] 最左前缀原则

link1，link2

1. 覆盖索引：如果在普通索引树上的查询已经直接提供了结果，不需要回表操作，这样的普通索引叫做覆盖索引.覆盖索引的使用可以显著提高查询效率，是常见的MySQL性能优化手段.

2. 索引的最左前缀原则：MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引.在联合索引的情况下，不需要索引的全部定义，只要满足最左前缀，就可以利用索引来加快查询速度.这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符.最左前缀原则的利用也可以显著提高查询效率，是常见的MySQL性能优化手段.

1. 一个 2 列的索引 (name， age)，对 (name)、(name， age) 上建立了索引；
2. 一个 3 列的索引 (name， age， sex)，对 (name)、(name， age)、(name， age， sex) 上建立了索引.

3. 索引下推：在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表.到主键索引上找出数据行，再对比字段值.MySQL5.6引入的索引下推优化，（联合索引前提）可以在索引遍历过程中，对索引中包含的其余字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，提升查询效率.

[7] 联合索引

MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列遵循最左前缀规则，对where，order by，group by 都生效.

[8] 覆盖索引

SQL语句只通过索引，就取到了所需要的数据，这个过程就叫做索引覆盖，换句话说查询列要被所使用的索引覆盖。

解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息。

注：遇到以下情况，执行计划不会选择覆盖查询。

• select选择的字段中含有不在索引中的字段 ，即索引没有覆盖全部的列。

• where条件中不能含有对索引进行like的操作。

[9] 什么情况下索引会失效？

即查询不走索引

下面列举几种不走索引的 SQL 语句：

\1. 索引列参与表达式计算：

SELECT 'sname' FROM 'stu' WHERE 'age' + 10 = 30;

\2. 函数运算：

SELECT 'sname' FROM 'stu' WHERE LEFT('date'，4) < 1990; 

\3. %词语%–模糊查询：

SELECT * FROM 'manong' WHERE `uname` LIKE '码农%' -- 走索引 
SELECT * FROM 'manong' WHERE `uname` LIKE "%码农%" -- 不走索引 

\4. 字符串与数字比较不走索引：

CREATE TABLE 'a' ('a' char(10)); EXPLAIN SELECT * FROM 'a' WHERE 'a'="1" -- 走索引 EXPLAIN SELECT * FROM 'a'WHERE 'a'=1 -- 不走索引，同样也是使用了函数运算 

\5. 查询条件中有 or ，即使其中有条件带索引也不会使用.换言之，就是要求使用的所有字段，都必须建立索引：

select * from dept where dname='xxx' or loc='xx' or deptno = 45;

\6. 正则表达式不使用索引.

\7. MySQL 内部优化器会对 SQL 语句进行优化，如果优化器估计使用全表扫描要比使用索引快，则不使用索引.

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

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

[10] 什么情况下不建议建索引

• 对于那些查询中很少涉及的列、重复值比较多的列不要建立索引 例如，在查询中很少使用的列，有无索引并不能提高查询的速度，相反增加了系统维护时间和消耗了系统空间；又如，“性别”列只有列值“男”和“女”，增加索引并不能显著提高查询的速度 对于定义为text、image和bit数据类型的列不要建立索引。因为这些数据类型的数据列的数据量要么很大，要么很小，不利于使用索引。
• 表记录比较少 例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引
• 索引的选择性较低 所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值 Index Selectivity = Cardinality / #T 显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。情况下不建议建索引
  • 对于那些查询中很少涉及的列、重复值比较多的列不要建立索引 例如，在查询中很少使用的列，有无索引并不能提高查询的速度，相反增加了系统维护时间和消耗了系统空间；又如，“性别”列只有列值“男”和“女”，增加索引并不能显著提高查询的速度 对于定义为text、image和bit数据类型的列不要建立索引。因为这些数据类型的数据列的数据量要么很大，要么很小，不利于使用索引。
  • 表记录比较少 例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引
  • 索引的选择性较低 所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值 Index Selectivity = Cardinality / #T 显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。

[11] Hash索引

因为底层是哈希表，数据存储在哈希表中顺序是没有关联的，所以他不适合范围查找，如果要范围查找就需要全表扫描，他只适合全值扫描；简单的来说就是hash索引适合等值查找，不适合范围查找。