MySql教程
Day63~65(MySQL高级)
本文主要是介绍Day63~65(MySQL高级)，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
13.慢查询日志
13.1.基本介绍
慢查询日志是什么？
-
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过
long_query_time值的SQL，则会被记录到慢查询日志中。 -
long_query_time的默认值为10，意思是运行10秒以上的语句。 -
由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前
explain进行全面分析。
特别说明
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
查看慢查询日志是否开以及如何开启
-
查看慢查询日志是否开启：
SHOW VARIABLES LIKE '%slow_query_log%';。 -
开启慢查询日志：
SET GLOBAL slow_query_log = 1;。使用该方法开启MySQL的慢查询日志只对当前数据库生效，如果MySQL重启后会失效。
# 1、查看慢查询日志是否开启 mysql> SHOW VARIABLES LIKE '%slow_query_log%'; +---------------------+--------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/1dcb5644392c-slow.log | +---------------------+--------------------------------------+ rows in set (0.01 sec) # 2、开启慢查询日志 mysql> SET GLOBAL slow_query_log = 1; Query OK, 0 rows affected (0.00 sec)
如果要使慢查询日志永久开启，需要修改my.cnf文件，在[mysqld]下增加修改参数。
# my.cnf [mysqld] # 1.这个是开启慢查询。注意ON需要大写 slow_query_log=ON # 2.这个是存储慢查询的日志文件。这个文件不存在的话，需要自己创建 slow_query_log_file=/var/lib/mysql/slow.log
开启了慢查询日志后，什么样的SQL才会被记录到慢查询日志里面呢？
这个是由参数long_query_time控制的，默认情况下long_query_time的值为10秒。
MySQL中查看long_query_time的时间：SHOW VARIABLES LIKE 'long_query_time%';。
# 查看long_query_time 默认是10秒 # 只有SQL的执行时间>10才会被记录 mysql> SHOW VARIABLES LIKE 'long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ row in set (0.00 sec)
修改long_query_time的时间，需要在my.cnf修改配置文件
[mysqld] # 这个是设置慢查询的时间，我设置的为1秒 long_query_time=1
查新慢查询日志的总记录条数：SHOW GLOBAL STATUS LIKE '%Slow_queries%';。
mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 3 | +---------------+-------+ row in set (0.00 sec)
13.2.日志分析工具
日志分析工具mysqldumpslow：在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
# 1、mysqldumpslow --help 来查看mysqldumpslow的帮助信息 root@1dcb5644392c:/usr/bin# mysqldumpslow --help Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] Parse and summarize the MySQL slow query log. Options are --verbose verbose --debug debug --help write this text to standard output -v verbose -d debug -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default # 按照何种方式排序 al: average lock time # 平均锁定时间 ar: average rows sent # 平均返回记录数 at: average query time # 平均查询时间 c: count # 访问次数 l: lock time # 锁定时间 r: rows sent # 返回记录 t: query time # 查询时间 -r reverse the sort order (largest last instead of first) -t NUM just show the top n queries # 返回前面多少条记录 -a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time # 2、 案例 # 2.1、得到返回记录集最多的10个SQL mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log # 2.2、得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log # 2.3、得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log # 2.4、另外建议使用这些命令时结合|和more使用，否则出现爆屏的情况 mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more
14.批量插入数据脚本
14.1.环境准备
1、建表SQL。
/* 1.dept表 */ CREATE TABLE `dept` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id', `dname` varchar(20) NOT NULL DEFAULT '' COMMENT '部门名字', `loc` varchar(13) NOT NULL DEFAULT '' COMMENT '部门地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表' /* 2.emp表 */ CREATE TABLE `emp` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `empno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '员工编号', `ename` varchar(20) NOT NULL DEFAULT '' COMMENT '员工名字', `job` varchar(9) NOT NULL DEFAULT '' COMMENT '职位', `mgr` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '上级编号', `hiredata` date NOT NULL COMMENT '入职时间', `sal` decimal(7,2) NOT NULL COMMENT '薪水', `comm` decimal(7,2) NOT NULL COMMENT '分红', `deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'
2、由于开启过慢查询日志，开启了
bin-log，我们就必须为function指定一个参数，否则使用函数会报错。
# 在mysql中设置 # log_bin_trust_function_creators 默认是关闭的 需要手动开启 mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin_trust_function_creators | OFF | +---------------------------------+-------+ row in set (0.00 sec) mysql> SET GLOBAL log_bin_trust_function_creators=1; Query OK, 0 rows affected (0.00 sec)
上述修改方式MySQL重启后会失败，在my.cnf配置文件下修改永久有效。
[mysqld] log_bin_trust_function_creators=ON
14.2.创建函数
# 1、函数：随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
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 $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100 + RAND() * 10);
RETURN i;
END $$
14.3.创建存储过程
# 1、函数：向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 $$
# 2、函数：向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,ename,job,mgr,hiredata,sal,comm,deptno) VALUES((START + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
14.4.调用存储过程
# 1、调用存储过程向dept表插入10个部门。 DELIMITER ; CALL insert_dept(100,10); # 2、调用存储过程向emp表插入50万条数据。 DELIMITER ; CALL insert_emp(100001,500000);
15.Show Profile
Show Profile是什么？
Show Profile：MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果。
分析步骤
1、是否支持，看看当前的MySQL版本是否支持。
# 查看Show Profile功能是否开启 mysql> SHOW VARIABLES LIKE 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ row in set (0.00 sec)
2、开启Show Profile功能，默认是关闭的，使用前需要开启。
# 开启Show Profile功能 mysql> SET profiling=ON; Query OK, 0 rows affected, 1 warning (0.00 sec)
3、运行SQL
SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000; SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5;
4、查看结果，执行SHOW PROFILES;
Duration：持续时间。
mysql> SHOW PROFILES; +----------+------------+---------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------------------------+ | 1 | 0.00156100 | SHOW VARIABLES LIKE 'profiling' | | 2 | 0.56296725 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 | | 3 | 0.52105825 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 | | 4 | 0.51279775 | SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5 | +----------+------------+---------------------------------------------------+ rows in set, 1 warning (0.00 sec)
5、诊断SQL，SHOW PROFILE cpu,block io FOR QUERY Query_ID;
# 这里的3是第四步中的Query_ID。 # 可以在SHOW PROFILE中看到一条SQL中完整的生命周期。 mysql> SHOW PROFILE cpu,block io FOR QUERY 3; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000097 | 0.000090 | 0.000002 | 0 | 0 | | checking permissions | 0.000010 | 0.000009 | 0.000000 | 0 | 0 | | Opening tables | 0.000039 | 0.000058 | 0.000000 | 0 | 0 | | init | 0.000046 | 0.000046 | 0.000000 | 0 | 0 | | System lock | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000023 | 0.000037 | 0.000000 | 0 | 0 | | preparing | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | | Creating tmp table | 0.000041 | 0.000053 | 0.000000 | 0 | 0 | | Sorting result | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.520620 | 0.516267 | 0.000000 | 0 | 0 | | Creating sort index | 0.000060 | 0.000051 | 0.000000 | 0 | 0 | | end | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | | removing tmp table | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000009 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000032 | 0.000064 | 0.000000 | 0 | 0 | | cleaning up | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+ rows in set, 1 warning (0.00 sec)
Show Profile查询参数备注：
-
ALL：显示所有的开销信息。 -
BLOCK IO：显示块IO相关开销（通用）。 -
CONTEXT SWITCHES：上下文切换相关开销。 -
CPU：显示CPU相关开销信息（通用）。 -
IPC：显示发送和接收相关开销信息。 -
MEMORY：显示内存相关开销信息。 -
PAGE FAULTS：显示页面错误相关开销信息。 -
SOURCE：显示和Source_function。 -
SWAPS：显示交换次数相关开销的信息。
6、Show Profile查询列表，日常开发需要注意的结论：
-
converting HEAP to MyISAM：查询结果太大，内存都不够用了，往磁盘上搬了。 -
Creating tmp table：创建临时表（拷贝数据到临时表，用完再删除），非常耗费数据库性能。 -
Copying to tmp table on disk：把内存中的临时表复制到磁盘，危险！！！ -
locked：死锁。
16.表锁(偏读)
表锁特点：
-
表锁偏向
MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。
16.1.环境准备
# 1、创建表
CREATE TABLE `mylock`(
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20)
)ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT='测试表锁';
# 2、插入数据
INSERT INTO `mylock`(`name`) VALUES('ZhangSan');
INSERT INTO `mylock`(`name`) VALUES('LiSi');
INSERT INTO `mylock`(`name`) VALUES('WangWu');
INSERT INTO `mylock`(`name`) VALUES('ZhaoLiu');
16.2.锁表的命令
1、查看数据库表锁的命令。
# 查看数据库表锁的命令 SHOW OPEN TABLES;
2、给
mylock表上读锁，给book表上写锁。
# 给mylock表上读锁，给book表上写锁 LOCK TABLE `mylock` READ, `book` WRITE; # 查看当前表的状态 mysql> SHOW OPEN TABLES; +--------------------+------------------------------------------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +--------------------+------------------------------------------------------+--------+-------------+ | sql_analysis | book | 1 | 0 | | sql_analysis | mylock | 1 | 0 | +--------------------+------------------------------------------------------+--------+-------------+
3、释放表锁。
# 释放给表添加的锁 UNLOCK TABLES; # 查看当前表的状态 mysql> SHOW OPEN TABLES; +--------------------+------------------------------------------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +--------------------+------------------------------------------------------+--------+-------------+ | sql_analysis | book | 0 | 0 | | sql_analysis | mylock | 0 | 0 | +--------------------+------------------------------------------------------+--------+-------------+
16.3.读锁案例
1、打开两个会话，
SESSION1为mylock表添加读锁。
# 为mylock表添加读锁 LOCK TABLE `mylock` READ;
2、打开两个会话，
SESSION1是否可以读自己锁的表？是否可以修改自己锁的表？是否可以读其他的表？那么SESSION2呢？
# SESSION1 # 问题1：SESSION1为mylock表加了读锁，可以读mylock表！ mysql> SELECT * FROM `mylock`; +----+----------+ | id | name | +----+----------+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | ZhaoLiu | +----+----------+ rows in set (0.00 sec) # 问题2：SESSION1为mylock表加了读锁，不可以修改mylock表！ mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1; ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated # 问题3：SESSION1为mylock表加了读锁，不可以读其他的表！ mysql> SELECT * FROM `book`; ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES # SESSION2 # 问题1：SESSION1为mylock表加了读锁，SESSION2可以读mylock表！ mysql> SELECT * FROM `mylock`; +----+----------+ | id | name | +----+----------+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | ZhaoLiu | +----+----------+ rows in set (0.00 sec) # 问题2：SESSION1为mylock表加了读锁，SESSION2修改mylock表会被阻塞，需要等待SESSION1释放mylock表！ mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1; ^C^C -- query aborted ERROR 1317 (70100): Query execution was interrupted # 问题3：SESSION1为mylock表加了读锁，SESSION2可以读其他表！ mysql> SELECT * FROM `book`; +--------+------+ | bookid | card | +--------+------+ | 1 | 1 | | 7 | 4 | | 8 | 4 | | 9 | 5 | | 5 | 6 | | 17 | 6 | | 15 | 8 | +--------+------+ rows in set (0.00 sec)
16.4.写锁案例
1、打开两个会话，
SESSION1为mylock表添加写锁。
# 为mylock表添加写锁 LOCK TABLE `mylock` WRITE;
2、打开两个会话，
SESSION1是否可以读自己锁的表？是否可以修改自己锁的表？是否可以读其他的表？那么SESSION2呢？
# SESSION1 # 问题1：SESSION1为mylock表加了写锁，可以读mylock的表！ mysql> SELECT * FROM `mylock`; +----+----------+ | id | name | +----+----------+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | ZhaoLiu | +----+----------+ rows in set (0.00 sec) # 问题2：SESSION1为mylock表加了写锁，可以修改mylock表! mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 问题3：SESSION1为mylock表加了写锁，不能读其他表! mysql> SELECT * FROM `book`; ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES # SESSION2 # 问题1：SESSION1为mylock表加了写锁，SESSION2读mylock表会阻塞，等待SESSION1释放！ mysql> SELECT * FROM `mylock`; ^C^C -- query aborted ERROR 1317 (70100): Query execution was interrupted # 问题2：SESSION1为mylock表加了写锁，SESSION2读mylock表会阻塞，等待SESSION1释放！ mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1; ^C^C -- query aborted ERROR 1317 (70100): Query execution was interrupted # 问题3：SESSION1为mylock表加了写锁，SESSION2可以读其他表！ mysql> SELECT * FROM `book`; +--------+------+ | bookid | card | +--------+------+ | 1 | 1 | | 7 | 4 | | 8 | 4 | | 9 | 5 | | 5 | 6 | | 17 | 6 | | 15 | 8 | +--------+------+ rows in set (0.00 sec)
16.5.案例结论
MyISAM引擎在执行查询语句SELECT之前，会自动给涉及到的所有表加读锁，在执行增删改之前，会自动给涉及的表加写锁。
MySQL的表级锁有两种模式：
-
表共享读锁（Table Read Lock）。
-
表独占写锁（Table Write Lock）。
対MyISAM表进行操作，会有以下情况：
-
対
MyISAM表的读操作（加读锁），不会阻塞其他线程対同一表的读操作，但是会阻塞其他线程対同一表的写操作。只有当读锁释放之后，才会执行其他线程的写操作。 -
対
MyISAM表的写操作（加写锁），会阻塞其他线程対同一表的读和写操作，只有当写锁释放之后，才会执行其他线程的读写操作。
16.6.表锁分析
mysql> SHOW STATUS LIKE 'table%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Table_locks_immediate | 173 | | Table_locks_waited | 0 | | Table_open_cache_hits | 5 | | Table_open_cache_misses | 8 | | Table_open_cache_overflows | 0 | +----------------------------+-------+ rows in set (0.00 sec)
可以通过Table_locks_immediate和Table_locks_waited状态变量来分析系统上的表锁定。具体说明如下：
Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。
Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在较严重的表级锁争用情况。
此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合作为主表的引擎。因为写锁后，其他线程不能进行任何操作，大量的写操作会使查询很难得到锁，从而造成永远阻塞。
17.行锁(偏写)
行锁特点：
-
偏向
InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
InnoDB存储引擎和MyISAM存储引擎最大不同有两点：一是支持事务，二是采用行锁。
事务的ACID：
-
Atomicity [ˌætəˈmɪsəti]。 -
Consistency [kənˈsɪstənsi]。 -
Isolation [ˌaɪsəˈleɪʃn]。 -
Durability [ˌdjʊərəˈbɪlɪti]。
17.1.环境准备
# 建表语句 CREATE TABLE `test_innodb_lock`( `a` INT, `b` VARCHAR(16) )ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='测试行锁'; # 插入数据 INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(1, 'b2'); INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(2, '3'); INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(3, '4000'); INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(4, '5000'); INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(5, '6000'); INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(6, '7000'); INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(7, '8000'); INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(8, '9000'); # 创建索引 CREATE INDEX idx_test_a ON `test_innodb_lock`(a); CREATE INDEX idx_test_b ON `test_innodb_lock`(b);
17.2.行锁案例
1、开启手动提交
打开SESSION1和SESSION2两个会话，都开启手动提交。
# 开启MySQL数据库的手动提交 mysql> SET autocommit=0; Query OK, 0 rows affected (0.00 sec)
2、读几知所写
# SESSION1 # SESSION1対test_innodb_lock表做写操作，但是没有commit。 # 执行修改SQL之后，查询一下test_innodb_lock表，发现数据被修改了。 mysql> UPDATE `test_innodb_lock` SET `b` = '88' WHERE `a` = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM `test_innodb_lock`; +------+------+ | a | b | +------+------+ | 1 | 88 | | 2 | 3 | | 3 | 4000 | | 4 | 5000 | | 5 | 6000 | | 6 | 7000 | | 7 | 8000 | | 8 | 9000 | +------+------+ rows in set (0.00 sec) # SESSION2 # SESSION2这时候来查询test_innodb_lock表。 # 发现SESSION2是读不到SESSION1未提交的数据的。 mysql> SELECT * FROM `test_innodb_lock`; +------+------+ | a | b | +------+------+ | 1 | b2 | | 2 | 3 | | 3 | 4000 | | 4 | 5000 | | 5 | 6000 | | 6 | 7000 | | 7 | 8000 | | 8 | 9000 | +------+------+ rows in set (0.00 se
3、行锁两个SESSION同时対一条记录进行写操作
# SESSION1 対test_innodb_lock表的`a`=1这一行进行写操作，但是没有commit mysql> UPDATE `test_innodb_lock` SET `b` = '99' WHERE `a` = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # SESSION2 也对test_innodb_lock表的`a`=1这一行进行写操作，但是发现阻塞了！！！ # 等SESSION1执行commit语句之后，SESSION2的SQL就会执行了 mysql> UPDATE `test_innodb_lock` SET `b` = 'asdasd' WHERE `a` = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
4、行锁两个SESSION同时对不同记录进行写操作
# SESSION1 対test_innodb_lock表的`a`=6这一行进行写操作，但是没有commit mysql> UPDATE `test_innodb_lock` SET `b` = '8976' WHERE `a` = 6; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # SESSION2 対test_innodb_lock表的`a`=4这一行进行写操作，没有阻塞！！！ # SESSION1和SESSION2同时对不同的行进行写操作互不影响 mysql> UPDATE `test_innodb_lock` SET `b` = 'Ringo' WHERE `a` = 4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
17.3.索引失效行锁变表锁
# SESSION1 执行SQL语句，没有执行commit。 # 由于`b`字段是字符串，但是没有加单引号导致索引失效 mysql> UPDATE `test_innodb_lock` SET `a` = 888 WHERE `b` = 8000; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 # SESSION2 和SESSION1操作的并不是同一行，但是也被阻塞了？？？ # 由于SESSION1执行的SQL索引失效，导致行锁升级为表锁。 mysql> UPDATE `test_innodb_lock` SET `b` = '1314' WHERE `a` = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
17.4.间隙锁的危害
什么是间隙锁？
当我们用范围条件而不是相等条件检索数据，并请求共享或者排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范文内但并不存在的记录，叫做"间隙(GAP)"。
InnoDB也会对这个"间隙"加锁，这种锁的机制就是所谓的"间隙锁"。
间隙锁的危害
因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值不存在。
间隙锁有一个比较致命的缺点，就是当锁定一个范围的键值后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会対性能造成很大的危害。
17.5.如何锁定一行
SELECT .....FOR UPDATE在锁定某一行后，其他写操作会被阻塞，直到锁定的行被COMMIT。
mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select ...for update语句，加共享锁可以使用select ... lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。
17.6.案例结论
InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。
但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。
17.7.行锁分析
mysql> SHOW STATUS LIKE 'innodb_row_lock%'; +-------------------------------+--------+ | Variable_name | Value | +-------------------------------+--------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 124150 | | Innodb_row_lock_time_avg | 31037 | | Innodb_row_lock_time_max | 51004 | | Innodb_row_lock_waits | 4 | +-------------------------------+--------+ rows in set (0.00 sec)
対各个状态量的说明如下：
-
Innodb_row_lock_current_waits：当前正在等待锁定的数量。 -
Innodb_row_lock_time：从系统启动到现在锁定总时间长度（重要）。 -
Innodb_row_lock_time_avg：每次等待所花的平均时间（重要）。 -
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间。 -
Innodb_row_lock_waits：系统启动后到现在总共等待的次数（重要）。
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化策略。
18.主从复制
18.1.复制基本原理
MySQL复制过程分为三步：
-
Master将改变记录到二进制日志(Binary Log)。这些记录过程叫做二进制日志事件，
Binary Log Events； -
Slave将Master的
Binary Log Events拷贝到它的中继日志(Replay Log); -
Slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步且串行化的。
18.2.复制基本原则
-
每个Slave只有一个Master。
-
每个Slave只能有一个唯一的服务器ID。
-
每个Master可以有多个Salve。
18.3.一主一从配置
1、基本要求：Master和Slave的MySQL服务器版本一致且后台以服务运行。
# 创建mysql-slave1实例 docker run -p 3307:3306 --name mysql-slave1 \ -v /root/mysql-slave1/log:/var/log/mysql \ -v /root/mysql-slave1/data:/var/lib/mysql \ -v /root/mysql-slave1/conf:/etc/mysql \ -e MYSQL_ROOT_PASSWORD=333 \ -d mysql:5.7
2、主从配置都是配在[mysqld]节点下，都是小写
# Master配置 [mysqld] server-id=1 # 必须 log-bin=/var/lib/mysql/mysql-bin # 必须 read-only=0 binlog-ignore-db=mysql
# Slave配置 [mysqld] server-id=2 # 必须 log-bin=/var/lib/mysql/mysql-bin
3、Master配置
# 1、GRANT REPLICATION SLAVE ON *.* TO 'username'@'从机IP地址' IDENTIFIED BY 'password'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'172.18.0.3' IDENTIFIED BY '123456'; Query OK, 0 rows affected, 1 warning (0.01 sec) # 2、刷新命令 mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) # 3、记录下File和Position # 每次配从机的时候都要SHOW MASTER STATUS;查看最新的File和Position mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 602 | | mysql | | +------------------+----------+--------------+------------------+-------------------+ row in set (0.00 sec)
4、Slave从机配置
CHANGE MASTER TO MASTER_HOST='172.18.0.4', MASTER_USER='zhangsan', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.File的编号', MASTER_LOG_POS=Position的最新值;
# 1、使用用户名密码登录进Master
mysql> CHANGE MASTER TO MASTER_HOST='172.18.0.4',
-> MASTER_USER='zhangsan',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=602;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
# 2、开启Slave从机的复制
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
# 3、查看Slave状态
# Slave_IO_Running 和 Slave_SQL_Running 必须同时为Yes 说明主从复制配置成功！
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event # Slave待命状态
Master_Host: 172.18.0.4
Master_User: zhangsan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 602
Relay_Log_File: b030ad25d5fe-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 602
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: bd047557-b20c-11ea-9961-0242ac120002
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec)
5、测试主从复制
# Master创建数据库 mysql> create database test_replication; Query OK, 1 row affected (0.01 sec) # Slave查询数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test_replication | +--------------------+ rows in set (0.00 sec)
6、停止主从复制功能
# 1、停止Slave
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)
# 2、重新配置主从
# MASTER_LOG_FILE 和 MASTER_LOG_POS一定要根据最新的数据来配
mysql> CHANGE MASTER TO MASTER_HOST='172.18.0.4',
-> MASTER_USER='zhangsan',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=797;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.18.0.4
Master_User: zhangsan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 797
Relay_Log_File: b030ad25d5fe-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 797
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: bd047557-b20c-11ea-9961-0242ac120002
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec)
这篇关于Day63~65(MySQL高级)的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
Mysql安装教程：新手必看的详细安装指南11-19
-
Mysql安装入门：新手必读指南11-18
-
MySQL事务MVCC原理入门详解11-18
-
MySQL资料：新手入门教程11-16
-
MySQL资料：新手入门教程11-16
-
MySQL教程：初学者必备的MySQL数据库入门指南11-15
-
MySQL教程：初学者必看的MySQL入门指南11-15
-
部署MySQL集群项目实战：新手入门教程11-04
-
如何部署MySQL集群资料：新手入门指南11-04
-
MySQL集群项目实战：新手入门指南11-02
-
初学者指南：部署MySQL集群资料11-02
-
部署MySQL集群教程：新手入门指南11-01
-
如何部署MySQL集群：新手入门教程11-01
-
部署MySQL集群学习：新手入门教程11-01
-
部署MySQL集群入门：新手必读指南11-01
栏目导航
