Mysql-SQL索引和锁(说明,分析,区别)

置顶阿新～

于 2024-06-07 10:39:12 发布

阅读量738

点赞数 26

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/2303_79633394/article/details/139520480

版权

1. Mysql 性能影响概述

在应用开发的过程中，由于前期数据量少，开发人员编写的SQL语句或者数据库整体解决方案都更重视在功能上的实现。

但是当应用系统正式上线后，随着生成数据量的急剧增长，很多SQL语句和数据库整体开始逐渐显露出了性能问题，对生成的影响也越来越大。

此时Mysql数据库的性能问题成为系统应用的瓶颈，因此需要进行Mysql数据库的性能优化。

问题体现和原因

问题体现：

SQL 执行时间长
Mysql 客户端连接时间长

问题原因：

查询语句写的不好，各种连接，各种子查询导致索引无法使用或者没有建立索引
建立的索引失效，建立了索引,在真正执行时,没有用上建立的索引
关联查询太多join
服务器调优及配置参数导致，如果设置的不合理,比例不恰当,也会导致性能下降,sql变慢
系统架构的问题

问题优化方案

优化的示意图

索引优化: 添加适当索引(index）(重点) 在适当的业务场景给适当的字段添加适当的索引

Sql优化: 写出高质量的sql，避免索引失效 (重点)

表设计优化: 表的设计合理化(符合3NF，有时候要进行反三范式操作-冗余字段)（重点）

架构优化：主从复制、读写分离、读负载均衡、分库分表、缓存优化(重点)

配置优化: 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]

硬件优化: 服务器的硬件优化内存 CPU 磁盘

PS: Java 开发人员主要优化的点在于 索引优化、Sql优化和表设计优化

2. 索引

2.1 索引概述

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

PS：一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高查询性能的最常用的手段。

2.2 索引优势劣势

优势

1）提高数据检索的效率，降低数据库的IO成本。

2）通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

(1）实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。

(2）虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

2.3 索引结构

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

BTREE 索引 ：最常见的索引类型，大部分索引都支持 B 树索引。
HASH 索引：只有Memory引擎支持，使用场景简单。
R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持
索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。

其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为索引。

2.3.1 BTREE 结构

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：

每个节点拥有子节点个数

树中每个节点最多包含m个孩子。
除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。
若根节点不是叶子节点，则至少有两个孩子。
每个节点拥有数据量
每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1

以3叉BTree为例，key的数量：公式推导[ceil(m/2)-1] <= n <= m-1。所以 1 <= n <=2 。当n > 2 时，中间节点分裂到父节点，两边节点分裂。

Btree 示意图

Btree 数据插入示例

需求：插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例，延时五叉树数据插入过程。

5叉BTree为例，key的数量：公式推导[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4 。当n > 4 时，中间节点分裂到父节点，两边节点分裂。

演变过程如下：

1). 插入前4个字母 C N G A

2). 插入H，n>4，中间元素G字母向上分裂到新的节点

3). 插入E，K，Q不需要分裂

4). 插入M，中间元素M字母向上分裂到父节点G

5). 插入F，W，L，T不需要分裂

6). 插入Z，中间元素T向上分裂到父节点中

7). 插入D，中间元素D向上分裂到父节点中。然后插入P，R，X，Y不需要分裂

8). 最后插入S，NPQR节点n>5，中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂

到此，该BTREE树就已经构建完成了， BTREE树和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。

2.3.3 B+TREE 结构

B+Tree为BTree的变种，B+Tree与BTree的区别为：

1). M 叉B+Tree最多含有 M 个key，而BTree最多含有 M-1 个key。

2). B+Tree的叶子节点保存所有的key完整data数据。

3). 所有的非叶子节点只保存 key 的索引部分，叶子结点才会保存 data 数据。

PS：根据上面的特点，B+tree 要比 Btree 的每个节点存储 key 值要多，使得树结构更矮。

B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。

B+tree 示意图

2.3.4 MySQL中的B+Tree

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

MySQL中的 B+Tree 索引结构示意图:

2.4 索引分类

Mysql 中对于索引的种类主要有下面三种形式

1） 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引

2） 唯一索引 ：索引列的值必须唯一，但允许有空值

3） 复合索引 ：即一个索引包含多个列

2.5 索引语法

索引在创建表的时候，可以同时创建，也可以随时增加新的索引。

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.5.1 创建索引

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)


index_col_name : column_name[(length)][ASC | DESC]

示例：为city表中的city_name字段创建索引；

2.5.2 查看索引

show index  from  table_name;

