Mysql 进阶

我用漂亮的押韵形容被掠夺一空的爱情

已于 2024-05-12 20:32:33 修改

阅读量171

点赞数 1

分类专栏：数据库文章标签： mysql

于 2020-07-09 18:17:37 首次发布

本文链接：https://blog.csdn.net/weixin_43854141/article/details/105725742

版权

数据库专栏收录该内容

24 篇文章 0 订阅

订阅专栏

Mysql 进阶

****************** 如有侵权请提示删除 *******************

文章目录

- Mysql 进阶

mysql架构介绍

MySQL的主要配置文件：
①二进制日志log-bin：用于主从复制。

②错误日志log-error：默认关闭，记录严重的警告和错误信息，每次启动和关闭的详细信息等。

③查询日志show-log：默认关闭，记录查询的sql语句，如果开启会降低mysql的整体性能，因为记录日志也是需要消耗系统资源的。

④frm文件：存放表结构。

⑤myd文件：存放表数据。

⑥myi文件：存放表索引。

特别提出MySQL中的重要配置文件：Windows下名为my.ini，Linux下为/etc/my.cnf。对于服务器的调优相关过程都在改配置文件中，需要特别掌握。

MySQL是架构非常优良，主要体现在存储引擎上。MySQL是插件式的存储引擎，它可以将查询处理和其他的系统任务以及数据的存储提取相分离。

MySQL的逻辑框架主要分为四层：
①连接层；
②服务层（主要进行sql语句相关的操作）；
③引擎层（注意引擎层是可拔插的）；
④存储层。
通过分层和可插拔式的架构，可以根据不同的生产环境构建最优的系统。

Mysql 存储引擎

MySQL存储引擎MyISAM与InnoDB区别

存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

常用的存储引擎有以下：

Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyISAM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。
MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换
MEMORY/HEAP存储引擎：支持HASH和BTREE索引

MyISAM与InnoDB区别

~	MyISAM	Innodb
存储结构	每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复	由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式	数据和索引是分别存储的，数据.MYD，索引.MYI	数据和索引是集中存储的，.ibd
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持（MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一）
锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的）	最小的粒度是表级锁，一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞	行级锁定、表级锁定，锁定力度小并发能力高，这也是 MySQL 将默认存储引擎从 MyISAM 变成InnoDB 的重要原因之一
SELECT	MyISAM更优	~
INSERT、UPDATE、DELETE	InnoDB更优	~
select count(*)	myisam更快，因为myisam内部维护了一个计数器，可以直接调取。速度很快	InnoDB不保存表的具体行数，所以执行该语句时需要全表扫描
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表
哈希索引	不支持	支持
全文索引	支持	不支持

如何选择：

是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；
如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。
系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；
MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差。

MyISAM索引与InnoDB索引的区别？

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

InnoDB引擎的4大特性

插入缓冲（insert buffer)
二次写(double write)
自适应哈希索引(ahi)
预读(read ahead)

存储引擎选择

如果没有特别的需求，使用默认的Innodb即可。

MyISAM：以读多写少为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

阿里巴巴、淘宝引擎使用

Percona 为mysql 数据库服务进行了改进，提升了在高负载情况下的IInnoDB的性能，提供了很多性能诊断工具，参数命令等。

该公司新建了一款XtraDB引擎，可完全替代InnoDB，性能和并发更好。

阿里大部分mysql 数据库使用的Percona原型加以修改
Alisql AliRedis

索引

简介

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

索引的优点

索引可以减少存储引擎需要扫描的数据量。可以大大加快数据的检索速度，降低数据库的IO成本，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
索引可以帮助我们进行排序以避免临时表。
索引可以把随机IO变为顺序IO。

索引的缺点

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；太多的索引会导致查询优化器的时间，因为查询优化器要在很多索引中选择出最合适的索引。
空间方面：索引需要占物理空间。由于在写入数据时也要维护索引，因此索引会增加写操作的成本。

使用原则

常更新的表要避免对其进行过多的索引，经常查询的应该建索引。
数据量小的最好不要建索引
在一同值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可以建立索引。
查询中与其他表关联的字段，外键关系建立索引
高并发下倾向创建组合索引
查询中排序字段，数据量大时应建索引

