MySQL的实战使用

TheLongir

已于 2022-10-18 10:53:03 修改

阅读量1.8k

点赞数 4

分类专栏： SQL 文章标签： mysql

于 2022-05-05 17:36:13 首次发布

本文链接：https://blog.csdn.net/TheLongir/article/details/124271550

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文档背景：

由于项目需要频繁观察请求响应时间，监控底层数据，所以掌握好底层MySQL是管理项目的核心之一。
再加上，知识点容易遗忘，所以整理一篇常用的MySQL常用的一些指令、优化DML,DQL的方法。

1、SQL技巧

1.1、编写顺序

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>

1.2、执行顺序

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>

1.3、正则表达式

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

select * from emp where name regexp '^T'; 
select * from emp where name regexp '2$'; 
select * from emp where name regexp '[uvw]';

1.4、MySQL常用函数

数字函数
在这里插入图片描述
字符串函数

日期函数

聚合函数

2、索引

想要优化查询，第一个想到的就是索引了。
MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。所以说，索引是一个数据结构。
那么MySQL是怎么根据索引去读数据的呢?
取第一个数为根节点，后面的节点依次向下比较。如果小则接入在左侧，大则接入在右侧。B+数的查询规则是查询到叶子节点后才触底反弹回到根节点。
在这里插入图片描述

innodb索引的默认数据结构是B+树。
Tip：B+树是一种树数据结构，通常用于数据库和操作系统的文件系统中。B+树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+树元素自底向上插入（也可以说是触底反弹），这与二叉树恰好相反。

假如这时我们要查询col的值为45的一行数据
没建立索引的时候遍历 7行才能查到该行地址
建立索引的时候遍历 45>33 走右边，45<88走左边，45=45返回该行地址。很显然3次就查到了直接减少了近似一半时间

2.1、准备环境

