您当前的位置： 首页 >  sql

white camel

暂无认证

  • 1浏览

    0关注

    442博文

    0收益

  • 0浏览

    0点赞

    0打赏

    0留言

私信
关注
热门博文

MySQL高级 一 MySQL结构图、各种索引介绍(索引优化)、Explain性能分析、单表查询优化、排序分组优化、锁机制

white camel 发布时间：2021-01-08 13:51:42 ，浏览量：1

转载自 NyimaのMySQL高级

MySQL高级

口诀 : 全职匹配我最爱，最左前缀要遵守 带头大哥不能死，中间兄弟不能断 索引列上少计算，范围之后全失效 LIKE 百分写最右，覆盖索引不写 不等空值还有 OR，索引影响要注意 VARCHAR 引号不可丢，SQL 优化有诀窍

一、Mysql逻辑架构 1、整体架构
  • 了解MySQL体系结构
  • 一条SQL语句的坎坷之旅（MySQL底层执行流程分析）
写在开篇：
  • 对于信息技术人员来说，世界上最受欢迎的开源数据库系统的体系结构非常重要。MySQL在世界范围内受欢迎的原因很多，但主要原因之一是它的体系结构，尽管有许多大型公司，例如Oracle，Microsoft SQL和DB2，但MySQL的体系结构使其成为大多数开发人员的独特且首选的选择。在本文中，我们将讨论MySQL关系数据库管理系统的内部体系结构。本文适用于新手数据库管理员，数据库开发人员，软件开发人员以及对使用MySQL数据库感兴趣的人员。
体系结构图：（高清图，请点击放大查看）

在这里插入图片描述

体系介绍：

Client Connectors 接入方。支持很多协议(JDBC、ODBC、.NET、PHP、Python、PERL、C 等)

Management Serveices & Utilities 系统管理和控制工具，mysqldump、 mysql复制集群、分区管理等

Connection Pool 连接池：管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求

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

Parser 解析器，SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的

Optimizer 查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化

Cache和Buffer（高速缓存区） 查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据

pluggable storage Engines 插件式存储引擎。存储引擎是MySql中具体的与文件打交道的子系统

File System 文件系统，数据、日志（redo，undo）、索引、错误日志、查询记录、慢查询等

在这里插入图片描述

二、性能与JOIN 1、性能下降原因 索引失效

单值索引

创建语句

CREATE INDEX idx_表名_字段名 ON 表名(字段名);

复合索引

CREATE INDEX idx_表名_字段名1字段名2... ON 表名(字段名1, 字段名2 ...);
关联太多JOIN
  • 内连接、外连接的表不要过多
服务器调优及参数设置 2、SQL执行加载顺序 手写顺序

img

随着 Mysql 版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序

下面是经常出现的查询顺序：

img

img

img

3、7种JOIN

img

建表语句 
CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `address` VARCHAR(40) DEFAULT NULL, PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL, empno INT NOT NULL, PRIMARY KEY (`id`), KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,NULL,100010);
JOIN查询
  • 笛卡尔积
SELECT * FROM t_dept, t_emp;

t_dept共20条记录，t_emp共6条记录。两表共同查询后共120条记录

  • 内连接
SELECT * FROM t_emp a INNER JOIN t_dept b ON  a.deptId = b.id;

img

查询结果为主表中所有记录，如果从表有匹配项则显示，如果从表没有匹配项则显示null。

  • 左外连接 (左边为主表, 右边为从表) - 从表没有的用NULL替换
SELECT * FROM t_emp a LEFT JOIN t_dept b ON  a.deptId = b.id;

img

  • 右外连接
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON  a.deptId = b.id;

img

  • 左外连接取左表的独有部分
SELECT * FROM t_emp a LEFT JOIN t_dept b ON  a.deptId = b.id WHERE a.deptId IS NULL;

img

  • 右外连接取右表的独有部分
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id WHERE a.deptId IS NULL;

img

注意：判断字段是否为NULL时，不能使用’=’

因为

= NULL

的结果不会报错，但是结果永远为false。所以必须使用

IS NULL

来进行判空

  • 全外连接

MySQL不支持全外连接，要查询两个表的全集，需要合并两个查询结果，所以要使用 UNION 关键字

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId = b.id
UNION
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id;

img

  • 查询两表独有内容
SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId = b.id WHERE b.id IS NULL
UNION
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id WHERE a.deptId IS NULL;

img

三、索引优化 1、什么是索引

