Mysql优化相关记录

一、 知识补充

1、文件

1.1 配置文件

文件名	文件说明
my.cnf	linux系统中mysql的主要配置文件，每个配置项都存在默认配置。
log-bin.index	mysql主从复制主要依赖文件，需要开启主从复制才会开始记录。
log-error.log	默认关闭。记录mysql严重的警告和错误信息，每次启动关闭的详细信息。
info.log	默认关闭。记录查询的sql语句，如果开启会影响性能。 只在某些特定场景下才会开启，比如sql的慢查询。

1.2 数据文件

文件名	说明
*.opt	记录数据库的选项，数据库字符集的设置
*.frm	表定义，是描述表结构的文件。
*.myd	保存了表的数据记录 数据信息文件，是表的数据文件。
*.myi	索引信息文件，是表数据文件中任何索引的数据树。
*.ibd	如果采用独立表存储模式，会产生此类文件，为myd + myi。

2、逻辑架构

2.1 架构图

• Connectors

  指的是不同语言中与SQL的交互。

• Connection Pool
  　　管理缓冲用户连接，线程处理等需要缓存的需求。负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。

• Management Serveices & Utilities
  　　系统管理和控制工具。

• SQL Interface
  　　接受用户的SQL命令，并且返回用户需要查询的结果。

• Parser
  　　SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。
  主要功能：
  　　a 、 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的；
  　　b、 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。

• Optimizer
  　　查询优化器：SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求query，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果。
  　　使用的是“选取-投影-联接”策略进行查询：
  用一个例子就可以理解： select uid,name from user where gender = 1;
  这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤；然后根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。最后将这两个查询条件联接起来生成最终查询结果。

• Cache和Buffer
  　　查询缓存：主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做一个对应。该 query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。
  　　如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。

• 存储引擎接口
  　　MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。
  注意：存储引擎是基于表的，而不是数据库。

2.1.1 连接层

最上层是一些客户端和连接操作，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。

主要完成一些类似连接处理，授权认证及相关的安全方案。。

2.1.2 服务层

在 MySQL据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断，SQL接口，SQL解析，SQL分析优化， 缓存查询的处理以及部分内置函数执行(如日期,时间,数学运算,加密)等等。

各个存储引擎提供的功能都集中在这一层，如存储过程，触发器，视图等。

2.1.3 引擎层

是底层数据存取操作实现部分，由多种存储引擎共同组成。

真正负责MySQL中数据的存储和提取。就像Linux众多的文件系统 一样。利用API与存储层交互。

2.1.4 存储层

将数据存储于裸设备的文件系统之上，完成与存储引擎的交互。

2.2 流程图

2.3 存储引擎

• 查看MySQL中现在提供的存储引擎：show engines;
• 查看MySQL现在默认使用的存储引擎：show variables like '%storage_engine%';
• 查看某表使用的存储引擎：show create table 表名;

MySQL有很多种存储引擎，他们以插件的形式存在，需要那个存储引擎则装上那个存储引擎。

在MySQL中主要使用MyISAM引擎和InnoDB引擎。

MyISAM引擎和InnoDB引擎简单对比：

	MyISAM引擎	InnoDB引擎
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁 不适合高并发	行锁 适合高并发
缓存	只缓存索引	缓存索引和真实数据
表空间	小	大
关注点	性能 偏读	事务
默认安装	是	是

二、 索引优化分析

1、索引简介

1.1 什么是索引？

Mysql官方对索引的定义为: 索引(Index)是帮助Mysql高效获取数据的数据结构。

也可以简单的理解为： 排好序的快速查找的数据结构。
	数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，就是索引

一般来说，索引本身也很大，不可能全部存储在内存中，一般都是以索引文件得形式存储在磁盘上。

索引一般都是指得 B+树 ， 其中：聚集索引，次要索引，覆盖索引，符合索引，唯一索引都是使用得B+树。

大白话说：索引是一种数据结构，具有排序和快速查找两大功能。

1.2 索引的优势

• 提高了数据检索的效率，降低了数据库的IO成本。
• 降低了数据排序的成本，降低了CPU的消耗。

1.3 索引的劣势

