mysql

DML

数据定义类

介绍

词	含义
CREATE	创建(表，数据库，索引，视图)
DELETE	删除(数据库，表，索引，视图)
ALTER	修改表的定义（表，索引，视图）

格式

CREATE TABLE table_name(
列名 数据类型 约束，
列名 数据类型 约束，
...
) 表的完整性约束

注意，没定义完一列数据属性，以逗号分隔换行。

DROP TABLE table_name

ALTER TABLE table_name(
#修改数据类型
ALTER COLUMN 列名 新的数据类类型，
#添加新的列
ADD 列名 数据类 约束，
# 删除新的列
DROP COLUMN 列名
# 添加约束
ADD constriant 约束名，约束定义
# 删除约束
DROP constriant 约束名
)

数据查询类

介绍

词	含义
SELECT	查询(selectd * from …)

格式

SELECT [DISTAINCT（去除重复行）]列名 FROM 数据源 [WHERE < 检索的条件>]
[GROUP BY <分组依据列>]
[HAVING <组提取条件>]
[ORDER BY <ASC|DESC> ]

常用查询条件

类型	具体谓词
比较	= ,> ,>=,<,<= ,!=	WHERE name=zhang
确定范围	BETWEEN AND ，NOT BETWEEN AND	WHERE age BERWEEN 10 ADN 22
合	IN， NOTIN
字符匹配	LIKE， NOTLIKE	WHERE … LIKE…（正则）
空值	IS NULL , IS NOT NULL
多重条件	AND，OR

SELECT DISTINCT 
	<select list>
FROM
	<left_table> <jion_type>
JOIN
	<right_table> ON <jion_condition>
WHERE
	<where_conditio>
GROUP BY
	<group_condition>
HAVING
	<having_by_condition>
ORDER BY
	<order_by_condition>
LIMIT
	<limit_condition>

数据控制类

词	含义
GRANT	授权某个用户拥有什么权限
REVOKE	撤销用户的授权

数据操纵类

词	含义
INSERT	插入数据
UPDATE	更新数据
DELETE	删除数据

列级的完整性约束

词	含义
NOT NULL	列取值不能为空
DEFAULT	指定默认值
UNIQUE	取值不能够重复
CHECK	限制取值范围
PRIMARY KEY	主键约束
FOREIGN KEY	外键约束

索引 index

快速查询表结构当中的数据结构，实现原理：将特定的属性以二叉树，红黑树，b-tree，b+tree保存实现快速的查询。

能够实现快速的实现查询，但是数据更新,删除，修改时会影响数据库的效率。

索引原理

b+树

事务

事务的四大特性：ACID

原子性：一个事务中的所有操作，要么全部完成，要么全部不完成。事务在执行过程当中出现错误，将会回滚到事务开始前的状态。

一致性：事务开始之前和结束之后，数据库的完整性没有被破坏。

隔离性：数据允许多个并发是事务同时执行对数据的读写，修改等操作，隔离性防止多个事务并发执行时由于交叉执行导致的数据不治之，事务的隔离级别分为：

• 读未提交(read uncommited)

• 读提交(read commited)

• 可重复读（repeatable read）

• 串行化

持久性：事务结束之后，对数据的修改就是持久化的，即便系统故障也不会丢失。

相对以myisam引擎，InnoDB默认支持行锁，并支持事务。默认事务是自动提交。

并发事务所带来的问题:

问题	含义
丢失更新	当两个或多个事务选择同一行，最初事务修改的值，会被后面的时候修改的值覆盖
脏读	当一个事务正在访问数据，并且数据进行了修改，而这种修改还没有提交到数据库中，另外一个事务也要访问这个数据，然后使用了这个数据。
不可重复读	一个事务在读取某些数据后的某个时间，再次读取以前读取过的数据，却发现和以前读取的数据不一致。
幻读	一个事务按照相同的查询条件重新读取以前查询过的数据，却返现其他事务插入了满足查询条件的数据。

-- 查看当前数据库的隔离级别
show variables like 'tx_isolation'
-- 修改事务隔离级别
-- 全局
SET global TRANSACTION ISOLATION LEVEL  [READ COMMITTED,READ UNCOMMITTED,REPEATABLE READ,SERIALIZABLE]
-- 当前会话
SET session TRANSACTION ISOLATION LEVEL  [READ COMMITTED,READ UNCOMMITTED,REPEATABLE READ,SERIALIZABLE]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LIAbbd6i-1590748140621)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200520211227500.png)]

事务的隔离级

隔离级别	脏读	幻读	不可重复读
read uncommitted	会	会	会
read committed	不会	会	会
repeatable read	不会	会	不会
serializable	不会	不会	不会

read uncommitted : 一个事务可以读取另一个事务没有提交的数据。

read committed: 一个事务可以读取另一一个事务的提交的数据。解决了事务脏读的现象

repeatabled read : 可重复读，显示的是第一次事务执行查询的结果。也就是说如果其他事务对数据进行修改，该事务再次进行查询时，显示的结果还是第一次查询的结果。解决了read committed 不可重复读的现象。

serializable:串行，上面所说的事务可以实现多个事务的并发执行，串行则不允许这样的情况出现，如果一个事务正在执行，那么另一个事务就需要上一个事务执行完成之后才能够再次进行。解决了repeatable read当中可能出现的欢度

事务的控制语句

语句	含义
begin	开启事务
commit	提交事务
rollback	回滚事务，这样会结束当前的事务
savepoint [identifier]	设置一个保存点，一个事务可以设置多个保存点
release [savepoint]	释放一个保存点
rollback to [savepoint]	回滚到某个保存点
set trasecation	设置隔离级别
set autocommit	0|1 0:不自动提交，1：自动提交

存储过程与函数

存储过程和函数时事先经过编译并存储在数据库的一段sql语句的集合，调用存储过程和函数可以简化数据在数据库和应用服务器之间的传输，提高数据处理的效率。

存储过程和函数的区别在于函数必须有返回值，存储过程则没有。

创建存储过程

CREATE PROCEDURE procedure_name ([proc_parameter[...,..]])
begin
	sql语句;
end$

调用存储过程

call proc_name()$;

查看存储过程

-- 
select name from mysql.proc where db=db_name;
--
show procedure status;
--
show create procedure test.pro_test1 \G;

删除存储过程

drop procedure if exists sp_name;

语法

