国内最专业的IT技术学习网

Mysql数据库

当前位置:主页 > Mysql数据库 >

详解MySQL数据库常见的索引问题:无索引,隐式转换,附实例说明银河官网:

发布时间:2019/09/06标签:   mysql      数据库      索引    点击量:

原标题:详解MySQL数据库常见的索引问题:无索引,隐式转换,附实例说明银河官网:

?

详解MySQL数据库常见的索引问题:无索引,隐式转换,附实例说明银河官网:

概述

在这些年的工作之中,由于SQL问题导致的数据库故障层出不穷,而索引问题是SQL问题中出现频率最高的,常见的索引问题包括:无索引,隐式转换。

索引问题

1、无索引

数据库中出现访问表的SQL无索引导致全表扫描,如果表的数据量很大,扫描大量的数据,应用请求变慢占用数据库连接,连接堆积很快达到数据库的最大连接数设置,新的应用请求将会被拒绝导致故障发生。

2、隐式转换

隐式转换是指SQL查询条件中的传入值与对应字段的数据定义不一致导致索引无法使用。常见隐士转换如字段的表结构定义为字符类型,但SQL传入值为数字;或者是字段定义collation为区分大小写,在多表关联的场景下,其表的关联字段大小写敏感定义各不相同。隐式转换会导致索引无法使用,进而出现上述慢SQL堆积数据库连接数跑满的情况。

无索引实例

表结构:

详解mysql数据库常见的索引问题--无索引,隐式转换,附实例说明


执行计划:


从上面的SQL看到执行计划中ALL,代表了这条SQL执行计划是全表扫描,银河官网,每次执行需要扫描707250行数据,这是非常消耗性能的,该如何进行优化?添加索引。

验证mo字段的过滤性:

详解mysql数据库常见的索引问题--无索引,隐式转换,附实例说明


可以看到mo字段的过滤性是非常高的,进一步验证可以通过select count(*) as all_count,count(distinct mo) as distinct_cnt from user,通对比 all_count和distinct_cnt这两个值进行对比,如果all_cnt和distinct_cnt相差甚多,银河网上开户,则在mo字段上添加索引是非常有效的。

添加索引

mysql>?alter?table?user?add?index?ind_mo(mo);?

mysql>SELECT?uid?FROM?`user`?WHERE?mo=13772556391?LIMIT?0,1;?

执行计划:

详解mysql数据库常见的索引问题--无索引,隐式转换,附实例说明

隐式转换

表结构:


执行计划:

mysql>?explain?extended?select?uid?from`user`?where?mo=13772556391?limit?0,1;?

mysql>?show?warnings;?

Warning1:Cannot?use?index?'ind_mo'?due?to?type?or?collation?conversion?on?field?'mo'??

Note:select?`user`.`uid`?AS?`uid`?from?`user`?where?(`user`.`mo`?=?13772556391)?limit?0,1?

如何解决:


上述案例中由于表结构定义mo字段后字符串数据类型,而应用传入的则是数字,进而导致了隐式转换,索引无法使用,所以有两种方案:

第一,将表结构mo修改为数字数据类型。

第二,修改应用将应用中传入的字符类型改为数据类型。

总结

在使用索引时,我们可以通过explain+extended查看SQL的执行计划,判断是否使用了索引以及发生了隐式转换。

由于常见的隐式转换是由字段数据类型以及collation定义不当导致,因此我们在设计开发阶段,要避免数据库字段定义,避免出现隐式转换。

【编辑推荐】

这句简单的SQL,如何加索引?颠覆了我多年的认知

如何在磁盘上查找 MySQL 表的大小

MySQL性能优化之Innodb事务系统,值得收藏

9月数据库排行:Microsoft SQL Server 分数罕见下滑

分享一次生产MySQL数据库主备切换演练

版权信息Copyright ? 银河官网 版权所有??? ICP备案编号:鲁ICP备09013610号