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
发表评论

坐等沙发
相关文章
Linux CentOS 7.2版mysql 备份命令
Linux CentOS 7.2版mysql 备份命令
Linux CentOS 7.2版 JDK 安装
Linux CentOS 7.2版 JDK 安装
Java技术中Mysql之Mybaitis中IN的函数拼接方法
Java技术中Mysql之Mybaitis中IN的函数拼…
查询数据库所有表和字段及其注释(mysql)
查询数据库所有表和字段及其注释(mysql)
java和安卓中对Map的key值和value值进行升序降序排序干货
java和安卓中对Map的key值和value值进行…
java用户metadata-extractor读取照片信息错误Exception in thread “main” java.lang.NoClassDefFoundError: com/adobe/xmp/XMPException
java用户metadata-extractor读取照片信…
javaweb开发程序员php开发,微信开发。接受定制开发

最新评论