MySQL-调优04

性能分析工具

show status like ‘参数’;
一些常用的性能参数如下：
connections:连接mysql服务器的次数
uptime:mysql服务器的上线时间
slow_queries:慢查询的次数
innodb_rows_read:select查询返回的行数
innodb_rows_inserted:执行insert操作插入的行数
innodb_rows_updated:执行update操作更新的行数
innodb_rows_deleted:执行delete操作删除的行数
com_select:查询操作的次数
com_insert:插入操作的次数。对于批量插入的insert操作，只累加一次
com_update:更新操作的次数
com_delete:删除操作的次数

慢查询日志
用来记录在mysql中响应时间超过阀值的语句，具体超过long_query_time值的SQL，默认long_query_time默认值为10
默认情况下，MySQL数据库没有开启慢查询日志，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响
1、开启慢查询日志参数
查看状态
show variables like ‘%slow_query_log’;
设置状态
set global slow_query_log = on;
查看慢查询日志文件的位置
show variables like ‘%slow_query_log%’;
2、修改long_query_time阈值
查看
show variables like ‘%long_query_time%’;
set global long_query_time = 1;
补充：配置文件中一并设置参数
修改my.cnf文件，【mysqld】下增加或修改参数long_query_time、slow_query_log和slow_query_log_file后，然后重启MySQL服务器
slow_query_log=ON # 开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/ttls-slow.log # 慢查询日志的目录和文件名信息
long_query_time=3 # 设置慢查询阈值3秒，超过此设定值的SQL即记录到慢查询日志
log_output=FILE

如果不指定存储路径，默认文件名为hostname-slow.log
查看慢查询数目
show status like ‘%slow_queries%’;

新建表student
create table student (
id int(11) not null auto_increment,
stuno int not null,
name varchar(20) default null,
age int(3) default null,
classId int(11) default null,
primary key (id)
) engine=innodb auto_increment=1 default charset=utf8;

新建存储过程
DELIMITER //
CREATE PROCEDURE insert_stu1( START INT, max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, name ,age ,classId ) VALUES ((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;

调用存储过程
call insert_stu1(100001,4000000);

设置set long_query_time = 1;然后查询超过1秒示例日志如下所示
#Time: 2022-04-12T14:39:54.297134Z
#User@Host: root[root] @ localhost [::1] Id: 10
#Query_time: 1.335789 Lock_time: 0.000076 Rows_sent: 14 Rows_examined: 4000000
SET timestamp=1649774394;
select * from student where name = ‘zbIlfU’;

关闭慢查询日志
永久性方式
修改my.cnf或者my.ini文件，把slow_query_log=OFF，修改保存后，重启MySQL服务生效
临时性方式
使用set语句 set GLOBAL slow_query_log=off;

show profile
show variables like ‘profiling’;
set profiling = ‘ON’;
show profiles; # 查看多条

show profile; # 查看最近的一条
show profile cpu for query 202; # 查看具体的Query_ID

分析查询语句：EXPLAIN
列名 描述
id 在一个大的查询语句中每个select关键字都对应一个唯一的id
select_type select关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际上使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息

#创建表
CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

#创建存储函数：
DELIMITER //
CREATE FUNCTION rand_string1(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT ‘abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ’;
DECLARE return_str VARCHAR(255) DEFAULT ‘’;
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

SET GLOBAL log_bin_trust_function_creators=1;

#创建存储过程：
DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s1 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s2 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
#调用存储过程
CALL insert_s1(10001,10000);
CALL insert_s2(10001,10000);
SELECT COUNT() FROM s1;
SELECT COUNT() FROM s2;

#1. table：表名
#查询的每一行记录都对应着一个单表
EXPLAIN SELECT * FROM s1;

#s1:驱动表 s2:被驱动表
EXPLAIN SELECT * FROM s1 INNER JOIN s2;

#2. id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
SELECT * FROM s1 WHERE key1 = ‘a’;

######查询优化器可能对涉及子查询的查询语句进行重写,转变为多表查询的操作########
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = ‘a’);

#Union去重
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

小结：
id如果相同，认为是一组，从上往下顺序执行
所有组中，id值越大，优先级越高，越先执行
id号每个号码，表示一趟独立的查询，一个sql的查询趟越少越好

#3. select_type：SELECT关键字对应的那个查询的类型,确定小查询在整个大查询中扮演了一个什么角色

#查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型
EXPLAIN SELECT * FROM s1;

#连接查询也算是SIMPLE类型
EXPLAIN SELECT * FROM s1 INNER JOIN s2;

#对于包含UNION或者UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个
#查询的select_type值就是PRIMARY

#对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询
#以外，其余的小查询的select_type值就是UNION

#MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是
#UNION RESULT
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

#子查询：
#如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询。
#该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = ‘a’;

#如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，
#则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY
EXPLAIN SELECT * FROM s1
WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = ‘a’;
#注意的是，select_type为DEPENDENT SUBQUERY的查询可能会被执行多次。

#在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了
#最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION。
EXPLAIN SELECT * FROM s1
WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = ‘a’ UNION SELECT key1 FROM s1 WHERE key1 = ‘b’);

#对于包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED
EXPLAIN SELECT *
FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;

#当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，
#该子查询对应的select_type属性就是MATERIALIZED
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); #子查询被转为了物化表

