Indexing

Search…
Indexing
前言
索引的目的是为了提高数据的查询效率。
但是索引不是一定能提升效率，比如
数据量较小，如果有回表操作，那么用索引的效率可能更低。
字段的可选值很少，且分布较均匀，比如性别。注意可选值少还不够，还需要分布较均匀，比如有个字段只有 1，0 取值，1 有 一千万条，0 有十条，那么当这个字段建了索引，查询 0 的时候也会比较快。
索引从功能上看主要有四种：
普通索引，没有任何约束，主要用于提高查询效率。
唯一索引，在普通索引基础上增加了唯一性约束，一张表可以有多个。
主键索引，在唯一索引的基础上增加了非空约束，一张表只能有一个。
全文索引，用的不多，MySQL 自带的全文索引仅支持英文。
普通索引和唯一索引的效率问题。
原理上，唯一索引由于增加了唯一性约束，找到了关键字会停止检索。而普通索引还会往下继续检索，效率在理论上会第一点。
但是这点差别可以忽略不计，原因是，根据数据库存储方式，InnoDB 会把整个数据页加载到内存中，一个页中可能存在上千条记录，普通所以仅仅是在内存中多几次判断下一条记录，这点时间可以忽略不计。
从物理实现上有两种：
聚集索引，按照主键的顺序存储数据，一张表只能一个，叶子节点存储了数据记录。
非聚集索引，也叫二级索引，一张表可以有多个，叶子节点存储的是数据位置。
按照字段个数，可以分为单一索引和联合索引，联合索引注意字段顺序，有最左匹配原则。
常见索引数据结构
​Hash 表：本质是数组+链表的数据结构。
缺点：做区间查询速度很慢。
适用场景：只有等值查询的情况。比如 Memcached 、Lucene 等。
有序数组：等值查询和范围查询都很快。等值查询用用二分查找，时间复杂度O(log(N))。范围查询先用等值查询找到第一个，然后往后遍历。
缺点：插入数据效率很低。
适用场景：静态数据，比如 2015年上海市的人口信息。
​平衡二叉查找树​
​B 数​
​B+ 树​
​跳表​
LSM 树
MySQL 中的 Memory 存储引擎支持 Hash 存储；另外 MySQL 的 InnoDB 有一个自适应 Hash 索引，当某个索引使用非常频繁时，会在 B+ 树的基础上再创建一个 Hash 索引。不过 Hash 索引相对于 B+ 树有一些缺点：
不支持范围查询。
不支持联合索引的最左匹配原则，因为 hash 函数对于输入的一点微小变化都很敏感。
不支持 ORDER BY 排序。
不支持 LIKE 'XX%'。
最佳实践
什么时候创建索引
字段数值有唯一限制，唯一索引和主键索引都能起到唯一性的约束。
作为 WHERE 查询条件的字段，尤其表较大时，包括 SELECT、UPDATE、DELETE、JOIN。
频繁作为 GROUP BY 和 ORDER BY 的字段。
DISTINCT 字段。
多表 JOIN 时，用于连接的字段。不过，连接表尽量不要超过 3 张。
对于同时存在 GOURP BY 和 ORDER BY 的语句，如：GROUP BY a ORDER BY b
若对 a，b 分别创建单列索引，则只有一个会生效。
创建（b, a）联合索引没有（a, b）联合索引效率高，因为先进行 GROUP BY。
什么时候不创建索引
非查询条件的字段，查询条件指的是 WHERE、GROUP BY、ORDER BY、DISTINCT。
数据量较小。
字段中有大量重复数据，且分布较均匀。
频繁更新的字段，因为更新时也会更新索引。
什么时候索引失效
索引进行了表达式计算：WHERE id + 1 = 9001 => WHERE id = 9000
索引进行了函数计算：WHERE SUBSTRING(a, 1, 3) = 'abc' => WHERE a LIKE 'abc%'
OR 条件中有一个列没有索引，那么其他索引列也会失效。如 WHERE a = 1 OR b = 2，a 有索引，b 没有，那么 a 的索引会失效。
LIKE 不能以 `%` 开头。
索引列与 NULL 或 NOT NULL 判断时。
MySQL 索引
自增主键
自增主键指插入记录时，若不指定主键值，则会获取当前主键值并加 1 作为下一条主键的值。通过 NOT NULL PRIMARY KEY AUTO_INCREMENT定义
优点：插入数据是递增的，主键索引不会发生页分裂。且整型占用空间少，二级索引占用的空间也少。
索引重建
索引可能因为删除、页分裂等情况，导致数据空洞，重建索引会把数据按照顺序插入，页面利用率就高。
若需要重建索引，下面方法是否可行？
alter table T drop index k;
alter table T add index(k);
​
alter table T drop primary key;
alter table T add primary key(id);
重建索引 k 是合理的。但是重建主键索引不合理，删除、创建主键都会重建整个表，所以前面的重建 k 就白做了。可以使用alter table T engine=InnoDB替代。
索引覆盖
若查询需要的结果已经包含在二级索引里面，不需要回表，称为索引覆盖。是一个常用的性能优化手段。
最左匹配
最左匹配可以是联合索引的最左边的 N 个字段，也可以是字符串索引的最左 M 个字符。
联合索引的顺序考虑：
如果能够通过调整顺序，减少一个索引，那么应该使用这个顺序。比如已经有 (a,b) 索引，就不需要单独的 a 索引了。
空间原则，比如 name 和 age 两个字段，name 长度大于 age，那么应该创建 (name,age) 和 (age)，而不是 (age,name) 和 (name)。
索引下推
MySQL 5.6 引入索引下推优化（index condition pushdown），可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录。比如下面语句：
mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;
在 5.6 之前需要回表四次：
在 5.6 仅需回表两次：
Transaction
Lock
Last modified 2yr ago
Copy link
Outline