<b>MySQL查询优化技术系列讲座之利用索引</b>[MySQL防范]

图1:无索引的ad表

图2:索引后的ad表

　　这个查询的后果应当是1000行,每个数据行包含三个相等的值.假如在没有索引的情形下处理这个查询,那么假如我们不对这些表举行全部地扫描,我们是没有办法知道哪些数据行含有哪些值的.因此你必须尝试全部的组合来查找符合WHERE条件的记录.大概的组合的数目是1000 x 1000 x 1000（10亿!）,它是匹配记录的数目的一百万倍.这就浪费了大量的工作.这个例子显示,假如没有利用索引,随着表的记录不断增长,处理这些表的联合所耗费的时间增长得更快,招致性能很差.我们可以通过索引这些数据表来明显地提高速度,因为索引让查询采取以下所示的方法来处理:

　　1．挑选表t1中的第一行并查看该数据行的值.

　　2．利用表t2上的索引,直接定位到与t1的值匹配的数据行.近似地,利用表t3上的索引,直接定位到与表t2的值匹配的数据行.

　　3．处理表t1的下一行并反复前面的历程.履行这样的操作直到t1中的全部数据行都被查抄过.

　　在这种情形下,我们仍旧对表t1履行了完好的扫描,但是我们可以在t2和t3上履行索引查找,从这些表中直接地获得数据行.理论上采取这种方法运行上面的查询会快一百万倍.当然这个例子是为了得出结论来人为成立的.但是,它办理的问题倒是实际的,给没有索引的表增添索引普通会得到惊人的性能提高.

　　MySQL有几种利用索引的方法:

　　· 如上所述,索引被用于提高WHERE条件的数据行匹配大概履行联合操作时匹配别的表的数据行的搜索速度.

　　· 关于利用了MIN()或MAX()函数的查询,索引数据列中最小或最大值可以很快地找到,不用查抄每个数据行.

　　· MySQL操纵索引来快速地履行ORDER BY和GROUP BY语句的排序和分组操作.

　　· 有时刻MySQL会操纵索引来读取查询得到的全部信息.假定你挑选了MyISAM表中的被索引的数值列,那么就不需求从该数据表中挑选别的的数据列.在这种情形下,MySQL从索引文件中读取索引值,它所得到的值与读取数据文件得到的值是相同的.没有必要两次读取相同的值,因此没有必要考虑数据文件.

[1] [2] 下一页  

索引的代价

普通来说,假如MySQL可以找到办法,操纵索引来更快地处理查询,它就会这样做.这意味着,关于大大都情形,假如你没有对表举行索引,就会使性能遭到侵害.这就是我所描绘的索引长处的美景.但是它有缺陷吗?有的,它在时间和空间上都有开销.在实践中,索引的长处的代价普通会超越这些缺陷,但是你也应当知道到底有一些什么缺陷.

首先,索引加快了检索的速度,但是减慢了插入和删除的速度,同时还减慢了更新被索引的数据列中的值的速度.也就是说,索引减慢了大大都触及写操作的速度.发生这种现象的缘由在于写入一条记录的时刻不但需求写入数据行,还需求改变全部的索引.数据表带有的索引越多,需求做出的改正就越多,平均性能的降低程度也就越大.在本文的"高效率载入数据"部份中,我们将更具体地理解这些现象并找出处理办法.

其次,索引会耗费磁盘空间,多个索引呼应地耗费更多的磁盘空间.这大概招致更快地到达数据表的大小限制:

· 关于MyISAM表,频繁地索引大概惹起索引文件比数据文件更快地到达最大限制.

· 关于BDB表,它把数据和索引值一同存储在同一个文件中,增添索引惹起这种表更快地到达最大文件限制.

· 在InnoDB的同享表空间中分配的全部表都竞争利用相同的大众空间池,因此增添索引会更快地耗尽表空间中的存储.但是,与MyISAM和BDB表利用的文件差别,InnoDB同享表空间并不受操作系统的文件大小限制,因为我们可以把它配置成利用多个文件.只要有额外的磁盘空间,你便可以通过增添新组件来扩大表空间.

利用单独表空间的InnoDB表与BDB表遭到的约束是一样的,因为它的数据和索引值都存储在单个文件中.