存储过是可以变成的，意味着可以使用变量，表达式，控制结构来完成复杂的过程。

变量

定义变量

DECLARE

通过declare可以定义一个局部变量，变量的作用范围只能在Begin… end 块中

declare name TYPE default 

变量赋值

SET

给变量赋值

Set var_name= 值，或者计算表达式

SELECT …INTO…FROM

将查询的结果赋值给变量

select name into var_name from table_name;

条件判断

if search_condition then statement_lsit
	elseif search_condition then statement_lsit
end if 

示例:

create procedure pro_test(in height int,out description varchar(10) )
#in 输入参数
#out 输出参数
#inout 即是输入又是输出参数。
begin
if height >= 180 then
	set decription='身材高挑';
elseif height >170 and heigt < 180 then
	set decription='标准';
else 
	set decription='一般';
end if;
select concat('身高'，height,'对应的类型:',descrption);
end$

CASE

CASE case_value
WHEN value then statement_list
end CASE

循环

while

while comdition do
循环体
end while;

示例:

create procedure pro_test(in int)
begin
declare total int default 0;
declare num int default 1;
while num <= n do
 	set total= total + num;
 	set num = num + 1;
end while;
select total;
end$

repeat

满足条件退出循环。

repeat 
	循环体;
	until 
	退出循环条件
end repeat;

loop

退出循环需要其他语句实现,leave 可以退出循环。

别名:loop
	循环条件
	if condition then
	leave 别名
	end if
end loop

游标/光标

游标也可称之为光标，用来存储查询结果的数据类型，在存储过程和函数可以使用游标对结果集进行循环的处理，光标的使用包括光标的声明，open，fetch 和close

声明

declare cousor_name COUSOR FOR select_statment

#打开
open cursor_name
#指向列表的一行的指针，每fetch一次，指针往下移动一行，从而获取游标的信息。
fetch cursor_name into
close cursor_name

示例

create procedure pro_test()
begin
declare e_id int(11);
declare e_name varchar(50);
declare e_age int(11);
declare e_salary int(11);
declare emp_result cursor for select * from emp;
-- 设置一个退出循环的句柄机制，当fetch指正没有指向的数据时，会触发退出句柄，这个时候将has_data设置为0，作为退出的判断的依据，必须声明在有游标之后 --
declare exit HANDLER for NOT FOUND set has_data=0;
declare has_data int default 1;


open emp_result;
repeat
fetch emp_result into e_id,e_name,e_age,e_salary;
select contact('id='，e_id,',name=',e_name,',age=',e_age,',salary=',e_salary)
until has_data=0
end repeat
close emp_result

存储函数

创建

create function func_name(param type)
return type
begin
	...
	return 
end$

查看

select function_name 

删除

drop function_name

触发器

触发器是与表相关的数据对象，指在insert/delete/update之后或者之前，触发并执行在触发器当中定义的sql语句集合，触发器的这种我一直可以应用在数据库确的完整性，日志记录，数据校验等操作。

使用OLD和NEW来引用触发器中发生的变化的记录内容，这与其他的数据库相似，现在的触发器还支持行级触发，不支持语句级触发。

简介

触发器	NEW和OLD的使用
insert	new表示将要或者已经新增的数据
update	old表示修改之前的数据，new表示将要修改或者已经修改的数据
delete	old表示将要或者已经删除的数据

创建触发器

create trigger triiger_name
before/after insert/update/delete
on table_name
[for each row] -- 行级触发器
trigger_statement;

示例：

通过触发器记录emp表的数据变更日志，包括增加，修改，删除。

创建一张日志表

CREATE TABLE emp_logd(
	id int(11) NOT NULL AUTO_INCREMENT，
	operation varchar(20) NOT NULL comment '操作类型，insert/update/delete',
	operate_time datetime NOT NULL comment '操作时间',
	operate_id int(11) NOT NULLd comment '操作表的id',
	operate_params varchar(500) comment '操作参数',
	PRIMARY KEY('id')
) ENGINE=InnoDB default charset=utf8;

创建触发器

-- 创建触发器
CREATE TRIGGER emp_insert_log
after insert
on emp
begin
-- new 指的是插入之前的表
insert into emp_log(id,operation,operate_time,operate_id,operate_params) 
	values(NULL,'insert',new.id,concat('id', new.id,',name',new.name,',age',new.age,',salary',new.salary));
end$

锁

锁的是计算机当中的协调多个基础或线程并发访问某一个资源的机制。

在计算机当中的，处理计算机资源的争用以外，数据也是一种许多用户共享的资源。如何保证数据并发的访问的一性，有效性。锁冲突影响数据库并发访问性能的一个重要因素。

分类：

1，从对数据库的粒度分：
1，表锁：操作时，会锁定整个表

​ 2，行锁： 操作时，会锁定当前的操作行。

2，从数据操作来说：

​ 1，共享锁(读锁)：针对同一分数据，多个读取操作可以同时进行，而不会互相影响

​ 2，排他锁(写锁)：当前操作没有完成之前，会阻塞其他写入和读取。

mysql当中引擎对锁的支持

引擎	表锁	行锁	页面锁
InnoDB	支持	支持	不支持
myisam	支持	不支持	不支持
memory	支持	不支持	不支持
BDB	支持	不支持	支持

锁的特性

锁	特点
表锁	偏向于myisam ,开销小，加锁块，不会出现死锁；锁的粒度大，发生锁冲突的概率高，并发支持低。
行锁	偏向于innodb,开销发，加锁慢，会出现死锁，锁的粒度小，发生锁冲突的概率低，并发支持高
页面锁	开销和加锁时间位于表锁与行锁之间，会出现死锁，并发度一般。

myisam 锁

mysql引擎支持表级锁。myisam引擎在执行select语句的时候会自动给所有表加锁，在执行更新(insert ,uptdate,delete)等之前，会自动给涉及的表加写锁，这个过程不需要用户干预，一般用户都不需要直接使用，lock table命令进行加锁。

-- myisam 引擎添加表的排他锁
lock table table_name read;
-- 添加 共享锁
lock table table_name write;
-- 解除锁
unlock table table_name;

总结： 读锁会限制同一表的写，不会影响读取，写锁会影响同一表的读与写。

-- 查看各个表的状态
show open tables

-- 查看表的锁定情况
show status table like 'table_locks_%';

table_locks_immediate:指的是能够立即获得表锁的次数，每次立即获取锁，值加1

