insert 优化
如果你在某一时刻有大量的 insert 操作, 一条一条插入是非常耗时的. insert 语句本身支持一次插入很多条记录, 插入记录数上限受 sql 语句长度限制, 一般一次插个几千条是没问题的. 在我的 《如何手动实现 Try Insert 和 Insert Or Update》 一文中对于各种情况都有具体的例子, 这里就不赘述了.
explain 语句结果分析
SQL 本身是一种对机器来说抽象级别很高的语言, 我们通过 SQL 告诉 DBMS 我们需要什么, 而没有告诉它具体要怎么做. DBMS 会猜测性地以最优的方法去完成我们给的任务, 但是它往往做得不太好, 毕竟不同业务最优做法各不相同, 目前我们还没有办法让机器完全理解我们的业务. 所以我们需要辅助机器, 帮助它找到最好的查询逻辑. 通常的做法是添加合适的索引, 让所有的查询都走索引. 在 MySQL 中, 在任何一个 select 语句前加上 explain, 就可以知道 MySQL 对这条查询的理解和实际执行逻辑.
下面来分析 explain 语句返回的结果. explain 会展示查询涉及到的每张表分析结果, 里面有很多参数, 我们一般只需要关注以下几个参数:
type
type 描述表是怎么 join 的, 按从最好到最坏一共有以下几个值:
值 | 解释 |
---|---|
system | 表只有一行,是一种特殊的 const type |
const | 表里只有一行匹配的记录,join 时可以认为是常量 |
eq_ref | 使用的索引为 primary key 或 unique not null index |
ref | join 只使用最左前缀匹配原则的普通索引 |
fulltext | 使用全文检索索引 |
ref_or_null | 与 ref 差不多,主要是多了 NULL 值的查询 |
index_merge | 使用了 MySQL 的索引合并优化 |
unique_subquery | 类似 eq_ref,主要用于包含 IN 子查询的查询 |
range | 走索引的范围查询 |
index | 索引树被整个扫了,速度比扫表好一点 |
ALL | 整个表被扫,非常糟糕的情况,一般要避免 |
一般做 SQL 优化, 通常出现 index 和 ALL 都是需要优化的.
Extra
MySQL 查询的附件信息, 有时候代表着查询的额外代价, 出现 Using filesort,Using temperary 都表示查询速度不行.
Using filesort 表示 order by 子句不走索引, 使用文件排序, 需要对 order by 进行优化.
Using temperary 表示查询过程中创建了临时表, 通常发生在包含 group by 和 order by 的查询中.
rows 和 filtered
rows 表示 MySQL 预估的查询需要的行数, filtered 表示根据条件过滤之后的行所占的百分比. 值为 100 表示没有行被过滤掉. 所以 rows*filtered 查询需要的总的行数. 这个值自然是越小越好.
查询优化实践
查询优化的策略就是加索引, primary key 和 unique key 在根据具体业务定, 我们做优化, 一般都是添加普通索引. 普通索引分为两种, 单个字段的索引和多个字段的联合索引. 联合索引的应用场景相对窄一点, 如果你要查的数据可以被联合索引全部囊括, 直接从索引拿数据, 可以考虑使用联合索引. 读多写少重复值少散列分布的字段最适合建索引. 你可以把你的程序使用到的所有 SQL 都列出来, 一条一条 explain, 没有走索引的, 就酌情给某个或某几个字段 (join 里的字段, where 里的字段都是重点考虑对象) 加上索引, 直到所有的查询走索引为止. 这么做以后, 你的查询 type 正常都可以到达比较好的情况, 但是对于包含 order by 子句的查询, 可能你的 Extra 信息就不太理想了. Using filesort 和 Using temperary 有时候阴魂不散, 很难搞. 这时候最佳的策略就是变着花样选择排序的字段. 比如你的表有一个自增主键, 你可以考虑用它作为插入时间来做排序. MySQL 本身在这方面的优化非常糟糕, 需要耐心地多尝试.
- Reference
- MySQL explain
MySQL ORDER BY 优化
来源: https://www.cnblogs.com/ripley/p/12080652.html