什么叫做覆盖索引？

  • 解释一 : 就是select的数据列只用从索引中就能够取得，换句话说查询列要被所使用的索引覆盖 (覆盖索引不需要回表查询)。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息。

  • 注：遇到以下情况，执行计划不会选择覆盖查询。
  1. select选择的字段中含有不在 索引 中的字段 ，即索引没有覆盖全部的列。
  2. where条件中不能含有对索引进行like的操作。

在这里插入图片描述

  • 聚簇索引, 非聚簇索引参考上面; 非聚簇索引必须要回表查询吗? 什么是回表查询, 非聚簇索引也称为二次索引, 也称为辅助索引, 首先不是必须要回表查询的, 如果建立的索引(联合索引), 查询的列, 都是索引列, 此时就不需要回表查询; (覆盖索引)
  • 如果select * 查, 此时除了索引列不需要回表查之外, 其他列都需要, 在叶子结点中根据指向聚簇索引的指针, 去聚簇索引树中进行查询数据。
  • 有时候回表查询太多, mysql底层就不走索引, 直接就走全表扫描.
  • MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质： 索引是数据结构。 可以简单理解为：排好序的快速查找数据结构
  • 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

img

  • 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用 二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录
  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上
2、索引的优缺点 优点
  • 提高数据检索的效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
缺点
  • 虽然索引大大提高了查询速度，同时却 会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
3、索引的分类 基本语法

  • 创建

    CREATE [UNIQUE] INDEX [indexName] ON table_name(column);

  • 删除

    DROP INDEX [indexName] ON table_name;

  • 查看

    SHOW INDEX FROM table_name;
分类

  • 聚簇索引 (主键索引) – 在Innodb中 (聚簇索引底层生成的B+树, 叶子结点存储的就是数据)

  • 单值索引

    • 定义：即一个索引只包含单个列，一个表可以有多个单列索引

    • 语法：

      --和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200), 
PRIMARY KEY(id), 
KEY (customer_name) --单值索引
);

--单独创建单值索引
CREATE INDEX idx_customer_name ON customer(customer_name);

  • 唯一索引

    • 定义：索引列的值必须唯一，但允许有空值

    • 语法：

      --和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200), 
PRIMARY KEY(id), 
KEY (customer_name), --单值索引
UNIQUE (customer_no) --唯一索引
);

--单独创建唯一索引
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

  • 主键索引

    • 定义：设定为主键后数据库会自动建立索引，innodb为聚簇索引

    • 语法：

      --和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200), 
PRIMARY KEY(id) --主键索引
);

--单独创建主键索引
ALTER TABLE customer ADD PRIMARY KEY customer(customer_no);

--删除主键索引
ALTER TABLE customer DROP PRIMARY KEY;

--修改建主键索引
必须先删除掉(drop)原索引，再新建(add)索引

  • 复合索引 (联合索引 / 辅助索引) --> 底层生成的B+树, 叶子结点存储的是 符合索引key和主键索引的地址

    • 定义：即一个索引包含多个列

    • 语法：

      --和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200), 
PRIMARY KEY(id), 
KEY (customer_name), --单值索引
UNIQUE (customer_no), --唯一索引
KEY (customer_no,customer_name) --复合索引
);

--单独创建复合索引
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);
4、MySQL的索引 B树与B+树
  • 这就是B+树的叶子结点, 它就是InnoDB中的页, 用来存放表数据 在这里插入图片描述 在这里插入图片描述

img

B+树除了叶子结点, 其他结点都存储的是key, 也就是列也就是索引列, 它们相当于一个指针, 叶子结点用来存储表中的数据. img

在这里插入图片描述

从上到下 —> 走索引 从左到右 —> 走全表扫描 在这里插入图片描述

区别

MySQL 索引底层为什么选择B+Tree

  • B树 的 关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；
  • B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录(表数据)只放在叶子节点中

为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？

  • B+树的磁盘读写代价更低
    • B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点 的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了
  • B+树的查询效率更加稳定
    • 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当
MySQL中的B+树

全表扫描: 就是直接查询B+树的叶子结点, 不从根节点开始通过索引查询

主键索引

img

  • MySQL在创建表时，会根据主键来创建主键索引（如果没有主键，会用一个隐藏值来作为主键）。主键索引所构建的B+树，表中所有的记录都存放在了树的最后一层。且与一般的B+树不同的是：叶子节点间的指针是双向的
复合索引 (辅助索引)

img