• 索引实际上也是一张表，保存了主键与索引字段，并指向实体表记录，也占用空间
• 所以提高了查询速度，当时降低了更新表的速度。
• 索引仅仅是提高效率的一个因素。

1.4 Mysql索引分类

• 单值索引：一个索引只包含单一列，一个表可以有多个单列索引。
• 唯一索引：索引列的值必须唯一，但允许有空值。
• 复合索引：一个索引包含多个列。
• 语句

-- 创建索引,如果列是Char或者VarChar类型，length可以小于实际长度，如果是Blob或Text必须指定长度。
CREATE [unique] INDEX indexName ON tableName(cloumnName(length)....);
ALTER tablename ADD [unique] INDEX[indexname] ON (cloumnName(length));
-- 删除索引
DROP INDEX[indexname] ON tablename;
-- 查看索引
SHOW INDEX FROM tablename\G

1.5 Mysql索引结构

• BTree索引

磁盘块、数据项、指针

• Hash索引
• Full-text全文索引
• R-Tree索引

1.6 需要创建索引的情况

1. 主键自动创建唯一索引；
2. 频繁作为查询条件的字段应该创建；
3. 查询中与其他表关联的字段，外键关系建立索引；
4. 高并发的情况下倾向创建组合索引；
5. 查询中需要排序的字段；
6. 查询中统计或者分组的字段。

1.7 不需要创建的情况

1. 表记录太少的情况下。Mysql官方说可以承载800W的记录数而不降低性能，实际上当达到300W+的时候就需要创建索引；
2. 频繁更新的表或者字段不适合创建索引；
3. Where条件里面用不到的字段不需要创建索引；
4. 数据重复且分布平均的表字段不需要创建索引。某列的总数 / 数据项数 越趋近1，则索引效果越好。

2、性能下降SQL执行慢

• 主要体现在：执行时间长，等待时间长。

• 导致的问题

1 查询语句写的差
2 索引失效
3 关联查询写了太多join
4 服务器参数设置有问题,比如缓冲大小，线程数等

3、SQL的执行顺序

• 手写

select distinct
	<select_list>
from
	<left_table> <join_type>
join <right_table> on <join_condition>
where 
	<where_condition>
group by
	<group_by_list>
having
	<having_condition>
order by 
	<order_by_condition>
limit <limit_number>

• 机器读取顺序

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

4、性能分析

4.1 Mysql常见瓶颈

• CPU：CPU饱和，常常发生于内存或磁盘装取和读取数据的时候；
• IO：发生在装入数据远大于内存容量的时候；
• 服务器硬件的性能瓶颈。

4.2 语句性能分析命令：Explain

4.2.1 简介

使用Explain 关键字可以模拟SQL优化器执行SQL的查询语句，从而知道Mysql是怎么处理此条SQL语句。

主要用来分析语句的执行性能。

4.2.2 作用

通过explain我们可以获得以下信息：

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

4.2.3 使用

-- Explain + SQL语句即可。
Explain Select * from user where username = 'root';
-- 返回：
+--+-----------+-----+----+-------------+---+-------+---+----+-----+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+--+-----------+-----+----+-------------+---+-------+---+----+-----+

4.2.4 解析

• id ：select查询的序号,包含了一组数字，表示查询中执行select子句或操作表的顺序。

# 主要分为三种情况：
- id相同，执行顺序由上至下；
- id不同，id值越大优先级越高，越先被执行。如果存在子查询，id的序号会递增；
- id相同又不同，同时存在。

• select_type ：查询的类型，主要用于区分普通查询、联合查询、子查询等复杂查询。

# 主要分为六大类：
- Simple：简单的select查询，查询中不包含子查询或者Union；
- Primary：查询中若包含任何复杂的子部分，最外层的查询被标记为Primary；
- SubQuery：在Select或Where列表种包含的子查询；
- Derived：在From列表中包含的子查询被标记为Derived(衍生)，会递归执行这些子查询，把结果放在临时表里；
- Union：若第二个Select查询出现在Union之后，则被标记为Union；若Union包含在From子句的子查询中，外层Select被标记为：Derived；
- Union Result：从Union表获取结果的Select。

