后端学习——Mysql进阶索引

前言前置学习内容一、索引介绍1.介绍

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。。

2.优点3.缺点

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。因此应该只为最经常查询和最经常排序的数据列建立索引。MySQL里同一个数据表里的索引总数限制为16个。

二、索引结构1.二叉树

二叉树，每个节点只能有两个子节点，如果是一颗单边二叉树，查询某个节点的次数与节点所处的高度相同，时间复杂度为O(n)；如果是一颗平衡二叉树，查找效率高出一半，时间复杂度为O(Log2n)。并且二叉树还有另一个坏处，二叉树上的每一个节点都是数据节点，那么对于一个比较高的数如果要获取最下面的数据遍历的节点数将会很消耗性能。

2.红黑树

想要了解红黑树需要了解平衡二叉树（图解：什么是平衡二叉树？），红黑树是平衡二叉树的一种变形。红黑树牺牲了平衡二叉树一定的平衡性，来提高数据插入删除效率。平衡二叉树删除插入数据可能需要进行多次旋转然而红黑树仅需1次-3次旋转。红黑树其实是一个披着二叉树皮的2-3-4结构树，仅需对红黑树的红节点进行合并，就可以得到一个2-3-4结构树，2-3-4结构树叶子节点多，树高矮，以使查找效率高。

红黑树需要遵循以下规则：

节点不是红色就是黑色，根节点是黑色红黑树的叶子节点并非传统的叶子节点，红黑树的叶子节点是null节点（空节点）且为黑色一个节点为红色，则其两个子节点必须是黑色的（根到叶子的所有路径，不可能存在两个连续的红色节点）每个节点到叶子节点的所有路径，都包含相同数目的黑色节点（相同的黑色高度）3.哈希表

存储结构是key-value形式存在数组中，然后通过hash函数（key）得到一个值，这个值就是它们的索引。当取数据的时候，key通过hash得到索引值，直接找就行了，复杂度为o(1)。

优点：查找速度快。

缺点：

4.B-Tree

B树是二叉树的升级版，又叫平衡多路查找树。它和平衡二叉树的区别在于：

平衡二叉树最多两个子树mysql索引表，而 B 树每个节点都可以有多个子树，M 阶 B 树表示每个节点最多有M个子树。平衡二叉树每个节点只有一个数据和两个指向孩子的指针，而 B 树每个中间节点有 k-1 个关键字（可以理解为数据）和 k 个子树（ k 介于阶数 M 和 M/2 之间，M/2 向上取整）。所有叶子节点均在同一层、叶子节点除了包含关键字和关键字记录的指针外也有指向其子节点的指针，只不过其指针地址都为 null 。

优点：

缺点：

5.B+Tree

优点：（与B-Tree区别）

6.总结

索引原理

最佳实践

三、索引分类1.主键索引

主键索引也就是我们说的聚集索引是一种唯一性索引。上面说过主键索引是基于主键来创建的 B+ 树索引结构，如果没有指定主键，也找不到任何一列不重复的列可以作为主键的情况下，InnoDB 会新增一个隐藏列 RowId 作为主键继而创建聚集索引。

创建

ALTER TABLE 表名 MODIFY 字段 类型 约束;
ALTER TABLE 表名 ADD PRIMARY KEY (字段);

删除

ALTER TABLE 表名 DROP PRIMARY KEY;

2.二级索引

二级索引就是指除了主键索引外的索引。主键索引和所有的二级索引都是各自维护各自的 B+ 树结构，但是有个不同的地方在于，二级索引的叶子节点存储的不是数据，而是主键索引对应的主键值。

即二级索引不再保存一份 data 数据，而是去主键索引中查数据。那么对于二级索引查找一条数据索要做的操作就是：

首先在二级索引中找到叶子节点对应的数据主键值；根据这个主键值去聚集索引中找到真正对应的数据行。

所以这里需要两次 B+ Tree 查找。

3.普通索引

这是最基本的索引，它没有任何限制。

创建

