sql 添加字段_SQL优化之索引篇：无索引，隐式转换

概述

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

---

索引问题

1、无索引

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

2、隐式转换

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

---

无索引实例

表结构：

执行计划：

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

验证mo字段的过滤性:

可以看到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> 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定义不当导致，因此我们在设计开发阶段，要避免数据库字段定义，避免出现隐式转换。

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下!