MySQL索引

最新推荐文章于 2023-07-13 12:26:15 发布

CCCZEDH

最新推荐文章于 2023-07-13 12:26:15 发布

阅读量483

点赞数

分类专栏： mysql 索引文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/CCCZEDH/article/details/121027092

版权

mysql 同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

索引

1 篇文章 0 订阅

订阅专栏

MySQL索引

1. 原理

MySQL逻辑分层：连接层、服务层、引擎层、存储层

在这里插入图片描述

InnoDB（默认）：事务优先（适合高并发操作：行锁）

MyISAM：性能优先（表锁）

查询数据库引擎

支持哪些引擎
```
show engines
```

在这里插入图片描述

查看当前使用的引擎
```
show variables like '%storage_engine%'
```

在这里插入图片描述

指定数据库对象的引擎

create table tb (
	id int(4) auto_increment,
	`name` varchar(10),
	dept varchar(10),
	primary key(id)
)engine=MyISAM AUTO_INCREMENT=1 DEFAULT CAHRSET=UTF8

在这里插入图片描述

2. SQL优化

原因：性能低、执行时间太长、等待时间长、SQL语句欠佳（连接查询）、索引时效、服务器参数设置不合理（缓冲、线程数等）

a.SQL：

编写过程：

select distinct … from … join… on…where… group by…having …order by limit…
解析过程

from … on … join … where … group by… having … select dinstinct … order by limit…

b.SQL优化，主要就是在优化索引

索引：相当于书的目录

索引：index是帮助MySQL高效获取数据的数据结构。索引是数据结构（树：B树（默认））、Hash树

索引的弊端：

索引本身很大，可以存放在内存/硬盘（通常为硬盘）
索引不是所有情况都适用：
1. 少量数据
2. 频繁更新的字段
3. 很少使用的字段
索引会降低增删改的效率

优势：

提高查询效率（降低IO使用率）
降低CPU使用率（…order by age desc，因为B树索引本身就是一个已经排序好的结构，因此在排序时，可以直接使用）

3. 索引

创建索引

方式一：

create	索引类型	索引名	on	表(字段)

单值：

create index dept_index on tb(dept)

唯一：

create unique index name_index on tb(name)

复合索引：

create index dept_name_index on tb(dept,name)

方式二：

alter	table	表名	索引类型	索引名(字段)

单值：

alter table tb add index dept_index(dept)

唯一：

alter table tb add unique index name_index(name)

复合：

alter table tb add index dept_name_index(dept,name)

删除索引

drop index 索引名 on 表名

drop index name_index on tb

查询索引

show index from 表名

show index from 表名 /g

show index from tb

在这里插入图片描述

4. SQL性能问题

分析SQL的执行计划

https://www.mysqlzh.com/doc/66/292.html

EXPLAIN语句可以用作DESCRIBE的一个同义词，或获得关于MySQL如何执行SELECT语句的信息：

EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。
如果在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，提供有关表如何联接和联接的次序。（explain，可以模拟SQL优化器执行SQL语句，让开发人员知道自己编写的SQL状况。）

EXPLAIN tbl_name

或

EXPLAIN [EXTENDED] SELECT select_options

在这里插入图片描述

解释

id：查询识别符。这是SELECT的查询序列号
select_type：查询类型
table：输出的行所引用的表
type：联接类型（详情见网页）
possible_keys：possible_keys列指出MySQL能使用哪个索引在该表中找到行
key：key列显示MySQL实际决定使用的键（索引）
key_len：key_len列显示MySQL决定使用的键长度
ref：ref列显示使用哪个列或常数与key一起从表中选择行
rows：rows列显示MySQL认为它执行查询时必须检查的行数
Extra：该列包含MySQL解决查询的详细信息

（1）id

id值相同：从上往下，顺序执行（数据小的表，优先查询）

在这里插入图片描述

执行顺序：t3->tc3->c4（这里的数字代表表中数据的行数）

在这里插入图片描述

执行顺序：tc3->c4->t6（这里的数字代表表中数据的行数）

笛卡尔积，尽量在执行过程中使用更少的内存

id值不同：id值越大越优先查询（本质：嵌套子查询时，先查内层，再查外层）

在这里插入图片描述

id值即有相同，又有不同：先根据id值越大越优先，然后id相同从上往下顺序执行

在这里插入图片描述