索引分类

1、索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
2、MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换
3、MEMORY/HEAP存储引擎：支持HASH和BTREE索引

普通索引
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
唯一索引
索引列中的值必须是唯一的，但是允许为空值，
主键索引
是一种特殊的唯一索引，不允许有空值。
组合索引
在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合
全文索引
全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引

索引的结构：

①BTREE索引；②Hash索引；③Full-Text索引；④R-Tree索引。

这里重点介绍以下两种：

BTree索引

使用B+树存储数据
B+树索引能够加快数据的查询的速度
更加适合进行范围查找
什么情况下用到BTree索引？

全值匹配的查询
匹配最左前缀的查询
匹配列前缀查询
范围查找
精确匹配左前列并范围匹配另外一列
只访问索引的查询

BTree索引的限制

如果不是按照索引最左列开始查找，那么无法使用索引。
使用索引时不能跳过索引中的列。
not in、<>、!=操作无法使用索引。
如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引。

Hash索引

Hash索引是基于Hash表实现的，只有查询条件精确匹配Hash索引中的列时才能够使用Hash索引。
对于Hash索引中的所有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码。

**Hash索引的限制

Hash索引中包含的只是Hash码与行指针，因此必须进行二次查找。
Hash索引的建立是由Hash码构成的，因此Hash索引无法用于排序。
Hash索引不支持部分索引查找也不适合范围查找。
Hash索引中Hash码的计算可能存在Hash冲突。

索引操作（新建，添加，删除）

1、普通索引

# 直接创建索引
create [unique] index indexname on tablename(columnname(length));

# 修改表结构的方式添加索引
ALTER  Table  table_name  ADD  INDEX   index_name   ON  (column(length));

#创建表的时候同时创建索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
);

注：如果是char、varchar类型的字段，length可以小于字段实际长度；如果是blob、text类型，必须指定length。

2、唯一索引 UNIQUE

#创建唯一索引
create  UNIQUE  INDEX  indexName    ON   table(column(length));

#修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

#创建表的时候直接指定
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

);

3、主键索引

#一般是在建表的时候同时创建主键索引，一个表只能有一个主键，不允许有空值
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

#建表后，添加主键索引
alter  table   table_name   add   primary  key(column_name);

4、组合索引

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

5、全文索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)    --主要用来查找文本中的关键字，而不是直接与索引中的值相比较
);

6、删除索引

# 格式一
DROP INDEX 索引名 ON 表名；
# 格式二
ALTER TABLE 表名 DROP INDEX 索引名

7、查看索引

SHOW   INDEX  FROM  table_name;

8、建立索引与否的具体情况

主键自动建立唯一索引。
频繁作为查询条件的字段。
查询中与其他表关联的字段，外键关系建立索引。
高并发下趋向创建组合索引。
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
查询中统计或分组字段。

②不需要创建索引的情况

表记录太少。（数据量太少MySQL自己就可以搞定了）
经常增删改的表。
数据重复且平均分配的字段，如国籍、性别，不适合创建索引。
频繁更新的字段不适合建立索引。
Where条件里用不到的字段不创建索引。

性能分析Explain

1、Mysql Query OPtimizer 优化器
2、Mysql常见瓶颈

CPU：cpu在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态
3、Explain
查看执行计划（explain 可以模拟优化器执行sql,从而知道mysql是怎样执行你的sql语句的）
能干嘛
表的读取顺序
数据读取操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
怎么用
Explain + sql 语句 explain select * from sales_order
各字段解释
explain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

id:选择标识符，一条语句中，该select是第几次出现
select_type: 表示查询的类型。SIMPLE表示为简单的SELECT

SIMPLE：简单SELECT(不使用UNION或子查询)
PRIMARY：最外面的SELECT
UNION：UNION中的第二个或后面的SELECT语句
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT：UNION的结果
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)

table: 数据表的名字。他们按被读取的先后顺序排列
partitions: 匹配的分区
type: 表示表的连接类型

