MySQL索引

知识库数据库MySQLMySQL大约 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 字段两边的字段都要创建索引

索引优化建议

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

索引使用建议

  1. 全值匹配我最爱。
  2. 最佳左前缀法则。(带头索引不能死,中间索引不能断)
  3. 不要在索引上做计算。不要进行这些操作:计算、函数、自动/手动类型转换,不然会导致索引失效而转向全表扫描。
  4. 范围条件右边的列失效。不能继续使用索引中范围条件(bettween、<、>、in 等)右边的列。
  5. 尽量使用覆盖索引。尽量使用覆盖索引(只查询索引的列),也就是索引列和查询列一致,减少 select *。
  6. 索引字段上不要使用不等。会导致索引失效而转向全表扫描。
  7. 索引字段上不要判断 null。会导致索引失效而转向全表扫描。
  8. 索引字段使用 like 不以通配符开头。
  9. 索引字段字符串要加单引号。索引字段是字符串,但查询时不加单引号,会导致索引失效而转向全表扫描。
  10. 索引字段不要使用 or。会导致索引失效而转向全表扫描。
# 优化口诀
全值匹配我最爱,最左前缀要遵守。
头大哥不能死,中间兄弟不能段。
索引列上少计算,范围之后全失效。
like百分写最右,覆盖索引不写*。
不等空值还有or,索引失效要少用。
字符串引号不可丢,SQL高级也不难。

经典面试题

  1. 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 条这样的数据记录
    
  2. 为什么索引结构默认使用 B+树,而不是 B-Tree,Hash 哈希,二叉树,红黑树?

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

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