MySQL入门很简单 第18章 性能优化.docx

1、MySQL入门很简单 第18章 性能优化第18章 性能优化性能优化是通过某些有效的方法提高MySQL数据库的性能。性能优化的目的是为了使MySQL数据库运行速度更快、占用的磁盘空间更小。性能优化包括很多方面，例如优化查询速度、优化更新速度和优化MySQL服务器等。本章将为读者介绍的内容包括：性能优化的介绍；优化查询；优化数据库结构；优化MySQL服务器。通过本章的学习，读者可以了解性能优化的目的。读者还可以了解优化查询、优化数据库结构和优化MySQL服务器的方法。学习完本章后，读者可以根据本章的知识对MySQL数据库进行相应的优化，以提高MySQL数据库的速度。18.1 优化简介优化MySQL

2、数据库是数据库管理员的必备技能。通过不同的优化方式达到提高MySQL数据库性能的目的。本节将为读者介绍优化的基本知识。MySQL数据库的用户和数据非常少的时候，很难判断一个MySQL数据库的性能的好坏。只有当长时间运行，并且有大量用户进行频繁操作时，MySQL数据库的性能才能体现出来了。例如，一个每天有几万用户同时在线的大型网站的数据库性能的优劣就很明显。这么多用户在同时连接MySQL数据库，并且进行查询、插入和更新的操作。如果MySQL数据库的性能很差，很可能无法承受如此多用户同时操作。试想用户查询一条记录需要花费很长时间，用户很难会喜欢这个网站。因此，为了提高MySQL数据库的性能，需要进

3、行一系列的优化措施。如果MySQL数据库中需要进行大量的查询操作，那么就需要对查询语句进行优化。对于耗费时间的查询语句进行优化，可以提高整体的查询速度。如果连接MySQL数据库的用户很多，那么就需要对MySQL服务器进行优化。否则，大量的用户同时连接MySQL数据库，可能会造成数据库系统崩溃。数据库管理员可以使用SHOW STATUS语句查询MySQL数据库的性能。语法形式如下：1 SHOW STATUS LIKE value ; 其中，value参数是常用的几个统计参数。这些常用参数介绍如下。Connections：连接MySQL服务器的次数；Uptime：MySQL服务器的上线时间；Slo

4、w_queries：慢查询的次数；Com_select：查询操作的次数；Com_insert：插入操作的次数；Com_update：更新操作的次数；Com_delete：删除操作的次数。说明：MySQL中存在查询InnoDB类型的表的一些参数。例如，Innodb_rows_read参数表示SELECT语句查询的记录数；Innodb_rows_inserted参数表示INSERT语句插入的记录数；Innodb_rows_updated参数表示UPDATE语句更新的记录数；Innodb_rows_deleted参数表示DELETE语句删除的记录数。如果需要查询MySQL服务器的连接次数，可以执行下

5、面的SHOW STATUS语句：2 SHOW STATUS LIKE Connections ; 通过这些参数可以分析MySQL数据库的性能。然后根据分析结果，进行相应的性能优化。18.2 优化查询查询是数据库中最频繁的操作。提高了查询速度可以有效的提高MySQL数据库的性能。本节将为读者介绍优化查询的方法。18.2.1 分析查询语句通过对查询语句的分析，可以了解查询语句的执行情况。MySQL中，可以使用EXPLAIN语句和DESCRIBE语句来分析查询语句。本小节将为读者介绍这两种分析查询语句的方法。EXPLAIN语句的基本语法如下：1 EXPLAIN SELECT语句 ; 通过EXPLAI