system：表仅有一行(=系统表)。这是const联接类型的一个特例。
const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY(换句话说，如果联接不能基于关键字选择单个行的话)，则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。
ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
unique_subquery：该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。
index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range
index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。
all：对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。

possible_keys: 表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的最大可能长度，并非实际使用长度
ref: 哪些列或常量被用于查找索引列上的值
rows: 估算出结果集行数
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。
Not exists：MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #)：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。
Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。
Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。
Using sort_union(…), Using union(…), Using intersect(…)：这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by：类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。
using join buffer : 使用了连接缓存
impossible where :
select tables optimized away :
distinct :

索引优化

单表优化

explain  select  id,wuthor_id  from  article  where category_id = 1 and comments > 1 order by views desc  linit 1;
# 如果将  category_id、comments、views 三个字段一起建在联合索引中，那么
#按照Btree索引的工作原理，先排序category_id，但由于comments 条件是一个范围（即range），mysql 无法利用索引再对后面的views 部分进行检索，即range类型查询字段后面的索引会失效
# 所以这里的索引只需要category_id、views两个字段一起建联合索引

两表优化

explain  select * from  class left  join  book  on  class.card = book.card; 
# 结论  type   有all
# 在左连接中，左边一定都有，条件用于确定如何从右表搜索，所以需要在右表建立索引

三表优化

select * from class
left join book on class.card = book.card 
left join phone  on  book.card = phone.card;

# alter table phone  add  index   z ('card');
# alter table book   add  index y ('card');

# 索引最好设置在需要经常查询的字段中
# 尽可能减少join语句中的NestedLoop的循环总次数；永远用小结果集驱动大的结果集
# 优先优化NestedLoop的内层循环
# 保证Join语句中被驱动表上Join条件字段已经被索引；
# 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要吝啬JoinBuffer的设置

避免索引失效

# 建表
create table staffs(
	id int  PRIMARY key auto_increment,
	name  VARCHAR(24) not null  default ''  comment '姓名',
	age int not null default 0 comment '年龄',
	pos VARCHAR(20)   not null default '' comment '职位',
	add_time  TIMESTAMP not null default CURRENT_TIMESTAMP comment '入职时间'
)charset  utf8 comment '员工记录数';

insert into staffs(name,age,pos,add_time) values ('z3',22,'manager',NOW());
insert into staffs(name,age,pos,add_time) values ('July',23,'dev',NOW());
insert into staffs(name,age,pos,add_time) values ('2000',22,'dev',NOW());

alter table staffs add index idx_staffs_nameAgePos(name,age,pos);                 ```
1. 全值匹配
2. 最佳左前缀法则（带头索引不能死，中间索引不能断）
3. 不要在索引上做任何操作（计算、函数、自动/手动类型转换），不然会导致索引失效而转向全表扫描
4. mysql存储引擎不能继续使用索引中范围条件（bettween、<、>、in等）右边的列
5. 尽量使用覆盖索引（只查询索引的列（索引列和查询列一致）），减少select *
6. 索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描
7. 索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描
8. 索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描
要么%只写右边，要么使用覆盖索引可以解决
9. 索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描
10. 少使用 or 时，会导致索引失效而转向全表扫描
[MySQL高级 之 索引失效与优化详解](https://blog.csdn.net/wuseyukui/article/details/72312574)

#### 优化口诀：

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；

####  优化建议

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

#### 查询截取分析
- 观察生产环境慢SQL产生的情况
- 开启慢查询日志，设置阙值，比如超过5秒就是慢SQL,并抓取出来
- explain  + 慢sql分析
- show  profile  查询sql在mysql服务器里面的执行细节和生命周期情况
- SQL数据库服务器的参数调优
### 查询优化

#### 1. 永远小表驱动大表

```sql
#当B表数据集小于A表的数据集时，用in优于exists
select * from   A   where id in (select id from  B)

#当A表的数据集小于B表时，用exists优于in
select  *  from  A   where  exists  (select  1  from B  where  B.id  = A.id)
#A、B表的id字段应该建立索引

2. order by 关键字优化

