mysql性能优化
1.MySQL性能优化目的和优化项目
- 一方面是找出系统的瓶颈，提高数据库整体的性能
- 另一方面需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。
性能优化包含：查询速度优化、数据库结构优化、MySQL服务器优化等。
2.优化查询
(1)分析查询语句-执行计划
explain [extended] select select_options
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
id select 识别符(查询序列号)
查询顺序标识
如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | NULL |
| 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
特别的：如果使用union连接气值可能为null
select_type
查询类型
SIMPLE 简单查询
PRIMARY 最外层查询
SUBQUERY 映射为子查询
DERIVED 导出表的select(from子句的子查询)
DEPENDENT SUBQUERY 子查询，取决于外面的查询
UNION 联合
UNION RESULT 使用联合的结果
...
table
正在访问的表名
type
查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
ALL 全表扫描，对于数据表从头到尾找一遍
select * from tb1;
特别的：如果有limit限制，则找到之后就不在继续向下扫描
select * from tb1 where email = 'seven@live.com'
select * from tb1 where email = 'seven@live.com' limit 1;
虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
INDEX 全索引扫描，对索引从头到尾找一遍
select nid from tb1;
RANGE 对索引列进行范围查找
select * from tb1 where name < 'alex';
PS:
between and
in
> >= < <= 操作
注意：!= 和 > 符号
INDEX_MERGE 合并索引，使用多个单列索引搜索
select * from tb1 where name = 'alex' or nid in (11,22,33);
REF 根据索引查找一个或多个值
select * from tb1 where name = 'seven';
EQ_REF 连接时使用primary key 或 unique类型
select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
CONST 常量
表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
select nid from tb1 where nid = 2 ;
SYSTEM 系统
表仅有一行(=系统表)。这是const联接类型的一个特例。
select * from (select nid from tb1 where nid = 1) as A;
possible_keys
可能使用的索引
key
真实使用的
key_len
MySQL中使用索引字节长度
rows
mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值
extra
该列包含MySQL解决查询的详细信息
“Using index”
此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
“Using where”
这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
“Using temporary”
这意味着mysql在对查询结果排序时会使用一个临时表。
“Using filesort”
这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
“Range checked for each record(index map: N)”
这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。
(2)正确使用索引
数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。即使建立索引，索引也不会生效：
- like '%xx'
select * from tb1 where name like '%cn';
- 使用函数
select * from tb1 where reverse(name) = 'wupeiqi';
- or
select * from tb1 where nid = 1 or email = 'seven@live.com';
特别的：当or条件中有未建立索引的列才失效，以下会走索引
select * from tb1 where nid = 1 or name = 'seven';
select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
- 类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然...
select * from tb1 where name = 999;
- !=
select * from tb1 where name != 'alex'
特别的：如果是主键，则还是会走索引
select * from tb1 where nid != 123
- >
select * from tb1 where name > 'alex'
特别的：如果是主键或索引是整数类型，则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
- order by
select email from tb1 order by name desc;
当根据索引排序时候，选择的映射如果不是索引，则不走索引
特别的：如果对主键排序，则还是走索引：
select * from tb1 order by nid desc;
- 组合索引最左前缀
如果组合索引为：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
其他注意事项
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合
3.优化数据库结构
(1)将字段很多的表分解成多个表
对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表，因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢
(2)增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询速度。即通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。
(3)增加冗余字段
设计数据表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库精致、优雅。但合理的加入冗余字段可以调高查询速度。
(4)优化插入记录的速度
插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等
对应myisam存储引擎，常见优化方法：
- 禁言索引 alter table table_name disable keys;
- 禁用唯一性检查 set unique_check = 0 ; 开启 set unique_check = 1;
- 使用批量插入
- 使用load data infile 批量导入
对应innodb存储引擎，常见优化方法：
- 禁言唯一性检查 set unique_check = 0 ; 开启 set unique_check = 1;
- 禁言外键检查 set foreign_key_checks = 0; 开启 set foreign_key_checks = 1;
- 禁言自动提交 set autocommit = 0; 开启 set autocommit = 1;
(5).分析表、检查表和优化表
- 分析表
analyze [local | no_write_to_binlog] table tb1_name[,tb1_name]...
local关键字是no_write_to_binlog关键字的别名，二者都是执行过程不写入二进制日志；使用analyze table分析表的过程中，数据库会自动对表加一个只读锁。
mysql> analyze table hjx_data;
+--------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| hjx.hjx_data | analyze | status | OK |
+--------------+---------+----------+----------+
1 row in set (0.02 sec)
说明：
Op 表示执行的操作，analyze表示进行分析操作
Msg_type 表示信息类型，状态(status)、信息(info)、注意(note)、警告(warning)和错误(error)
Msg_text 显示信息
- 检查表
check table 语句能够检查innodb和myisam类型的表是否存在错误。对应myisam类型：还会更新关键字统计数据；而且也可以检查视图是否有错误。
check table tb_name [,tb_name] ... [option] ...
option = {quick | fast | medium | extended | changed}
说明：
option参数5个取值：
quick 不扫描行，不检查错误的连接
fast 只检查没有被正确关闭的表
changed 只检查上次检查后被更新的表和没有被正确关闭的表。
medium 扫描行，以验证被删除的连接是否是有效的，也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点。
extended 对每行的所有关键字进行一个全面的关键字查找
注意：option只对myisam类型的表有效，对innodb类型的表无效。
- 优化表
使用optimize table 语句来优化表，对innodb和myisam类型表都有效，但只能优化表中varchar、blog或text类型的字段
optimize [local | no_write_to_binlog] table tb_name[,tb_name]...
通过optimize table语句可以消除删除和更新造成的文件碎片。
4.优化mysql服务器
(1)优化服务器硬件
- 配置较大的内存。
- 配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。
- 合理分布磁盘I/O,把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力
- 配置多处理器，mysql是多线程的数据库，多处理器可同时执行过个线程。
(2)优化mysql的参数
配置参数文件：my.cnf或者 my.ini
key_buffer_size 表示索引缓冲区的大小 索引缓冲区所有的线程共享，这个值也不是越大越好，大小取决于内存的大小
table_cache 表示同时打开数据库的个数
query_cache_size 表示查询缓存区的大小
sort_buffer_size 表示排序缓存区的大小，值越大，进行排序的速度越快
read_buffer_size 表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小，set session read_buffer_size=n 临时设置
read_rnd_buffer_size 表示为每个线程保留的缓冲区的大小，与read_buffer_size相似，但主要用于存储按特定顺序读取出来的记录
innodb_buffer_pool_size 表示innodb类型的表和索引的最大缓存，值越大查询的速度会越快，但值太大会影响操作系统的性能
max_connections 表示数据库的最大连接数
innodb_flush_log_at_trx_commit 表示何时将缓存区的数据写入日志文件，并将日志文件写入磁盘。
对innodb引擎非常重要，参数有3个值：0、1、2，默认值为1安全性最高
0 每隔1秒将数据写入日志文件并将日志文件写入磁盘
1 表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘
2 表示每次提交事务时将数据写入日志文件，每隔1秒将日志文件写入磁盘
back_log 表示在mysql暂时停止回答前新请求之前的短时间内，多少个请求可以被存在堆栈中。
interactive_timeout 表示服务器在关闭连接前等待行动的秒数
sort_buffer_size 表示每个需要进行排序的线程分配的缓冲区的大小，可以提高order by 或group by 操作的速度，默认数值 2097144(2MB)
thread_cacha_size 表示可以复用的线程的数量
wait_timeout 表示服务器在关闭一个连接时等待行动的秒数，默认值28800
5.其他
show variables;查询变量信息
show variables like '%query%';
mysql> show variables like '%query%';
+------------------------------+------------------------------------------------
-------+
| Variable_name | Value
|
+------------------------------+------------------------------------------------
-------+
| binlog_rows_query_log_events | OFF
|
| ft_query_expansion_limit | 20
|
| have_query_cache | YES
|
| long_query_time | 10.000000
|
| query_alloc_block_size | 8192
|
| query_cache_limit | 1048576
|
| query_cache_min_res_unit | 4096
|
| query_cache_size | 1048576
|
| query_cache_type | OFF
|
| query_cache_wlock_invalidate | OFF
|
| query_prealloc_size | 8192
|
| slow_query_log | OFF
|
| slow_query_log_file | D:\Mysql\mysql-5.7.23-winx64\data\CCHNCQL008-sl
ow.log |
+------------------------------+------------------------------------------------
-------+
13 rows in set, 1 warning (0.01 sec)
a、配置MySQL自动记录慢日志
slow_query_log = OFF 是否开启慢日志记录
long_query_time = 2 时间限制，超过此时间，则记录
slow_query_log_file = /usr/slow.log 日志文件
log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录
修改配置
set global 变量名 = 值
set session 变量名 = 值
b、查看MySQL慢日志
mysqldumpslow -s at -a /usr/local/var/mysql/MacBook-Pro-3-slow.log
········
--verbose 版本
--debug 调试
--help 帮助
-v 版本
-d 调试模式
-s ORDER 排序方式
what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r 反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM 显示前N条just show the top n queries
-a 不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN 正则匹配；grep: only consider stmts that include this string
-h HOSTNAME mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l 总时间中不减去锁定时间；don't subtract lock time from total time
