Mysql日常使用优化--高频率使用的五招

今天学习总结了一波Mysql常见的优化方法，以此做一个笔记

演示优化前的数据准备：

建表语句如下：
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
)

想办法插入10000条数据（程序或者写一个存储过程），保证字段【id】【a】【b】按照插入数字自然增长，时间字段【c】直接随机插入相同时间即可，例如【2021-08-09 00:00:00】

最后修改最后一条数据：

update t1 set c = '2021-08-08 00:00:00' where id=10000;

最终效果，部分截图如下：

1）where条件过滤字段有索引，但为何却不走索引？

我们执行sql：

explain select * from t1 where date(c) ='2021-08-08';

不走索引原因分析：

根据B+树结构可得知，索引树中存储的是列的实际值和主键值。如果拿 ‘2021-08-09’ 去匹配，将无法定位到索引树中的值。因此放弃走索引，而选择全表扫描。 

优化sql之后的sql：

explain select * from t1 where c>='2021-08-08 00:00:00' and c<='2021-08-08 23:59:59';

 总结：若是求某一天的数据或者某一个月，某一年，需要写成上述范围式的查询方式，使用explain做分析是否走索引

2）隐式转换带来的不走索引问题

继续看下面的案例：

explain select * from t1 where a=1000;

 因为对索引字段做了函数操作，优化器会放弃使用索引

优化：

explain select * from t1 where a='1000';

总结：对索引字段不要做隐式的函数操作 

 3）模糊查询导致的不走索引

explain select * from t1 where a like '%1111%';

 当然结合世界业务可以修改sql为：

explain select * from t1 where a like '1111%';

 

 总结：当然这样查询出来的数据可能会不准确，所以需要根据具体的业务场景去做改写

4）范围查询不走索引

例如，查询下面的查询：

explain select * from t1 where b>=1 and b <=2000;

 发现查询并没有走字段【b】的索引，因为mysql的优化器会根据检索比例、表大小、I/O块大小等进行评估是否使用索引，若是单次查询的数据量过大，优化器将不走索引

改写sql：

select * from t1 where b>=1 and b <=1000;
select * from t1 where b>=1001 and b <=2000;

再次分析：

explain select * from t1 where b>=1001 and b <=2000;

 总结：类似这种大范围查询的sql,需要使用explain关键字做分析看一看是否走索引，若是没有走索引是否是因为数据范围太大，数据量大导致，这个时候就需要考虑缩小查询范围，优化直至走索引为止。

5）查询条件进行计算操作不走索引的情况

explain select * from t1 where b-1 =1000;

优化改写：

explain select * from t1 where b =1000 + 1;

 总结：在MySQL中计算的情况避免不了，那必须把计算放在等号后面