order by 子句，尽量使用Index方式排序，避免使用Fileort方式排序，Index效率高，它指Mysql扫描索引本身完成排序。
尽可能在索引列上完成排序操作，遵照索引建立的最佳左前缀
如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序
order by满足两种情况，会使用Index方式排序：

ORDER BY 语句使用索引最左前列
使用where子句与Order By 子句条件列组合满足索引最左前列

优化策略

增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置

# Mysql 两种排序：文件排序或扫描有序索引排序
#Mysql 能为排序和查询使用相同的索引

#假设  索引 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 by  a  asc, b  desc ,  c  desc         /*排序不一致*/
where  g= const   order by   b,c           /*丢失a索引*/
where  a= const   order by   c              /*丢失b索引,索引断裂*/
where  a= const   order by   a,d           /*d不是索引的一部分*/
where  a in (...)  order by   b,c               /*对于排序来说，对个相等条件也是范围查询*/

3. group by 关键字优化

优化与order by 类似

group by 本质是先排序后进行分组，遵照索引建的最佳左前缀
当无法使用索引列，增大max_length_for_fort_data参数的设置 + 增大sort_buffer_size参数的设置
where高于having，能写在where限定的条件就不要去having限定了

慢查询日志

定义：

Mysql提供的一种日志记录，记录响应超过阀值的语句，具体指响应超过long_query_time值的SQL
long_query_time默认值为10，运行10秒以上的语句

说明

默认不开启慢查询日志，需手动设置参数
如果不是调优需要，一般不建议开启，会有一定的性能影响

查看以及如何开启

查看 show variables like ‘%slow_query_log%’;
开启，配置文件修改参数 slow-query_log=1
或者直接命令设置 set global long_query_time=3;
设置后不能立马看到，需要新开一个连接会话才能看到修改的值

日志分析工具mysqldumpslow

查看mysqldumpslow 的帮助信息
指令: mysqldumpslow --help

批量数据脚本

前言：使用脚本进行大数据量的批量插入，对特定情况下测试数据集的建立非常有用。