如果是按照查询全部列, select * , 此时除了查询出走索引的列, 还需要通过主键索引去表中回查其他列的数据, 这个过程也叫做回表查询

  • 如果查询的是 select b, c, d 此时就直接走索引查询了
  • 最左前缀原则, 就是通过 B+树节点上的索引列, 进行匹配, 看是否可以从上到下进行索引查找.
  • select * from t_emp where b = 1 and c = 1; 此时根据最左前缀, 是可以走索引的; 下图 1 1 * 可以找到 1 1 1
  • select * from t_emp where c = 1 and d = 1; 此时就不可走索引; * 1 1, 无法确定谁开头, 所以不能走索引 在这里插入图片描述 注意: 为什么要根据最左前缀才能使索引生效? 因为联合索引添加的列, 也就是索引列, 都会存储在B+树的非叶子结点中, 存储的结构是和创建联合索引的顺序是一致的; 如果不遵守最左前缀原则, 则想通过索引是查询不到的.

创建复合索引时，会将作为复合索引字段的值进行排序并放在B+树的最后一层中，同时还会将其对应的主键值放在其后。如：

a（主键）bcde2111a

其中字段a为主键，字段bcd共同作为复合索引，此时存放在最后一层的数据就是：111（复合索引） 2（主键索引）

根据这个特点，可以看出复合索引具有以下使用方法

  • 最左前缀原则：使用复合索引的顺序必须和创建的顺序一致

  • 覆盖索引的同时，可以带上主键字段，如

    SELECT a, b, c, d FROM t_emp;

    因为 主键字段和复合索引一起存放在了复合索引说产生的B+树的最后一层。如果需要a字段，无需进行全表扫描

  • 如果进行范围查找，可能会进行全表扫描，这取决于处在范围内记录的多少

    • 记录多，从复合索引映射到主键索引的次数过多(回表查询次数太多)，成本过高，会直接进行全表扫描

      EXPLAIN SELECT * FROM t_emp WHERE age > 1;

      img

    • 记录少，先使用复合索引，然后映射到全表中的对应记录上

      EXPLAIN SELECT * FROM t_emp WHERE age > 80;

      img

    • 但是使用覆盖索引，无论记录多少，都会用到索引

      EXPLAIN SELECT age, name FROM t_emp WHERE age > 1;

      img

  • 不带WHERE也可以通过复合索引查找到主键+复合索引的记录

  EXPLAIN SELECT id, age, name, deptId FROM t_emp ;

img

5、索引的使用场景 适合索引的场景
  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其它表关联的字段，外键关系建立索引
  • 单键/组合索引的选择问题，组合索引性价比更高
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  • 查询中统计或者分组字段
不适合索引的场景
  • 表记录太少（有无索引差别不大）
  • 经常增删改的表或者字段
  • Where 条件里用不到的字段不创建索引
  • 过滤性不好的不适合建索引（重复性较高，比如国籍、性别之类的字段）
四、Explain 性能分析

不会MySQL调优？来来瞅瞅SQL的执行计划吧

1、概念
  • 使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈
2、用法 
--EXPLAIN + SQL语句，如：
EXPLAIN SELECT * FROM person;

Explain 执行后返回的信息：

img

3、表头字段介绍 准备工作 
CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));
id：SQL语句中表的读取顺序

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

  • id相同：执行顺序为 从上至下执行

    EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id;

    img

    查询时，表的加载顺序为t1, t2, t3

  • id不同：执行顺序为 id大的先执行

    EXPLAIN SELECT t2.id FROM t2 WHERE t2.id = 
(SELECT t1.id FROM t1 WHERE t1.id = 
(SELECT t3.id FROM t3)
);

    img

    查询时，表的加载顺序为t3, t1, t2

  • id相同又不同： 执行顺序为

    • id不同时，值较大的先执行
    • id相同时，从上至下执行
    EXPLAIN SELECT * FROM (SELECT t3.id FROM t3) s1, t2 WHERE s1.id = t2.id;

    img

    查询时，表的加载顺序为t3, t2, 虚表dervied2

    • 其中dervied2 的 2，为 id = 2
select_type：查询操作类型

select_type代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

elect_type 属性含义SIMPLE简单的 select 查询,查询中不包含子查询或者 UNIONPRIMARY查询中若包含任何复杂的子部分，最外层查询则被标记为 PrimaryDERIVED在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) MySQL 会递归执行这些子查询, 把结果放在临时表里SUBQUERY在SELECT或WHERE列表中包含了子查询DEPEDENT SUBQUERY在SELECT或WHERE列表中包含了子查询,子查询基于外层UNCACHEABLE SUBQUERY无法使用缓存的子查询UNION若第二个SELECT出现在UNION之后，则被标记为UNION； 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVEDUNION RESULT从UNION表获取结果的SELECT

  • SUBQUERY 和 DEPEDENT SUBQUERY

    • 都是 WHERE 后面的条件，SUBQUERY 是单个值（=），DEPEDENT SUBQUERY 是一组值（IN）

  • UNCACHEABLE SUBQUERY

    • 当使用了 @@来引用系统变量 的时候，不会使用缓存

  • UNION 和 UNION RESULT

    EXPLAIN SELECT * FROM t_emp a LEFT JOIN  t_dept b ON a.deptId = b.id WHERE b.id IS NULL
