学习资源

  1. 视频课程
  2. 时长统计小工具
  3. mysql5.5
  4. sql语句汇总

学习目录

  1. mysql架构介绍
  2. 索引优化分析
  3. 查询截取分析
  4. mysql锁机制
  5. 主从复制

学习笔记

一、mysql架构介绍

  • 查询是否安装了mysql：#rpm -qa|grep -i mysql
  • 安装mysql的rpm包：**_#__ _**``**_rpm -ivh MySQL...linux...rpm_**
  • 查看mysql是否安装成功(有返回就代表成功了):
    • #**_ps -ef|grep mysql_**
    • **_# _**``**_cat /etc/group|grep mysql_**
  • 停止mysql服务：#**_service mysql stop_**
  • 初次安装后设置root密码：#**_/usr/bin/mysqladmin -u root password xxxyyyzzz_**
  • 设置完root密码后重新启动数据库命令行：#__mysql -u root -p (回车后再输入密码)
  • 设置开机自启动mysql：#__chkconfig mysql on

  • 安装目录分析

    • _/var/lib/mysql/ _mysql数据库文件的存放路径
    • /usr/bin 相关命令目录
    • /usr/share/mysql 配置文件目录

  • 修改配置文件

    • 将原先在 /usr/share/mysql/my-huge.cnf 拷贝一份到 /etc/my.cnf ，算是一种对配置文件的备份。#__cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
    • 查看字符集
      • #__show variables like 'character%';
      • #__show variables like 'char%';
    • mysql启停
      • #__service mysql start
      • #__service mysql stop

        宝塔linux安装的Mysql配置文件是 /etc/my.cnf

  • mysql底层结构

    • 连接层
    • 服务层
    • 引擎层
    • 存储层
  • 查看引擎：#__show engines;

二、索引优化分析

1. 性能下降sql满，执行时间长、等待时间长

  • 查询不行
  • 索引失效（单值、复合）
    • 单值索引
      • 索引名命名规范：**`idx表名单值索引字段名`**
      • 创建单值索引：
        • #__**`CREATE INDEX idx表名单值索引字段名 ON 表名(单值索引字段名);`**
        • #__**`ALTER TABLE 表名 ADD INDEX idx表名单值索引字段名 (单值索引字段名) ;`**
    • 复合索引
      • 复合索引命名规范：**`idx表名复合索引字段名驼峰命名`**
      • 创建复合索引：#__**`CREATE INDEX idx表名复合索引字段名驼峰命名 ON 表名(索引字段名, 索引字段名);`**
  • 关联查询join多（设计缺陷）
  • 服务器调优（缓存、线程）

2. 常见通用的Join查询

2.1 sql执行顺序

2.1.1 手写

select
from
join on
where
group by
having
order by
limit

2.1.2 机读

from
on
join
where
group by
having
select
distinct
order by
limit

2.2 7种join

  1. 图示

20201019101931.png
对应的7种sql语句如下

  1. SELECT * 
  2. FROM TableA A 
  3. INNER JOIN TableB B 
  4. ON A.Key = B.Key;
  1. SELECT * 
  2. FROM TableA A 
  3. LEFT JOIN TableB B 
  4. ON A.Key = B.Key;
  1. SELECT * 
  2. FROM TableA A 
  3. RIGHT JOIN TableB B 
  4. ON A.Key = B.Key;
  1. SELECT * 
  2. FROM TableA A 
  3. LEFT JOIN TableB B 
  4. ON A.Key = B.Key
  5. WHERE B.Key IS NULL;
  1. SELECT * 
  2. FROM TableA A 
  3. RIGHT JOIN TableB B 
  4. ON A.Key = B.Key
  5. WHERE A.Key IS NULL;
  1. SELECT * 
  2. FROM TableA A 
  3. FULL OUTER JOIN TableB B # mysql不支持
  4. ON A.Key = B.Key;
  1. SELECT * 
  2. FROM TableA A 
  3. FULL OUTER JOIN TableB B # mysql不支持
  4. ON A.Key = B.Key
  5. WHERE A.Key IS NULL 
  6.     OR B.Key IS NULL;
  1. 练习 ```sql

    P13 七种JOIN的SQL编写

    CREATE TABLE IF NOT EXISTS tbl_emp ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, deptId int(11) DEFAULT NULL, PRIMARY KEY (id) , KEY fk_dept_id(deptId) )ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS tbl_dept ( id int(11) NOT NULL AUTO_INCREMENT, deptName varchar(30) DEFAULT NULL, locAdd varchar(40) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

INSERT INTO tbl_dept(deptName,locAdd) VALUES(‘RD’, 11); INSERT INTO tbl_dept(deptName,locAdd) VALUES(‘HR’, 12); INSERT INTO tbl_dept(deptName,locAdd) VALUES(‘MK’, 13); INSERT INTO tbl_dept(deptName,locAdd) VALUES(‘MIS’, 14); INSERT INTO tbl_dept(deptName,locAdd) VALUES(‘FD’, 15);

INSERT INTO tbl_emp(NAME, deptId) VALUES(‘z3’, 1); INSERT INTO tbl_emp(NAME, deptId) VALUES(‘z4’, 1); INSERT INTO tbl_emp(NAME, deptId) VALUES(‘z5’, 1);

INSERT INTO tbl_emp(NAME, deptId) VALUES(‘w5’, 2); INSERT INTO tbl_emp(NAME, deptId) VALUES(‘w6’, 2);

INSERT INTO tbl_emp(NAME, deptId) VALUES(‘s7’, 3); INSERT INTO tbl_emp(NAME, deptId) VALUES(‘s8’, 4); INSERT INTO tbl_emp(NAME, deptId) VALUES(‘s9’, 51);

  1. ```sql
  2. # 1. 单独查 tbl_emp 表格
  3. SELECT * FROM tbl_emp;
  5. # 2. 单独查 tbl_dept 表格
  6. SELECT * FROM tbl_dept;
  8. # 3. 笛卡尔积 A * B
  9. SELECT * FROM tbl_emp, tbl_dept;
  11. # 4. A ∩ B
  12. SELECT * 
  13. FROM tbl_emp A
  14. JOIN tbl_dept B
  15.     ON A.deptId = B.id;
  17. # 5. A
  18. SELECT * 
  19. FROM tbl_emp A
  20. LEFT JOIN tbl_dept B
  21.     ON A.deptId = B.id;
  23. # 6. B
  24. SELECT * 
  25. FROM tbl_emp A
  26. RIGHT JOIN tbl_dept B
  27.     ON A.deptId = B.id;
  29. # 7. A - B
  30. SELECT * 
  31. FROM tbl_emp A
  32. LEFT JOIN tbl_dept B
  33.     ON A.deptId = B.id
  34. WHERE B.id IS NULL;
  36. # 8. B - A
  37. SELECT * 
  38. FROM tbl_emp A
  39. RIGHT JOIN tbl_dept B
  40.     ON A.deptId = B.id
  41. WHERE A.id IS NULL;
  43. # 9. A ∪ B
  44. SELECT * FROM tbl_emp A LEFT JOIN tbl_dept B ON A.deptId = B.id
  45. UNION
  46. SELECT * FROM tbl_emp A RIGHT JOIN tbl_dept B ON A.deptId = B.id;
  48. # 10. (A - B) ∪ (B - A) 或者可以表达为 (A ∪ B) - (A ∩ B)
  49. SELECT * FROM tbl_emp A LEFT JOIN tbl_dept B ON A.deptId = B.id WHERE B.id IS NULL
  50. UNION
  51. SELECT * FROM tbl_emp A RIGHT JOIN tbl_dept B ON A.deptId = B.id WHERE A.id IS NULL;

