189 8069 5689

mysql索引失效怎么优化,mysql索引失效怎么优化数据

SQL优化(二)

SQL优化一: sql优化(一)

文水ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景,ssl证书未来市场广阔!成为创新互联公司的ssl证书销售渠道,可以享受市场价格4-6折优惠!如果有意向欢迎电话联系或者加微信:13518219792(备注:SSL证书合作)期待与您的合作!

上片文章已经详细介绍了explain各个字段的含义,以及什么情况应该建立索引,什么情况不需要建立索引以及sql语句性能的判断依据,接下来我介绍下如何合理的建立索引。

sql语句:select id,author_id from article where category_id = 1 and comments1 order by views desc limit 1;

分析:首先我们根据where后面的条件建立符合索引,然后根据order by后面的字段建立索引,因此建立索引idx_article_ccv,即以(category_id,comments,views)数据列建立复合索引,但由于comments是一个范围,按照BTree索引的原理,先排序category_id,如果遇到相同的category_id则再排序comments,如果遇到相同的comments则再排序views,又因为comments字段在复合索引里处于中间位置,而comments1是一个条件(是一个范围值),在复合索引的一个范围值的数据列后面的索引全部失效,mysql无法利用索引再对后面的views部分进行检索,也就是说views无法按照索引排序,所以explain下此sql语句,type为range,extra使用的是Using filesort,这是比较糟糕的。所以我们放弃comments这个范围字段,建立索引idx_article_cv,即以(category_id,views)数据列建立复合索引,explain 此sql,type变成了ref,extra的using filesort也变成了using index,这就变得好多了。

索引:idx_article_cv,即以(category_id,views)数据列建立复合索引

前段时间做了一个销售精细化项目,是公司crm项目的一个大模块,大致就是为销售人员制定指标,实现销售目标从区域到团到业务员到客户,实时跟踪业务员所负责客户的下单量的情况。这就存在许多关联关系,区域-团,团-业务员,业务员-客户,这使得sql常常需要关联多张表。

sql语句:SELECT

tu.fuserid,

tu.faccount,

tu.fphone,

tu.fcertificationtype,

tu.fcertificatename,

tu.fkeyarea,

tu.fkeyareatext,

DATE_FORMAT(tcr.fupdatetime,'%Y-%m-%d %H:%i:%s') as fupdatetime,

tag.forggroupid,

tag.forggroupname,

tug.forguserid,

tug.fusername,

tug.fuserphone,

tag.fcitycode

FROM t_finedt_user AS tu

LEFT JOIN t_finedt_customer_relation AS tcr

ON tu.fuserid = tcr.fuserid

LEFT JOIN t_finedt_usergroup AS tug

ON tcr.forguserid = tug.forguserid

and tcr.forggroupid = tug.forggroupid

LEFT JOIN t_finedt_areagroup AS tag

ON tug.forggroupid = tag.forggroupid

where tu.fkeyarea=? and tu.fuserid=? and tug.forggroupid = ?

分析:上面的sql是左连接,左边的表一定是全表查询,所以要建立右边表对应关联字段的索引,在表t_finedt_user上建立tu_fuserid_fkeyarea索引,即以(fuserid,fkeyarea)字段建立索引,在表t_finedt_customer_relation 上建立tcr_forguserid_forggroupid索引,即以(forguserid,forggroupid)字段建立索引,在表t_finedt_usergroup 上建立tug_forguserid_forggroupid索引,即以(forguserid,forggroupid)字段建立索引,在表t_finedt_areagroup上建立tag_forggroupid索引,即以(forggroupid)字段建立索引。建立索引后,sql查询速度明显快了很多

索引:tcr_forguserid_forggroupid,tu_fuserid_fkeyarea,tug_forguserid_forggroupid,tag_forggroupid

1、尽可能减少join语句中的NestedLoop的循环次数,永远用小结果集驱动大结果集

2、优先优化NestedLoop的内层循环

3、保证join语句总被驱动表上的join字段已经被索引

4、当无法保证被驱动表join条件字段被索引,且内存资源充足的前提下,不要太吝啬joinBuffer的设置

1、全值匹配我最爱

2、最佳左前缀原则——如果索引了多列,要遵守最左前缀原则,指的是查询从索引的最左前列开始并且不跳过索引中的列

3、并在索引列上做任何操作(计算、函数、自动or手动类型转换),这些会导致索引失效而转向全表扫描

4、存储引擎不能使用索引中范围条件右边的列,范围之后的索引全失效

5、尽量使用覆盖索引(之访问索引的查询(索引列和查询的列一致)),减少select *

6、mysql在使用不等于(!=、、)的时候无法使用索引会导致全表扫描。

7、is null、is not null也无法使用索引。

8、like以通配符开头("%abc.."),mysql索引失效也会变成全表扫描的操作。

9、字符串不加单引号也会引起索引失效

10、少用or,用它来连接时会索引失效。

1、对于单值索引,尽量选择针对当前query过滤性更好的索引

2、在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好

3、在选择组合索引的时候,尽量选择尽可能包含当前query中的where字句中更多字段的索引

4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

全值匹配我最爱,最左前缀要遵守

带头大哥不能死,中间兄弟不能断

索引列上少计算,范围之后全失效

like百分写最右,覆盖索引不写里

不等空值还有or,索引失效要少用

var引号不可丢,sql高级也不难

mysql 避免索引失效

where条件==order by 条件==group by 条件 按顺序遵守 最佳左前缀法则

假设创建了复合索引:a,b,c