#1.创建tb_dept_bigdata（部门表）。
create table tb_dept_bigdata(
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 comment='部门表';

#2.创建tb_emp_bigdata（员工表）。
create table tb_emp_bigdata(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0 comment '编号',/*编号*/
empname varchar(20) not null default '' comment '名字',/*名字*/
job varchar(9) not null default '' comment '工作',/*工作*/
mgr mediumint unsigned not null default 0 comment '上级编号',/*上级编号*/
hiredate date not null comment '入职时间',/*入职时间*/
sal decimal(7,2) not null comment '薪水',/*薪水*/
comm decimal(7,2) not null comment '红利',/*红利*/
deptno mediumint unsigned not null default 0 comment '部门编号'/*部门编号*/
)engine=innodb default charset=utf8 comment='员工表';

#由于在创建函数时，可能会报：This function has none of DETERMINISTIC.....因此我们需开启函数创建的信任功能。
show  variables  like   'log_bin_trust_function_creators';

set global  log_bin_trust_function_creators=1;
# 这样设置参数，mysql重启后，参数会消失
#所以，也可通过在my.cnf中永久配置的方式开启该功能
#windows  下my.ini   加上log_bin_trust_function_creators=1
#Linux  下     /etc/my.cnf    下my.cnf 加上log_bin_trust_function_creators=1；

创建函数，保证每条数据都不同

#1.创建随机生成字符串的函数。
delimiter $$
drop function if exists rand_string;
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(52) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
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 $$
                                           
#2.创建随机生成编号的函数。                                           
delimiter $$
drop function if exists rand_num;
create function rand_num() returns int(5)
begin
declare i int default 0;
set i=floor(100+rand()*100);
return i;
end $$

创建存储过程用于批量插入数据

#1.创建往tb_dept_bigdata表中插入数据的存储过程。
delimiter $$
drop procedure if exists insert_dept;
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 tb_dept_bigdata (deptno,dname,loc) values(rand_num(),rand_string(10),rand_string(8));
until i=max_num
end repeat;
commit;
end $$

#2.创建往tb_emp_bigdata表中插入数据的存储过程。
delimiter $$
drop procedure if exists insert_emp;
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 tb_emp_bigdata (empno,empname,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'developer',0001,curdate(),2000,400,rand_num());
until i=max_num
end repeat;
commit;
end $$

具体执行过程批量插入数据

#1.首先执行随机生成字符串的函数。

#2.然后执行随机生成编号的函数。

#3.查看函数是否创建成功。
show FUNCTION   status;

#4.执行插入数据的存储过程，并查看其创建情况。

#恢复分好结束标识
delimiter;

#首先执行insert_dept存储过程
call insert_dept(100,100);
SELECT count(*) from tb_dept_bigdata;

#然后执行insert_emp存储过程
delimiter;
call insert_emp(100,300);
SELECT count(*) from  tb_emp_bigdata;

#5.执行存储过程，插入数据。

删除函数与存储过程

# 删除函数
drop function rand_num;
drop function rand_string;

#删除存储过程
drop procedure insert_dept;
drop procedure insert_emp;

Show Profile

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

分析步骤

开启Show Profile功能，默认该功能是关闭的，使用前需开启

show  variables  like  'profiling';
set  profiling=on;
show  variables  like  'profiling';

根据MySQL高级知识（十）——批量插入数据脚本中的数据脚本向tb_emp_bigdata表中插入50w条数据。然后执行如下查询语句：

select *from tb_emp_bigdata group by id%10 limit 150000;
select *from tb_emp_bigdata group by id%20 order by 5;

通过show profiles查看结果。

show  profiles;

使用show profile对sql语句进行诊断。

show profile cpu,block io for query Query_ID;/*Query_ID为#3步骤中show profiles列表中的Query_ID*/

#比如执行：show profile cpu,block io for query 15;
show profile cpu,block io for query 15;

5.show profile的常用查询参数。
①ALL：显示所有的开销信息。

②BLOCK IO：显示块IO开销。

③CONTEXT SWITCHES：上下文切换开销。

④CPU：显示CPU开销信息。

⑤IPC：显示发送和接收开销信息。

⑥MEMORY：显示内存开销信息。

⑦PAGE FAULTS：显示页面错误开销信息。

⑧SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。

⑨SWAPS：显示交换次数开销信息。

日常开发需注意的结论
①converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。

②Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。

③Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！

④locked。

如果在show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。

总结

show profile默认是关闭的，并且开启后只存活于当前会话，也就说每次使用前都需要开启。
通过show profiles查看sql语句的耗时时间，然后通过show profile命令对耗时时间长的sql语句进行诊断。
注意show profile诊断结果中出现相关字段的含义，判断是否需要优化sql语句。
可更多的关注MySQL官方文档，获取更多的知识。

全局查询日志

前言：全局查询日志用于保存所有的sql执行记录，该功能主要用于测试环境，在生产环境中永远不要开启该功能。

1. 如何开启

方式一：通过my.cnf配置开启该功能

# 开启全局查询日志，测试时开启，生成环境绝不能开启
general_log=1
general_log_file=/usr/local/mysql-5.7.22/data/log/globalquerylog/log_glocalquery.log

注：对my.cnf文件配置后，需重启mysql。

#1通过命令查看全局查询日志是否开启成功。
show  variables  like   '%general%';

#2查看全log_globalquery.log文件中的内容。
cat  log_glocalquery.log

方式二：通过命令开启该功能

set global  general  log=1;
set global  log_output='TABLE';

通过以上配置，执行过的sql语句将会记录到mysql库中general_log表里。
SELECT * FROM mysql.general_log;

2. 总结

①通过命令方式开启该功能，重启mysql后失效。

②全局查询日志只用在测试环境，切记生产环境中永远不要开启该功能。

数据库锁

一、概述

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不能例外。MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定。

1. 表级锁定（table-level）

表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。
当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。
使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

2. 行级锁定（row-level）

行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
使用行级锁定的主要是InnoDB存储引擎。

3. 页级锁定（page-level）

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。
使用页级锁定的主要是BerkeleyDB存储引擎。

总的来说，MySQL这3种锁的特性可大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

引擎	行锁	表锁	页锁
MyISAM	不	支持	不
BDB	不	支持	支持
InnoDB	支持	支持	不

二、表级锁

由于MyISAM存储引擎使用的锁定机制完全是由MySQL提供的表级锁定实现，所以下面我们将以MyISAM存储引擎作为示例存储引擎。

1. MySQL表级锁的锁模式

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

锁模式的兼容性：

对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；
MyISAM表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

2. 何加表锁

MyISAM在执行查询语句**（SELECT）前**，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

# 手动增加表锁
lock table  表名字 read(write),  表名字2   read(write),其他;

#解锁
unlock  table  表名;

#查看表上加的锁，**In_use显示不为0，则表示表被加锁**
show  open  tables;

3. MyISAM表锁优化建议

对于MyISAM存储引擎，虽然使用表级锁定在锁定实现的过程中比实现行级锁定或者页级锁所带来的附加成本都要小，锁定本身所消耗的资源也是最少。但是由于锁定的颗粒度比较大，所以造成锁定资源的争用情况也会比其他的锁定级别都要多，从而在较大程度上会降低并发处理能力。所以，在优化MyISAM存储引擎锁定问题的时候，最关键的就是如何让其提高并发度。

思路：
由于锁定级别是不可能改变的了，所以我们首先需要尽可能让锁定的时间变短，然后就是让可能并发进行的操作尽可能的并发。

查询表级锁争用情况

MySQL内部有两组专门的状态变量记录系统内部锁资源争用情况：

检查分析table_locks_waited 和 table_locks_immediate 状态变量

#使用如下命令查看是否有表被锁定
show open tables where In_use>0;

#使用如下命令分析表锁
show   status  like  'table%';

table_locks_immediate : 产生表级锁的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
table_locks_waited : 出现表级锁定争用而发生等待的次数，不能马上获得锁的数据

两个状态值都是从系统启动后开始记录，出现一次对应的事件则数量加1。如果这里的Table_locks_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。

如果是myisam引擎表 table_lock_wait 值比较大，那说明性能有问题，并发高，存储引擎最好改为innodb引擎

缩短锁定时间

如何让锁定时间尽可能的短呢？唯一的办法就是让我们的Query执行时间尽可能的短。

a) 尽量减少大的复杂Query，将复杂Query分拆成几个小的Query分布进行；
b) 尽可能的建立足够高效的索引，让数据检索更迅速；
c) 尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型；
d) 利用合适的机会优化MyISAM表数据文件。

