MySQL技术内幕第五章 - 查询优化

MySQL技术内幕第五章 - 查询优化

一、使用索引

用来加速查询的技术有很多，其中最为重要的就是索引。

1.索引的优点

（1）加快数据检索速度 （2）数据组织更加有序

2.索引的代价

（1）如果在表数据量很大的情况下，建多个索引，会占用更多的存储空间。
（2）大量地建索引会导致降低了索引列插入，更新，删除的速度。

3.挑选索引

（1）为用于搜索、排序或分组的列创建索引，而对于用作输出显示的列则不用创建索引。（即出现在WHERE、ON、GROUP BY、ORDER BY语句的字段加索引）
（2）认真考虑数据列基数，基数越高，使用索引效果越好。 （列基数是指该列非重复的个数）
（3）索引短小值，应尽量选用较小的数据类型。
（4）索引字符串值的前缀，如果字符串列索引的前缀能够确定唯一性，那就用前面的前缀来进行索引。（长度越短越好）
（5）利用最左前缀。（复合索引的最左前缀规则，即如果对a,b,c列进行复合索引，那么该索引可用于搜索下面几种组合：a,b,c; a,b; a;）
（6）不要建立过多的索引。
（7）让参与比较的索引类型保持匹配。（即索引列的数据类型要一致。）
（8）利用慢查询日志找出那些性能低劣的查询。

二、MySQL查询优化程序。

优化程序的主要目标：尽可能使用索引，并且使用最严格的的索引。

1.查询优化程序的工作原理

（1）分析表，可以使用ANALYZE TABLE语句告知服务器执行键值分析。 Analyze table tbl_name; – 修复健值
（2）使用EXPLAIN验证优化程序的操作。(EXPLAIN语句可用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句一起使用)
（3）在必要时给予优化程序提示或改写它。（USE INDEX语句，STRAIGHT_JOIN语句）
（4）比较相同类型的列。
（5）让索引列在比较表达式重单独出现。（索引列不要加表达式）
（6）不要在LIKE模式的开始位置使用通配符。（尽量不要使用LIKE ‘%abc%’）
（7）利用优化程序的长处。（将子查询替代为连接查询）
（8）测试查询的各种替代形式，并多次运行它们。
（9）避免过多使用自动类型转换。（避免隐式转换）

三、选择利于高效查询的数据类型

（1）多用数字运算，少用字符串运算。
（2）当较小类型够用时，就不用较大类型。
（3）把数据列声明成NOT NULL。（非必要）
（4）考虑使用ENUM列。
（5）整理表碎片，对于那些频繁修改的表，尤其是那些包含有可变长度数据列的表，往往会产生大量碎片。定期使用OPTIMIZE TABLE语句，可以消除或减少碎片化的MyISAM或InnoDB表里的空间浪费，并有助于防止性能降低。
（6）把数据压缩到BLOB或TEXT列。
（7）避免检索很大的BLOB或TEXT值，除非迫不得已。

四、高效加载数据

（1）LOAD DATA语句比INSERT的效率高。
（2）批量插入比单条插入效率高。
（3）表的索引越少，加载数据的速度越快。
（4）与长SQL语句相比，短语句的数据加载速度更快。

五、调度、锁定和并发

1.MySQL的调度策略如下：

（1）写入的优先级比读取高。
（2）表的写入操作一次只能进行一个，多个写入请求按其到达的先后顺序依次处理。
（3）可以同时处理多个对同一个表的读取操作。

2.存储引擎的锁定级别

InnoDB：行级锁；MyISAM、MERGE和MEMORY：表级锁。
一般而言，更精细的锁定级别会有更好的并发性。