Mysql索引

全栈开发工程师 2020年06月18日 35次浏览

索引知识

一. 索引的作用

1. 索引可以让服务器快速定位到表的指定位置,减少数据扫描量(查找算法)
2. 索引可以帮助服务器避免拍下和临时表(索引存储了列的值)
3. 索引可以将随机IO变成顺序IO(索引的有序性)

二. 索引的类型

Hash索引

基于hash表的实现,只有精确匹配的所有列的查询才能生效,不能实现范围查找,但精确查找应该能更快

B-Tree

默认的索引方式,使用B+树作为存储方式,使用树作为查找方式,数据按顺序排放,所以能快速查范围查找数据

空间索引

全文索引

三. 怎样建索引

1. 建立独立列的索引

例如建立一个用户表可以建立一个用户名的索引,当查询语句如下的时候将会使用索引查询

select * from tb_user where user_name = 'nihao';

2. 建立前缀索引

当一个索引需要很长的字符列,这会让索引变得特别长,查询速度也会变得慢,这个时候考虑建立前缀索引

建立前缀索引的方法和技巧
1. 选择足够长的前缀使索引有足够的选择性
2. 索引前缀长度的使基数和非前缀的基数接近(也就是我有100个城市,我取前缀也接近100个城市)

3. 建立多列索引

如果一个查询中有多个条件,那么就可能需要建立多列索引,为每列单独建一个索引对多个条件的查询速度是没有提升的,多个索引还可能拖慢插入和更新速度。

在新的mysql数据库中,多个单列索引会被数据库优化为索引合并的操作,例如下面例子

select * from tb_user where sex = 'w' and(or) age = 12

数据库会优化成

select * from tb_user where sex = 'w'
union (join)
select * from tb_user where age = 12

但这样的消耗的资源远远要比多列索引要多

多列索引的顺序

同样以上边例子为例子,索引顺序应该是(age,sex)还是(sex,age),下面给了一个柜子

1. 分别查看每个单独查询看看那一个的可选择性大,例如查出性别男的有3000个,但查age=12的有200个,这样就选择age作为开始列

聚簇索引

聚簇索引是数据的存储方式,在InnoDB中和普通索引一样,但是却在叶子中保存了数据,因为这样聚簇所以有下面的优点

1. 数据访问速度更快,对比其他索引,不需要获取到数据ID后进行二次查找

2. 不需要进行磁盘io的扫描(数据存在索引上面)

使用聚簇索引需要注意的是

1. 尽量使用顺序的序列来作为Id,否则可能会出现页分裂等问题(uuid可能会在原有数据中间插入已满列的时候,会产生这个问题)

2. 列数据越短越好,因为二级索引的叶子点引用的就是行的主键列

覆盖索引

如果一个索引覆盖一个查询所有的查询字段的值,我们就称为索引覆盖

使用好处

1. 索引条目通常会少于数据行的大小,如果只读取索引就可以,那mysql就会极大减少数据访问量。

2. 索引是按列值顺序排列储存的,所以要比随机读取列速度要快。

3. 如果索引的数据覆盖了所有需要查询的字段,那么就不需要对主键索引的二次查询,覆盖索引的作用就等于聚簇索引了。

利用索引覆盖进行延迟关联

如下面sql

select * from tb_user where city = 'gz' company like '%google%'

sql中使用了like,加上查询所有列加上没有索引可以覆盖,所以mysql会进行二次索引查找。

所以这个时候需要重写查询语句为(新版本Mysql中可能作用不大,优化效果取决于返回的行数)

select * from tb_user user2 join
(select id from tb_user where city = 'gz' company like '%google%') user1 
on user1.id = user2.id;

我们把这种方式叫做延迟关联,在查询的第一阶段使用覆盖索引,在from字句中找到对应的id,虽然整个语句不能完全覆盖索引,但也比完全不能使用更好

利用覆盖索引进行优化排序

如下面sql

select * from tb_user where sex = 'm' order by age limit 10;

如果索引中没有包含age列的话,搜索会变得很慢,即使有索引如果需要翻页,如下sql

select * from tb_user where sex = 'm' order by age limit 10000,10;

sql也会变得很慢,因为mysql需要花费大量的时间进行丢弃数据

同样这种比较好的方式也是使用延迟关联,将sql改为下面语句

select * from tb_user user2 join
( select id from tb_user where sex = 'm' order by age limit 10000,10) user1
on user1.id = user2.id;