3. 索引简介

3.1 是什么

  • 索引是一种数据结构
  • 目的是提高查找效率
  • 排好序的快速查找数据结构
  • 影响 查找 和 排序
  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引以索引文件的形式存储在磁盘上
  • 我们平常说的索引，没有特别指名，都指B+树索引（多路搜索树，并不一定是二叉的）结构组织的索引。

3.2 优势

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

3.3 劣势

  • 索引也是占用空间的
  • 索引提高了查询效率，但是会降低更新表的速度，如对表INSERT、UPDATE、DELETE。每次对表的更新，都需要重新保存索引文件降低更新效率。
  • 花时间研究最优索引

3.4 mysql索引分类

  1. 单值索引：一个索引只含单独列，一个表可以有多个单值索引
  2. 唯一索引：索引列的值必须唯一，但允许有空值
  3. 复合索引：一个索引包含多个列
  4. 基本语法
    1. 创建
      1. CREATE [UNIQUE] INDEX indexName ON 表名(字段名(length));
      2. ALTER 表名 ADD [UNIQUE] INDEX [索引名] ON (字段名(length));
    2. 删除
      DROP INDEX 索引名 ON 表名;
    3. 查看
      SHOW INDEX FROM 表名;

3.5 mysql索引结构

  1. BTree索引
  2. Hash索引 （* 知道名字即可）
  3. full-text全文索引 （* 知道名字即可）
  4. R-Tree索引 （* 知道名字即可）

3.6 需要创建索引情况

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段
  3. 查询中余其他表关联的字段，外键关系建立索引
  4. 频繁更新的字段不适合创建索引
  5. Where条件里用不到的字段不创建索引
  6. 单键/组合索引选择问题？优先组合索引
  7. 查询中排序的字段，若通过索引去访问将大大提高排序速度
  8. 查询中统计或者分组的字段

3.7 不需要创建索引情况

  1. 表记录太少
  2. 经常增删改的表
  3. 数据重复且分布平均的表字段
    1. 表中有2000条记录，表索引列有1980个，索引选择性为 1980/2000=0.99，索引选择性越接近1，效率就越高

4. 性能分析

4.1 MySQL Query Optimizer

SQL语言SELECT查询优化器

4.2 MySQL常见瓶颈

  1. CPU: CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
  2. IO: 装入数据远大于内存容量的时候
  3. 服务器硬件性能瓶颈：top、free、iostat和vmstat 来查看系统的性能状态

