【MySQL学习笔记】02、索引的类型

最新推荐文章于 2024-08-31 00:07:03 发布

zzzfeiyu

最新推荐文章于 2024-08-31 00:07:03 发布

阅读量93

点赞数

分类专栏： mySQL

本文链接：https://blog.csdn.net/zzzfeiyu/article/details/100063328

版权

7 篇文章 0 订阅

订阅专栏

索引优化与查询优化紧密相关，好的索引能让查询上升几个数量级，索引与服务层无关，是在引擎层实现，常用的索引有B-Tree索引和哈希索引

为什么mysql很多存储引擎使用B+Tree索引：B+Tree索引能够加快访问数据的速度，索引结点有指向子节点的指针，通过与索引结点值的比较，可以快速定位当前要查找的值的精确范围，遍历访问这一小段范围要么可以查找到对应的值，要么查找失败，减少了大量的I/O。
联合key索引的字典序排列：如果定义一张表为：Student( name, dept_name, age)且key = ( name, dept_name, age),这时候索引的建立会按照字典序进行，即当name的值接近的优先放在同一(name)列，如(zhangsan,CS,20)和(zhangyu,SE,22)的索引比较靠近，当同名学生出现如则会按照dept_name的字典序排列，如(zhangsan,CS,20)会在(zhangsan,SE,20)之前
字典序对于索引的影响：由以上的情况可以看到，索引是将key按照顺序进行字典序排列，这也就意味着无法跳过列顺序执行索引，或者说跳过顺序后后续索引无效，如查询条件为WHERE name = "zhangsan" AND dept_name LIKE 'C%' AND age = 20 ，在模糊查询dept_name后的 age=20条件已经无效，因为现有索引无法跳过dept_name直接对age排序(除非重定义key=（name,age,dept_name）)。再看ORDER BY 是字典序排列索引的很方便的直接应用，但是由于字典序原因，它的前提是查询条件不跳过列顺序。

哈希索引只有memory引擎使用，它主要有两个缺点：
缺点1：key只能作为整体计算哈希码，如abcdefg-----234567，计算后的哈希码由于只是数字会失去原有的大部分特征，如字典序，以及无法拆分查询（无法查询abc前缀）....等等，
缺点2：当数据库的数据量增大到一定量时哈希冲突会很频繁

当出现一个较长的字符串（如url），使用B+Tree会浪费大量空间，这时候可以创建伪哈希索引，值得一提的是，InnoDB引擎在察觉到用户频繁使用某些索引值时会类似启动“自适应哈希索引”。
伪哈希索引创建方法是CREATE TABLE 伪哈希( id,url, url哈希值,key = id)，这里哈希值的算法可以使用CRC32(循环冗余校验：左移+模+只看余数不看商)、MD5（只能取部分，否则太长浪费空间），最后新建触发器在添加修改之前插入 :url哈希值 = CRC32(New.url)。
由于依然会存在哈希冲突，所以在查找时要附带常量：WHERE url哈希值=CRC32("www.baidu.com") AND url = "www.baidu.com".