MySQL数据库技术（20)[MySQL防范]

? ? 关系数据库的世界是一个表与调集、表与调集上的运算占统治地位的世界.数据库是一个表的调集,而表又是行和列的调集.在公布一条SELECT 查询从表中举行检索行时,得到另一个行和列的调集.这些都是一些抽象的概念,关于数据库系统用来操作表中数据的基本
表示没有多少参考代价.另一个抽象概念是,表上的运算都同时举行;查询是一种概念性的调集运算,并且调集论中没有时间概念.当然,实际世界是相当差别的.数据库管理系统实现了抽象的概念,但是在实际的硬件
? ? 范围内要遭到实际的物理约束.后果是,查询要花时间,有时要花很长的时间.而人类很简单不耐烦,不喜好等候,因此我们丢下了调集上的那些刹时的数学运算的抽象世界去追求加快查询的办法.幸运的是,有几种加快运算的技术,可对表举行索引使数据库服务器查找行更快.可考虑怎样充分操纵这些索引来编写查询.可编写影响服务器调度机制的查询,使来自多个客户机的查询合作得更好.我们考虑基本硬件怎样运行,以便想出怎样降服其物理约束对性能举行改进的办法.
? ? 这些恰是本章所要谈论的问题,其目标是优化数据库系统的性能,使其尽大概快地处理各种查询.MySQL 已经相当快了,但即便是最快的数据库,在人的计划下还能运行得更快.
? ? 4.1 利用索引
? ? 我们首先谈论索引,因为它是加快查询的最重要的工具.还有其他加快查询的技术,但是最有效的莫过于恰本地利用索引了.在MySQL 的邮件清单上,人们普通询问关于使查询更快的问题.在大量的案例中,都是因为表上没有索引,普通只要加上索引便可以当即办理问题.但这样也并非老是有效,因为优化并非老是那样简单.但是,假如不利用索引,在很多情形下,用其他手段改进性能只会是浪费时间.应当首先考虑利用索引获得最大的性能改进,然后再追求其他大概有帮忙的技术.
? ? 本节介绍索引是什么、它怎样改进查询性能、索引在什么情形下大概会降低性能,以及怎样为表挑选索引.下一节,我们将谈论MySQL 的查询优化程序.除了知道怎样成立索引外,理解一些优化程序的知识也是有好处的,因为这样可以更好地操纵所成立的索引.某些编写查询的办法实际上会阻碍索引的效果,应当避免这种情形呈现.（固然并非总会这样.有时也会但愿忽视优化程序的作用.我们也将介绍这些情形.）
? ? 4.1.1 索引的好处
? ? 让我们从一个无索引的表着手来观察索引是怎样起作用的.无索引的表就是一个无序的行集.比方,图4 - 1给出了我们在第1章"MySQL 与SQL 介绍" 中首先看到的ad 表.这个表上没有索引,因此假如我们查找某个特定公司的行时,必须查看表中的每一行,看它能否与所需的值匹配.这是一个全表扫描,很慢,假如表中只有少数几个记录与搜索条件相匹配,则其效率是相当低的.

? ? 图4 - 2给出了相同的表,但在表的company_num 列上增添了一个索引.此索引包含表中每行的一项,但此索引是在company_num 上排序的.目前,不需求逐行搜索全表查找匹配的条款,而是可以操纵索引举行查找.假定我们要查找公司1 3的全部行,那么可以扫描索引,后果得出3行.然后到达公司1 4的行,这是一个比我们正在查找的要大的号码.索引值是排序的,因此在读到包含1 4的记录时,我们知道不会再有匹配的记录,可以退出了.假如查找一个值,它在索引表中某此中间点从前不会呈现,那么也有找到其第一个匹配索引项的定位算法,而不用举行表的次序扫描（如二分查找法）.这样,可以快速定位到第一个匹配的值,以节俭大量搜索时间.数据库操纵了各种各样的快速定位索引值的技术,这些技术是什么并不重要,重要的是它们工作正常,索引技术是个好东西.
? ? 有人会问,为什么不只对数据文件举行排序,免却索引文件?这样不也在搜索时产生相同的效果吗?问得好,假如只有单个索引时,
是这样的.不过有大概会用到第二个索引,但同时以两种差别的办法对同一个数据文件举行排序是不大概的.（如,想要一个顾客名的索
引,同时又要一个顾客ID 号或电话号码的索引.）将索引文件作为一个与数据文件独立的实体就办理了这个问题,并且答应成立多个索
引.此外,索引中的行普通要比数据文件中的行短.在插入或删除值时,为保持排序次序而移动较短的索引值与移动较长的数据行相比更
为简单.