示例：查看city表中的索引信息；

2.5.3 删除索引

DROP  INDEX  index_name  ON  tbl_name;

示例：想要删除city表上的索引idx_city_name，可以操作如下：

2.5.4 ALTER命令

##该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
1). alter  table  tb_name  add  primary  key(column_list); 

##这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
2). alter  table  tb_name  add  unique index_name(column_list);
	
##添加普通索引， 索引值可以出现多次。
3). alter  table  tb_name  add  index index_name(column_list);

##该语句指定了索引为FULLTEXT， 用于全文索引
4). alter  table  tb_name  add  fulltext  index_name(column_list);

2.6 索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

1).数据量较大，且查询比较频繁的表

针对于数据量较大，且查询比较频繁的表建立索引，单表超过10万数据（增加用户体验）

2)常作为查询条件、排序、分组的字段

针对于常作为查询条件(where)、排序(order by)、分组 (group by)操作的字段建立索引

3)字段内容区分度高

尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

4)内容较长，使用前缀索引

如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引

5)尽量使用联合索引

尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率

6)要控制索引的数量

要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率

7)如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它

当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

创建复合索引:
	CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相当于
	对name 创建索引 ;
	对name , email 创建了索引 ;
	对name , email, status 创建了索引 ;

Mysql如何给字符串添加索引(前缀索引)-腾讯云开发者社区-腾讯云

3. SQL 分析

在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，即使数据库表创建索引，也会出现性能上的问题。此时应该从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并尽快解决问题。

下面将通过下面的操作步骤来对 SQL 进行分析

查询 SQL 执行频率

- 查询 Mysql 服务对于 DML 和 DQL 语句的操作次数

定位低效 SQL 语句

- 开启慢查询日志

explain 分析执行任务

- 分析具体 SQL 内容

3.1 查看SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

下面的命令显示了当前 session 中所有统计参数的值：

show status like 'Com_______';

show status like 'Innodb_rows_%';

Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

参数	含义
Com_select	执行 select 操作的次数，一次查询只累加 1。
Com_insert	执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update	执行 UPDATE 操作的次数。
Com_delete	执行 DELETE 操作的次数。
Innodb_rows_read	select 查询返回的行数。
Innodb_rows_inserted	执行 INSERT 操作插入的行数。
Innodb_rows_updated	执行 UPDATE 操作更新的行数。
Innodb_rows_deleted	执行 DELETE 操作删除的行数。
Connections	试图连接 MySQL 服务器的次数。
Uptime	服务器工作时间。
Slow_queries	慢查询的次数。

Com_ : 这些参数对于所有存储引擎的表操作都会进行累计。

Innodb_ : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

3.2 定位低效率执行SQL

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

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

慢查询日志是什么

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

long_query_time的默认值为10，意思是运行10秒以上的语句
由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前explain进行全面分析

PS：默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

慢查询日志是否开以及开启

查看慢查询日志是否开启：SHOW VARIABLES LIKE '%slow_query_log%';。
开启慢查询日志：SET GLOBAL slow_query_log = 1;。使用该方法开启MySQL的慢查询日志只对当前数据库生效，如果MySQL重启后会失效。

-- 指定数据库
mysql> use 数据库名称;
Database changed
--  查看慢查询日志是否开启
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+---------------------------------------------------------------------------+
| Variable_name       | Value                                                                     |
+---------------------+---------------------------------------------------------------------------+
| slow_query_log      | OFF                                                                       |
| slow_query_log_file | xxxxxx\dam-slow.log |
+---------------------+---------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
--  开启慢查询日志
mysql> SET GLOBAL slow_query_log = 1;
Query OK, 0 rows affected (0.01 sec)

如果要使慢查询日志永久开启（不推荐，浪费性能），需要修改my.cnf文件，在[mysqld]下增加修改参数。

# my.cnf
[mysqld]
# 开启慢查询
slow_query_log=ON  
# 指定存储慢查询日志的文件。如果这个文件不存在，会自动创建
slow_query_log_file=/var/lib/mysql/slow.log

设置慢SQL的时间阈值

查看阈值

时间阈值是由参数long_query_time控制的，默认情况下long_query_time的值为10秒。

MySQL中查看long_query_time的时间：SHOW VARIABLES LIKE 'long_query_time%';。

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

注意：是超过阈值才会被记录，等于不会被记录

设置阈值

--  设置阈值
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
--  可以发现设置没有成功
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

也可以不重启连接，使用如下命令直接查看

show global variables like 'long_query_time';