• table ：显示这一行的数据是关于哪张表的。
• type ：显示查询使用了何种类型。

# 主要分为了八大类。从最好到最差如下：
# system > const > eq_ref > ref > range > infex > all > null
- system：表只有一行记录，这是const类型的特例；
- const：表示通过索引一次就找到了，const用于比较primary key或者unique索引；
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引扫描；
- ref：非唯一性索引扫描，返回匹配某个单独值得所有行。
- range：只检索给定范围得行，使用一个索引来选择行。
- index：Full Index scan，遍历索引树。
- all：扫描全表找到匹配的行。
# 在平常项目中，sql语句只要能达到range以上即可。

• possible_keys ：显示可能应用在这张表中的索引，一个或多个。但是不一定被查询实际使用。
• key ：实际应用的索引。如果为Null，则没有使用索引。
• key_len ：表示索引中使用的字节数。
• ref ：显示索引的哪一列被使用了，如果可能的话，是一个常量。
• rows ：根据表统计信息及索引选用的情况，大致估算出找到所需的记录所需读取的行数。
• Extra ：包含不适合在其他列中显示但十分重要的额外信息。

# 主要是如下几个：
- Using fileSort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。（❌）
- Using temporay：使用了临时表保存中间结果。常见于排序order by和分组查询group by。（❌）
- Using Index：表示相应的select使用了覆盖索引（Covering Index），避免访问了表的数据行。
- Using where：表示使用了where过滤。
- Using join buffer：表示使用了链接缓存。
- impossible where：where子句的值总是false，不能用来获取任何元组。
- distinct：优化distinct操作。

5、索引优化

5.1 索引分析

• 单表：索引的创建顺序以及索引的使用影响查询效率；
• 两表：

# 使用不同的join方式，优化的方式也不同。
# Left join条件用于确定如何从右表搜索行，所以右边为关键点，必须创建索引。
# Right join跟上面相反，左表必须创建索引。

• 三表：结论跟两表一样。

# Join语句的优化结论：
1 尽可能渐少Join语句中的嵌套循环的循环总次数；
2 永远用小结果集驱动大结果集；
3 优先优化嵌套循环的内层循环；
4 保证join语句中被驱动表上Join条件字段已经被索引；
5 当无法保证被驱动表的Join条件字段被索引的前提下，调大JoinBuffer的大小。

5.2 防止索引失效

1. 全值匹配法则，即条件后面都是= 例如：select * from a where b = 1 and c = 2。
2. 最佳左前缀法则：如果索引了多列，要遵守从索引的最左前列开始并且不能跳过索引中的列。
3. 不在索引列上做任何操作：计算，函数，类型转换。做了的话会导致索引失效从而转向全表扫描。
4. 使用索引中范围条件之后，其右边列的索引失效。
5. 尽量使用覆盖索引(索引列和查询列一致)，减少select *。
6. MySQL在使用不等于(!= 或者 <> )的时候无法使用索引。
7. is null 和 is not null 也无法使用索引。
8. like 以通配符开头'%abc...' mysql索引也会失效变成全表扫描。结论：使用like的时候% 写右边不会造成索引失效或者使用覆盖索引。
9. 字符串不加单引号，索引也会失效。
10. 少用or 用其链接时候，索引也会失效。
11. group by 基本上都需要进行排序，如果顺序不对，会产生临时表。

江湖话：

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

Like百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；

Var引号不能丢，SQL高级也不难。

5.3 建议

1. 对于单键索引，尽量选择针对当前query过滤性更好的索引；
2. 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
3. 在选择组合索引的时候，尽量选择可以包含当前query中where子句中更多字段的索引。
4. 尽可能通过分析统计信息和调整query的写法来达到合适索引的目的。

三、 查询截取分析

1、查询优化

主要过程如下：

0. 发现问题；
1. 慢查询的开启并捕获；
2. explain  + 慢SQL 分析；
3. show profile查询SQL在Mysql里面执行的细节以及生命周期；
4. SQL数据库服务器的参数调优。

1.1 永远小表驱动大表