（2）select_type

primary：包含子查询SQL中的主查询（最外层）

subquery：包含子查询SQL中的子查询（非最外层）

simple：简单查询（不包含子查询、union查询）

在这里插入图片描述

derived：衍生查询（使用到了临时表）

两个触发条件：

在from子查询中只有一张表
在from子查询中，如果table1 union table2，则table1 就是derived，table2就是union

（3）type：索引类型

性能（大到小）：system、const、eq_ref、ref、range、index、all

system、const只是理想情况，实际能达到 eq_ref~~range

1.`system`：只有一条数据的系统表（忽略），或衍生表只有一条数据的主查询

在这里插入图片描述

2.`const`：仅仅只能查到一条数据的SQL，用于primary 可以或unique索引

EXPLAIN SELECT cid FROM test01 WHERE tid=1

在这里插入图片描述

删除主键索引，再建立一个索引之后

alter table test01 drop primary key
create index test01_index on test01(tid)
EXPLAIN SELECT cid FROM test01 WHERE tid=1

再次执行计划，可以看到type变为了ref

在这里插入图片描述

3.`eq_ref`：唯一性索引，对于每个索引键的查询，返回匹配唯一一行数据（有且只有一个）

select * from table where name = "??"
#常见！于唯一索引和主键索引

要给这个name建立unique或主键索引，但是name唯一是不现实的，因此强调主键索引

alter table teacherCard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_ucid unique index(tcid);

explain select t.tcid from teacher t,teacherCard tc where t.tcid=tc.tcid;

以上SQL用到的索引是 t.tcid，即teacher表中的tcid字段

如果teacher表的数据个数和连接查询的数据个数一致，则有可能满足eq_ref级别

在这里插入图片描述

4.`ref`：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0~多条数据）

select * from teacher where tname = "tz";

explain select * from teacher where tname = "tz";

在这里插入图片描述

5.`range`：检索指定范围的行，where后面是一个范围查询（between, in , >, <, >= …)

注意in有时候会时效，可能会转为无索引all

alter table teacher add index tid_index(tid);
#必须要有索引才能优化
EXPLAIN SELECT * FROM teacher t WHERE t.tid IN (1,2);
EXPLAIN SELECT * FROM teacher t WHERE t.tid < 3;

在这里插入图片描述

6.`index`：查询索引中的全部数据（在B-Tree中查询所有）

该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。

当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型

在这里插入图片描述

7.`all`：查询表中的全部数据（将整个表查询一遍，包括查所有行和列）

对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出

在这里插入图片描述

从打印结果看出，course表中没有索引，cid不是所有，需要检索所有表中的所有数据

总结

system/const：结果只有一条数据

eq_ref：结果多条，但是每条数据是唯一的（如name列没有相同的值，没有两个同名的）

ref：结果多条，但是每条数据是0或多条

（4）possible_keys：可能用到的索引，是一种预测，并不准确

如果possible_key/key是null，说明没有索引

在这里插入图片描述

（5）key：实际用到的索引，见（4）

（6）ken_len：索引的长度

作用：用于判断符合索引是否被完全使用

复合索引：（a,b,c），区别于非复合索引 a

在这里插入图片描述

解释：name char(20) 而key_len是60的原因：

在utf8：一个字符占3个字节
拓展
- gbk：1个字符2个字节
- latin：1个字符1个字节

alter table test_kl add column name1 char(20);
alter table test_kl add index index_name1(name1);
explain select * from test_kl where name1='';

在这里插入图片描述

如果索引字段可以为null，那么MySQL会使用1个字节作为标识

删除原来的索引，并增加符合索引

drop index index_name on test_kl;
drop index index_name1 on test_kl;
alter table test_kl add index name_name1_index(name,name1);
explain select * from test_kl where name1=''; --121（要使用name1则先使用name）
explain select * from test_kl where name=''; --60 （20*3）

在这里插入图片描述

alter table test_kl add column name2 varchar(20); --可以为null
alter table test_kl index name2_index(name2); --添加字段索引
explain select * from test_kl where name2=''; --63=20*3+1+2

MySQL使用2个字节表示可变长度；1个字节标识可以为null；

（7）ref：指明当前表所参照的字段

注意与type(ref)区分！

select ... where a.c = b.x; --其中b.x可以是常量(const)

在这里插入图片描述