6、N关键字可以分析后面的SELECT语句的执行情况。并且能够分析出所查询的表的一些内容。【示例18-1】 下面使用EXPLAIN语句来分析一个查询语句。代码执行如下：2 mysql EXPLAIN SELECT * FROM student G 3 * 1. row * 4 id: 1 5 select_type: SIMPLE 6 table: student 7 type: ALL 8 possible_keys: NULL 9 key: NULL 10 key_len: NULL 11 ref: NULL 12 rows: 6 13 Extra: 14 1 row in set (0.01

7、 sec) 查询结果显示了id、select_type、table、type、possible_keys和key等信息。下面分别进行解释。id：表示SELECT语句的编号；select_type：表示SELECT语句的类型。该参数有几个常用的取值，即SIMPLE表示简单查询，其中不包括连接查询和子查询；PRIMARY表示主查询，或者是最外层的查询语句；UNION表示连接查询的第二个或后面的查询语句；table：表示查询的表；type：表示表的连接类型。该参数有几个常用的取值，即system表示表中只有一条记录；const表示表中有多条记录，但只从表中查询一条记录；ALL表示对表进行了完整的扫描

8、；eq_ref表示多表连接时，后面的表使用了UNIQUE或者PRIMARY KEY；ref表示多表查询时，后面的表使用了普通索引；unique_subquery表示子查询中使用了UNIQUE或者PRIMARY KEY；index_subquery表示子查询中使用了普通索引；range表示查询语句中给出了查询范围；index表示对表中的索引进行了完整的扫描；possible_keys：表示查询中可能使用的索引；key：表示查询使用到的索引；key_len：表示索引字段的长度；ref：表示使用哪个列或常数与索引一起来查询记录；rows：表示查询的行数；Extra：表示查询过程的附件信息。DESCR

9、IBE语句的使用方法与EXPLAIN语句是一样的。这两者的分析结果也是一样的。DESCRIBE语句的语法形式如下：15 DESCRIBE SELECT语句 ; DESCRIBE可以缩写成DESC。18.2.2 索引对查询速度的影响索引可以快速的定位表中的某条记录。使用索引可以提高数据库查询的速度，从而提高数据库的性能。本小节将为读者介绍索引对查询速度的影响。如果查询时不使用索引，查询语句将查询表中的所有字段。这样查询的速度会很慢。如果使用索引进行查询，查询语句只查询索引字段。这样可以减少查询的记录数，达到提高查询速度的目的。【示例18-2】 下面是查询语句中不使用索引和使用索引的对比。现在分析

10、未使用索引时的查询情况，EXPLAIN语句执行如下：1 mysql EXPLAIN SELECT * FROM student WHERE name=张三G 2 * 1. row * 3 id: 1 4 select_type: SIMPLE 5 table: student 6 type: ALL 7 possible_keys: NULL 8 key: NULL 9 key_len: NULL 10 ref: NULL 11 rows: 6 12 Extra: Using where 13 1 row in set (0.00 sec) 结果显示，rows参数的值为6。这说明这个查询语句查

11、询了6条记录。现在在name字段上建立一个名为index_name的索引。CREATE语句执行如下：14 mysql CREATE INDEX index_name ON student(name); 15 Query OK, 6 rows affected (0.09 sec) 16 Records: 6 Duplicates: 0 Warnings: 0 现在，name字段上已经有索引了，然后再分析查询语句的执行情况。EXPLAIN语句执行如下：17 mysql EXPLAIN SELECT * FROM student WHERE name=张三G 18 * 1. row * 19 id

12、: 1 20 select_type: SIMPLE 21 table: student 22 type: ref 23 possible_keys: index_name 24 key: index_name 25 key_len: 22 26 ref: const 27 rows: 1 28 Extra: Using where 29 1 row in set (0.01 sec) 结果显示，rows参数的值为1。这表示这个查询语句只查询了一条记录，其查询速度自然比查询6条记录快。而且possible_keys和key的值都是index_name，这说明查询时使用了index_name索引