UNION
SELECT * FROM t_emp a RIGHT JOIN  t_dept b ON a.deptId = b.id WHERE a.deptId IS NULL;

    img

table：表的来源
  • table表示这个数据是基于哪张表的
type：访问类型 (重点)
  • type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

--常见的顺序为
system > const > eq_ref > ref > range > index > all

一般来说，得保证查询 至少达到 range 级别，最好能达到 ref

类型名含义SYSTEM表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计CONST表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量EQ_REF唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描REF非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行， 然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体RANGE只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现 了 between、、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而 结束语另一点，不用扫描全部索引INDEX出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组ALLFull Table Scan，将遍历全表以找到匹配的行

  • REF

    --其中deptId为索引，且用到了' = '
EXPLAIN SELECT * FROM t_emp WHERE deptId = 3;

    img

  • RANGE

    --其中deptId为索引，用到了 BETWEEN...AND... , IN , > , < 等范围查询
EXPLAIN SELECT * FROM t_emp WHERE deptId > 3;

    img

  • INDEX

-- 使用了覆盖索引
  --其中deptId为索引，查找了整张表时，用到了索引
  EXPLAIN SELECT deptId FROM t_emp;

img

  • ALL

    --其中name为非索引
EXPLAIN SELECT name FROM t_emp;

    img

possible_key：可能用到的索引
  • 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一 定被查询实际使用
key：实际使用的索引
  • 实际使用的索引。如果为NULL，则没有使用索引
EXPLAIN SELECT * FROM t_emp WHERE id = 1 AND deptId = 1;

img

MySQL推测可能用到主键索引和idx_dept_id索引，实际上用到的是主键索引

覆盖索引
  • 当查找的字段与建立的索引的匹配（查询的字段都是索引，但不需要全是索引）时，会发生覆盖索引。MySQL推测使用的索引为NULL，而实际上会使用索引

有以下两种解释

  • select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖
  • 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引

注意：要使用覆盖索引，则只取出需要的列（被令为索引），不要使用 SELECT *

--其中id和deptId都为索引
EXPLAIN SELECT id, deptId FROM t_emp;

img

img

key_len：索引使用字节数

根据列修饰的类型计算, 比如 int类型占4个字节

  • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引
  • ken_len 越长，说明索引使用的越充分
ref：显示被使用的索引的具体信息
  • ref显示索引的哪一列被使用了，如果可能的话，可以是一个常数。哪些列或常量被用于查找索引列上的值
EXPLAIN SELECT * FROM t_dept, t_emp WHERE t_emp.deptId = t_dept.id;

img

rows：被查询的行数
  • rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好！

验证

--先删除索引
DROP INDEX idx_dept_id ON t_emp;

--查找
EXPLAIN SELECT * FROM t_dept, t_emp WHERE t_emp.deptId = t_dept.id;

--再创建索引
CREATE INDEX idx_dept_id ON t_emp(deptId);

--查找
EXPLAIN SELECT * FROM t_dept, t_emp WHERE t_emp.deptId = t_dept.id;

结果如下

  • 未使用索引时，一共需要查询26行

    img

  • 使用索引后，一共需要查询6行

    img

Extra：额外重要信息

其他的额外重要的信息

  • Using filesort：使用外部索引排序（未使用用户创建的索引）

    解决: 查询的时候按照最左前缀原则查询;

    • 说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引 完成的排序操作称为“文件排序”
    • 出现 Using filesort 说明SQL语句设计的不好，没有按照创建的索引进行排序，或者未按照索引指定的顺序进行排序

    演示

    --创建符合索引
CREATE INDEX idx_emp_empno_age ON t_emp(empno, age);

--进行查询操作，通过 age 字段进行排序（未按照复合索引顺序进行排序查询）
EXPLAIN SELECT empno FROM t_emp WHERE empno >100002 ORDER BY age;