table_locks_wait:指的是不能立即获取表锁而需要等待的次数，每等待一次，值加1，此值较高说明存在严重的表级锁争抢的情况。

索引

避免索引失效

• 全值匹配，对索引当中的所有列表在指定具体的值。

• 最左前缀法则： 如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始，并不跳过索引的其他列。

• 范围查询的右边的列，索引失效

  -- address 不会使用的索引进行查询，导致索引失效
  select * info  where name='z' and age>10 and address='cdu'
  

• 不要在索引列上进行运算操作，索引将失效

• 字符串不加单引号，索引会失效

• 尽量使用覆盖索引，避免使用select *

  explain select name from info where name='z' 
  

• 条件查询where字段当中使用or，or之后的条件不在index，整个索引将失效

• 以% 开头的like语句，索引会失效。如果仅仅是尾部的模糊查询，索引不会失效。

• 如果走全表扫描比索引快，那么不会使用index，一般全表扫描比索引块的情况是：某个属性值比较特殊，比如具有唯一性。

查看索引的使用情况。

show status like "Handler_read%"
-- 全局索引
show global status like 'Headler_read%'

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YWYNnkyw-1590748140627)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200519003034552.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Oq814Rr0-1590748140632)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200519003105828.png)]

sql优化

体系结构体

connection pool: 连接池，复制权限认证，连接限制，检测内存，线程复用，缓存大小设置

paser:解析器，解析客户端请求。

Optimizater: 优化sql语句

存储引擎

存储引擎	描述
InnoDB
MyISAM
MEMORY
MRG_MYISAM
BLACKHOLE
PERFORMANCE_SCHEMA
CVS
ARCHIVE
FEDERATED
Aria

引擎特点

特点	Innodb	myIsam	memory	merge	Ndb
存储限制	64T	有	有	没有	有
事务支持	支持
锁支持	行锁(适合高并发)	表锁	表锁	表锁	行锁
b-tree 索引	支持	支持	支持	支持	支持
哈希索引			支持
全文索引	5.6后支持	支持
集群索引	支持
数据索引	支持		支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低	n/a	低	低
内存使用	高	低	中等	低	低
批量插入	低	高	高	高	高
支持外键	支持

InnoDB

InnoDB 是Mysql默认的存储引擎，提供l具有提交，回滚，失败恢复能力的的事务安全，但是对比MyISAM写的处理效率差一些，并不会占用更多的磁盘空间以保留数据和索引。

存储方式

InnoDB 存储表和索引有两种方式：

1，使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件当中 。数据和索引保存在innodb_data_home_dir和 innodb_data_file_path定义的表空间当中。可以是多个文件

2,.使用多表空间存储，这种方式创建的表的结构任然保存在.frm 文件当中，但是每个表的数据和索引单独保存在.idb中。

MyISAM

myisam 不支持事务，不支持外键，其优势在于访问的速度块，对事务的完整性没有要求。

存储方式

每个MyISAM在磁盘上存储成三个文件，

.frm:表结构

.MYD:数据文件

.MYI:索引文件

memory

memory存储引擎当中将表存放在内存当中，每个memory 实际对应一个磁盘文件，格式是：.frm ,该文件只存储表的结构，其数据文件都是存储在内存当在，这个有利于数据的快速的处理，提高整个表的效率。使用的索引方式是：HASH.服务一旦关闭，表中的数据将会丢失。

MERGE

merge存储引擎是一组myisam表的组合，这些myisam表必须结构完全相同，merge本身没有存储数据，对merge表可以进行查询，更新，删除操作，实际上时对内部的myisam表进行操作的。

对于merge类型表插入操作，是通过insert_method 句子定义插入表，可以有三个不同的值：frist,.last

SQL语句优化

查看sql语句的执行频率

 -- 查看全局数据库的信息，不指定的情况下显示的是当前数据
 show [global] status like 'com_%'

-- 查看InnoDB 表行操作的频率
show global status like 'InnoDB_rows_%';

定位抵效率的sql语句

可以通过两种方式定位执行效率低的sql语句：

• 慢查询日志：通过慢查询日志定位那些执行效率比较低的sql预计，用–log-slow-queriers=file_name启动时，mysqld写一个包含所有执行时间超出log_query_time 秒的sql语句日志。

• show processlist: 慢查询日志在查询结束之后才会记录，所以在应用反应执行频率出现问题的时候不能定位问题，可以使用 show processlist 命令查看当前的mysql进行线程，包括线程的状态，是否锁表等，可以实时的查看sql的执行情况，同时对一些锁表操作进行优化。

名称	含义
id	用户登录mysql 时，系统会分配Connection_id 可以使用Connection_id（）查看
user	当前用户
host	sql 语句是哪个ip:port执行的。
db	当前操作数据库
command	执行的命令是什么
time	在执行show processlsit sql语句已经执行的时间
state	执行的状态

explain 分析执行计划

通过上面的步骤查询到执行效率低的sql语句后，通过explian 或 desc 获取mysql如何执行select 语句的信息，包括select语句执行的过程中表如何连接和连接的顺序。

explain sql语句;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-r7Gp9mJJ-1590748140641)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200517124120060.png)]

名称	含义
id	id表示的是其语句的有限级，相同的优先级，越靠前查询语句越先执行，数字越大的，优先级越高，越先执行
select_type	select类型
table	显示数据来自那张表
type	指的是访问的类型，这个是判断sql语句的执行效率的一个总要指标
possible_key	可能用到的索引
key	实际用到的索引，实际当中在查询表的时候，观察是否通过索引进行查询，如果没有可以进行优化
key_len	索引的长度
ref
rows	扫描的行的数量
Extra	其他的额外的执行信息

select_type	含义
simple	简单的select语句，不包含子查询，或者union
primary	查询当中包含任务复杂的子查询。
subquery	在select或where列表当中的包含子查询
derived	在FROM 列表当中包含子查询
union	若第二个select语句出现在union之后，就会标记为union,但是union当中包含from则会标记为derived
union result	从union 表获取结果的select

