关于mysql索引分析和优化(四)

bangiao
362 阅读13分钟

mysql 锁详解

锁分类

以范围规定分为:

  • 行锁: 锁定操作的行
  • 表锁: 锁定操作的表

以操作规定分为:

  • 读锁(共享锁): 针对一个数据, 可以存在多个读操作, 但不允许写操作
  • 写锁(排他锁): 锁定数据, 在没有解锁之前, 无法读, 无法写

而在 mysql 中的储存引擎支持不同的锁

锁类型特点
表级锁偏向 MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁偏向 InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从上面的存储引擎中, 可以分析出:

  • 在 myISAM 储存引擎中, 写操作的优先级比读操作的优先级要高, 所以在这种模式下, 如果写操作非常多, 那么用户则无法读取到数据了

  • 在 InnoDB 储存引擎中, 它和 myISAM 存储引擎的区别在于, InnoDB 支持了 事务 和 行锁, 在这种情况下, InnoDB 容易出现死锁的情况, 但相对应的并发率较高

查看锁的争用情况

show open tables；

In_user : 表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。 Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作

show status like 'Table_locks%';

Table_locks_immediate ： 指的是能够立即获得表级锁的次数，每立即获取锁，值加1。 Table_locks_waited ： 指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况

关于 ACID 和 事务相关

ACID属性含义
原子性（Atomicity）事务是一个不可分割的原子操作单元，其对数据的修改，要么全部成功，要么全部失败。
一致性（Consistent）在事务开始和完成时，数据都必须保持一致状态。事务内对数据的修改不会丢失
隔离性（Isolation）数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的 “独立” 环境 下运行。
持久性（Durable）事务完成之后，对于数据的修改是永久
  • 事务隔离级别

为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行，这显然与“并发” 是矛盾的。 数据库的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、 Serializable，这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。

隔离级别丢失更新脏读不可重复读幻读
Read uncommitted×√√√
Read committed××√√
Repeatable read（默认）×××√
Serializable××××

备注 ： √ 代表可能出现 ， × 代表不会出现

Mysql 的数据库的默认隔离级别为 Repeatable read ， 查看方式：

show variables like 'tx_isolation';

总结

我讲下自己对事务隔离级别的认识

事务隔离级别, 主要讲的是一个事务的修改, 哪些数据在事务内可见, 哪些数据是事务间可见
① Read uncommitted(读未提交): 主要讲事务与事务之间的数据是实时同步的, 一个事务的修改, 不论这个事务是否提交了, 另一个事务还是能看得见数据的修改并且直接使用(说白了就是一点隔离性都没有)
② Read committed(读已提交 or 不可重复读): 一个事务只能读取到另一个事务提交后的数据, 换句话说一个时候的修改不会被另一个事务发现, 说句话, 数据是隔离的, 只有在另一个事务提交之后才能够发现变量的修改, 但同时他无法重复读取, 事务A读取了数据, 事务B也读取了数据, 此时事务B修改了数据提交事务, 事务A第二次读取事务后发现, 数据变化了, 在同一个事务中读取了两次数据两次都是不同的, 这就是不可重复读 (不可重复读是 oracle 默认隔离级别) ③ Repeatable read(重复读): 在一个事务A的生命周期中, 另外的其他事务不论对这个数据修改了多少次, 事务A也不论读取了多少次, 还是那个值, 但这种事务隔离级别没有解决幻读的问题, 幻读就是在修改一个范围的数据时, 另一个事务添加了新的数据(或删除)在这个范围中, 之前的事务再次读取这个范围的数据, 发现多了或者少了几行数据, 这就是幻读问题, 但InnoDB存储引擎中提供么 MVCC(Multiversion Concurrent Control) 多版本控制功能, 解决了幻读问题, 可重复读是 mysql 的默认隔离级别
④ Serializable(序列化): 将事务串行化, 前面一个事务执行完毕后面的事务才能够执行, 完美解决了幻读问题, 但是会出现超时问题, 效率非常的慢

前面说了一大堆, 说点简单的,

  • 0 级保护, 事务的提交无效, 数据随时共享, 没有隔离性
  • 1 级保护, 现在事务的提交有效了, 保护在事务提交之前的隔离性, 但在事务提交之后, 数据隔离性不再存在
  • 2 级保护, 现在事务的提交与提交之间不存在交互关系, 在一个事务中, 其他无数的事务是否提交都不会影响到这个事务对这个数据的读取和修改, 隔离性存在, 但不会仅针对这几条数据, 事务关注的数据不保证添加和删除, 它只保证了修改和读取
  • 3 级保护, 保证了保证了数据的CRUD, 不论数据是否添加, 修改, 删除或读取都是安全的, 因为它把事务的执行做了串行化
隔离级别脏读可能性不可重复读可能性幻读可能性加锁读
READ UNCOMMITTEDYesYesYesNo
READ COMMITTEDNoYesYesNo
REPEATABLE READNoNoYesNo
SERIALIZABLENoNoNoYes

InnoDB 的行锁模式

InnoDB 实现了以下两种类型的行锁

  • 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
  • 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)； 对于普通SELECT语句，InnoDB不会加任何锁；

可以通过以下语句显示给记录集加共享锁或排他锁

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

案例准备工作

create table test_innodb_lock(
id int(11),
name varchar(16),
sex varchar(1)
)engine = innodb default charset=utf8;
insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);

行锁基本演示

证明行锁的存在

session1session2
关闭自动提交事务 set autocommit = 0;关闭自动提交事务 set autocommit = 0;
select * from test_innodb_lock til where til.id = 3;select * from test_innodb_lock til where til.id = 3;
update test_innodb_lock set name = 'A1' where id = 3;update test_innodb_lock set name = 'A1' where id = 3;
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0阻塞(但我的太久了, 超时了ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction)

