《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 列剥离出来形成一个单独的表。

高效加载数据

１．减少磁盘I/O次数的方案可提升I/O效率。
２．在加载数据前不设索引，加载完成后建立索引比加载前设置索引处理效率更高。
３．LOAD DATA优于INSERT。
４．通过将多个INSERT集成到一个事务，或在不支持事务下用LOCK / UNLOCK TABLES包围多个INSERT，可实现多个INSERT后整体刷新一次索引，比每次INSERT后刷新索引更有效率。

调度、锁定和并发

MySQL的调度策略：

• 写入的优先级比读取的高。
• 表的写入操作一次只能进行一个，多个写入请求按其到达的先后顺序依次处理。
• 可以同时处理多个对同一个表的读取操作。
• 为把数据写入表，客户端必须具有对表进行互斥访问的锁定。
• 为从表里读取数据，客户端必须锁定它，以防止其他客户端把数据写入表，或者在读取数据期间修改它。

一般情况下，更精细的锁定会有更好的并发性，因为如果各个客户端使用的是表的不同部分，那么可以让更多的客户端同时使用这个表。
实际的影响是：不同的存储引擎适合于不同的查询语句混合情况。

• 当有许多更新操作时，InnoDB 表可以提供更好的性能。
• MyISAM 表的检索速度极快。

学习参考资料：

《MySQL技术内幕》第5版