CREATE INDEX 索引名 ON 表名 (列名(length),...)
ALTER table 表名 ADD INDEX 索引名(列名(length),...)
CREATE TABLE mytable([...] INDEX [索引名] (列名(length)，...));  

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

删除

DROP INDEX [索引名] ON 表名; 
ALTER TABLE 表名 DROP INDEX 列名;

4.唯一性索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX 索引名 ON 表名 (列名(length),...)
ALTER table 表名 ADD UNIQUE INDEX 索引名(列名(length),...)
CREATE TABLE mytable([...] UNIQUE INDEX [索引名] (列名(length)，...));  

删除同普通索引

5.联合索引

有的时候我们会对多个列建立一个索引，这种索引被称为联合索引。而关于联合索引的建立和使用需要严格要遵循 “左前匹配原则”。

什么是左前匹配原则？

其实就是字面意义上的从建立索引的第一个字段开始先匹配查询条件，如果当前查询条件不是第一个字段那么就不会走该索引。例如

创建一个索引idx_a_b_c=(a,b,c)
select  *  from test where b = '10';

这不符合“左前匹配原则” 不会走索引查询，最终还是全表扫描。原因就在于联合索引的结构上。上面对 a,b,c 三个字段建立索引， 那么对应的 B+ Tree 索引结构每个节点其实是按照三个字段的前后顺序排列的，即 a 字段检索在最前面，然后是b，然后是c。如果你的查询不是按照这个顺序来检索，是不会被这个索引识别的。

又例如：

select  *  from test where a="0" and b = '10' and d="1" and c="2";

这里ab符合”左前匹配原则“进行索引查询，但是在经历了d的查询之后，到了c就不会使用索引了，因为d的查询已经将索引的顺序打乱了，从 d 条件过后就没有办法直接使用联合索引。

6.覆盖索引

覆盖索引简单来说就是只查询索引就能获取到数据不必再回表查询，换句话说要查询的列已经被索引列覆盖。

使用覆盖索引有如下优点：

索引项通常比记录要小，所以 MySQL 访问更少的数据；索引都按值的大小顺序存储，相对于随机访问记录，需要更少的 I/O；大多数据引擎能更好的缓存索引。比如 MyISAM 只缓存索引;覆盖索引对于 InnoDB 表尤其有用，因为 InnoDB 使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。覆盖索引不能是任何索引，只有 B Tree 索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引( Memory 和 Falcon 就不支持)。

例如：

CREATE INDEX 索引名 ON 表名 (id,name)
select name from test where id = 1;
索引中因为已经包含了name，可以之间从索引中取得name数据就无需在进行主键索引，从而提高效率。

四、索引优化1.Explain优化查询检测

EXPLAIN可以帮助开发人员分析SQL问题，explain显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在select语句前加上Explain就可以了：

Explain select * from blog where false;

mysql在执行一条查询之前，会对发出的每条SQL进行分析，决定是否使用索引或全表扫描如果发送一条select * from blog where false，Mysql是不会执行查询操作的，因为经过SQL分析器的分析后MySQL已经清楚不会有任何语句符合操作。

2.如何创建主键3.如何创建二级索引4.索引字段进行运算5.索引查询6.limit分页优化

语法： ELECT * FROM table LIMIT [offset,] rows 或者 rows OFFSET offset

SELECT * FROM table LIMIT 5,10; //检索记录行6-15
SELECT * FROM table LIMIT 10 OFFSET 5
SELECT * FROM table LIMIT 95,-1; //检索记录行96-last
SELECT * FROM table LIMIT 95 OFFSET -1
SELECT * FROM table LIMIT 5; //检索前5个记录行

不同offset大小使用方式

1.offset较小时

select * from table limit 5,10

直接使用limit即可，其他优化方案效果差异不大

2.offset较大时

Select * From table Where id >=(  
Select id From table Order By id limit 10000,1  
) limit 10

offset较大时，使用这种查询方案效率会高！这是用了id做索引的结果。

用id作为数据表的主键：

select id from table limit 10000,10

效率没有因为offset而参数太大影响，因为主键作为索引结果。

分页数据性能优化：

附录其他学习内容参考文献

（编辑：武汉站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!