MySQL索引

知识库大约 6 分钟

索引：是帮助 MySQL高效获取数据的数据结构，一般默认都是使用 B+树结构组织的索引。

索引的类型

-- 主键索引：索引列中的值必须是唯一的，不允许有空值。
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
-- 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
ALTER TABLE table_name ADD INDEX index_name (column_name);
-- 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
CREATE UNIQUE INDEX index_name ON table(column_name);
-- 创建全文索引（很少使用），只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引
ALTER TABLE `t_fulltext` ADD FULLTEXT INDEX `idx_content`(`content`);
-- 前缀索引（很少使用），指定索引列的长度，但是数值类型不能指定
ALTER TABLE table_name ADD INDEX index_name (column1(length));
-- 组合索引的使用，需要遵循最左前缀原则。
ALTER TABLE table_name ADD INDEX index_name (column1,column2);
-- 删除索引
DROP INDEX index_name ON table;
-- 查看索引
SHOW INDEX FROM table_name \G;
-- 查看索引 B+树的高度
SELECT b.name, a.name, index_id, type, a.space , a.PAGE_NO FROM information_schema.INNODB_SYS_INDEXES a, information_schema.INNODB_SYS_TABLES b WHERE a.table_id = b.table_id AND a.space <> 0;

索引的数据结构

数据结构示例网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.htmlopen in new window

hash

特点：key-value 形式的数据结构。查询速度非常快。直接通过可以找到 value。

优点：速度快。时间复杂度 O(1)

缺点：不适合范围查询。不适合做索引。
二叉查找树

特点：左子树一定是小于根节点，右子树一定是大于根节点。时间复杂度 O(log2n)

问题：根节点的选取最好是所有数据中中间数值。否则左子树和右子树高度可能不一致。最差情况下退化成链表。
二叉平衡树查找树

特点：向树中增加节点，动态调整树的平衡状态，要求左右子树的高度相差最大不能超过 1。

问题：不断的添加数据，二叉树会不断的调整状态。调整的过程也是非常耗时的。总体上性能也不太好。
B 树（多叉平衡查找树）

特点：每个节点中都保存数据；数据出现在中间节点中，就不会出现在叶子节点中。

问题：使用 B 树做范围查询效率不高。
B+树（对 B 树的改进）
- b+树中除叶子节点外，中间节点不保存数据，中间节点保存的是主键。
- b+树中所有的数据都存放在叶子节点中。
- b+树中叶子节点之间有双向指针，形成一个双向链表。
- b+树适合于等值查询也适合于范围查询。在 mysql 中所有的索引都是 b+tree 结构。

索引创建原则

频繁出现在 where 条件判断，order 排序，group by 分组字段
select 频繁查询的列，考虑是否需要创建联合索引（覆盖索引，不回表）
多表 join 关联查询，on 字段两边的字段都要创建索引

索引优化建议

表记录很少不需创建索引（索引是要有存储的开销）。
一个表的索引个数不能过多。
频繁更新的字段不建议作为索引。
不建议使用区分度低的字段（比如性别）作为建索引。（仅供参考）
在 InnoDB 存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段。
不建议用无序的值作为索引。例如身份证、UUID。
尽量创建组合索引，而不是单列索引。

索引使用建议

全值匹配我最爱。
最佳左前缀法则。（带头索引不能死，中间索引不能断）
不要在索引上做计算。不要进行这些操作：计算、函数、自动/手动类型转换，不然会导致索引失效而转向全表扫描。
范围条件右边的列失效。不能继续使用索引中范围条件（bettween、<、>、in 等）右边的列。
尽量使用覆盖索引。尽量使用覆盖索引（只查询索引的列），也就是索引列和查询列一致，减少 select *。
索引字段上不要使用不等。会导致索引失效而转向全表扫描。
索引字段上不要判断 null。会导致索引失效而转向全表扫描。
索引字段使用 like 不以通配符开头。
索引字段字符串要加单引号。索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描。
索引字段不要使用 or。会导致索引失效而转向全表扫描。

# 优化口诀
全值匹配我最爱，最左前缀要遵守。
头大哥不能死，中间兄弟不能段。
索引列上少计算，范围之后全失效。
like百分写最右，覆盖索引不写*。
不等空值还有or，索引失效要少用。
字符串引号不可丢，SQL高级也不难。

经典面试题

InnoDB 一棵 B+树可以存放多少行数据？

这个问题的简单回答是：约2千万行。
-- 在计算机中，磁盘存储数据最小单元是扇区，一个扇区的大小是512字节。
-- Liunx文件系统中，最小单位是块，一个块大小默认是4k。`getconf PAGE_SIZE`
-- InnoDB存储引擎最小储存单元是页，一页大小默认是16k。`show global status like "Innodb_page_size"`
-- 假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16*1024B/14B = 1170
-- 一棵高度为2的B+树，能存放1170 * 16=18720 条这样的数据记录
-- 同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400 条这样的数据记录

为什么索引结构默认使用 B+树，而不是 B-Tree，Hash 哈希，二叉树，红黑树？

Hash哈希，只适合等值查询，不适合范围查询。
二叉树，可能会特殊化为一个链表，相当于全表扫描。
红黑树，是一种特化的平衡二叉树，MySQL 数据量很大的时候，索引的体积也会很大，内存放不下的而从磁盘读取，树的层次太高的话，读取磁盘的次数就多了。
B-Tree，叶子节点和非叶子节点都保存数据，相同的数据量，B+树更矮壮，也是就说，相同的数据量，B+树数据结构，查询磁盘的次数会更少。

B-树和 B+树的区别

B-树内部节点是保存数据的;而B+树内部节点是不保存数据的，只作索引作用，它的叶子节点才保存数据。
B+树相邻的叶子节点之间是通过链表指针连起来的，B-树却不是。
查找过程中，B-树在找到具体的数值以后就结束，而B+树则需要通过索引找到叶子结点中的数据才结束
B-树中任何一个关键字出现且只出现在一个结点中，而B+树可以出现多次。

MySQL索引

# 索引的类型

# 索引的数据结构

# 索引创建原则

# 索引优化建议

# 索引使用建议

# 经典面试题

索引的类型

索引的数据结构

索引创建原则

索引优化建议

索引使用建议

经典面试题