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 。
- NULL 值依然会占用空间 , 并且会使索引更新更加复杂 , 更新 NULL 时容易发生索引分裂的现象 。
- 可以使用有意义的值来代替 NULL 值 , 例如 “none” 字符串等等 。
- 一般超长字符串 , varchar 难以存储 , 我们一般会使用 text 类型 。
- 但是 text 类型的字段尽量避免放在主表中 , 而是抽出来在子表里 , 用业务主键关联 。
推荐阅读
- MySQL库表设计小技巧
- MySQL数据库教程-环境与集成开发工具
- 淘宝店铺优化主要从哪些方面进行 店铺优化的方法有哪些
- 淘宝店铺标题优化怎么做 淘宝标题优化的好处
- 淘宝直通车点击率低怎么优化 淘宝直通车如何设置地域推广
- 传统网站优化怎么做?具体有哪些步骤?
- win7系统如何设置开机启动项,优化开机速度?
- Mysql这种用法千万要小心,一不注意就锁表
- 淘宝直通车怎么看同行点击率 淘宝直通车点击率低怎么优化
- 淘宝标题优化怎么做 淘宝商品标题优化方法有哪些