来自慕课网专栏《一线数据库工程师带你深入理解MySQL》
目录
创建测试表1 函数操作2 隐式转换3 模糊查询4 范围查询5 计算操作
创建测试表
CREATE TABLE `t1` ( /* 创建表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() /* 创建存储过程insert_t1 */
begin
declare i int; /* 声明变量i */
set i=1; /* 设置i的初始值为1 */
while(i<=10000)do /* 对满足i<=10000的值进行while循环 */
insert into t1(a,b) values(i,i); /* 写入表t1中a、b两个字段，值都为i当前的值 */
set i=i+1; /* 将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字段改为与其它行都不一样的数据，以便后面实验使用 */
1 函数操作
mysql> explain select id,a,b from t1 where date_format(c,'%Y-%m-%d')=date_format('2019-05-21', '%Y-%m-%d') ;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10302 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
ref = null，rows = 10302，全盘扫描，索引并没有起作用。
原因：对条件字段做函数操作走不了索引。索引树中存储的是列的实际值和主键值。如果拿 ‘2019-05-21’ 去匹配，将无法定位到索引树中的值，因为值为'2019-05-22 00:00:00'。因此放弃走索引，而选择全表扫描。
解决办法
改成 c 字段实际值相匹配的形式，因此可以改成范围查询
mysql> explain select * from t1 where c>='2019-05-21 00:00:00' and c<='2019-05-21 23:59:59';
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | idx_c | idx_c | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
2 隐式转换
mysql> explain select * from t1 where a=1000;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | idx_a | NULL | NULL | NULL | 10302 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
ref = null，rows = 10302，全盘扫描，索引并没有起作用。
原因：
mysql> desc t1;
+-------+-------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-------------------+-------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| a | varchar(20) | YES | MUL | NULL | |
| b | int(20) | YES | MUL | NULL | |
| c | datetime | NO | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------+-------------+------+-----+-------------------+-------------------+
a是 varchar(20)，语句中 a 字段条件值没加单引号，导致 MySQL 内部会先把a转换成int型，再去做判断。
解决办法
索引字符串列条件添加单引号
mysql> explain select * from t1 where a='1000';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | idx_a | idx_a | 83 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
经验分享：
建议在写SQL时，先看字段类型，然后根据字段类型写SQL。
3 模糊查询
mysql> explain select * from t1 where a like '%1111%';
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10302 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
解决办法
修改业务，让模糊查询必须包含条件字段前面的值
mysql> explain select * from t1 where a like '1111%';
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | idx_a | idx_a | 83 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
经验分享：
如果条件只知道中间的值，需要模糊查询去查，那就建议使用ElasticSearch或其它搜索服务器。
4 范围查询
mysql> explain select * from t1 where b>=1 and b <=3000;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | idx_b | NULL | NULL | NULL | 10302 | 29.12 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
原因：优化器会根据检索比例、表大小、I/O块大小等进行评估是否使用索引。比如单次查询的数据量过大，优化器将不走索引。
解决办法
降低单次查询范围，分多次查询：
mysql> explain select * from t1 where b>=1 and b <=1000;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | idx_b | idx_b | 5 | NULL | 1000 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
mysql> explain select * from t1 where b>=1001 and b <=2000;
mysql> explain select * from t1 where b>=2001 and b <=3000;
经验分享：
实际这种范围查询而导致使用不了索引的场景经常出现，比如按照时间段抽取全量数据，每条SQL抽取一个月的；或者某张业务表历史数据的删除。
遇到此类操作时，应该在执行之前对SQL做explain分析，确定能走索引，再进行操作，否则不但可能导致操作缓慢，在做更新或者删除时，甚至会导致表所有记录锁住，十分危险。
5 计算操作
mysql> explain select * from t1 where b>=1 and b <=3000;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | idx_b | NULL | NULL | NULL | 10302 | 29.12 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
原因：对索引字段做运算将使用不了索引。
解决办法
将计算操作放在等号后面：
mysql> explain select * from t1 where b =1000 + 1;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | idx_b | idx_b | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
经验分享：
一般需要对条件字段做计算时，建议通过程序代码实现，而不是通过MySQL实现。如果在MySQL中计算的情况避免不了，那必须把计算放在等号后面。