# 有两种情况：
大表在前使用in：select * from A where id in (select id from B);
大表在后使用exists: select * from A where exists(select 1 from B where B.id = A.id)

# exists函数说明。### in() 的变种。
# 语法: select ... from table where exists(subquery);
# 理解：将足查询的数据，放到子查询中做条件验证，根据验证结果来决定足查询中的语句时候保留。
# 提示：
# 1 exists(subquery)只返回true或者false，因此select 后面是什么都无所谓，官方说明里面会忽略。
# 2 exists的子查询往往也可以比用条件表达式、其他子查询或者join来代替。

1.2 order by 优化

1. order by 子句，尽量使用index 方式排序，避免使用fileSort 方式排序；
2. 尽可能在索引列上完成排序操作，遵照索引健的最佳左前缀；
3. 如果不在索引列上，filesort有两种算法：双路排序和挡路排序；

# 双路排序：
Mysql 4.1 之前使用的双路排序，说秒两次磁盘，最终得到数据。
读取行指针和order by 列，对他们进行排序，然后扫描已经排序号的列表，按照列表中的值重新从表中读取对应的数据输出。
# 单路排序：
从磁盘读取查询需要的所有列，按照orderby列在buffer中对他们进行排序，然后扫描排序后输出。

4. 优化策略：

# 提高Order By的速度：
1 Order by时在select上面只查询需要的字段即可，不能使用select * 获取全字段。
	当数据超出sort_buffer容量后，会创建临时文件进行合并排序，导致多次IO操作。
2 尝试提高服务器参数：sort_buff_size;
3 尝试提高服务器参数：max_length_for_sort_data。

总结：

• Mysql的两种排序方式：文件排序 或者 扫描有序索引排序。
• Mysql能为排序与查询使用相同的索引。

Key a_b_c (a,b,c)

- order by 能使用索引的最左前缀
order by a;
order by a,b;
order by a,b,c;
order by a desc ,b desc ,c desc;

- 如果where子句后面使用最左前缀定义为常量，则order by可以使用索引
where a = const order by b,c;
where a = const and b = const order by c;
where a = const and b > const order by b,c;

- 不能使用索引进行排序
order a ,b desc , c desc;
where g = const order by b, c ;
where a = const order by c;
where a = const order by a, d;
where a in (..) order by b, c;

1.3 group by 优化

1. group by 实质上时先排序后进行分组，遵照索引健的最左前缀原则。
2. 当无法使用索引时，遵照order by 调优。
3. where 高于 having ，能在where 中写的限定就不要去having 中限定。

2、慢查询日志

2.1 是什么

MySQL的慢查询日志是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time 值得SQL，则会被记录到慢查询得日志中。

2.2 怎么用

说明

跟慢查询日志相关的参数主要有如下几个：

# mysqld 下的配置

# 配置是否开启慢查询日志，0为false，1为true。默认为0，flase
slow_query_log=1;	
# 慢查询日志的存放路径，系统会给一个缺省的文件：host_name-slow.log
slow_query_log_file=/var/lib/mysql/xxx-slow.log; 
# 设置慢查询日志的时间，默认为10s,并且是运行时间大于此值，而非大于等于
long_query_time=3;
log_output=FILE;

查看是否开启及如何开启慢查询日志

查看是否开启：show variables like '%slow_qeury_log%';

开启或者关闭：set global slow_query_log = 1/0;

一些控制台命令：

查看当前系统中有多少条慢查询sql ：show global status like '%Slow_queries%'

查看多少秒算慢：show variables like '%long_query_time%'

修改慢的阈值：set global long_query_time==3

2.3 日志分析工具：mysqldumpslow

查看mysqldumpslow的基本参数

-s : 表示按照何种方式排序，
	al：平均锁定时间
	at：平均查询时间
	ar：平均返回记录数
	 c: 访问次数
	 l：锁定时间
	 r：返回记录
	 t：查询时间
-t ：返回前面多少条数据
-g : 后面跟随一个正则表达式

工作常用参考

• 得到返回记录集最多的10个sql

mysqldumpslow -s r -t 10 /xxxx/xxxx/xxxx/slow.log

• 得到访问次数最多的10个sql

