mysql开发和 与实践 第13章mysql性能优化.pdf

本章要求:第13章M ySQL性能优化 了解使用索弓|优化查询 了解在M ySQL中分析查询效率 掌握在M ySQL中应用高速缓存提高查询性能 掌握如何在多表查询中提高查询性能 掌握在M ySQL中使用临时表提高优化查询效率 掌握通过控制数据表的设计和处理，实现优化查询性能.第13章 M ySQL性能优化主要内容1.优化概述2.优化查询3.优化数据库结构4.查询高速缓存5.优化多表查询6.优化表设计7.综合实例查看M ySQL服务器的连接和查询次数13.1优化概述优化M ySQL数据库是数据库管理员的必备技能。通过不同的优化方式达到提高M ySQL数据库性能 的目的。本节将为读者介绍优化的基本知识。M ySQL数据库的用户和数据非常少的时候，很难判断一个M ySQL数据库的性能的好坏。只有当长 时间运行，并且有大量用户进行频繁操作时，M ySQL数据库的性能才能体现出来。例如，一个每天有 几万用户同时在线的大型网站的数据库性能的优劣就很明显。这么多用户在同时连接M ySQL数据库，并且进行查询、插入和更新的操作。如果M ySQL数据库的性能很差，很可能无法承受如此多用户同时 操作。试想用户查询一条记录需要花费很长时间，用户很难会喜欢这个网站。因此，为了提高M ySQL数据库的性能，需要进行一系列的优化措施。如果M ySQL数据库需要进行 大量的查询操作，那么就需要对查询语句进行优化。对于耗费时间的查询语句进行优化，可以提高 整体的查询速度。如果连接M ySQL数据库用户很多，那么就需要对M ySQL服务器进行优化。否则，大 量的用户同时连接M ySQL数据库，可能会造成数据库系统崩溃。数据库管理员可以使用SHOW STA TUS语句查询M ySQL数据库的性能。语法形式如下：SHOW STATUS LIKE value;其中，value参数时常用的几个统计参数。这些常用参数介绍如下。Connections：连接M ySQL服务器的次数；Uptime：M ySQL服务器的上线时间；Slow queries：慢查询的次数；Com_select：查询操作的次数；Com_insert：插入操作的次数；Com_delete：删除操作的次数。说明：M ySQL中存在查询InnoDB类型的表的一些参数。例如，Innodbjowsjead参数表示SE LE CT语句查询 的记录数；Innodb rows inserted参数表示INSE RT语句插入的记录数；Innodb rows updated参数 表示UPDA TE语句更新的记录数；Innodb_rows_deleted参数表示DE LE TE语句删除的记录数。如果需要查询M ySQL服务器的连接次数，可以执行下面的SHOW STA TUS语句：SHOW STATUS LIKE Connections,;通过这些参数可以分析M ySQL数据库性能。然后根据分析结果，进行相应的性能优化。13.2优化查询13.2.1分析查询语句13.2.2索引对查询速度的影响13.2.3使用索引查询.J A A ，J i .13.2.1分析查询语句分析查询语句在前面小节中都有应用，在M ySQL中，可以使用E XPLA IN语句和DE SCRIB E语句来分 析查询语句。应用E XPLA IN关键字分析查询语句，其语法结构如下：EXPLAIN SELECT语句；“SE LE CT语句”参数为一般数据库查询命令，如“SE LE CT*FROM students”。【例13-1下面使用E XPLA IN语句分析一个查询语句，其代码如下：EXPLAIN SELECT*FROM timcinfb;其运行结果如图13T所示。roysql EXPLAIN SELECT*FROM tineinfo；!id s elect，ype!table；type：possible_Jceysi key key_len!ref：row s!Extra I卜一.；1:SIMPLE!timeinfo：ALL：NULL：NULL!NULL INULL!2 I S1 row in set 图13-1应用E XPLA IN分析查询语句仁其中各字段所代表的意义如下所示：id列：指出在整个查询中SE LE CT的位置。table列：存放这、所查询的表名。type列：连接类型，该列中存储很多值，范围从const到A LL。possible_keys列：指出为了提高查找速度，在M ySQL中可以使用的索引。key列：指出实际使用的键。rows列：指出M ySQL需要在相应表中返回查询结果所检验的行数，为了得到该总行数，M ySQL必 须扫描处理整个查询，再成一每个表的行值。E xtra列：包含一些其他信息，设计M ySQL如何处理查询。在M ySQL中，也可以应用DE SCRIB E语句来分析查询语句。DE SCRIB E语句的使用方法与E XPLA IN语 法是相同的，这两者的分析结果也大体相同。其中DE SCRIB E的语法结构如下：DESCRIBE SELECT 语句；在命令提示符下输入如下命令：describe select*from studcntinfb;.其运行结果如图13-2所示。nysql describe s elect*from tineinfo；id select_type!table i type i possible一keys key!key_len i ref!rdws!Extra i+-+-+-+-+-+-+!1!SIMPLE!tine info!ALL!NULL!NULL!NULL：NULL I2:：1 row in s et 图13-2应用DE SCRIB E分析查询语句将图13-2与图13-1对比，读者可以清楚的看出，其运行结果基本相同。分析查询也可以应用 DE SCRIB E关键字。说明：DE SCRIB E”可以缩写成“DE SC”。13.2.2索引对查询速度的影响 在查询过程中使用索引，势必会提高数据库查询效率，应用索引来查询数据库中的内容，可以减少查询的记录数，从而达到查询优化的目的。下面将通过对使用索引和不使用索引进行对比，来分析查询的优化情况。【例13-2】首先，分析未使用索引时的查询情况，其代码如下：，explain select*from studentinfo where name=mrsoft其运行结果如图13-3所示。图13-3未使用索引的查询情况上述结果表明，表格字段rows下为7,这意味这在执行查询过程中，数据库存在的7条数据都被 查询了一遍，这样在数据存储量小的时候，查询不会有太大影响，试想当数据库中存储庞大的数据 资料时，用户为了搜索一条数据而遍历整个数据库中的所有记录，这将会耗费很多时间。现在，在 name字段上建立一个名为index_name的索引。创建索引的代码如下：CREATE INDEX index_name ON studentinfo(name);上述代码的作用是在student info表的name字段添加索引。在建立索引完毕后，然后再应用 E XPLA IN关键字分析执行情况，其代码如下所示：explain select*from studentinfo where name=mrsoft 其运行结果如图13-4所示。图13-4使用索引后查询情况从上述结果可以看出，由于创建的索引使访问的的行数由7行减少到1行。所以，在查询操作中,使用索引不但会自动优化查询效率，同时也会降低服务器的开销。13.2.3使用索引查询在M ySQL中，索引可以提高查询的速度。但并不能充分发挥其作用，所以在应用索引查询时，也可以通过关键字或其他方式来对查询进行优化处理。1.应用LIKE关键字优化索引查询【例13-3】下面示例应用LIKE关键字，并且匹配字符串中含有百分号“”符号，应用E XPLA IN语句执行如下命令：EXPLAIN SELECT*FROM studentinfo WHERE name LIKE 其运行结果如图13-5所示。nysql explain s elect*from studentinfo w here name like f zl,；!id i select_type i table i type i pos s ibleJeys i key i key_len；ref!row s i Extra i!1 I SIMPLE!studentinfo!ALL；NULL!NULL；NULL I NULL!7 Using w here!row in s et 图13-5应用LIKE关键字优化索引查询仁口从图13-5中可能看出其rows参数仍为“7”并没有起到优化作用，这是因为如果匹配字符串中,第一个字符为百分号“”时，索引不会被使用，如果“”所在匹配字符串中的位置不是第一位置,则索引会被正常使用，在命令提示符中输入如下命令：EXPLAIN SELECT*FROM studentinfo WHERE name LIKE le%;运行结果如图13-6所示。1 row in s et(nysqlexplains elect*from studentinfow here name like9 lex9；!id!refselect_type i table row s Extra itypepossible_keys!keyi key_len!1!NULLSIMPLE!1!stud entinfo Using w here i-+-!range-+-i index_name-+-i index_nane-+-：152-+-图13-6正常应用索引的LIKE子句运行结果2.查询语句中使用多列索引多列索引在表的多个字段上创建一个索引。只有查询条件中使用了这些字段中的一个字段时，索引才会被正常使用。应用多列索引在表的多个字段中创建一个索引，其命令如下：CREATE INDEX index_student_info ON studentinfo(name,sex);说明：在应用sex字段时，索引不能被正常使用。这就意味着索引并未在M ySQL优化中起到任何作用，故 必须使用第一字段name时，索引才可以被正常使用，有兴趣的读者可以实际动手操作一下。这里不 再赘述。仁口3.查询语句中使用OR关键字在M ySQL中，查询语句只有包含OR关键字时，要求查询的两个字段必须同为索引，如果所搜索 的条件中，有一个字段不为索引，则在查询中不会应用索引进行查询。其中，应用OR关键字查询索 引的命令如下：SELECT*FROM studentinfo WHERE name=Chris or sex-M;【例13-4】同样，通过E XPLA IN来分析查询命令，在命令提示符中输入如下代码：EXPLAIN SELECT*FROM studentinfo WHERE name=Chris or sex=M;其运行结果如图13-7所示。nysql explain:s elect*from studentinfo w here name=*Chris*or sex=,；!id i select_type key_len i ref i:i table!type i poss iblejceysrow s i Extra i!key!1 1!SIMPLE NULL：NULLai student inf o!ALL i index_nane,index_student_inf o 1 Using w here i!NULL!1 row in s et desc student_extra；Field1 1Type1 1Null1 1Key1 1Default-+-Extraid1 1int1 1NO1 1PRI1 1NULL auto_increnentextra1text1YES11NULL1-+-2 row s in s et 图13-8将字段很多的表分解成多个表,如果需要查询某个学生的备注信息，可以用学号（id）来查询。如果需要将学生的学籍信息与备注信息同时显示时，可以将student表和student_extra表进行联表查询，查询语句如下：SELECT*FROM student,student_extra WHERE student.id=student_extra.id;通过这种分解，可以提高student表的查询效率。因此，遇到这种字段很多，而且有些字段使用 不频繁的，可以通过这种分解的方式来优化数据库的性熊。仁口图13-9增加中间表13.3.2增加中间表有时需要经常查询某两个表中的几个字段。如果经常进行联表查询，会降低M ySQL数据库的查询 速度。对于这种情况，可以建立中间表来提高查询速度。本小节将为读者介绍增加中间表的方法。先分析经常需要同时查询哪几个表中的哪些字段。然后将这些字段建立一个中间表，并将原来那几 个表的数据插入到中间表中，之后就可以使用中间表来进行查询和统计。【例13-6】下面有个学生表student和分数表score。这两个表的结构如下：nysql desc student；!Field!Type!Null 1!Key i Default!Extra!id；int!NOPRI!NULLauto_increnenti name i uarchar i NOMUL!NULL；sex!varchai*:NO；NULL!age!int；NO!NULLi zhuanye i uarchar NO!NULLi address uarchar i NO：NULL._._ _ rr6 row s in s et nysql desc score；!Field!Type：Null 11 Key Default 1 Extra；id!int!NOPRI!NULLauto_increnent；stu_id!int:NO!NULLi c_name!uarchar i VES!NULL!grade!int!YES!NULL4 row s in set musal 一实际中经常要查学生的学号、姓名和成绩。根据这种情况可以创建一个temp_score表。temp score表中存储3个字段，分别是id,name和grade。CRE A TE语句执行如下：CREATE TABLE temp_score(id INT NOT NULL,Name VARCHAR(20)NOT NULL,grade FLOAT);然后从student表和score表中将记录导入到temp score表中。INSE RT语句如下：INSERT INTO temp_score SELECT student.id,student.name,score.gradeFROM student,score WHERE student.id=score.stu_id;将这些数据插入到temp_score表中以后，可以直接从temp_score表中查询学生的学号、姓名和 成绩。这样就省去了每次查询时进行表连接。这样可以提高数据库的查询速度。13.3.3优化插入记录的速度 插入记录时，索引、唯一性校验都会影响到插入记录的速度。而且，一次插入多条记录和多次.插入记录所耗费的时间是不一样的。根据这些情况，分别进行不同的优化。本小节将为读者介绍优 Ki 化插入记录的速度的方法。弓 I不入;己录时，M ySQL会根据表的索引对插入的记录进行排序。如果插入大量数据时，这些排序会I 降低插入记录的速度。为了解决这种情况，在插入记录之前先禁用索引。等到记录都插入完毕后再 开启索引。禁用索引的语句如下：U ALTER TABLE 表名 DISABLE KEYS;重新开启索引的语句如下：I.ALTER TABLE 表名 ENABLE KEYS;对于新创建的表，可以先不创建索引。等到记录都导入以后再创建索引。这样可以提高导入数 一，据的速度。2.禁用唯一性检查*插入数据时，M ySQL会对插入的记录进行校验。这种校验也会降低插入记录的速度。可以在插入.记录之前禁用唯一性检查。等到记录插入完毕后再开启。禁用唯一性检查的语句如下：W SET UNIQUE_CHECKS=O;重新开启谨一性检查的语句如下：SET UNIQUE_CHECKS=1;3.优化INSE RT语句.插入多条记录时，可以采取两种写INSE RT语句的方式。第一种是一个INSE RT语句插入多条记录.o INSE RT语句的情形如下：.INSERT INTO food VALUES（NULL,果冻，CC果冻厂,1.8,2011,北京）,（NULL,咖啡，CF咖啡厂，25,2012,天津）,（NULL,奶糖，旺仔奶糖，15,2013,广东）;第二种时一个INSE RT语句只插入一条记录，执行多个INSE RT语句来插入多条记录。INSE RT语句的情形如下；INSERTINTOfoodVALUES（NULL,果冻，CC果冻厂，L8,2011,北京）;,INSERT INTO food VALUES（NULL,咖啡，CF咖啡厂，25,2012,天津）;INSERT INTO food VALUES（NULL,奶糖，旺仔奶糖，15,2013,广东）;第一种方式减少了与数据库之间的连接等操作，其速度比第二种方式要快。说明之 当插入大量数据时，建议使用一个INSE RT语句插入多条记录的方式。而且，如果能用LOA D DA TAINFILE语句，就尽量用LOA D DA TA INFILE语句。因为LOA D DA TA INFILE语句导入数据的速度比 INSE RT语句的速度快。13.3.4分析表、检查表和优化表 分析表主要作用是分析关键字的分布。检查表主要作用是检查表是否存在错误。优化表主要作.用是消除删除或者更新造成的空间浪费。本小节将为读者介绍分析表、检查表和优化表的方法。,1.分析表.M ySQL中使用A NA LYZE TA B LE语句来分析表，该语句的基本语法如下：.使用A NA LYZE TA B LE分析表的过程中，数据库系统会对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。A NA LYZE TA B LE语句能够分析InnoDB和M ylSA M类型的表。.【例13-7下面使用A NA LYZE TA B LE语句分析score表，分析结果如下：nysql ANALYZE TABLE s core；卜-.-i Table!Op i Msg_type i Msg_text!卜-+-+-+-+db_databasel4.score!analyze i s tatus i Table is already up to date ik-+-+-+-+L row in s et 图13-10分析表上面结果显示了4列信息，详细介绍如下：Table：表示表的名称；Op：表示执行的操作。analyze表示进行分析操作。check表示进行检查查找。optimize表示进 行优化操作；M sg_type：表示停息类型，其显示的值通常是状态、警告、错误和信息这四者之一；M sg_text:：显示信息。检查表布优化表之后也会出现这4列信息。2.检查表M ySQL中使用CHE CK TA B LE语句来检查表。CHE CK TA B LE语句能够检查InnoDB和M ylSA M类型的表是 否存在错误。而且，该语句还可以检查视图是否存在错误。该语句的基本语法如下：CHECK TABLE 表名 1，表名 2option;其中，option参数有5个参数，分别是QUICK、FA ST、CHA NGE D M E D I UM和E XTE NDE D。这5个参数 的执行效率依次降低。option选项只对M ylSA M类型的表有效，对InnoDB类型的表无效。CHE CK TA B LE语句在执行过程中也会给表加上只读锁。3.优化表M ySQL中使用OPTIM IZE TA B LE语句来优化表。该语句对InnoDB和M ylSA M类型的表都有效。但是,OPTILM IZE TA B LE与拒绝只能优化表中的V A RCHA R、B LOB或TE XT类型的字段。OPTILM IZE TA B LE语 句的基本语法如下：OPTIMIZE TABLE 表名 1，表名2;通过OPTIM IZE TA B LE语句可以消除删除和更新造成的磁盘碎片，从而减少空间的浪费。OPTIM IZE TA B LE语句在执行过程中也会给表加上只读锁。说明：如果一个表使用了TE XT或者B LOB这样的数据类型，那么更新、删除等操作就会造成磁盘空间的 浪费。因为，更新和删除操作后，以前分配的磁盘空间不会自动收回。使用OPTIM IZE TA B LE语句就 可以将这些磁盘碎片整理出来，以便以后再利用。13.4查询高速缓存13.4.1检验高速缓存是否开启13.4.2使用高速缓存13.4.1检验高速缓存是否开启【例13-8】在M ySQL中，应用V A RIA B LE S关键字，以通配符形式查看服务器变量。其代码如下:SHOW VARIABLES LIKE%query_cache%;运行上述代码，其结果如图13-11所示。1 row in s et mysql shovi uariables like,Zquery_cacheZ,；+-+-+!Uariable_nane!Ualue ihaue_query_cache I YES iquery_cache_linit 1048576 iquery_cache_nin_res_unit 4096 iquery_cache_s ize 0 ique ry_c ac he _t ype I ON!quei*y_cache_w lock_inualidate OFF+-+-+6 row s in s et 下面对主要的参数进行说明：have_query_cache:：表明服务器在默认安装条件下，是否已经配置查询高速缓存。query_cache_size：高速缓存分配空间，如果该空间为86则证明分配给高速缓存空间的大小为 8 6M B o如果该值为0则表明查询高速缓存已经关闭。query_cache_type：判断高速缓存开启状态，其变量值范围从0-2。其中当该值为。或OFF时，表 明查而高速凌存已经关闭；当该值为1或ON时表明高速缓存已经打开；其值为2或DE M A ND时，表 明要根据需要运行打死有SQL/A CHE选项的SE LE CT语句，提供查询高速缓存。13.4.2使用高速缓存在M ySQL中，查询高速缓存的具体语法结构如下：SELECT SQL_CACHE*FROM 表名；【例13-9】下面通过具体示例来查询高速缓存运行中的反应结果。在命令提示符下输入以下命令SELECT SQL_CACHE*FROM student;其运行结巢如图13-12所示。mysql s elect sql_cache*from student；!id name i sex i age i zhuanye i address123456一果三四五也 王章女女男男女男 II 3!1985!计算机系!1986!中文系；1990:中文系：1990；学语系；1991!黄语系：1988:升算机系;北京市海淀区;北京市昌平区福建誉面访:湖磊初阳市row s in s et 图13-12使用查询高速缓存运行结果仁口然后不使用高速缓存查询该数据表。其结果如图13-13所示。iysql s elect sql_no_cache*from student；id i name sex i age i zhuanye i address12345 6燕 一枭三四五也 王章!玄!1985:计号加系!女；1986:中文系：男：1990：中文系!期 5 1990：猪鼓!女 1991!对f系男!1988；计舁机系；北京市海淀区；北京市昌平区福湾奥师；湖磊赛阳市row s in s et 图13-13未使用查询高速缓存运行结果如果经常运行查询高速缓存，将会提高M ySQL数据库的性能。说明：一旦表有变化，使用这个表的查询高速缓存将会失效。且将从高速缓存中删除。这样放置查询 从旧表中返回无效数据。另外不使用高速缓存查找可以应用SQL_NO CA CHE关键字。L n r m 二13.5优化多表查询 在M ySQL中，用户可以通过连接来实现多表查询，在查询过程中，用户将表中的一个或多个共同.字段进行连接，定义查询条件，返回统一的查询结果。这通常用来建立RDB M S常规表之间的关系。在 多表查询中，可以应用子查询来优化多表查询，即在SE LE CT语句中嵌套其他SE LE CT语句。采用子查.询优化多表查询的好处有很多，其中，可以将分步查询的结果整合成一个查询，这样就不需要在执,行多个单独查询，从而提高了多表查询的效率。1例13-10下面通过一个实例来说明如何优化多表查询，首先在命令提示符输入如下命令：select address from student where id=(select id from student_extra wh.ere name=nihao);其运行结果如图13-14所示。mysql s elect address from student w here id=s elect id from student_extra w here jxtra=J nihao9；i address;五京市博淀区row in s et 图13-14应用一般SE LE CT嵌套子查询下面应用优化算法，以便可以优化查询速度。在命令提示符输入以下命令：select address from student as stu,student_extra as stu_e where stu.id=stu_e.id and stu_e.extra=nihao;以上命令的作用是将student和student_extra表分别设置别名stu、stu_e,通过两个表的id字 段建立连接，并判断student_extra表中是否含有名称为“nihao”的内容，异将地址在屏幕上输出 O该语句已经将算法进行优花，以便提高数据库的效率从而实现查询优化的效果。其运行结果如图 13T5所示。mysql s elect address from student w here id=；.-i address i:北京市海淀区+:4-+1 row in s et 图13-15应用算法的优化查询如果用户希望避免因出现SE LE CT嵌套而导致代码可读性下降，则用户可以通过服务器变量来进 行优化处理，下面应用SE LE CT嵌套方式来查询数据，在命令提示符中输入如下命令：select name from student where age(select avg(age)from student_extra);.其运行结果如图13T6所示。iysql s elect name from student w here age Xs elect aug from student_extra；!name燕一汽三四一n_row s in s et SHOW STATUS LIKE9 ConnectionsJ；i Uariable_nane Ualue；i Connections 231 row in s et mysql SHOU STATUS LIKE9Com_selectJ；i Uariable_name*Ualue；i Con_select*6 i1 row in s et mysql SHOU STATUS LIKE9Slow-queries,；i Uariable_name i Ualuei Slow _queries!0 i1 row in s et .图13-17查看M ySQL服务器的连接和查询次数使用SHOW STA TUS语句时，可以通过指定统计参数为Connections、Com select和Slow queries,来实现显示M ySQL服务器的连接数、查询次数和慢查询次数的功能。本实椀的关键代码如下：SHOW STATUS LIKE Connections;SHOW STATUS LIKE Com_select,;SHOW STATUS LIKE Slow_queries,;知拥点提炼(1)数据库管理员可以使用SHOW STA TUS语句查询M ySQL数据库的性能。(2)在M ySQL中，可以使用E XPLA IN语句和DE SCRIB E语句来分析查询语句。(3)在查询过程中使用索引，势必会提高数据库查询效率，应用索引来查询数据库中的内容，可以 减少查询的记录数，从而达到查询优化的目的。(3)对于字段特别多且有些字段的使用频率很低的表，可以将其分解成多个表。(4)有时需要经常查询某两个表中的几个字段，这种情况，可以建立中间表来提高查询速度。(5)优化表主要作用是消除删除或者更新造成的空间浪费。(6)在M ySQL中，用户可以通过连接来实现多表查询，在查询过程中，用户将表中的一个或多个共 同字段进行连接，定义查询条件，返回统一的查询结果。打散 1.分析表、检查表和优化表时出现4列详细信息分别是什么。.2.在表的多个字段中创建一个索引的SQL语句是什么。3.如何检查高速缓存是否开启。二二二仁口