分离能并行的操作

说到MyISAM的表锁，而且是读写互相阻塞的表锁，可能有些人会认为在MyISAM存储引擎的表上就只能是完全的串行化，没办法再并行了。大家不要忘记了，MyISAM的存储引擎还有一个非常有用的特性，那就是**ConcurrentInsert（并发插入）**的特性。

MyISAM存储引擎有一个控制是否打开Concurrent Insert功能的参数选项：concurrent_insert，可以设置为0，1或者2。三个值的具体说明如下：
concurrent_insert=2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录；
concurrent_insert=1，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置；
concurrent_insert=0，不允许并发插入。

可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。例如，将concurrent_insert系统变量设为2，总是允许并发插入；同时，通过定期在系统空闲时段执行OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。

合理利用读写优先级

MyISAM存储引擎的是读写互相阻塞的，那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？
答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前。

这是因为MySQL的表级锁定对于读和写是有不同优先级设定的，默认情况下是写优先级要大于读优先级。

所以，如果我们可以根据各自系统环境的差异决定读与写的优先级：

通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接读比写的优先级高。如果我们的系统是一个以读为主，可以设置此参数，如果以写为主，则不用设置；
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

虽然上面方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。

另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给写进程一定获得锁的机会。

这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”，
因此，应用中应尽量避免出现长时间运行的查询操作，
不要总想用一条SELECT语句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，
执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，
使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，
应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

三、行级锁

行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的，如广为大家所知的InnoDB存储引擎，以及MySQL的分布式存储引擎NDBCluster等都是实现了行级锁定。考虑到行级锁定由各个存储引擎自行实现，而且具体实现也各有差别，而InnoDB是目前事务型存储引擎中使用最为广泛的存储引擎，所以这里我们就主要分析一下InnoDB的锁定特性。