如果不是同一个数据的话, 直接就成功了

无索引行锁升级为表锁

如果在有索引的情况下, 不会发生任何的阻塞

session1session2
关闭自动提交事务 set autocommit = 0;关闭自动提交事务 set autocommit = 0;
update test_innodb_lock set sex = '2' where name = 400;update test_innodb_lock set sex = '2' where id = 9;
但这样不会阻塞不会发生阻塞

在前面我们创建了 idx_test_innodb_lock_name 的索引, 所以这里我们需要暂时删除掉它

如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样

session1session2
关闭自动提交事务 set autocommit = 0;关闭自动提交事务 set autocommit = 0;
update test_innodb_lock set sex = '0' where name = '300';update test_innodb_lock set sex = '2' where id = 9;
阻塞
commit通过, 不阻塞了
commit

在这种情况下, 前面的 where name 将会全表扫描, 后面的 where id 将会在 id 主键索引中扫描, 这样即使后面的 id 直接确定了位置, 也将会在确定位置之前被 where name 的全表扫描拦截, 阻塞

间隙锁危害

间隙锁是什么?

间隙锁的存在主要是在 sql 中出现的范围查找, 如果在这个范围存在的row还好说, 但在这个范围, 但是还没有的 row 那么此时这种在范围但不存在的row, 被叫做间隙, 而对这个范围上锁就叫间隙锁(next-key-lock)

session1session2
关闭自动提交事务 set autocommit = 0;关闭自动提交事务 set autocommit = 0;
update test_innodb_lock set name = '123' where id > 8;insert into test_innodb_lock(id, name, sex) values (10, '123', '0');
阻塞
commit通过, 不阻塞了
commit

这就是间隙锁的问题

InnoDB 行锁争用情况

show status like 'innodb_row_lock%';
key详解
Innodb_row_lock_current_waits当前正在等待锁定的数量
Innodb_row_lock_time从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg每次等待所花平均时长
Innodb_row_lock_time_max从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits系统启动后到现在总共等待的次数

当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划

总结：

  • 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能减少索引条件，及索引范围，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度
  • 尽可使用低级别事务隔离（但是需要业务层面满足需求）

常用SQL技巧

SQL执行顺序

编写顺序

SELECT DISTINCT
	<select list>
FROM
	<left_table> <join_type>
JOIN
	<right_table> ON <join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_by_list>
HAVING
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT
	<limit_params>

执行顺序

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT DISTINCT <select list>
ORDER BY <order_by_condition>
LIMIT <limit_params>

正则表达式使用

正则表达式（Regular Expression）是指一个用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串

符号含义
^在字符串开始处进行匹配
$在字符串末尾处进行匹配
.匹配任意单个字符, 包括换行符
[...]匹配出括号内的任意字符
[^...]匹配不出括号内的任意字符
a*匹配零个或者多个a(包括空串)
a+匹配一个或者多个a(不包括空串)
a?匹配零个或者一个a
a1a2匹配a1或a2
a(m)匹配m个a
a(m,)至少匹配m个a
a(m,n)匹配m个a 到 n个a
a(,n)匹配0到n个a
(...)将模式元素组成单一元素
select * from emp where name regexp '^T';
select * from emp where name regexp '2$';
select * from emp where name regexp '[uvw]';

MySQL 常用函数

数字函数

函数名称作 用
ABS求绝对值
SQRT求二次方根
MOD求余数
CEIL 和 CEILING两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR向下取整，返回值转化为一个BIGINT
RAND生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND对所传参数进行四舍五入
SIGN返回参数的符号
POW 和 POWER两个函数的功能相同，都是所传参数的次方的结果值
SIN求正弦值
ASIN求反正弦值，与函数 SIN 互为反函数
COS求余弦值
ACOS求反余弦值，与函数 COS 互为反函数
TAN求正切值
ATAN求反正切值，与函数 TAN 互为反函数
COT求余切值

字符串函数

函数名称作 用
LENGTH计算字符串长度函数，返回字符串的字节长度
CONCAT合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT替换字符串函数
LOWER将字符串中的字母转换为小写
UPPER将字符串中的字母转换为大写
LEFT从左侧字截取符串，返回字符串左边的若干个字符
RIGHT从右侧字截取符串，返回字符串右边的若干个字符
TRIM删除字符串左右两侧的空格
REPLACE字符串替换函数，返回替换后的新字符串
SUBSTRING截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

日期函数

函数名称作 用
CURDATE 和 CURRENT_DATE两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME两个函数作用相同，返回当前系统的时间值
NOW 和 SYSDATE两个函数作用相同，返回当前系统的日期和时间值
MONTH获取指定日期中的月份
MONTHNAME获取指定日期中的月份英文名称
DAYNAME获取指定曰期对应的星期几的英文名称
DAYOFWEEK获取指定日期对应的一周的索引位置值
WEEK获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR获取年份，返回值范围是 1970〜2069
TIME_TO_SEC将时间参数转换为秒数
SEC_TO_TIME将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME时间加法运算，在原始时间上添加指定的时间
SUBTIME时间减法运算，在原始时间上减去指定的时间
DATEDIFF获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT格式化指定的日期，根据参数返回指定格式的值
WEEKDAY获取指定日期在一周内的对应的工作日索引

聚合函数

函数名称作 用
MAX查询指定列的最大值
MIN查询指定列的最小值
COUNT统计查询结果的行数
SUM求和，返回指定列的总和
AVG求平均值，返回指定列数据的平均值
avatar
文章
阅读
粉丝