t表引用const，c表引用myDB.t.tid

（8）rows：被索引优化查询的数据个数（实际通过索引而查询到的数据个数）

在这里插入图片描述

（9）Extra

using filesort：标识性能消耗大，需要“额外”的一次排序（查询）

create table test02(
	a1 char(3),
    a2 char(3),
    a3 char(3),
    index idx_a1(a1),
    index idx_a2(a2),
    index idx_a3(a3),
);
explain select * from test02 where a1='' order by a1; --
explain select * from test02 where a1='' order by a2; --using filesort

在这里插入图片描述

小结：对于单索引，如果排序和查找是同一个字段，则不会出现using filesort；如果不是同一个字段就会有using filesort。where哪些字段，就order by哪些字段

复合索引：不能跨列（最佳左前缀）

不能跨列的意思是，复合索引如果是(a1,a2,a3)，那么不能where a1=’’ order by a3，这样就跨了列a2。必须按照索引，从左到右，左满右不满

--先删除之前的单索引
drop index idx_a'i' on test02;
alter table test02 add index idx_a_all(a1,a2,a3);
explain select * from test02 where a1='' order by a3; --using filesort
explain select * from test02 where a2='' order by a3; --using filesort
select * from test02 where a1='' order by a2;

在这里插入图片描述

小结：避免where和order按照复合索引的顺序使用，不要跨列或无序使用

using temporary：性能损耗大，用到了临时表；一般出现在group by中

explain select a1 from test02 where a1 in(1,2,3) group by a1
explain select a1 from test02 where a1 in(1,2,3) group by a2; --using temporary

在这里插入图片描述

产生原理：group by a2时，产生了a2分组的临时表；已经有临时表，但是不适用，因为查询的字段里面没有group by的字段

避免：查询哪些列，就根据那些列分组

解析过程：

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

using index：性能提升；索引覆盖

原因：不读取源文件，只从索引文件中获取数据（不需要回表查询）

回表查询：如果索引的列在 select 所需获得的列中或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表

索引覆盖：使用的列全部都在索引中

在这里插入图片描述

如果使用了索引覆盖，会对possible_keys和key造成影响

如果没有where，则索引只出现在key中
如果有where，则索引出现在key和possible_keys中

在这里插入图片描述

using where：需要回表查询

a3有索引，a1没有索引

在这里插入图片描述

impossible where：where字句永远为false

EXPLAIN SELECT * FROM tb WHERE 1=0

在这里插入图片描述

理想SQL语句

在这里插入图片描述

虽然编写的顺序和索引顺序不一致，但SQL优化器优化了，因此SQL执行一致，不会产生跨列

在这里插入图片描述

a4跨列，造成索引时效，需要回表查询，因此是using where，以上可以通过key_len进行验证

explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a3

在这里插入图片描述

不要跨列使用（where和order by拼起来，不要跨列使用）

explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a2,a3
--不会using filesort，a4时效，但a1,a2,a3有序

在这里插入图片描述

总结

如果（a,b,c,d）复合索引和使用的顺序全部一致（且不跨列使用），则复合索引全部使用；如果部分一致则（且不跨列使用），则使用部分索引
where和order by拼接，没有跨列使用

5.优化案例

1.单表优化

#创建表，并插入值
CREATE TABLE book(
bid INT(4) PRIMARY KEY,
NAME VARCHAR(20) NOT NULL,
authorid INT(4) NOT NULL,
publicid INT(4) NOT NULL,
typeid INT(4) NOT NULL
);
INSERT INTO book VALUES(1,'b1',1,1,2);
INSERT INTO book VALUES(2,'b2',2,1,2);
INSERT INTO book VALUES(3,'b3',2,2,3);
INSERT INTO book VALUES(4,'b4',3,2,2);
INSERT INTO book VALUES(5,'b5',3,2,3);

问题示例：查询authorid=1且typeid为2或3的bid，并以typeid降序

EXPLAIN SELECT bid FROM book WHERE typeid IN (2,3) AND authorid=1 ORDER BY typeid DESC;

在这里插入图片描述

优化：加索引，调整where … order … select的索引顺序，修改SQL语句，由于in易失效，所以放在where条件的最后

EXPLAIN SELECT bid FROM book WHERE authorid=1 AND typeid IN (2,3) ORDER BY typeid DESC;
ALTER TABLE book ADD INDEX idx_tba(authorid,typeid,bid) ;