type	含义
null	mysql不访问任何得表，索引，直接返回结果。
system	表只有一行记录，这个时const类型的特例。
const	表通过索引一次就找到。const用于比较primary key 或者unique索引。所以只匹配单行数据，所以很快，如果将组建至于where列表中，mysql就能将查询转换为一个常量，const
eq_ref	区别与const，关联查询的结果只有一条记录。
ref	非唯一性索引扫描，返回匹配的单独值的多有行,本质上也是一种索引的访问，返回所有匹配某个单独值的所有行（多个）
range	只检索给定的返回行，使用一个索引选择行，where之后出现between,<>,in等操作。
index	index与all区别为index类型知识遍历索引树，通常比all块，all为遍历数据文件
all	遍历全表以找到匹配的行

show profile 分析sql

Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

默认show profile 时关闭的。需要使用前开启

set profiling=on

查看

show profiles

Durtation:执行的耗时

# query id 为5的查询语句详细信息
show profile all for query 5
```![在这里插入图片描述](https://img-blog.csdnimg.cn/20200529183658846.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM1NTQzOTAw,size_16,color_FFFFFF,t_70)

### trace 分析优化器的执行计划

mysql5.6 提供了对sql语句的跟踪trace，通过trace文件能够进一步了解为什么优化选择a计划，而不是b计划，

打开trace，设置格式为json，并设置trace对打能够使用的内存大小，避免在解析过程当中因为默认的内存大小过小而不能完整的展示。

```mysql
set optimizer_trace=‘enabled=on’,end_markers_in_jsond=on;
set optimize_trace_max_men_size=100000;

怎么查看trcae当中的对查询语句优化的跟踪日志信息,在系统表的information_schema当中可以查看跟踪后生成的json文件

select * infomation_schema.optimizer.trace /G;

大批量插入数据

批量加载数据时，数据的最好处于有序状态，这样会提高数据的导入的速度。

加载数据

load data local file '/PATH/filename.txt' into table 'table_name' fields terminated by ',' lines terminated by '\n'
-- 逗号为每个数据之间的分隔符，\n 为每一行数据之间的换行符

优化insert语句

当进行数据的insert操作的时候，可以考虑采用集中优化的方案：

• 如果需要同时对一张表插入多行的数据时，应该多使用多个值表的insert的语句，这种方式将大大减少客户端与数据库之间的连接，关闭等消耗，是的效率比分开执行的单个insert语句要快。

优化 order by

两种排序方式： 尽可能在index当中进行查询数据排序。

• 通过对返回数据进行排序，也就是通常说的filesort 排序，所有不是通过索引直接返回排序的结果的排序都称之为filesort排序。

  • filesort的优化
    1. 两次扫描算法，在mysql4.1 之前，使用该方法镜像排序的，首先根据条件排序字段和行指针信息，然后再排序区sort buffer当中的排序，如果sort buffer 不够，则在临时表temporary table 中进行排序。完成排序之后，在根据指针回表读取记录，该操作可能会导致大量的随机IO操作
    2. 一次扫描算法： 一次性取出满足条件的资源，然后再sort buffer当中的进行排序输出结构，排序的内存消耗大，但是排序效率高。

  mysql通过比较系变脸max_length_for_sort_data大小和查询语句取出的字段的大小来判断采用哪种排序方式。

  可以适当的提高sort_buffer_size max_length_for_sort_data 变量的大小来提高效率。

• 通过有序的索引顺序扫描直接返回有序的数据，即为: use index ,不需额外的排序，操作效率高。

优化嵌套查询

在某些情况下子查询可以被jion多表联合查询所替代，尽量减少查询语句当中的子查询。

使用sql提示

sql提示，是数据库优化的一个重要的手段，简单来说，就是sql语言加入一些人为提示来达到优化的目的。

use index

在查询语句表的后面，添加use index来提供希望mysql去参考的索引列表，就可以让mysql不再考虑其他的可用的索引。

select id name from employees use index(id_pri) where id='z'

ignore index

如果用户只是单纯的想让mysql忽略一个或者多个索引，则可以使用ignore index作为hint

select id name from employees ignore index(id_pri) where id='z'

force index

强制使用指定的索引查询

sql服务器优化

应用优化

使用数据连接池

对于数据访问数据库来说，建议连接的代价比较比较昂贵，因为频繁的创建关闭连接，是比较消耗系统资源的。这个时候有必要建立数据连接池，以提高访问的新能。

减少对mysql的访问

在编写sql语句，能够在逻辑层面与mysql尽量减少与数据库的建立连接的次数，如：insert语句，一次可以插入多条信息，就使用一个insert语句即可。

增加cache层

使用redis或memcache 等缓存服务器实现对常用的内容的缓存，或者使用mybats框架提供的一级/二级缓存。

负载均衡

利用mysql的主从复制，读写分离实现master节点实现增删改，查数据则各个slave节点当中实现。

mysql中的查询缓存优化

开启查询缓存，当执行相同的sql语句的时候，服务器就直接会从缓存当中读取结果，当数据被修改，之前的缓存将会失效，修改比较频繁的表不适合做查询缓存。

操作流程：

配置

相关参数: /ect/my.cnf.d/server.conf

query_cacahe_type=0|1|2

选项	含义
0	关闭查询缓存
1	启用查询缓存
2	只有当的select语句指明sql_cache才缓存

• 查看当前mysql支持查询缓存：

  show variables like 'have_query_cache'
  

• 查看是否开始了查询缓存

   show variables like 'query_cache_type'
  

+ 查看查询缓存的占用大小

show variables like 'query_cache_szie'

单位为字节

• 查看查询缓存的信息

  show variables like 'Qcache%'
  

查询缓存当中的select选项

可以在select语句指定两个查询缓存的相关选项：

SQL_CACHE ：如果查询结果可以缓存的，且query_cache_type系统变量问1或2，则缓存查询结果。

SQL_NO_CACHE: 服务器不适用查询缓存。

select sql_cache id,name from constomer;

查询缓存失效/不使用的情况

• 前后两次sql语句不一致，大小写也会影响不能使用缓存

• 查询语句有一些不确定是，则不会缓存，如：now(),current_data() 等函数存在与sql语句中

• 不使用任何表查询

  select 'A';
  

• 查询mysql ，information_schema ,perfromance_schema 数据库时，不会使用缓存

• 在存储的函数，触发器或事件的主体内执行的查询语句。

• 如果表发生的变动，那么缓存会失效，再次查询不会使用缓存进程查询。

mysql内存管理的优化

优化原则：