4.3 Explain

  1. What？
    模拟优化器执行SQL查询语句，从而知道MySQL如何处理SQL语句的。分析查询语句或表结构的性能瓶颈

  2. Why?

    1. 表的读取顺序
    2. 数据读取操作的操作类型
    3. 哪些索引可以使用
    4. 哪些索引被实际使用
    5. 表之间引用
    6. 每张表多少行被优化器查询

  3. How？
    _Explain [SQL语句];

  4. Explain code

    • id
      • select 查询序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序
      • 三种情况
        • id相同，执行顺序由上至下
        • id不同， 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
        • id相同不同，同时存在，id大的先执行
      • id如果相同可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。
      • 决定了‘表的读取顺序’
    • select_type
      • 分类
        • simple 简单的select查询，不包含子查询或者union
        • primary 若包含任何复杂子部分，最外层查询标记为此
        • subquery 在select或where列表中包含了子查询
        • derived 在from列表中包含的子查询被标记为此。mysql会递归执行这些子查询，结果放入临时表中
        • union 若第二个SELECT出现在UNION之后，则被标记为UNION;
        • union result 从UNION表获取结果的SELECT
      • 决定了‘数据读取操作的操作类型’
    • table
    • type
      • 访问类型
      • 从最好到最差：
        system > const > eq_ref > ref > range > index > ALL
      • 至少达到 range 界别，最好达到 ref 级别
      • 分类
        • system 表只有一行记录（等于系统表），这是const类型的特例。平时不会出现，可以忽略不计
        • const 通过索引一次就可以找到，用于比较 primary key 或者 unique 索引，如果将主键置于where列表中，mysql能将该查询转换为一个常量
        • eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
        • ref 非唯一性索引扫描，返回匹配某个单独值的所有行，找到的结果不止一行
        • range 检索给定范围的行，使用一个索引来选择行。一般是当where中出现了 between、<、>、in 等的查询。
        • index 从索引中读取
        • all 从硬盘中读取
    • possible_keys
      • 显式可能应用在这张表中的索引，一个或多个。查询涉及到的字段若存在索引，则列出索引，但不一定实际使用
    • key
      • 实际使用的索引。如果为NULL，则没有使用索引
      • 查询中出现了覆盖索引，则该索引仅出现在key列表中
    • key_len
      • 表示索引中使用的字节数。不损失精确性的情况下，越短越好。
      • 索引字段的最大可能长度，并非实际使用长度。
    • ref
      • 显示索引的哪一列被使用了，如果可能的话，是一个常数。
      • 分析实际索引命中的列，如果有出现等于一个常量的情况，则输出一个 const
      • 决定了‘哪些索引被实际使用’
    • rows
      • 根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数
      • 决定了‘每张表多少行被优化器查询’
    • Extra
      • 包含了不适合在其他列中显示但是十分重要的额外信息
      • 分类
        • Using filesort 无法利用索引完成的称为‘文件排序’
        • Using temporary 使用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。
        • USING index 索引覆盖
      • 索引覆盖
        • 不需要读取数据行，能直接从所建的索引中查找到select需要的所有字段

  5. DEMO

20201020100936.png
执行顺序如下表

id select_type table type 执行语句
4 UNION t2 ALL select name,id from t2
3 DERIVED t1 ALL select id,name from t1 where other_column = ''
2 SUBQUERY t3 index select id from t3
1 PRIMARY system select d1.name ......
NULL UNION RESULT ALL 两个结果UNION操作

5. 索引优化

5.1 索引分析

  1. 单表
    • range 类型查询字段后面的索引失效
    • where中带范围的range会导致range后的索引失效，解决办法就是移除range类型的这个索引字段
    • 分析过程与截图 ```sql

      P31 执行文档

CREATE TABLE IF NOT EXISTS article( id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, author_id INT (10) UNSIGNED NOT NULL, category_id INT(10) UNSIGNED NOT NULL , views INT(10) UNSIGNED NOT NULL , comments INT(10) UNSIGNED NOT NULL, title VARBINARY(255) NOT NULL, content TEXT NOT NULL ); INSERT INTO article(author_id,category_id ,views ,comments ,title ,content )VALUES (1,1,1,1,’1’,’1’), (2,2,2,2,’2’,’2’), (1,1,3,3,’3’,’3’);

SELECT * FROM ARTICLE;

— # 1. 查询演示及初始化分析

— SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

— EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

— # 2. 根据where初步建立一个索引

— CREATE INDEX idx_article_categoryidCommentsViews ON article (category_id, comments, views);

— SHOW INDEX FROM article;

— EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

— # 3. 删除刚刚创建的索引 — DROP INDEX idx_article_categoryidCommentsViews ON article;

— — # 4. 去除range的字段，建立 index 字段的索引

— CREATE INDEX idx_article_categoryidViews ON article (category_id, views);

— SHOW INDEX FROM article;

— EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

  1. 分析：comments 的索引查找类型是 range，导致继续往后面查找views时不再是index查找，变成了文件查找，实际上没有利用到 views 的索引，导致问题出现。解决方式就是移除 范围查找的索引，仅仅保留等值index类型的索引。<br />![单表索引优化初始化分析.png](https://cdn.nlark.com/yuque/0/2020/png/339035/1603176942719-b9864052-bf6a-4147-b884-b1605d5a7ada.png#align=left&display=inline&height=125&margin=%5Bobject%20Object%5D&name=%E5%8D%95%E8%A1%A8%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96%E5%88%9D%E5%A7%8B%E5%8C%96%E5%88%86%E6%9E%90.png&originHeight=125&originWidth=1504&size=10365&status=done&style=none&width=1504)<br />![单表索引优化-建立所有where字段索引.png](https://cdn.nlark.com/yuque/0/2020/png/339035/1603176948361-7d658f11-c8ee-4c01-a3e1-82584ca6ea54.png#align=left&display=inline&height=246&margin=%5Bobject%20Object%5D&name=%E5%8D%95%E8%A1%A8%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96-%E5%BB%BA%E7%AB%8B%E6%89%80%E6%9C%89where%E5%AD%97%E6%AE%B5%E7%B4%A2%E5%BC%95.png&originHeight=246&originWidth=1513&size=19852&status=done&style=none&width=1513)<br />![单表索引优化-优化索引后.png](https://cdn.nlark.com/yuque/0/2020/png/339035/1603176952128-9905ac60-780f-43d8-9b01-5823c7801ba2.png#align=left&display=inline&height=272&margin=%5Bobject%20Object%5D&name=%E5%8D%95%E8%A1%A8%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96-%E4%BC%98%E5%8C%96%E7%B4%A2%E5%BC%95%E5%90%8E.png&originHeight=272&originWidth=1498&size=19559&status=done&style=none&width=1498)
  3. 2. 两表
  4.    - 左连接因为左边全查询，所以用不用索引不影响，关键点在右表是否使用了索引；右连接同理，关键点在左表是否使用了索引
  5.    - 总结
  6.       - **左连接**建立**右表索引**
  7.       - **右**连接建立**左**表索引
  8.    - 分析过程与截图
  9. ```sql
  10. # P32 执行文档
  12. # 代码准备
  13. CREATE TABLE IF NOT EXISTS `class`(
  14. `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  15. `card` INT (10) UNSIGNED NOT NULL
  16. );
  17. CREATE TABLE IF NOT EXISTS `book`(
  18. `bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  19. `card` INT (10) UNSIGNED NOT NULL
  20. );
  21. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  22. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  23. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  24. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  25. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  26. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  27. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  28. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  29. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  30. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  31. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  32. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  33. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  34. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  35. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  36. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  37. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  38. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  39. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  40. INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  42. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  43. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  44. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  45. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  46. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  47. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  48. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  49. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  50. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  51. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  52. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  53. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  54. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  55. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  56. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  57. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  58. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  59. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  60. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  61. INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
  63. -- # 1. 查询语句演示与初始分析
  64. -- select * from class left join book on class.card = book.card;
  65. -- 
  66. -- explain select * from class left join book on class.card = book.card;
  68. -- # 2. 添加 class 表索引
  69. -- create index idx_class_card on class(card);
  70. -- 
  71. -- show index from class;
  72. -- 
  73. -- explain select * from class left join book on class.card = book.card;
  75. -- # 3. 删除 class 表索引
  76. -- drop index idx_class_card on class ;
  78. -- # 4. 添加 book 表索引
  79. -- create index idx_book_card on book(card);
  80. -- 
  81. -- show index from book;
  82. -- 
  83. -- explain select * from class left join book on class.card = book.card;
  85. # 5. 删除 book 表索引
  86. -- drop index idx_book_card on book;