13、。18.2.3 使用索引查询索引可以提高查询的速度。但是有些时候即使查询时使用的是索引，但索引并没有起作用。本小节将向读者介绍索引的使用。1查询语句中使用LIKE关键字在查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为%时，索引不会被使用。如果%不是在第一个位置，索引就会被使用。【示例18-3】 下面查询语句中使用LIKE关键字，并且匹配的字符串中含有%符号。EXPLAIN语句执行如下：1 mysql EXPLAIN SELECT * FROM student WHERE name LIKE %四 G 2 * 1. row * 3 id: 1 4 select_type:

14、SIMPLE 5 table: student 6 type: ALL 7 possible_keys: NULL 8 key: NULL 9 key_len: NULL 10 ref: NULL 11 rows: 6 12 Extra: Using where 13 1 row in set (0.00 sec) 14 15 mysql EXPLAIN SELECT * FROM student WHERE name LIKE 李% G 16 * 1. row * 17 id: 1 18 select_type: SIMPLE 19 table: student 20 type: range

15、 21 possible_keys: index_name 22 key: index_name 23 key_len: 22 24 ref: NULL 25 rows: 1 26 Extra: Using where 27 1 row in set (0.00 sec) 第一个查询语句执行后，rows参数的值为6，表示这次查询过程中查询了6条记录；第二个查询语句执行后，rows参数的值为1，表示这次查询过程只查询一条记录。同样是使用name字段进行查询，第一个查询语句没有使用索引，而第二个查询语句使用了索引index_name。因为第一个查询语句的LIKE关键字后的字符串以%开头。2查询语句

16、中使用多列索引多列索引是在表的多个字段上创建一个索引。只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。【示例18-4】 下面在birth和department两个字段上创建多列索引，然后验证多列索引的使用情况。28 mysql CREATE INDEX index_birth_department ON student(birth,department); 29 Query OK, 6 rows affected (0.01 sec) 30 Records: 6 Duplicates: 0 Warnings: 0 31 32 mysql EXPLAIN SELECT * FROM

17、student WHERE birth=1991 G 33 * 1. row * 34 id: 1 35 select_type: SIMPLE 36 table: student 37 type: ref 38 possible_keys: index_birth_department 39 key: index_birth_department 40 key_len: 2 41 ref: const 42 rows: 1 43 Extra: Using where 44 1 row in set (0.00 sec) 45 46 mysql EXPLAIN SELECT * FROM st

18、udent WHERE department=英语系 G 47 * 1. row * 48 id: 1 49 select_type: SIMPLE 50 table: student 51 type: ALL 52 possible_keys: NULL 53 key: NULL 54 key_len: NULL 55 ref: NULL 56 rows: 6 57 Extra: Using where 58 1 row in set (0.00 sec) 在birth字段和department字段上创建一个多列索引。第一个查询语句的查询条件使用了birth字段，分析结果显示rows参数的值

19、为1。而且显示查询过程中使用了index_birth_department索引。第二个查询语句的查询条件使用了department字段，结果显示rows参数的值为6。而且key参数的值为NULL，这说明第二个查询语句没有使用索引。因为name字段是多列索引的第一个字段，只有查询条件中使用了name字段才会使index_name_department索引起作用。3查询语句中使用OR关键字查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引时，查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。【示例18-5】 下面演示OR关键字的使用。59 mysql EXPL

20、AIN SELECT * FROM student WHERE name=张三 or sex=女 G 60 * 1. row * 61 id: 1 62 select_type: SIMPLE 63 table: student 64 type: ALL 65 possible_keys: index_name 66 key: NULL 67 key_len: NULL 68 ref: NULL 69 rows: 6 70 Extra: Using where 71 1 row in set (0.00 sec) 72 73 mysql EXPLAIN SELECT * FROM studen

21、t WHERE name=张三 or id=3 G 74 * 1. row * 75 id: 1 76 select_type: SIMPLE 77 table: student 78 type: index_merge 79 possible_keys: PRIMARY,id,index_name 80 key: index_name,PRIMARY 81 key_len: 22,4 82 ref: NULL 83 rows: 2 84 Extra: Using union(index_name,PRIMARY); Using where 85 1 row in set (0.00 sec)