#5. type：针对单表的访问方法

#当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，
#那么对该表的访问方法就是system。
CREATE TABLE t(i INT) ENGINE=MYISAM;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t;

#当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const
EXPLAIN SELECT * FROM s1 WHERE id = 10005;
EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;

#在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的
#（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则
#对该被驱动表的访问方法就是eq_ref
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

#当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref
EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’;

#当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法
#就可能是ref_or_null
EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ OR key1 IS NULL;

#单表访问方法时在某些场景下可以使用Intersection、Union、
#Sort-Union这三种索引合并的方式来执行查询
EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ OR key3 = ‘a’;

#unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询
#转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type
#列的值就是unique_subquery
EXPLAIN SELECT * FROM s1
WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = ‘a’;

#如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法
EXPLAIN SELECT * FROM s1 WHERE key1 IN (‘a’, ‘b’, ‘c’);

#同上
EXPLAIN SELECT * FROM s1 WHERE key1 > ‘a’ AND key1 < ‘b’;

#当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = ‘a’;

#最熟悉的全表扫描
EXPLAIN SELECT * FROM s1;

小结：
结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
SQL性能优化的目标：至少要达到range级别，要求是ref级别，最好是consts级别

#6. possible_keys和key：可能用到的索引 和 实际上使用的索引
EXPLAIN SELECT * FROM s1 WHERE key1 > ‘z’ AND key3 = ‘a’;

#7. key_len：实际使用到的索引长度(即：字节数)
#帮你检查是否充分的利用上了索引，值越大越好,主要针对于联合索引，有一定的参考意义。
EXPLAIN SELECT * FROM s1 WHERE key_part1 = ‘a’ AND key_part2 = ‘b’;
EXPLAIN SELECT * FROM s1 WHERE key_part1 = ‘a’ AND key_part2 = ‘b’ AND key_part3 = ‘c’;

#练习：
#varchar(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

#varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

#char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)

#char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

#8. ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。
#比如只是一个常数或者是某个列。
EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’;
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

#9. rows：预估的需要读取的记录条数
#值越小越好
EXPLAIN SELECT * FROM s1 WHERE key1 > ‘z’;

#10. filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比
#如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用
#到对应索引的搜索条件外的其他搜索条件的记录有多少条。
EXPLAIN SELECT * FROM s1 WHERE key1 > ‘z’ AND common_field = ‘a’;

#对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询 #中驱动表对应的执行计划记录的filtered值，它决定了被驱动表要执行的次数(即：rows * filtered)
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = ‘a’;

#11. Extra:一些额外的信息
#更准确的理解MySQL到底将如何执行给定的查询语句
#当查询语句的没有FROM子句时将会提示该额外信息
EXPLAIN SELECT 1;

#查询语句的WHERE子句永远为FALSE时将会提示该额外信息
EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

#当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE
#子句中有针对该表的搜索条件时，在Extra列中会提示上述额外信息。
EXPLAIN SELECT * FROM s1 WHERE common_field = ‘a’;