mysqldumpslow -s c -t 10 /xxxx/xxxx/xxxx/slow.log

• 得到按照时间排序的前10条里面包含左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /xxx/xxx/slow.log

3、批量数据脚本

这个脚本的内容，仅仅是为了测试数据的填充。

建表语句。

- department表
create table dept(
    id INT unsigned primary key auto_increment,
	deptno mediumint unsigned not null default 0,
	dname varchar(20) not null default "",
	loc varchar(13) not null default ""
)engine=innodb default charset=utf8;

- emp表
create table emp(
    id int unsigned primary key auto_increment,
    empno mediumint not null default 0,
    ename varchar(20) not null default "",
    job varchar(20) not null default "",
    mgr mediumint unsigned not null default 0,
    hiredate date not null,
    sal decimal(7,2) not null,
    comm decimal(7,2) not null,
    deptno mediumint unsigned not null default 0
)engine=innodb default charset=utf8;

设置一下系统参数，防止创建函数的时候，系统报错。

创建函数的时候，假如报错： This function has none of DETERMINISTIC....
# 是由于开启了慢查询日志，必须为funcition指定一个参数

show variables like '%log_bin_trust_function_creators%';
set global log_bin_trust_function_creators=1;
# 使用命令行模式在mysql重启之后就会失效，为了方便，应该在配置文件中配置。

创建随机函数。

- 随机创建字符串的函数
delimiter $$
create function rand_string(n Int) returns varchar(255)
begin
	declare char_str varchar(100) default 'qwertyuiopasdfghjklzxcvbnmQWERTYUIOPLKJHGFDSAZXCVBNM';
	declare return_str varchar(255) default "";
	declare i int default 0;
	while i < n do
    set return_str = concat(return_str,substring(char_str,floor(1+rand()*52),1));
    set i = i+1;
	end while;
	return return_str;
end $$
delimiter ;
- 创建员工部门号码函数
delimiter $$
create function rand_num() returns int(5)
begin
	declare i int default 0;
	set i = floor(100+rand()*10);
return i;
end $$
delimiter ;

创建填充数据的存储过程。

