MYSQL优化方式-附实际操作校验

本文优化内容主要针对MySQL，有些会进行实际操作进行检验
参考
SQL优化最干货总结（2020最新版）
设计索引的原则是什么？怎么避免索引失效？

总结三点

• 最大化利用索引
• 尽可能避免全表扫描
• 减少无效数据的查询

本文所用例：mainten表
SELECT count(*) FROM or_mainten

已有索引

1、避免出现select *

使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。
建议：只查询业务需要的字段

2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

注意：只有单单order by 是无法使用到索引的

3、尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE '%陈%'

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE '陈%'

实际操作测试：


如果需求是要在前面使用模糊查询，

• 使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()，查询字符串出现的角标位置
• 使用FullText全文索引，用match against 检索
• 数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级
• 当表数据量较少（几千条儿那种），别整花里胡哨的，直接用like ‘%xx%’。

4、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

5、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

下图REAL_MAINTEN_PERSON和status都在索引INDEX_REAL_MAINTEN_PERSON中，但是在or情况下索引失效

解决：为or条件中的每个列都建立索引，但是和索引不能建立太多相违背

6、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

实际操作
选择int字段

添加索引

7、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

实际操作
添加索引

索引失效

索引正常应用

8、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'

-name以abc开头的id

应改为:

select id from t where name like 'abc%'

实际操作

9、最左前缀匹配原则

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
实际操作

10、选择区分度大的列作为索引

并不是所有索引对查询都有效，如一表中有字段性别sex，男女几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
原因是当索引列有大量数据重复时，MySQL还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
惯用的百分比界线是"30%"。（匹配的数据量超过一定限制的时候查询器会放弃使用索引（这也是索引失效的场景之一哦）。

11、索引并不是越多越好

索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

12、尽量使用数字型字段

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了

13、尽可能的使用 varchar 代替 char

因为首先变长字段存储空间小，可以节省存储空间，
其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

14、为频繁查询的字段建立索引

我们在建立索引的时候，要为那些经常作为查询条件的字段建立索引，这样能够提高整个表的查询速度。
但是查询条件一般不是一个字段，所以一般是建立的联合索引比较多。
另外查询条件中一般会有like这样的模糊查询，如果是模糊查询请最好遵守最左前缀查询原则。

15、避免为"大字段"建立索引

这个可以换句话说：就是尽量使用数据量小的字段作为索引。
举个例子来说，假设有两个这样的字段，一个是varchar(5),一个是varchar(200)，这种情况下优先选择为varchar(5)的字段建立索引，因为MySQL在维护索引的时候是会将字段值一起维护的，那这样必然会导致索引占用更多的空间，另外在排序的时候需要花费更多的时间去对比。
那假如就要为varchar(100)建立索引呢？那就取部分数据，例如 address 类型为varchar(200)，在建立索引的时候可以这么写：

CREATE INDEX tbl_address ON dual(address(20));

16、避免隐式转换

假设字段 age 类型为 int，那我们一般是这么查询的

SELECT * FROM student WHERE age=15

上面这种情况是能使用到索引的，但是如果你这么写

SELECT * FROM student WHERE age='15'

那这种情况是使用不到索引的，也就是age列情的索引是失效的。

---

MySQL中实际操作发现并不会失效的，未在其它数据库进行验证

1、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

在MySQL中实际操作发现并不会放弃使用索引

2、尽量避免使用in 和not in，会导致引擎走全表扫描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

实际操作