在这里插入图片描述

using index：使用了索引idx_tba中的第一个键authorid

using where：由于in语句使索引idx_tba的第二个键typeid失效，因此需要回表查询；输出的key_len为4也说明了索引只有第一个键生效

总结

最佳左前缀，保持索引的定义和使用的顺序一致
索引需要逐步优化
将含in的范围查询放到where条件的最后，以防索引失效

2.双表优化

CREATE TABLE teacher(
tid INT(4) PRIMARY KEY,
cid INT(4)
);
INSERT INTO teacher VALUES(1,2);
INSERT INTO teacher VALUES(2,1);
INSERT INTO teacher VALUES(3,3);

CREATE TABLE course(
cid INT(4),
cname VARCHAR(20)
);
INSERT INTO course VALUES(1,'java');
INSERT INTO course VALUES(2,'c++');
INSERT INTO course VALUES(3,'C#');

EXPLAIN SELECT * FROM teacher t LEFT OUTER JOIN course c ON t.cid = c.cid WHERE cname="java";

在这里插入图片描述

优化思路：

小表驱动大表：编程语言的优化原则，外层循环比内层循环的次数小，效率更高
索引建立在常用的字段上
一般情况，左外连接给左表加索引，右外连接给右表加索引

ALTER TABLE teacher ADD INDEX idx_teacher_cid(cid)
EXPLAIN SELECT * FROM teacher t LEFT OUTER JOIN course c ON t.cid = c.cid WHERE cname="java";

在这里插入图片描述

再次优化：给cname添加索引

ALTER TABLE course ADD INDEX idx_course_cname(cname)

在这里插入图片描述

总结：

小表驱动大表：编程语言的优化原则，外层循环比内层循环的次数小，效率更高
索引建立在常用的字段上
一般情况，左外连接给左表加索引，右外连接给右表加索引

3.多表优化

原则与上面的相同

6.避免索引失效的一些原则

复合索引
- 最佳左前缀
- 尽量使用全索引匹配
- 不能使用不等（!=、<>）或is null（is not null），否则自身及右侧索引全部失效
不要在索引上进行任何操作（计算，函数，类型转换），否则索引失效，如下例子：

计算：select * form tb where a*2 = 1; --对于复合索引，任然需要符合最佳左前缀
SQL优化是一种概率层面的优化，至于是否实际使用了手动优化，需要通过explain进行推测
- 概率情况原因是因为服务层中有SQL优化器，可能影响手动的优化
- 索引优化是一个大部分情况都适用的结论，但由于SQL优化器等原因，优化效果不能绝对肯定
补救措施：使用索引覆盖（using index）
- ```
select a,b,c from tb where a=. b=. c=. ; -- 复合索引(a,b,c)
```
范围查询（>、<、in）之后的索引失效

like尽量以"常量"开头，不要用’%'开头，否则索引失效

select * from tb where name like '%field%'; --name索引失效;type=all;using where
select * from tb where name like 'field%'; --type=range;using where

select name from tb where name like '%field%'; --name索引覆盖：挽救;type=index

尽量不要使用类型转换（显示、隐式），否则索引失效
使用or会导致索引全部失效

7.一些其它的优化方法

（1）exist和in

select .. from table where exist/in (子查询);
select .. from table where 字段 in (子查询);

如果主查询的数据集大，则使用in，效率更高；

如果子查询的数据集大，则使用exist，效率更高

exist语法：将主查询的结果，放到子查询结果中进行条件校验（看子查询是否有数据，如果有数据，则校验成功，如果符合校验，则保留数据

select tname from teacher where exist (select * from teacher);
--等价于select tname from teacher

在这里插入图片描述

in语法：

select .. from table where tid in (1,3,5);

（2）order by优化

using filesort有两种算法：双路排序、单路排序（根即IO的次数）

MySQL4.1之前默认使用双路排序；之后默认使用单路排序

双路排序：

扫描2次磁盘
第一次从磁盘读取排序字段，对排序字段进行排序（在buffer中进行）
第二次扫描其它字段
IO较消耗性能

单路排序：