? ? 这个例子与MySQL 索引表的办法符合.表的数据行保存在数据文件中,而索引值保存在索引文件中.一个表上可有不止一个索引;假如确切有不止一个索引,它们都保存在同一个索引文件中.索引文件中的每个索引由排过序的用来快速拜候数据文件的键记录数组构成.
前面的谈论描写了单表查询中索引的好处,此中利用索引消除了全表扫描,极大地加快了搜索的速度.在履行触及多个表的衔接查询时,索引乃至会更有代价.在单个表的查询中,每列需求查看的值的数目就是表中行的数目.而在多个表的查询中,大概的组合数目极大,因为这个数目为各表中行数之积.
? 　 假定有三个未索引的表t 1、t 2、t 3,辨别只包含列c 1、c 2、c 3,每个表辨别由含有数值1到1000 的1000 行构成.查找对应值相等的表行组合的查询以下所示:
? ? SELECT c1,c2,c3
? ? FROM t1,t2,t3
? ? WHERE c1=c2 AND c1=c3
? ? 此查询的后果应当为1000 行,每个组合包含3 个相等的值.假如我们在无索引的情形下处理此查询,则不大概知道哪些行包含那些值.因此,必须探求出全部组合以便得出与WHERE 子句相配的那些组合.大概的组合数目为1 0 0 0×1 0 0 0×1 0 0 0（十亿）,比匹配数目多一百万倍.很多工作都浪费了,并且这个查询将会非常慢,即便在如像MySQL 这样快的数据库中履行也会很慢.而这还是每个表中只有1000 行的情形.假如每个表中有一百万行时,将会怎样?很明显,这样将会产生性能极其低下的后果.假如对每个表举行索引,就可以极大地加快查询进程,因为操纵索引的查询处理以下:
? ? 1) 以下从表t1 中挑选第一行,查看此行所包含的值.
? ? 2) 利用表t2 上的索引,直接跳到t2 中与来自t1 的值匹配的行.近似,操纵表t3 上的索引,直接跳到t3 中与来自t1 的值匹配的行.
? ? 3) 进到表t1 的下一行并反复前面的历程直到t1 中全部的行已经查过.在此情形下,我们仍旧对表t1 履行了一个完好扫描,但可以在表t2 和t3 上举行索引查找直接取出这些表中的行.从原理上说,这时的查询比未用索引时要快一百万倍.如上所述,MySQL 操纵索引加快了WHERE 子句中与条件相配的行的搜索,大概说在履行衔接时加快了与其他表中的行匹配的行的搜索.它也操纵索引来改良其他操作的性能:
? ? ■ 在利用MIN( ) 和MAX( ) 函数时,可以快速找到索引列的最小或最大值.
? ? ■ MySQL 常常可以操纵索引来完成ORDER BY 子句的排序操作.
? ? ■ 有时,MySQL 可避免对整个数据文件的读取.假定从一个索引数值列中挑选值,并且不挑选表中其他列.这时,通过对索引值的读取,就已经得到了读取数据文件所要得到的值.没有对相同的值举行两次读取的必要,因此,乃至无需触及数据文件.
? ? 4.1.2 索引的弊端
? ? 普通情形下,假如MySQL 可以知道怎样用索引来更快地处理查询,它就会这样做.这表示,在大大都情形下,假如您不对表举行索引,则侵害的是您自己的好处.可以看出,作者描绘了索引的诸多好处.但有不利之处吗?是的,有.实际上,这些缺陷被长处所掩盖了,
但应当对它们有所理解.
? ? 首先,索引文件要占磁盘空间.假若有大量的索引,索引文件大概会比数据文件更快地到达最大的文件尺寸.其次,索引文件加快了检索,但增添了插入和删除,以及更新索引列中的值的时间（即,降低了大大都触及写入的操作的时间）,因为写操作不但触及数据行,并且还常常触及索引.一个表拥有的索引越多,则写操作的平均性能下降就越大.在4 . 4节"有效地装载数据"中,我们将更为具体地介绍这些性能问题,并谈论怎样办理.
? ? 4.1.3 挑选索引
? ? 成立索引的语法已经在3 . 4 . 3节"成立和删除索引"中举行了介绍.这里,我们假定您已经阅读过该节.但是知道语法并不能帮忙肯定表怎样举行索引.要决意表怎样举行索引需求考虑表的利用方法.本节介绍一些关于怎样肯定和挑选索引列的原则:
? ? ■ 搜索的索引列,不一定是所要挑选的列.换句话说,最合适索引的列是呈目前WHERE 子句中的列,或衔接子句中指定的列,而不是呈目前SELECT 关键字后的挑选列表中的列:


? ? 当然,所挑选的列和用于WHERE 子句的列也大概是相同的.关键是,列呈目前挑选列表中不是该列应当索引的标志.呈目前衔接子句中的列或呈目前形如col1 = col2 的表达式中的列是很合适索引的列.查询中的col_b 和col_c 就是这样的例子.假如MySQL 能操纵衔接列来优化一个查询,表示它通过消除全表扫描相当可观地削减了表行的组合.
? ? ■ 利用惟一索引.考虑某列中值的分布.关于惟一值的列,索引的效果最好,而具有多个反复值的列,其索引效果最差.比方,存放年纪的列具有差别值,很简单辨别各行.而用来记录性别的列,只含有" M"和"F",则对此摆列行索引没有多大用处（不管搜索哪个值,城市得出大约一半的行）.
? ? ■ 利用短索引.假如对串摆列行索引,应当指定一个前缀长度,只要有大概就应当这样做.比方,假若有一个CHAR(200) 列,假如在前10 个或20 个字符内,大都值是惟一的,那么就不要对整个摆列行索引.对前10 个或20 个字符举行索引可以节俭大量索引空间,也大概会使查询更快.较小的索引触及的磁盘I/O 较少,较短的值对比起来更快.更为重要的是,关于较短的键值,索引高速缓存中的块能包容更多的键值,因此,MySQL 也可以在内存中包容更多的值.这增添了找到行而不用读取索引中较多块的大概性.（当然,应当操纵一些常识.如仅用列值的第一个字符举行索引是不大概有多大好处的,因为这个索引中不会有很多差别的值.）
? ? ■ 操纵最左前缀.在成立一个n 列的索引时,实际是成立了MySQL 可操纵的n 个索引.多列索引可起几个索引的作用,因为可操纵索引中最左边的列集来匹配行.这样的列集称为最左前缀.（这与索引一个列的前缀差别,索引一个列的前缀是操纵该的前n 个字符作为索引值.）
? ? 假定一个表在辨别名为s t a t e、city 和zip 的三个列上有一个索引.索引中的行是按state/city/zip 的次序存放的,因此,索引中的行也会自动按state/city 的次序和state 的次序存放.这表示,即便在查询中只指定state 值或只指定state 和city 的值,M y S Q L也可以操纵索引.因此,此索引可用来搜索下列的列组合:
? ? state,city,zip
? ? state,city
? ? sate
? ? MySQL 不能利用不触及左前缀的搜索.比方,假如按city 或zip 举行搜索,则不能利用该索引.假如要搜索某个州以及某个zip 代码（索引中的列1和列3）,则此索引不能用于呼应值的组合.但是,可操纵索引来探求与该州符合的行,以削减搜索范围.
? ? ■ 不要过度索引.不要认为索引"越多越好",什么东西都用索引是错的.每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能,这一点我们前面已经介绍过.在改正表的内容时,索引必须举行更新,有时大概需求重构,因此,索引越多,所花的时间越长.假若有一个索引很少操纵或从不利用,那么会不必要地减缓表的改正速度.此外,MySQL 在生成一个履行筹划时,要考虑各个索引,这也要费时间.成立多余的索引给查询优化带来了更多的工作.索引太多,也大概会使MySQL 挑选不到所要利用的最好索引.只保持所需的索引有利于查询优化.假如想给已索引的表增添索引,应当考虑所要增添的索引能否是现有多列索引的最左索引.假如是,则就不要吃力去增添这个索引了,因为已经有了.
? ? ■ 考虑在列上举行的对比范例.索引可用于" <"、" < = "、" = "、" > ="、" > "和BETWEEN 运算.在情势具有一个直接量前缀时,索引也用于LIKE 运算.假如只将某个列用于其他范例的运算时（如STRCMP( )）,对其举行索引没有代价.

　　以上是“MySQL数据库技术（20)[MySQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：