您现在的位置是:群英 > 数据库 > MySQL数据库
MySQL 索引有什么用?匹配方式有哪些?
Admin发表于 2021-11-13 17:39:301056 次浏览

    这篇文章给大家分享的是MySQL 索引的内容,下文将介绍索引的优点、用处、分类、技术名词以及匹配方式这些,文中介绍的非常详细,对大家学习和理解MySQL 索引有一定的帮助,感兴趣的朋友接下来一起跟随小编看看吧。

    对于高级开发,我们经常要编写一些复杂的sql,那么防止写出低效sql,我们有必要了解一些索引的基础知识。通过这些基础知识我们可以写出更高效的sql。

01 索引的优点

  • 大大减少服务器需要扫描的数据量,也就是IO量
  • 帮助服务器避免排序和临时表(尽量避免文件排序,而是使用索引排序)
  • 将随机IO变成顺序IO

02 索引的用处

  • 快速查找匹配where子句中的行
  • 如果可以在多个索引中选择,mysql通常会使用找到最少行的索引
  • 如果表具有多列索引,则优化器可以使用索引的任何最左前缀来查找行
  • 当有表连接的时候,从其他表检索行数据
  • 查找特定索引列的min和max的值
  • 如果排序或者分组时可用索引的最左前缀完成的,则对表进行排序和分组
  • 在某些情况下,可以优化查询以检索数据值而无需查找数据行

03 索引的分类

数据库默认建立的索引是给唯一键建立的

  • 主键索引(唯一且非空)
  • 唯一索引(唯一可为空)
  • 普通索引(普通字段的索引)
  • 全文索引(一般是varchar,char,text类型建立的,但很少用)
  • 组合索引(多个字的建立的索引)

04 索引的技术名词

1. 回表

name字段是普通索引,从name列的B+树找到主键,再从主键的B+树找到最终的数据,这就是回表。(主键索引的叶子节点保存的是列的所有数据,但是普通所有的叶子结点保存的是对应的主键ID)

如图:一个use表中name建立的索引结构sql是select * from use where name='sun'首先会通过name这个非主键索引找到sun对应的主键Id=2,然后通过id=2在主键索引中找到整个行数据,并返回,这个就是回表。

2. 覆盖索引

在非主键索引上可以查询到所需要的字段,不需要回表再次查询就叫覆盖索引。

如上图name索引,sql是 select id,name from user where name ="1" ,id的值在第一步非主键索引就已经有了,就不需要根据ID到主键索引中查询行数据了。

3. 最左匹配

组合索引中 先匹配左边,再继续向后匹配;比如user表中有name+age组成的联合索引,select * from user where name="纪先生" and age = 18 就符合最左匹配,可以用的索引。而select * from user where age = 18就不符合,用不到这个索引。

扩展;

如果是下面两个sql怎么建立索引

select * from user where name="纪先生" and age = 18;
select * from user where age = 18;

由于最左匹配原则:只需要建立一个组合索引age+name即可

如果是下面三个sql呢

select * from user where name="纪先生" and age = 18;
select * from user where name= "纪先生";

建立name+age和age索引,或者建立age+name和name索引,看着两个都可以。

其实name+age和age更好,因为索引也是需要持久化存储的,占用磁盘空间,读取的时候也是占用内存的,name+age和age+name这两个占用是一样的,但是name和age单独比较,肯定age占用空间更少,name更长(索引越大,IO次数可能更多)

注意!注意!注意!:

在看很多文章的时候,经常看到一些对于最左匹配错误的举例:

如果索引是name+age的组合索引,sql是select * from user where age = 18 and name="纪先生"很多人认为这种是不能走索引,实际上可以的。mysql的优化器会优化调整顺序的,调整成 name="纪先生" and age = 18

4. 索引下推

组合索引中尽量利用索引信息,来尽可能的减少回表的次数

案例:还是 name+age的组合索引如果没有索引下推的查询是 在组合索引中通过name查询所有匹配的数据,然后回表根据ID查询对于的数据行,之后在筛选出符合age条件的数据。索引下推就是组合索引中通过name查询匹配再根据age找到符合的数据ID,然后回表根据ID查询对应行数据,明显会减少数据的条数

05 索引匹配方式

mysql官网准备了一些学习测试的数据库,可以直接下载通过source导入到我们自己的数据库

