MySQL技术内幕——查询优化
使用索引
索引的优点
大大提升查找效率。
MySQL使用索引的方式有以下几种。
- 索引可以用于加快对 WHERE 子句匹配的行进行搜索的速度,或者用于加快对,与另一个连接表里的行匹配的行,进行搜索的速度。
- 对于使用 MIN() 或 MAX() 函数的查询,MySQL可以在不用逐行检查的情况下,快速找到索引列里的最小值或最大值。
- 对于 ORDER BY 和 GROUP BY 子句,MySQL经常使用索引来高效地完成分类和分组操作。
- 有时,MySQL也会通过索引来读取查询所请求的所有信息。
索引的代价
首先,索引可以加快检索速度,但是它同时也降低了索引列插入、删除和更新值的速度。(即,索引降低了大部分与写入相关的操作的速度)
其次,索引也会占用磁盘空间,多个索引会占据更大的空间。与没有索引相比,使用索引会让你很快便达到表的大小极限。
- 对于 MyISAM 表,大量地对它进行索引,有可能导致索引文件比数据文件更快地到达其最大大小。
- 存储在 InnoDB 系统表空间里的所有 InnoDB 表,都共享同一个存储空间池,添加索引会使表空间里用于存储的空间减少的更快。
挑选索引
- (1)为用于搜索,排序或分组的列创建索引,,而对于用作输出显示的列则不用创建索引。
- (2)认真考虑数据列基数。
列的基数是指它所容纳的所有非重复值的个数。 - (3)索引短小值。应尽量选用较小的数据类型。
- (4)索引字符串值的前缀。
- (5)利用最左前缀。
- (6)不要建立过多的索引。
- (7)让参与比较的索引类型保持匹配。
- (8)利用慢查询日志找出那些性能低劣的查询。
MySQL查询优化程序
查询优化程序的工作原理
- (1)分析表。
ANALYSE TABLE 。会计算出表中各个索引列值数量信息,这些信息在对指定查询进行优化时,是可以决定优化的参考信息。 - (2)使用 EXPLAIN 验证优化程序的操作。
将输出执行策略信息;依据此策略进行评估,调整,以得到更加高效的方案。 - (3)在必要时给优化程序提示或改写它。
- (4)比较拥有相同数据类型的列。
尽量让参与运算的各列类型一致,以消除运算时的类型转换 - (5)让索引列在比较表达式中单独出现。
索引列本身构成表达式时才会启用列的索引。 - (6)不要在 LIKE 模式的开始位置使用通配符。
- (7)利用优化程序的长处。
- (8)测试查询的各种替代形式,并多次运行它们。
- (9)避免过多使用自动类型转换。
选择利于高效查询的数据类型
- 多用数字运算,少用字符串运算。
- 当较小类型够用时,就不用较大类型。
- 把数据列声明成 NOT NULL。
- 考虑使用 ENUM 列。
- 使用 PROCEDURE ANALYSE() 。
- 整理表碎片。
- 把数据压缩到 BLOB 或 TEXT 列。
- 使用合成索引。
- 避免检索很大的 BLOB 或 TEXT值,除非迫不得已。
- 把 BLOB 或 TEXT 列剥离出来形成一个单独的表。
高效加载数据
1.减少磁盘I/O次数的方案可提升I/O效率。
2.在加载数据前不设索引,加载完成后建立索引比加载前设置索引处理效率更高。
3.LOAD DATA优于INSERT。
4.通过将多个INSERT集成到一个事务,或在不支持事务下用LOCK / UNLOCK TABLES包围多个INSERT,可实现多个INSERT后整体刷新一次索引,比每次INSERT后刷新索引更有效率。
调度、锁定和并发
MySQL的调度策略:
- 写入的优先级比读取的高。
- 表的写入操作一次只能进行一个,多个写入请求按其到达的先后顺序依次处理。
- 可以同时处理多个对同一个表的读取操作。
- 为把数据写入表,客户端必须具有对表进行互斥访问的锁定。
- 为从表里读取数据,客户端必须锁定它,以防止其他客户端把数据写入表,或者在读取数据期间修改它。
一般情况下,更精细的锁定会有更好的并发性,因为如果各个客户端使用的是表的不同部分,那么可以让更多的客户端同时使用这个表。
实际的影响是:不同的存储引擎适合于不同的查询语句混合情况。
- 当有许多更新操作时,InnoDB 表可以提供更好的性能。
- MyISAM 表的检索速度极快。
学习参考资料:
《MySQL技术内幕》第5版