MySQL入门很简单 第18章 性能优化文档格式.docx
- 文档编号:4011714
- 上传时间:2023-05-02
- 格式:DOCX
- 页数:26
- 大小:27.75KB
MySQL入门很简单 第18章 性能优化文档格式.docx
《MySQL入门很简单 第18章 性能优化文档格式.docx》由会员分享,可在线阅读,更多相关《MySQL入门很简单 第18章 性能优化文档格式.docx(26页珍藏版)》请在冰点文库上搜索。
MySQL服务器的上线时间;
Slow_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服务器的连接次数,可以执行下面的SHOWSTATUS语句:
2SHOWSTATUSLIKE'
Connections'
通过这些参数可以分析MySQL数据库的性能。
然后根据分析结果,进行相应的性能优化。
18.2优化查询
查询是数据库中最频繁的操作。
提高了查询速度可以有效的提高MySQL数据库的性能。
本节将为读者介绍优化查询的方法。
18.2.1分析查询语句
通过对查询语句的分析,可以了解查询语句的执行情况。
MySQL中,可以使用EXPLAIN语句和DESCRIBE语句来分析查询语句。
本小节将为读者介绍这两种分析查询语句的方法。
EXPLAIN语句的基本语法如下:
1EXPLAINSELECT语句;
通过EXPLAIN关键字可以分析后面的SELECT语句的执行情况。
并且能够分析出所查询的表的一些内容。
【示例18-1】下面使用EXPLAIN语句来分析一个查询语句。
代码执行如下:
2mysql>
EXPLAINSELECT*FROMstudent\G
3***************************1.row***************************
4id:
1
5select_type:
SIMPLE
6table:
student
7type:
ALL
8possible_keys:
NULL
9key:
10key_len:
11ref:
12rows:
6
13Extra:
141rowinset(0.01sec)
查询结果显示了id、select_type、table、type、possible_keys和key等信息。
下面分别进行解释。
id:
表示SELECT语句的编号;
select_type:
表示SELECT语句的类型。
该参数有几个常用的取值,即SIMPLE表示简单查询,其中不包括连接查询和子查询;
PRIMARY表示主查询,或者是最外层的查询语句;
UNION表示连接查询的第二个或后面的查询语句;
table:
表示查询的表;
type:
表示表的连接类型。
该参数有几个常用的取值,即system表示表中只有一条记录;
const表示表中有多条记录,但只从表中查询一条记录;
ALL表示对表进行了完整的扫描;
eq_ref表示多表连接时,后面的表使用了UNIQUE或者PRIMARYKEY;
ref表示多表查询时,后面的表使用了普通索引;
unique_subquery表示子查询中使用了UNIQUE或者PRIMARYKEY;
index_subquery表示子查询中使用了普通索引;
range表示查询语句中给出了查询范围;
index表示对表中的索引进行了完整的扫描;
possible_keys:
表示查询中可能使用的索引;
key:
表示查询使用到的索引;
key_len:
表示索引字段的长度;
ref:
表示使用哪个列或常数与索引一起来查询记录;
rows:
表示查询的行数;
Extra:
表示查询过程的附件信息。
DESCRIBE语句的使用方法与EXPLAIN语句是一样的。
这两者的分析结果也是一样的。
DESCRIBE语句的语法形式如下:
15DESCRIBESELECT语句;
DESCRIBE可以缩写成DESC。
18.2.2索引对查询速度的影响
索引可以快速的定位表中的某条记录。
使用索引可以提高数据库查询的速度,从而提高数据库的性能。
本小节将为读者介绍索引对查询速度的影响。
如果查询时不使用索引,查询语句将查询表中的所有字段。
这样查询的速度会很慢。
如果使用索引进行查询,查询语句只查询索引字段。
这样可以减少查询的记录数,达到提高查询速度的目的。
【示例18-2】下面是查询语句中不使用索引和使用索引的对比。
现在分析未使用索引时的查询情况,EXPLAIN语句执行如下:
1mysql>
EXPLAINSELECT*FROMstudentWHEREname='
张三'
\G
2***************************1.row***************************
3id:
4select_type:
5table:
6type:
7possible_keys:
8key:
9key_len:
10ref:
11rows:
12Extra:
Usingwhere
131rowinset(0.00sec)
结果显示,rows参数的值为6。
这说明这个查询语句查询了6条记录。
现在在name字段上建立一个名为index_name的索引。
CREATE语句执行如下:
14mysql>
CREATEINDEXindex_nameONstudent(name);
15QueryOK,6rowsaffected(0.09sec)
16Records:
6Duplicates:
0Warnings:
0
现在,name字段上已经有索引了,然后再分析查询语句的执行情况。
EXPLAIN语句执行如下:
17mysql>
18***************************1.row***************************
19id:
20select_type:
21table:
22type:
ref
23possible_keys:
index_name
24key:
25key_len:
22
26ref:
const
27rows:
28Extra:
291rowinset(0.01sec)
结果显示,rows参数的值为1。
这表示这个查询语句只查询了一条记录,其查询速度自然比查询6条记录快。
而且possible_keys和key的值都是index_name,这说明查询时使用了index_name索引。
18.2.3使用索引查询
索引可以提高查询的速度。
但是有些时候即使查询时使用的是索引,但索引并没有起作用。
本小节将向读者介绍索引的使用。
1.查询语句中使用LIKE关键字
在查询语句中使用LIKE关键字进行查询时,如果匹配字符串的第一个字符为"
%"
时,索引不会被使用。
如果"
不是在第一个位置,索引就会被使用。
【示例18-3】下面查询语句中使用LIKE关键字,并且匹配的字符串中含有"
符号。
EXPLAINSELECT*FROMstudentWHEREnameLIKE'
%四'
\G
14
15mysql>
李%'
16***************************1.row***************************
17id:
18select_type:
19table:
20type:
range
21possible_keys:
22key:
23key_len:
24ref:
25rows:
26Extra:
271rowinset(0.00sec)
第一个查询语句执行后,rows参数的值为6,表示这次查询过程中查询了6条记录;
第二个查询语句执行后,rows参数的值为1,表示这次查询过程只查询一条记录。
同样是使用name字段进行查询,第一个查询语句没有使用索引,而第二个查询语句使用了索引index_name。
因为第一个查询语句的LIKE关键字后的字符串以"
开头。
2.查询语句中使用多列索引
多列索引是在表的多个字段上创建一个索引。
只有查询条件中使用了这些字段中第一个字段时,索引才会被使用。
【示例18-4】下面在birth和department两个字段上创建多列索引,然后验证多列索引的使用情况。
28mysql>
CREATEINDEXindex_birth_departmentONstudent(birth,department);
29QueryOK,6rowsaffected(0.01sec)
30Records:
31
32mysql>
EXPLAINSELECT*FROMstudentWHEREbirth=1991\G
33***************************1.row***************************
34id:
35select_type:
36table:
37type:
38possible_keys:
index_birth_department
39key:
40key_len:
2
41ref:
42rows:
43Extra:
441rowinset(0.00sec)
45
46mysql>
EXPLAINSELECT*FROMstudentWHEREdepartment='
英语系'
47***************************1.row***************************
48id:
49select_type:
50table:
51type:
52possible_keys:
53key:
54key_len:
55ref:
56rows:
57Extra:
581rowinset(0.00sec)
在birth字段和department字段上创建一个多列索引。
第一个查询语句的查询条件使用了birth字段,分析结果显示rows参数的值为1。
而且显示查询过程中使用了index_birth_department索引。
第二个查询语句的查询条件使用了department字段,结果显示rows参数的值为6。
而且key参数的值为NULL,这说明第二个查询语句没有使用索引。
因为name字段是多列索引的第一个字段,只有查询条件中使用了name字段才会使index_name_department索引起作用。
3.查询语句中使用OR关键字
查询语句只有OR关键字时,如果OR前后的两个条件的列都是索引时,查询中将使用索引。
如果OR前后有一个条件的列不是索引,那么查询中将不使用索引。
【示例18-5】下面演示OR关键字的使用。
59mysql>
orsex='
女'
60***************************1.row***************************
61id:
62select_type:
63table:
64type:
65possible_keys:
66key:
67key_len:
68ref:
69rows:
70Extra:
711rowinset(0.00sec)
72
73mysql>
orid=3\G
74***************************1.row***************************
75id:
76select_type:
77table:
78type:
index_merge
79possible_keys:
PRIMARY,id,index_name
80key:
index_name,PRIMARY
81key_len:
22,4
82ref:
83rows:
84Extra:
Usingunion(index_name,PRIMARY);
851rowinset(0.00sec)
第一个查询语句没有使用索引,因为sex字段上没有索引;
第二个查询语句使用了index_name和PRIMARY这两个索引,因为name字段和id字段上都有索引。
技巧:
使用索引查询记录时,一定要注意索引的使用情况。
例如,LIKE关键字配置的字符串不能以"
开头;
使用多列索引时,查询条件必须要使用这个索引的第一个字段;
使用OR关键字时,OR关键字连接的所有条件都必须使用索引。
18.2.4优化子查询
很多查询中需要使用子查询。
子查询可以使查询语句很灵活,但子查询的执行效率不高。
子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表。
然后外层查询语句再临时表中查询记录。
查询完毕后,MySQL需要撤销这些临时表。
因此,子查询的速度会受到一定的影响。
如果查询的数据量比较大,这种影响就会随之增大。
在MySQL中可以使用连接查询来替代子查询。
连接查询不需要建立临时表,其速度比子查询要快。
18.3优化数据库结构
数据库结构是否合理,需要考虑是否存在冗余、对表的查询和更新的速度、表中字段的数据类型是否合理等多方面的内容。
本节将为读者介绍优化数据库结构的方法。
18.3.1将字段很多的表分解成多个表
有些表在设计时设置了很多的字段。
这个表中有些字段的使用频率很低。
当这个表的数据量很大时,查询数据的速度就会很慢。
本小节将为读者介绍优化这种表的方法。
对于这种字段特别多且有些字段的使用频率很低的表,可以将其分解成多个表。
【示例18-6】下面的学生表中有很多字段,其中在extra字段中存储着学生的备注信息。
有些备注信息的内容特别多。
但是,备注信息很少使用。
这样就可以分解出另外一个表。
将这个取名叫student_extra。
表中存储两个字段,分别为id和extra。
其中,id字段为学生的学号,extra字段存储备注信息。
student_extra表的结构如下:
DESCstudent_extra;
2+-------+-----------+--------+-------+--------------+---------+
3|Field|Type|Null|Key|Default|Extra|
4+-------+-----------+--------+-------+--------------+---------+
5|id|int(11)|NO|PRI|NULL||
6|extra|text|YES||NULL||
7+-------+-----------+--------+-------+--------------+---------+
82rowsinset(0.00sec)
如果需要查询某个学生的备注信息,可以用学号(id)来查询。
如果需要将学生的学籍信息与备注信息同时显示时,可以将student表和student_extra表进行联表查询,查询语句如下:
9SELECT*FROMstudent,student_extraWHEREstudent.id=student_extra.id;
通过这种分解,可以提高student表的查询效率。
因此,遇到这种字段很多,而且有些字段使用不频繁的,可以通过这种分解的方式来优化数据库的性能。
18.3.2增加中间表
有时需要经常查询某两个表中的几个字段。
如果经常进行联表查询,会降低MySQL数据库的查询速度。
对于这种情况,可以建立中间表来提高查询速度。
本小节将为读者介绍增加中间表的方法。
先分析经常需要同时查询哪几个表中的哪些字段。
然后将这些字段建立一个中间表,并将原来那几个表的数据插入到中间表中,之后就可以使用中间表来进行查询和统计了。
【示例18-7】下面有个学生表student和分数表score。
这两个表的结构如下:
DESCstudent;
2+----------------+----------------+---------+------
-+-------------+----------+
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+----------------+----------------+---------+------
126rowsinset(0.03sec)
13
DESCscore;
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|
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MySQL入门很简单 第18章 性能优化 MySQL 入门 简单 18 性能 优化