什么是索引
什么是索引:在之前写的文章解释过 索引
索引的作用
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 平衡树,二叉树
![19a001fa9bd379e423558c6f738f1511.png](https://i-blog.csdnimg.cn/blog_migrate/61da9d89a627bdf7cfd6a6f60dcab5c2.jpeg)
B+树是以 N(N > 2)叉树的形式存在的,这样降低了树的高度,降低了磁盘的 I/O 操作,不需要在进行全表扫描,每次顺着根节点就能很快的找到目标数据,每个节点的大小即一个磁盘块的大小,一次 IO 会将一个页(每页包含多个磁盘块)的数据都读入(即磁盘预读,程序局部性原理:读到了某个值很大可能这个值周围的数据也会被用到,干脆一起读入内存),叶子节点通过指针的相互指向连接,能有效减少顺序遍历时的随机 IO,而且我们也可以看到,叶子节点都是按索引的顺序排序好的,这也意味着根据索引查找或排序都是排序好了的,不会再在内存中形成临时表。
2.哈希(Hash)索引:(散列表)
简单地说,哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。
假设我们对名字建立了哈希索引,则查找过程如下图所示:
![21f15081af642df46ed0317b0a8607e8.png](https://i-blog.csdnimg.cn/blog_migrate/0949113bae366b469d27e46035e4c6d7.jpeg)
哈希索引优点:
因为索引自身只存储对应的哈希值,所以索引的结构十分紧凑,这让哈希索引查找的速度非常快!
哈希索引的缺点:
1.Hash索引只支持等值查找,不支持区间查找。
2.Hash索引不支持排序操作。
3.Hash索引无法使用部分索引列匹配查找
4.Hash索引也不支持多列联合索引的最左匹配规则
5.Hash索引在有大量重复键值情况下,哈希索引的效率也是极低的,因为存在所谓的哈希碰撞问题。
6.Hash索引存在Hash冲突
哈希索引用途:
mysql只有MEMORY存储引擎显示支持哈希索引。
innoDB 引擎本身是不支持显式创建哈希索引的,我们可以在 B+ 树的基础上创建一个伪哈希索引,它与真正的哈希索引不是一回事,它是以哈希值而非键本身来进行索引查找的
在 InnoDB 引擎中就有一种名为「自适应哈希索引」的特殊索引,当 innoDB 注意到某些索引值使用非常频繁时,就会内存中基于 B-Tree 索引之上再创建哈希索引,这样也就让 B+ 树索引也有了哈希索引的快速查找等优点,这是完全自动,内部的行为,用户无法控制或配置,不过如果有必要可以关闭该功能。
用途: 为超长的键创建哈希索引。列值太长,导致索引体积过大,查询速度也会受到影响。
![d208115631ab0e7750e92d2d474836a4.png](https://i-blog.csdnimg.cn/blog_migrate/27785b69eece802efbe0328517622127.png)
增加一个额外哈希列,将列值映射成哈希值,对哈希列进行再进行索引。在where条件处手动指定使用哈希函数。
![4ca398f9480933cb6c398407e2f3ebeb.png](https://i-blog.csdnimg.cn/blog_migrate/7c4f4eb6ecf3b84599e8fa42064db20d.jpeg)
假设使用的是哈希函数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 都伴随着索引的更新,很可能频繁伴随着页分裂与页合并。