官网地址:dev.mysql.com/doc/index-o…

如上图下载zip, 其中包含了sakila-schema.sql和sakila-data.sql,分别是sakila的库,表和数据的创建脚本。

mysql> source /Users/ajisun/Downloads/sakila-db/sakila-schema.sql;
mysql> source /Users/ajisun/Downloads/sakila-db/sakila-data.sql;

需要通过explain来查看索引的执行情况,执行计划以前有文章详细讲过,具体参考执行计划explain

1. 全值匹配

指和某个索引中的所有列进行匹配,例如使用数据库sakila中的staff

新建一个三个字段的联合索引:

mysql> alter table staff add index index_n1(first_name,last_name,username);

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer' and username='Mike'复制代码

其中的ref是三个const, 用到三个字段,能全匹配一条数据

2. 最左前缀匹配

只匹配组合索引中前面几个字段

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer';

ref只出现2个const,比上面全值匹配少一个,就只匹配了前面两个字段

3. 匹配列前缀

可以匹配某一列的的开头部分,像like属性

执行sql:

mysql> explain select * from staff where first_name like 'Mi%';

type=range ,是个范围查询,可以匹配一个字段的一部分,而不需要全值匹配

如果有模糊匹配的字段不要放在索引的最前面,否则有索引也不能使用,如下

4. 匹配一个范围值

可以查找某一个范围的数据

mysql> explain select * from staff where first_name > 'Mike';

5. 精确匹配某一列并范围匹配另一列

可以查询第一列的全部和另一列的部分

mysql> explain select * from staff where first_name = 'Mike' and last_name like 'Hill%';

6. 只访问索引的查询

查询的时候只需要访问索引,不需要访问数据行,其实就是索引覆盖

mysql> explain select first_name,last_name,username from staff where first_name='Mike' and last_name='Hillyer';

extra=Using index 说明是使用了索引覆盖,不需要再次回表查询。

其实一张表中有索引并不总是最好的。总的来说,只有当索引帮助存储引擎快速提高查找到记录带来的好处大于其带来的额外工作时,索引才是有效的。对应很小的表,大部分情况下没有索引,全表扫描更高效;对应中大型表,索引时非常有效的;但是对于超大的表,索引的建立和使用代价也就非常高,一般需要单独处理特大型的表,例如分区,分库,分表等。

    本文对MySQL 索引有很详细的介绍,对新手学习和深入理解MySQL 索引都有一定的参考价值,有这方面学习需要的朋友可以参考。最后,想要了解更多MySQL 索引的内容,大家可以关注群英网络其它相关文章。

文本转载自PHP中文网

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:mmqy2019@163.com进行举报,并提供相关证据,查实之后,将立刻删除涉嫌侵权内容。

标签: MySQL 索引
相关信息推荐
2022-05-18 16:10:48 
摘要:查询mysql编码的方法:1、使用“show variables like 'character_set_database';”语句,可查看数据库编码格式;2、使用“show create table 数据表名;”语句,可查看数据表的编码。
2022-10-18 17:33:08 
摘要:解决mysql服务无法启动的1069错误方法:1、在管理用户中找到mysql用户并重新设置mysql密码;2、在服务中找到mysql服务选项,在属性中通过更改后的密码重新登录mysql服务即可。出现1069错误的原因是更改了服务器的登录密码。
2022-06-09 17:55:39 
摘要:很长一段时间,我认为后端开发,在安全性方面最容易出问题的地方就在于SQL注入。通过 where 1=1这种魔幻的SQL写法,就可以很容易的对一个存在问题的系统进行攻击,以至于最终演进出sqlmap这样的神器存在。
群英网络助力开启安全的云计算之旅
立即注册,领取新人大礼包
  • 联系我们
  • 24小时售后:4006784567
  • 24小时TEL :0668-2555666
  • 售前咨询TEL:400-678-4567

  • 官方微信

    官方微信
Copyright  ©  QY  Network  Company  Ltd. All  Rights  Reserved. 2003-2019  群英网络  版权所有   茂名市群英网络有限公司
增值电信经营许可证 : B1.B2-20140078   粤ICP备09006778号
免费拨打  400-678-4567
免费拨打  400-678-4567 免费拨打 400-678-4567 或 0668-2555555
微信公众号
返回顶部
返回顶部 返回顶部