搜索到
12
篇与
MySQL
的结果
-
MySQL-隐式类型转换可能导致索引失效以及可能的查询数据异常 MySQL-隐式类型转换导致索引失效以及可能的查询数据异常前言数据库优化是一个任重而道远的任务，想要做优化必须深入理解数据库的各种特性。在开发过程中我们经常会遇到一些原因很简单但造成的后果却很严重的疑难杂症，这类问题往往还不容易定位，排查费时费力最后发现是一个很小的疏忽造成的，又或者是因为不了解某个技术特性产生的。于数据库层面，最常见的恐怕就是索引失效了，且一开始因为数据量小还不易被发现。但随着业务的拓展数据量的提升，性能问题慢慢的就体现出来了，处理不及时还很容易造成雪球效应，最终导致数据库卡死甚至瘫痪。造成索引失效的原因可能有很多种，相关技术博客已经有太多了，今天我要记录的是隐式转换造成的索引失效。数据准备首先使用存储过程生成 1000 万条测试数据， 测试表一共建立了 7 个字段（包括主键），num_int和num_str保存的是和ID一样的顺序数字，其中num_str是字符串类型。 type1和type2保存的都是主键对 5 的取模，目的是模拟实际应用中常用类似 type 类型的数据，但是type2是没有建立索引的。 str1和str2都是保存了一个 20 位长度的随机字符串，str1不能为NULL，str2允许为NULL，相应的生成测试数据的时候我也会在str2字段生产少量NULL值（每 100 条数据产生一个NULL值）。-- 创建测试数据表 DROP TABLE IF EXISTS `test1`; CREATE TABLE `test1` ( `id` int NOT NULL, `num_int` int NOT NULL DEFAULT 0, `num_str` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', `type1` int NOT NULL DEFAULT 0, `type2` int NOT NULL DEFAULT 0, `str1` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', `str2` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_num_int`(`num_int` ASC) USING BTREE, INDEX `idx_num_str`(`num_str` ASC) USING BTREE, INDEX `idx_type1`(`type1` ASC) USING BTREE, INDEX `idx_str1`(`str1` ASC) USING BTREE, INDEX `idx_str2`(`str2` ASC) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- 创建存储过程 DROP PROCEDURE IF EXISTS pre_test1; DELIMITER; CREATE PROCEDURE `pre_test1`() BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; WHILE i < 10000000 DO SET i = i + 1; SET @str1 = SUBSTRING(MD5(RAND()),1,20); -- 每100条数据str2产生一个null值 IF i % 100 = 0 THEN SET @str2 = NULL; ELSE SET @str2 = @str1; END IF; INSERT INTO test1 (`id`, `num_int`, `num_str`, `type1`, `type2`, `str1`, `str2`) VALUES (CONCAT('', i), CONCAT('', i), CONCAT('', i), i%5, i%5, @str1, @str2); -- 事务优化，每一万条数据提交一次事务 IF i % 10000 = 0 THEN COMMIT; END IF; END WHILE; END; DELIMITER; -- 执行存储过程 CALL pre_test1();数据量比较大，还涉及使用MD5生成随机字符串，所以速度有点慢，稍安勿躁，耐心等待即可。1000 万条数据，30分钟左右才跑完（实际时间跟你电脑硬件配置有关）。这里贴几条生成的数据，大致长这样。SQL 测试先来看这组 SQL，一共四条，我们的测试数据表num1是int类型，num2是varchar类型，但是存储的数据都是跟主键id一样的顺序数字，两个字段都建立有索引。SELECT * FROM `test1` WHERE num_int = 10000; SELECT * FROM `test1` WHERE num_int = '10000'; SELECT * FROM `test1` WHERE num_str = 10000; SELECT * FROM `test1` WHERE num_str = '10000';这四条 SQL 都是有针对性写的，1、2 查询的字段是 int 类型，3、4 查询的字段是varchar类型。1、2 或 3、4 查询的字段虽然都相同，但是一个条件是数字，一个条件是用引号引起来的字符串。这样做有什么区别呢？先不看下边的测试结果你能猜出这四条 SQL 的效率顺序吗？经测试这四条 SQL 最后的执行结果却相差很大，其中 124 三条 SQL 基本都是瞬间出结果，大概在 0.01~0.05 秒，在千万级的数据量下这样的结果可以判定这三条 SQL 性能基本没差别了。但是第三条 SQL，多次测试耗时基本在 4.5~4.8 秒之间。为什么 34 两条 SQL 效率相差那么大，但是同样做对比的 12 两条 SQL 却没什么差别呢？查看一下执行计划，下边分别 1234 条 SQL 的执行计划数据：可以看到，124 三条 SQL 都能使用到索引，连接类型都为ref，扫描行数都为 1，所以效率非常高。再看看第三条 SQL，没有用上索引，所以为全表扫描，rows直接到达 1000 万了，所以性能差别才那么大。仔细观察你会发现，34 两条 SQL 查询的字段num2是varchar类型的，查询条件等号右边加引号的第 4 条 SQL 是用到索引的，那么是查询的数据类型和字段数据类型不一致造成的吗？如果是这样那 12 两条 SQL 查询的字段num1是int类型，但是第 2 条 SQL 查询条件右边加了引号为什么还能用上索引呢。查阅 MySQL 相关文档发现是隐式转换造成的隐式转换MySQL 8.0 的官方文档：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html，以下规则描述了比较操作如何进行转换：如果一个或两个参数是NULL，则比较的结果是NULL，但NULL-safe <=> 相等比较运算符除外。对于NULL <=> NULL，结果为真。无需转换。如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较。如果两个参数都是整数，则将它们作为整数进行比较。如果不与数字比较，十六进制值将被视为二进制字符串。如果其中一个参数是 a TIMESTAMP或 DATETIME列，而另一个参数是常量，则在执行比较之前将常量转换为时间戳。这样做是为了对 ODBC 更友好。这不适用于 的参数 IN()。为了安全起见，在进行比较时，请始终使用完整的日期时间、日期或时间字符串。例如，要在使用 BETWEEN日期或时间值时获得最佳结果，请使用CAST()将值显式转换为所需的数据类型。来自一个或多个表的单行子查询不被视为常量。例如，如果子查询返回要与值进行比较的整数DATETIME ，则比较将作为两个整数进行。整数不会转换为时间值。要将操作数作为 DATETIME值进行比较，请使用 CAST()将子查询值显式转换为DATETIME.如果其中一个参数是十进制值，则比较取决于另一个参数。如果另一个参数是十进制或整数值，则将参数作为十进制值进行比较，如果另一个参数是浮点值，则将其作为浮点值进行比较。在所有其他情况下，参数将作为浮点（双精度）数字进行比较。例如，字符串和数字操作数的比较是作为浮点数的比较进行的。按照上述规则的最后一条，我们的查询SQL中，字符串与整数的比较会被转换成两个浮点数比较，左边是字符串类型 "1" 转换成浮点数为1.0，右边 INT类型的 1 转换成浮点数 1.0 。根据官方文档的描述，我们的第 23 两条 SQL 都发生了隐式转换，第 2 条 SQL 的查询条件num1 = '10000'，左边是int类型右边是字符串，第 3 条 SQL 相反，那么根据官方转换规则第 7 条，左右两边都会转换为浮点数再进行比较。先看第 2 条 SQL：SELECT * FROMtest1WHERE num1 = '10000'; 左边为 int 类型10000，转换为浮点数还是10000，右边字符串类型'10000'，转换为浮点数也是10000。两边的转换结果都是唯一确定的，所以不影响使用索引。第 3 条 SQL：SELECT * FROMtest1WHERE num2 = 10000; 左边是字符串类型'10000'，转浮点数为 10000 是唯一的，右边int类型10000转换结果也是唯一的。但是，因为左边是检索条件，'10000'转到10000虽然是唯一，但是其他字符串也可以转换为10000，比如'10000a'，'010000'，'10000'等等都能转为浮点数10000，这样的情况下，是不能用到索引的。关于这个隐式转换我们可以通过查询测试验证一下，先插入几条数据，其中num2='10000a'、'010000'和'10000'：INSERT INTO `test1` (`id`, `num_int`, `num_str`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000001', '10000', '10000a', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523'); INSERT INTO `test1` (`id`, `num_int`, `num_str`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000002', '10000', '010000', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523'); INSERT INTO `test1` (`id`, `num_int`, `num_str`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000003', '10000', ' 10000', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523');然后使用第三条 SQL 语句SELECT * FROM test1 WHERE num_str = 10000;进行查询：从结果可以看到，后面插入的三条数据也都匹配上了。那么这个字符串隐式转换的规则是什么呢？为什么num_str='10000a'、'010000'和'10000'这三种情形都能匹配上呢？查阅相关资料发现规则如下：不以数字开头的字符串都将转换为0。如'abc'、'a123bc'、'abc123'都会转化为0；以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止。比如'123a'会转换为123，'0123abc'会转换为0123也就是123，'03.8abc'会转换为3.8，其他同理。现对以上规则做如下测试验证：SELECT 123 = '123a'; # 1 SELECT 123 = '0123a'; # 1 SELECT 3.8 = '03.8abc'; # 1 SELECT -3.80 = '-03.8000abc'; # 1如此也就印证了之前的查询结果了。再次写一条 SQL 查询 str1 字段：SELECT * FROM test1 WHERE str1 = 1234;分析和总结通过上面的测试我们发现 MySQL 使用操作符的一些特性：当操作符左右两边的数据类型不一致时，会发生隐式转换。当查询字段为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。当查询字段为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。所以，我们在写 SQL 时一定要养成良好的习惯，查询的字段是什么类型，等号右边的条件就写成对应的类型。特别当查询的字段是字符串时，等号右边的条件一定要用引号引起来标明这是一个字符串，否则会造成索引失效触发全表扫描1、索引列是字符串时，如果传入的条件参数是整数，会先转换成浮点数，再全表扫描，导致索引失效；2、条件参数要尽可能与列的类型相同，避免隐式转换，或者把传入的条件参数转换成索引列的类型。参考阅读MySQL 5.7官方文档： https://dev.mysql.com/doc/refman/5.7/en/type-conversion.htmlMySQL 8.0 的官方文档：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html浅析 MySQL 的隐式转换：https://xiaomi-info.github.io/2019/12/24/mysql-implicit-conversion/MySQL-隐式类型转换导致索引失效 https://mp.weixin.qq.com/s/DGEvqiHPhf3BrutgheC4WgMySQL性能优化：MySQL中的隐式转换造成的索引失效 https://www.guitu18.com/post/2019/11/24/61.html附录1、MySQL 中字符串转浮点型时的规则如下：不以数字开头的字符串都将转换为0：SELECT CAST('abc' AS UNSIGNED) -----------------------+ 0|以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止：SELECT CAST(' 0123abc' AS UNSIGNED) ----------------------------+ 123| -
MySQL原理及优化实战-笔记 一、索引的概述1.为什么要使用索引在海量数据中进行查询某条记录的场景是经常发生的，那么如何提升查询性能，就跟要查询的数据字段是否有索引有关系。如果字段加了索引，那么查询的性能就非常快！——就是为了快！索引为什么快？索引到底是什么？在使用索引的是要注意什么样的事项？2.索引是什么查字典的方式？“数”shu--通过目录来查，能够快速的定位到目标数据所在的页码。没有使用索引的时候，数据的查询需要进行多次IO读写，这样的性能较差——全表扫描的过程。为数据库的某个字段创建索引，相当是为这个字段的内容创建了一个目录。通过这个目录可以快速的实现数据的定位，也就是通过索引能够快速的找到某条数据所在磁盘的位置。现在的疑问？索引存放位置索引的分类及如何创建索引使用了哪种数据结构：各种数据结构的查询性能进行分析3.索引存放的位置对于mac系统在/usr/local/mysql文件夹中，对于win系统c:/programdata/mysql(隐藏文件夹)InnoDB存储引擎的表：将索引和数据存放在同一个文件里。（为什么？有什么优势？）*.ibdMyISAM存储引擎的表：索引和数据分开两个文件来存储。索引：*.MYI ; 数据：MYD4.索引的分类主键索引：主键自带索引效果，也就意味着通过主键来查询表中的记录，性能是非常好的。普通索引：为普通列创建的索引。创建索引的命令：# 格式 create index 索引名称 on 表名(列名) # 例子 create index idx_name on employees(name)唯一索引：就像是唯一列，列中的数据是唯一的。比普通索引的性能要好。# 格式 create unique index 索引名称 on 表名(列名) # 例子 create unique index idx_unique_name on employees(name)联合索引（组合索引）：一次性为表中的多个字段一起创建索引，最左前缀法则（如何命中联合索引中的索引列）。注意：一个联合索引建议不要超过5个列# 格式 create index 索引名称 on 表(列1,列2,列3) # 例子 create index idx_name_age_position on employees(name,age,position)全文索引进行查询的时候，数据源可能来自于不同的字段或者不同的表。比如去百度中查询数据，千锋教育，来自于网页的标题或者网页的内容 。MyISAM存储引擎支持全文索引。在实际生产环境中，并不会使用MySQL提供的MyISAM存储引擎的全文索引功能来是实现全文查找。而是会使用第三方的搜索引擎中间件比如ElasticSearch（多）、Solr。二、索引使用的数据结构使用索引查找数据性能很快，避免了全表扫描的多次磁盘IO读写。但是我们发现，使用索引实际上也需要在索引中查找数据，而且数据量是一样的，那么凭什么索引就能快呢？这就跟索引使用了哪种数据结构支持快速查找。什么叫数据结构：存放数据的结构。比如：数组、链表、栈、堆、队列等等这些概念。1.线性表：线性的维护数据的顺序。对于线性表来说，有两种数据结构来支撑：线性顺序表：相邻两个数据的逻辑关系和物理位置是相同的。线性链式表：相邻两个数据的逻辑关系和物理存放位置没有关系。数据是有先后的逻辑关系，但是数据的物理存储位置并不连续。单向链表：能够通过当前结点找到下一个节点的位置，以此来维护链表的逻辑关系结点结构： 数据内容+下一个数据的指针双向链表：能够通过当前结点找到上一个或下一个节点的位置，双向都可找。结点结构： 上一个数据的指针+数据内容+下一个数据的指针顺序表和链式表的区别：- 数组：进行数据的查询性能（可以通过数组的索引/下标） ：时间复杂度（比较次数）/空间复杂度（算法需要使用多少个变量空间） 数组的查询性能非常好： 时间复杂度是O(1)。 数组的增删性能是非常差的：- 链表：查询的性能是非常差的： 时间复杂度是O(n)。 增删性能是非常好的：2.栈、队列、串、广义表栈：先进后出，有顺序栈、链式栈队列：先进先出，有顺序队列、链式队列串：String 定长串、StringBuffer/Stringbuilder动态串广义表：更加灵活的多维数组，可以在不同的元素中创建不同的维度的数组。3.树查找树的查找性能是明显比线性表的性能要好，那么接下来我们就要学习这么几种树：1）多叉树非二叉树2）二叉树一个结点最多只能有2个子结点，可以是0、1、2子结点。3）二叉查找树二叉查找树的查找性能是ok的，查询性能跟树的高度有关，树的高度又根你插入数据的顺序有关系。特点：二叉树的根结点的数值是比所有左子树的结点的数值大，比右子树的几点的数值小。这样的规律同样满足于他的所有子树。4）平衡二叉树（理想概念的树）我们知道二叉查找树不能非常智能的维护树的高度，因此二叉查找树在某些情况下查询性能是不ok的，此时平衡二叉树就出现了。特点： 平衡二叉树中的树及其所有子树都应满足：左子树和右子树的深度差不能超过1如果平衡二叉树不满足这个特点，那么平衡二叉树要进行自己旋转，如何自己旋转：左旋、右旋、双向（先左后右、先右后左）5）红黑树（平衡二叉树的一种体现）平衡二叉树为了维护树的平衡，在一旦不满足平衡的情况就要进行自旋，但是自旋会造成一定的系统开销。因此红黑树在自旋造成的系统开销和减少查询次数之间做了权衡。因此红黑树有时候并不是一颗平衡二叉树。红黑树已经是在查询性能上得到了优化，但索引依然没有使用红黑树作为数据结构来存储数据，因为红黑树在每一层上存放的数据内容是有限的，导致数据量一大，树的深度就变得非常大，于是查询性能非常差。因此索引没有使用红黑树。6）B树B树允许一个结点存放多个数据。这样可以使更小的树的深度来存放更多的数据。但是，B树的一个结点中到底能存放多少个数据，决定了树的深度。通过数值计算，B树的一个结点最多只能存放15个数据，因此B树依然不能满足海量数据的查询性能优化。7）B+树B+树的特点：非叶子结点冗余了叶子结点中的键。叶子结点是从小到大、从左到右排列的叶子结点之间提供了指针，提高了区间访问的性能只有叶子结点存放数据，非叶子结点是不存放数据的，只存放键8）哈希表使用哈希表来存取数据的性能是最快的，O(1)，但是不支持范围查找（区间访问）三、InnoDB和MyISAM的区别InnoDB和MyISAM都是数据库表的存储引擎。那么在互联网公司，或者追求查询性能的场景下，都会使用InnoDB作为表的存储引擎。为什么？1.InnoDB引擎——聚集索引把索引和数据存放在一个文件中，通过找到索引后就能直接在索引树上的叶子结点中获得完整的数据。可以实现行锁/表锁2.MyISAM存储引擎——非聚集索引把索引和数据存放在两个文件中，查找到索引后还要去另一个文件中找数据，性能会慢一些。除此之外，MyISAM天然支持表锁，而且支持全文索引。四、索引常见的面试题1.问题一：为什么非主键索引的叶子节点存放的数据是主键值如果普通索引中不存放主键，而存放完整数据，那么就会造成：数据冗余，虽然提升了查询性能，但是需要更多的空间来存放冗余的数据维护麻烦：一个地方修改数据，需要在多棵索引树上修改。2.问题二：为什么InnoDB表必须创建主键创建InnoDB表不使用主键能创建成功吗？如果能创建功能，能不能为这张表的普通列创建索引？如果没有主键，MySQL优化器会给一个虚拟的主键，于是普通索引会使用这个虚拟主键——也会造成性能开销。为了性能考虑，和设计初衷，那么创建表的时候就应该创建主键。3.问题三：为什么使用主键时推荐使用整型的自增主键1）为什么要使用整型：主键-主键索引树-树里的叶子结点和非叶子结点的键存放的是主键的值，而且这颗树是一个二叉查找树。数据的存放是有大小顺序的。整型： 大小顺序是很好比较的字符串：字符串的自然顺序的比较是要进行一次编码成为数值后再进行比较的。（字符串的自然顺序，A Z）uuid随机字符串2）为什么要自增：如果不用自增： （10 1 6。 200。 18。29）使用不规律的整数来作为主键，那么主键索引树会使用更多的自旋次数来保证树索引树的叶子节点中的数据是从小到大-从左到右排列，因此性能必然比使用了自增主键的性能要差！五、联合索引和最左前缀法则1.联合索引的特点在使用一个索引来实现多个表中字段的索引效果。2.联合索引是如何存储的3.最左前缀法则最左前缀法则是表示一条sql语句在联合索引中有没有走索引（命中索引/不会全表扫描）# 创建联合索引 create index idx_a_b_c on table1(a,b,c); # sql语句有没有命中索引 select * from table1 where a = 10; select * from table1 where a = 10 and b=20; select * from table1 where a = 10 and b=20 and c=30; select * from table1 where b = 10; select * from table1 where b = 10 and c=30; select * from table1 where a = 10 and c=30; select * from table1 where c = 30; select * from table1 where a = 10 and c = 30 and b = 20; (abc全走)=》mysql有一个内部优化器 会做一次内部优化。 六、SQL优化SQL优化的目的是为了SQL语句能够具备优秀的查询性能，实现这样的目的有很多的途径：工程优化如何实现：数据库标准、表的结构标准、字段的标准、创建索引SQL语句的优化：当前SQL语句有没有命中索引。1.工程优化如何实现参考《MySQL军规升级版》2.Explain执行计划——SQL优化神器得知道当前系统里有哪些SQL是慢SQL，查询性能超过1s的sql，然后再通过Explain工具可以对当前SQL语句的性能进行判断——为什么慢，怎么解决。要想知道哪些SQL是慢SQL，有两种方式，一种是开启本地MySQL的慢查询日志；另一种是阿里云提供的RDS（第三方部署的MySQL服务器），提供了查询慢SQL的功能。explain SELECT * from employees where name like "customer100%"通过在SQL语句前面加上explain关键字，执行后并不会真正的执行sql语句本身，而是通过explain工具来分析当前这条SQL语句的性能细节：比如是什么样的查询类型、可能用到的索引及实际用到的索引，和一些额外的信息。3.MySQL的内部优化器在SQL查询开始之前，MySQL内部优化器会进行一次自我优化，让这一次的查询性能尽可能的好。当前执行的SQLexplain select * from tb_book where id=1; show warnings;内部优化器优化后的效果：/* select#1 */ select '1' AS `id`,'千锋Java厉害' AS `name` from `db_mysql_pro`.`tb_book` where true4.select_type列关闭 MySQL 对衍生表的合并优化：set session optimizer_switch='derived_merge=off'; 执行了这样的计划：EXPLAIN select (select 1 from tb_author where id=1) from (select * from tb_book where id=1) der;derived：第一条执行的sql是from后面的子查询，该子查询只要在from后面，就会生成一张衍生表，因此他的查询类型：derivedsubquery：在select之后 from之前的子查询primary：最外部的selectsimple：不包含子查询的简单的查询union：使用union进行的联合查询的类型5.table列当前查询正在查哪张表6.type列type列可以直观的判断出当前的sql语句的性能。type里的取值和性能的优劣顺序如下：null > system > const > eq_ref > ref > range > index > all对于SQL优化来说，要尽量保证type列的值是属于range及以上级别。null性能最好的，一般在使用了聚合函数操作索引列，结果直接从索引树获取即可，因此是性能最好。system很少见。直接和一条记录进行匹配。const使用主键索引或唯一索引和常量进行比较，这种性能非常好eq_ref在进行多表连接查询时。如果查询条件是使用了主键进行比较，那么当前查询类型是eq_refEXPLAIN select * from tb_book_author left JOIN tb_book on tb_book_author.book_id = tb_book.idref简单查询：EXPLAIN select * from tb_book where name='book1' 如果查询条件是普通列索引，那么类型ref复杂查询：EXPLAIN select book_id from tb_book left join tb_book_author on tb_book.id = tb_book_author.book_id 如果查询条件是普通列索引，那么类型refrange:使用索引进行范围查找explain select * from tb_book where id>1index查询没有进行条件判断。但是所有的数据都可以直接从索引树上获取(book表中的所有列都有索引)explain select * from tb_bookall没有走索引，进行了全表扫描explain select * from tb_author7.id列在多个select中，id越大越先执行，如果id相同。上面的先执行。8.possible keys列这一次的查询可能会用到的索引。也就是说mysql内部优化器会进行判断，如果这一次查询走索引的性能比全表扫描的性能要查，那么内部优化器就让此次查询进行全表扫描——这样的判断依据我们可以通过trace工具来查看EXPLAIN select * from employees where name like 'custome%'这条sql走索引查询的行数是500多万，那么总的数据行数也就500多万，因此直接进行全表扫描性能更快9.key列实际该sql语句使用的索引10.rows列该sql语句可能要查询的数据条数11.key_len列键的长度，通过这一列可以让我们知道当前命中了联合索引中的哪几列。EXPLAIN select * from employees where name = 'customer10011' # 74 EXPLAIN select * from employees where name = 'customer10011' and age=30 # 74 4 = 78 EXPLAIN select * from employees where name = 'customer10011' and age=30 and position='dev' # 74 4 62 = 140 EXPLAIN select * from employees where name = 'customer10011' and position='dev' # 74name长度是74，也就是当看到key-len是74，表示使用了联合索引中的name列计算规则：- 字符串 1. char(n): n字节长度 2. varchar(n): 2字节存储字符串长度,如果是utf-8,则长度3n + 2 - 数值类型 1. tinyint: 1字节 2. smallint: 2字节 3. int: 4字节 4. bigint: 8字节 - 时间类型 1. date: 3字节 2. timestamp: 4字节 3. datetime: 8字节 如果字段允许为NULL,需要1字节记录是否为NULL 索引最大长度是768字节,当字符串过长时, mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引12.extra列extra列提供了额外的信息，是能够帮助我们判断当前sql的是否使用了覆盖索引、文件排序、使用了索引进行查询条件等等的信息。Using index:使用了覆盖索引所谓的覆盖索引，指的是当前查询的所有数据字段都是索引列，这就意味着可以直接从索引列中获取数据，而不需要进行查表。使用覆盖索引进行性能优化这种手段是之后sql优化经常要用到的。EXPLAIN select book_id,author_id from tb_book_author where book_id = 1 -- 覆盖索引 EXPLAIN select * from tb_book_author where book_id = 1 -- 没有使用覆盖索引using where使用了普通索引列做查询条件EXPLAIN select * from tb_author where name > 'a'using index condition查询结果没有使用覆盖索引，建议可以使用覆盖索引来优化EXPLAIN select * from tb_book_author where book_id > 1Using temporary在非索引列上进行去重操作就需要使用一张临时表来实现，性能是非常差的。当前name列没有索引EXPLAIN select DISTINCT name from tb_authorUsing filesort使用文件排序： 会使用磁盘+内存的方式进行文件排序，会涉及到两个概念：单路排序、双路排序EXPLAIN select * from tb_author order by nameSelect tables optimized away直接在索引列上进行聚合函数的操作，没有进行任何的表的操作EXPLAIN select min(id) from tb_book七、Trace工具在执行计划中我们发现有的sql会走索引，有的sql即使明确使用了索引也不会走索引。这是因为mysql的内部优化器任务走索引的性能比不走索引全表扫描的性能要差，因此mysql内部优化器选择了使用全表扫描。依据来自于trace工具的结论。set session optimizer_trace="enabled=on", end_markers_in_json=on; -- 开启trace select * from employees where name > 'a' order by position; -- 执行查询 SELECT * FROM information_schema.OPTIMIZER_TRACE; -- 获得trace的分析结果{ "steps": [ { "join_preparation": { -- 阶段1:进入到准备阶段 "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { -- 阶段2: 进入到优化阶段 "select#": 1, "steps": [ { "condition_processing": { -- 条件处理 "condition": "WHERE", "original_condition": "(`employees`.`name` > 'a')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`employees`.`name` > 'a')" }, { "transformation": "constant_propagation", "resulting_condition": "(`employees`.`name` > 'a')" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`employees`.`name` > 'a')" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ -- 表依赖详情 { "table": "`employees`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`employees`", "range_analysis": { "table_scan": { "rows": 5598397, "cost": 576657 } /* table_scan */, "potential_range_indexes": [ -- 可能使用到的索引 { "index": "PRIMARY", -- 主键索引 "usable": false, "cause": "not_applicable" }, { "index": "idx_name_age_position", -- 联合索引 "usable": true, "key_parts": [ "name", "age", "position", "id" ] /* key_parts */ }, { "index": "idx_hire_time", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "idx_name_age_position", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { -- 分析各个索引使用的成本 "range_scan_alternatives": [ { "index": "idx_name_age_position", "ranges": [ "a < name" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": true, "index_only": false, -- 是否使用了覆盖索引 "rows": 2799198, -- 要扫描的行数 "cost": 2.08e6, -- 要花费的时间 "chosen": false, -- 是否选择使用这个索引 "cause": "cost" -- 不选择的原因：开销比较大 } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`employees`", "best_access_path": { -- 最优访问路径 "considered_access_paths": [ -- 最后选择的访问路径 { "rows_to_scan": 5598397, -- 全表扫描的行数 "access_type": "scan", -- 全表扫描 "resulting_rows": 5.6e6, -- 结果的行数 "cost": 576655, -- 花费的时间 "chosen": true, -- 选择这种方式 "use_tmp_table": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 5.6e6, "cost_for_plan": 576655, "sort_cost": 5.6e6, "new_cost_for_plan": 6.18e6, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`employees`.`name` > 'a')", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`employees`", "attached": "(`employees`.`name` > 'a')" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`employees`.`position`", "items": [ { "item": "`employees`.`position`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`employees`.`position`" } /* simplifying_order_by */ } /* optimizing_distinct_group_by_order_by */ }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ ] /* steps */, "index_order_summary": { "table": "`employees`", "index_provides_order": false, "order_direction": "undefined", "index": "unknown", "plan_changed": false } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, { "finalizing_table_conditions": [ { "table": "`employees`", "original_table_condition": "(`employees`.`name` > 'a')", "final_table_condition ": "(`employees`.`name` > 'a')" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`employees`" } ] /* refine_plan */ }, { "considering_tmp_tables": [ { "adding_sort_to_table": "employees" } /* filesort */ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "employees", "filesort_information": [ { "direction": "asc", "expression": "`employees`.`position`" } ] /* filesort_information */, "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" } /* filesort_priority_queue_optimization */, "filesort_execution": [ ] /* filesort_execution */, "filesort_summary": { "memory_available": 262144, "key_size": 40, "row_size": 190, "max_rows_per_buffer": 1379, "num_rows_estimate": 5598397, "num_rows_found": 5913852, "num_initial_chunks_spilled_to_disk": 1954, "peak_memory_used": 262144, "sort_algorithm": "std::stable_sort", "sort_mode": "<fixed_sort_key, packed_additional_fields>" } /* filesort_summary */ } ] /* steps */ } /* join_execution */ } ] /* steps */ }八、SQL优化实战1.order by优化在排序应用场景中，很容易出现文件排序的问题，文件排序会对性能造成影响，因此需要优化# using filesort Explain select * from employees where name='customer' order by position; # 没有使用文件排序 Explain select * from employees where name='customer' order by age, position; # 不满足最左前缀法则，使用了文件排序 Explain select * from employees where name='customer' order by position, age; # 满足最左前缀法则，使用索引排序 Explain select * from employees where name='customer' and age=20 order by position, age; show WARNINGS; /* select#1 */ select `db_mysql_pro`.`employees`.`id` AS `id`,`db_mysql_pro`.`employees`.`name` AS `name`,`db_mysql_pro`.`employees`.`age` AS `age`,`db_mysql_pro`.`employees`.`position` AS `position`,`db_mysql_pro`.`employees`.`hire_time` AS `hire_time` from `db_mysql_pro`.`employees` where ((`db_mysql_pro`.`employees`.`age` = 20) and (`db_mysql_pro`.`employees`.`name` = 'customer')) order by `db_mysql_pro`.`employees`.`position` # 排序方向不同，没有使用索引排序 Explain select * from employees where name='customer' and age=20 order by age, position desc; # 使用范围查询，使用了文件排序 Explain select * from employees where name in ('customer','aa') order by age, position; # 使用范围查询，使用了文件排序 Explain select * from employees where name > 'a' order by name;优化手段：如果排序的字段创建了联合索引，那么尽量在业务不冲突的情况下，遵循最左前缀法则来写排序语句。如果文件排序没办法避免，那么尽量想办法使用覆盖索引。all->index2.group by优化group by 的原理是先排序后分组，因此对于group by 的优化参考order by3.文件排序的原理在执行文件排序的时候，会把查询的数据的大小与系统变量：max_length_for_sort_data的大小进行比较（默认是1024字节）,如果比系统变量小，那么执行单路排序，反之则执行双路排序单路排序 把所有的数据扔到sort_buffer内存缓冲区中，进行排序，然后结束双路排序取数据的排序字段和主键字段，在内存缓冲区中排序完成后，将主键字段做一次回表查询，获取完整数据。4.分页优化对于这样的优化查询，mysql会把全部的10010数据拿到，并舍弃掉前面的10000条-- 一次行获取10010，再舍弃掉前10000条 Explain select * from employees limit 1000000,10如果在主键连续的情况下，可以使用主键来做条件，但是这种情况是很少见的Explain select * from employees where id>100000 limit 10对于主键不连续情况下的例子：Explain select * from employees order by name limit 1000000,10 -- 通过先进行覆盖索引的查找，然后在使用join做连接查询获取所有数据。这样比全表扫描要快 explain select * from employees a inner join (select id from employees order by name limit 1000000,10) b on a.id = b.id;5.join优化在join中会涉及到大表（数据量大）和小表（数据量小）的概念。MySQL内部优化器会根据关联字段是否创建了索引来使用不同的算法：Nlj(嵌套循环算法)：如果关联字段使用了索引，mysql会对小表做全表扫描，用小表的数据去和大表的数据去做索引字段的关联查询（type：ref）bnlj（块嵌套循环算法）：如果关联字段没有使用索引，mysql会提供一个join buffer缓冲区，先把小表放到缓冲区中，然后全表扫描大表，把大表的数据和缓冲区中的小表数据在内存中进行匹配。结论：使用join查询时，一定要建立关联字段的索引，且两张表的关联字段在设计之初就要做到字段类型、长度是一致的，否则索引失效。6.in和exists优化在sql中如果A表是大表，B表是小表，那么使用in会更加合适。反之应该使用exists。in: B的数据量<A的数据量select * from A where id in (select id from B) # 相当于： for(select id from B){ //B的数据量少，所以循环次数少。 select * from A where A.id = B.id }exists: B的数据量>A的数据量 (10: id 1. 2. 3. 4)select * from A where exists (select 1 from B where B.id = A.id) true / false 等价于 for(select * from A){ select * from B where B.id = A.id }7.count优化对于count的优化应该是架构层面的优化，因为count的统计是在一个产品会经常出现，而且每个用户访问，所以对于访问频率过高的数据建议维护在缓存中。九、锁的定义和分类1.锁的定义锁是用来解决多个任务（线程、进程）在并发访问同一共享资源时带来的数据安全问题。虽然使用锁解决了数据安全问题，但是会带来性能的影响，频繁使用锁的程序的性能是必然很差的。对于数据管理软件MySQL来说，必然会到任务的并发访问。那么MySQL是怎么样在数据安全和性能上做权衡的呢？——MVCC设计思想。2.锁的分类1）从性能上划分：乐观锁和悲观锁悲观锁：悲观的认为当前的并发是非常严重的，所以在任何时候操作都是互斥。保证了线程的安全，但牺牲了并发性。——总有刁民要害朕。乐观锁：乐观的认为当前的并发并不严重，因此对于读的情况，大家都可以进行，但是对于写的情况，再进行上锁。以CAS自旋锁，在某种情况下性能是ok的，但是频繁自旋会消耗很大的资源。——天网恢恢疏而不漏2）从数据的操作细粒度上划分：表锁和行锁表锁：对整张表上锁行锁：对表中的某一行上锁。3）从数据库的操作类型上划分：读锁和写锁这两种锁都是属于悲观锁读锁（共享锁）：对于同一行数据进行”读“来说，是可以同时进行但是写不行。写锁（拍他锁）：在上了写锁之后，及释放写锁之前，在整个过程中是不能进行任何的其他并发操作（其他任务的读和写是都不能进行的）。3.表锁对整张表进行上锁。MyISAM存储引擎是天然支持表锁的，也就是说在MyISAM的存储引擎的表中如果出现并发的情况，将会出现表锁的效果。MyISAM不支持事务。InnoDB支持事务在InnoDB中上一下表锁:# 对一张表上读锁/写锁格式： lock table 表名 read/write; # 例子 lock table tb_book read; # 查看当前会话对所有表的上锁情况 show open tables; # 释放当前会话的所有锁 unlock tables;读锁： 其他任务可以进行读，但是不能进行写写锁：其他任务不能进行读和写。4.行锁MyISAM只支持表锁，但不支持行锁，InnoDB可以支持行锁。在并发事务里，每个事务的增删改的操作相当于是上了行锁。上行锁的方式：update tb_book set name='qfjava2101' where id=8; 对id是8的这行数据上了行锁。select * from tb_book where id=5 for update; 对id是5的这行数据上了行锁。十、MVCC设计思想MySQL为了权衡数据安全和性能，使用了MVCC多版本并发控制的设计。1.事务的特性原子性：一个事务是一个最小的操作单位（原子），多条sql语句在一个事务中要么同时成功，要么同时失败。一致性：事务提交之前和回滚之后的数据是一致的。持久性：事务一旦提交，对数据的影响是持久的。隔离性：多个事务在并发访问下，提供了一套隔离机制，不同的隔离级别会有不同的并发效果。2.事务的隔离级别read uncommitted（读未提交）： 在一个事务中读取到另一个事务还没有提交的数据——脏读。Read committed（读已提交）: 已经解决了脏读问题，在一个事务中只会读取另一个事务已提交的数据，这种情况会出现不可重复读的问题。就是：在事务中重复读数据，数据的内容是不一样的。repeatable read（可重复读）：在一个事务中每次读取的数据都是一致的，不会出现脏读和不可重复读的问题。会出现虚读（幻读）的问题。什么是幻读：解决方案：通过上行锁来解决虚读问题：Serializable:串行化的隔离界别直接不允许事务的并发发生，不存在任何的并发性。相当于锁表，性能非常差，一般都不考虑脏读、不可重复读、虚读（幻读）3.MVCC思想解读MySQL在读和写的操作中，对读的性能做了并发性的保障，让所有的读都是快照读，对于写的时候，进行版本控制，如果真实数据的版本比快照版本要新，那么写之前就要进行版本（快照）更新，这样就可以既能够提高读的并发性，又能够保证写的数据安全。十一、死锁和间隙锁1.死锁所谓的死锁，就是开启的锁没有办法关闭，导致资源的访问因为无法获得锁而处于阻塞状态。演示：事务A和事物B相互持有对方需要的锁而不释放，造成死锁的情况。2.间隙锁行锁只能对某一行上锁，如果相对某一个范围上锁，就可以使用间隙锁。间隙锁给的条件where id>13 and id<19，会对13 和19 所处的间隙进行上锁。
-
MySQL 8.0 中的索引可以隐藏了 MySQL 8.0 虽然发布很久了，但可能大家都停留在 5.7.x，甚至更老，其实 MySQL 8.0 新增了许多重磅新特性，比如栈长今天要介绍的 "隐藏索引" 或者 "不可见索引"。隐藏索引是什么鬼？隐藏索引 字面意思就是把索引进行隐藏，即不可见，它不是用来查询优化的，所以它不会被优化器使用到。隐藏索引适用于除主键索引（显示或者隐式设置）之外的索引，意味着主键索引是不能通过任何方式隐藏的。MySQL 数据库默认创建的索引都是可见的，要显式控制一个索引的可见性，可以在 CREATE TABLE，CREATE INDEX 或 ALTER TABLE 的索引定义命令中使用 VISIBLE 或 INVISIBLE 关键字。如下面示例所示：CREATE TABLE javastack ( age INT, weight INT, tall INT, INDEX age_idx (age) INVISIBLE ) ENGINE = InnoDB; CREATE INDEX weight_idx ON javastack (weight) INVISIBLE; ALTER TABLE javastack ADD INDEX tall_idx (tall) INVISIBLE;要变更现有索引的可见性，可以在 ALTER TABLE ... ALTER INDEX 命令中使用 VISIBLE 或 INVISIBLE 关键字。年龄索引变更为不可见（隐藏）：ALTER TABLE javastack ALTER INDEX age_idx INVISIBLE;年龄索引变更为可见：ALTER TABLE javastack ALTER INDEX age_idx VISIBLE;怎么知道一个表中的索引是可见还是不可见，可以从 INFORMATION_SCHEMA.STATISTICS 表，或者 SHOW INDEX 命令输出中获得。例如：mysql> SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 'javastack'; +------------+------------+ | INDEX_NAME | IS_VISIBLE | +------------+------------+ | age_idx | YES | | weight_idx | NO | | tall_idx | NO | +------------+------------+隐藏索引有什么用？从上面隐藏索引介绍我们知道，隐藏索引可以不被优化器所使用，那么我们可以把某个表的某个索引设置隐藏，然后再测试 SQL 语句的查询性能。即可以利用隐藏索引快速测试删除索引后对 SQL 查询性能的影响，而无需进行索引删除、重建操作，如果需要该索引，再设置可见就好了，这在大表测试中无疑非常有用，因为对于大表索引的删除和重新添加很耗性能，甚至影响表的正常工作。隐藏索引设置如果一个索引被设置成隐藏了，但实际上又需要被优化器所使用，有几种表索引情况缺失对查询造成的影响：1）SQL 查询语句中包含了索引提示指向不可见索引会发生错误；2）性能模式数据中显示了受影响 SQL 查询语句的负载增高；3）SQL 查询语句进行 EXPLIAN 时出现了不同的执行计划；4）SQL 查询语句出现在了慢查询日志中（之前没有出现）；系统变量 optimizer_switch 的 use_invisible_indexes 标志的值，控制了优化器执行计划构建时是否使用隐藏索引。如果 use_invisible_indexes 值设置为 off 关闭状态（默认值），优化器默认会忽略隐藏索引，即和加入该参数之前的效果一样。如果 use_invisible_indexes 值设置为 on 打开状态，隐藏索引仍然保持不可见，但优化器会把隐藏索引加入到执行计划的构建中。如果想要在某条单个 SQL 查询语句上启用隐藏索引，可以使用 SET_VAR 优化器提示来临时更新 optimizer_switch 的值，如下所示：mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */ > age, weight FROM javastack WHERE weight >= 150\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: javastack partitions: NULL type: range possible_keys: weight_idx key: weight_idx key_len: 5 ref: NULL rows: 2 filtered: 100.00 Extra: Using index condition mysql> EXPLAIN SELECT age, weight FROM javastack WHERE weight >= 150\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: javastack partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5 filtered: 33.33 Extra: Using where索引的可见性不会影响索引的自身维护，例如，不管索引是可见还是不可见，每次表数据行的更改索引都会更新，并且唯一索引也可防止插入重复数据。没有显式主键的表如果在 NOT NULL 列上有任何一个唯一索引，则仍可能成为有效的隐式主键。在这种情况下，第一个这样的索引会对表数据行施加与显式主键相同的约束，并且该索引不能设置为不可见。如以下表的定义：CREATE TABLE javastack ( age INT NOT NULL, weight INT NOT NULL, UNIQUE weight_idx (weight) ) ENGINE = InnoDB;该表定义不包含任何显式主键，但是 weight 列为 NOT NULL，在该列上创建的唯一索引在数据行上与主键具有相同的约束，并且不能使其不可见：mysql> ALTER TABLE javastack ALTER INDEX weight_idx INVISIBLE; ERROR 3522 (HY000): A primary key index cannot be invisible.假设现在我们将一个显式主键添加到表中：ALTER TABLE javastack ADD PRIMARY KEY (age);显式主键不能设置为不可见，此时，weight 列上的唯一索引不再充当隐式主键，因此可以使其设置不可见。mysql> ALTER TABLE javastack ALTER INDEX weight_idx INVISIBLE; Query OK, 0 rows affected (0.03 sec)总结本文介绍了 MySQL 8.0 中的新特性：隐藏（不可见）索引，这个索引并不是新加的索引类型，而是可以控制索引是否加入到执行计划的构建之中。在实际生产中也可以利用隐藏索引进行 SQL 语句的性能测试，或者对索引进行逻辑删除，以及索引的灰度发布测试等，用处还是蛮大的。本次的分享就到这里了，希望对大家有用。觉得不错，在看、转发分享一下哦~最后，MySQL 系列教程还会继续更新，关注Java技术栈公众号第一时间推送，还可以在公众号菜单中获取历史 MySQL 教程，都是干货。参考文档：https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html版权申明：本文系公众号 "Java技术栈" 原创，原创实属不易，转载、引用本文内容请注明出处，禁止抄袭、洗稿，请自重，尊重他人劳动成果和知识产权。
-
MySQL中日期加减（前一天、下个月等）以及格式化的函数 序言查询条件中使用时间区间作为筛选条件，难免的会碰到对日期的操作，如获取前一天、后一天、一周前、一个月前等，索性整理一下MySQL中的相关函数日期的加减DATE_ADD和DATE_SUB语法为：DATE_ADD(date,interval expr type)、DATE_SUB(date,interval expr type)其中常用的type的类型有：second、minute、hour、day、month、year等DATE_ADD是对日期的增加，如果天数为负数时，则表示对日期减少，DATE_SUB是对日期的减少，如果天数为负数时，则表示对日期增加详细说明请查看官方文档https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html可灵活使用例如：-- 获取当前日期 2020-04-07 SELECT CURDATE(); -- 获取当前时间 15:24:01 SELECT CURTIME(); -- 获取当前日期加时间 2020-04-07 23:10:30 SELECT NOW(); -- 获取明天的日期 2020-04-08 获取日期时间格式将CURDATE()替换成NOW()即可 SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY); -- 或者 其他获取月、年方法同样就不重复写了 SELECT DATE_SUB(CURDATE(), INTERVAL -1 DAY); # 获取下个月、明年将DAY替换成对应MONTH、YEAR即可 SELECT DATE_ADD(CURDATE(), INTERVAL 1 MONTH); -- 获取明年的日期 2021-04-07 SELECT DATE_ADD(CURDATE(), INTERVAL 1 YEAR); -- 获取减去一天2小时 SELECT DATE_ADD('2022-02-04 20:00:00', INTERVAL '-1 2' DAY_HOUR); # -> '2022-02-03 18:00:00' # 获取减去一天1小时1分钟1秒 SELECT DATE_SUB('2025-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND); # -> '2024-12-30 22:58:59'日期/时间转换为字符串(格式化)：date_format语法为：date_format(date,format)，date 参数是合法的日期。format 规定日期/时间的输出格式。常用的格式有：格式描述%Y年，4 位%y年，2 位%m月，数值(00-12)%M月名%D带有英文前缀的月中的天%d月的天，数值(00-31)%H小时 (00-23)%h小时 (01-12)%i分钟，数值(00-59)%S秒(00-59)%s秒(00-59)-- 格式化当前日期 2020-04-07 23:23:23 date_format(now(),'%Y-%m-%d %H:%i:%s' ) -- 格式化当前时间 23:23:23 time_format(CURTIME(),'%H:%i:%s') 字符串转换为日期:str_to_date这是DATE_FORMAT()函数的逆函数。它接受一个字符串str和一个格式字符串format。如果格式字符串同时包含日期和时间部分，STR_TO_DATE()返回一个DATETIME值;如果字符串只包含日期或时间部分，则返回一个date或time值。str中包含的日期、时间或datetime值应该以format指定的格式给出。关于可以在格式中使用的说明符，请参阅DATE_FORMAT()函数描述。如果str包含非法的日期、时间或datetime值STR_TO_DATE(STR, FORMAT) SELECT STR_TO_DATE('08/11/2018', '%M/%D/%Y'); -- 2018-08-11 SELECT STR_TO_DATE('08/11/08' , '%M/%D/%Y'); -- 2018-08-11 SELECT STR_TO_DATE('08.11.2008', '%M.%D.%Y'); -- 2018-08-11 SELECT STR_TO_DATE('08:00:30', '%H:%I:%S'); -- 08:00:30 SELECT STR_TO_DATE('08.11.2018 08:00:30', '%M.%D.%Y %H:%I:%S');日期的差值datediffDATEDIFF(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数（date2-date1，正负情况都存在）。date1 和 date2 为日期或 date-and-time 表达式，计算差值时只会计算日期的差值，单位为天。-- 当前时间2020-04-08，差值为-2 SELECT DATEDIFF(NOW(),'2020-04-10') -- 当前时间2020-04-08，差值为2 SELECT DATEDIFF(NOW(),'2020-04-06') timestampdiff语法为：TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)。返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由interval 参数给出。常用的值有：FRAC_SECOND。表示间隔是毫秒SECOND。秒MINUTE。分钟HOUR。小时DAY。天WEEK。星期MONTH。月QUARTER。季度YEAR。年-- now()值为 2020-04-08 23:20:20 SELECT TIMESTAMPDIFF(DAY,NOW(),'2020-04-10 23:23:23') -- 结果为2，相差两天，取整数 -- 其他单位同理Unix 时间戳转换：# 1533956241 (s) SELECT UNIX_TIMESTAMP(); # 1533916800 SELECT UNIX_TIMESTAMP('2018-08-11'); # 1533961800 SELECT UNIX_TIMESTAMP('2018-08-11 12:30:00'); # 2018-08-11 10:57:21 SELECT FROM_UNIXTIME(1533956241); # 2018-08-11 00:00:00 SELECT FROM_UNIXTIME(1533916800); # 2018-08-11 12:30:00 SELECT FROM_UNIXTIME(1533961800); # 2018 11th August 12:30:00 2018 SELECT FROM_UNIXTIME(1533961800, '%Y %D %M %h:%i:%s %x');
-
MySQL 锁表后快速解决方法 及 MySQL中的锁 MySQL 锁表后快速解决方法 及 MySQL中的锁(1) 遇到锁表快速解决办法 依次执行1-6步，运行第6步生成的语句即可。 如果特别着急，运行 1 2 6 步 以及第6步生成的kill语句 即可。1. 第1步 查看表是否在使用。show open tables where in_use > 0 ;如果查询结果为空。则证明表没有在使用。结束。mysql> show open tables where in_use > 0 ; Empty set (0.00 sec)如果查询结果不为空，继续后续的步骤。mysql> show open tables where in_use > 0 ; +----------+-------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+-------+--------+-------------+ | test | t | 1 | 0 | +----------+-------+--------+-------------+ 1 row in set (0.00 sec)2. 第2步 查看数据库当前的进程，看一下有无正在执行的慢SQL记录线程。show processlist;show processlist 是显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。SHOW PROCESSLIST shows which threads are running. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.3. 第3步 当前运行的所有事务SELECT * FROM information_schema.INNODB_TRX;4. 第4步 当前出现的锁SELECT * FROM information_schema.INNODB_LOCKs;5. 第5步 锁等待的对应关系SELECT * FROM information_schema.INNODB_LOCK_waits;看事务表INNODB_TRX，里面是否有正在锁定的事务线程，看看ID是否在show processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。搜索的结果是在事务表发现了很多任务，这时候最好都kill掉。6. 第6步 批量删除事务表中的事务这里用的方法是：通过information_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件，然后执行临时文件中生成的指令。SELECT concat('KILL ',id,';') FROM information_schema.processlist p INNER JOIN information_schema.INNODB_TRX x ON p.id=x.trx_mysql_thread_id WHERE db='test';记得修改对应的数据库名。这个语句执行后结果如下：mysql> SELECT concat('KILL ',id,';') FROM information_schema.processlist p INNER JOIN information_schema.INNODB_TRX x ON p.id=x.trx_mysql_thread_id WHERE db='test'; +------------------------+ | concat('KILL ',id,';') | +------------------------+ | KILL 42; | | KILL 40; | +------------------------+ 2 rows in set (0.00 sec)执行结果里的两个kill语句即可解决锁表。 首先问几个问题：MySQL里有哪些锁？如何造成锁表？如何造成死锁？全局锁加锁方法的执行命令是什么?主要的应用场景是什么?做整库备份时为什么要加全局锁?MySQL的自带备份工具, 使用什么参数可以确保一致性视图, 在什么场景下不适用?不建议使用set global readonly = true的方法加全局锁有哪两点原因?表级锁有哪两种类型? 各自的使用场景是什么?MDL中读写锁之间的互斥关系怎样的?如何安全的给小表增加字段?(2) 复盘自己创建了一个测试的表t，插入了两条数据。然后手动构造锁表和死锁模拟。测试的时候用的root用户测试show processlist 是显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。SHOW PROCESSLIST shows which threads are running. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.(2.1) 创建表t并插入2条数据CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; INSERT INTO `t` (id, c) VALUES (1, 1), (2, 1);(2.2) 准备多个shell模拟锁表可以看到我打开三个shell，用root创建了三个连接，分别是 15 40 41 mysql> show processlist ; +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 15 | root | localhost:49914 | NULL | Query | 0 | Init | show processlist | 0.000 | +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ 6 rows in set (0.00 sec) mysql> mysql> show processlist ; +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 15 | root | localhost:49914 | NULL | Query | 0 | Init | show processlist | 0.000 | | 40 | root | localhost:50872 | test | Sleep | 41 | | NULL | 0.000 | +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ 7 rows in set (0.00 sec) mysql> mysql> show processlist ; +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 15 | root | localhost:49914 | NULL | Query | 0 | Init | show processlist | 0.000 | | 40 | root | localhost:50872 | test | Sleep | 64 | | NULL | 0.000 | | 41 | root | localhost:50888 | test | Sleep | 5 | | NULL | 0.000 | +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ 8 rows in set (0.00 sec)(2.3) 模拟锁表在第一个shell里观察在第二个shell里执行 start transaction; delete from t where c=1 ; 故意打开事务，然后执行语句不提交，占用写锁。在第三个shell里执行 delete from t where c=1 ; 执行删除语句，造成锁表。这个时候 session3在等待session2释放写锁。这个时候已经锁表了。如果再在 第三个shell里执行 delete from t where c=2 ;在 第二个shell里执行 delete from t where c=1 ;就会相互等待，造成死锁。(2.4) 锁表后查看然后在第一个shell里查看(2.4.1)查看是否锁表可以看到下面的查询语句有结果，确实是锁表了。mysql> show open tables where in_use > 0 ; +----------+-------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+-------+--------+-------------+ | test | t | 1 | 0 | +----------+-------+--------+-------------+ 1 row in set (0.00 sec)(2.4.2) 查看数据库当前的进程mysql> show processlist ; +----+-------------+-----------------+------+---------+------+--------------------------+-------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------------+------+---------+------+--------------------------+-------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 15 | root | localhost:49914 | NULL | Query | 0 | Init | show processlist | 0.000 | | 40 | root | localhost:50872 | test | Sleep | 15 | | NULL | 0.000 | | 41 | root | localhost:50888 | test | Query | 11 | Updating | delete from t where c=1 | 0.000 | +----+-------------+-----------------+------+---------+------+--------------------------+-------------------------+----------+ 8 rows in set (0.00 sec)(2.4.3) 当前运行的所有事务mysql> SELECT * FROM information_schema.INNODB_TRX; +--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+------------------+----------------------------+ | trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_is_read_only | trx_autocommit_non_locking | +--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+------------------+----------------------------+ | 23312 | LOCK WAIT | 2019-09-05 23:16:18 | 23312:78:3:2 | 2019-09-05 23:16:18 | 2 | 41 | delete from t where c=1 | starting index read | 1 | 1 | 2 | 1136 | 1 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | | 23311 | RUNNING | 2019-09-05 23:16:13 | NULL | NULL | 3 | 40 | NULL | NULL | 0 | 1 | 2 | 1136 | 3 | 1 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | +--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+------------------+----------------------------+ 2 rows in set (0.00 sec)(2.4.4) 当前出现的锁mysql> SELECT * FROM information_schema.INNODB_LOCKs; +--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 23312:78:3:2 | 23312 | X | RECORD | `test`.`t` | PRIMARY | 78 | 3 | 2 | 1 | | 23311:78:3:2 | 23311 | X | RECORD | `test`.`t` | PRIMARY | 78 | 3 | 2 | 1 | +--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set (0.00 sec)(2.4.5) 锁等待的对应关系mysql> SELECT * FROM information_schema.INNODB_LOCK_waits; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 23312 | 23312:78:3:2 | 23311 | 23311:78:3:2 | +-------------------+-------------------+-----------------+------------------+ 1 row in set (0.00 sec)(2.4.6) 删除事务表中的事务mysql> SELECT p.id, p.time, i.trx_id, i.trx_state, p.info FROM INFORMATION_SCHEMA.PROCESSLIST p, INFORMATION_SCHEMA.INNODB_TRX i WHERE p.id = i.trx_mysql_thread_id AND i.trx_state = 'LOCK WAIT'; +----+------+--------+-----------+-------------------------+ | id | time | trx_id | trx_state | info | +----+------+--------+-----------+-------------------------+ | 41 | 27 | 23312 | LOCK WAIT | delete from t where c=1 | +----+------+--------+-----------+-------------------------+ 1 row in set (0.01 sec)(2.4.7) kill掉锁表的语句这儿有两种观点，一种是只kill掉后面等待的那个语句。还有一种是把两个语句都kill掉。这个根据实际情况处理。mysql> kill 41 ; Query OK, 0 rows affected (0.00 sec) mysql> SELECT p.id, p.time, i.trx_id, i.trx_state, p.info FROM INFORMATION_SCHEMA.PROCESSLIST p, INFORMATION_SCHEMA.INNODB_TRX i WHERE p.id = i.trx_mysql_thread_id AND i.trx_state = 'LOCK WAIT'; Empty set (0.01 sec)杀掉41mysql> show processlist ; +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 15 | root | localhost:49914 | NULL | Query | 0 | Init | show processlist | 0.000 | | 40 | root | localhost:50872 | test | Sleep | 56 | | NULL | 0.000 | +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ 7 rows in set (0.00 sec)然后到第3个shell窗口查看，可以看到mysql> delete from t where c=1 ; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction因为第3个shell里执行的语句被kill掉了。到这儿可以看到死锁解决了。但其实有个问题。第3个shell里的语句被kill掉了。但第2个shell里的语句还在执行。如果第二个shell里的事务不提交或者kill，在第3个shell里执行删除语句还会造成锁表。第二种观点的办法SELECT p.id, p.time, x.trx_id, x.trx_state, p.info FROM INFORMATION_SCHEMA.PROCESSLIST p, INFORMATION_SCHEMA.INNODB_TRX x WHERE p.id = x.trx_mysql_thread_id ; mysql> SELECT p.id, p.time, x.trx_id, x.trx_state, p.info FROM INFORMATION_SCHEMA.PROCESSLIST p, INFORMATION_SCHEMA.INNODB_TRX x WHERE p.id = x.trx_mysql_thread_id ; +----+------+--------+-----------+-------------------------+ | id | time | trx_id | trx_state | info | +----+------+--------+-----------+-------------------------+ | 42 | 3 | 23317 | LOCK WAIT | delete from t where c=1 | | 40 | 1792 | 23311 | RUNNING | NULL | +----+------+--------+-----------+-------------------------+ 2 rows in set (0.01 sec)然后同时杀掉 40 42 就可以。(3) MySQL中的锁数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。(3.1) 全局锁全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。 全局锁的典型使用场景是，做全库逻辑备份。 也就是把整库每个表都 select 出来存成文本。风险：1.如果在主库备份，在备份期间不能更新，业务停摆2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。全局锁主要用在逻辑备份过程中。对于全部是 InnoDB 引擎的库，建议你选择使用 –single-transaction 参数，对应用会更友好。(3.2) 表级锁MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。(3.2.1) 表锁表锁是在Server层实现的。ALTER TABLE之类的语句会使用表锁，忽略存储引擎的锁机制。 表锁的语法是 lock tables … read/write。 与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。(3.2.1) metadata lock 另一类表级的锁是 MDL（metadata lock)。 MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是并发情况下维护数据的一致性，保证读写的正确性。(避免加字段删字段导致查询结果异常)因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。而实际上，即使是小表，操作不慎也会出问题。在修改表的时候会持有MDL写锁，如果这个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。MDL 是并发情况下维护数据的一致性，在表上有事务的时候,不可以对元数据经行写入操作,并且这个是在server层面实现的(3.3) 行锁MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。InnoDB行锁包括 Record Lock 、 Gap Lock、 Next-Key Lock在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。知道了这个设定，对我们使用事务有什么帮助呢？那就是，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作：从顾客 A 账户余额中扣除电影票价；给影院 B 的账户余额增加这张电影票价；记录一条交易日志。试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。(3.3.1) MySQL/InnoDB的加锁过程Read Committed (RC)Repeatable Read (RR)InnoDB的加锁分析前提条件前提一： 查询列是不是主键？前提二： 当前系统的隔离级别是什么？前提三： 查询列上有索引吗？前提四： 查询列是唯一索引吗？前提五： 两个SQL的执行计划是什么？索引扫描？全表扫描？update t1 set update_time = now() where k = 10 ;组合一： k列是主键，RC隔离级别组合二： k列是二级唯一索引，RC隔离级别组合三： k列是二级非唯一索引，RC隔离级别组合四： k列上没有索引，RC隔离级别组合五： k列是主键，RR隔离级别组合六： k列是二级唯一索引，RR隔离级别组合七： k列是二级非唯一索引，RR隔离级别组合八： k列上没有索引，RR隔离级别组合九： Serializable隔离级别组合一: Read Committed 隔离级别，k列是主键，给定SQL：update t1 set update_time = now() where k = 10; 只需要将主键上 k = 10的记录加上X锁即可组合二: Read Committed 隔离级别，k列有unique索引，unique索引上的k=10一条记录加上X锁，同时，会根据读取到的列，回主键索引(聚簇索引)，然后将聚簇索引上对应的主键索引项加X锁。组合三: Read Committed 隔离级别，k列上有索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，然后将聚簇索引上对应的主键索引项加X锁。组合四: Read Committed 隔离级别，若k列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。组合五: Repeatable Read 隔离级别，k列是主键列，给定SQL update t1 set update_time = now() where k = 10; 只需要将主键上 k = 10的记录加上X锁即可。组合六: Repeatable Read 隔离级别，k列有unique索引，unique索引上的k=10一条记录加上X锁，同时，会根据读取到的列，回主键索引(聚簇索引)，然后将聚簇索引上对应的主键索引项加X锁。组合七：Repeatable Read 隔离级别，k列有索引， 通过索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。考虑到B+树索引的有序性，满足条件的项一定是连续存放的。如果要插入一条记录，肯定会插入在相同位置，为了保证两次查询查到的值一致，MySQL选择了用GAP锁，将 查询值范围前、查询值范围、查询值范围后 三个GAP给锁起来。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。k | 7 | 8 | 10 | 10 | 40 | 50 | primary id | 1 | 2 | 3 | 4 | 5 | 6 |为了保证[8,2]与[10,3]间，[10,3]与[10,4]间，[10,4]与[40,5]不会插入新的满足条件的记录，MySQL选择了用GAP锁，将这三个GAP给锁起来。组合八： 在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发 更新/删除/插入 操作 聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。组合九：Serializable隔离级别下直接用加锁的方式来避免并行访问。在RC，RR隔离级别下，都是快照读，不加锁。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。(3.4) 死锁当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。当出现死锁以后，有两种策略：一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。(4) 可能遇到的问题(4.1) 备份一般都会在备库上执行，你在用–single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？备份一般都会在备库上执行，你在用–single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？假设这个 DDL 是针对表 t1 的， 这里我把备份过程中几个关键的语句列出来：Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT； /* other tables */ Q3:SAVEPOINT sp; /* 时刻 1 */ Q4:show create table `t1`; /* 时刻 2 */ Q5:SELECT * FROM `t1`; /* 时刻 3 */ Q6:ROLLBACK TO SAVEPOINT sp; /* 时刻 4 */ /* other tables */在备份开始的时候，为了确保 RR（可重复读）隔离级别，再设置一次 RR 隔离级别 (Q1);启动事务，这里用 WITH CONSISTENT SNAPSHOT 确保这个语句执行完就可以得到一个一致性视图（Q2)；设置一个保存点，这个很重要（Q3）；show create 是为了拿到表结构 (Q4)，然后正式导数据 （Q5），回滚到 SAVEPOINT sp，在这里的作用是释放 t1 的 MDL 锁 （Q6）。当然这部分属于“超纲”，上文正文里面都没提到。DDL 从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到达后，如果开始执行，则很快能够执行完成。参考答案如下：如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。(4.2) 删数据问题如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：第一种，直接执行 delete from T limit 10000;第二种，在一个连接中循环执行 20 次 delete from T limit 500;第三种，在 20 个连接中同时执行 delete from T limit 500。你会选择哪一种方法呢？为什么呢？方案一，事务相对较长，则占用锁的时间较长，会导致其他客户端等待资源时间较长。方案二，串行化执行，将相对长的事务分成多次相对短的事务，则每次事务占用锁的时间相对较短，其他客户端在等待相应资源的时间也较短。这样的操作，同时也意味着将资源分片使用（每次执行使用不同片段的资源），可以提高并发性。方案三，人为自己制造锁竞争，加剧并发量。(4.3) 问题31.如何在死锁发生时,就把发生的sql语句抓出来？2.在使用连接池的情况下,连接会复用.比如一个业务使用连接set sql_select_limit=1,释放掉以后.其他业务复用该连接时,这个参数也生效.请问怎么避免这种情况,或者怎么禁止业务set session？3.很好奇双11的成交额,是通过redis累加的嘛？4.不会改源码能成为专家嘛？show engine innodb status 里面有信息，不过不是很全…5.7的reset_connection接口可以考虑一下用redis的话，为了避免超卖需要增加了很多机制来保证。修改都在数据库里执行就方便点。前提是要解决热点问题我认识几位处理问题和分析问题经验非常丰富的专家，不用懂源码，但是原理还是要很清楚的(4.4) 转义导致死锁问题前天在开发中，还遇到过一次死锁，是在一个批处理中，要删除1000条数据，5个线程，200条数据commit一次，sol：delete from 表A where id =15426169754750004759008 STORAGEDB(id是主键)我同事解决了，说原因是id 是char 类型，但是没有加单引号，所以没有进入id索引中，然后锁表了，所以导致死锁。这个问题的出现，应该是人为只要并发导致锁冲突吧？但是为什么不加单引号会死锁，加了单引号就能正常跑呢？References[1] Mysql 锁、事务强制手动kill/释放[2] 19 | 为什么我只查一行的语句，也执行这么慢？MySQL实战45讲[3] 06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？MySQL实战45讲[4] 07 | 行锁功过：怎么减少行锁对性能的影响？MySQL实战45讲[5] mysql 5.7 lock-tables[6] MySQL 5.7 Reference Manual / The InnoDB Storage Engine / Locks Set by Different SQL Statements in InnoDB[7] MySQL 5.7 Reference Manual / The InnoDB Storage Engine / InnoDB Startup Options and System Variables[8] 《高性能MySQL》 O’REILLY[9] mysql-show-open-tables[10] mysql-show-processlist[11] innodb-locking[12] innodb-index-types[13] 面试官：同学，分析一下MySQL/InnoDB的加锁过程吧[14] 解决死锁之路 - 常见 SQL 语句的加锁分析[15] mysql insert锁机制