#当使用索引访问来执行对某个表的查询，并且该语句的WHERE子句中
#有除了该索引包含的列之外的其他搜索条件时，在Extra列中也会提示上述额外信息。
EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ AND common_field = ‘a’;

#当查询列表处有MIN或者MAX聚合函数，但是并没有符合WHERE子句中
#的搜索条件的记录时，将会提示该额外信息
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = ‘abcdefg’;

EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = ‘NlPros’; #NlPros 是 s1表中key1字段真实存在的数据
#select * from s1 limit 10;

#当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以
#使用覆盖索引的情况下，在Extra列将会提示该额外信息。比方说下边这个查询中只
#需要用到idx_key1而不需要回表操作：
EXPLAIN SELECT key1,id FROM s1 WHERE key1 = ‘a’;

#有些搜索条件中虽然出现了索引列，但却不能使用到索引
#看课件理解索引条件下推
EXPLAIN SELECT * FROM s1 WHERE key1 > ‘z’ AND key1 LIKE ‘%a’;

#在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为
#其分配一块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法
#见课件说明
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;

#当我们使用左（外）连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，
#而且那个列又是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示Not exists额外信息
EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;

#如果执行计划的Extra列出现了Using intersect(...)提示，说明准备使用Intersect索引
#合并的方式执行查询，括号中的...表示需要进行索引合并的索引名称；
#如果出现了Using union(...)提示，说明准备使用Union索引合并的方式执行查询；
#出现了Using sort_union(...)提示，说明准备使用Sort-Union索引合并的方式执行查询。
EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ OR key3 = ‘a’;

#当我们的LIMIT子句的参数为0时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息
EXPLAIN SELECT * FROM s1 LIMIT 0;

#有一些情况下对结果集中的记录进行排序是可以使用到索引的。
#比如：
EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

#很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）
#进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort）。

#如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort提示
EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

#在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们
#在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来完成
#查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行
#计划的Extra列将会显示Using temporary提示
EXPLAIN SELECT DISTINCT common_field FROM s1;
#EXPLAIN SELECT DISTINCT key1 FROM s1;

#同上。
EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;

#执行计划中出现Using temporary并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以
#我们最好能使用索引来替代掉使用临时表。比如：扫描指定的索引idx_key1即可
EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;

#json格式的explain
EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2
WHERE s1.common_field = ‘a’;

分析优化器执行计划：trace
会将跟踪结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中
此功能默认关闭，开启trace，并设置格式为JSON，同时设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示
SET optimizer_trace=“enabled=on”,end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

MySQL监控分析视图-sys schema
新增sys schema，它将performance_schema和information_schema中的数据以更容易理解的方式总结归纳为“视图”，其目的就是为了降低查询performance_schema的复杂度，让DBA能够快速的定位问题
主机相关：以host_summary开头，主要汇总了IO延迟的信息
Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息
IO相关：以io开头，汇总了等待IO、IO使用量情况
内存使用情况：以memory开头，从主机、线程、事件等角度展示内存的使用情况
连接与会话信息：processlist和session相关视图，总结了会话相关信息
表相关：以schema_table开头的视图，展示了表的统计信息
索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况
语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息
用户相关：以user开头的视图，统计了用户使用的文件IO、执行语句统计信息
以wait开头，展示等待事件的延迟情况

索引情况
查询冗余索引：select * from sys.schma_redundant_indexs;
查询未使用过的索引：select * from sys.schema_unused_indexes;
查询索引的使用情况：select * from sys.schema_index_statistics where table_schema=‘dbname’;

表相关
查询表的访问量：select * from sys.schema_index_statistics group by table_schema, table_name order by io desc;
查询占用bufferpool较多的表：select * from sys.innodb_buffer_stats_by_table order by allocated limit 10;
查看表的全表扫描情况：select * from sys.statements_with_full_table_scans where db=‘dbname’;

语句相关
监控SQL执行的频率：select * from sys.statement_analysis order by exec_count desc;
监控使用了排序的SQL：select * from sys.statements_with_sorting limit 1;
监控使用了临时表或者磁盘临时表的SQL：select * from sys.statement_analysis where tmp_tables > 0 or tmp_disk_tables > 0 order by (tmp_tables+tmp_disk_tables) desc;

