Mysql索引的最左前缀原则

通常我们在建立联合索引的时候,也就是对多个字段建立索引,相信建立过索引的同学们会发现,无论是oralce还是mysql都会让我们选择索引的顺序,比如我们想在a,b,c三个字段上建立一个联合索引,我们可以选择自己想要的优先级,a、b、c,或者是b、a、c 或者是c、a、b等顺序。为什么数据库会让我们选择字段的顺序呢?不都是三个字段的联合索引么?这里就引出了数据库索引的最左前缀原理。

比如:索引index1:(a,b,c)有三个字段,我们在使用sql语句来查询的时候,会发现很多情况下不按照我们想象的来走索引。

select * from table where c = '1' 这个sql语句是不会走index1索引的,select * from table where b =‘1’ and c ='2' 这个语句也不会走index1索引。

什么语句会走index1索引呢?

答案是:

select * from table where a = '1'

select * from table where a = '1' and b = ‘2’

select * from table where a = '1' and b = ‘2’ and c='3'

mysql里创建‘联合索引’的意义?
"一个顶三个"。建了一个(a,b,c)的复合索引,那么实际等于建了(a),(a,b),(a,b,c)三个索引,因为每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,这可是不小的开销!
覆盖索引。同样的有复合索引(a,b,c),如果有如下的sql: select a,b,c from table where a=1 and b = 1。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一
索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql:select * from table where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W*10%=100w 条数据,然后再回表从100w条数据中找到符合b=2 and c= 3的数据,然后再排序,再分页;如果是复合索引,通过索引筛选出1000w *10% *10% *10%=1w,然后再排序、分页,哪个更高效,一眼便知

为什么要使用联合索引
减少开销。建一个联合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销!
覆盖索引。对联合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。
效率高。索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W10%=100w条数据,然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据,然后再排序,再分页;如果是联合索引,通过索引筛选出1000w10% 10% *10%=1w,效率提升可想而知!

Mysql索引的最左前缀原则-上流阁

*文章为作者独立观点,不代表上流阁立场
本文由 江风成 授权 上流阁 发表,并经上流阁编辑。转载此文章须经作者同意,并请附上出处(上流阁)及本页链接。原文链接https://www.o6c.com/java/2019/11/08/1147.html
发表评论

坐等沙发
相关文章
jar包解压后,修改完配置文件,再还原成jar包
jar包解压后,修改完配置文件,再还原成…
JAVA DES 十六进制加密
JAVA DES 十六进制加密
HTML中Data URI scheme BASE64 文件的拼接头
HTML中Data URI scheme BASE64 文件的拼…
mysql 优化方案整理
mysql 优化方案整理
windows BAT 文件 多个命令同步执行
windows BAT 文件 多个命令同步执行
java 中 封装BigDecimal常用计算类MathUtils
java 中 封装BigDecimal常用计算类MathU…
javaweb开发程序员php开发,微信开发。接受定制开发

最新评论