MySQL查询性能优化.docxVIP

MySQL查询功能优化 　　索引是存储引擎中用于快速找到记录的一种数据结构。索引有多种分类方式，依据存储方式可以分为：聚簇索引和非聚簇索引；依据数据的独一性可以分为：独一索引和非独一索引；依据列个数可以分为：单列索引和多列索引等。索引也有多品种型：B-Tree索引、Hash索引、空间数据索引（R-Tree）、全文索引等。 B-Tree索引 　　在利用B-Tree索引进行查询的过程中，有几点留意事项，我们以表A进行说明。其中表A的定义如下： 　　create table A(id int auto_increment primary key, name varchar(10), age tinyint, sex enum(男,女), birth datatime, key(name,age,sex));?id为主键，并在name,age,sex列上建立了索引。 全值婚配：指和索引中的全部列进行婚配，例如查找name=Jone and age=13 and sex=男的人； 婚配最左前缀：指用索引的第一列name，如where name=Jone，该查询只使用了索引的第一列 婚配列前缀：婚配索引列值的开头，如where name like J%，查找名字以J开头的人； 婚配范围值：例如查找年龄在10-30之间的Jone，where name=Jone and age between 10 and 30; 只访问索引的查询：假如在select中选择的字段都是索引中的字段，那么就不需要访问数据行，从而提高查询速度。 假如不是依据索引的最左列进行查找，则无法使用索引，如当仅查找表A中年龄为15岁的人时则无法使用索引； 不能跳过索引中的列，如查找表A中名字为Jone且为男性的人，则索引只能使用name列，无法使用sex列； 查询中索引的某列是范围查询，则该列后的查询条件将不能使用索引。 Hash索引与B-Tree的区分： Hash索引指包含哈希值（依据key中的列计算）和行指针，而B-Tree存储的是列值。所以Hash不能使用索引来避开读取数据行； Hash索引数据不是依据索引值挨次存储的，所以无法用于排序； Hash索引不支持部分索引列婚配查找，由于Hash值是依据索引中的全部列计算出来的； Hash索引只支持等值比较查询，包括=、in()、=。不支持范围查询。 索引的优点 索引不只仅可以让服务器快速定位到表的指定位置，而且还有以下优点： B-Tree索引依据列的挨次存储数据，所以可以用来做Order by和group by操作，避开排序和临时表 B-Tree索引中存储索引列的值，所以当select的值在索引中时，可以避开访问数据行 索引可以有效削减服务器扫描的数据量。 高功能的索引策略 　　正确地创建和使用索引是实现高功能查询的基础。前面已经引见了各品种型的索引以及对应的优缺点。高效地选择和使用索引有很多种方式，其中有些是针对特殊案例的优化，有些则是针对特定行为的优化。 独立的列：指索引不能是表达式的一部分，也不能是函数的参数。如：select * from A where id+1=5; 则无法使用主键索引。 前缀索引和索引选择性：有时需要索引很长的字符串，索引会占用很大的空间，通常可以索引开头的部分字符来节省索引空间，提高索引效率，但也会降低索引的选择性。索引的选择性=不反复索引值/数据表的记录总数。索引的选择性越高查询效率越高。 多列索引：首先需要说明在多列上创建索引不等同于给这些列的每一列单独建立索引。当执行查询的时候，MySQL只能使用一个索引。假如你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。即便是限制最严格的单列索引，它的限制力量也确定远远低于这三个列上的多列索引。比如我们想查询表A中id为3或者名字首字母为A的人，sql语句的两种写法对比，其中其次种写法比第一种削减对表的扫描次数： 多列索引中索引列的挨次也格外重要，在设计索引的挨次时也需要考虑如何更好地满足排序和分组的需要（B-Tree）。在一个多列的B-Tree索引中，索引列的挨次意味着索引首先依据最左列进行排序，其次是其次列等等。确定索引列的挨次有一个阅历法则：将选择性最高的列放到索引最前列。当然假如需要考虑对表的排序的情况就需要另当考虑了。 聚簇索引：不是一种单独的索引类型，而是一种数据存储方式，具体的细节依靠于其实现方式，InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行，一个表只能有一个聚簇索引。聚簇索引的优（1-3）缺（4-7）点如下： 可以把相关数据保存在一起。例照实现电子邮箱时，可以依据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能够猎取某个用户的全部邮件。假如没有聚簇索引，则每封邮件都可能导致一次磁盘I