1. InnoDB锁定模式及实现机制

总的来说，InnoDB的锁定机制和Oracle数据库有不少相似之处。InnoDB的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。所以，可以说InnoDB的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），我们可以通过以下表格来总结上面这四种所的共存逻辑关系：

模式	共享锁（S）	排他锁（X）	意向共享锁（IS）	意向排他锁（IX）
共享锁（S）	兼容	冲突	兼容	冲突
排他锁（X）	冲突	冲突	冲突	冲突
意向共享锁（IS）	兼容	冲突	兼容	兼容
意向排他锁（IX）	冲突	冲突	兼容	兼容

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE

用SELECT … IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。
但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

2. InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。

在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

3. 间隙锁（Next-Key锁）

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

InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

例：
假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

mysql> select * from emp where empid > 100 for update;
是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
InnoDB使用间隙锁的目的：

防止幻读，以满足相关隔离级别的要求。对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；
为了满足其恢复和复制的需要。

很显然，在使用范围条件检索并锁定记录时，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

除了间隙锁给InnoDB带来性能的负面影响之外，通过索引实现锁定的方式还存在其他几个较大的性能隐患：

当Query无法利用索引的时候，InnoDB会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；
当Query使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所只想的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；
当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定。

因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。

4. 死锁

上文讲过，MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，当两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。
在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当InnoDB检测到系统中产生了死锁之后，InnoDB会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。
那InnoDB是以什么来为标准判定事务的大小的呢？MySQL官方手册中也提到了这个问题，实际上在InnoDB发现死锁之后，会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多，在死锁中就越不会被回滚掉。
但是有一点需要注意的就是，当产生死锁的场景中涉及到不止InnoDB存储引擎的时候，InnoDB是没办法检测到该死锁的，这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。
需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法：

在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁。这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。

5. 什么时候使用表锁

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁：
（1）事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
（2）事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。
在InnoDB下，使用表锁要注意以下两点。
（1）使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、InnoDB_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁，否则，InnoDB将无法自动检测并处理这种死锁。
（2）在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式见如下语句：
例如，如果需要写表t1并从表t读，可以按如下做：

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

6. InnoDB行锁优化建议

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

（1）要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：
①尽可能让所有数据都通过索引来完成，避免无索引行升级为表锁。

②合理设计索引，尽量缩小锁的范围。

③尽可能使用较少的检索条件，避免间隙锁。

④尽量控制事务大小，减少锁定资源量和时间长度。

⑤尽可能降低事务隔离级别。

（2）由于InnoDB的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率的小建议：

a) 类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；
b) 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
c) 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

（3）可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

show status like 'innodb_row_lock%';

各个状态量说明：

①Innodb_row_lock_current_waits：当前正在等待锁定的数量。

②Innodb_row_lock_time：从系统启动到现在锁定的时长。(重要)

③Innodb_row_lock_time_avg：每次等待锁所花平均时间。(重要)

④Innodb_row_lock_time_max：从系统启动到现在锁等待最长的一次所花的时间。

⑤Innodb_row_lock_waits：系统启动后到现在总共等待锁的次数。(重要)

主从复制

前言：本章主要讲解MySQL主从复制的操作步骤。由于环境限制，主机使用Windows环境，从机使用用Linux环境。另外MySQL的版本最好一致，笔者采用的MySQL5.7.22版本，具体安装过程请查询相关资料。

1. 主从复制的基本原理

slave会从master读取binlog来进行数据同步。主要有以下三个步骤：

①master将改变记录到二进制日志（binary log），这些记录过程叫做二进制日志事件（binary log events）。

②slave将master的binary log events拷贝到中继日志（relay log）。

③slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL的复制是异步且串行化的。

2. 主从复制的规则

①每个slave只能有一个master。（一对一）

②每个slave只能有一个唯一的服务器ID。

③每个master可以有多个slave。（一对多）

在主从复制过程中，最大的问题就是延时。

3. 一主一从的常见配置

#1.要求。