这些要素的实际含义是:假如你不需求利用特别的索引帮忙查询履行得更快,就不要成立索引.

挑选索引

假定你已经知道了成立索引的语法,但是语法不会奉告你数据表应当若何索引.这要求我们考虑数据表的利用方法.这一部份指导你若何辨认出用于索引的备选数据列,以及若何最好地成立索引:

用于搜索、排序和分组的索引数据列并不但仅是用于输出显示的.换句话说,用于索引的最好的备选数据列是那些呈目前WHERE子句、join子句、ORDER BY或GROUP BY子句中的列.仅仅呈目前SELECT关键字背面的输出数据列列表中的数据列不是很好的备选列:

SELECT
col_a <- 不是备选列
FROM
tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c <- 备选列
WHERE
col_d = expr; <- 备选列

当然,显示的数据列与WHERE子句中利用的数据列也大概相同.我们的概念是输出列表中的数据列本质上不是用于索引的很好的备选列.

Join子句或WHERE子句中近似col1 = col2情势的表达式中的数据列都是分外好的索引备选列.前面显示的查询中的col_b和col_c就是这样的例子.假如MySQL可以操纵联合列来优化查询,它一定会通过削减整表扫描来大幅度削减潜在的表－行组合.

考虑数据列的基数（cardinality）.基数是数据列所包含的差别值的数目.比方,某个数据列包含值1、3、7、4、7、3,那么它的基数就是4.索引的基数相关于数据表行数较高（也就是说,列中包含很多差别的值,反复的值很少）的时刻,它的工作效果最好.假如某数据列含有很多差别的年纪,索引会很快地辨别数据行.假如某个数据列用于记录性别（只有"M"和"F"两种值）,那么索引的用处就不大.假如值呈现的概率几近相等,那么无论搜索哪个值都大概得到一半的数据行.在这些情形下,最好根本不要利用索引,因为查询优化器发现某个值呈目前表的数据行中的百分比很高的时刻,它普通会忽视索引,举行全表扫描.惯用的百分比界限是"30%".目前查询优化器越发复杂,把别的一些因素也考虑进去了,因此这个百分比并非MySQL决意挑选利用扫描还是索引的唯一因素.

索引较短的值.尽大概地利用较小的数据范例.比方,假如MEDIUMINT充足保存你需求存储的值,就不要利用BIGINT数据列.假如你的值不会擅长25个字符,就不要利用CHAR(100).较小的值通过几个方面改进了索引的处理速度:

· 较短的值可以更快地举行对比,因此索引的查找速度更快了.

· 较小的值招致较小的索引,需求更少的磁盘I/O.

· 利用较短的键值的时刻,键缓存中的索引块（block）可以保存更多的键值.MySQL可以在内存中一次保持更多的键,在不需求从磁盘读取额外的索引块的情形下,提高键值定位的大概性.

关于InnoDB和BDB等利用聚簇索引（clustered index）的存储引擎来说,保持主键（primary key）短小的上风更突出.聚簇索引中数据行和主键值存储在一同（聚簇在一同）.别的的索引都是次级索引;它们存储主键值和次级索引值.次级索引服从主键值,它们被用于定位数据行.这表示主键值都被复制到每个次级索引中,因此假如主键值很长,每个次级索引就需求更多的额外空间.

索引字符串值的前缀（prefixe）.假如你需求索引一个字符串数据列,那么最好在任何得当的情形下都应当指定前缀长度.比方,假若有CHAR(200)数据列,假如前面10个或20个字符都差别,就不要索引整个数据列.索引前面10个或20个字符会节俭大量的空间,并且大概使你的查询速度更快.通过索引较短的值,你可以得到那些与对比速度和磁盘I/O节俭相关的好处.当然你也需求操纵常识.仅仅索引某个数据列的第一个字符串大概用处不大,因为假如这样操作,那么在索引中不会有太多的唯一值.

你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT数据列的前缀.

利用最左（leftmost）前缀.成立多列复合索引的时刻,你实际上成立了MySQL可以利用的多个索引.复合索引可以作为多个索引利用,因为索引中最左边的列调集都可以用于匹配数据行.这种列调集被称为"最左前缀"（它与索引某个列的前缀差别,那种索引把某个列的前面几个字符作为索引值）.