1. 将尽量多的内存分配给mysql作为缓存，但是要要给操作系统和其他程序预留足够的内存。
2. myisam 存储引擎的数据文件读取依赖与操作系统本省的IO缓存。因此，如果Myisam表，就要预留更多的内存给操作系统做io缓存。
3. 排序区，连接区，等缓是分配给每一个数据库会话（session）专用的。其默认值的设置要根据最大的连接数进行合理的分配。如果设置太大，不但浪费资源，而且并发连接较高时，会导致物理内存的耗尽

myIsam内存优化

myisam 存储引擎使用的是key_buffer 缓存索引块加速myisam索引的读写速度。对于myisam表的数据块。mysql没有特别的缓存机制。依赖于操作系统的IO缓存。

[myisamchk]
##决定了myisam索引块的缓存区大小，对直接影响到myisam的存取小，一般的myisam数据库，建议将1/4的内存分配给索引缓存区
key_buffer_size = 128M
## 排序区大小
sort_buffer_size = 128M
## read 与 wirte是分配给每个session，一个会话消耗的资源，不建议设置过大
read_buffer = 2M
write_buffer = 2M

InnoDB内存的优化

InnoDB用一块的内存区做IO的缓存，改缓存池不仅用来缓存InnoDB的索引块，而且也用缓存InnoDB的数据块。

#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

• innodb_buffer_size

  该变量决定了InnoDB存储引起数据和索引数据的最大缓存区大小，在满足系统的情况下，其值越大，缓存命中的也高，查询的速度越块

• InnoDB_log_buffer_pool_size

  该变量决定了InnoDB重做日志的缓存打下，对于可能产生更新记录的事务，可以增加其大小，可以避免InnoDB在事务提交前就执行不必要的日志写入磁盘。

mysql并发参数调整

mysql为多线程结构，包括后台线程，客户端线程。多线程可以有效利用服务器的资源。提高数据库的鬓发新能，在mysql中,控制线程主要的包括以下几个参数：

• max_connections

  采用max_connections控制mysql最大的连接数量。默认是151，如果状态量connection_errors_max_connections不为0，并一直增长，则说明不断有链接请求因为数据连接数已经达到了允许最大而失败。这时可以考虑调整最大的连接数。

  mysql最大支持的连接数，取决于很多因素，包括系统线程库质量，内存大小，每个连接的负载，cpu的处理速度，响应时间等。

  set global max_connections=500;
  

• back_log

  back_log参数控制mysql监听tcp端口时的积压请求栈的大小，如果mysql的来南京二数达到max_connections时，新来的请求将会被存栈，以等待某个连接的释放。该堆栈能够支持的缓冲连接数的数量取决于back_log。当等待的连接数量超过back_log，将不被授予连接资源，将会报错。默认是50 ，一般是max_connnection/5 最大不超过900。

• table_open_cache

  该参数用于控制所有sql语句执行线程可以打开缓冲的数量，而在执行sql语句是，每个sql执行线程至少打开一个缓存表，该参数设置应该根据最大的连接数，一个每个连接数执行关联查询设计的表的最大数量来设定,max_connnections*n。

• thread_cache_size

  为了加快数据库的连接速度，mysql会缓存一定数量的客户服务线程以备使用，通过thread_cache_size 可以控制缓存客户端服务线程的的数量。

• innodb_lock_wait _timeout

  该参数用来设置innodb事务等待的行锁的时间，默认都是50ms，可以根据动态时间进行调整，对需要快速反馈的业务系统，可以将行锁等待时间调小，避免事务长时间的挂起，对于后台批处理程序来说，可以将行锁的时间调大，避免事务发生回滚。

mysql 日志

在任何一种数据库当中，都会有各种各样的日志，记录数据库的工作，一棒子数据库管理员追踪曾经发生的各种事件，mysql也不例外，在mysql当中 ，有4种不同的日志，分别是错误日志，二进制日志，查询日志和慢查询日志。

错误日志

错误日志是mysql中最重要的日志之一，它记录了mysqld启动和停止时，以及服务器在运行过程当中任何严重错误时的信息，当数据库出现任何故障导致无法正常运行使用时，可以优先查询日志。

该日志默认是开启的，默认文件路径/var/log/mariadb/mariadb.log

二进制日志

二进制日志(binlog) 记录所有的DDL语句和DML语句但是不包括数据查询语句。此日志对数据恢复及其重要，mysql的主从复制，通过该binlog实现。

配置文件: /ect/my.conf.d/

配置参数:

#配置日志前缀
log_bin=mysqlbin
#配置日志的格式
binlig_format=STATEMENT

二进制日志的格式

STATEMENT

该日志格式在日志问世间当中的记录的都是SQL语句，每一条对数据的丢该的sql 都会记录在日志文件中，通过mysql提供的msqlbinglog工具，可以查看每一台语句的文本，主从固执的时候，slave将从日志当中的解析原文本，并在从库当中重新执行一遍。

ROW

该日志在日志文件中记录的是每一行的数据变更，而不是记录sql语句，比如执行sql语句：update tb_book set statues = '1‘， 如果是statement日志格式，在日志中会记录一行sql文件，；如果是row，由于对全表进行更新，row格式的日志中会记录每一行的数据变更。

MIXED

这个时目前mysql的默认日志的格式，即回合了statement和raw，默认情况下是采用statement，但是在歇一歇特殊的情况下采用ROW进行记录，mixed格式能尽量避开其缺点。

查询日志

查询日志记录了用户对数据库的所有操，在并发操作的环境下会产生大量的日志数据，导致许多不必要的磁盘IO，所有默认情况下查询日志功能是关闭。

相关配置参数：

# 默认关闭
general_log=0
#日志文件地址
general_log_file=PTAH

慢查询日志

慢查询日志是在进行mysql性能优化最重要的参考日志，记录了执行时间超过指定时间的查询语句。

相关配置：

# 开启慢查询
slow_query_log = 1

# 开启慢查询时间，此处为1秒，达到此值才记录数据
long_query_time = 3

# 检索行数达到此数值，才记录慢查询日志中
min_examined_row_limit = 100

# mysql 5.6.5新增，用来表示每分钟允许记录到slow log的且未使用索引的SQL语句次数，默认值为0，不限制。
log_throttle_queries_not_using_indexes = 0

# 慢查询日志文件地址
slow_query_log_file = PATH

# 开启记录没有使用索引查询语句
log-queries-not-using-indexes = 1

配置文件详解

[client]
port = 3306
# 默认情况下，socket文件应为/usr/local/mysql/mysql.socket,所以可以ln -s xx  /tmp/mysql.sock
socket = /tmp/mysql.sock 