由以前知识知晓‘相同id，由上而下执行’，所以添加左连接添加右表class索引可以加快查询
未添加任何索引左连接默认情况.png
左连接，右表添加索引.png
左连接，左表添加索引.png

  1. 三表
    • 分析过程与截图 ```sql

      P33

初始化建表及添加数据

CREATE TABLE IF NOT EXISTS phone( phoneid INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, card INT (10) UNSIGNED NOT NULL )ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()20)));

— # 1. 查询与初步分析 — SELECT * FROM class — LEFT JOIN book ON class.card = book.card

— LEFT JOIN phone ON phone.card = book.card;

— EXPLAIN SELECT * FROM class — LEFT JOIN book ON class.card = book.card — LEFT JOIN phone ON phone.card = book.card;

— # 2. 添加索引后继续分析 — alter table book add index y(card);

— alter table phone add index z(card);

— EXPLAIN SELECT * FROM class — LEFT JOIN book — ON class.card = book.card — LEFT JOIN phone — ON phone.card = book.card;

  1. 分析：接上面知识点，多表连接需要在对应反向表创建索引<br />![三表索引优化-初始化查询与分析.png](https://cdn.nlark.com/yuque/0/2020/png/339035/1603178687299-365f45e3-7cda-4e45-ba70-0b3ce7493cbd.png#align=left&display=inline&height=356&margin=%5Bobject%20Object%5D&name=%E4%B8%89%E8%A1%A8%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96-%E5%88%9D%E5%A7%8B%E5%8C%96%E6%9F%A5%E8%AF%A2%E4%B8%8E%E5%88%86%E6%9E%90.png&originHeight=356&originWidth=856&size=17657&status=done&style=none&width=856)<br />![三表索引优化-添加索引后.png](https://cdn.nlark.com/yuque/0/2020/png/339035/1603178693986-f7da954a-f9d3-4da4-baf9-c6be922de162.png#align=left&display=inline&height=392&margin=%5Bobject%20Object%5D&name=%E4%B8%89%E8%A1%A8%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96-%E6%B7%BB%E5%8A%A0%E7%B4%A2%E5%BC%95%E5%90%8E.png&originHeight=392&originWidth=1146&size=20748&status=done&style=none&width=1146)
  3. 4. 结论
  4.    1. 尽可能减少 JOIN 语句中的 NestedLoop 的循环总次数；‘_**永远用小结果集驱动大结果集**_’；
  5.    1. 优先优化NestedLoop内层循环
  6.    1. 保证JOIN语句中被驱动表上JOIN条件字段已经被索引；
  7.    1. 当无法保证驱动表的JOIN条件字段被索引且内存资源充足的前提下，不要太林夕JoinBuffer的设置
  9. <a name="EBbXF"></a>
  10. #### 5.2 索引失效（避免）
  12. 1. 表格及数据准备
  13. ```sql
  14. CREATE TABLE staffs(
  15. id INT PRIMARY KEY AUTO_INCREMENT,
  16. `name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
  17. `age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
  18. `pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
  19. `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
  20. )CHARSET utf8 COMMENT'员工记录表';
  21. INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
  22. INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
  23. INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
  25. ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
  1. 案例
    1. 全值匹配我最爱
    2. 最佳左前缀法则
      • 如果索引了多列，要遵守最左前缀法则，查询从索引的最左前列开始并且 不跳过索引中的列
    3. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
    4. 存储引擎不能使用索引中范围条件右边的列
    5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
    6. mysql 在使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描
    7. is null，is not null 也无法使用索引
    8. like 以通配符ddd开头（’%abc…’）mysql索引失效会变成全表扫描的操作
      • 问题：解决like‘%字符串%’时索引不被使用的方法？覆盖索引
    9. 字符串不加单引号索引失效
    10. 少用or，用它来连接时索引会失效
  2. 代码 ```sql

    2. 最佳左前缀法则

    show index from staffs;

type反映类型，ref反映性能

EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July’;

EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July’ AND age = 25;

EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July’ AND age = 25 and pos = ‘dev’;

EXPLAIN SELECT * FROM staffs WHERE age = 23 and pos = ‘dev’;

EXPLAIN SELECT * FROM staffs WHERE pos = ‘dev’;

EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July’ AND pos = ‘dev’;

3. 不在索引上做操作

EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July’;

EXPLAIN SELECT * FROM staffs WHERE left(NAME, 4) = ‘July’;

EXPLAIN SELECT * FROM staffs WHERE left(NAME, 3) = ‘July’;

4. 范围条件后全失效

EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July’ AND age=25;

EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July’ AND age=25 AND pos=’manager’;

EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July’ AND age>25 AND pos=’manager’;

5. 尽量使用覆盖索引

EXPLAIN SELECT * FROM staffs WHERE NAME =’July’ AND age = 25 AND pos=’dev’;

EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME =’July’ AND age = 25 AND pos=’manager’;

EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME =’July’ AND age > 25 AND pos=’dev’;

EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME =’July’ AND age = 25;

6. 不等于

EXPLAIN SELECT * FROM staffs WHERE NAME =’July’;

EXPLAIN SELECT * FROM staffs WHERE NAME !=’July’;

7. is [not] null

EXPLAIN SELECT * FROM staffs WHERE NAME is null;

EXPLAIN SELECT * FROM staffs WHERE NAME is not null;

8. like 通配符开头

EXPLAIN SELECT * FROM staffs WHERE NAME like ‘%July%’;

EXPLAIN SELECT * FROM staffs WHERE NAME like ‘%July’;

EXPLAIN SELECT * FROM staffs WHERE NAME like ‘July%’;

  2. - P40 建表语句
  3. ```sql
  4. CREATE TABLE `tbl_user`(
  5. `id` INT(11) NOT NULL AUTO_INCREMENT,
  6. `name` VARCHAR(20) DEFAULT NULL,
  7. `age`INT(11) DEFAULT NULL,
  8. `email` VARCHAR(20) DEFAULT NULL,
  9. PRIMARY KEY(`id`)
  10. )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  12. INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
  13. INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
  14. INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
  15. INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');
  17. # before create index
  18. # 1
  19. EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
  20. # 2
  21. EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
  22. # 3
  23. EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';
  24. # 4
  25. EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
  26. # 5
  27. EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%';
  28. # 6
  29. EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
  30. # 7
  31. EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
  32. # 8
  33. EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%';
  35. # create index
  36. -- CREATE INDEX idx_user_nameAge ON tbl_user(NAME, age);
  1. # 9. 字符串不加单引号索引失效
  2. EXPLAIN SELECT * FROM staffs WHERE name='2000';
  4. EXPLAIN SELECT * FROM staffs WHERE name=2000;
  6. # 10. 少用or
  7. EXPLAIN SELECT * FROM staffs WHERE name='July' or name='z3';
  • 练习 ```sql create table test03( id int primary key not null auto_increment, c1 char(10), c2 char(10), c3 char(10), c4 char(10), c5 char(10));

insert into test03(c1,c2,c3,c4,c5) values (‘a1’,’a2’,’a3’,’a4’,’a5’); insert into test03(c1,c2,c3,c4,c5) values (‘b1’,’b2’,’b3’,’b4’,’b5’); insert into test03(c1,c2,c3,c4,c5) values (‘c1’,’c2’,’c3’,’c4’,’c5’); insert into test03(c1,c2,c3,c4,c5) values (‘d1’,’d2’,’d3’,’d4’,’d5’); insert into test03(c1,c2,c3,c4,c5) values (‘e1’,’e2’,’e3’,’e4’,’e5’);

select * from test03;

create index idx_test03_c1234 on test03(c1, c2, c3, c4); show index from test03;

问题：创建了复合索引idx_test03_c1234 根据一下SQL分析下索引使用情况？

explain select from test03 where c1=’a1’; explain select from test03 where c1=’a1’ and c2=’a2’; explain select from test03 where c1=’a1’ and c2=’a2’ and c3=’a3’; explain select from test03 where c1=’a1’ and c2=’a2’ and c3=’a3’ and c4=’a4’;

1

explain select * from test03 where c1=’a1’ and c2=’a2’ and c4=’a4’ and c3=’a3’;

2

explain select * from test03 where c2=’a2’ and c4=’a4’ and c3=’a3’ and c2=’a2’ and c1=’a1’;

3

explain select * from test03 where c1=’a1’ and c2=’a2’ and c3>’a3’ and c4=’a4’;

4

explain select * from test03 where c1=’a1’ and c2=’a2’ and c4>’a4’ and c3=’a3’;

5 c3作用是排序而不是查找

explain select * from test03 where c1=’a1’ and c2=’a2’ and c4=’a4’ order by c3;

6

explain select * from test03 where c1=’a1’ and c2=’a2’ and c4>’a4’ order by c3;

7

explain select * from test03 where c1=’a1’ and c2=’a2’ order by c3;

8 filesort

explain select * from test03 where c1=’a1’ and c2=’a2’ order by c4;

9

explain select * from test03 where c1=’a1’ and c5=’a5’ order by c2, c3;

10 filesort

explain select * from test03 where c1=’a1’ and c5=’a5’ order by c3, c2;

11 跟10的区别是c2=一个值已经是常量了，一个常量排序不影响结果

explain select * from test03 where c1=’a1’ and c2=’a2’ and c5=’a5’ order by c2, c3;

12

explain select * from test03 where c1=’a1’ and c2=’a2’ and c5=’a5’ order by c3, c2;

13

explain select * from test03 where c1=’a1’ and c4=’a4’ group by c2, c3;

14 Using where; Using temporary; Using filesort

explain select * from test03 where c1=’a1’ and c4=’a4’ group by c3, c2;

  2. <a name="kJmWa"></a>
  3. #### 5.3 一般性建议
  5. - 单键索引，尽量选择针对当前query过滤性更好的索引
  6. - 组合索引，过滤性最好的字段在索引字段顺序中，位置越靠前越好
  7. - 组合索引，能够包含query中where更多字段的索引
  8. - 分析统计信息调整query写法适合索引
  10. <a name="S03Xl"></a>
  11. #### 5.4 口诀
  12. 全值匹配我最爱，最左前缀要遵守<br />带头大哥不能死，中间兄弟不能断<br />索引列上少计算，范围之后全失效<br />LIKE百分写最右，覆盖索引不写星<br />不等空值还有or，索引失效要少写
  14. <a name="RRDbr"></a>
  15. ## 三、查询截取分析
  16. <a name="tkPCj"></a>
  17. ### 1. 查询优化
  19. - 生产环境分析步骤
  20.    1. 观察，至少线上跑一天，看看生产慢SQL情况
  21.    1. 开启慢查询日志，设置阈值，比如超过5秒就是慢SQL，抓取慢SQL
  22.    1. explain +慢SQL分析
  23.    1. show profile
  24.    1. SQL数据库服务器的参数调优
  25. - 生产环境排查总结
  26.    1. 慢查询开启并捕获
  27.    1. explain + 慢SQL分析
  28.    1. show profile 查询SQL在MySQL服务器执行细节和生命周期情况
  29.    1. SQL数据库服务器的参数调优
  31. <a name="usuir"></a>
  32. #### 1.1 小表驱动大表
  34. - (A in B) 当B表的数据集必须小于A表的数据集时，用 in 优于 exists
  35. - (A exists B) 当A表的数据集必须小于B表的数据集时，用 exists 优于 in
  36. - SELECT ... FROM table WHERE EXISTS (subquery)<br />**将主查询的数据放入子查询中做条件验证，根据验证结果（true/false）决定著查询的数据结果是否得以保存**
  38. **
  39. <a name="Cjxsv"></a>
  40. #### 1.2 order by 优化
  42. - 尽量使用 index 方式排序，避免使用 filesort 方式排序
  43. ```sql
  44.  create table tblA(
  45. #id int primary key not null auto_increment,
  46. age int,
  47. birth timestamp not null
  48. );
  50. insert into tblA(age, birth) values(22, now());
  51. insert into tblA(age, birth) values(23, now());
  52. insert into tblA(age, birth) values(24, now());
  54. create index idx_A_ageBirth on tblA(age, birth);
  56. select * from tblA;
  57. show index from tblA;
  59. # 1
  60. explain select * from tblA where age > 20 order by age;
  61. # 2
  62. explain select * from tblA where age > 20 order by age, birth;
  63. # 3. filesort
  64. explain select * from tblA where age > 20 order by birth;
  65. # 4. filesort
  66. explain select * from tblA where age > 20 order by birth, age;
  67. # 5. 覆盖索引
  68. explain select * from tblA where birth > '2016-01-28 00:00:00' order by age;
  69. # 6. filesort (order 默认升序，与实际顺序相反所以filesort)
  70. explain select * from tblA order by age ASC, birth DESC;
  • order by 使用 Index 方式排序需要满足的两情况：
    • order by 语句使用索引最左前列
    • where子句和order by子句条件列组合满足索引最左前列
  • 尽可能在索引列上排序
  • 如果不在索引列上，filesort有两种算法：双路排序和单路排序
  • 优化策略
    • sort_buffer_size
    • max_length_for_sort_data
    • order by 不使用 select *，只 query 需要的字段

