mysql索引基础

最新推荐文章于 2020-12-17 19:08:40 发布

太兮

最新推荐文章于 2020-12-17 19:08:40 发布

阅读量187

点赞数 1

分类专栏： mysql 文章标签： mysql 索引

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

索引的类型

B-Tree索引
哈希索引（hash index）
空间数据索引（R-Tree）

1.1 B-Tree索引

MYISAM使用前缀压缩技术使得索引更小，通过数据的物理位置引用被索引的行
而InnoDB按照原数据格式进行存储，根据主键引用被索引的行。
原理： B-Tree从索引的根节点开始进行搜索，而不是进行全表的扫描来获取需要的数据。根节点的中存放了指向了子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际定义了子节点页中值的上限和下限。最终存储引擎要么找到对应的值，要么该记录不存在。

查看表的索引

show index from table_name;
show keys from table_name;
eg：
show index from orders;
这里写图片描述

可以使用B-Tree索引的查询类型

全值匹配
例如刚才上图中索引可以查找biz_refund_id为6603 和 pay_log_id为1494929的的订单
匹配最左前缀
例如刚才上图中索引可以查找biz_refund_id为6603 的订单（注意没有pay_log_id为1494929这个索引列）只使用了索引的第一列
匹配列前缀
例如刚才上图中索引可以查找biz_refund_id 在6603—— ‘0013e7e3-9439-41ff-8b06-1b6b4de46379’之间的列，这里只使用了索引的第一列
匹配范围
例如刚才上图中索引可以查找biz_refund_id为6603或者 ‘0013e7e3-9439-41ff-8b06-1b6b4de46379’的订单,这里也是只使用了索引的第一列
精确匹配某一列并范围匹配另一列
例如刚才上图中索引可以查找biz_refund_id为6603，pay_log_id 以‘0013e7e3’开头或者结尾的列
只访问索引的查询
其实就是覆盖索引：例如就是select biz_refund_id，pay_log_id from orders where biz_refund_id = ‘6603’ and pay_log_id like ‘%0013e7e’ ;就是一个覆盖索引。得到的列就是索引列或者是索引列的子集，这种查询只需要访问索引，而不需要访问数据行。
注意索引的顺序
- 如果不是按照索引的最左列开始查找，则无法使用索引。例如上面的索引不能只使用pay_log_id去查询，因为不是第一列，这里就变成了using where type为all
- 不能跳过索引中的列。例如key(col1,col2,col3)若是某个查询只使用col1和col3 则这个查询没有用到这个索引
- 如果某个查询中有列的范围查询，，则该列右边所有的列都无法使用索引来优化查找
  例如key(col1,col2,col3),如果where col1= ‘131’ and col2 like ‘%43%’ and col3 = ‘eazc’;则这个查询只用了col1和col2。

2.1哈希索引

哈希索引：基于哈希表实现，对于每一列数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同的键值的行计算出来的哈希码也不一样。哈希索引会存放所有的哈希码和指向每个数据行的指针（哈希码+行指针，并不存放字段值）。只有Memory引擎支持哈希索引。例如
这里写图片描述

因为哈希索引只需存储对应的哈希值，所以索引的结构十分紧凑，这让哈希索引的查找速度非常快
但限制如下：
- 哈希索引只包含哈希值和行指针，不存放字段值，不想B - Tree索引那样可以有覆盖索引来避免读取行。（在访问内存中的行的速度很快，大部分情况下对性能影响不明显（摘自高性能mysql））
- 哈希索引的数据并不是按照索引值的顺序存储的，故无法用于排序
- 哈希索引不支持部分索引匹配查找。因为哈希索引始终是使用索引列的全部内容来计算哈希码的。
- 哈希索引只支持等值比较查询，包括有=，in(),<=> (安全等于的意思)。不支持任何任何范围的查询如：age>20
- 哈希索引冲突比较多的话，哈希索引的维护的代价比较高。而且查找的时候必须遍历链表的所有的行指针，逐行进行比较所有复合条件的行（一般采用联地址法处理冲突，如java.Collection中的HashMap）。

3.1 空间数据索引（R-Tree）

R-Tree会从所有维度来索引数据。查询时可以使用任何维度来组合查询。必须使用mysql的GIS相关函数如MBRCONTAILS()等维护数据。（一笔带过，有兴趣查相关文档或者网站）。
总结：在帮助引擎快速查找到记录时候：针对非常小的表，大部分情况下简单的全表扫描更高效（参看http://blog.itpub.net/28539951/viewspace-1655342/
）。针对中到大型的表，索引就非常有效。针对特大型的表，建立和使用索引的代价随之增大。可以考虑分库分表的技术。