# 服务端设置
[mysqld]

##########################################################################################################
# 基础信息
#Mysql服务的唯一编号 每个mysql服务Id需唯一
server-id = 1

#服务端口号 默认3306
port = 3306

# 启动mysql服务进程的用户
user = mysql

##########################################################################################################
# 安装目录相关
# mysql安装根目录
basedir = /usr/local/mysql-5.7.21

# mysql数据文件所在位置
datadir = /usr/local/mysql-5.7.21/data

# 临时目录 比如load data infile会用到,一般都是使用/tmp
tmpdir  = /tmp

# 设置socke文件地址
socket  = /tmp/mysql.sock


##########################################################################################################
# 事务隔离级别，默认为可重复读（REPEATABLE-READ）。（此级别下可能参数很多间隙锁，影响性能，但是修改又影响主从复制及灾难恢复，建议还是修改代码逻辑吧）
# 隔离级别可选项目：READ-UNCOMMITTED  READ-COMMITTED  REPEATABLE-READ  SERIALIZABLE
# transaction_isolation = READ-COMMITTED
transaction_isolation = REPEATABLE-READ

##########################################################################################################
# 数据库引擎与字符集相关设置

# mysql 5.1 之后，默认引擎就是InnoDB了
default_storage_engine = InnoDB
# 内存临时表默认引擎，默认InnoDB
default_tmp_storage_engine = InnoDB
# mysql 5.7新增特性，磁盘临时表默认引擎，默认InnoDB
internal_tmp_disk_storage_engine = InnoDB

#数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
character-set-server = utf8

#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server = utf8_general_ci

# 设置client连接mysql时的字符集,防止乱码
# init_connect='SET NAMES utf8'

# 是否对sql语句大小写敏感，默认值为0，1表示不敏感
lower_case_table_names = 1


##########################################################################################################
# 数据库连接相关设置
# 最大连接数，可设最大值16384，一般考虑根据同时在线人数设置一个比较综合的数字，鉴于该数值增大并不太消耗系统资源，建议直接设10000
# 如果在访问时经常出现Too Many Connections的错误提示，则需要增大该参数值
max_connections = 10000

# 默认值100，最大错误连接数，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST
# 考虑高并发场景下的容错，建议加大。
max_connect_errors = 10000

# MySQL打开的文件描述符限制，默认最小1024;
# 当open_files_limit没有被配置的时候，比较max_connections*5和ulimit -n的值，哪个大用哪个，
# 当open_file_limit被配置的时候，比较open_files_limit和max_connections*5的值，哪个大用哪个。
open_files_limit = 65535

# 注意：仍然可能出现报错信息Can't create a new thread；此时观察系统cat /proc/mysql进程号/limits，观察进程ulimit限制情况
# 过小的话，考虑修改系统配置表，/etc/security/limits.conf和/etc/security/limits.d/90-nproc.conf

# MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
# MySQL连接闲置超过一定时间后(单位：秒，此处为1800秒)将会被强行关闭
interactive_timeout = 1800 
wait_timeout = 1800 

# 在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中 
# 官方建议back_log = 50 + (max_connections / 5),封顶数为900
back_log = 900

##########################################################################################################
# 数据库数据交换设置
# 该参数限制服务器端，接受的数据包大小，如果有BLOB子段，建议增大此值，避免写入或者更新出错。有BLOB子段，建议改为1024M
max_allowed_packet = 128M

##########################################################################################################
# 内存，cache与buffer设置


# 内存临时表的最大值,默认16M，此处设置成128M
tmp_table_size = 64M
# 用户创建的内存表的大小，默认16M，往往和tmp_table_size一起设置，限制用户临师表大小。
# 超限的话，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下，增大IO压力，建议内存大，增大该数值。
max_heap_table_size = 64M



# 表示这个mysql版本是否支持查询缓存。ps：SHOW STATUS LIKE 'qcache%'，与缓存相关的状态变量。
# have_query_cache

# 这个系统变量控制着查询缓存工能的开启的关闭，0时表示关闭，1时表示打开，2表示只要select 中明确指定SQL_CACHE才缓存。
# 看业务场景决定是否使用缓存，不使用，下面就不用配置了。
query_cache_type = 0 

# 默认值1M，优点是查询缓冲可以极大的提高服务器速度, 如果你有大量的相同的查询并且很少修改表。
# 缺点：在你表经常变化的情况下或者如果你的查询原文每次都不同,查询缓冲也许引起性能下降而不是性能提升。
query_cache_size = 64M 

# 只有小于此设定值的结果才会被缓冲，保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖。
query_cache_limit = 2M

# 每个被缓存的结果集要占用的最小内存,默认值4kb，一般不怎么调整。
# 如果Qcache_free_blocks值过大，可能是query_cache_min_res_unit值过大，应该调小些
# query_cache_min_res_unit的估计值：(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
query_cache_min_res_unit = 4kb

# 在一个事务中binlog为了记录SQL状态所持有的cache大小
# 如果你经常使用大的,多声明的事务,你可以增加此值来获取更大的性能.
# 所有从事务来的状态都将被缓冲在binlog缓冲中然后在提交后一次性写入到binlog中
# 如果事务比此值大, 会使用磁盘上的临时文件来替代.
# 此缓冲在每个连接的事务第一次更新状态时被创建
binlog_cache_size = 1M



#*** MyISAM 相关选项
# 指定索引缓冲区的大小, 为MYISAM数据表开启供线程共享的索引缓存,对INNODB引擎无效。相当影响MyISAM的性能。
# 不要将其设置大于你可用内存的30%,因为一部分内存同样被OS用来缓冲行数据
# 甚至在你并不使用MyISAM 表的情况下, 你也需要仍旧设置起 8-64M 内存由于它同样会被内部临时磁盘表使用.
# 默认值 8M，建议值：对于内存在4GB左右的服务器该参数可设置为256M或384M。注意：该参数值设置的过大反而会是服务器整体效率降低！
key_buffer_size = 64M

# 为每个扫描MyISAM的线程分配参数设置的内存大小缓冲区。 
# 默认值128kb，建议值：16G内存建议1M，4G：128kb或者256kb吧
# 注意，该缓冲区是每个连接独占的，所以总缓冲区大小为 128kb*连接数；极端情况128kb*maxconnectiosns，会超级大，所以要考虑日常平均连接数。
# 一般不需要太关心该数值，稍微增大就可以了，
read_buffer_size = 262144 

