一、索引基础
1、索引是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能。
2、索引优化是对查询性能优化最有效的手段,能够轻松将查询性能提高几个数量级。
索引的优缺点
优点
- 提高数据检索的效率,降低数据库的IO成本(将随机I/O变为顺序I/O)
- 索引大大减少了服务器需要扫描的数据量
- 可以避免排序和临时表
- 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
缺点
- 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
- 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的
简易的工作原理
存储引擎先在索引中找到对应值,然后根据匹配的索引记录找到对应的行。样例如下:
mysql> select first_name from users where id=5;
如果在id上有索引,则MySQL将使用该索引找到id=5的行。
二、索引类型
在MySQL中,索引是存储在引擎层而不是服务器层实现的。不同存储引擎的索引的工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引,其底层的实现也可能不同。
B-Tree索引
1、简述:
每个节点都保存指针和数据信息。
2、底层的存储引擎也可能使用不同的存储结构。例如,NDB集群存储引擎内部实际上使用了 T-Tree结构存储这种索引;InnoDB则使用B+Tree。
3、存储引擎以不同的方式使用B-Tree索引,性能也各不相同。例如,MyISAM使用前缀压缩技术使得索引更小,但InnoDB使用源数据存储。再如,MyIASM索引通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引的行。
4、B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据。通过比较节点页的值和要查找的值可以选择指针进入下一节点,最终存储引擎要么找到数据,要么不存在。
5、B-Tree对索引是顺序组织存储的,所以很适合范围查找。
可以使用B-Tree索引查询的类型
B-Tree适用于全键值查找,键值范围或键前缀查找,具体如下:
CREATE TABLE People(
last_name varchar(50) not null,
first_name varchar(50) not null,
birth date not null,
gender enum('m','f') not null,
key(last_name,first_name,birth)
);//last_name是姓,first_name是名
1、全值匹配
全职匹配指的是和索引的所有列进行匹配,比如找,王 富贵 1990-01-01出生的人。
2、匹配最左前缀原则
找姓王的人,即只用索引第一列。若找叫富贵的索引会失效。
3、匹配列前缀
找叫王富_ 的人,即只匹配某一列的前部分,后边的索引会失效。
4、匹配范围值
找姓王到姓李之间的人,后边的索引会失效。
5、只访问索引的查询
select last_name,first_name from People;
这种情况叫索引覆盖,使用索引直接获取列信息,不用读取行。
Hash索引
1、hash索引是基于hash表实现,只有精准匹配hash索引所有列的查询才有效。哈希索引将所有的hash码存储在索引中,同时在hash表中存指向每个数据行的指针。如果多个列的hash值相同索引会以链表的形式存放多个记录。MySQL中只有Memory索引支持hash索引。
2、例:以People表为例
SELECT last_name from People where first_name='Peter'
MySQL先计算‘Peter’ 的hash值,并使用该值寻找记录指针,并根据指针找到对应行,最后比较该行是否为‘Peter’,以确保就是要查找的行。
3、因为索引自身只需要存储对应的hash值,所以hash结构十分紧凑,这使得hash索引查找的速度非常快。然而hash索引也有它的限制。
- hash索引只包含hash值和行指针,而不存储字段,所以不能避免读取行。
- hash索引数据并不是按照索引值顺序存储的,所以不能用于排序。
- hash索引不支持匹配查找,因为hash值是索引的的全部内容计算的。例如,索引列为(A,B),如果查询只有A列,就不能用该索引。
- hash只支持等值比较查询,不支持范围查询。
- 访问hash索引非常快但是会有hash冲突。当出现hash冲突的时候,存储引擎必须遍历链表的所有行指针。
- 如果hash冲突非常多,索引维护代价会很高。
自适应hash索引
1、InnoDB特有的,当InnoDB引擎注意到某些索引值被使用得非常频繁时,它会在内存中基于B-Tree索引之上建立一个hash索引,这会让B-Tree索引具备某些hash索引的特性,例如快速的hash查找。
空间数据索引(R-Tree)
1、MyISAM表支持空间索引,可以用来支持地理数据存储。和B-Tree不同,该类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时,可以按照各种维度来组合查询。
全文索引
1、全文索引是一种特殊的索引,它查找的是文本中的关键字,而不是直接比较索引中的值。全文索引更类似于搜素引擎的事情。
2、在相同列上同时创建全文索引和基于值的B-Tree索引不会引起冲突,全文索引适用于MATCH
和AHAINST操作,而不是普通的WHERE操作。
聚簇索引
1、聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。InnoDB的局促索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时,它的数据行存在索引的叶子页。
2、InnoDB会使用主键聚集数据,如果没有定义主键,InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录,包含相邻键值的页面可能会相聚甚远。
聚簇索引的优点
- 可以把相关数据保存在一起。
- 数据访问更快。
- 使用索引覆盖扫描的查询可以直接使用在页节点中主键值。
聚簇索引的缺点
- 聚簇索引最大程度的提高了I/O密集型应用的性能,如果数据都存在了内存中,就没有优势了。
- 插入速度严重依赖于顺序
- 更新聚簇索引列的改价很高
- 基于聚簇索引的表在插入新行,或者主键被改变时导致需要移动行的时候,可能面临页分裂的问题。当页已满时插入新行,存储引擎会将该页分裂成两个页存储该行。
- 聚簇索引可能导致全表扫描变慢,尤其行比较稀疏
其它索引
TokuDB等。
三、索引失效
以People表为例
1、未按照最左前缀匹配
SELECT * from People WHERE first_name ='Peter' //第二列索引
Select * from People WHERE last_name ='Allen' and birth=2000-03-01 //跳过第二列,只会使用第一列索引
Select * from People where birth=2000-03-01 and first_name ='Peter' and last_name='Allen'//未按照顺序使用
2、字符串没加''
SELECT *from People where last_name=Allen
3、索引上做结算
SELECT * FROM People where datediff(now(),birth)>3000
4、范围后边的索引都失效
SELECT *FROM People where last_name>'Allen' and last_name ='Peter'
5、使用不等会失效
SELECT *FROM People where last_name<>'Allen'
6、使用null或not null可能会失效
但是如果允许字段为空,则
- IS NULL 不会导致索引失效
- IS NOT NULL 会导致索引失效
7、模糊查询放在具体值得左边
SELECT *FROM People where last_name like '%飞'