＜MySQL入门到精通＞阅读笔记之性能优化

性能优化简介

MySQL 性能优化就是通过合理安排资源， 调整系统参数使 MySQL 运行更快、 更节省资
源。 MySQL 性能优化包括查询速度优化、 数据库结构优化、 MySQL 服务器优化等。

优化查询

查询是数据库中最频繁的操作， 提高查询速度可以有效地提高 MySQL 数据库的性能

分析查询语句

MySQL 中提供了 EXPLAIN 语句和 DESCRIBE 语句， 用来分析查询语句。

EXPLAIN SELECT * FROM books

DESC SELECT * FROM books

select_type 指定使用的SELECT查询类型 这里值为SIMPLE 表示简单的SELECT 不适用UNION或子查询 其他可能的取值有 PRIMARY UNION SUBQUERY等

table 指定数据库读取的数据表名字 按被读取的先后顺序排列

type 指定了本数据表与其他数据表之间的关联关系 可能取值有system const eq_ref ref range index 和 All

possible_keys 给出MYSQL在搜索数据记录时可选用的各个索引

key 是MYSQL实际选用的索引

key_len 索引按字节计算的长度 key_len越小 越快

ref 关联关系中另一个数据表的数据列名字

rows MYSQL在执行这个查询时预计会从这个数据表里读出的数据行个数

extra 提供了关联操作有关的信息

索引对查询速度的影响

在查询过程中使用索引 会提高数据库查询效率 应用索引来查询数据库中的内容 可以减少查询的记录数 达到优化的目的

EXPLAIN SELECT * FROM books  WHERE NAME = 'lear1'

表格字段row下为83 说明数据库存在的83条数据都被查了个遍 在数据量小的时候不会有太大影响 但数据库存储庞大的数据资料时 搜索一条数据需要遍历整个表 会耗费很多时间

给需要查询得字段添加索引

ALTER TABLE admin_method ADD INDEX index_name (`name`)

EXPLAIN SELECT * FROM books  WHERE NAME = 'lear1'

从上述结果可以看出 创建得索引使访问行数从83减少到1 所以索引不但会自动优化查询效率 还会降低服务器的开销

使用索引查询

应用LIKE关键字优化索引

EXPLAIN SELECT * FROM admin_method WHERE NAME LIKE  '%I'

从上图可以看出 rows为83 说明并没有起到优化作用 这是因为如果匹配字符串中第一个字符串为 % 时 索引不会被使用 如果%不是第一个 则会被正常使用

EXPLAIN SELECT * FROM admin_method WHERE NAME LIKE  'I%'

查询语句中使用多列索引

CREATE INDEX admin_method_index ON `admin_method`(NAME,request)

在使用request字段时 索引不能被正常使用

EXPLAIN SELECT * FROM admin_method WHERE request = 'G%'

使用第一字段name时 才能被正常使用

EXPLAIN SELECT * FROM admin_method WHERE request = 'G%' AND NAME = "I%"

查询语句中使用OR关键字

查询语句中只要包含OR关键字时 两个字段必须同为索引

// method 不是索引字段 request为索引字段
EXPLAIN SELECT * FROM admin_method WHERE request = 'G%' OR method = "I%"

查询出来rows为83 说明索引没生效

EXPLAIN SELECT * FROM admin_method WHERE request = 'G%' OR NAME = "I%"

优化子查询

Mysql从4.1 开始支持子查询 子查询可以一次完成很多逻辑 但执行效率不高 执行子查询时 MYSQL需要为内层查询语句的查询结果建一个临时表 查询完毕后再撤销这些临时表 因此子查询的速度会受到一定的影响 如果查询数据量大 这种影响会随之增大
在MYSQL中 可以使用连接查询来替代子查询 连接查询不需要建立临时表 其速度比子查询要快 如果查询中使用索引得话 性能会更好

优化数据库结构

数据库结构是否合理 需要考虑是否存在冗余 对表的查询和更新速度 表中的字段数据类型是否合理等多方面内容

