mysql之SQL优化实例

1.单表优化

很多时候我们一开始优化并不是一上来就对SQL进行优化。

很多时候我们需要检查数据库的设计是否合理，比如我们使用允许为空的字段修改为0或者其他特殊字符代替，这里我们把一些字段改为默认值表示null。

尽量避免null。因为如果查询中包含null的列，对mysql来讲更难优化，因为可为null的列使得索引，索引统计和值比较都更复杂。

null 的列会使用更多的存储空间，在mysql里也需要特殊处理。若null的列为索引时，每个索引记录需要一个额外的字节。在myisam里甚至还可能导致固定大小的索引

案例1  or 导致的索引失效

对数据库表进行调整后，接下来我们看SQL语句

explain select name,photo,mobile,yearbonus from customers1 where photo = 0 or mobile = 0 or yearbonus = 0 \G;

如果对上面的语句SQL进行explain操作，会发现改SQL并没用触发索引。原因是使用了 or 操作，对结果的合并，排序等操作需要费大量的CPU和内存资源，特别是当其中的某些索引选择性不高，需要返回合并大量数据时，查询成本更高。所以这种情况下还不如走全表扫描。

解决方法：使用union或union all 对其进行优化。

union和union all的区别是，union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

改写：

explain select name from customers1 where photo = 0 union all select name from customers1 where mobile = 0 union all select name from customers1 where yearbonus = 0;

注意事项：字符串字段索引值不加引号会失效

我们在选择索引的时候要考虑回表的问题，因为如果回表的数据多的话，查询效率还不如全表扫描。

案例2：函数对于索引的失效问题

使用函数索引：所谓的函数索引其实本质上解释还是btree索引，实现的方式主要是通过在数据表中新增一个字段用来存放函数字段列，然后对这个字段定义一个索引

eg:

1、添加一个虚拟的字段 alter table class add column scorce_generated int generated always as (ROUND(score)); 

2、添加索引 alter table class add key idx_scorce_generated(scorce_generated);

官方的例子，直接在创建数据库表的时候添加这个函数字段

CREATE TABLE triangle ( 
    sidea DOUBLE, 
    sideb DOUBLE, 
    sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)) );

INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

对于函数字段的存储格式：

virtual generated column (虚拟的列) ： 不存储改列值，只是当读取改行时，触发器对该列进行计算显示 （建议使用）

Stored Generated Column (存储生成的列) ： 存储改列值，即该列在插入或更新行时进行计算和存储