2020-10-20_234118.png

1.3 group by 优化

  • group by 实质是先排序后分组，按照索引最佳左前缀
  • where高于having，能写在where就不要写having**

**

2. 慢查询日志

2.1 what？

  • 记录MySQL中响应时间超过阈值的语句。具体指超过 long_query_time 值得SQL语句
  • long_query_time 默认值为 10 （秒）
  • 默认情况下，慢查询日志处于关闭状态。除了调优需要，不建议启动

2.2 how？

  1. 查看是否开启及如何开启
    1. 默认： SHOW VARIABLES LIKE '%slow_query_log%';
    2. 开启：SET GLOBAL slow_query_log=1; 只对当前数据库生效，MySQL重启后失效
    3. 永久生效，修改my.cnf文件后重启MySQL服务器
      1. slow_query_log=1;
      2. slow_query_log_file=/var/lib/mysql/my-slow.log;
      3. long_query_time=3;
      4. log_output=FILE;
    4. 如果没配置慢查询日志存放路径，系统默认缺省 host_name-slow.log
  2. 哪些会记录进慢查询日志
    1. SHOW VARIABLES LIKE 'long_query_time%';
    2. 判断的是大于，即开区间
    3. 设置：SET GLOBAL long_query_time=3;
      1. 如果报错 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation，就去掉 GLOBAL 再试试
    4. select sleep(4); 睡眠4秒执行
    5. show global status like '%Slow_queries%'; 查询多少慢SQL