将字段很多的表分解成多个表

有些表设计时使用了很多字段 表中的字段使用频率很低 当表中的数据量大时 查询速度就会变慢 对于使用频率很低

假如上面表中的school字段使用频率很低 我们就可以分解出另外一个表

id 为学生得Id
如果需要查询某个学生的学校信息可以进行关联查询

SELECT * FROM `student_school` s,`student` st WHERE s.id = st.id 

优化插入记录的速度

禁用索引和开启索引

插入记录时 MYSQL 会根据表的索引对插入的记录进行排序 如果插入大量数据 这些排序会降低插入记录的速度 为了解决这种情况 在插入记录前应该先禁用索引

	// 禁用
	ALTER TABLE `admin_method` DISABLE  KEYS;
	// 开启
	ALTER TABLE admin_method ENABLE KEYS;

禁用唯一性检查

插入数据时 MYSQL会对插入记录进行校验 这种校验也会降低插入记录的速度 可以在插入前先关闭 插入后在开启

	// 关闭
    SET UNIQUE_CHECKS=0
    // 开启
	SET UNIQUE_CHECKS=1

优化INSERT 语句

插入多条数据时 可以采取两种INSERT语句的方式 第一种是插入多条记录

	INSERT INTO books VALUES(341,'leart1',1)
	,(12,'leart1',1)
	,(13,'leart1',1)
	,(14,'leart1',1)
	,(145,'leart1',1)
	,(16,'leart1',1)
	,(77,'leart1',1)
	,(771,'leart1',1);

第二种一次只插一条

	INSERT INTO books VALUES(341,'leart1',1)

第一种减少了数据库之间的连接操作 其速度要比第二种快

分析表检查表优化表

分析表主要作用是分析关键字的分布 检查表的主要作用是检查表是否存在错误 优化表的作用是消除删除或者更新造成的空间浪费

分析表

ANALYZE TABLE books

table表示表名称
op表示执行的操作 analyze表示分析操作 check表示检查查找 optimize 表示优化操作
msg_type 表示信息类型 其显示的值通常是状态 警告 错误 和信息这四者之一
msg_text 显示信息

检查表

	CHECK TABLE books 
	CHECK TABLE 表名1，[表名2],[option]

检查表能够检查InnoDB和MyISAM 类型的表是否存在错误

option有5个参数 QUICK,FAST,CHANGED,MEDIUM和EXETENDED
这5个参数的执行效率依次降低 option选项只对MyISAM类型的表有效
CHECK TABLE 语句在执行的过程中也会给 表加上读锁

优化表

OPTIMIZE TABLE books

Mysql中使用OPTIMIZE TABLE语句可以消除删除和更新造成的磁盘碎片 从而减少空间的浪费
OPTIMIZE TABLE执行过程中也会给表加上读锁 该语句对InnoDB和MyISAM都有效 如果一个表
使用了TEXT或者BLOB这样的数据类型 那么更新 删除等操作会造成磁盘碎片的浪费 因为更新和删除操作后 以前分配的磁盘空间不会自动收回 使用OPTIMIZE TABLE可以将这些磁盘碎片整理出来 以便以后再利用

查询高速缓存

在MYSQL中 用户通过SELECT语句查询时 该操作会保存到一个特殊的高级缓存中 首次查询后 当用户再次做相同查询时 MYSQL即可从高速缓存中检索结果

检查高速缓存是否开启

	SHOW VARIABLES LIKE '%query_cache%'

have_query_cache 表明服务器在默认安装条件西 是否已经配置了查询高速缓存
query_cache_size 高速缓存分配空间 单位为MB
query_cache_type 判断高速缓存是否时开启状态 0/OFF 是关闭 1/ON为开启 2/DEMAND 根据需要运行有SQL_CACHE选项的SELECT语句 提供高速缓存

使用高速缓存

	SELECT SQL_CACHE * FROM books 

// 不使用高速缓存
	SELECT SQL_NO_CACHE * FROM books