- 玩emp表添加数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
# 需要把自动提交给关闭
set autocommit = 0;
repeat
	set i = i + 1;
	insert into emp(empno,enmae,job,mgr,hiredate,sal,comm,deptno) values ((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,4000,rand_num());
	until i = max_num
end repeat;
commit;
end $$
delimiter ;
- 往dept表添加东西
delimiter $$
create procedure insert_dept(in start int(10), in max_num int(10))
begin 
declare i int default 0;
# 关闭自动提交
set autocommit = 0;
repeat
	set i = i + 1;
	insert into dept (deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
	until i = max_num
end repeat;
commit;
end $$
delimiter ;

使用存储过程。

call insert_emp(100000,500000);
call insert_dept(100000,500000);

4、Show Profile

4.1 是什么

show profile 是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL调优测量。

4.2 怎么用

默认情况下，该参数是关闭状态，但是会保存最近15次的运行结果。

4.3 分析步骤

查看当前mysql版本是否支持

show variables like 'profil%';
- 查询结果如下:默认为关闭，并且保存数量为15条；
+-------------------------+-------+
| Variable_name    		  | Value |
+-------------------------+-------+
| profiling         	  | OFF   | 
| profiling_history_size  | 15    |
+-------------------------+-------+

开启profile功能

set global profiling=on

执行需要诊断的sql语句

查看profile保存的所有运行结果

show profiles;
- 展示结果如下：
+----------+------------+------------------------------+
| Query_ID | Duration    | Query                  	   |
+----------+------------+------------------------------+
|        1 |  0.0004925 | show variables like 'profi%' |
|        2 | 0.00028125 | select * from dept limit 10  |
+----------+------------+------------------------------+
- queryId 为自增标志位
- duration 为语句耗时
- query 查询语句

诊断SQL

show profile [type..] for query [queryID];
- type字段的取值：
all : 显示所有的开销信息；
block io : 显示块IO相关开销；
cpu ： 显示CPU相关开销信息；
ipc ： 显示发送和接收相关开销信息；
memory : 显示内存相关开销信息；
page faults : 显示页面错误相关开销信息；
source : 显示和source相关的开销信息；
swaps ： 显示交换次数相关开销的信息；
context switches ： 显示上下文切换开销信息；

常用基本上为：show profile cpu,block io,memory for query x;

需要注意的结论

• converting HEAP to MyISAM ：查询的结果集太大，内存不够用，存储到磁盘上。
• Creating tmp table ：创建临时表。A：拷贝数据到临时表；B：使用临时表并删除。
• Coping to tmp table on disk ：把内存中的临时表复制到磁盘中。
• locked ： 产生了锁。

5、全局查询日志

！！！！！！！！！！！！！！！！！！！！！！！！！！！！

这个配置项永远不要在生产环境中配置。

- 查看是否开启
show variables like 'general%';
- 开启
set global general_log=1;
- 设置输出信息为Table
set log_output='TABLE';
- 设置之后，所有运行的sql语句，都会记录到 mysql.general.log 表中。

四、 MySQL锁机制

1、 概述

1.1 定义

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

在数据库中，除传统的计算资源的争用以外，数据也是一种供许多用户共享的资源。锁冲突是影响数据库并发访问性能的一个重要因素。

1.2 锁的分类

从对数据操作的类型分

可以分为两大类，读锁和写锁。

• 读锁(共享锁)：针对同一份数据，多个读操作可以同事进行而不会互相影响；
• 写锁(拍它锁)：当前写操作没有完成前，它会阻断其他写锁和读锁。

对数据操作的粒度分

可以分为两大类，表锁和行锁。

1.3 锁的操作

- 查看表上是否有锁；如果in_use为1，则说明加锁了。
show open tables;
+----------+----------+--------+-------------+
| Database | Table    | In_use | Name_locked |
+----------+----------+--------+-------------+
- 手动加锁
lock table 表名 read(write), 表名B read(write), 其他；
- 手动释放锁
unlock tables;

2、 三类锁

2.1 表锁（MyISAM）

特点

偏向 MyISAM 存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

读锁（共享锁）

# 给当前表加锁Session
可以读取该表的数据；但是不能查询其它没有锁定的表；也不能对表进行增删改，会报错。
# 非当前Session
可以读取该表的记录；可以查询其它没有锁定的表；对加锁的表进行增删改的是否会处于阻塞状态。

写锁（排他锁）

# 给当前表加锁的Session
可以读取该表的数据；也可以对该表进行增删改的操作；但是不能查询别的表的数据。
# 非当前Session
对该表的所有操作都处于阻塞状态；不影响查询别的表的数据。

分析

# 分析当前数据库的表锁状态
show status like 'table%';
# 会有两个参数：
# Table_locks_immediate:产生表级锁定的次数，标识可以立即获取锁的查询次数；
# Table_locks_waited:出现表级锁定争用而发生等待的次数。值越高说明情况越严重。

总结

MyISAM在执行查询语句前,会自动给涉及的所有表加读锁,在执行增删改操作之前,会自动给涉及的表加写锁.
1、对表的读操作(加读锁)，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。
2、对表的写操作(加写锁)，会阻塞其它进程对同一表的读和写操作。

读锁会阻塞写，但是不会阻塞读。写锁则会把读和写都阻塞。

2.2 行锁（InnoDB）

特点

InnoDB存储引擎开销大，加锁慢；会出现死锁现象；锁的粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB和MyISAM的最大区别为： 一是支持事务；二是才用行级锁。

事务

Transaction

特点：原子性(Atomictiy)、一致性(Consistent)、隔离性(Isolation)、持久性(Durable)。

问题：更新丢失(Lost Update)、脏读(Dirty Reads)、不可重复读(Non-Repeatable Reads)、幻读(Phantom Reads)。

行锁

# 在设置了不自动提交之后
set autocommit = 0;
# 多个session同时操作A表时
# 查询不阻塞，操作同一行记录时发生阻塞，不同行不阻塞。

• 索引失效时，行锁会升级为表锁。

任何导致索引失效的因素，都会导致行锁升级为表锁。

• 间隙锁的危害

  • 什么时间隙锁？

    当我们用范围条件而不是先等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据的索引项加锁；队友键值在条件范围内但是不存在的记录，叫做间隙。

  • 危害

    因为在查询执行过程中，通过范围查找的话，它会锁定整个范围内所有的索引键值，及时这个键值不存在。

    会造成在锁定的时候，无法插入锁定键值范围内的任何数据。

分析

# 分析当前数据库的行锁状态
show status like 'innodb_row_lock%';
# 会有五个参数：
innodb_row_locak_current_waits:当前正在等待的数量；
innodb_row_locak_time:从系统启动到现在锁定总时间长度；
innodb_row_locak_time_avg：每次等待所花的平均时间；
innodb_row_locak_time_max:最长的一次花费的时间；
innodb_row_locak_waits:总共等待的次数。

优化建议

1. 尽可能让所有的数据检索都通过索引来完成，避免无索引行锁升级为表锁；
2. 合理设计索引，尽量缩小锁的范围；
3. 尽可能较少检索条件，避免间隙锁；
4. 尽量控制事务的大小，减少锁定资源量和时间长度；
5. 尽可能低级别的事务隔离。

2.3 页锁

特点

开销和加锁时间介于上述两者之间：会出现死锁；锁定粒度适中。

2.4 如何只锁定其中一行数据？

- 使用如下命令即可锁定其中一行数据。
set autocommit=0;	- 将自动提交关闭；
begin;	- 标识位；
select xxx from xxx where y=z for update;	- 将当前y=z这一行锁定，直至提交；
commit;	- 如果一直不提交，则别人都没有办法操作这一行，一直处于阻塞状态。

五、 主从复制

1、复制的基本原理

slave 会从master 读取binlog 来进行数据同步。

# 主要分为了三大步骤：
1、master将改变记录到二进制日志中(binary log).这些记录过程叫做二进制日志事件(binary log events).
2、slave将master的binary log events拷贝到它的中继日志中(relay log).
3、slave重做中继日志中的事件,将改变应用到自己的数据库中。

MySQL 的复制是异步串行的。

2、 复制的基本原则

1. 每个slave 只有一个master
2. 每个slave 只能有一个唯一的服务器ID
3. 每个master 可以有多个slave

3、 复制的最大问题

延时问题。

4、 一主一从常见配置

• mysql版本一致并且都已后台服务运行；

• 主从配置都在[mysqld] 节点下，都是小写；

• 主机修改配置文件；

# 主服务器唯一ID
server-id = 1
# 启用二进制日志
log_bin = /usr/local/mysql-5.7.21/log/mysql-bin.log
# 启用错误日志
log_error = /usr/local/mysql-5.7.21/log/mysql-error.log
# 根目录配置
basedir = /usr/local/mysql-5.7.21
# 临时目录配置
tmpdir  = /tmp
# 数据目录配置
datadir = /usr/local/mysql-5.7.21/data
# 配置只读参数
read-only=0
# 设置不需要复制的数据库
binlog-ignore-db=mysql
# 设置需要复制的数据库
binlog-do-db=xxxx

• 从机修改配置文件；

# 设置服务器唯一ID
server-id = 2
# 启用二进制日志
log_bin = /usr/local/mysql-5.7.21/log/mysql-bin.log

• 重启，并且关闭相应的防火墙；

• 在主机创建账户并授权；

- 创建账户以及权限分配；
grant replication slave on *.* to 'slave'@'%' identified by 'passwd';
- 刷新权限；
flush privileges;
- 查看master状态；
show master status;
- 记录上述语句的：File和Position的值

• 在从机方面配置需要复制的主机信息；

- 将当前从机和需要复制的主机绑定；
change master to master_host='xxx.xxx.xx.xxx',	- master的IP
master_user='slave',	- master创建的账户
master_password='passwd',	- master创建账户的密码
master_log_file={File},	- master上执行 show master status 之后显示的两个值
master_log_pos={Position}; - 同上
- 开启从机复制模式
start slave;
- 查看状态
show slave status\G
- 主要有两个参数：Slave_IO_Running和Slave_SQL_Running都必须要保证为Yes。

• 停止主从复制：stop slave