22、 第一个查询语句没有使用索引，因为sex字段上没有索引；第二个查询语句使用了index_name和PRIMARY这两个索引，因为name字段和id字段上都有索引。技巧：使用索引查询记录时，一定要注意索引的使用情况。例如，LIKE关键字配置的字符串不能以%开头；使用多列索引时，查询条件必须要使用这个索引的第一个字段；使用OR关键字时，OR关键字连接的所有条件都必须使用索引。18.2.4 优化子查询很多查询中需要使用子查询。子查询可以使查询语句很灵活，但子查询的执行效率不高。子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句再临时表中查询记录。查询完毕后，MySQL需

23、要撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。在MySQL中可以使用连接查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快。18.3 优化数据库结构数据库结构是否合理，需要考虑是否存在冗余、对表的查询和更新的速度、表中字段的数据类型是否合理等多方面的内容。本节将为读者介绍优化数据库结构的方法。18.3.1 将字段很多的表分解成多个表有些表在设计时设置了很多的字段。这个表中有些字段的使用频率很低。当这个表的数据量很大时，查询数据的速度就会很慢。本小节将为读者介绍优化这种表的方法。对于这种字段特别多且有些字段的使用频率很低的表，可以

24、将其分解成多个表。【示例18-6】 下面的学生表中有很多字段，其中在extra字段中存储着学生的备注信息。有些备注信息的内容特别多。但是，备注信息很少使用。这样就可以分解出另外一个表。将这个取名叫student_extra。表中存储两个字段，分别为id和extra。其中，id字段为学生的学号，extra字段存储备注信息。student_extra表的结构如下：1 mysql DESC student_extra ; 2 +-+-+-+-+-+-+ 3 | Field | Type | Null | Key | Default | Extra | 4 +-+-+-+-+-+-+ 5 | id |

25、 int(11) | NO | PRI | NULL | | 6 | extra | text | YES | | NULL | | 7 +-+-+-+-+-+-+ 8 2 rows in set (0.00 sec) 如果需要查询某个学生的备注信息，可以用学号（id）来查询。如果需要将学生的学籍信息与备注信息同时显示时，可以将student表和student_extra表进行联表查询，查询语句如下：9 SELECT * FROM student, student_extra WHEREstudent.id=student_extra.id ; 通过这种分解，可以提高student表的查询效率

26、。因此，遇到这种字段很多，而且有些字段使用不频繁的，可以通过这种分解的方式来优化数据库的性能。18.3.2 增加中间表有时需要经常查询某两个表中的几个字段。如果经常进行联表查询，会降低MySQL数据库的查询速度。对于这种情况，可以建立中间表来提高查询速度。本小节将为读者介绍增加中间表的方法。先分析经常需要同时查询哪几个表中的哪些字段。然后将这些字段建立一个中间表，并将原来那几个表的数据插入到中间表中，之后就可以使用中间表来进行查询和统计了。【示例18-7】 下面有个学生表student和分数表score。这两个表的结构如下：1 mysql DESC student; 2 +-+-+-+-+-+

27、-+ 3 | Field | Type | Null | Key | Default | Extra | 4 +-+-+-+-+-+-+ 5 | id | int(10) | NO | PRI | NULL | | 6 | name | varchar(20) | NO | MUL | NULL | | 7 | sex | varchar(4) | YES | | NULL | | 8 | birth | year(4) | YES | MUL | NULL | | 9 | department | varchar(20) | YES | | NULL | | 10 | address | varchar(50) | YES | | NULL | | 11 +-+-+-+-+-+-+ 12 6 rows in set (0.03 sec) 13 14 mysql DESC score; 15 +-+-+-+-+-+-+ 16 | Field | Type | Null | Key | Default | Extra | 17 +-+-+-+-+-+-+ 18 | id | int(10) | NO | PRI | NULL | auto_increment | 19 | stu_id | int(10) | NO | MUL | NULL | | 20 | c_name |