--进行查询操作，通过 empno 或者 empno + age 字段进行排序（按照复合索引顺序进行排序查询）
EXPLAIN SELECT empno FROM t_emp WHERE empno >100002 ORDER BY empno;
EXPLAIN SELECT empno FROM t_emp WHERE empno >100002 ORDER BY empno, age;

    结果

    img

    img

    img

  • Using temporary

造成的原因: order by / group by ; 在联合索引的情况下, 未按照最左前缀访问索引列

  • 使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by
  • 出现 Using temporary 说明SQL语句设计的非常不好，可能是因为没有按照顺序使用复合索引

演示

--进行查询操作， 通过 age 字段进行分组（未按照复合索引顺序进行排序查询）
CREATE INDEX idx_emp_empno_age ON t_emp(empno, age);

----进行查询操作，通过 empno 或者 empno + age 字段进行分组（按照复合索引顺序进行排序查询）
EXPLAIN SELECT empno FROM t_emp WHERE empno >100002 GROUP BY empno;
EXPLAIN SELECT empno FROM t_emp WHERE empno >100002 GROUP BY empno, age;

结果

img

img

img

重要结论

注意: 如果创建了复合索引，一定要按照复合索引的顺序来使用 (最左前缀原则)，否则会使得性能大幅下降

  • Using index

    • Using index 代表表示相应的 select 操作中使用了 覆盖索引 (Covering Index)，详见key：实际用到的索引——覆盖索引，避免访问了表的数据行，效率不错！
    • 如果同时出现 using where，表明索引被用来执行索引键值的查找
    • 如果没有同时出现 using where，表明索引只是用来读取数据而非利用索引执行查找。

    演示

    --查询 age 字段，使用了WHERE
EXPLAIN SELECT age FROM t_emp WHERE age >100000;

--查询 empno 和 age 字段，未使用WHERE
EXPLAIN SELECT empno, age FROM t_emp;

--查询 empno 和 name 字段 （name字段不是索引）
EXPLAIN SELECT empno, name FROM t_emp;

    结果

    img

    img

    img

  • Using where

    • 表明使用了 where 过滤

  • Using join buffer

    • 使用了连接缓存

  • impossible where

    • where 子句的值总是 false，不能用来获取任何元组

  • select tables optimized away

    • 在没有 GROUP BY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操 作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
五、单表查询优化 1、全值匹配很快捷

SQL语句

--建立符合索引（age, deptId, name）
CREATE INDEX idx_emp_ade ON t_emp(age, deptId, NAME);

--查找
EXPLAIN SELECT empno FROM t_emp WHERE age = 90;
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1;
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1 AND name = '风清扬';

--和上一条SQL语句中WHERE后字段的顺序不同，但是不影响查询结果
EXPLAIN SELECT empno FROM t_emp WHERE deptId = 1 AND name = '风清扬' AND age = 90;

对应结果

img

img

img

img

可以看到，复合索引都被用到了，并且SQL中查询字段的顺序，跟使用索引中字段的顺序，没有关系。优化器会在不影响 SQL 执行结果的前提下，自动地优化

结论：全职匹配我最爱指的是，查询的字段按照顺序在索引中都可以匹配到

2、最佳左前缀法则

SQL语句

--先删除之前创建的单值索引
DROP INDEX idx_dept_id ON t_emp; 

--查询，未按照最佳左前缀法则
EXPLAIN SELECT empno FROM t_emp WHERE deptId = 1;
EXPLAIN SELECT empno FROM t_emp WHERE deptId = 1 AND name = '风清扬';

--查询，部分按照最佳左前缀法则（age字段和复合索引匹配，但name没有）
EXPLAIN SELECT empno FROM t_emp WHERE  age = 90 AND name = '风清扬';

--查询，完全按照最佳左前缀法则
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1;
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1 AND name = '风清扬';

对应结果

img

img

img

img

img

  • 可以看到，查询 字段与索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效

原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用

3、索引列上进行 (计算、函数、(自动 or 手动)类型转换), 索引失效
  • 不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），可能会导致索引失效而转向全表扫描

SQL语句

--直接查询
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1 AND NAME = '风清扬';

--使用MySQL函数查询
EXPLAIN SELECT empno FROM t_emp WHERE LEFT(age,2) = 90 AND deptId = 1 AND name = '风清扬';

对应结果

img

img

可以看出，当age字段使用了left函数以后，导致索引完全失效

结论：等号左边无计算

4、范围之后全失效

SQL语句

--范围查询
EXPLAIN SELECT empno FROM t_emp WHERE age > 50 AND deptId = 1 AND name = '风清扬';
EXPLAIN SELECT empno FROM t_emp WHERE age = 50 AND deptId > 1 AND NAME = '风清扬';