CREATE DATABASE demo_01 DEFAULT charset = utf8mb4;
USE demo_01;
CREATE TABLE `city` (
	`city_id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`city_name` VARCHAR ( 50 ) NOT NULL,
	`country_id` INT ( 11 ) NOT NULL,
	PRIMARY KEY ( `city_id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;

CREATE TABLE `country` (
	`country_id` INT ( 11 ) NOT NULL AUTO_INCREMENT, 
	`country_name` VARCHAR ( 100 ) NOT NULL, 
	 PRIMARY KEY ( `country_id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `city` ( `city_id`, `city_name`, `country_id` )
VALUES
	( 1, '西安', 1 );
INSERT INTO `city` ( `city_id`, `city_name`, `country_id` )
VALUES
	( 2, 'NewYork', 2 );
INSERT INTO `city` ( `city_id`, `city_name`, `country_id` )
VALUES
	( 3, '北京', 1 );
INSERT INTO `city` ( `city_id`, `city_name`, `country_id` )
VALUES
	( 4, '上海', 1 );
INSERT INTO `country` ( `country_id`, `country_name` )
VALUES
	( 1, 'China' );
INSERT INTO `country` ( `country_id`, `country_name` )
VALUES
	( 2, 'America' );
INSERT INTO `country` ( `country_id`, `country_name` )
VALUES
	( 3, 'Japan' );
INSERT INTO `country` ( `country_id`, `country_name` )
VALUES
	( 4, 'UK' );

2.2、创建索引

#创建索引
create index idx_city_name on city(name)
#语法
create [unique|fulltext|spatial] index index_name [using index_type] on tb_name(col_name)

2.3、查看索引

show index from city

在这里插入图片描述

2.4、删除索引

drop index idx_city_name on city;
#语法
drop index index_name on tb_name;

2.5、ALTER命令

#column_list表示可以使多个列 中间用逗号隔开
#添加一个主键，意味着索引值必须是唯一的，且不能为NULL
alter table tb_name add primary key(column_list)
#创建索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)
alter table tb_name add unique index_name(column_list)
#添加普通索引，索引值可以出现多次
alter table tb_name add index index_name(column_list)
#该语句指定了索引为FULLTEXT，用于全文索引
alter table tb_name add fulltext index_name(column_list)

2.6、索引设计原则

根据《深入浅出MySQL》中，总结出索引的一些创建规则

对于查询频次高，且数据量比较大的表建立索引。
索引字段最好选择where子句的条件中提取，如果where子句的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合
使用唯一索引，区分度高，使用索引的效率就越高
索引数量不应过多，因为它也是一种数据结构，会占用存储空间，增加维护难度，也会使效率变低
使用短索引，尽量让索引的字段总长度比较短
利用索引的最左前缀法则，N个列组合而成的组合索引，那么相当于使创建了N个索引，如果查询时where子句使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率

创建复合索引
CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
就相当于
  对name 创建索引；
  对name，email创建索引
  对name，email，status创建了索引

2.7、避免索引失效

1）全值匹配,对索引中所有列都指定具体值
例：

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

2）最左前缀法则
如果索引了多列，要遵循最左前缀法则。指从索引的最左列开始，并且不跳过索引中的列。

如果过滤的字段符合最左前缀法则，又有跳跃的字段则符合的字段走最左索引
范围查询的右边的列不会走索引

3）索引失效

不要在索引列上进行运算操作，不然索引将失效。
字符串不加单引号，造成索引失效。
尽量使用覆盖索引，避免select *
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
示例，name字段是索引列，而createtime不是索引列，中间是or进行连接是不走索引的：
以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
如果MySQL评估使用索引比全表更慢，则不使用索引。
is NULL ， is NOT NULL 有时索引失效。
in 走索引， not in 索引失效。
单列索引和复合索引。尽量使用复合索引，而少使用单列索引。

2.8、查看索引使用情况

#默认session
show status like 'Handler_read%';
show global status like 'Handler_read%';

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。
Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。
Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。
Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

3、视图

通俗来讲，视图是一张虚拟的表。视图并不在数据库中实际存在。它是利用数据库里实际存在的表虚拟出来的。
一旦视图数据结构被定义好了，对源表的新增操作不会对视图有影响。源表修改列名，可以修改视图解决，但是修改视图数据会影响源表。
视图使用的不多，就贴一下语法吧

3.1、创建视图

CREATE [ OR REPLACE ] [ ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE }] VIEW view_name [( column_list )] AS select_statement [ WITH [ CASCADED | LOCAL ] CHECK OPTION]

3.2、修改视图

ALTER [ ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE }] VIEW view_name [( column_list )] AS select_statement [ WITH [ CASCADED | LOCAL ] CHECK OPTION]

选项 :
WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。 LOCAL ： 只要满足本视图的条件就可以更新。 CASCADED ： 必须满足所有针对该视图的所有视图的条件才可以更新。 默认值.

示例

create or replace view city_country_view asselect t.*,c.country_name from country c , city t where c.country_id = t.country_id;

3.3、查看视图

查看视图的方法是与查看表一模一样
从MySQL5.1版本开始，show tables 就可以显示该表的名字，也会显示视图的名字，所以就不存在单独显示视图的show views命令

show table_name

3.4、删除视图

DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]

示例

DROP VIEW city_country_view ;

4、触发器

4.1、介绍

触发器是与表有关的数据对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端保证数据的完整性，日志记录，数据校验等操作

触发器类型	NEW和OLD的使用
INSERT型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

4.2、创建触发器

CREATE TRIGGER 
trigger_name 
BEFORE / AFTER INSERT / UPDATE / DELETE 
ON tbl_name
[ FOR EACH ROW ] -- 行级触发器 
begin
	trigger_stmt ;
end;

示例

通过触发器记录emp表的数据变更日志，包含增加，修改，删除；

CREATE TABLE emp_logs (
	id INT ( 11 ) NOT NULL auto_increment,
	operation VARCHAR ( 20 ) NOT NULL COMMENT '操作类型, insert/update/delete',
	operate_time datetime NOT NULL COMMENT '操作时间',
	operate_id INT ( 11 ) NOT NULL COMMENT '操作表的ID',
	operate_params VARCHAR ( 500 ) COMMENT '操作参数',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT charset = utf8;

4.2.1、INSERT型触发器

创建 insert 型触发器，完成插入数据时的日志记录 :

CREATE TRIGGER emp_logs_insert_trigger AFTER INSERT ON emp FOR EACH ROW
BEGIN
		INSERT INTO emp_logs ( id, operation, operate_time, operate_id, operate_params )
	VALUES
		(
			NULL,
			'insert',
			now(),
			new.id,
		concat( '插入后(id:', new.id, ', name:', new.NAME, ', age:', new.age, ', salary:', new.salary, ')' ) 
	);
END

4.2.1、UPDATE型触发器

创建update型触发器，完成更新时的日志记录

CREATE TRIGGER emp_logs_update_trigger AFTER UPDATE ON emp FOR EACH ROW
BEGIN
		INSERT INTO emp_logs ( id, operation, operate_time, operate_id, operate_params )
	VALUES
		(
			NULL,
			'update',
			now(),
			new.id,
			concat(
				'修改前(id:',
				old.id,
				', name:',
				old.NAME,
				', age:',
				old.age,
				', salary:',
				old.salary,
				') , 修改后(id',
				new.id,
				'name:',
				new.NAME,
				', age:',
				new.age,
				', salary:',
				new.salary,
				')' 
			));
END

4.2.1、DELETE型触发器

创建delete行的触发器，完成删除数据时的日志记录：

CREATE TRIGGER emp_logs_delete_trigger AFTER DELETE ON emp FOR EACH ROW
BEGIN
		INSERT INTO emp_logs ( id, operation, operate_time, operate_id, operate_params )
	VALUES
		(
			NULL,
			'delete',
			now(),
			old.id,
		concat( '删除前(id:', old.id, ', name:', old.NAME, ', age:', old.age, ', salary:', old.salary, ')' ));

END

4.3、删除触发器

#语法
drop trigger [schema_name.]trigger_name

4.4、查看触发器

show triggers

5.存储引擎

5.1、查看MySql数据库默认存储引擎

show variables like '%storage_engine%';

5.2、存储引擎的选择

InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高
的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，
那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定，还
可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，
InnoDB是最合适的选择。
MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发
性要求不是很高，那么选择这个存储引擎是非常合适的。
MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。
MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数
据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结
果。
MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优
点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善
MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。

6、优化SQL

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息
默认为session

#用来查询某个MySQL运行状态的信息
show status

6.1、查看SQL执行频率

6.1.1、显示当前session中所有统计参数的值

#展示当前session会话语句频次
show status like 'Com_______';
#展示当前session的innodb存储引擎影响的行数
show status like 'Innodb_rows_%';

Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。
在这里插入图片描述
Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。
Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

6.2、定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的SQL语句

慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句，用–log-slow-queries[=file_name]选项启
动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。
show processlist : 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询
日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否
锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

#查看MySQL进程列表
show processlist

在这里插入图片描述

id       #ID标识，要kill一个语句的时候很有用
use      #当前连接用户
host     #显示这个连接从哪个ip的哪个端口上发出
db       #数据库名
command  #连接状态，一般是休眠（sleep），查询（query），连接（connect）
time     #连接持续时间，单位是秒
state    #显示当前sql语句的状态
info     #显示这个sql语句

其中state是最关键的，详细含义看:https://blog.csdn.net/weixin_30871701/article/details/96155576

6.3、explain分析执行计划

#分析该查询语句的执行计划
explain select * from city

在这里插入图片描述

详细说明：
1）id的值越大泽泽优先级越高越先被执行。如果相等则是从上往下执行
2）select_type常取的值
在这里插入图片描述
3）table表示这一行的数据是关于哪一张表的
4）type

至少能达到range级别以上就行了
5）extra

其他的字段没什么详细的含义

6.4、show profile分析SQL

#查看是否支持profile
select @@having_profiling

6.4.1、设置开关

#开启profiling开关
set profiling = 1;

6.4.2、如何使用profiles

#执行一系列SQL语句之后
#执行 查看SQL语句执行的耗时
show profiles

6.4.3、查看该SQL执行明细类型和详细过程

show profile [all|cpu|block io|context switch|page faults] for query [id]

TIP ：Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。
由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。

6.5、trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。
打开trace ， 设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能
够完整展示。

SET optimizer_trace="enabled=on",end_markers_in_json=on; 
set optimizer_trace_max_mem_size=1000000;

例：
执行SQL

select * from tb_item where id < 4;
#最后， 检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的 ：
select * from information_schema.optimizer_trace\G;

6.6、优化group by语句

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分
组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在
GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗，则可以执行order by null 禁止排序。如下：

drop index idx_emp_age_salary on emp; 
explain select age,count(*) from emp group by age;

在这里插入图片描述

优化后

explain select age,count(*) from emp group by age order by null;

在这里插入图片描述

6.7、优化嵌套查询

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL
操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接
（JOIN）替代。

示例，查找有角色的所有的用户信息 :

explain select * from t_user where id in (select user_id from user_role );

在这里插入图片描述
优化后

explain select * from t_user u , user_role ur where u.id = ur.user_id;

在这里插入图片描述

6.8、优化or条件

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索
引； 如果没有索引，则应该考虑增加索引。

#使用or条件
select * from emp where id = 1 or age = 30;
#建议使用union替换or
select * from emp where id = 1 union select * from emp where age = 30;

6.9、优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ，
此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非
常大 。

explain select * from tb_item limit 2000000,10;

有两种思路去优化分页查询

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

EXPLAIN SELECT * FROM tb_item t,( SELECT id FROM tb_item ORDER BY id LIMIT 2000000, 10 ) a 
WHERE
	t.id = a.id

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询。

explain select * from tb_item where id>2000000 limit 10;

6.10、使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目
的。

6.10.1、USE INDEX

建议查询时走某个索引

select * from tb_seller use index(idx_seller_name) where name ='小米科技'

6.10.2、IGNORE INDEX

查询时忽略一个或多个索引

select * from tb_seller ignore index(idx_seller_name) where name = '小米科技';

6.10.3、FORCE INDEX

强制MySQL走某一个特定的索引

select * from tb_seller force index(idx_seller_name) where name = '小米科技';

7、缓存

7.1、查询缓存配置

#查看当前的MySQL数据库是否支持查询缓存
SHOW VARIABLES LIKE 'have_query_cache';
# 查看当前MySQL是否开启了查询缓存
SHOW VARIABLES LIKE 'query_cache_type';
#查看查询缓存的占用大小
SHOW VARIABLES LIKE 'query_cache_size';
#查看查询缓存的状态变量
SHOW STATUS LIKE 'Qcache%';

在这里插入图片描述

7.2、开启查询缓存

MySQL的查询缓存默认是关闭的，需要手动配置参数 query_cache_type ， 来开启查询缓存。query_cache_type
该参数的可取值有三个 ：

在这里插入图片描述
在 /usr/my.cnf 配置中，增加以下配置：

#开启mysql的查询缓存
query_cache_type=1

7.3、查询缓存SELECT选项

可以在SELECT语句中指定两个与查询缓存相关的选项 ：
SQL_CACHE : 如果查询结果是可缓存的，并且 query_cache_type 系统变量的值为ON或 DEMAND ，则缓存查询
结果 。
SQL_NO_CACHE : 服务器不使用查询缓存。它既不检查查询缓存，也不检查结果是否已缓存，也不缓存查询结果。
例子：

SELECT SQL_CACHE id, name FROM customer; 
SELECT SQL_NO_CACHE id, name FROM customer;

7.3、查询缓存失效的情况

1） SQL 语句不一致的情况，要想命中查询缓存，查询的SQL语句必须一致。

SQL1 : select count(*) from tb_item; 
SQL2 : Select count(*) from tb_item;

2）当查询语句中有一些不确定的时，则不会缓存。如： now() , current_date() , curdate() , curtime() , rand() ,
uuid() , user() , database() 。

SQL1 : select * from tb_item where updatetime < now() limit 1; 
SQL2 : select user(); 
SQL3 : select database();

3）不使用任何表查询语句。

select 'A';

4）查询 mysql， information_schema或 performance_schema 数据库中的表时，不会走查询缓存。

select * from information_schema.engines;

5）在存储的函数，触发器或事件的主体内执行的查询。
6）如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。这包括使用 MERGE 映射到
已更改表的表的查询。一个表可以被许多类型的语句，如被改变 INSERT， UPDATE， DELETE， TRUNCATE
TABLE， ALTER TABLE， DROP TABLE，或 DROP DATABASE 。

8、锁

概述： 锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。

在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如
何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的
一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

8.1、锁概述

根据粒度可将锁分为两种

表锁，触发时，会锁住整个表
行锁，触发时，会锁住整行

根据类型可以分为两种

共享锁(读锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响。
排它锁(写锁)：当前操作没有完成之前，它会阻断其他写锁和读锁。

8.2、MySQL锁

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗
列出了各存储引擎对锁的支持情况：
在这里插入图片描述
MySQL这3种锁的特性可大致归纳如下：

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级
锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；而行级锁则更适合于有大量按索引
条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。
结论：
锁模式的相互兼容性如表中所示：
在这里插入图片描述
由上表可见：
1）对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
2）对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；
简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。
此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其
他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

8.3、查看锁争用情况

1.查看操作的表

show open tables；

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

show status like 'Table_locks%';

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

show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits: 当前正在等待锁定的数量 
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度 
Innodb_row_lock_time_avg:每次等待所花平均时长
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间 
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数 当等待的次数很高，而且每次等待的时长也不小的时候，
我们就需要分析系统中为什么会有如此多的等待，然后根 据分析结果着手制定优化计划。

8.4、 InnoDB 的行锁模式

两个案例：
1）有索引过滤走行锁
2）无索引升级为表锁。如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。
如图为行锁的演示
在这里插入图片描述
如图，为表锁的演示

8.5、间隙锁

当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进
行加锁； 对于键值在条件范围内但并不存在的记录，叫做 "间隙（GAP）" ， InnoDB也会对这个 "间隙" 加锁，这
种锁机制就是所谓的 间隙锁（Next-Key锁） 。

在这里插入图片描述

8.6、总结

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是
在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM
相比就会有比较明显的优势。
但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不
能比MyISAM高，甚至可能会更差。
优化建议：