《深入浅出Mysql数据库开发、优化与管理维护》笔记
[b]1、优化sql的一般步骤[/b]
[b]1.1 使用 show status 命令了解各种sql的执行频率[/b]
进入mysql控制台执行
或者在操作系统的终端执行
mysqladmin extended-status
可以通过like语句来查询一些特定的内容
比如 show global status like "Com_%";就查看上次启动以来的每个Com_xxx语句执行的次数
这些参数会都mysql所有存储引擎的表操作都做记录
另外还有专门针对某一些存储引擎的,比如:
通过上面这些数据的比较可以判断数据库是写为主,还是查询为主
关于事务的信息 可以通过 Com_commit 和 Com_rollback 来了解提交和回滚的情况。如果回滚操作非常的频繁,说明应用编写存在很大的问题
另外几个比较重要的,可以展示数据库基本情况的句子:
[b]1.2 定位执行效率比较低的sql语句[/b]
有两种方法:
一、通过慢查询日志来定位。用--log-slow-queries[=filename]选项启动时,mysqld写一个包含所有执行时间超过long_query_time秒得sql语句的日志文件
二、使用show processlist命令查看当前MySQL在进行的线程,包括线程的状态,是否锁表等,可以实时的查看sql的执行情况,同时对一些锁表操作进行优化。
[b]1.3 通过Explain分析低效SQL的执行计划[/b]
再通过上面步骤,查到低效率的sql语句之后,可以通过 explain和desc命令获取mysql如何执行select语句的信息,包括select执行过程中表如何连接和连接的顺序。
比如
这个显示的结果是横向的,不够友好,在后面 追加参数“\G”可以每一项一列信息的显示
上面每一列的含义说明
[b]2 索引问题[/b]
[b]2.1 索引的存储分类[/b]
MyISAM存储索引的表的数据和索引是自动分开存储的,各自是独立的文件
InnoDB存储引擎的表的数据和索引是存储在同一个表的空间里面,但是可以有多个文件组成
Mysql中的存储类型木匾只有两种,BTREE和HASH,具体情况和表的存储引擎有关。
MyISAM和InnoDb存储引擎都支持BTREE索引,
MEMORY/HEAP存储引擎可以支持HASH和BTREE索引
mysql不支持函数索引,但是能对队列的前面的某一部分进行索引,例如name字段,可以只取name的前四个字符来进行索引。
[b] 2.2 使用索引[/b]
对相关列使用索引是提高select性能的最佳途径
使用索引的条件:a、查询条件中有索引关键字,b、多列索引只有查询条件使用了多列关键字最左边的前缀时,才可以使用索引,否则将不能使用索引。
[b]2.2.1 使用索引[/b]
一下情况中会使用到索引:
(1) 多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用
(2) 对于使用like查询,后面如果是常量,只有%号不在第一个字符时,索引才可能被用到 比如 like "%3" 不会用索引, like "3%"就会走索引
(3) 对大文本进行搜索的时候,使用全文索引,而不是使用 like '%...%'
(4) 如果列名是索引,使用column_name is null将使用索引,
例如: select * from aaa where name is null(name是索引列)
[b]2.2.2 存在索引但不使用索引[/b]
在下列情况下,虽然mysql存在索引,但是并不会使用到索引
(1)如果Mysql估计使用索引比全表扫描更慢,则不使用索引。例如如果列key_1 均匀分布在1和100之间,那么查询 select * from table where key_1 > 1 and key_1 < 90;
(2)如果使用MEMPRY/HEAP表并且,where条件中不使用“=”进行索引列,那么不会用到索引。heap表只有在使用“=”的时候,才使用索引
(3)用or隔开的条件,如果or前面的列中有索引,而后面的列中没有索引,那么涉及的索引都不会被用到(or中有一个条件中的列没有索引就用不到索引)
(4)如果不是索引列的第一部分(复合索引的第一部分)
(5)如果like是%开始的
(6)如果列类型是字符串,那么一定记得在where条件中把字符常量值用引号引起来,否则即便是这个列上有索引,也不用用到(比如name字段是字符串的,却写了name=123。要改成“123”)
[b] 2.3 查看索引使用情况[/b]
如果索引正在工作,Handler_read_key的值将很高,这个值代表了一个行被索引值读的次数,很低得知表明增加索引得到的性能改善不高
Handler_read_rnd_next的值高则意味着查询的效率低效,并且应该建立索引补救。它的含义是:数据文件中读下一行的请求数。如果正在进行大量的表扫描,Handle_read_rnd_next的值较高,则通常说明表索引不正确或者写入的查询没有利用索引
查看方法:show status like 'Handler_read%'
[b]3 简单的优化方法[/b]
[b]3.1 定期分析表和检查表[/b]
分析表的语法:
analyze [local | no_write_to_binlog] table tab1_name [, ta1_name] ...
用于分析和存储表的关键字分布
分析结果可以使得系统得到更准确地统计信息,使得sql能够正确的执行计划。
如果用户感觉实际执行计划并不是与预期的执行计划,执行一次分析表可能会解决问题。
在分析期间,使用一个读取锁对表进行锁定,这对于MyISAM,BDB和InnoDb表有作用。对于MyISAM表,与使用myisamchk -a 相当
例如:
analyze table user;
检查表的语法如下:
check table tab1_name [,tab1_name] ... [option] .. option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
作用是:检查一个或者多个表中是否有错误。他对MyISAM和InnoDB表有作用,对于MyISAM表,关键字统计数据被更新。例如:
check table也可以检查视图是否有错误,例如:在视图定义中被引用的表不存在
[b] 3.2 定期优化表[/b]
语法:
optimize [local | no_write_to_binlog] table tab1_name [, tab1_name] ...
适用范围:
a、删除了表的一部分
b、对含有可变长度行的表(varchar,blob,text列的表)进行了很多更改。
这个命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费。但是这个命令只对MyISAM,BDB和InnoDb表起作用
[b]4 常用SQL的优化[/b]
[b]4.1 大批量插入数据[/b]
当用load命令导入数据的时候,适当的设置可以提高导入的速度
[b]4.1.1 对于MyISAM存储引擎的表方法如下:[/b]
上面操作是打开或者关闭MyISAM表不是唯一索引的更新
ps:导入非空数据表的时候,上面方法很有效,但是导入空表的时候,索引是数据导入完毕之后才去创建的,所以没有影响
[b]4.2.2 对InnoDB表的数据导入[/b]
(1)InnoDB类型的表是按照主键的顺序来存储的,所以导入数据按照主键的顺序排列,可以有效的提高导入的效率
(2)在导入数据之前,关闭唯一性校验,set unique_checks=0 ,在导入结束之后设置为 1 开启唯一性校验,可以提高导入效率
(3)如果应用使用自动提交的方式,建议在导入数据的时候执行 set autocomment=0,关闭自动提交,导入结束之后再设置为1,打开自动提交
[b] 4.2 优化insert语句[/b]
(1)如果是从同一客户那里插入很多航,尽量使用多个值表的insert语句,这种方式将大大的缩短客户端与数据库之间的链接,关闭等资源的消耗,使得效率比分开执行的单个insert语句快
例如:insert to test values(1,2),(1,3),(1,4)....
(2)如果从不同的客户中杀入多行,能通过使用insert delayed语句得到更高的速度。delayed是让insert语句立马执行,四十数据都被放在内存的队列中,并没有真正的写入磁盘,这比每一条数据分别插入快得多。low_priority 刚好想法,是所有其他用户对表的读写完成后才进行插入
(3)将索引文件和数据文件分在不同的磁盘上存放
(4)如果进行批量插入可以增加 bulk_insert_buffer_size 变量值的方法来提高速度,但是这只能对MyIsAM表使用
(5)当一个文本文件装载一个表时,使用 load file insert。这比通常的sql语句快20倍
[b] 4.3 优化group by语句[/b]
如果查询包括group by但是用户想避免白须结果的小号,可以指定order by null禁止排序
例如:
[b]4.4 优化order by语句[/b]
可以使用索引来满足一个order by语句
条件:
where条件和order by使用相同的索引,并且order by的顺序和索引顺序相同,并且order by的字段都是升序或者降序
但是以下情况不可以
[b] 4.5 优化嵌套查询[/b]
有些情况下,子查询可以被更加有效的join查询代替
[b] 4.6 优化OR条件[/b]
or的每一个条件都使用索引
但是如果or的几个条件正好是复合索引的元素,则不能起到加速的效果
[b]4.7 使用SQL提示[/b]
在sql中加入一些人为的提示来达到优化操作的目的
例如:
select sql_buffer_results * from
这个语句将强行MySql生成一个临时的结果集,只要临时结果集生成之后,所有表上的锁都会被释放。
这能解决遇到表锁问题时,或者要花长时间将结果传给客户端时有所帮助,因为这样可以尽快的释放资源
(1)use index
在查询语句中表名的后面,添加use index来提供希望mysql去参考的索引列表,就可以让mysql不再考虑其他可用的索引
(2)ignore index
如果用户只是单纯的想让mysql忽略一个多个索引,则可以使用ignore index作为hint
(3)force index
强制MySQL使用一个特定的索引,可在查询中使用
[b]1、优化sql的一般步骤[/b]
[b]1.1 使用 show status 命令了解各种sql的执行频率[/b]
进入mysql控制台执行
show session status 查询当前连接的统计结果
show global status 查询自数据库上次启动至今的统计结果
或者在操作系统的终端执行
mysqladmin extended-status
可以通过like语句来查询一些特定的内容
比如 show global status like "Com_%";就查看上次启动以来的每个Com_xxx语句执行的次数
Com_select: 执行select查询的次数
Com_insert: 执行insert的次数
Com_update: 执行update的次数
Com_delete: 执行delete的次数
这些参数会都mysql所有存储引擎的表操作都做记录
另外还有专门针对某一些存储引擎的,比如:
Innodb_rows_read
Innodb_rows_insert
Innodb_rows_update
Innobd_rows_delete
通过上面这些数据的比较可以判断数据库是写为主,还是查询为主
关于事务的信息 可以通过 Com_commit 和 Com_rollback 来了解提交和回滚的情况。如果回滚操作非常的频繁,说明应用编写存在很大的问题
另外几个比较重要的,可以展示数据库基本情况的句子:
Connections: 试图连接Mysql数据库的次数
Uptime: 服务器工作时间
Slow_queries: 慢查询的次数
[b]1.2 定位执行效率比较低的sql语句[/b]
有两种方法:
一、通过慢查询日志来定位。用--log-slow-queries[=filename]选项启动时,mysqld写一个包含所有执行时间超过long_query_time秒得sql语句的日志文件
二、使用show processlist命令查看当前MySQL在进行的线程,包括线程的状态,是否锁表等,可以实时的查看sql的执行情况,同时对一些锁表操作进行优化。
[b]1.3 通过Explain分析低效SQL的执行计划[/b]
再通过上面步骤,查到低效率的sql语句之后,可以通过 explain和desc命令获取mysql如何执行select语句的信息,包括select执行过程中表如何连接和连接的顺序。
比如
explain select * from sum(money) from sales a, company b where a.company_id = b.id and a.year = 2006
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| 1 | SIMPLE | user | system | NULL | NULL | NULL | NULL | 0 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set (0.00 sec)
这个显示的结果是横向的,不够友好,在后面 追加参数“\G”可以每一项一列信息的显示
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 0
Extra: const row not found
1 row in set (0.00 sec)
ERROR:
No query specified
上面每一列的含义说明
select_type: 表示select的类型(SIMPLE=>简单表,不使用表连接或者子查询,PRIMARY=>主查询,外层的查询,UNION=>UNION中的第二个或者后面的查询语句,SUBQUERY=>子查询中的第一个select)
table; 输出结果集的表
possible_keys: 表示查询时,可能使用的索引
key: 表示实际使用的索引
key_len: 索引字段的长度
rows; 扫描的行的数量
Extra: 执行情况的说明和描述
type: 表示表的连接类型,性能有好到差的链接类型为:
system=>只有一行,也就是常量表,
const=>单表中最多有一个匹配行,例如primary key或者unique index,
eq_ref=>对于前面的每一行,在此表中只查询一条记录,简单来说,就是多表连接中使用primary key或者unique index,
ref=>与eq_ref类似,区别在于不是使用primay key或者unique index而是使用普通的索引,
ref_or_null=>与ref类似,区别在于条件中包含对null的查询,
index_merge=>索引合并优化,
unique_subquery=>in的后面是一个查询主键字段的子查询,
index_subquery=>与unique_subquery类似,区别在于in的后面是查询非唯一索引字段的子查询,
range=>单表中的查询范围,
index=>对于前面的每一行,都通过查询索引来得到数据,
all=>对于前面的每一行,都通过全表扫描来得到数据
[b]2 索引问题[/b]
[b]2.1 索引的存储分类[/b]
MyISAM存储索引的表的数据和索引是自动分开存储的,各自是独立的文件
InnoDB存储引擎的表的数据和索引是存储在同一个表的空间里面,但是可以有多个文件组成
Mysql中的存储类型木匾只有两种,BTREE和HASH,具体情况和表的存储引擎有关。
MyISAM和InnoDb存储引擎都支持BTREE索引,
MEMORY/HEAP存储引擎可以支持HASH和BTREE索引
mysql不支持函数索引,但是能对队列的前面的某一部分进行索引,例如name字段,可以只取name的前四个字符来进行索引。
[b] 2.2 使用索引[/b]
对相关列使用索引是提高select性能的最佳途径
使用索引的条件:a、查询条件中有索引关键字,b、多列索引只有查询条件使用了多列关键字最左边的前缀时,才可以使用索引,否则将不能使用索引。
[b]2.2.1 使用索引[/b]
一下情况中会使用到索引:
(1) 多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用
(2) 对于使用like查询,后面如果是常量,只有%号不在第一个字符时,索引才可能被用到 比如 like "%3" 不会用索引, like "3%"就会走索引
(3) 对大文本进行搜索的时候,使用全文索引,而不是使用 like '%...%'
(4) 如果列名是索引,使用column_name is null将使用索引,
例如: select * from aaa where name is null(name是索引列)
[b]2.2.2 存在索引但不使用索引[/b]
在下列情况下,虽然mysql存在索引,但是并不会使用到索引
(1)如果Mysql估计使用索引比全表扫描更慢,则不使用索引。例如如果列key_1 均匀分布在1和100之间,那么查询 select * from table where key_1 > 1 and key_1 < 90;
(2)如果使用MEMPRY/HEAP表并且,where条件中不使用“=”进行索引列,那么不会用到索引。heap表只有在使用“=”的时候,才使用索引
(3)用or隔开的条件,如果or前面的列中有索引,而后面的列中没有索引,那么涉及的索引都不会被用到(or中有一个条件中的列没有索引就用不到索引)
(4)如果不是索引列的第一部分(复合索引的第一部分)
(5)如果like是%开始的
(6)如果列类型是字符串,那么一定记得在where条件中把字符常量值用引号引起来,否则即便是这个列上有索引,也不用用到(比如name字段是字符串的,却写了name=123。要改成“123”)
[b] 2.3 查看索引使用情况[/b]
如果索引正在工作,Handler_read_key的值将很高,这个值代表了一个行被索引值读的次数,很低得知表明增加索引得到的性能改善不高
Handler_read_rnd_next的值高则意味着查询的效率低效,并且应该建立索引补救。它的含义是:数据文件中读下一行的请求数。如果正在进行大量的表扫描,Handle_read_rnd_next的值较高,则通常说明表索引不正确或者写入的查询没有利用索引
查看方法:show status like 'Handler_read%'
[b]3 简单的优化方法[/b]
[b]3.1 定期分析表和检查表[/b]
分析表的语法:
analyze [local | no_write_to_binlog] table tab1_name [, ta1_name] ...
用于分析和存储表的关键字分布
分析结果可以使得系统得到更准确地统计信息,使得sql能够正确的执行计划。
如果用户感觉实际执行计划并不是与预期的执行计划,执行一次分析表可能会解决问题。
在分析期间,使用一个读取锁对表进行锁定,这对于MyISAM,BDB和InnoDb表有作用。对于MyISAM表,与使用myisamchk -a 相当
例如:
analyze table user;
mysql> analyze table user;
+------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+---------+----------+----------+
| mysql.user | analyze | status | OK |
+------------+---------+----------+----------+
1 row in set (0.05 sec)
检查表的语法如下:
check table tab1_name [,tab1_name] ... [option] .. option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
作用是:检查一个或者多个表中是否有错误。他对MyISAM和InnoDB表有作用,对于MyISAM表,关键字统计数据被更新。例如:
check table user;
mysql> check table user;
+------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+-------+----------+----------+
| mysql.user | check | status | OK |
+------------+-------+----------+----------+
1 row in set (0.00 sec)
check table也可以检查视图是否有错误,例如:在视图定义中被引用的表不存在
[b] 3.2 定期优化表[/b]
语法:
optimize [local | no_write_to_binlog] table tab1_name [, tab1_name] ...
适用范围:
a、删除了表的一部分
b、对含有可变长度行的表(varchar,blob,text列的表)进行了很多更改。
这个命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费。但是这个命令只对MyISAM,BDB和InnoDb表起作用
mysql> optimize table user;
+------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+----------+----------+----------+
| mysql.user | optimize | status | OK |
+------------+----------+----------+----------+
1 row in set (0.01 sec)
[b]4 常用SQL的优化[/b]
[b]4.1 大批量插入数据[/b]
当用load命令导入数据的时候,适当的设置可以提高导入的速度
[b]4.1.1 对于MyISAM存储引擎的表方法如下:[/b]
alter table tab_name disable keys;
load the data
alter table tab_name enable keys;
上面操作是打开或者关闭MyISAM表不是唯一索引的更新
ps:导入非空数据表的时候,上面方法很有效,但是导入空表的时候,索引是数据导入完毕之后才去创建的,所以没有影响
[b]4.2.2 对InnoDB表的数据导入[/b]
(1)InnoDB类型的表是按照主键的顺序来存储的,所以导入数据按照主键的顺序排列,可以有效的提高导入的效率
(2)在导入数据之前,关闭唯一性校验,set unique_checks=0 ,在导入结束之后设置为 1 开启唯一性校验,可以提高导入效率
(3)如果应用使用自动提交的方式,建议在导入数据的时候执行 set autocomment=0,关闭自动提交,导入结束之后再设置为1,打开自动提交
[b] 4.2 优化insert语句[/b]
(1)如果是从同一客户那里插入很多航,尽量使用多个值表的insert语句,这种方式将大大的缩短客户端与数据库之间的链接,关闭等资源的消耗,使得效率比分开执行的单个insert语句快
例如:insert to test values(1,2),(1,3),(1,4)....
(2)如果从不同的客户中杀入多行,能通过使用insert delayed语句得到更高的速度。delayed是让insert语句立马执行,四十数据都被放在内存的队列中,并没有真正的写入磁盘,这比每一条数据分别插入快得多。low_priority 刚好想法,是所有其他用户对表的读写完成后才进行插入
(3)将索引文件和数据文件分在不同的磁盘上存放
(4)如果进行批量插入可以增加 bulk_insert_buffer_size 变量值的方法来提高速度,但是这只能对MyIsAM表使用
(5)当一个文本文件装载一个表时,使用 load file insert。这比通常的sql语句快20倍
[b] 4.3 优化group by语句[/b]
如果查询包括group by但是用户想避免白须结果的小号,可以指定order by null禁止排序
例如:
select id, sum(money) from sale2 group by id order by null
[b]4.4 优化order by语句[/b]
可以使用索引来满足一个order by语句
条件:
where条件和order by使用相同的索引,并且order by的顺序和索引顺序相同,并且order by的字段都是升序或者降序
比如: order by key1,key2
where key1=123 order by key1 desc, key2 desc
order by key1 desc, key2 desc
但是以下情况不可以
order by key1 asc, key2 desc 混合使用ASC和DESC
where key2 = 1 , order by key1 查询的关键字和排序的不一样
order by key1,key2 对不同的关键字使用排序
[b] 4.5 优化嵌套查询[/b]
有些情况下,子查询可以被更加有效的join查询代替
比如 select * from a where b_id not in(select id from b)
换成 select * from a left join b on a.b_id = b.id where a.b_id is not null
[b] 4.6 优化OR条件[/b]
or的每一个条件都使用索引
但是如果or的几个条件正好是复合索引的元素,则不能起到加速的效果
[b]4.7 使用SQL提示[/b]
在sql中加入一些人为的提示来达到优化操作的目的
例如:
select sql_buffer_results * from
这个语句将强行MySql生成一个临时的结果集,只要临时结果集生成之后,所有表上的锁都会被释放。
这能解决遇到表锁问题时,或者要花长时间将结果传给客户端时有所帮助,因为这样可以尽快的释放资源
(1)use index
在查询语句中表名的后面,添加use index来提供希望mysql去参考的索引列表,就可以让mysql不再考虑其他可用的索引
select * from a use index(ind_a_id) where id = 1
(2)ignore index
如果用户只是单纯的想让mysql忽略一个多个索引,则可以使用ignore index作为hint
select * from a ignore index(ind_a_id) where id = 1
(3)force index
强制MySQL使用一个特定的索引,可在查询中使用