--未使用范围查询
EXPLAIN SELECT empno FROM t_emp WHERE age = 50 AND deptId = 1 AND name = '风清扬';

对应结果

img

img

img

可以看出，当对age字段使用范围查询后，使得范围后面的索引失效了

建议：将可能做范围查询的字段的索引顺序放在最后

结论：使用范围查询后，如果范围内的记录过多，会导致索引失效，因为从自定义索引映射到主键索引需要耗费太多的时间，反而不如全表扫描来得快

5、覆盖索引多使用

SQL语句

--查询所有字段
EXPLAIN SELECT * FROM t_dept WHERE id = 1;

--查询索引字段
EXPLAIN SELECT id FROM t_dept WHERE id = 1;

对应结果

img

img

结论：使用覆盖索引（Using index）会提高检索效率

6、使用不等会失效

在使用不等于(!= 或者)时，有时会无法使用索引会导致全表扫描

SQL语句

--SQL语句中有不等于
EXPLAIN SELECT * FROM t_emp WHERE age != 90;
EXPLAIN SELECT * FROM t_emp WHERE age  90;

--SQL语句中没有不等于
EXPLAIN SELECT * FROM t_emp WHERE age = 90;

对应结果

img

img

img

结论：尽量不要使用不等于

7、使用NULL值要小心

在使用

IS NULL
或者
IS NOT

时，可能会导致索引失效

但是如果允许字段为空，则

  • IS NULL 不会导致索引失效
  • IS NOT NULL 会导致索引失效

img

SQL语句

EXPLAIN SELECT * FROM t_emp WHERE age IS NULL;

EXPLAIN SELECT * FROM t_emp WHERE age IS NOT NULL;

对应结果

img

img

8、模糊查询加右边

要使用模糊查询时，百分号最好加在右边，而且进行模糊查询的字段必须是单值索引

SQL语句

--创建单值索引
CREATE INDEX idx_emp_name ON t_emp(NAME);

--进行模糊查询
EXPLAIN SELECT * FROM t_emp WHERE name LIKE '%风';
EXPLAIN SELECT * FROM t_emp WHERE name LIKE '风%';
EXPLAIN SELECT * FROM t_emp WHERE name LIKE '%风%';

对应结果

img

img

img

可以看出，对索引使用模糊查询时，只有当百分号在右边，索引为单值索引且模糊查询语句在最右边时，索引才会生效

其他情况均失效了

但是有时必须使用其他类型的模糊查询，这时就需要用覆盖索引来解决索引失效的问题

SQL语句

EXPLAIN SELECT name FROM t_emp WHERE name LIKE '%风';
EXPLAIN SELECT name FROM t_emp WHERE name LIKE '风%';

EXPLAIN SELECT NAME FROM t_emp WHERE name LIKE '%风%';

对应结果

img

img

img

结论：对索引进行模糊查询时，最好在右边加百分号。必须在左边或左右加百分号时，需要用到覆盖索引来提升查询效率

9、字符串加单引号

当字段为字符串时，查询时必须带上单引号。否则会发生自动的类型转换，从而发生全表扫描

用于查询的表

img

其中card_id字段为varchar类型，且设置了单值索引

SQL语句

--使用了单引号
EXPLAIN SELECT card_id FROM person WHERE card_id = '1';

--未使用单引号，发生自动类型转换
EXPLAIN SELECT card_id FROM person WHERE card_id = 1;

对应结果

img

img

10、尽量不用or查询

如果使用or，可能导致索引失效。所以要减少or的使用，可以使用 union all 或者 union 来替代：

SQL语句

--使用or进行查询
EXPLAIN SELECT * FROM t_emp WHERE age = 90 OR NAME = '风清扬';

对应结果

img

口诀
  • 全职匹配我最爱，最左前缀要遵守 带头大哥不能死，中间兄弟不能断 索引列上少计算，范围之后全失效 LIKE 百分写最右，覆盖索引不写 不等空值还有 OR，索引影响要注意 VARCHAR 引号不可丢，SQL 优化有诀窍
六、关联查询优化

建表语句

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`bookid`)
);
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
1、LEFT JOIN优化

SQL语句

--未建立索引时的左外连接查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

--左表（class）建立索引
CREATE INDEX idx_class_card ON class(card);

--再次执行查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

--去掉左表索引
DROP INDEX idx_class_card ON class;

--右表建立索引
CREATE INDEX idx_book_card ON book(card);

--再次执行查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

对应结果

img

img

img