假定你在表的state、city和zip数据列上成立了复合索引.索引中的数据行按照state/city/zip次序布列,因此它们也会自动地按照state/city和state次序布列.这意味着,即便你在查询中只指定了state值,大概指定state和city值,MySQL也可以利用这个索引.因此,这个索引可以被用于搜索以下所示的数据列组合:

state, city, zip
state, city
state

MySQL不能操纵这个索引来搜索没有包含在最左前缀的内容.比方,假如你按照city或zip来搜索,就不会利用到这个索引.假如你搜索给定的state和具体的ZIP代码（索引的1和3列）,该索引也是不能用于这种组合值的,固然MySQL可以操纵索引来查找匹配的state从而缩小搜索的范围.

不要过量地索引.不要认为"索引越多,性能越高",不要对每个数据列都举行索引.我们在前面提到过,每个额外的索引城市耗费更多的磁盘空间,并降低写操作的性能.当你改正表的内容的时刻,索引就必须被更新,乃至大概重新整理.假如你的索引很少利用或永不利用,你就没有必要减小表的改正操作的速度.此外,为检索操作生成履行筹划的时刻,MySQL会考虑索引.成立额外的索引会给查询优化器增添更多的工作量.假如索引太多,有大概（未必）呈现MySQL挑选最优索引失利的情形.保护自己必须的索引可以帮忙查询优化器来避免这类错误.

假如你考虑给已经索引过的表增添索引,那么就要考虑你将增添的索引能否是已有的多列索引的最左前缀.假如是这样的,不用增添索引,因为已经有了（比方,假如你在state、city和zip上成立了索引,那么没有必要再增添state的索引）.

让索引范例与你所履行的对比的范例相匹配.在你成立索引的时刻,大大都存储引擎会挑选它们将利用的索引实现.比方,InnoDB普通利用B树索引.MySQL也利用B树索引,它只在三维数据范例上利用R树索引.但是,MEMORY存储引擎支持散列索引和B树索引,并答应你挑选利用哪类索引.为了挑选索引范例,需求考虑在索引数据列上将履行的对比操作范例:

· 关于散列（hash）索引,会在每个数据列值上利用散列函数.生成的后果散列值存储在索引中,并用于履行查询.散列函数实现的算法近似于为差别的输入值生成差别的散列值.利用散列值的好处是散列值比原始值的对比效率更高.散列索引用于履行=或<=>操作等切确匹配的时刻速度非常快.但是关于查询一个值的范围效果就非常差了:

id < 30
weight BETWEEN 100 AND 150

· B树索引可以用于高效率地履行切确的大概基于范围（利用操作<、<=、=、>=、>、<>、!=和BETWEEN）的对比.B树索引也可以用于LIKE情势匹配,前提是该情势以文字串而不是通配符开首.

假如你利用的MEMORY数据表只举行切确值查询,散列索引是很好的挑选.这是MEMORY表利用的默许的索引范例,因此你不需求特地指定.假如你但愿在MEMORY表上履行基于范围的对比,应当利用B树索引.为了指定这种索引范例,需求给索引定义增添USING BTREE.比方:

CREATE TABLE lookup
(
id INT NOT NULL,
name CHAR(20),
PRIMARY KEY USING BTREE (id)
) ENGINE = MEMORY;

假如你但愿履行的语句的范例答应,单个MEMORY表可以同时拥有散列索引和B树索引,即便在同一个数据列上.

有些范例的对比不能利用索引.假如你只是通过把值传送到函数（比方STRCMP()）中来履行对比操作,那么对它举行索引就没有代价.服务器必须计算出每个数据行的函数值,它会解除数据列上索引的利用.

利用慢查询（slow-query）日记来辨认履行情形较差的查询.这个日记可以帮忙你找出从索引中受益的查询.你可以直接查看日记（它是文本文件）,大概利用mysqldumpslow工具来统计它的内容.假如某个给定的查询多次呈目前"慢查询"日记中,这就是一个线索,某个查询大概没有优化编写.你可以重新编写它,使它运行得更快.你要记着,在评价"慢查询"日记的时刻,"慢"是按照现及时间测定的,在负载较大的服务器上"慢查询"日记中呈现的查询会多一些.

上一页  [1] [2]