只读取一次（全部字段），在buffer中进行排序
单路排序有一定的隐患，因为它不一定是真的“单路”（一次IO），有可能多次IO，原因是当数据量特别大，则无法将所有字段的数据一次性读取完毕，因此会进行“分片读取”
单路排序比双路排序占用更多的buffer
单路排序在使用时，如果数据特别大，可以考虑调大buffer的容量：set max_length_for_sort_data=1024 byte。如果此值太低（需要排序的列的总大小超过了max_length_for_sort_data定义的字节数），则MySQL会自动从单路->双路

提高order by查询的策略

选择使用单路、双路，并调整buffer的大小
避免select *…，这样可能会导致索引覆盖失效
复合索引不要跨列使用，避免using filesort
保证全部的排序字段，排序的一致性（都desc或asc）
- ~~order a1 asc, a2 desc~~

8.SQL排查 - 慢查询日志

慢查询日志是MySQL提供的一种日志记录，用于记录MySQL响应时间超过阈值的SQL语句，long_query_time 默认是10s，超过10s就是慢查询SQL

慢查询日志默认是关闭的，建议开发调优打开，部署关闭

查询MySQL是否打开了慢查询日志：

show variables like '%slow_query_log%'

临时开启：

set global slow_query_log=1 --在内存中开启；需要重启服务才能查询
exit 
service mysql restart

永久开启：

vi /etc/my.cnf
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log
:wq

慢查询阈值：

show variables like '%long_query_time%';

设置临时阈值

set global long_query_time=5 --重新登录起效（不需要）

设置永久阈值

vi /etc/my.cnf
[mysqld]
long_query_time=3;
:wq

查询超过阈值的SQL

show global status like '%slow_queries%'

查看慢SQL

慢查询的SQL被记录在了慢查询日志文件中，可以通过日志查看具体的SQL
```
cat /var/lib/mysql/localhost-slow.log
```

通过mysqldumpslow工具查看慢SQL

mysqldumpslow --help
####
s：排序方式
r：逆序
l：锁定时间
g：正则匹配模式

几个示例
--获取返回记录最多的三个SQL
mysqldumoslow -s r -t 3 /var/lib/mysql/localhost-slow.log
--获取访问次数最多的3个SQL
mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log
--按照时间排序，前十条包含left join查询语句的SQL
mysqldumoslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log

9.分析海量数据

模拟海量数据（存储过程、存储函数、使用Java批量插入），具体略

分析海量数据：

(1)profiles
show profiles; --默认关闭
show variables like '%prifiling%';
set profiling = on;
show profiles; --会记录所有prifiling打开之后的 全部SQL查询语句；不够精确，只能看到总共耗费的时间，不能看到各个硬件花费的时间（cpu、io....）

(2)精确分析：SQL诊断
show profile all for query; -- 上一步查询的Qeury_Id
show profile cpu,io for query; --同

(3)全局查询日志：记录开启之后的全部SQL语句（开发可开，部署需关）
show variables like '%general_log%';

执行的所有SQL记录在表中
set global general_log = 1; --开启全局日志
set global log_output = 'table'; --table是表名

set global general_log_file = '/tmp/general_log';
--开启后，会记录所有SQL到mysql.general_log中
select * from mysql.general_log;

cat /tmp/general_log;

10.锁机制：解决资源共享而造成的并发问题

测试

会话（session）：每一个访问数据的dos命令行、数据库客户端工具，都是一个会话

查看加锁的表

show open tables;

释放锁

unlock table

加读锁

对A表加读锁的会话（会话0）：如果某一个会话对A表加了read锁，那么该会话可以对A表进行读操作，不能进行写操作；且该会话不能对其它表进行读写操作。即给A表加了读锁，则当前会话只能对A表进行读操作，不能操作其它表
其它会话（会话1）：可以对A表读操作，写操作要等到会话0释放锁；可以对除A之外的表读写操作

总结（会话0给A表加读锁）：

会话0
- 对A可读，不可写
- 不能操作其它表
其它会话
- 可以对除A外的表进行读写操作
- 对A表可读，写要等待会话0释放读锁

加写锁

总结（会话0给A表加写锁）

会话0
- 可以对A表进行任何操作
- 不能操作其它表
其它会话
- 不能对A表进行读操作，写操作要等待A表的写锁释放
- 可以对除A之外的表进行读写操作

MySQL表级锁的锁模式

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（DML）前，会自动给涉及的表加写锁

所有对MyISAM表进行操作，会有以下情况：