# 支持任何存储引擎
# MySQL的随机读缓冲区大小，适当增大，可以提高性能。
# 默认值256kb；建议值：得参考连接数，16G内存，有人推荐8M
# 注意，该缓冲区是每个连接独占的，所以总缓冲区大小为128kb*连接数；极端情况128kb*maxconnectiosns，会超级大，所以要考虑日常平均连接数。
read_rnd_buffer_size = 1M

# order by或group by时用到 
# 支持所有引擎，innodb和myisam有自己的innodb_sort_buffer_size和myisam_sort_buffer_size设置
# 默认值256kb；建议值：得参考连接数，16G内存，有人推荐8M.
# 注意，该缓冲区是每个连接独占的，所以总缓冲区大小为 1M*连接数；极端情况1M*maxconnectiosns，会超级大。所以要考虑日常平均连接数。
sort_buffer_size = 1M

# 此缓冲被使用来优化全联合(full JOINs 不带索引的联合)
# 类似的联合在极大多数情况下有非常糟糕的性能表现,但是将此值设大能够减轻性能影响.
# 通过 “Select_full_join” 状态变量查看全联合的数量
# 注意，该缓冲区是每个连接独占的，所以总缓冲区大小为 1M*连接数；极端情况1M*maxconnectiosns，会超级大。所以要考虑日常平均连接数。
# 默认值256kb;建议值：16G内存，设置8M.
join_buffer_size = 1M

# 缓存linux文件描述符信息，加快数据文件打开速度
# 它影响myisam表的打开关闭，但是不影响innodb表的打开关闭。
# 默认值2000，建议值：根据状态变量Opened_tables去设定
table_open_cache = 2000

# 缓存表定义的相关信息，加快读取表信息速度
# 默认值1400，最大值2000，建议值：基本不改。
table_definition_cache = 1400
# 该参数是myssql 5.6后引入的，目的是提高并发。
# 默认值1，建议值：cpu核数，并且<=16
table_open_cache_instances = 2

# 当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁。可重用，减小了系统开销。
# 默认值为9，建议值：两种取值方式，方式一，根据物理内存，1G  —> 8；2G  —> 16； 3G  —> 32； >3G  —> 64；
# 方式二，根据show status like  'threads%'，查看Threads_connected值。
thread_cache_size = 16

# 默认值256k,建议值：16/32G内存，512kb，其他一般不改变，如果报错：Thread stack overrun，就增大看看,
# 注意，每个线程分配内存空间，所以总内存空间。。。你懂得。
thread_stack = 512k


##########################################################################################################
# 日志文件相关设置，一般只开启三种日志，错误日志，慢查询日志，二进制日志。普通查询日志不开启。

# 普通查询日志，默认值off，不开启
general_log = 0
# 普通查询日志存放地址
general_log_file = /usr/local/mysql-5.7.21/log/mysql-general.log

# 全局动态变量，默认3，范围：1～3
# 表示错误日志记录的信息，1：只记录error信息；2：记录error和warnings信息；3：记录error、warnings和普通的notes信息。
log_error_verbosity = 2
# 错误日志文件地址
log_error = /usr/local/mysql-5.7.21/log/mysql-error.log


# 开启慢查询
slow_query_log = 1

# 开启慢查询时间，此处为1秒，达到此值才记录数据
long_query_time = 3

# 检索行数达到此数值，才记录慢查询日志中
min_examined_row_limit = 100

# mysql 5.6.5新增，用来表示每分钟允许记录到slow log的且未使用索引的SQL语句次数，默认值为0，不限制。
log_throttle_queries_not_using_indexes = 0

# 慢查询日志文件地址
slow_query_log_file = /usr/local/mysql-5.7.21/log/mysql-slow.log

# 开启记录没有使用索引查询语句
log-queries-not-using-indexes = 1


# 开启二进制日志
log_bin = /usr/local/mysql-5.7.21/log/mysql-bin.log
# mysql清除过期日志的时间，默认值0，不自动清理，而是使用滚动循环的方式。
expire_logs_days = 0
# 如果二进制日志写入的内容超出给定值，日志就会发生滚动。你不能将该变量设置为大于1GB或小于4096字节。 默认值是1GB。
max_binlog_size = 1000M
# binlog的格式也有三种：STATEMENT，ROW，MIXED。mysql 5.7.7后，默认值从 MIXED 改为 ROW
# 关于binlog日志格式问题，请查阅网络资料
binlog_format = row
# 默认值N=1，使binlog在每N次binlog写入后与硬盘同步，ps：1最慢
# sync_binlog = 1 

##########################################################################################################
# innodb选项

# 说明：该参数可以提升扩展性和刷脏页性能。
# 默认值1，建议值：4-8；并且必须小于innodb_buffer_pool_instances
innodb_page_cleaners = 4

# 说明：一般8k和16k中选择，8k的话，cpu消耗小些，selcet效率高一点，一般不用改
# 默认值：16k；建议值：不改，
innodb_page_size = 16384

# 说明：InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.
# 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的60%-80%
# 注意别设置的过大，会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率
# 默认值：128M，建议值：物理内存的60%-80%
innodb_buffer_pool_size = 512M

# 说明:只有当设置 innodb_buffer_pool_size 值大于1G时才有意义，小于1G，instances默认为1，大于1G，instances默认为8
# 但是网络上有评价，最佳性能，每个实例至少1G大小。
# 默认值：1或8，建议值：innodb_buffer_pool_size/innodb_buffer_pool_instances >= 1G
innodb_buffer_pool_instances = 1

# 说明：mysql 5.7 新特性，defines the chunk size for online InnoDB buffer pool resizing operations.
# 实际缓冲区大小必须为innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances*倍数，取略大于innodb_buffer_pool_size
# 默认值128M，建议值：默认值就好，乱改反而容易出问题，它会影响实际buffer pool大小。
innodb_buffer_pool_chunk_size = 128M 

# 在启动时把热数据加载到内存。默认值为on，不修改
innodb_buffer_pool_load_at_startup = 1
# 在关闭时把热数据dump到本地磁盘。默认值为on，不修改
innodb_buffer_pool_dump_at_shutdown = 1