也直接在my.cnf配置文件中修改，一般不建议，此文件有运维维护。

[mysqld]
long_query_time=1

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

模拟慢查询

mysql> select sleep(4);
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.01 sec)

mysqld, Version: 5.7.25 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2023-12-13T20:02:49.901360Z
# User@Host: root[root] @  [192.168.200.1]  Id:    14
# Query_time: 10.000954  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use demo_01;
SET timestamp=1702497769;
/* ApplicationName=IntelliJ IDEA 2022.3.3 */ select sleep(10);

use advanced_mysql_learning：使用的数据库
Query_time：实际查询时间，单位是秒
Lock_time：锁时间
select sleep(4)：超时的语句

3.3 explain分析执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划：

explain  select * from tb_item where id = 317578;

explain  select * from tb_item where name = '莎米特SUMMIT 旅行拉杆箱28英寸PC材质大容量旅行行李箱PC154 黑色';

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为( system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------> all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

3.3.1 环境准备

CREATE TABLE `t_role` (
  `id` varchar(32) NOT NULL,
  `role_name` varchar(255) DEFAULT NULL,
  `role_code` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `t_user` (
  `id` varchar(32) NOT NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `user_role` (
  `id` int(11) NOT NULL auto_increment ,
  `user_id` varchar(32) DEFAULT NULL,
  `role_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ur_user_id` (`user_id`),
  KEY `fk_ur_role_id` (`role_id`),
  CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生2');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师1');



INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');


INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;

3.3.2 explain 之 id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id 情况有三种：

1） id 相同表示加载表的顺序是从上到下。

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;

2） id 不同id值越大，优先级越高，越先被执行。

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))

3） id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ;

3.3.3 explain 之 select_type（重要）

表示 SELECT 的类型，常见的取值，如下表所示：

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在SELECT 或 WHERE 列表中包含了子查询
DERIVED	在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
UNION	若第二个SELECT出现在UNION之后，则标记为UNION ；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为： DERIVED
UNION RESULT	从UNION表获取结果的SELECT

3.3.4 explain 之 table

展示这一行的数据是关于哪一张表的

3.3.5 explain 之 type（重要）

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果
system	表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const	表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常亮。const于将 "主键" 或 "唯一" 索引的所有部分与常量值进行比较
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描（聚籔索引）
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个-非聚簇索引）
range	只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
index	index 与 ALL的区别为 index 类型只是遍历了索引树，通常比ALL 快， ALL 是遍历数据文件。
all	将遍历全表以找到匹配的行

结果值从最好到最坏以此是：

NULL > system > const > eq_ref > ref  > range > index > ALL

一般来说，我们需要保证查询至少达到 range 级别，最好达到ref 。

3.3.6 explain 之 key（重要）

possible_keys : 显示可能应用在这张表的索引， 一个或多个。 

key ： 实际使用的索引， 如果为NULL， 则没有使用索引。

key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。

3.3.7 explain 之 rows

扫描行的数量。

3.3.8 explain 之 extra（重要）

其他的额外的执行计划信息，在该列展示。

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为 “文件排序”, 效率低。
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by；效率低
using index	表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错。

3.4 show profile分析SQL（扩展）

Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过 have_profiling 参数，能够看到当前MySQL是否支持profile：

默认profiling是关闭的，可以通过set语句在Session级别开启profiling：

set profiling=1; //开启profiling 开关；

通过profile，我们能够更清楚地了解SQL执行的过程。

首先，我们可以执行一系列的操作，如下图所示：

show databases;

use db01;

show tables;

select * from tb_item where id < 5;

select count(*) from tb_item;

执行完上述命令之后，再执行show profiles 指令，来查看SQL语句执行的耗时：

通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

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

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间：

字段	含义
Status	sql 语句执行的状态
Duration	sql 执行过程中每一个步骤的耗时
CPU_user	当前用户占有的cpu
CPU_system	系统占有的cpu

4. 索引的使用

索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

4.1 验证索引提升查询效率

在我们准备的表结构tb_item 中，一共存储了 8 万多记录；

A. 根据ID查询

select * from tb_item where id = 546872;

查询速度很快，接近0s ，主要的原因是因为id为主键，有索引；

B. 根据 name 进行精确查询

select * from tb_item where name = 'RIMOWA 21寸托运箱拉杆箱 SALSA AIR系列果绿色 820.70.36.4';

处理方案，针对 name 字段，创建索引：

create index idx_item_title on tb_item(name);

索引创建完成之后，再次进行查询：

select * from tb_item where name = 'RIMOWA 21寸托运箱拉杆箱 SALSA AIR系列果绿色 820.70.36.4';

4.2 索引的使用

4.2.1 准备环境

create table `tb_seller` (
	`sellerid` varchar (100),
	`name` varchar (100),
	`nickname` varchar (50),
	`password` varchar (60),
	`status` varchar (1),
	`address` varchar (100),
	`createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');


create index idx_seller_name_sta_addr on tb_seller(name,status,address);

4.2.2 避免索引失效

1). 全值匹配，对索引中所有列都指定具体值。

改情况下，索引生效，执行效率高。

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

2). 最左前缀法则

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

匹配最左前缀法则，走索引：

违法最左前缀法则，索引失效：

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

3). 范围查询右边的列，不能使用索引

根据前面的两个字段name ， status 查询是走索引的，但是最后一个条件address 没有用到索引。

4). 不要在索引列上进行运算操作，索引将失效

5). 字符串不加单引号，造成索引失效。

由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

6). 尽量使用覆盖索引，避免select *

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。

如果查询列，超出索引列，也会降低性能。

TIP : 
	
    using index ：使用覆盖索引的时候就会出现

    using where：在查找使用索引的情况下，需要回表去查询所需的数据

    using index condition：查找使用了索引，但是需要回表查询数据

    using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

聚集索引会根据结构找到完整的数据。

聚集索引示意图

非聚集索引是有无规律和无顺序。

非聚集索引会根据结构找到和聚集索引的关联数据。

非聚集索引示意图

7). 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

示例，name字段是索引列，而createtime不是索引列，中间是or进行连接是不走索引的：

explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00';

8). 以%开头的Like模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

解决方案：

通过覆盖索引来解决

9). 如果MySQL评估使用索引比全表更慢，则不使用索引。

10). is NULL ， is NOT NULL 有时索引失效。

11). in 走索引， not in 索引失效。

12). 单列索引和复合索引

尽量使用复合索引，而少使用单列索引。

创建复合索引

create index idx_name_sta_address on tb_seller(name, status, address);

就相当于创建了三个索引 ： 
	name
	name + status
	name + status + address

创建单列索引

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引。

5. SQL优化

5.1 优化insert语句

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。
示例，原始方式为：

insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');

优化后的方案为：

insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

在事务中进行数据插入。

start transaction;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;

数据有序插入

insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');

优化后

insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
insert into tb_test values(4,'Tim');
insert into tb_test values(5,'Rose');

5.2 优化order by语句

5.2.1 环境准备

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(3) NOT NULL,
  `salary` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');

create index idx_emp_age_salary on emp(age,salary);

5.2.2 排序方式

1). 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

2). 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

多字段排序

总结：

尽量减少额外的排序，通过索引直接返回有序数据
where 条件和Order by 使用相同的索引
Order By 的顺序和索引顺序相同
Order by 的字段都是升序，或者都是降序

5.3 优化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;

从上面的例子可以看出，第一个SQL语句需要进行"filesort"，而第二个SQL由于order by null 不需要进行 "filesort"，而上文提过Filesort往往非常耗费时间。

创建索引：

create index idx_emp_age_salary on emp(age,salary)；

5.4 优化嵌套查询

Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的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;

连接(Join)查询之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

5.5 优化OR条件

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

获取 emp 表中的所有的索引：

示例：

explain select * from emp where id = 1 or age = 30;

建议使用 union 替换 or ：

我们来比较下重要指标，发现主要差别是 type 和 ref 这两项

type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null  > index_merge > unique_subquery > index_subquery > range > index > ALL

UNION 语句的 type 值为 ref，OR 语句的 type 值为 range，可以看到这是一个很明显的差距

UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快

这两项的差距就说明了 UNION 要优于 OR 。

5.6 优化分页查询

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

5.6.1 优化思路一

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

5.6.2 优化思路二

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

6. Mysql锁问题

6.1 锁概述

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

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

6.1.2 锁分类

从对数据操作的粒度分

1）表锁：操作时，会锁定整个表。

2）行锁：操作时，会锁定当前操作行。

从对数据操作的类型分

1）读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

2）写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

6.1.3 Mysql 锁

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：

存储引擎	表级锁	行级锁	页面锁
MyISAM	支持	不支持	不支持
InnoDB	支持	支持	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

MySQL这3种锁的特性可大致归纳如下：

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

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

6.2 MyISAM 表锁

MyISAM 存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。

6.2.1 如何加表锁

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

显示加表锁语法：

加读锁 ： lock tables table_name read;

加写锁 ： lock tables table_name write；

6.2.2 读锁案例

准备环境

create database demo_03 default charset=utf8mb4;

use demo_03;

CREATE TABLE `tb_book` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  `publish_time` DATE DEFAULT NULL,
  `status` CHAR(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java编程思想','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr编程思想','2088-08-08','0');



CREATE TABLE `tb_user` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_user (id, name) VALUES(NULL,'令狐冲');
INSERT INTO tb_user (id, name) VALUES(NULL,'田伯光');

客户端一：

1）获得tb_book 表的读锁

lock tables tb_book read;

2）执行查询操作

select * from tb_book;

可以正常执行，查询出数据。

客户端二：

3）执行查询操作

select * from tb_book;

客户端一：

4）查询未锁定的表

select name from tb_user;

客户端二：

5）查询未锁定的表

select name from tb_user;

可以正常查询出未锁定的表；

客户端一：

6）执行插入操作

insert into tb_book values(null,'Mysql高级','2088-01-01','1');

执行插入，直接报错，由于当前tb_book 获得的是读锁，不能执行更新操作。

客户端二：

7）执行插入操作

insert into tb_book values(null,'Mysql高级','2088-01-01','1');

当在客户端一中释放锁指令 unlock tables 后，客户端二中的 inesrt 语句，立即执行；

6.2.3 写锁案例

客户端一 :

1）获得tb_book 表的写锁

lock tables tb_book write ;

2）执行查询操作

select * from tb_book ;

查询操作执行成功；

3）执行更新操作

update tb_book set name = 'java编程思想（第二版）' where id = 1;

更新操作执行成功；

客户端二 :

4）执行查询操作

select * from tb_book ;

当在客户端一中释放锁指令 unlock tables 后，客户端二中的 select 语句，立即执行；

6.2.4 结论

锁模式的相互兼容性如表中所示：

由上表可见：

1）对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

2）对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。

此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

6.2.5 查看锁的争用情况

show open tables；

In_user : 表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。

Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。

show status like 'Table_locks%';

Table_locks_immediate ：指的是能够立即获得表级锁的次数，每立即获取锁，值加1。

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

6.3 InnoDB 行锁

6.3.1 行锁介绍

行锁特点：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是采用了行级锁。

6.3.2 背景知识

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元。

事务具有以下4个特性，简称为事务ACID属性。

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';

6.3.3 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

6.3.4 案例准备工作

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);

6.3.5 行锁基本演示

Session-1

Session-2

关闭自动提交功能

可以正常的查询出全部的数据

查询id 为3的数据；

获取id为3的数据；

更新id为3的数据，但是不提交；

更新id为3 的数据，出于等待状态

通过commit，提交事务

解除阻塞，更新正常进行

以上，操作的都是同一行的数据，接下来，演示不同行的数据：

更新id为3数据，正常的获取到行锁，执行更新；

由于与Session-1 操作不是同一行，获取当前行锁，执行更新；

6.3.6 无索引行锁升级为表锁

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

查看当前表的索引： show index from test_innodb_lock ;

Session-1

Session-2

关闭事务的自动提交

执行更新语句：

执行更新语句，但处于阻塞状态：

提交事务：

解除阻塞，执行更新成功：

执行提交操作：

由于执行更新时， name字段本来为varchar类型，我们是作为数组类型使用，存在类型转换，索引失效，最终行锁变为表锁；

6.3.7 间隙锁危害

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

示例：

Session-1	Session-2
关闭事务自动提交	关闭事务自动提交
根据id范围更新数据
	插入id为2的记录，出于阻塞状态
提交事务；
	解除阻塞，执行插入操作：
	提交事务：

6.3.8 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: 系统启动后到现在总共等待的次数


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

6.3.9 总结

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。

但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

优化建议：

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

阿新～

关注

26
点赞
踩
30

收藏

觉得还不错? 一键收藏
0
评论
Mysql-SQL索引和锁(说明,分析,区别)

在应用开发的过程中，由于前期数据量少，开发人员编写的SQL语句或者数据库整体解决方案都更重视在功能上的实现。但是当应用系统正式上线后，随着生成数据量的急剧增长，很多SQL语句和数据库整体开始逐渐显露出了性能问题，对生成的影响也越来越大。此时Mysql数据库的性能问题成为系统应用的瓶颈，因此需要进行Mysql数据库的性能优化。问题体现和原因SQL 执行时间长Mysql 客户端连接时间长查询语句写的不好，各种连接，各种子查询导致索引无法使用或者没有建立索引。
复制链接

扫一扫