对MyISAM表的读操作（加读锁），不会阻塞其它会话（进程）对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它会话的写操作
对MyISAM表的写操作（加写锁），会阻塞其它会话（进程）对同一表的读写操作，只有写锁释放后，才会执行其它会话的读写操作

分析表锁定

查看哪些表加了锁：

show open tables; --1代表被加了锁

在这里插入图片描述

分析表锁定的严重程度：

show status like 'table%';
Table_locks_immediate; --可能获取到的锁
Table_locks_waited; --需要等待的表锁树；该值越大，说明存在越大的锁竞争

在这里插入图片描述

36：可以给表加36个锁

在这里插入图片描述

一般建议，Table_locks_immediate/Table_locks_waited > 5000（左右），建议采用InnoDB引擎，否则用MyISAM

11.行锁（InnoDB）

创建测试表

create table linelock(
id int(5) primary key auto_increment,
name varchar(20)
)engine=innodb;
--插入4条数据
insert into linelock(name) values("a1");
insert into linelock(name) values("a2");
insert into linelock(name) values("a3");
insert into linelock(name) values("a4");
insert into linelock(name) values("a5");
--mysql默认自动commit；oracle不会自动commit

为了研究行锁，暂时将自动commit关闭

set autocommit = 0; --注意之后需要commit

测试1：不同会话操作相同数据

会话0：写操作

insert into linelock(name) values("a6");

会话1（对数据库操作，而不是操作缓存）：更新

update linelock set name = 'ax' where id=6;
--更新时发现此数据被加锁了，直到其它会话将该锁释放后，才能操作

经过测试，会话0只有在commit之后，会话1才能update。

对行锁情况：

如果会话X对某条数据进行DML操作（前提是关闭了字段commit），则其它会话必须等待会话X结束事务（commit/rollback）后，才能对数据A进行操作
表锁是通过unlock tables解锁；行锁通过事务（commit/rollback）解锁

测试2：不同会话操作不同数据

行锁，一次锁一行数据，不同会话操作不同数据互不干扰

行锁注意事项：

如果没有索引，行锁转为表锁

show index from linelock;
alter table linelock add index idx_linelock_name(name);

测试1

--会话0：写操作
update linelock set name="as2" where name = 'a2';
--会话1：写操作
update linelock set name="as3" where name = 'a3';

测试2

--会话0：写操作
update linelock set name="as3" where name = 3;
--会话1：写操作
update linelock set name="as4" where name = 4;

数据被阻塞了（加锁），原因是索引类发生了类型转换，索引失效，行锁转化为表锁

间隙锁（行锁的一种特殊情况）

间隙锁：值在查询范围内，但是范围内有数据不存在（如id是2-8，id=7不存在）
```
--此时linelock中没有id=7的数据
update linelock set name = 'xxx' where id > 1 and id < 9;
--自动给id=7的数据加间隙锁
```
如果有where，则实际加锁的范围就是where的范围，而不是实际的值
如果仅仅是查询数据，也能加锁

关闭自动提交
```
set autocommit=0;
start transaction;
begin;
--三种方式等价
```
会话0
```
select * from linelock where id = 2 for update;
```
会话1
```
update table linelock set name = 'asx2' where id = 2;
```
发现会话1要等到会话0事务结束之后才能进行操作，原因是通过for update对query语句进行了加锁

总结

行锁：InnoDB默认采用行锁

缺点：性能损耗不表锁大
优点：并发能力强

因此，高并发建议使用InnoDB

行锁分析

show status like '%innodb_row_lock%';
###列解析###
Innodb_row_lock_current_waits：当前正在等待锁的数量
Innodb_row_lock_time：等待总时长
Innodb_row_lock_avg：平均等待时长，从系统启动到现在平均等待的时间
Innodb_row_lock_time_max：最大等待时长，从系统启动到现在最大一次等待时间
Innodb_row_lock_waits：等待次数

CCCZEDH

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL索引

MySQL索引1. 原理MySQL逻辑分层：连接层、服务层、引擎层、存储层InnoDB（默认）：事务优先（适合高并发操作：行锁）MyISAM：性能优先（表锁）查询数据库引擎支持哪些引擎show engines查看当前使用的引擎show variables like '%storage_engine%'指定数据库对象的引擎create table tb ( id int(4) auto_increment, `name` varchar(10), dept
复制链接

扫一扫