结论

  • 在优化关联查询时，只有在 被驱动表上建立索引才有效
  • left join时，左侧的为驱动表，右侧为被驱动表
2、INNER JOIN优化

SQL语句

--查询操作，目前索引在book表的card上，class表和book表的位置不会改变查询结果
EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;
EXPLAIN SELECT * FROM book INNER JOIN class ON book.card = class.card;

--删除book表中的几条记录
DELETE FROM book WHERE bookid 50 ORDER BY age, deptId;

--按照复合索引顺序进行排序
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age;
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age, deptId;
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age, deptId, name;

--不按照复合索引顺序进行排序（无 age 字段），发生Using filesort
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY deptId, name;

--不按照复合索引顺序进行排序（索引顺序打乱），发生Using filesort
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY deptId, name, age;

--排序时部分(age)升序，部分(deptId)降序，发生Using filesort
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age ASC, deptId DESC;

--排序时都为降序
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age DESC, deptId DESC;

--排序时，在前面的字段为常量时（非范围）
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age = 50 ORDER BY deptId, name;
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age = 50 AND deptId>10000 ORDER BY deptId, name;

对应结果

img

img

img

img

img

img

img

img

img

img

结论：

要想在排序时使用索引，避免 Using filesort，首先需要发生索引覆盖，其次

  • ORDER BY 后面字段的顺序 要和复合索引的顺序完全一致
  • ORDER BY 后面的索引必须按照顺序出现，排在后面的可以不出现
  • 要进行升序或者降序时，字段的排序顺序必须一致。不能一部分升序，一部分降序，可以都升序或者都降序
  • 如果复合索引前面的字段作为常量出现在过滤条件中，排序字段可以为紧跟其后的字段
MySQL的排序算法

当发生 Using filesort 时，MySQL会根据自己的算法对查询结果进行排序

  • 双路排序
    • MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
    • 从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段
    • 简单来说，取一批数据，要对磁盘进行了两次扫描，众所周知，I\O 是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序
  • 单路排序
    • 从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO,但是它会使用更多的空间， 因为它把每一行都保存在内存中了
    • 存在的问题：在 sort_buffer 中，方法 B 比方法 A 要多占用很多空间，因为方法 B 是把所有字段都取出, 所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多 路合并），排完再取取 sort_buffer 容量大小，再排……从而多次 I/O。也就是本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失
  • 优化Using filesort
    • 增大 sort_butter_size 参数的设置
      • 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M 之间调整
    • 增大 max_length_for_sort_data 参数的设置
      • mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data
      • 提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大， 明显症状是高的磁盘 I/O 活动和低的处理器使用率。（1024-8192 之间调整）
    • 减少 select 后面的查询的字段
      • 查询的字段减少了，缓冲里就能容纳更多的内容了，间接增大了sort_buffer_size

img

2、GROUP BY 优化
  • 优化方式和 ORDER BY 类似，参考ORDER BY 的优化方式即可
八、截取查询分析 1、慢日志查询

通过慢查询日志, 将超过10s执行的sql, 收集起来. 这些就是慢sql

  • 拿到这些sql后, 我们可以再通过explain来分析sql, 达到优化sql的目的
概念
  • MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中
  • 具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为 10，意思是运行10秒以上的语句
  • 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能 收集超过5秒的sql，结合之前explain进行全面分析
使用

  • 默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数

  • 如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。 慢查询日志支持将日志记录写入文件

SQL 语句描述备注SHOW VARIABLES LIKE ‘%slow_query_log%’查看慢查询日志是否开启默认情况下 slow_query_log 的值为 OFFset global slow_query_log=1开启慢查询日志SHOW VARIABLES LIKE ‘long_query_time%’查看慢查询设定阈值单位：秒set long_query_time=1设定慢查询阈值单位：秒
  • 运行查询时间长的 sql，可以打开慢查询日志查看
九、MySQL锁机制

InnoDB 事务与锁的前世今生

1、表锁
  • MylSAM引擎使用表锁，并且不支持事务

SQL语句

--展示表是否加锁
SHOW OPEN TABLES;

--加锁 read (读锁) write (写锁)
LOCK TABLE table1 read(write), table2 read(write)...

--全部解锁
UNLOCK TABLES;
读锁
  • 主机A给表加上 表锁（读锁） 以后
    • 主机A和其他主机都可以读取该表的信息
    • 主机A不能读取库中其他表的信息，但其他主机可以读取库中所有表的信息
    • 如果要修改被锁表的信息
      • 主机A如果对表进行修改，会修改失败
      • 其他主机对表进行修改，会被阻塞，直到锁被释放

