索引体积_索引原理

什么是索引

什么是索引：在之前写的文章解释过 索引

索引的作用

1.索引能极大地减少扫描行数，加快查询速度，详细解释看 索引概述

2.索引可以帮助服务器避免排序和临时表

当我们正常运行 sql 语句的时候，不适用索引运行下面这条语句：

SELECT * FROM user order by age desc;

MySQL 的流程是这样的，扫描所有行，把所有行加载到内存后，再按 age 排序生成一张临时表,再把这表排序后将相应行返回给客户端,更糟的,如果这张临时表的大小大于 tmp_table_size的值（默认为 16 M），内存临时表会转为磁盘临时表，性能会更差；

但是如果加了索引，索引本身是有序的，所以从磁盘读的行数本身就是按 age 排序好的，也就不会生成临时表，就不用再额外排序 ，无疑提升了性能。

3.索引可以将随机 IO 变成顺序 IO

mysql 数据最终都会刷到磁盘上去，刷盘分随机IO和顺序IO，两者性能相差很大，大多情况下我们会改变一下设计使mysql 的随机IO变为顺序IO来提高性能；

如果你要执行2条insert语句，但是你的log file正好只能容纳一条，那么在写入磁盘的时候。只有一条可以执行，那么比如正好在磁盘2。过了一段时间在执行第二条语句，数据跑到了磁盘32的位置。这种操作是随机io。如果log file正好可以全部容纳2条语句，两条语句同时被刷新到硬盘。第一条在磁盘2，第二条在磁盘3。那么这就是顺序io。这是在插入的情况。

接下来就是查询的时候，为什么要使用聚集索引，因为B+树的叶子都是数据。如果数据的基本是连续的，那么基本需要顺序i/o即可。如果底层存的是指针，那么就会走随机i/o性能查了一大截（因为你需要根据指针一个一个找数据那么两者的性能就会相差太大）。

随机 IO 和顺序 IO 大概相差百倍 (随机 IO：10 ms/ page, 顺序 IO 0.1ms / page)，可见顺序 IO性能之高，索引带来的性能提升显而易见！

索引的种类

1.B+树索引：

B+树在上篇文章写到过请看 B+树 下图是 B+树; 你还可能感兴趣 B树,AVL 平衡树,二叉树

B+树是以 N（N > 2）叉树的形式存在的,这样降低了树的高度,降低了磁盘的 I/O 操作,不需要在进行全表扫描,每次顺着根节点就能很快的找到目标数据,每个节点的大小即一个磁盘块的大小，一次 IO 会将一个页（每页包含多个磁盘块）的数据都读入（即磁盘预读，程序局部性原理:读到了某个值很大可能这个值周围的数据也会被用到，干脆一起读入内存），叶子节点通过指针的相互指向连接，能有效减少顺序遍历时的随机 IO，而且我们也可以看到，叶子节点都是按索引的顺序排序好的，这也意味着根据索引查找或排序都是排序好了的，不会再在内存中形成临时表。

2.哈希(Hash)索引:(散列表)

简单地说，哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

假设我们对名字建立了哈希索引，则查找过程如下图所示:

哈希索引优点:

因为索引自身只存储对应的哈希值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快!

哈希索引的缺点:

1.Hash索引只支持等值查找，不支持区间查找。

2.Hash索引不支持排序操作。

3.Hash索引无法使用部分索引列匹配查找

4.Hash索引也不支持多列联合索引的最左匹配规则

5.Hash索引在有大量重复键值情况下,哈希索引的效率也是极低的,因为存在所谓的哈希碰撞问题。

6.Hash索引存在Hash冲突

哈希索引用途:

mysql只有MEMORY存储引擎显示支持哈希索引。

innoDB 引擎本身是不支持显式创建哈希索引的，我们可以在 B+ 树的基础上创建一个伪哈希索引，它与真正的哈希索引不是一回事，它是以哈希值而非键本身来进行索引查找的

