您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

面对 MySQL 查询索引失效,程序员的六大优化技巧!

时间:2020-02-22 15:37:42  来源:  作者:

作者 | 曹建

责编 | 屠敏

我们都知道创建索引的目的是快速从整体集合中选择性地读取满足条件的一部分集合。MySQL中一张表是可以支持多个索引的。但是,你写SQL语句的时候,并没有主动指定使用哪个索引。不知道你有没有碰到过这种情况,一条创建了索引的SQL语句在查询过程中却没有使用索引,或是一条本来可以执行的很快的语句,却由于MySQL选错了索引,而导致查询速度变得很慢?充分优化和利用索引能够大大提高数据的查询效率,但是在实际的应用中MySQL可能并不总会选择合适且效率高的索引。那么我们今天就一起来讨论下 MySQL 索引以及索引的优化,首先我们来看一个案例,下面是一张建表的SQL如下:

CREATETABLE`t_test3`(

`id`bigint( 11) NOTNULL,

`name`varchar( 32) DEFAULTNULL,

PRIMARY KEY( `id`),

KEY`t_test_name`( `name`)

) ENGINE= InnoDBDEFAULTCHARSET=utf -8;

使用以下的SQL查看对应的执行计划:

desc select* fromt_test3 wherenamein( 'a', 'b');

事实上,在建立表的sql中我们是对name这一列建立了索引,为何在执行计划的时候没有使用索引呢?

要找到这个原因,我们需要首先了解下SQL在mysql中的执行过程,MYSQL 的整个架构可以分为 server 层 和存储引擎层2个部分。Server 层 包括连接器,查询缓存,分析器,优化器,执行器等模块;存储引擎层 负责数据的存储与提取。其架构模式是插件式的,支持InnoDB、MyISAM、Memory等多个存储引擎,默认的是InnoDB。可以在建表的时候使用engine = memory来指定存储引擎 。

其中Server 层执行步骤如下:

第一步连接器:通过账号和密码连接到对应的数据库上,连接器负责与客户端建立连接,获取权限,维持和管理连接。连接分为长连接和短连接,长连接是指连接成功后,客户端不断有请求,则一直使用同一个连接。短连接:处理几个请求后,断开连接,之后的请求需要重新连接。

第二步查询缓存:建立连接之后,mysql拿到一个查询请求后,会先查询缓存中之前是否执行过这条语句,如果查询缓存命中,则查询结果直接返回给客户端,如果查询缓存不命中,就会继续后面的执行阶段。完成以后,执行结果会被存入查询缓存中。大多数情况下不建议使用查询缓存。如果缓存命中,mysql不需要执行后面的复杂操作,就可以直接返回结果,效率很高,但是查询缓存失效非常频繁,只要有对一个表的更新,这个表的所有查询缓存都会被清空,因此可能你费力地把结果缓存起来,还没使用,就被一个更新全部清空了。除非你的业务是一张静态表,很长时间才会更新一次,这种情况下可以使用查询缓存。

第四步优化器:优化器是数据库的一个核心子系统,你也可以把他理解为 MySQL 数据库中的一个核心模块或者一个核心功能模块。优化器的目的是按照一定原则来得到它认为的目标SQL在当前情形下最有效的执行路径,优化器的目的是为了得到目标SQL的执行计划。经过分析器,mysql就知道你要做什么了。SQL 在执行的过程中经过优化器,并由优化器生成 SQL 的执行计划。

传统关系型数据库里面的优化器分为CBO和RBO两种:

  • RBO--- Rule_Based Potimizer 基于规则的优化器:RBO所用的判断规则是一组内置的规则,这些规则是硬编码在数据库的编码中的,RBO会根据这些规则去从SQL诸多的路径中来选择一条作为执行计划(比如在RBO里面,有这么一条规则: 有索引使用索引。 那么所有带有索引的表在任何情况下都会走索引)所以,RBO现在被很多数据库抛弃(oracle默认是CBO,但是仍然保留RBO代码,MySQL只有CBO),RBO最大问题在于硬编码在数据库里面的一系列固定规则,来决定执行计划。 并没有考虑目标SQL中所涉及的对象的实际数量,实际数据的分布情况,这样一旦规则不适用于该SQL,那么很可能选出来的执行计划就不是最优执行计划了。
  • CBO---Cost_Based Potimizer 基于成本的优化器:CBO在会从目标诸多的执行路径中选择一个成本最小的执行路径来作为执行计划。 这里的成本他实际代表了MySQL根据相关统计信息计算出来目标SQL对应的步骤的IO,CPU等消耗。 也就是意味着数据库里的成本实际上就是对于执行目标SQL所需要IO,CPU等资源的一个估计值。 而成本值是根据索引,表,行的统计信息计算出来的(计算过程比较复杂)。