MySQL最好版本一致且后台以服务运行。并且保证主机与从机互相ping通。主从配置都在(mysqld)结点下，都是小写。

#2.主机修改my.ini配置文件

①server-id=1，主机服务器id。（必须）

②必须启用二进制文件。

log-bin="E:\devSoft\mysql-5.7.22-winx64\data\mysql-bin"

配置该项后，重新启动mysql服务，出现mysql-bin.00001和mysql-bin.index文件

③启用错误日志。（可选）

log_error ="E:\devSoft\mysql-5.7.22-winx64\data\log\errorlog\log_error.log"

#mysql安装根目录
basedir ="E:\devSoft\mysql-5.7.22-winx64"

#mysql数据文件所在位置
datadir ="E:\devSoft\mysql-5.7.22-winx64\data"

⑤临时目录。（可选）

tmpdir  ="E:\devSoft\mysql-5.7.22-winx64\"

⑥read-only=0，表示主机读写都可以。

⑦可设置不需要复制的数据库。（可选）

binlog-ignore-db=mysql

⑧可设置需要复制的数据库。（可选）

binlog-do-db=databasename

#3.从机修改my.cnf配置文件
①从服务器ID。（必须）
②启用二进制日志。（可选）
#4.主机与从机都关闭防火墙，其实也可配置ip规则，但关闭防火墙更快速。
#5.在Windows主机上建立账户并授权给slave。

a.首先在主机上创建账户：

 #%表示任何客户端都可以连接
 grant all privileges on *.* to slaveaccount（用户名）@"%(或者指定ip)" identified by '你想设置的密码' with grant option;

b.然后刷新权限表：

flush privileges;

c.然后授权给slave：

GRANT REPLICATION SLAVE ON *.* TO 'slaveaccount（上面创建的用户名）'@'从机数据库ip' IDENTIFIED BY '你想设置的密码'

d.利用b步骤命令再次刷新权限表。

#6.查询master的状态

show mastet  status;

File和Position这两个字段非常重要，File告诉从机需要从哪个文件进行复制，Position告诉从机从文件的哪个位置开始复制，在从机上配置时需用到。执行完此操作后，尽量不要在操作主服务器MySQL，防止主服务器状态变化（File和Position状态变化）。

#7.在Linux从机上配置需要的主机

CHANGE MASTER TO MASTER_HOST='主机IP',
MASTER_USER='salveaccount',
MASTER_PASSWORD='主机授权的密码',
MASTER_LOG_FILE='File名字',
MASTER_LOG_POS=Position数字;

#8.启动从服务器复制功能

start slave；

启动复制功能后，需要查看主从复制配置是否成功。

show  slave   status\G

注：只有当Slave_IO_Running:Yes和Slave_SQL_Running:Yes，这两个都为Yes的时候，主从复制配置才成功。

#9.进行测试
①首先在主机上建立数据库并插入数据。
②在从机中查看是否有相应数据库。
通过从机上查看相应数据，可知主从复制配置成功。
#10.停止从服务复制功能

stop slave;

总结
#1.主从复制的配置，大部分都在主机上，注意查看相关步骤。

#2.这里将主从机的防火墙都关闭是为了更好的演示，实际生产环境中一般不会出现windows主机和linux从机这种情况，因此不应该关闭防火墙，而是根据具体情况配置防火墙规则。

SQL执行加载顺序

FROM
ON 
WHERE 
GROUP  BY 
HAVING
SELECT
DISTINCT 
ORDER BY 
LIMIT

参考：
MySQL高级知识（一）——基础 - developer_chan - 博客园

我用漂亮的押韵形容被掠夺一空的爱情

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
Mysql 进阶

Mysql 进阶mysql架构介绍参考：MySQL高级知识（一）——基础 - developer_chan - 博客园MySQL的主要配置文件：①二进制日志log-bin：用于主从复制。②错误日志log-error：默认关闭，记录严重的警告和错误信息，每次启动和关闭的详细信息等。③查询日志show-log：默认关闭，记录查询的sql语句，如果开启会降低mysql的整体性能，因为记...
复制链接

扫一扫