在 InnoDB 引擎中就有一种名为「自适应哈希索引」的特殊索引，当 innoDB 注意到某些索引值使用非常频繁时，就会内存中基于 B-Tree 索引之上再创建哈希索引，这样也就让 B+ 树索引也有了哈希索引的快速查找等优点，这是完全自动，内部的行为，用户无法控制或配置，不过如果有必要可以关闭该功能。

用途: 为超长的键创建哈希索引。列值太长，导致索引体积过大，查询速度也会受到影响。

增加一个额外哈希列，将列值映射成哈希值，对哈希列进行再进行索引。在where条件处手动指定使用哈希函数。

假设使用的是哈希函数hash()，查询语句如下：

select * from table where 列B= hash('XXXXXX') and 列A=‘XXXXXX'

XXXXXX对应上表列A的值，列B还是利用B+Tree索引进行查找，只不过我们是利用哈希值而不是列键本身进行索引。

url键查询

select * from url_hash where url='blog.csdn.netqq_2622285'

使用mysql自带的CRC32函数对url做哈希处理，就可以使用下面的函数查询

select * from url_hash where url_crc=CRC32('blog.csdn.netqq_2622285' ) and url='blog.csdn.netqq_2622285'

mysql优化器会选择性能高且体积小的基于url_crc列的索引来完成查找，即使用多个相同的索引值,查找仍然很快。

但是，我们需要手动维护crc_url哈希列，可通过触发器在插入和更新时实时维护url_crc列

加了索引，为何却不生效

之前写过一篇 一条SQL语句执行得很慢的原因有哪些 也提到了相关内容

1.索引列是表示式的一部分，或是函数的一部分,导致索引不生效

例如下 sql 语句:

SELECT book_id FROM BOOK WHERE book_id + 1 = 5;

SELECT book_id FROM BOOK WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(gmt_create) <= 10

上述两个 SQL 虽然在列 book_id 和 gmt_create 设置了索引 ，但由于第一条语句在字段的左边做了运算,那么在查询的时候,会导致索引无法使用,第二条语句对字段进行了函数的操作，导致索引无法生效，最终导致全表扫描

2.隐式类型转换

假设有以下表:

CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `tradeid` (`tradeid`),
   KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

执行 SQL 语句:

SELECT * FROM tradelog WHERE tradeid=110717;

虽然 tradeid 字段上有索引，但使用 EXPLAIN 语句查询执行却发现使用了全表扫描，tradeId 的类型是 varchar(32), 而此 SQL 用 tradeid 一个数字类型进行比较，发生了隐形转换，会隐式地将字符串转成整型，如下:

mysql> SELECT * FROM tradelog WHERE CAST(tradid AS signed int) = 110717;

这样也就触发了上文中第一条的规则 ，即：索引列不能是函数的一部分

追加: 怎样查询是否使用索引,代码如下:

-- 实际SQL，查找用户名为 Jefabc 的员工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引，前面加上 EXPLAIN 即可
explain select * from emp where name = 'Jefabc';

3.隐式编码转换

这种情况非常隐蔽，来看下这个例子,如下表:

CREATE TABLE `trade_detail` ( 
 `id` int(11) NOT NULL, 
 `tradeid` varchar(32) DEFAULT NULL, 
 `trade_step` int(11) DEFAULT NULL, /*操作步骤*/ 
 `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/ 
   PRIMARY KEY (`id`), KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

trade_defail 是交易详情，上个实验表 tradelog 是操作此交易详情的记录，现在要查询 id=2的交易的所有操作步骤信息，则我们会采用如下方式:

SELECT d.* FROM tradelog l, trade_detail d WHERE d.tradeid=l.tradeid AND l.id=2;

由于 tradelog 与 trade_detail 这两个表的字符集不同，且 tradelog 的字符集是 utf8mb4，而trade_detail 字符集是 utf8, utf8mb4 是 utf8 的超集，所以会自动将 utf8 转成utf8mb4。即上述语句会发生如下转换:

SELECT d.* FROM tradelog l, trade_detail d WHERE (CONVERT(d.traideid USING utf8mb4)))=l.tradeid AND l.id=2;

这时也就触发了「索引列不能是函数的一部分」这条规则。怎么解决呢，第一种方案当然是把两个表的字符集改成一样,如果业务量比较大,生产上不方便改的话,还有一种方案是把 utf8mb4 转成utf8，如下:

SELECT d.* FROM tradelog l , trade_detail d WHERE d.tradeid=CONVERT(l.tradeid USING utf8) AND l.id=2;

这样索引列就生效了

4.使用 order by 造成的全表扫描

SELECT * FROM user ORDER BY age DESC

上述语句在 age 上加了索引，但依然造成了全表扫描，这是因为我们使用了 SELECT *,导致回表查询，MySQL 认为回表的代价比全表扫描更大，所以不选择使用索引，如果想使用到 age 的索引，我们可以用覆盖索引来代替:

SELECT age FROM user ORDER BY age DESC

或者加上 limit 的条件 (数据比较小的情况)

SELECT * FROM user ORDER BY age DESC limit 10

这样就能利用到索引

无法避免对索引列使用函数，怎么使用索引

有时候我们无法避免对索引列使用函数，但这样做会导致全表索引，是否有更好的方式呢。

比如我现在就是想记录 2016 ~ 2018 所有年份 7月份的交易记录总数

SELECT count(*) FROM tradelog WHERE month(t_modified)=7;

由于索引列是函数的参数，所以无法用到索引，这时我们可以将它改造成基本字段区间的查找如下

SELECT count(*) FROM tradelog WHERE
    -> (t_modified >= '2016-7-1' AND t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' AND t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' AND t_modified<'2018-8-1');

前缀索引与索引选择性

之前我们说过，如果长字符串的字段（如 url），我们可以用伪哈希索引的形式来创建索引，以避免索引变得既大又慢，除此之外其实还可以用前缀索引（字符串的部分字符）的形式来达到我们的目的，那么这个前缀索引应该如何选取呢，这叫涉及到一个叫索引选择性的概念

索引选择性：不重复的索引值（也称为基数，cardinality）和数据表的记录总数的比值，比值

越高，代表索引的选择性越好，唯一索引的选择性是最好的，比值是 1。

我们可以通过 SHOW INDEXES FROM table来查看每个索引 cardinality的值以评估索引设计的合理性

怎么选择这个比例呢，我们可以分别取前 3，4，5，6，7 的前缀索引，然后再比较下选择这几个前缀索引的选择性，执行以下语句

SELECT 
 COUNT(DISTINCT LEFT(city,3))/COUNT(*) as sel3,
 COUNT(DISTINCT LEFT(city,4))/COUNT(*) as sel4,
 COUNT(DISTINCT LEFT(city,5))/COUNT(*) as sel5,
 COUNT(DISTINCT LEFT(city,6))/COUNT(*) as sel6,
 COUNT(DISTINCT LEFT(city,7))/COUNT(*) as sel7
FROM city_demo

得结果如下:

sel3    sel4    sel5    sel6    sel7
0.0239  0.0293  0.0305  0.0309  0.0310

可以看到当前缀长度为 7 时，索引选择性提升的比例已经很小了，也就是说应该选择 city 的前六个字符作为前缀索引，如下

ALTER TABLE city_demo ADD KEY(city(6))

我们当前是以平均选择性为指标的，有时候这样是不够的，还得考虑最坏情况下的选择性，以这个demo 为例，可能一些人看到选择 4，5 的前缀索引与选择 6，7 的选择性相差不大，那就得看下选择 4，5 的前缀索引分布是否均匀了

SELECT 
    COUNT(*) AS  cnt, 
    LEFT(city, 4) AS pref
  FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 5

可能会出现以下结果

cnt     pref
305     Sant
200     Toul
90      Chic
20      Chan

可以看到分布极不均匀，以 Sant，Toul 为前缀索引的数量极多，这两者的选择性都不是很理想,所以要选择前缀索引时也要考虑最差的选择性的情况。

前缀索引虽然能实现索引占用空间小且快的效果,但它也有明显的弱点,MySQL 无法使用前缀索引做ORDER BY 和 GROUP BY ，而且也无法使用前缀索引做覆盖扫描，前缀索引也有可能增加扫描行数。

假设有以下表数据及要执行的 SQL:

id  email
1   zhangssxyz@163.com
2   zhangs1@163.com
3   zhangs1@163.com
4   zhangs1@163.com

SELECT id,email FROM user WHERE email='zhangssxyz@xxx.com';

如果我们针对 email 设置的是整个字段的索引，则上表中根据 「zhangssxyz@163.com」查询到相关记记录后,再查询此记录的下一条记录，发现没有，停止扫描，此时可知只扫描一行记录，如果我们以前六个字符（即 email(6)）作为前缀索引，则显然要扫描四行记录，并且获得行记录后不得不回到主键索引再判断 email 字段的值，所以使用前缀索引要评估它带来的这些开销。

另外有一种情况我们可能需要考虑一下，如果前缀基本都是相同的该怎么办，比如现在我们为某市的市民建立一个人口信息表，则这个市人口的身份证虽然不同，但身份证前面的几位数都是相同的，这种情况该怎么建立前缀索引呢。

一种方式就是我们上文说的，针对身份证建立哈希索引，另一种方式比较巧妙，将身份证倒序存储，查的时候可以按如下方式查询:

SELECT field_list FROM t WHERE id_card = reverse('input_id_card_string');

这样就可以用身份证的后六位作前缀索引了

实际上上文所述的索引选择性同样适用于联合索引的设计，如果没有特殊情况，我们一般建议在建立联合索引时，把选择性最高的列放在最前面，比如，对于以下语句：

SELECT * FROM payment WHERE staff_id = xxx AND customer_id = xxx;

单就这个语句而言， (staff_id，customer_id) 和 (customer_id, staff_id) 这两个联合索引我们应该建哪一个呢，可以统计下这两者的选择性。

SELECT 
 COUNT(DISTINCT staff_id)/COUNT(*) as staff_id_selectivity,
 COUNT(DISTINCT customer_id)/COUNT(*) as customer_id_selectivity,
 COUNT(*)
FROM payment

结果为:

staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049

从中可以看出 customer_id 的选择性更高，所以应该选择 customer_id 作为第一列。

索引设计准则：三星索引

如果一个查询满足三星索引中三颗星的所有索引条件，理论上可以认为我们设计的索引是最好的索引。什么是三星索引:

1.第一颗星：WHERE 后面参与查询的列可以组成了单列索引或联合索引

2.第二颗星：避免排序，即如果 SQL 语句中出现 order by colulmn，那么取出的结

果集就已经是按照 column 排序好的，不需要再生成临时表

3.第三颗星：SELECT 对应的列应该尽量是索引列，即尽量避免回表查询。

所以对于如下语句:

SELECT age, name, city where age = xxx and name = xxx order by age

设计的索引应该是 (age, name,city) 或者 (name, age,city)

三星索引是一个比较理想化的标准,实际操作往往只能满足期望中的一颗或两颗星，考虑如下语句:

SELECT age, name, city where age >= 10 AND age <= 20 and city = xxx order by name desc

假设我们分别为这三列建了联合索引，则显然它符合第三颗星（使用了覆盖索引），如果索引是（city, age, name)，则虽然满足了第一颗星，但排序无法用到索引，不满足第二颗星，如果索引是 (city, name, age)，则第二颗星满足了，但此时 age 在 WHERE 中的搜索条件又无法满足第一星

另外第三颗星（尽量使用覆盖索引）也无法完全满足，试想我要 SELECT 多列，要把这多列都设置为联合索引吗，这对索引的维护是个问题，因为每一次表的 CURD 都伴随着索引的更新，很可能频繁伴随着页分裂与页合并。