第五步执行器:开始执行的时候,首先会判断此次连接是否有对应的操作权限,如果没有,则返回没有权限的错误。如果有权限,则打开表继续执行。打开表的时候,执行器会根据表的引擎定义,去使用这个引擎提供的接口。

比如下面这条sql语句执行器流程是这样的:

select* fromt_test3 wherename= 'a';

  1. 调用InnoDB引擎接口获取这个表的第一行,判断name的值是不是a,如果不是则跳过,如果是则将这行存在结果集中。
  2. 调用引擎接口获取下一行,重复相应的判断逻辑,直到取到最后一行数据
  3. 执行器将遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

通过了解sql执行的过程以及优化器,发现mysql采用的是第二种基于成本的优化器,它会根据sql执行的成本选择合适的路径。所以可以推断出上面sql执行计划没有采用对应列的索引原因。当我在表中插入一万条数据的时候,再重新查看对应的执行计划时,发现此时,该sql的查询类型会使用range类型及使用name对应的索引进行查询。

当数据量比较小的时候,会使用all类型进行查询对应数据,当数据量比较大时,查询数据量增大时,会采用range类型,并使用对应列的索引进行查询。这便涉及到了数据库查询索引的离散度。离散度,外文 Measures of Dispersion,是指通过随机地观测变量各个取值之间的差异程度,用来衡量风险大小的指标。离散度在不超过全表的10%-15%的前提下索引才可以显示索引所具有的价值。当离散度超过该值的情况下全表扫描可能反倒比索引扫描更有效。我们所追求的目标就是创建全表扫描所无法比拟的有效索引。比如当我们对一张学生表信息中对性别添加索引,性别只有两种值,会产生大量的重复,离散度较小,使用性别索引会增加查询开销,使得在使用性别的索引查询时可能比没有性别索引的查询更慢。

基于数据库索引的离散度,可以参考以下两个建议进行创建索引:

  • 在允许的情况下,对具有较好离散度的列单独创建索引,这样可以提高该索引的使用弹性;
  • 对于离散度较差的列,通过对多列进行合理的组合来创建组合索引,虽然这样做在很大程度上降低了各个列的使用弹性,但是却可以发挥多个列的综合效应。

在实际应用的过程中,MySQL索引失效的情形很多。例如:在WHERE条件的LIKE关键字匹配的字符串以”%“开头,这种情况下,索引是不会起到作用的;WHERE条件中使用OR关键字来连接多个查询条件,如果有一个条件没有使用索引,那么其他的索引也不会起作用;多列索引的第一个字段没有使用,那么这个多列索引也不会起作用。使用in查询时,in查询条件超过数据库表的一半的时候也会失效。

根据这些情况,我们必须选择对索引有正确的理解,并不是创建索引就能增加查询速度。根据使用索引的特性,对创建索引的一些技巧总结如下:

  1. 首先数据量小的表不需要建立索引,因为数据量小的表即使建立索引也不会有大的用处,还会增加额外的索引开销 。
  2. 不经常引用的列不要建立索引,因为不常用,即使建立了索引也没有多大意义 。
  3. 经常频繁更新的列不要建立索引,因为肯定会影响插入或更新的效率 。
  4. 尽量避免在 where 子句中使用 ! = 或者 <> 操作符,查询引用会放弃索引而进行全表扫描。
  5. 数据类型越小越简单的索引更好。 越小越简单的数据类型通常在磁盘、内存和cpu缓存中需要的空间更少,处理起来更快。
  6. 尽量避免NULL: 在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。 可以采用0、一个特殊的值或者一个空串代替空值 。

在实际应用的过程中,mysql并不总会选择合理的索引进行查询,此时便可以使用force index(index name)来强制告诉mysql选择哪一个索引。使用一下sql查询:

desc select* fromt_test3 forceINDEX(t_test_name) wherenamein( 'a', 'b');

其对应的执行计划与上图的执行计划相同,采用的是sql中指定的索引。

因此我们在一些情况下首先可以适当的使用force index(indexname) 强制告诉mysql使用什么索引。force index( index name )指令可以指定本次查询使用哪个索引!一条sql只会用到一个索引,mysql优化器会计算出一个合适的索引,但是这个索引不一定是最好的。force index指令可以避免MySql优化器用到了一个低效的索引,并可以提高sql的执行效率。



Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  Tags: MySQL  点击:(7)  评论:(0)  加入收藏
一、为什么要搭建主从架构呢1.数据安全,可以进行数据的备份。2.读写分离,大部分的业务系统来说都是读数据多,写数据少,当访问压力过大时,可以把读请求给到从服务器。从而缓解数据...【详细内容】
2021-12-15  Tags: MySQL  点击:(12)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  Tags: MySQL  点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  Tags: MySQL  点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  Tags: MySQL  点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  Tags: MySQL  点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  Tags: MySQL  点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  Tags: MySQL  点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  Tags: MySQL  点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Tags: MySQL  点击:(31)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条