索引是存储引擎用于快速找到记录的一种数据结构。
1、索引基础
要理解MySQL中索引是如何工作的,最简单的方法就是去看看一本书的“索引”部分,如果想在一本书中找到某个特定
1.1 索引的类型
在MySQL中,索引是在存储引擎层,而不是服务层
B-Tree索引
MySQL大多数都是使用B-Tree索引 InnoDB使用B+Tree
存储引擎以不同的方式使用B-Tree索引,性能各有不同,各有优劣
eg:MyISAM使用前缀压缩技术使得索引更小,但InnoDB按照原数据格式进行存储
eg:MyISAM索引通过数据的物理位置引用被索引的行,而InnoDB根据主键引用被索引的行
可以使用B-Tree索引的查询类型。B-Tree索引适用于全键值、键值范围或者键前缀查找
全值匹配 和索引中的所有列进行匹配
匹配最左前缀 只使用索引第一列
匹配列前缀 只匹配某一列的值的开头部分
匹配范围值
精准匹配某一列并范围匹配另外一列
只访问索引的查询
一些关于B-Tree索引的限制
(1) 如果不是按照索引的最左列开始查找,则无法使用索引
(2) 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查询
哈希索引
哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码,哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样。
MySQL中,只有Memery引擎显示支持哈希索引,这是Memery引擎的默认索引。
全文索引
全文索引是一种特殊类型的索引,它查找的是文本中的关键词,而不是直接比较索引中的值。更类似于搜索引擎做的事情,而不是简单的where条件匹配。 match against
在相同的列同时创建全文索引和基于值的B-Tree索引不会冲突。
MySql自带的全文索引只能用于数据库引擎为MYISAM的数据表,如果是其他数据引擎,则全文索引不会生效。此外,MySql自带的全文索引只能对英文进行全文检索,目前无法对中文进行全文检索
2、索引的优点
索引可以让服务器快速定位到表的指定位置。但是这并不是索引的唯一作用,到目前为主可以看到,根据创建索引的数据结构不同,索引也有一些其他的附加作用
最常见的的B-Tree索引,按照顺序存储数据,所以MySQL可以用来order by 和 group by
1 索引大大减少了服务器需要扫描的数据量
2 索引可以帮助服务器避免排序和临时表
3 索引可以将随机I/O变成顺序I/O
3、高性能索引策略
3.1 独立的列
独立的列 是指 索引列不能是表达式的一部分,也不能是函数的参数
eg : select user_id from user_info where user_id + 1 = 5;
3.2 前缀索引和索引选择性
有时候索引很长的字符列,会让索引变得大且慢。
可以索引开始的部分字符,大大节约索引空间,提高索引效率,这样也会降低索引的选择性
前缀索引是一种能使索引更小、更快的有效办法,但另一方面也有其缺点 :MySQL无法使用前缀索引order by group by ,也无法做覆盖扫描
3.3 多列索引
很多人对多列索引的理解都不够,一个常见的错误就是,为每个列创建独立的索引,或者按照错误的顺序创建多列索引
单个列,单个索引 用where or 就完蛋
3.4 择合适的索引列顺序
索引列的顺序意味着索引首先按照最左列进行排序,其次是第二列
如何选择顺序,经验法则:选择性最高的列放在索引最前列。
当不考虑排序和分组时,将选择性最好的列放在前面通常是最好的,这时候索引的作用只是用于优化where条件的查询。
select count(distinct topic_id)/count(*) as topic_num , count(distinct post_id)/count(*) as post_num from topic_post_map;
3.5 压缩(前缀)索引
MyISAM使用前缀压缩来减少索引的大小,从而让更多的索引可以放入内存。
压缩块使用更少的空间,代价是某些操作可能更慢。 比如 order by desc
3.6 冗余和重复索引
重复索引是指在相同的列按照相同的顺序创建的相同类型的索引
冗余索引 索引(A,B),再创建索引(A)就是冗余索引
再创建索引(B,A)或者 索引(B)都不是冗余索引
索引(A,ID)其中ID是主键,对于InnoDB来说主键列已经包含再二级索引中了