MYSQL数据库进阶篇——索引

一.索引概述

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引的优缺点：

二.索引结构

常说的索引没有特殊说明的话，默认值B+树结构d的索引。

• Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。
• 二叉树就是为了保证每次查找都可以这折半而减少IO次数，但在顺序插入时会形成链表，查询性能大大降低，层级越深，检索越慢。
• 红黑树层级越深，检索越慢。
• BTree是多叉平衡二叉树，减小了树的深度，但不支持多范围的快速查询。
• B+Tree是BTree的优化，增加了一个指向相邻叶子节点的指针，B+Tree与BTree的本质区别在于非叶子节点是否存储数据。

思考：

1.为什么InnoDB存储引擎使用B+Tree索引结构？

解答：①相比二叉树，层级更少，效率更高。

②相比BTree，无论叶子结点还是非叶子节点都会保存数据，这会导致一页中存储的键值减少，指针也减少，如果要保存大量数据，只能增加树的高度，导致性能降低。

三.索引分类

在InnoDB存储引擎中，根据存储形式可分为以下两种：

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将用第一个唯一索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，InnoDB会自动生成一个row作为隐藏的聚集索引。

四.索引语法

例如:

五.性能分析

1.查看执行频次

2.慢查询日志

慢查询日志记录了所有执行时间超过指定参数的所有SQL语句的日志，默认是关闭，需在MYSQL的配置文件（/etc/my,cnf)中配置如下信息：

3.show profiles

show profiles能够中做SQL优化时帮助我们了解具体的时间耗费，通过have_profiling参数，能够看到MySQL是否支持profile操作：

如：

4.explain

explain或desc命令获取SQL如何执行select语句的信息，一般直接在select前面加上explain。

六.使用规则

1.最左前缀法则

最左前缀法则针对的是联合索引（A,B,C），指查询从最左列开始，并且不跳过索引中的列，如果跳过某一列，索引将部分失效，例如：有AC无B，则A只有A索引生效，有AB无C则C失效，如果没有A则全部失效。

2.索引失效

1.索引列运算：如果在索引列进行运算操作，索引将失效。

例如：

2.字符串不加引号：字符串类型字段使用时，不加引号，索引将失效。

3.模糊匹配：如果是尾部模糊匹配，索引不失效，如果头部模糊匹配，索引将失效。

例如：

4.or连接的条件：or分开的条件（A or B），如果a条件有索引，B没有索引，索引会全部失效

5.数据分布影响：如果MYSQL评估使用索引比全表更慢，则不会使用索引。

3.SQL提示

SQL提示是优化数据库的重要手段，就是在SQL语句中加入一些人为的提示来达到优化操作的目的，一般加在where前面。

4.覆盖索引

尽量使用覆盖索引，减少使用select *。

using index condition:查询使用了索引，但需要回表查询。

using where，using index：查询使用了索引，但需要的数据在索引列能够找到，不需要回表查询。

5.前缀索引

当字段为字符串类型，且索引很长的字符串时，会让索引变得更大，查询时会浪费大量磁盘IO，影响查询效率，此时可以只将字符串部分前缀作为索引来建立，这样节约了索引空间，提高索引效率

这里的colum是字段名。

6.设计原则