演示

  • 给dept表加锁并查询状态

    LOCK TABLE dept READ;

SHOW OPEN TABLES;

    img

读取

  • 两个客户端分别读取dept表的信息，都能读出来

    SELECT id FROM dept WHERE id = 1;

  • 客户端A（加锁端）A读取其他表信息，读取失败

    SELECT * FROM t_emp;

    img

  • 其他客户端读取度其他表信息，读取成功

    SELECT * FROM t_emp;

    img

修改

  • 客户端A对表中内容进行修改，修改失败

    DELETE FROM dept WHERE id = 1;

    img

  • 客户端B对表中内容进行修改，进入阻塞状态

    DELETE FROM dept WHERE id = 1;

img

  • 从客户端A解锁后，客户端B修改成功

    UNLOCK TABLES;

    img

写锁

主机A给表加上 表锁（写锁） 以后

  • 主机A可以读取该表信息，但其他主机读取时，会进入阻塞状态，知道读锁被释放
  • 主机A不能读取库中其他表的信息，但其他主机可以读取库中除该表以外所有表的信息
  • 如果要修改被锁表的信息
    • 主机A如果对表进行修改，修改成功
    • 其他主机对表进行修改，会被阻塞，直到锁被释放

演示

  • 给dept表加上写锁并查看

    LOCK TABLE dept WRITE;

SHOW OPEN TABLES;

    img

读取

  • 客户端A查询该表内容，查询成功；读取其他表，读取失败

    SELECT * FROM dept;

SELECT * FROM t_emp;

    img

img

  • 其他表读取该表信息，进入阻塞状态

    SELECT * FROM dept;

    img

  • 释放后，读取成功

    UNLOCK TABLES;

修改

  • 客户端A修改该表内容，修改成功

    DELETE dept WHERE id = 2;

    img

  • 客户端A修改其他表内容，修改失败

    DELETE FROM t_emp WHERE id = 2;

    img

  • 其他客户端修改该表内容，进入阻塞状态

    DELETE FROM t_emp WHERE id = 2;
总结

读锁不会阻塞读，只会阻塞写。但是写锁会阻塞读和写。

2、行锁

InnoDB使用行锁，并且支持事务，事务相关可参考 MySQL基础

特点

如果两个客户端对同一条记录进行修改

  • 客户端A修改后，未提交（未commit），此时客户端B修改，则会阻塞
  • 客户端A修改后，提交后，客户端B再修改，则不会阻塞

如果两个客户端分别对不同的记录进行修改，则不会被阻塞

修改同一条记录

--关闭自动提交
SET autocommit = 0;

--客户端A、B查询id=2的记录
SELECT * FROM t_emp WHERE id = 2;

--客户端A进行修改操作（将年龄改为了80），但未提交
UPDATE t_emp SET age = 80 WHERE id = 2;

--客户端A进行查询
SELECT * FROM t_emp WHERE id = 2;

--客户端B进行查询
SELECT * FROM t_emp WHERE id = 2;

--客户端B进行修改（客户端A未提交）
UPDATE t_emp SET age = 90 WHERE id = 2;

--客户端A提交
COMMIT;

--客户端B提交
COMMIT;

对应结果

客户端A查询结果

img

客户端B查询结果

img

客户端A修改后A查询

img

客户端A修改后B查询

img

客户端A修改，未提交，此时B进行修改，被阻塞

img

客户端A提交后，B修改成功

img

修改不同记录

--客户端A对id=2的年龄进行修改
UPDATE t_emp SET age = 90 WHERE id = 2;

--客户端B对id=3的年龄进行修改
UPDATE t_emp SET age = 30 WHERE id = 3;

--客户端A，B分别提交
COMMIT;
COMMIT;

因为InnoDB使用行锁，对于不同行的操作，不会出现阻塞现象

索引失效

索引失效，行锁变表锁

当索引失效后，即使多个客户端操作的不是同一条记录，如果未提交，其他客户端也会进入阻塞状态

所以要避免索引失效

间隙锁危害 概念

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁

对于键值在条件范围内但并不存在的记录，叫做**“间隙(GAP)**” ，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。

危害

因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无 法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

演示 
--查询表记录，此处没有id=2的记录
SELECT * FROM t_emp;

--客户端A进行范围查询，但是范围内没有id=2的记录
UPDATE t_emp SET deptId = 1 WHERE id>1 AND id
关注
打赏
1661428283
查看更多评论

最近更新

热门博客

[ 申请 ]友情链接：

ClashX教程 绘画宝宝 配音宝宝
0.0468s