正确利用条件字段索引
- 函数操作
- 1）验证对条件字段使用函数是否能走索引
- 2）对条件字段使用函数操作不走索引的原因
- 3）函数操作的SQL优化
- 隐式转换
- 1）隐式转换定义及规则
- 2）验证隐式转换能否走索引
- 3）不走索引的原因
- 4）隐式转换SQL优化
- 模糊查询
- 1）分析模糊查询
- 2）模糊查询优化
- 范围查询
- 1）构造不能使用索引的范围查询
- 2）范围查询SQL优化
- 计算操作
- 1）计算操作的执行效率
- 2）计算操作SQL优化
- 总结
MySQL中，如果需要查找某一行的值，可以先通过索引找到对应的值，之后根据索引匹配的记录找到需要返回的数据行。MySQL索引会有专门的一组笔记进行整理，这里只是对在条件字段中正确使用索引进行总结。
条件字段存在索引，但是不走索引导致慢查询的情况有如下几种。
函数操作
有时会借助MySQL函数实现查询。
1）验证对条件字段使用函数是否能走索引
创建测试表，
use test;
drop table if exists t1;
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
a varchar(20) DEFAULT NULL,
b int(20) DEFAULT NULL,
c datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_a (a) USING BTREE,
KEY idx_b (b) USING BTREE,
KEY idx_c (c) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1，则删除 */
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t1(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1(); /* 运行存储过程insert_t1 */
update t1 set c = '2019-05-22 00:00:00'; /* 更新表t1的c字段，值都为'2019-05-22 00:00:00' */
update t1 set c = '2019-05-21 00:00:00' where id=10000; /* 将id为10000的行的c字段改为与其它行都不一样的数据 */对于上表中的几率，查询单独某一天的所有数据，
# DATE() 函数返回日期或日期/时间表达式的日期部分
explain select * from t1 where date(c) = '2019-5-21';返回分析结果，
执行计划中type字段为ALL，参考explain返回结果，可知此查询未走索引。
2）对条件字段使用函数操作不走索引的原因
本例中字段c普通索引的B+树索引如下，
索引树中存储的是索引c的实际值和其对应的主键值。如果使用DATE函数返回的2019-05-21进行匹配，在索引树中不会有匹配结果，所以放弃索引，使用全表扫描。
3）函数操作的SQL优化
本例中，可以将SQL优化为字段c实际值相匹配的形式。如使用范围查询对某天的记录进行搜索，
explain select * from t1 where c>='2019-05-21 00:00:00' and c<='2019-05-21 23:59:59';返回结果如下，
类似某一天或者某个月数据的需求，建议写为范围查询的形式，可以让查询走索引。在实际开发中，尽量避免对条件字段使用函数。
隐式转换
1）隐式转换定义及规则
隐式转换： 当条件查询值的类型与字段类型不一致时（或者说等号两端的数据类型不一致），就会发生类型转换，使操作能够兼容。
隐式转换规则
- 如果一个或两个参数都是NULL，比较的结果是NULL，除了NULL安全的<=>相等比较运算符。对于NULL <=> NULL，结果为true。不需要转换
- 如果两个参数都是字符串，则进行字符串比较
- 如果两个参数都是整数，则进行整数比较
- 十六进制值如果不与数字比较，则被视为二进制字符串
- 如果其中一个参数是十进制值，则比较取决于另一个参数。如果另一个参数是十进制或整型值，则将其作为十进制值进行比较；如果另一个参数是浮点值，则将其作为浮点值进行比较。
- 如果其中一个参数是时间戳或日期时间，而另一个参数是常数，则常数在执行比较之前会转换成时间戳。
为了安全起见，在进行比较时，总是使用完整的datetime、date或time字符串。例如，为了在使用BETWEEN with date或time值时获得最佳结果，可以使用CAST()来显式地将这些值转换为所需的数据类型。 - 在所有其他情况下，参数都作为浮点数(实数)进行比较。例如，字符串和数字操作数的比较将作为浮点数的比较。
隐式转换参见MySQL文档。
2）验证隐式转换能否走索引
explain select * from t1 where a = 1000;返回结果如下，
type字段显示的是ALL，是最差的情况即全表扫描。key项显示没有走字段a的索引。
3）不走索引的原因
表中字段a的类型是varchar，而上面查询语句中条件字段的值没有加引号，依据隐式转换的规则，
在所有其他情况下，参数都作为浮点数(实数)进行比较。例如，字符串和数字操作数的比较将作为浮点数的比较。
相当于在执行语句时，MySQL内部会先把字段a的值转换成int型再去做判断，即内部执行，
select * from t1 where cast(a as signed int) = 1000;对索引字段使用函数操作时，是不会走索引的。本质上触发的还是第一个原因。
4）隐式转换SQL优化
写SQL时，先明确字段类型，根据字段类型写SQL，保证等号两端的类型是相同的。
模糊查询
1）分析模糊查询
有时候根据某个字段的关键字进行模糊查询，
explain select * from t1 where a like '%1111%';返回结果如下，
各项结果显示，即使a字段存在索引，但是上面的模糊匹配没有走索引。通配符不走索引会在索引专题的笔记中说明。
2）模糊查询优化
让模糊查询必须包含条件字段前面的值，即能省略开头的%通配符就尽量省略（即like查询不能以%开头，否则不走索引）。当模糊查询的开头字符明确时，MySQL是能够走索引的，
explain select * from t1 where a like '1111%';返回结果，
如果只知道中间值，需要模糊查询时，建议使用ElasticSearch或者其他搜索引擎。
范围查询
范围查询在实际开发中很常见，但是有时会出现范围查询的条件字段存在索引但不走索引的情况。
1）构造不能使用索引的范围查询
假设取出b字段范围在1到2000的数据，SQL如下，
explain select * from t1 where b >= 1 and b <= 2000;返回结果如下，
b字段没有走索引查询的原因是优化器根据检索比例、表大小、IO块大小等进行评估是否使用索引。如果单次查询数据量过大，优化器不会走索引。
2）范围查询SQL优化
降低单次查询范围，分为多次查询，
explain select * from t1 where b >= 1 and b <= 1000;
explain select * from t1 where b >= 1001 and b <= 2000;返回结果如下，
范围查询无法走索引的情况经常出现，在执行这类SQL前应先做explain分析，确定能走索引再进行操作。
计算操作
对条件字段进行运算时需要格外注意运算的方式。
1）计算操作的执行效率
explain select * from t1 where b-1 = 1000;返回结果如下，
2）计算操作SQL优化
务必将计算操作放在等号右侧，不要作用在条件字段上。
explain select * from t1 where b = 1000+1;返回结果如下，
一般对条件字段进行计算时，建议使用程序代码实现，而不是通过MySQL实现。如果避免不了在MySQL中进行运算，务必把运算放在等号右侧。
总结