# 说明：影响Innodb缓冲区的刷新算法，建议从小到大配置，直到zero free pages；innodb_lru_scan_depth * innodb_buffer_pool_instances defines the amount of work performed by the page cleaner thread each second.
# 默认值1024，建议值: 未知
innodb_lru_scan_depth = 1024

# 说明：事务等待获取资源等待的最长时间，单位为秒，看具体业务情况，一般默认值就好
# 默认值：50，建议值：看业务。
innodb_lock_wait_timeout = 60

# 说明：设置了Mysql后台任务（例如页刷新和merge dadta from buffer pool）每秒io操作的上限。
# 默认值：200，建议值：方法一，单盘sata设100，sas10，raid10设200，ssd设2000，fushion-io设50000；方法二，通过测试工具获得磁盘io性能后，设置IOPS数值/2。
innodb_io_capacity = 2000
# 说明：该参数是所有缓冲区线程io操作的总上限。
# 默认值：innodb_io_capacity的两倍。建议值：例如用iometer测试后的iops数值就好
innodb_io_capacity_max = 4000

# 说明：控制着innodb数据文件及redo log的打开、刷写模式，三种模式：fdatasync(默认)，O_DSYNC，O_DIRECT
# fdatasync：数据文件，buffer pool->os buffer->磁盘；日志文件，buffer pool->os buffer->磁盘；
# O_DSYNC：  数据文件，buffer pool->os buffer->磁盘；日志文件，buffer pool->磁盘；
# O_DIRECT： 数据文件，buffer pool->磁盘；           日志文件，buffer pool->os buffer->磁盘；
# 默认值为空，建议值：使用SAN或者raid，建议用O_DIRECT，不懂测试的话，默认生产上使用O_DIRECT
innodb_flush_method = O_DIRECT


# 说明：mysql5.7之后默认开启，意思是，每张表一个独立表空间。
# 默认值1，开启
innodb_file_per_table = 1

# 说明：The path where InnoDB creates undo tablespaces.通常等于undo log文件的存放目录。
# 默认值./;自行设置
innodb_undo_directory = /usr/local/mysql-5.7.21/log
# 说明：The number of undo tablespaces used by InnoDB.等于undo log文件数量。5.7.21后开始弃用
# 默认值为0，建议默认值就好，不用调整了。
innodb_undo_tablespaces = 0
# 说明：定义undo使用的回滚段数量。5.7.19后弃用
# 默认值128，建议不动，以后弃用了。
innodb_undo_logs = 128
# 说明：5.7.5后开始使用，在线收缩undo log使用的空间。
# 默认值：关闭，建议值：开启
innodb_undo_log_truncate = 1
# 说明：结合innodb_undo_log_truncate，实现undo空间收缩功能
# 默认值：1G，建议值，不改。
innodb_max_undo_log_size = 1G

# 说明：重作日志文件的存放目录
innodb_log_group_home_dir = /usr/local/mysql-5.7.21/log
# 说明：日志文件的大小
# 默认值:48M,建议值：根据你系统的磁盘空间和日志增长情况调整大小
innodb_log_file_size = 128M
# 说明：日志组中的文件数量，mysql以循环方式写入日志
# 默认值2，建议值：根据你系统的磁盘空间和日志增长情况调整大小
innodb_log_files_in_group = 3
# 此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据。MySQL开发人员建议设置为1－8M之间
innodb_log_buffer_size = 16M



# 说明：可以控制log从系统buffer刷入磁盘文件的刷新频率，增大可减轻系统负荷
# 默认值是1；建议值不改。系统性能一般够用。
innodb_flush_log_at_timeout = 1
# 说明：参数可设为0，1，2；
# 参数0：表示每秒将log buffer内容刷新到系统buffer中，再调用系统flush操作写入磁盘文件。
# 参数1：表示每次事物提交，将log buffer内容刷新到系统buffer中，再调用系统flush操作写入磁盘文件。
# 参数2：表示每次事物提交，将log buffer内容刷新到系统buffer中，隔1秒后再调用系统flush操作写入磁盘文件。
innodb_flush_log_at_trx_commit = 1


# 说明：限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。
# 值默认是2000，建议值：参考数据库表总数再进行调整，一般够用不用调整。
innodb_open_files = 8192

# innodb处理io读写的后台并发线程数量，根据cpu核来确认，取值范围：1-64
# 默认值：4，建议值：与逻辑cpu数量的一半保持一致。
innodb_read_io_threads = 4
innodb_write_io_threads = 4
# 默认设置为 0,表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量
innodb_thread_concurrency = 0
# 默认值为4，建议不变。InnoDB中的清除操作是一类定期回收无用数据的操作。mysql 5.5之后，支持多线程清除操作。
innodb_purge_threads = 4 

# 说明：mysql缓冲区分为new blocks和old blocks；此参数表示old blocks占比；
# 默认值：37，建议值，一般不动
innodb_old_blocks_pct = 37
# 说明：新数据被载入缓冲池，进入old pages链区，当1秒后再次访问，则提升进入new pages链区。
# 默认值：1000
innodb_old_blocks_time=1000
# 说明：开启异步io，可以提高并发性，默认开启。
# 默认值为1，建议不动
innodb_use_native_aio = 1

# 说明：默认为空，使用data目录，一般不改。
innodb_data_home_dir=/usr/local/mysql-5.7.21/data
# 说明：Defines the name, size, and attributes of InnoDB system tablespace data files.
# 默认值，不指定，默认为ibdata1:12M:autoextend
innodb_data_file_path = ibdata1:12M:autoextend


# 说明:设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小,除非你的数据对象及其多，否则一般默认不改。
# innodb_additional_mem_pool_size = 16M


# 说明：The crash recovery mode。只有紧急情况需要恢复数据的时候，才改为大于1-6之间数值，含义查下官网。
# 默认值为0；
#innodb_force_recovery = 0

##########################################################################################################
# 其他。。。。
# 参考http://www.kuqin.com/database/20120815/328905.html
# skip-external-locking

# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。
# 缺点：所有远程主机连接授权都要使用IP地址方式，因为只认得ip地址了。
# skip_name_resolve = 0

# 默认值为off,timestamp列会自动更新为当前时间，设置为on|1，timestamp列的值就要显式更新
explicit_defaults_for_timestamp = 1




[mysqldump]
# quick选项强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中
quick
max_allowed_packet = 16M

[mysql]
# mysql命令行工具不使用自动补全功能，建议还是改为
# no-auto-rehash
auto-rehash
socket = /tmp/mysql.sock