2.3 日志分析工具 mysqldumpslow

  • 安装：找到mysql的安装目录配置如下，在系统命令行内执行，不是在mysql命令行（因为我使用了宝塔linux，所以 /www/server/mysql 是我的mysql安装路径，可以修改为你自己的）
    • ln -s /www/server/mysql/bin/mysqldumpslow /usr/bin
  • 测试：mysqldumpslow**--help**
  • 得到返回记录集最多的10个SQL: mysqldumpslow -s r -t 10 /www/server/mysql/my-slow.log
  • 访问次数最多的10个SQL：mysqldumpslow -s c -t 10 /www/server/mysql/my-slow.log
  • 按照时间排序的前10条含有左连接的SQL：mysqldumpslow -s t -t 10 -g "left join" /www/server/mysql/my-slow.log
  • 避免爆屏结合 | 和 more 使用：mysqldumpslow -s c -t 10 /www/server/mysql/my-slow.log | more

    3. 批量数据脚本

    ```sql

    1. 建表 dept

    CREATE TABLE dept ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dename VARCHAR ( 20 ) NOT NULL DEFAULT “”, loc VARCHAR ( 13 ) NOT NULL DEFAULT “” );

2. 建表 emp

CREATE TABLE emp ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, #编号 ename VARCHAR(20) NOT NULL DEFAULT “”, # 名字 job VARCHAR(9) 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# 部门编号 );

3. 因为开启了 bin-log,必须为function指定一个参数

添加参数后，mysql重启，上述参数不会消失，永久方法是修改my.cnf添加 log_…=1

show variables like ‘log_bin_trust_function_creators’;

set global log_bin_trust_function_creators = 1;

3. 创建函数

定义结束标记

DELIMITER $

3.1 创建函数1：随机字符串

CREATE FUNCTION rand_string ( n INT ) RETURNS VARCHAR ( 255 ) BEGIN DECLARE chars_str VARCHAR ( 100 ) DEFAULT ‘abcdefghijklmnopqrstuvwxyz’; DECLARE return_str VARCHAR ( 255 ) DEFAULT ‘’; DECLARE i INT DEFAULT 0; WHILE i < n DO

  1.         SET return_str = CONCAT(
  2.             return_str,
  3.         SUBSTRING( chars_str, FLOOR( 1+RAND ()* 52 ), 1 ));
  5.     SET i = i + 1;
  7. END WHILE;
  8. RETURN return_str;

END $

3.2 创建随机数字

CREATE FUNCTION rand_num () RETURNS INT ( 5 ) BEGIN DECLARE i INT DEFAULT 0;

  1. SET i = FLOOR( 100 + RAND()* 10 );
  2. RETURN i;

END $

4. 创建存储过程

DELIMITER $ d

4.1 批提交的存储过程

CREATE PROCEDURE insert_emp ( IN START INT ( 10 ), IN max_num INT ( 10 )) BEGIN DECLARE i INT DEFAULT 0;# set autocommit = 0 把自动提交设置为0，手动提交

  1. SET autocommit = 0;
  2. REPEAT
  4.         SET i = i + 1;
  5.     INSERT INTO emp ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
  6.     VALUES
  7.         ((
  8.                 START + i 
  9.                 ),
  10.             rand_string ( 6 ),
  11.             'SALESMAN',
  12.             0001,
  13.             CURDATE(),
  14.             2000,
  15.             400,
  16.         rand_num ());
  17.     UNTIL i = max_num 
  18. END REPEAT;
  19. COMMIT;# 手动提交

END $

4.2 批提交的存储过程

CREATE PROCEDURE insert_dept ( IN START INT ( 10 ), IN max_num INT ( 10 ) ) BEGIN DECLARE i INT DEFAULT 0;

  1. # set autocommit = 0 
  2. # 把自动提交设置为0，手动提交
  3. SET autocommit = 0;
  4. REPEAT        
  5.         SET i = i + 1;
  6.         INSERT INTO dept ( deptno, dname, loc )
  7.             VALUES (
  8.                 (START + i),
  9.                 rand_string ( 10 ),
  10.                 rand_string ( 8 )
  11.             );
  12.         UNTIL i = max_num 
  13. END REPEAT;
  14. COMMIT;# 手动提交

END $

5. 调用存储过程

DELIMITER ; CALL insert_dept(100, 10); CALL insert_emp(100001, 500000); # 插入了40万条数据，花费了175秒，慢慎用

  2. <a name="bgjgH"></a>
  3. ### 4. Show Profile
  5. - 分析当前会话中语句执行的资源消耗情况
  6. - 默认关闭，并保存最近15次运行结果
  7. - 分析步骤
  8.    1. 是否支持 **`SHOW VARIABLES LIKE 'profiling';`**
  9.    1. 开启功能，默认关闭
  10.    1. 运行SQL
  11.    1. 查看结果 _**`show profiles;`**_
  12.    1. 诊断SQL _**`show profile cpu,block io for query 请求id;`**_
  13.       1. converting HEAP to MyISAM 查询结果太大，内存不够
  14.       1. Creating tmp table 创建临时表
  15.       1. Copying to tmp table on disk 把内存临时表复制到了磁盘
  16.       1. locked
  18. <a name="IiYKK"></a>
  19. ### 5. 全局查询日志
  21. - **只允许在测试环境用**
  22. - 配置启用
  23.    - 修改my.cnf配置文件
  24.    - general_log = 1
  25.    - general_log_file=/path/logfile
  26.    - log_output=FILE
  27. - 编码启用
  28.    - `# ``set global general_log=1;`
  29.    - `# ``set global log_output='TABLE';`
  30.    - 查看  `# ``select * from mysql.general_log;`
  33. <a name="0qsuB"></a>
  34. ## 四、mysql锁机制
  35. <a name="R9xuX"></a>
  36. ### 1. 概述
  38. - 为了保证数据的一致性、有效性而使用的一种机制
  39. - 分类
  40.    - 对数据操作（读/写）类型分
  41.       - 读锁（共享锁） 同一份数据多个读操作同时进行
  42.       - 写锁（排他锁）当前写操作未完成时，阻断其他写锁和读锁
  43.    - 对数据操作粒度分
  44.       - 表锁
  45.       - 行锁
  48. <a name="X2OgF"></a>
  49. ### 2. 三锁
  51. <a name="cA2Xo"></a>
  52. #### 2.1 表锁（偏读）
  54. - 特点
  55.    - 偏向MyISAM存储引擎，开销小，加锁快；
  56.    - 无死锁；
  57.    - 锁力度大，发生锁冲突概率高，并发度最低
  58. - 语法
  59.    - 手动增加表锁 `# `_**`lock table 表名 read(write), 表名2 read(write), 其他;`**_
  60.    - 查看哪些表上加了锁 `# `_**`show open tables;`**_
  61.    - 释放锁 `# `_**`unlock tables;`**_
  63. ```sql
  64. use big_data;
  66. create table mylock (
  67. id int not null primary key auto_increment,
  68. name varchar(20) default ''
  69. ) engine myisam;
  71. insert into mylock(name) values('a');
  72. insert into mylock(name) values('b');
  73. insert into mylock(name) values('c');
  74. insert into mylock(name) values('d');
  75. insert into mylock(name) values('e');

  • 读锁 | 操作步骤 | session_1 | session_2 | | —- | —- | —- | | 1 | 获得表mylock的READ锁 | 连接终端 | | 2 | 可查询该表记录 | 其他session也可查询该表记录 | | 3 | 不能查询该read锁之外的表 | 其他session可查询或更新未锁的表 | | 4 | 插入或更新锁定的表都会报错 | 其他session插入或更新锁定的表都会阻塞，一直等待获得锁 | | 5 | 释放锁 | session2 获得锁，之前陷入阻塞状态的插入操作得以完成 |

  • 写锁 | 操作步骤 | session_1 | session_2 | | —- | —- | —- | | 1 | 获得表mylock的WRITE锁 | 待 session_1 开启写锁后，session2在连接终端 | | 2 | 对锁定表查询+更新+插入操作都可以执行 | 其他session对锁定表的查询被阻塞，需要等待锁被释放；（因为mysql有缓存，如果没效果请换用查询锁定表内的其他数据） | | 3 | 释放锁 | session2 获得锁，之前陷入阻塞状态的操作得以完成 |

  • 总结

20201021150412.png

读锁阻塞写，不阻塞读；写锁把读写都阻塞

**

  • 表锁分析
    • show status like 'table%';
      • Table_locks_immediate 表级锁定的次数，可以立即获取锁的查询次数，每立即获取锁值加1
      • Tbale_locks_waited 出现表级锁定征用而发生的等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高寿命存在较严重的表级锁争用情况

2.2 行锁（偏写）

  • 特点
    • InnoDB存储引擎，开销大，加锁慢
    • 会出现死锁
    • 锁定力度最小，发生锁冲突概率低，并发度最高
    • InnoDB与MyISAM区别
      1. InnoDB支持事务
      2. 采用行级锁

  • 复习

    • acid(原子性、一致性、隔离性、持久性)
    • 问题
      • 更新丢失 后一个事务覆盖了前一个事务对某个数据的更改
      • 脏读 读到了修改的数据
      • 不可重复读 之前读一个数据，之后某个时间再去读这个数据已改变或者已删除
      • 幻读 读到了新增的数据
    • show variables like 'tx_isolation';

  • 案例

    1. CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;
    2. INSERT INTO test_innodb_lock VALUES(1,'b2');
    3. INSERT INTO test_innodb_lock VALUES(3,'3');
    4. INSERT INTO test_innodb_lock VALUES(4, '4000');
    5. INSERT INTO test_innodb_lock VALUES(5,'5000');
    6. INSERT INTO test_innodb_lock VALUES(6, '6000');
    7. INSERT INTO test_innodb_lock VALUES(7,'7000');
    8. INSERT INTO test_innodb_lock VALUES(8, '8000');
    9. INSERT INTO test_innodb_lock VALUES(9,'9000');
    10. INSERT INTO test_innodb_lock VALUES(1,'b1');
    11. CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
    12. CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
    13. SET autocommit=0;
    • 无索引行升级为表锁
      • 比如varchar类型没有带单引号导致直接锁全表查询（升级成了表锁）
    • 间隙锁危害
      • 范围内的数据即使不存在也会被加锁，这种加在不存在数据上的锁就是间隙锁。会阻塞并发时向不存在的数据插入
    • select * from 表名 where 筛选条件__for update; 锁定某一行后，其他的操作会被锁阻塞，知道锁定行的会话提交commit。
  • 行锁分析
    • 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 系统启动到现在共等待多少次

当等待次数过大且平均花费时间过长就要优化优化了

  • 优化分析
    • 尽可能使用索引
    • 控制事务大小
    • 低级别事务隔离

2.3 页锁

  • 特点
    • 开锁和加锁时间介于表锁和行锁之间
    • 会出现死锁
    • 粒度介于表锁和行锁之间
    • 并发度一般

五、主从复制

1. 基本原理

  1. master将改变记录到二进制日志
  2. slave将master的二进制日志拷贝到中继日志
  3. slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步且串行化的。

2. 基本原则

  • 每个slave只有一个master
  • 每个slave只能有一个唯一的服务器ID
  • 每个master可以有多个salve

3. 最大问题

延时

4. 一主一从常见配置

  • mysql版本一致，且后台已经运行
  • 主从 配置都在mysqld 节点下，都是小写
  • 同一网段下可以ping通