IO相关
查看消耗磁盘IO文件：select * from sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb相关
行锁阻塞情况：select * from sys.innodb_lock_waits;

风险提示：通过sys查询会消耗大量资源区收集相关信息，严重的可能会导致业务请求被阻塞，从而引起故障，建议不要频繁去查询sys

数据库调优措施

调优维度和步骤
第1步：选择合适的DBMS
DBMS的选择关系到了后面整个设计过程，所以第一步就是要选择合适的DBMS
第2步：优化表设计
好的表结构可以在业务发展和用户量增加的情况下依然发挥作用，不好的表结构设计会让数据表变得非常臃肿，查询效率也会降低
第3步：优化逻辑查询
SQL的查询重写包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等
第4步：优化物理查询
需要掌握的重点是对索引的创建和使用
第5步：使用redis和memcached作为缓存
通常我们对于查询响应要求高的场景（响应时间短，吞吐量大），可以考虑内存数据库，毕竟术业有专攻
第6步：库级优化
读写分离
数据分片：分库分表

优化MySQL服务器
1、优化服务器硬件
配置较大的内存
配置高速磁盘系统
合理分布磁盘I/O
配置多处理器
2、优化MySQL的参数
innodb_buffer_pool_size:表和索引的最大缓存
key_buffer_size：索引缓冲区的大小
table_cache:同时打开表的个数
query_cache_size:查询缓冲区的大小
query_cache_type:值是0时，所有的查询都不使用查询缓存区
sort_buffer_size:需要进行排序的线程分配的缓冲区的大小
join_buffer_size = 8M : 联合查询操作所能使用的缓冲区大小
read_buffer_size:每个线程连续扫描时为扫描的每个表分配的缓冲区大小
innodb_flush_log_at_trx_commit：何时将缓冲区的数据写入日志文件
innodb_log_buffer_size:事务日志所使用的缓冲区
max_connections:允许连接到MySQL数据库的最大数量，默认151
back_log:控制MySQL监听TCP端口时设置的积压请求栈大小
thread_cache_size:线程池缓存线程数量的大小
wait_timeout:一个请求的最大连接时间
interactive_timeout:表示服务器在关闭连接前等待行动的秒数

优化数据库结构
1、拆分表：冷热数据分离
将经常查询的热数据放到一张表中，不怎么用到的冷数据放到另一张表中
2、增加中间表
把需要经常联合查询的数据插入中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率
3、增加冗余字段
4、优化数据类型
优先选择符合存储需要的最小的数据类型
5、优化插入记录的速度
插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等这里我们分为MyISAM引擎和InnoDB存储引擎来讲
5.1、MyISAM引擎的表
禁用索引：可以在插入记录之前禁用索引，数据插入完毕后再开启索引
禁用唯一性检查：可以在插入记录之前禁用唯一性检查，等到记录插入完毕后再开启
使用批量插入：将多条INSERT语句合成一条
使用LOAD DATA INFILE批量导入：当需要批量导入数据时，如果能用LOAD DATA INFILE语句，就尽量使用。因为此语句导入数据的速度比INSERT语句快
5.2、InnoDB引擎的表：
禁用唯一性检查：可以在插入记录之前禁用索引，数据插入完毕后再开启索引
禁用外键检查：插入数据之前执行禁止对外键检查，数据插入完成之后再恢复对外键的检查
禁止自动提交：插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作
6、使用非空约束
在设计字段的时候，如果业务允许，建议尽量使用非空约束
7、分析表、检查表与优化表
分析表：主要是分析关键字的分布（ANALYZE TABLE user1;）
检查表：主要是检查表是否存在错误(CHECK TABLE user1;)
优化表：主要是消除删除或者更新造成的空间浪费(OPTIMIZE TABLE user;)

大表优化
1、限定查询的范围
禁止不带任何限制数据范围条件的查询语句
2、读/写分离
经典的数据库拆分方案，主库负责写，从库负责读
3、垂直拆分
分库分表
4、水平拆分
历史归档