18条MySQL优化技巧( 三 )


select id , product_name from orders where mobile = '12345678900' or user_id = 6;一般我们为了提升上面 SQL 的查询效率 , 会想着为字段 mobile 和 user_id 建立一个复合索引 index(mobile,user_id);
可是我们使用 explain 可以发现执行计划里面并没有提示到使用复合索引 , 所以 or 关键字无法命中 mobile + user_id 的组合索引 。
那么我们可以分别为两个字段建立普通索引 , 然后采用 union 关键字 , 如下所示:
(select id , product_name from orders where mobile = '12345678900') union(select id , product_name from orders where user_id = 6);此时 mobile 和 user_id 字段都有索引 , 查询才最高效 。
5、IN 优化
in 关键字适合主表大子表小 , exist 关键字适合主表小子表大 。由于查询优化器的不断升级 , 很多场景这两者性能差不多一样了 , 可以尝试改为 join 查询 。
假设我们现在有一条 SQL  , 要查询 VIP 用户的所有订单数据:
select id from orders where user_id in (select id from user where level = 'VIP');我们可以发现不会有任何关于索引的优化 , 所以我们可以采用 join查询 , 如下所示:
select o.id from orders o join user u on o.user_id = u.id and u.level = 'VIP';此时被驱动表应该是 user , 那么可以利用到 user 表的主键索引 , 即可以使用 BKA 算法来提升 join 查询的性能 。
6、Like 优化
like 用于模糊查询 , 但是如果是全模糊查询 , 将不能命中对应字段的索引 。
假设现在关于学生表有一条 SQL:
SELECT name,age,birthday FROM student WHERE name like '%苏%';使用 explain 可以发现执行计划提示查询未命中索引 。
因为本来需求就是查询姓张的所有同学信息 , 所以没必要使用全模糊查询 , 使用右模糊查询即可 。
换成下面的写法:
SELECT name,age,birthday FROM student WHERE name like '苏%';但是产品经理一定要前后模糊匹配呢?全文索引 FULLTEXT 可以尝试一下 , 但是 MySQL 的全文索引不支持中文查询的 。
所以说 Elasticsearch 才是终极武器!
三、数据表设计优化1、数据类型:应该选择更简单或者占用空间更小的类型 。

  • 整型选择:可以根据长度选择 tinyint、smallint、medium_int , 而不是直接使用 int 。
  • 字符串选择:能确定字符串长度的 , 尽量使用 char 类型 , 而不是变长的 varchar 类型 。
  • 浮点型选择:精度要求比较高的使用 decimal 而不是 double;也可以考虑使用 BIGINT 来保存 , 小数位保存可以使用乘以整百来解决 。
  • 日期选择:尽量使用 timestamp 而不是 datetime 。
2、避免空值:
  • NULL 值依然会占用空间 , 并且会使索引更新更加复杂 , 更新 NULL 时容易发生索引分裂的现象 。
  • 可以使用有意义的值来代替 NULL 值 , 例如 “none” 字符串等等 。
3、超长字符串:
  • 一般超长字符串 , varchar 难以存储 , 我们一般会使用 text 类型 。
  • 但是 text 类型的字段尽量避免放在主表中 , 而是抽出来在子表里 , 用业务主键关联 。




推荐阅读