不在索引列上做任何的操作(计算、函数、显式或隐式的类型转换),否则会导致索引失效而转向全表扫描

1、字符不加单引号会导致索引失效

name字段为varchar类型

这条sql发生了隐式的类型转换:数值==字符串。所以导致了全表扫描,索引失效

应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

mysql中的范围条件有:in/not in、 like、 、BETWEEN AND ;

后面的索引失效

in会导致索引全部失效!!!

BETWEEN AND 范围条件不会导致索引失效!!!

尽量让索引列和查询列一致;减少select * 的使用

1、查询表结构

2、查询表的索引结构

联合索引:name,age,post;说明add_time字段没有添加索引

3、查看select * 的执行计划

4、查看 select name,age,pos的执行计划

5、如果select只用一部分索引

like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。

解决:可以使用 覆盖索引 来解决这个问题!

1、先查看表上的索引

id、name、age、pos 四个字段上都有索引; 注意:name是联合索引中的第一个,带头大哥!

2、查看表结构

有个add_time字段没有用到索引

3、查看执行计划

使用UNION ALL

假设创建了联合索引 x(a,b,c)

ps:like虽然也是范围查询但是区别于、,%用在最前面就只用到索引a了;%用在最后面可以用到a+b+c!

下面的sql几乎违背了上面的所有原则,索引依然全部生效。因为select是索引覆盖的,select里不包含没有建立索引的字段。因此总是用到索引的。可以看出来索引覆盖在sql优化中的作用性

为什么MySQL字符串不加引号索引会失效?这个答案是我见过最靠谱

作为一名程序员,在求职面试时,不知你有没有遇到类似这样的问题。

张工是一名java程序员,最近到一家软件公司应聘软件开发岗位,面试官问了他关于MySql索引这样的一个问题。

对于这个问题张工之前在做项目时也曾遇到,那时候字段明明是加了索引,可不明白为什么还是很慢。后加上引号就正常了,为了赶项目进度,张工也没有再去留意。

现在面试官突然这么一问,张工也说不出个所以然来。

面试官让他回去等通知。

我们知道MySql索引可以加快数据检索速度,这也是使用的索引的最主要原因。但有时候使用不当就会遇到索引失效问题,譬如在MySQL字符串类型查询时不加引号索引会失效,是因为MySQL内部进行了隐式转换。

那为什么会发生隐式转换?又是怎么转换的呢?

今天我们来聊聊关于MySql索引失效的话题。

先来看看一般导致索引失效的有哪些?

如果一张表的索引有多个,要遵守最佳左前缀法则,即查询从索引的最左前列开始并且不跳过索引中的列。

用户表tb_user字段 id,name,age,sex

创建索引为idx_user_name

执行语句:

这时候就会导致索引失效

在索引列上做加工操作,查询时会导致索引失效,从而导致全表扫描。所以,建议不要在索引列上做任何操作。

举个例子,例如订单表tb_order有个索引是dt(日期), 字段数据存放的格式是这样的2021-12-10 这样的,如果有个需求需要根据dt,格式是20220207这样的来查询,这时候就不要对dt进行格式转换了,

这样索引就失效了。

而是应该对 20220207做格式处理

这样dt索引才不会失效。

例如我们在订单表tb_order建立了索引idx_order_id,order_id字段类型为varchar

在查询时如果使用where order_id= 20220207123654100,这样的查询方式会直接造成索引失效。

要让索引生效,正确的用法为

假如有张用户表tb_user,创建的索引为idx_user_name_age_sex_phone 其中name、age、sex都加了索引。

执行语句

上面这条sql语句只会命中name和age索引,sex索引会失效,复合索引失效需要查看key_len的长度。

再来看一个例子:

从这两条SQL执行的结果我们可以看出,执行第一条SQL没有使用到索引,而执行第二条SQL时使用到了索引。这是为什么呢?

我们需要先了解下mysql索引优化器工作的原理。选择索引是优化器工作,优化器工作有自己的一套规则,如果等号两边的数据类型不一致,则会发生隐式转换。

基于这条规则,我们回过头看看

这条SQL语句执行时就会变为

由于对索引列进行了函数操作,所以才导致索引失效,从而全表扫描了。

那么问题来了,细心的你不知有没有留意到为什么是把左侧的列转为int类型,而不是把右侧的值转成字符串类型呢?

什么情况下把数字转为字符串,什么情况下把字符串转为数字,优化器它是根据什么规则来进行判断的?其实规则也并不复杂。

根据这个规则,我们再回过头看看之前的查询语句

select '12345678936' = 12345678936

返回1 所以这时候就把左侧的列值12345678936转成数字。

关于MySql索引失效的问题先简单写到这,建议平时在做项目时还是要多了解下原理,如果你了解其背后的原理,求职面试时和面试官交流起来就会很舒服了,相信能为这次面试加分,提高被录用的概率。

为什么MySQL字符串类型查询时不加引号索引会失效?这是因为要查询的字符串字段没有加引号时,MySQL内部进行了隐式转换,此次查询会导致全表扫描,所以慢了。

总结:

在索引列上进行了函数操作,MySQL内部会进行了隐式转换,导致索引失效,从而产生全表扫描。

由于笔者知识及水平有限,文中错漏之处在所难免,如有不足之处,欢迎交流。

拓展

索引创建

1、主键索引:

2、唯一索引:

3、普通索引:

4、全文索引:

alter table table_name add fulltext (column)

5、联合索引:

索引删除


名称栏目:mysql索引失效怎么优化,mysql索引失效怎么优化数据
本文路径:http://cdxtjz.cn/article/hsgssc.html

其他资讯