超全MySQL学习笔记
导读
MyISAM和InnoDB
| 对比 | MyISAM | InnoDB |
|---|---|---|
| 主外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，操作时即使操作一条记录也会锁住一整张表，不适合高并发的操作 | 行锁，操作时只锁住某一行，不会影响到其他行，适合高并发 |
| 缓存 | 只缓存索引，不缓存其他数据 | 缓存索引和真实数据，对内存要求较高，而且内存大小对性能有影响 |
| 表空间 | 小 | 大 |
| 关注点 | 性能 | 事务 |
| 默认安装 | Y | Y |
性能下降SQL慢的原因：
- 查询语句写的差
- 索引失效
- 关联查询太多join （设计缺陷或不得已的需求）
- 服务器调优及各个参数设置(缓冲，线程参数)
Mysql执行顺序
- 手写
- 机读先从from开始
SQLJoin
a表
mysql> select * from tbl_dept; +----+----------+--------+ | id | deptName | locAdd | +----+----------+--------+ | 1 | RD | 11 | | 2 | HR | 12 | | 3 | MK | 13 | | 4 | MIS | 14 | | 5 | FD | 15 | +----+----------+--------+ 5 rows in set (0.00 sec)
b表
+----+------+--------+ | id | name | deptId | +----+------+--------+ | 1 | z3 | 1 | | 2 | z4 | 1 | | 3 | z5 | 1 | | 4 | w5 | 2 | | 5 | w6 | 2 | | 6 | s7 | 3 | | 7 | s8 | 4 | | 8 | s9 | 51 | +----+------+--------+ 8 rows in set (0.00 sec)
mysql不支持全连接
使用以下方式可以实现全连接
mysql> select * from tbl_dept a right join tbl_emp b on a.id=b.deptId
-> union
-> select * from tbl_dept a left join tbl_emp b on a.id=b.deptId;
+------+----------+--------+------+------+--------+
| id | deptName | locAdd | id | name | deptId |
+------+----------+--------+------+------+--------+
| 1 | RD | 11 | 1 | z3 | 1 |
| 1 | RD | 11 | 2 | z4 | 1 |
| 1 | RD | 11 | 3 | z5 | 1 |
| 2 | HR | 12 | 4 | w5 | 2 |
| 2 | HR | 12 | 5 | w6 | 2 |
| 3 | MK | 13 | 6 | s7 | 3 |
| 4 | MIS | 14 | 7 | s8 | 4 |
| NULL | NULL | NULL | 8 | s9 | 51 |
| 5 | FD | 15 | NULL | NULL | NULL |
+------+----------+--------+------+------+--------+
9 rows in set (0.00 sec)
a的独有和b的独有
mysql> select * from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.id is null
-> union
-> select * from tbl_dept a right join tbl_emp b on a.id=b.deptId where a.id is null;
+------+----------+--------+------+------+--------+
| id | deptName | locAdd | id | name | deptId |
+------+----------+--------+------+------+--------+
| 5 | FD | 15 | NULL | NULL | NULL |
| NULL | NULL | NULL | 8 | s9 | 51 |
+------+----------+--------+------+------+--------+
2 rows in set (0.01 sec)
索引
索引的定义：
索引是帮助SQL高效获取数据的数据结构，索引的本质：数据结构
可以简单的理解为：排好序的快速查找数据结构
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式（引用）指向数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引，下图就是一种示例：
一般来说索引也很大，因此索引往往以索引文件的方式存储在磁盘上
我们平常所说的索引，如果没有特别指明，一般都是指B树（多路搜索树，不一定是二叉的）结构组织的索引，
其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引，当然除了B+树这种类型的索引之外，还有哈希索引。
索引的优劣
1.优势
类似大学图书馆图书编号建索引，提高了数据检索的效率，降低数据库的IO成本
通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗
2.劣势
实际上索引也是一张表，该表保存了主键与存在索引的字段，并指向实体表的记录，所以索引列也是占用空间的
虽然索引大大提高了查询速度，但是会降低更新表的速度，比如 update，insert，delete操作，因为更新表时，MySQL不仅要数据也要保存索引文件每次更新添加了索引的字段，都会调整因为更新所带来的键值变化后的索引信息
索引只是提高效率的一个因素，在一个大数据量的表上，需要建立最为优秀的索引或者写优秀的查询语句，而不是加了索引就能提高效率
索引分类
- 单值索引
- 唯一索引
- 复合索引
- 基本语法:
创建
create [unique] index indexName on mytable(cloumnname(length));
alter mytable add [unique] index [indexName] on (columnname(length));
删除
drop index [indexName] on mytable
查看
show index from table_name\G
有四种方式来添加数据表的索引
mysql索引结构
- BTree索引
- Hash索引
- full-text全文索引
- R-Tree
那些情况建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表相关联的字段，外键关系建立索引
- 频繁更新的字段不适合创建索引，因为每次更新不单单更新了记录还更新了索引
- where条件里用不到的字段不要创建索引
- 单键/组合索引的选择问题 who？（高并发下建议组合索引）
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或分组字段
哪些情况不要建索引
- 表记录少
- 经常操作dml语句的表
- 数据重复且平均分布的表字段，因此只为最经常查询和最经常排序的数据列建立索引，注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果
性能分析
explian重点
能干什么
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以被使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
id三种情况
- id 相同，执行顺序由上至下
- id不同，如果是子查询，id序号递增，id越大优先级越高
- id相同不同 ，同时存在
select_type
- SIMPLE 简单查询
- PRIMARY 主查询 （最外层的查询）
- SUBQUERY 子查询
- DERIUED 某个查询的子查询的临时表
- UNION 联合查询
- UNION RESULT 联合查询结果
type：：
type显示的是访问类型排列，是较为重要的一个指标
从最好到最差依次是：
system > const > eq_ref> ref > range > index > ALL;
一般来说，得保证查询至少达到range级别，最好ref
----------------------------------------------type类型-------------------------------------------------------
- system:表只有一行记录（等于系统表） 这是const类型的特列 一般不会出现，可忽略不计
- const：表示通过索引一次就查询到了，const用来比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，Mysql就能将该查询转换为一个常量
- eq_ref：唯一性索引扫描，表中只有一条记录与之匹配，常用于主键或唯一索引扫描（两个表是多对一或者一对一的关系，被连接的表是一的情况下，他的查询是eq_ref）
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回匹配某个单独值的所有行，然而他可能会找到多个复合条件的行，属于查找和扫描的结合体
- range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般where语句里出现了betweent，<,>,in等的查询，这种范围扫描索引比全表扫描好
- index：index与ALL的区别，index只遍历索引树，索引文件通常比数据文件小
- ALL：全表扫描
----------------------------------------------type类型-------------------------------------------------------
- possible_keys：显示可能应用的的索引（理论上）
- key：实际使用的索引，查询中若使用了覆盖索引，则该索引仅仅出现在key中
- key_len：表示索引中使用的字节数，在不损失精度的情况下越短越好，kenlen显示的值为索引字段的最大可能长度，并非实际使用长度，kenlen是根据表定义计算而得，而不是通过表内检索出的
key_len长度：13是因为char(4)*utf8(3)+允许为null(1)=13
- ref：显示索引的哪一列被使用了，如果可能的话是一个常数，哪些列或常量被用于查找索引列上的值
- rows：根据表统计信息及索引选用情况，大致计算出找到所需的记录所需要读取的行数
没建立索引时查询t1 t2表 t1表对应t2表的id t2表 col1的值要为'ac'
对于Id这个字段t1表对t2表相当于 一对多
t1表的type为 eq_ref代表唯一性索引扫描，表中只有一条记录与之匹配，t2表对应t1的这个id对应的col值只有一个，根据t2表的主键id索引查询，t1表读取了一行，t2表读取了640行
建立索引后
t1读取一行，t2读取142行，ref非唯一性索引扫描，返回匹配某个单独值的所有行，返回t2对应id的col所有行，而t1对应id的col只有一行，所以type为eq_ref
Extra
包含不适合在其他列展现但十分重要的信息
\G ：竖直显示排序
- Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成排序的操作称为文件排序未被方框框住的图建立了复合索引，但是直接使用col3进行排序导致空中楼阁，mysql不得已只能进行filesoft
- Using temporary：使用了临时表保存中间中间结果，MySQL在对查询结果排序时使用临时表。常见于order by排序和group by分组上表中建立了复合索引 col1_col2 但是直接通过col2进行分组导致了mysql不得已只能进行filesoft和建立临时表
- using index 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，如果同时出现using where 表示索引被用来执行索引键值的查找，没有usingwhere表示索引用来读取数据而非执行查找动作
- using where 表示使用了 where过滤
- using join buffer 私用了链接缓存
- impossible buffer where子句的值总是false 不能用来获取任何元组
- select tables optimized away 在没有group by子句的情况下，基于索引优化min/max操作，或者对myisam存储引擎执行count(*)操作，不必等到执行操作进行，查询执行计划生成的阶段即完成优化
- distinct 优化distinct操作，在找到第一匹配的元组后立即停止查找同样值的操作
案例
索引优化
单表优化
CREATE TABLE IF NOT EXISTS `article`( `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `author_id` INT (10) UNSIGNED NOT NULL, `category_id` INT(10) UNSIGNED NOT NULL , `views` INT(10) UNSIGNED NOT NULL , `comments` INT(10) UNSIGNED NOT NULL, `title` VARBINARY(255) NOT NULL, `content` TEXT NOT NULL ); INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES (1,1,1,1,'1','1'), (2,2,2,2,'2','2'), (1,1,3,3,'3','3'); SELECT * FROM ARTICLE; mysql> select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1; +----+-----------+ | id | author_id | +----+-----------+ | 3 | 1 | +----+-----------+ 1 row in set (0.00 sec) mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc li imit 1; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)
可以看出虽然查询出来了 但是 type是all，Extra里面出现了using filesort证明查询效率很低
需要优化
建立索引
create index idx_article_ccv on article(category_id,comments,views);
查询
mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc limit 1; +----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | article | NULL | range | inx_article_ccv | inx_article_ccv | 8 | NULL | 1 | 100.00 | Using index condition; Using filesort | +----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+ 1 row in set, 1 warning (0.00 sec)
这里发现type 变为了 range 查询全表变为了 范围查询 优化了一点
但是 extra 仍然 有 using filesort 证明 索引优化并不成功
所以我们删除索引
drop index idx_article_ccv on article;
建立新的索引，排除掉range
create index idx_article_cv on article(category_id,views); mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc limit 1; +----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | article | NULL | ref | idx_article_cv | idx_article_cv | 4 | const | 2 | 33.33 | Using where | +----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 这时候会发现 优化成功 type 变为了ref extra变为了 using where 在这次实验中我又加入了一次试验 发现当建立索引时comments放在最后也是可行的 mysql> create index idx_article_cvc on article(category_id,views,comments); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc limit 1; +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | article | NULL | ref | idx_article_cvc | idx_article_cvc | 4 | const | 2 | 33.33 | Using where | +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
这时候会发现 优化成功 type 变为了ref extra变为了 using where
在这次实验中我又加入了一次试验 发现当建立索引时comments放在最后也是可行的
这里发现了 type仍然是ref，extra也是usingwhere，而只是把索引建立的位置换了一换，把范围查询的字段挪到了最后!!!!
双表优化
CREATE TABLE IF NOT EXISTS `class`( `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `card` INT (10) UNSIGNED NOT NULL ); CREATE TABLE IF NOT EXISTS `book`( `bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `card` INT (10) UNSIGNED NOT NULL ); 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))); mysql> create index Y on book(card); explain select * from book left join class on book.card=class.card; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | book | NULL | index | NULL | Y | 4 | NULL | 20 | 100.00 | Using index | | 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)
会发现并无多大区别 还是全表查询 这是因为俩表查询左连接把左表必须全查询 这时候只有对右表建立索引才有用
相反的右链接必须对左表建立索引才有用
对右表建立索引
create index Y on class; explain select * from book left join class on book.card=class.card; +----+-------------+-------+------------+-------+---------------+------+---------+----------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+----------------+------+----------+-------------+ | 1 | SIMPLE | book | NULL | index | NULL | Y | 4 | NULL | 20 | 100.00 | Using index | | 1 | SIMPLE | class | NULL | ref | Y | Y | 4 | db01.book.card | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+------+---------+----------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
会发现 右表只查询了一次。。type为ref
三表优化
CREATE TABLE IF NOT EXISTS `phone`( `phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `card` INT (10) UNSIGNED NOT NULL )ENGINE = INNODB; INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
先删除所有索引
drop index Y on book; drop index Y on class; explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL | | 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | phone | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 3 rows in set, 1 warning (0.00 sec)
建立索引
create index y on book(card); create index z on phone(card); explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card; +----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+ | 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL | | 1 | SIMPLE | book | NULL | ref | y | y | 4 | db01.class.card | 1 | 100.00 | Using index | | 1 | SIMPLE | phone | NULL | ref | z | z | 4 | db01.book.card | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec)
会发现索引建立的非常成功。。 但是left join 最左表必须全部查询 建立索引
create index x on class(card); explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card; +----+-------------+-------+------------+-------+---------------+------+---------+-----------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+-----------------+------+----------+-------------+ | 1 | SIMPLE | class | NULL | index | NULL | x | 4 | NULL | 20 | 100.00 | Using index | | 1 | SIMPLE | book | NULL | ref | y | y | 4 | db01.class.card | 1 | 100.00 | Using index | | 1 | SIMPLE | phone | NULL | ref | z | z | 4 | db01.book.card | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+------+---------+-----------------+------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec)
结果仍然一样
建立表
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
建立索引
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
索引口诀
- 1.带头大哥不能死，中间兄弟不能断:当建立复合索引时，必须带上头索引，不能跳过中间索引直接使用后面的索引，使用后面的索引必须加上中间的索引（可以先使用后面的索引再使用中间的索引，但是不能直接使用后面的索引而跳过中间索引）（针对where）
可以从上图看出 跳过name的都用不了索引
mysql> explain select * from staffs where name='july'; +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where name='july' and pos='dev'; +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 33.33 | Using index condition | +----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
可以从语句中看出跳过中间的索引后 key_len 不变 证明第索引pos没有被用到
- 2.不能对索引列进行任何操作（计算，类型转换 等等）
- 3.存储引擎不能使用索引中范围条件右边的列（索引列上少计算）
- 4.尽量使用覆盖索引，即是只访问索引的查询减少select *的用法
- 5.少使用（！=，<>,<,>） is not null ,is null;
- 6.like以 '%'开头会导致索引失效（使用覆盖索引课避免索引失效）覆盖索引：（建立的索引与查询的字段顺序数量尽量一致）
- 7.字符串不加单引号会导致索引失效（mysql会将字符串类型强制转换 导致索引失效）
- 8.少用or，用它连接会失效
索引案例
假设index（a，b，c）
Y代表索引全部使用了 N全没使用
| where语句 | 索引是否被使用 |
|---|---|
| where a=3 and c=5 | (中间b断掉了)使用了a 没使用c |
| where a=3 and b=4 andc=5 | Y |
| where a=3 and c=5 and b=4 | Y这里mysql自动做了优化对语句排序 |
| where a=3 and b>4 and c=5 | a,b被使用 |
| where a=3 and b like 'k%' and c=5 | Y like后面常量开头索引全用 |
| where b=3 and c=4 | N |
| where a=3 and c>5 and b=4 | Y:mysql自动做了优化对语句排序 范围c之后索引才会失效 |
| where b=3 and c=4 and a=2 | Y :mysql自动做了优化对语句排序 |
| where c=5 and b=4 and a=3 | Y :mysql自动做了优化对语句排序 |
假设index（a，b，c, d）
create table test03( id int primary key not null auto_increment, a int(10), b int(10), c int(10), d int(10), insert into test03(a,b,c,d) values (3,4,5,6); insert into test03(a,b,c,d) values (3,4,5,6); insert into test03(a,b,c,d) values (3,4,5,6); insert into test03(a,b,c,d) values (3,4,5,6); create index idx_test03_abcd on test03(a,b,c,d);
###
| where a=3 and b>4 and c=5 | 使用了a和b ，b后面的索引全失效 |
|---|---|
| where a=3 and b=4 and d=6 order by c | 使用了a和b，c其实也用了但是是用在排序，没有统计到mysql中 |
| where a=3 and b=4 order by c | 使用了a和b，c其实也用了但是是用在排序，没有统计到mysql中 |
| where a=3 and b=4 order by d | 使用了a和b, 这里跳过c 会导致using filesort |
| where a=3 and d=6 order by b ,c | 使用了a, 排序用到了b,c索引 |
| where a=3 and d=6 order by c ,b | 使用了 a，会产生using filesort，因为跳过了b对c进行排序 |
| where a=3 and b=4 order by b ,c | Y 全使用 |
| where a=3 and b=4 and d&##61;6 order by c , b | 使用了a，b，不会产生using filesort 因为在对c，b排序前对b进行了查询，查询时b已经确定了（常量），这样就没有跳过b对c进行排序了，而是相当于直接对c排序 相当于第三格的查询语句 |
group by 更严重group by先分组再排序 把order by换为 group by 甚至会产生using temporary，与order by差不多，但是更严重 而且与group by产生的效果差不多就不做演示了
Order By 索引优化
| orderBy 条件 | Extra |
|---|---|
| where a>4 order by a | using where using index |
| where a>4 order by a,b | using where using index |
| where a>4 order by b | using where, using index ,using filesort（order by 后面带头大哥不在） |
| where a>4 order by b,a | using where, using index ,using filesort（order by 后面顺序） |
| where a=const order by b,c | 如果where使用索引的最左前缀定义为常量，则order by能使用索引 |
| where a=const and b=const order by c | where使用索引的最左前缀定义为常量，则order by能使用索引 |
| where a=const and b>3 order by b c | using where using index |
| order by a asc, b desc ,c desc | 排序不一致 升降机 |
exsites
select a.* from A a where exists(select 1 from B b where a.id=b.id)
以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false. 它的查询过程类似于以下过程
List resultSet=[]; Array A=(select * from A)
for(int i=0;i<A.length;i++) { if(exists(A[i].id) { //执行select 1 from B b where b.id=a.id是否有记录返回 resultSet.add(A[i]); } } return resultSet;
当B表比A表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行. 如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等. 如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果. 再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.
Mysql慢查询日志命令
show VARIABLES like '%slow_query_log%';
显示是否开启mysql慢查询日志
set global slow_query_log=0;
关闭mysql慢查询日志
set global slow_query_log=1;
开启mysql慢查询日志
show VARIABLES like '%long_query_time%';
显示超过多长时间即为 慢查询
set global long_quert_time=10;
修改慢查询时间为10秒，当查询语句时间超过10秒即为慢查询
show global status like '%Slow_queries%';
显示一共有几条慢查询语句
[root@iZ0jlh1zn42cgftmrf6p6sZ data]# cat mysql-slow.log
linux查询慢sql
函数操作批量插入数据
CREATE TABLE dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT '',
loc VARCHAR(13) NOT NULL DEFAULT ''
)ENGINE=INNODB DEFAULT CHARSET=GBK;
CREATE TABLE emp(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, #编号
enname VARCHAR(20) NOT NULL DEFAULT '', #名字
job VARCHAR(9) NOT NULL DEFAULT '', #工作
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, #上级编号
hiredate DATE NOT NULL, #入职时间
sal DECIMAL(7,2) NOT NULL, #薪水
comm DECIMAL(7,2) NOT NULL, #红利
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 #部门编号
)ENGINE=INNODB DEFAULT CHARSET=GBK;
show variables like 'log_bin_trust_function_creators'; set global log_bin_trust_function_creators=1;
创建函数：随机产生部门编号 随机产生字符串
DELIMITER $$是因为sql都是；进行结尾但是创建函数过程要多次使用；所以改变sql执行结束的条件为输入$$，相当于代替了分号' ；'
//定义函数1
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_set VARCHAR(100) DEFAULT 'abcdefghigklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_set,FLOOR(1 + RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
//定义函数2
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100 + RAND()*10);
RETURN i;
END $$
//定义存储过程1
DELIMITER $$
CREATE PROCEDURE insert_emp(IN start INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp(empno, enname, job, mgr, hiredate, sal, comm, deptno) VALUES((start + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
//定义存储过程2
DELIMITER $$
CREATE PROCEDURE insert_dept(IN start INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept(deptno,dname,loc) VALUES((start + i),rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
//开始插入数据
DELIMITER ;
call insert_dept(100,10);
call insert_emp(100001,500000);
show Profile分析sql
mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> set profiling=on; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | ON | +---------------+-------+ 1 row in set (0.01 sec)
随便写几条插入语句‘
显示查询操作语句的速度
mysql> show profiles; +----------+------------+----------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+----------------------------------------------------------------+ | 1 | 0.00125325 | show variables like 'profiling' | | 2 | 0.00018850 | select * from dept | | 3 | 0.00016825 | select * from tb1_emp e inner join tbl_dept d on e.deptId=d.id | | 4 | 0.00023900 | show tables | | 5 | 0.00031125 | select * from tbl_emp e inner join tbl_dept d on e.deptId=d.id | | 6 | 0.00024775 | select * from tbl_emp e inner join tbl_dept d on e.deptId=d.id | | 7 | 0.00023725 | select * from tbl_emp e inner join tbl_dept d on e.deptId=d.id | | 8 | 0.00023825 | select * from tbl_emp e left join tbl_dept d on e.deptId=d.id | | 9 | 0.35058075 | select * from emp group by id%10 limit 15000 | | 10 | 0.35542250 | select * from emp group by id%10 limit 15000 | | 11 | 0.00024550 | select * from tbl_emp e left join tbl_dept d on e.deptId=d.id | | 12 | 0.36441850 | select * from emp group by id%20 order by 5 | +----------+------------+----------------------------------------------------------------+ 12 rows in set, 1 warning (0.00 sec)
显示查询过程 sql生命周期
mysql> show profile cpu,block io for query 3; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000062 | 0.000040 | 0.000021 | 0 | 0 | | checking permissions | 0.000004 | 0.000003 | 0.000001 | 0 | 0 | | checking permissions | 0.000015 | 0.000006 | 0.000003 | 0 | 0 | | Opening tables | 0.000059 | 0.000039 | 0.000020 | 0 | 0 | | query end | 0.000004 | 0.000002 | 0.000001 | 0 | 0 | | closing tables | 0.000002 | 0.000001 | 0.000000 | 0 | 0 | | freeing items | 0.000014 | 0.000010 | 0.000005 | 0 | 0 | | cleaning up | 0.000009 | 0.000006 | 0.000003 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+ 8 rows in set, 1 warning (0.00 sec) mysql> show profile cpu,block io for query 12; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000063 | 0.000042 | 0.000021 | 0 | 0 | | checking permissions | 0.000006 | 0.000003 | 0.000002 | 0 | 0 | | Opening tables | 0.000013 | 0.000009 | 0.000004 | 0 | 0 | | init | 0.000028 | 0.000017 | 0.000008 | 0 | 0 | | System lock | 0.000007 | 0.000004 | 0.000002 | 0 | 0 | | optimizing | 0.000004 | 0.000002 | 0.000002 | 0 | 0 | | statistics | 0.000014 | 0.000010 | 0.000004 | 0 | 0 | | preparing | 0.000008 | 0.000005 | 0.000003 | 0 | 0 | | Creating tmp table | 0.000028 | 0.000018 | 0.000009 | 0 | 0 | | Sorting result | 0.000003 | 0.000002 | 0.000001 | 0 | 0 | | executing | 0.000002 | 0.000002 | 0.000001 | 0 | 0 | | Sending data | 0.364132 | 0.360529 | 0.002426 | 0 | 0 | | Creating sort index | 0.000053 | 0.000034 | 0.000017 | 0 | 0 | | end | 0.000004 | 0.000002 | 0.000002 | 0 | 0 | | query end | 0.000007 | 0.000005 | 0.000002 | 0 | 0 | | removing tmp table | 0.000005 | 0.000003 | 0.000002 | 0 | 0 | | query end | 0.000003 | 0.000002 | 0.000001 | 0 | 0 | | closing tables | 0.000006 | 0.000004 | 0.000002 | 0 | 0 | | freeing items | 0.000023 | 0.000016 | 0.000007 | 0 | 0 | | cleaning up | 0.000012 | 0.000007 | 0.000004 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+ 20 rows in set, 1 warning (0.00 sec)
如果出现以上这四个 中的任何一个就需要 优化查询语句
全局查询日志
set global general_log=1; set global log_output='TABLE';
此后你编写的sql语句将会记录到mysql库里的general_log表，可以用下面的命令查看
select * from mysql.general_log; mysql> select * from mysql.general_log; +----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ | event_time | user_host | thread_id | server_id | command_type | argument | +----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ | 2021-12-06 11:53:53.457242 | root[root] @ localhost [] | 68 | 1 | Query | select * from mysql.general_log | +----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ 1 row in set (0.00 sec)
Mysql锁
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
- 写锁（排它锁）：当前写操作没有完成时，它会阻断其他写锁和读锁
- 行锁：偏向InnoDB引擎，开销大，加锁慢，会出现死锁：锁定粒度最小，发生锁冲突的概率最低，并发量高
- 表锁：偏向myisam引擎，开销小，加锁快；锁定粒度大，发生锁冲突的概率最高，并发度最低
在下面进行表锁的测试
use big_data;
create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) engine myisam;
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
select * from mylock;
锁命令
lock table mylock read,book write;## 读锁锁mylock 写锁锁book show open tables; ##显示哪些表被加锁了 unlock tables;##取消锁
表锁：读锁
##添加读锁后不可修改 mysql> lock table mylock read;##1 Query OK, 0 rows affected (0.00 sec) mysql> select * from mylock;##1 +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec) mysql> update mylock set name='a2' where id=1; ##1 ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated ##改不了当前读锁锁住的表 ##读不了其他表 mysql> select * from book;##1 ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
为了区分两个命令 把1当作原有的mysql命令终端上的操作，2当作新建的mysql终端
新建一个mysql终端命令操作
##新建一个mysql终端命令操作 mysql> update mylock set name='a3' where id=1; ##2
发现会出现阻塞操作
在原有的mysql命令终端上取消锁
unlock tables;##1 Query OK, 1 row affected (2 min 1.46 sec) ##2 Rows matched: 1 Changed: 1 Warnings: 0 ##2
会发现阻塞了两分钟多
总结 ：当读锁锁表mylock之后：1.查询操作：当前client（终端命令操作1）可以进行查询表mylock
其他client（终端命令操作2）也可以查询表mylock 2.DML操作（增删改）当前client会失效报错 ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated 其他client进行DML操作会让mysql陷入阻塞状态直到当前session释放锁
表锁：写锁
mysql> lock table mylock write; Query OK, 0 rows affected (0.00 sec) 给当前session mylock表加上写锁 mysql> update mylock set name='a4'where id=1 ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a4 | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ mysql> select * from book; ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
会发现无法操其他表但是可以操作加上锁的表
再开启一个新的客户端测试被锁住的表
mysql> select * from mylock; 5 rows in set (2 min 30.92 sec)
发现新的客户端上操作（增删改查）被写锁锁住的表会陷入阻塞状态
作
分析表锁定
mysql> show status like 'table%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Table_locks_immediate | 194 | | Table_locks_waited | 0 | | Table_open_cache_hits | 18 | | Table_open_cache_misses | 2 | | Table_open_cache_overflows | 0 | +----------------------------+-------+ 5 rows in set (0.00 sec)
行锁
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
由于行锁支持事务，在此复习一下
事务
事务是一组由SQL语句组成的逻辑处理单元，事务具有四个属性：ACID
- 原子性（Atomicity）：事务是一个原子操作单元，其对数据的操作要么全部执行，要么全不执行。
- 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
- 隔离性（Isolation）：数据库提供一定的隔离机制，保证事务在不受外部并发操作影响的"独立"环境执行。这意味着事务处理过程的中间状态对外部都是不可见的，反之亦然。
- 持久性（Durable）：事务完成后，它对数据的操作是永久性的，哪怕出现系统故障也能维持
并发事务带来的问题：
更新丢失，脏读，不可重复读，幻读
| ACID属性 | 含义 |
|---|---|
| 原子性（Atomicity） | 事务是一个原子操作单元，其对数据的修改，要么全部成功，要么全部失败。 |
| 一致性（Consistent） | 在事务开始和完成时，数据都必须保持一致状态。 |
| 隔离性（Isolation） | 数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的 “独立” 环境下运行。 |
| 持久性（Durable） | 事务完成之后，对于数据的修改是永久的。 |
并发事务处理带来的问题
| 问题 | 含义 |
|---|---|
| 丢失更新（Lost Update） | 当两个或多个事务选择同一行，最初的事务修改的值，会被后面的事务修改的值覆盖。 |
| 脏读（Dirty Reads） | 当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。 |
| 不可重复读（Non-Repeatable Reads） | 一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现和以前读出的数据不一致。 |
| 幻读（Phantom Reads） | 一个事务按照相同的查询条件重新读取以前查询过的数据，却发现其他事务插入了满足其查询条件的新数据。 |
事务隔离级别
为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行，这显然与“并发” 是矛盾的。
数据库的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。
| 隔离级别 | 丢失更新 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| Read uncommitted | × | √ | √ | √ |
| Read committed | × | × | √ | √ |
| Repeatable read（默认） | × | × | × | √ |
| Serializable | × | × | × | × |
备注 ： √ 代表可能出现 ， × 代表不会出现 。
Mysql 的数据库的默认隔离级别为 Repeatable read ， 查看方式：
show variables like 'tx_isolation';
行锁测试建表， 案例准备工作
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);
行锁测试
还是开俩个终端测试，关闭事自动事务提交，因为自动事务提交会自动加锁释放锁；
mysql> set autocommit=0; mysql> set autocommit=0;
会发现查询无影响
对左边进行更新操作
mysql> update test_innodb_lock set name='100' where id=3; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0
对左边进行更新操作
对右边进行更新操作后停止操作
mysql> update test_innodb_lock set name='340' where id=3; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
会发现进行阻塞了 直到锁释放或者提交事务（commit）为止
对于innodb引擎来说，对某一行数据进行DML（增删改）操作会对操作的那行添加排它锁
别的事务就不能执行这行语句了，但是可以操作其他行的数据
无索引行锁会升级成表锁：如果不通过索引条件检索数据，那么innodb会对表中所有记录加锁，实际效果和表锁一样
记住进行操作时使用索引：innodb引擎索引失效时时行锁会升级为表锁
mysql> update test_innodb_lock set sex='2' where name=400; Query OK, 0 rows affected (0.00 sec) Rows matched: 2 Changed: 0 Warnings: 0
注意这里name没有加单引号 索引失效
mysql> update test_innodb_lock set sex='3' where id=3; Query OK, 1 row affected (23.20 sec) Rows matched: 1 Changed: 1 Warnings: 0
发现了对其他行操作也陷入了阻塞状态，这是没加索引导致的行锁升级为表锁
本来只对一行数据加锁 但是由于忘记给name字段加单引号导致索引失效给全表都加上了锁；
间隙锁
当我们使用范围条件而不是想等条件进行检索数据，并请求共享或排它锁，在那个范围条件中有不存在的记录，叫做间隙，innodb也会对这个间隙进行加锁，这种锁机制就叫做间隙锁
mysql> select * from test_innodb_lock; +------+------+------+ | id | name | sex | +------+------+------+ | 1 | 100 | 2 | | 3 | 100 | 3 | | 4 | 400 | 0 | | 5 | 500 | 1 | | 6 | 600 | 0 | | 7 | 700 | 3 | | 8 | 800 | 1 | | 9 | 900 | 2 | | 1 | 200 | 0 | +------+------+------+ 没有id为2的数据
行锁征用情况查看
mysql> show status like 'innodb_row_lock%'; +-------------------------------+--------+ | Variable_name | Value | +-------------------------------+--------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 284387 | | Innodb_row_lock_time_avg | 21875 | | Innodb_row_lock_time_max | 51003 | | Innodb_row_lock_waits | 13 | +-------------------------------+--------+ 5 rows in set (0.00 sec) Innodb_row_lock_current_waits: 当前正在等待锁定的数量 Innodb_row_lock_time: 从系统启动到现在锁定总时间长度 Innodb_row_lock_time_avg:每次等待所花平均时长 Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间 Innodb_row_lock_waits: 系统启动后到现在总共等待的次数
行锁总结
InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。
但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。
优化建议：
- 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。
- 合理设计索引，尽量缩小锁的范围
- 尽可能减少索引条件，及索引范围，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度
- 尽可使用低级别事务隔离（但是需要业务层面满足需求）
猜你喜欢
评论(0)
