MySQL笔记【高级篇】

转x33g5p2x  于2021-12-14 转载在 Mysql  
字(40.7k)|赞(0)|评价(0)|浏览(338)

一、MySQL架构介绍

1、环境准备

# 1、拉取镜像
docker pull mysql:5.7

# 2、创建实例并启动
docker run -p 3306:3306 --name mysql \
-v /root/mysql/log:/var/log/mysql \
-v /root/mysql/data:/var/lib/mysql \
-v /root/mysql/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7
执行命令：apt-get update
执行命令：apt-get install vim -y

#说明：执行apt-get install vim命令若出现E: Unable to locate package vim，不要忘了执行apt-get update
root@8d87e9c530cf:/etc/mysql# pwd 
/etc/mysql
root@8d87e9c530cf:/etc/mysql# ls
conf.d	my.cnf	my.cnf.fallback  mysql.cnf  mysql.conf.d

#进入容器找到 /etc/mysql/mysql.conf.d 目录，然后对其中mysqld.cnf进行配置
root@8d87e9c530cf:/# cd /etc/mysql/mysql.conf.d/
root@8d87e9c530cf:/etc/mysql/mysql.conf.d# ls
mysqld.cnf

#添加如下 ：

[mysqld]
character_set_server=utf8

注意：安装MySQL完毕之后，第一件事就是修改字符集编码。

#退出容器，重新容器，使用 C.UTF-8 编码
docker exec -it 【容器ID/名称】 env LANG=C.UTF-8 bash

至此，进入容器后，登录MySQL，即可输入中文。

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
mysql> create database db02 ;
Query OK, 1 row affected (0.00 sec)

mysql> use db02 ;
Database changed

mysql> create table user(id int not null , name varchar(255));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user values(1,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user ;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)

环境搭建完成！

2、安装位置

2.1、Windows环境目录结构
D:\MySQL\mysql-5.7.33

data目录就是存放我们的具体的数据库！

2.2、Linux环境目录结构
#进入容器，查看mysql配置文件位置！
root@8d87e9c530cf:/# whereis mysql
mysql: /usr/bin/mysql /usr/lib/mysql /etc/mysql /usr/share/mysql

#/etc :配置文件,mysql的配置文件会放在这里面
#/var/lib/mysql: mysql数据存放位置

可以看到我们创建的数据库！

Linux环境下MySQL的安装目录

路径解释
/var/lib/mysqlMySQL数据库文件存放位置
/usr/share/mysql错误消息和字符集文件配置
/usr/bin客户端程序和脚本 mysqladmin mysqldump
/etc/init.d/mysql启停脚本相关

3、MySQL配置文件

1、二进制日志log-bin：主从复制。

# my.cnf
# 开启mysql binlog功能
log-bin=mysql-bin

2、错误日志log-error：默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等。

# my,cnf
# 数据库错误日志文件
log-error = error.log

3、查询日志log：默认关闭，记录查询的sql语句，如果开启会降低MySQL整体的性能，因为记录日志需要消耗系统资源。

# my,cnf
# 慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = slow.log

4、数据文件

  • frm文件：存放表结构
  • myd文件：存放表数据
  • myi文件：存放表索引
# mysql5.7 使用.frm文件来存储表结构
# 使用 .ibd文件来存储表索引和表数据
-rw-r-----  1 mysql mysql   8988 Jun 25 09:31 pms_category.frm
-rw-r-----  1 mysql mysql 245760 Jul 21 10:01 pms_category.ibd

4、MySQL逻辑架构

  • Connectors：指的是不同语言中与SQL的交互。
  • Connection Pool：管理缓冲用户连接，线程处理等需要缓存的需求。MySQL数据库的连接层。
  • Management Serveices & Utilities：系统管理和控制工具。备份、安全、复制、集群等等。。
  • SQL Interface：接受用户的SQL命令，并且返回用户需要查询的结果。
  • Parser：SQL语句解析器。
  • Optimizer：查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求query，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果。For Example： select uid,name from user where gender = 1;这个select查询先根据where语句进行选取，而不是先将表全部查询出来以后再进行gender过滤；然后根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。最后将这两个查询条件联接起来生成最终查询结果。
  • Caches & Buffers：查询缓存。
  • Pluggable Storage Engines：存储引擎接口。MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎(注意：存储引擎是基于表的，而不是数据库)。
  • File System：数据落地到磁盘上，就是文件的存储。

和其他数据库相比，MySQL有点与众不同，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需求选择合适的存储引擎。

逻辑架构分层

  • 连接层：最上层是一些客户端和连接服务，包含本地socket通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
  • 服务层：MySQL的核心服务功能层，该层是MySQL的核心，包括查询缓存，解析器，解析树，预处理器，查询优化器。主要进行查询解析、分析、查询缓存、内置函数、存储过程、触发器、视图等，select操作会先检查是否命中查询缓存，命中则直接返回缓存数据，否则解析查询并创建对应的解析树。
  • 引擎层：存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
  • 存储层：数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

5、MySQL存储引擎

mysql> show engines;  #通过命令查询当前MySQL所支持的存储引擎

show variables like 'default_storage_engine%'; #查看当前数据库正在使用的存储引擎,以及默认存储引擎

InnoDB和MyISAM对比

对比项MyISAMInnoDB
外键不支持支持
事务不支持支持
行表锁表锁，即使操作一条记录也会锁住整张表，不适合高并发操作行锁，操作时只锁某一行，不对其他行有影响，适合高并发操作
缓存只缓存索引，不缓存真实数据不仅缓存索引还要缓存真实数据，対内存要求较高，而且内存大小対性能有决定性影响
表空间小大
关注点性能事务
默认安装YY

6、SQL性能下降的原因

  • 查询语句写的差
  • 索引失效：索引建了，但是没有用上
  • 关联 查询太多join（设计缺陷或者不得已的需求）
  • 服务器调优以及各个参数的设置（缓冲、线程数等）

7、SQL执行顺序

select              # 7
from                # 1
join				# 3
on					# 2
where               # 4 
group by            # 5
having              # 6
distinct			# 8
order by            # 9
limit               # 10

8、七种JOIN理论

/*AB共有*/
select * from tbl_dept a join tbl_emp b on a.id = b.deptid ;
/*A全部和AB共有*/
select * from tbl_dept a left join tbl_emp b on a.id = b.deptid ;
/*B全部和AB共有*/
select * from tbl_dept a right join tbl_emp b on a.id = b.deptid ;
/*A独占*/
select * from tbl_dept a left join tbl_emp b on a.id = b.deptid where b.id is null;
/*B独占*/
select * from tbl_dept a right join tbl_emp b on a.id = b.deptid where a.id is null;
/*A独占+B独占+AB共有*/
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 ;
/*A独占+B独占*/ 
mysql> select * from tbl_dept a left join tbl_emp b on a.id = b.deptid where b.deptid is null
    -> union
    -> select * from tbl_dept a right join tbl_emp b on a.id = b.deptid where a.id is null ;

注意：其中union会进行去重！

二、索引优化分析

1、索引

1、什么是索引

从而可以获得索引的本质：索引是排好序的快速查找数据结构

索引的目的在于提高查询效率，可以类比字典的目录。如果要查mysql这个这个单词，我们肯定要先定位到m字母，然后从上往下找y字母，再找剩下的sql。如果没有索引，那么可能需要a---z，这样全字典扫描，如果我想找Java开头的单词呢？如果我想找Oracle开头的单词呢？？？

重点：索引会影响到MySQL==查找(WHERE的查询条件)和排序(ORDER BY)==两大功能！

除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上

# Linux下查看磁盘空间命令 df -h 
[root@Ringo ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        40G   16G   23G  41% /
devtmpfs        911M     0  911M   0% /dev
tmpfs           920M     0  920M   0% /dev/shm
tmpfs           920M  480K  920M   1% /run
tmpfs           920M     0  920M   0% /sys/fs/cgroup
overlay          40G   16G   23G  41%

我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种数据结构的索引之外，还有哈希索引（Hash Index）等。

2、索引的优势和劣势

优势

  • 查找：类似大学图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本
  • 排序：通过索引対数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
  • 虽然索引大大提高了查询速度，但是同时会降低表的更新速度，例如对表频繁的进行INSERT、UPDATE和DELETE。因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
  • 索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引。
3、MySQL索引分类和创建

-InnoDB

  • 主键索引：设为主键的字段会自动创建索引（无序手动创建），innodb为聚簇索引
  • 单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
  • 唯一索引：索引列的值必须唯一，但是允许空值。（但是只能有一个null）
  • 复合索引：一个索引包含多个字段。

-MyISAM

  • Full text 全文索引（mysql5.7之前版本，只能用于myISAM存储引擎）

建议：一张表建的索引最好不要超过5个！

1、建表之后创建索引

/* 1、创建索引 [UNIQUE]可以省略*/
/* 如果只写一个字段就是单值索引，写多个字段就是复合索引 */
CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length));
ALTER TABLE tableName ADD [UNIQUE] INDEX	 indexName ON (columnName(length));

/* 2、删除索引 */
DROP INDEX [indexName] ON tabName;

/* 3、查看索引 */
/* 加上\G就可以以列的形式查看了 不加\G就是以表的形式查看 */
SHOW INDEX FROM tabName \G;

2、使用ALTER命令来为数据表添加索引

/* 1、该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL */
ALTER TABLE tabName ADD PRIMARY KEY(column_list);

/* 2、该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次) */
ALTER TABLE tabName ADD UNIQUE indexName(column_list);

/* 3、该语句创建普通索引，索引值可以出现多次 */
ALTER TABLE tabName ADD INDEX indexName(column_list);

/* 4、该语句指定了索引为FULLTEXT，用于全文检索 */
ALTER TABLE tabName ADD FULLTEXT indexName(column_list);

3、建表的时候添加索引

CREATE TABLE tb01(id INT PRIMARY KEY , NAME VARCHAR(20),UNIQUE(NAME));  #添加唯一索引，主键索引

CREATE TABLE tb01(id INT PRIMARY KEY , NAME VARCHAR(20),KEY(NAME));  	#添加单值索引，主键索引

CREATE TABLE tb02(id INT PRIMARY KEY , NAME VARCHAR(20),age INT ,KEY(NAME,age));  #添加复合索引
4、MySQL索引底层原理 *
1.思考
---建表
create table t_emp(id int primary key,name varchar(20),age int);  

--插入数据
insert into t_emp values(5,'d',22);
insert into t_emp values(6,'d',22);
insert into t_emp values(7,'e',21);
insert into t_emp values(1,'a',23);
insert into t_emp values(2,'b',26);
insert into t_emp values(3,'c',27);
insert into t_emp values(4,'a',32);
insert into t_emp values(8,'f',53);
insert into t_emp values(9,'v',13);

--查询
select * from t_emp;

  • 原因是：mysql底层为主键自动创建索引,一定创建索引会进行排序 ，也就是mysql底层真正存储是这样的

为什么要排序呢 ?

  • 因为排序之后在查询就相对比较快了 如查询 id=3的我只需要按照顺序找到3就行啦(如果没有排序大海捞针,全靠运气😸!)

  • 就是基于页的形式进行管理索引
  • 如 ：查询id=4的 直接先比较页 先去页目录中找,再去 数据目录中找

于是就采用如下这种B+Tree的数据结构 ：

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

​ 从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果

data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查

询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子

节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

  • 非叶子节点只存储键值信息。
  • 所有叶子节点之间都有一个链指针。
  • 数据记录都存放在叶子节点中。

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗3）。也就是说一个深度为3的B+Tree索引可以维护103 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在24层。MySQL的InnoDB存储引擎在设计时是将==根节点常驻内存==的，也就是说查找某一键值的行记录时最多只需要13次磁盘I/O操作。

  • 1次IO的情况：我们的B+Tree只有2层，由于顶层常驻内存，所以只有查找数据的时候进行一次磁盘IO ；

  • 2次IO情况：B+Tree有三层，同样是顶层查询走内存，所以查到数据需要进行2次磁盘IO ；

  • 3次IO情况：B+Tree有四层，或者B+Tree有三层采用Innodb存储引擎，索引是辅助索引！

  • 聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

  • 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

注意：在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、单值索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找我们把==回到主键索引树搜索的过程，我们称为回表==

  • InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

  • 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

  • 聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会**隐式定义一个主键（类似oracle中的RowId）**来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

  • MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

1、使用聚簇索引的优势

问题: 每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？

  • 1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来查询数据，获得数据更快。
  • 2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

2、聚簇索引需要注意什么?

  • 当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
  • 建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

3、 为什么主键通常建议使用自增id

  • 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

4、什么情况下无法利用索引呢?

  • 查询语句中使用LIKE关键字
    在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。
  • 查询语句中使用多列索引
    多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。
  • 查询语句中使用OR关键字
    查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。

innodb聚簇索引和myisam非聚簇索引

InnoDB 的辅助索引叶子节点为什么不直接保存的记录地址而要存主键键值

B-Tree、B+Tree详解

5、哪些情况需要建索引
  • 主键自动建立主键索引（唯一 + 非空）
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

建的复合索引尽量与Order by 一致

  • 查询中统计或者分组字段（group by也和索引有关）
6、那些情况不要建索引
  • 记录太少的表。
  • 经常增删改的表。
  • 频繁更新的字段不适合创建索引。
  • Where条件里用不到的字段不创建索引。
  • 假如一个表有10万行记录，有一个字段A只有true和false两种值，并且每个值的分布概率大约为50%，那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

性能分析

2、索引性能分析

1、MySQL Query Optimizer

主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（它认为最优的数据检索方式，但不见得是DBA认为最优的，这部分最耗费时间）

当客户端像MySQL请求一条Query，命令解析器模块完成请求分类，去别处是SELECT并转发给MySQL Query Optimizer ，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件，结构调整等。然后分析Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读区所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

2、MySQL常见瓶颈

CPU：CPU再饱和的时候一般发生再数据装入内存或从磁盘上读区数据的时候

IO：磁盘IO瓶颈发生再装入数据远大于内存容量的时候

服务器硬件的性能瓶颈：top free iostat和vmstat来查看系统的性能状态

3、explain

EXPLAIN怎么使用

  • 语法：explain + SQL语句

默认以表的形式展示

mysql> explain select * from tbl_emp\G ;   #/G是表示以列的形式展示
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_emp
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

EXPLAIN能干嘛

    • id：表的读取顺序
  • select_type：数据读取操作的操作类型
    • possible_keys：哪些索引可以使用
  • key：哪些索引被实际使用
    • ref：表之间的引用
  • rows：每张表有多少行被优化器查询
4、explain字段详解

值有以下三种情况

  • id相同，执行顺序由上至下
  • id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  • id相同不同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行

该字段存在以下的6个值 ：

  • SIMPLE：简单的SELECT查询，查询中不包含子查询或者UNION。
select * from table ;  #就是最简单的查询
  • PRIMARY：查询中如果包含任何复杂的子部分，最外层查询则被标记为：PRIMARY。
select * from t1 where (select * from t2) ; #对t1表进行的就是primary查询
  • SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
select * from t1 where (select * from t2) ;   #对t2的表的查询就是子查询
  • DERIVED：在FROM子句中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中。
select * from t1 join (select * from s1) t2 on t2.id = t1.id  #会将t2这个临时表看为一个虚表也就是drived
  • UNION：如果第二个SELECT出现在UNION之后则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。
select * from t1 union select * from t2 ;  #对t2的查询类型就是union
  • UNION RESULT：从UNION表获取结果的SELECT。

从最好到最次依次是System > const > eq_ref > ref > range > index > all除了ALL没有用到索引，其他级别都用到索引了。

一般来说，得保证查询至少达到range级别，最好达到ref

  • system ：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个可以忽略不计。【表中只有一条记录】
  • const : 表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where中，MySQL就能将该查询转化为一个常量 【条件写死，只查一条数据】
  • eq_ref ：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。除了 system 和const 类型之外, 这是最好的联接类型。 【只有一条满足条件的数据】
  • ref ：非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录。 【有多条满足条件记录】
  • range ： 只检索给定范围的行，一般就是在WHERE语句中出现了BETWEEN、< >、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。 【在某个范围之间的查询】
  • index ：Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。【全扫描，扫描的是索引树】

也就是说虽然ALL和index都是读全表，但是index是从索引中读的，ALL是从磁盘中读取的。 【全扫描，扫描的是磁盘】

  • ALL：Full Table Scan，没有用到索引，全表扫描。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

如果为NULL，则没建或没有使用索引，即索引失效。查询中如果使用了覆盖索引，则该索引仅仅出现在key**列表中。**与Extra有关

覆盖索引： 表中为字段添加的索引和要查询的字段一致

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的 用于表示本

次查询中，所选择的索引长度有多少字节，通常我们可借此判断联合索引有多少列被选择了。**key_len越短越好，**说白了就是字段类型越

短越好

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值**【当前表中的索引字段，引用了谁！】**

这个rows就是mysql认为必须要逐行去检查和判断的记录的条数。 rows越少越好

举个例子来说，假如有一个语句 select * from t where column_a = 1 and column_b = 2;

全表假设有100条记录，column_a字段有索引（非联合索引），column_b没有索引。

column_a = 1 的记录有20条， column_a = 1 and column_b = 2 的记录有5条。

那么最终查询结果应该显示5条记录。 explain结果中的rows应该是20. 因为这20条记录mysql引擎必须逐行检查是否满足where条件。

Using filesort ：MySQL中无法利用索引完成的排序操作称为“文件排序”

Using temporary

要解决查询，MySQL需要创建一个临时表来保存结果。 如果查询包含不同列的GROUP BY和ORDER BY子句，则通常会发生这种情况。官方解释：”为了解决查询，MySQL需要建立临时表(temporary table)来暂存中间结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。很明显就是通过where条件一次性检索出来的结果集太大了，内存放不下了，只能通过家里临时表来辅助处理；

典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

原理：select col 1 from t group by col1 order by col2 ,在此sql语句当中我们的order by 执行在select 之后，会按照col2分组，但是我们的select只能查出col1，不存在col2字段所以无法分组会报错！

Using Index ：查询时不需要回表查询，直接通过索引就可以获取查询的数据

  • Using where：表明使用了WHERE过滤。

  • Using join buffer：使用了连接缓存。

  • impossible where：WHERE子句的值总是false，不能用来获取任何元组。

  • explain中type字段中ref和eq_ref的区别：

  • explain字段详解：【强烈推荐】

  • explain中的ref字段

  • explain中的key_len字段

  • explain中的rows字段

  • explain中Extra字段详解

3、索引优化分析

1、索引单表优化案例
DROP TABLE IF EXISTS `article`;

CREATE TABLE IF NOT EXISTS `article` (
    `id`          INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    `author_id`   INT(10) UNSIGNED NOT NULL COMMENT '作者id',
    `category_id` INT(10) UNSIGNED NOT NULL COMMENT '分类id',
    `views`       INT(10) UNSIGNED NOT NULL COMMENT '被查看的次数',
    `comments`    INT(10) UNSIGNED NOT NULL COMMENT '回帖的备注',
    `title`       VARCHAR(255)     NOT NULL COMMENT '标题',
    `content`     VARCHAR(255)     NOT NULL COMMENT '正文内容'
) COMMENT '文章';

INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`)
VALUES (1, 1, 1, 1, '1', '1'),
       (2, 2, 2, 2, '2', '2'),
       (3, 3, 3, 3, '3', '3'),
       (1, 1, 3, 3, '3', '3'),
       (1, 1, 4, 4, '4', '4');

案例：查询category_id为1且comments大于1的情况下，views最多的article_id。

# 1、sql语句
SELECT id,author_id FROM article 
WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

# 2、查看sql执行计划
mysql> EXPLAIN SELECT id,author_id FROM article 
WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1

在这个执行计划当中我们发现type = ALL 全表扫描并且再extra中存在 Using filesort产文件内排序，所以需要优化

1、创建索引: idx_article_ccv

mysql> create index index_article_ccv on article(category_id,comments,views);

2、查看当前表的索引

3、查看现在SQL语句的执行计划

==发现已经由全表扫描------>变为范围查询了！==我们发现，创建符合索引idx_article_ccv之后，虽然解决了全表扫描的问题，但是在

order by排序的时候没有用到索引，MySQL居 然还是用的Using filesort，为什么？

4、我们试试修改SQL，看看SQL的执行计划

SELECT id,author_id FROM article WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1;

我们知识将 > 修改为 = 了，并没有做其他的修改为什么就能解决呢？得出结论：范围之后的索引会失效

5、原来的索引idx_article_ccv最后一个字段views会失效，那么我们如果删除这个索引，创建idx_article_cv索引呢

drop index index_article_ccv on article ;  #删除索引

create index index_article_cv on article(category_id,views) ; #创建新的索引

show idnex from article ;  #查看表的索引结构！

6、再次查看原SQL的执行计划

我们发现全表扫描和文件内排序问题都被优化了

2、索引两表优化案例
DROP TABLE IF EXISTS `class`;
DROP TABLE IF EXISTS `book`;

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类' 
) COMMENT '商品类别';

CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类'
) COMMENT '书籍';

1、不创建索引的执行

2、左表创建索引

create index index_book_card on book(card) ;

我们发现type由全表扫描变为范围查询，rows变为1；

3、删除左表索引，右表创建索引

create index index_class_card on class(card) ;

我们发现，优化不是那么明显，仅仅是type变为index，只是走了索引

原理：左连接，可以保证左表当中的全部的列，我们需要筛选的是右表，所以索引加在右表最合适，右连接同理！

结论 ： 左连接，索引加在右表字段；右连接，索引加载左表字段

3、索引三表优化案例
CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

#再结合上述的class表和book表

检查三张表，保证没有新建索引！

1、先执行SQL，不加索引，查看执行计划

select * from book left join class on book.card = class.card left join phone on book.card = phone.card ;

至此，我们发现很爽啊，全部都是全表扫描，不走索引，哈哈哈！

2、又于是两个左连接，所以为两个左连接的右表添加索引

/* 在book表创建索引 */
CREATE INDEX idx_class_card ON class(card);

/* 在phone表上创建索引 */
CREATE INDEX idx_phone_card ON phone(card);

3、再次查看SQL的执行计划

后两个都是ref且总row优化很好，效果不错。因此索引最好设置再需要经常查询的字段中。

JOIN语句的优化：

  • 尽可能减少JOIN语句中的NestedLoop（嵌套循环）的总次数：永远都是小的结果集驱动大的结果集。个人理解：【一亿条数据的book表，1万条数据的class，表我们查询肯定是用class表[也就是所谓小表]，作为连接查询中需要全部查询的一方！】
  • 优先优化NestedLoop的内层循环。
  • 保证JOIN语句中被驱动表上JOIN条件字段已经被索引。
  • 当无法保证被驱动表的JOIN条件字段被索引且内存资源充足的前提下，不要太吝惜Join Buffer 的设置。

4、索引失效

数据准备

CREATE TABLE `staffs`(
`id` INT(10) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` INT(10) NOT NULL DEFAULT 0 COMMENT '年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
)COMMENT '员工记录表';

INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('Ringo', 18, 'manager');
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('张三', 20, 'dev');
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('李四', 21, 'dev');

/* 创建索引,方式一 */
CREATE INDEX idx_staffs_name_age_pos ON `staffs`(`name`,`age`,`pos`);

/*创建索引，方式二*/
alter table staffs add index idx_staffs_nameAgePos(name,age,pos);
1、索引失效的情况
  1. 全值匹配我最爱。
  2. 最佳左前缀法则。
  3. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。
  4. 索引中范围条件右边的字段会全部失效。
  5. 尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少SELECT *。
  6. MySQL在使用!=或者<>的时候无法使用索引会导致全表扫描。
  7. is null、is not null也无法使用索引。
  8. like以通配符开头%abc索引失效会变成全表扫描（使用覆盖索引就不会全表扫描了）。
  9. 字符串不加单引号索引失效。
  10. 少用or，用它来连接时会索引失效。
2、最佳左前缀法则
/* 用到了idx_staffs_name_age_pos索引中的name字段 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo';

/* 用到了idx_staffs_name_age_pos索引中的name, age字段 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18;

/* 用到了idx_staffs_name_age_pos索引中的name，age，pos字段 这是属于全值匹配的情况！！！*/
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';

/* 索引没用上，ALL全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `age` = 18 AND `pos` = 'manager';

/* 索引没用上，ALL全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `pos` = 'manager';

/* 用到了idx_staffs_name_age_pos索引中的name字段，pos字段索引失效 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `pos` = 'manager';

概念

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

口诀：带头大哥不能死，中间兄弟不能断。

2、索引列上不计算

1、我们分别通过name = ’july‘ 和 MySQL的内置函数left(name,4) = ‘july’，执行查询

结果：得出查询的结果一致 ， 补充（left函数的意思是，我们的name字段从左开始的前4个字符如果是july）

2、分别查看两者的执行计划

我们发现，第一种走的是索引你，而我们的第二种走的是全表扫描

结论 ： 索引列不可使用函数，不参与计算，不然索引失效

口诀：索引列上少计算

3、范围之后全失效

1、查看如下两条SQL的执行计划

/*用到了idx_staffs_name_age_pos索引中的name，age，pos字段，这是全值匹配的情况 */
explain select * from staffs where name = 'july' and age = 25 and pos = 'manager' ;  
/* 用到了idx_staffs_name_age_pos索引中的name，age字段，pos字段索引失效 */
explain select * from staffs where name = 'july' and age > 25 and pos = 'manager' ;

由此可知，查询范围的字段使用到了索引，但是范围之后的索引字段会失效。

口诀：范围之后全失效。

4、覆盖索引尽量用
/* 没有用到覆盖索引 */
explain select * from staffs where name = 'july' and age = 25 and pos = 'manager' ;
/* 用到覆盖索引 */
explain select name,age,pos from staffs where name = 'july' and age = 25 and pos = 'manager' ;

口诀：查询一定不用*

5、不等有时会失效、
explain select * from staffs where name != 'july';   #使用了！= 或者<> ,不会再走索引，导致索引失效

explain select * from staffs where name = 'july';

需要注意： 实际开发的过程中，改用还得用！

is null 或 is not null 同样的也会导致索引失效

explain select * from staffs where name is not null ;

explain select * from staffs where name is null ;  #他们之间的区别是is null在possible_keys字段都为null

6、like百分加右边
/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%july%';
type：ALL

/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%july';
type：ALL

/* 使用索引范围查询 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE 'july%';
type：range

口诀：like百分加右边。

但是如果我们的业务要求必须要加在两边呢？不加查不出来？

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `name` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `age` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `pos` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `age` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`,`name`, `age`, `pos` FROM `staffs` WHERE `name` LIKE '%in';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `pos` LIKE '%na';
type：index

/* 索引失效 全表扫描 */
EXPLAIN SELECT `name`, `age`, `pos`, `add_time` FROM `staffs` WHERE `name` LIKE '%in';
type：ALL

结论：如果必须用两端%，那么查询的字段必须是全部是索引列，不然仍然全表扫描 ；

口诀：覆盖索引保两边

7、字符要加单引号

1、执行如下SQL

select * from staffs where name = '2000' ;

select * from staffs where name = 2000 ;

我们看到，我们varchar类型的字段为什么不加 ’ ’ 也可以将数据查询出来，原因是我们的MySQL会对其做一个自动类型转换！

2、查看SQL执行计划

结论 ： 虽然会得出数据，但是会导致索引失效！ 所以我们一定要加上单引号！

8、索引相关题目
Where语句索引是否被使用
where a = 3Y，使用到a
where a = 3 and b = 5Y，使用到a，b
where a = 3 and b = 5Y，使用到a，b，c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4N，没有用到a字段
where a = 3 and c = 5使用到a，但是没有用到c，因为b断了
where a = 3 and b > 4 and c = 5使用到a，b，但是没有用到c，因为c在范围之后
where a = 3 and b like ‘kk%’ and c = 4Y，a，b，c都用到
where a = 3 and b like ‘%kk’ and c = 4只用到a
where a = 3 and b like ‘%kk%’ and c = 4只用到a
where a = 3 and b like ‘k%kk%’ and c = 4Y，a，b，c都用到
9、索引面试题分析
create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);

insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

/* 创建复合索引 */
CREATE INDEX idx_test03_c1234 ON test03(c1,c2,c3,c4);
/* 最好索引怎么创建的，就怎么用，按照顺序使用，避免让MySQL再自己去翻译一次 */

/* 1.全值匹配 用到索引c1 c2 c3 c4全字段 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` = 'a3' AND `c4` = 'a4';

/* 2.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' AND `c3` = 'a3';

/* 3.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c4` = 'a4' AND `c3` = 'a3' AND `c2` = 'a2' AND `c1` = 'a1';

/* 4.用到索引c1 c2 c3字段，c4字段失效，范围之后全失效 type：range*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` > 'a3' AND `c4` = 'a4';

/* 5.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序 type：range*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` > 'a4' AND `c3` = 'a3';

/* 6.用到了索引c1 c2 c3三个字段, c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中，c4字段失效*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' ORDER BY `c3`;

/* 7.用到了索引c1 c2 c3三个字段，c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c3`;

/* 8.用到了索引c1 c2两个字段，c4失效，c1和c2两个字段用于查找， c4字段排序产生了 Using filesort 说明排序没有用到c4字段 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c4`;

/* 9.用到了索引c1 c2 c3三个字段，c1用于查找，c2和c3用于排序 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c2`, `c3`;

/* 10.用到了c1一个字段，c1用于查找，c3和c2两个字段索引失效，产生了Using filesort */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c3`, `c2`;

/* 11.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND  `c2` = 'a2' ORDER BY c2, c3;

/* 12.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND  `c2` = 'a2' AND `c5` = 'a5' ORDER BY c2, c3;

/* 13.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 没有产生Using filesort 因为之前c2这个字段已经确定了是'a2'了，这是一个常量， 再去ORDER BY c3,c2 这时候c2已经不用排序了！ 所以没有产生Using filesort 和(10)进行对比学习！*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c3, c2;

/* GROUP BY 表面上是叫做分组，但是分组之前必定排序。 */

/* 14.用到c1 c2 c3三个字段，c1用于查找，c2 c3用于排序，c4失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c2`,`c3`;

/* 15.用到c1这一个字段，c4失效，c2和c3排序失效产生了Using filesort Using temporary 5.7之后这种不是分组函数和GROUP BY出现的字段不能select*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c3`,`c2`;

简单说就是 order之前顺序准确就用到了索引查找，order后面顺序正确只用到索引排序 否则就是文件排序

10、优化总结口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
字符引号不可丢，SQL高级也不难！

三、查询截取分析

1、分析慢SQL的步骤

分析

  • 观察，至少跑1天，看看生产的慢SQL情况。
  • 开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
  • explain + 慢SQL分析。
  • show Profile。
  • 运维经理 OR DBA，进行MySQL数据库服务器的参数调优

总结（大纲）

  1. 慢查询日志的开启并捕获。
  2. explain + 慢SQL分析。
  3. show Profile查询SQL在MySQL数据库中的执行细节和生命周期情况。
  4. MySQL数据库服务器的参数调优。

2、查询优化

1、小表驱动大表

优化原则：对于MySQL数据库而言，永远都是小表驱动大表。

/** * 举个例子：可以使用嵌套的for循环来理解小表驱动大表。 * 以下两个循环结果都是一样的，但是对于MySQL来说不一样， * 第一种可以理解为，和MySQL建立5次连接每次查询1000次。 * 第一种可以理解为，和MySQL建立1000次连接每次查询5次。 */
for(int i = 1; i <= 5; i ++){
    for(int j = 1; j <= 1000; j++){
        
    }
}
// ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
for(int i = 1; i <= 1000; i ++){
    for(int j = 1; j <= 5; j++){
        
    }
}

IN 和 EXISTS

#优化原则：小表驱动大表，即小的数据集驱动大的数据集

#IN适合B表比A表数据小的情况
SELECT * FROM A WHERE id IN (SELECT id FROM B)
#等价于
for SELECT id FROM B
for SELECT * FROM A WHERE A.id = B.id

#EXISTS适合B表比A表数据大的情况
SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);
#等价于
for SELECT * FROM A 
for SELECT * FROM B WHERE B.id = A.id

其实就是SQL的机读顺序问题，in的时候先执行括号里的查询，所以括号里的表要小，exists的时候先执行外查询，所以括号外的表要小

结论：
EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT … FROM…)， 我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。

一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。

SQL中exists用法

2、ORDER BY优化
CREATE TABLE `talA`(
 id integer primary key auto_increment,
`age` INT,
`birth` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO `talA`(`age`) VALUES(18);
INSERT INTO `talA`(`age`) VALUES(19);
INSERT INTO `talA`(`age`) VALUES(20);
INSERT INTO `talA`(`age`) VALUES(21);
INSERT INTO `talA`(`age`) VALUES(22);
INSERT INTO `talA`(`age`) VALUES(23);
INSERT INTO `talA`(`age`) VALUES(24);
INSERT INTO `talA`(`age`) VALUES(25);

/* 创建索引 */
CREATE INDEX idx_talA_age_birth ON `talA`(`age`, `birth`);
/* 1.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`;

/* 2.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`,`birth`;

/* 3.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`;

/* 4.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`,`age`;

/* 5.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` ORDER BY `birth`;

/* 6.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `birth`;

/* 7.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `age`;

/* 8.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` ORDER BY `age` ASC, `birth` DESC;

ORDER BY子句，尽量使用索引排序，避免使用Using filesort排序。

MySQL支持两种方式的排序，FileSort和Index，Index的效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足两情况，会使用Index方式排序

  • ORDER BY语句使用索引最左前列。
  • 使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列。

结论：尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀原则。

  • 双路排序（又叫回表排序模式）：MySQL 4.1 之前使用的双路排序，先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；一句话，先从磁盘取排序字段，在buffer中进行排序，再从磁盘取其他字段(稳定2次对磁盘的IO操作)
  • 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；（如果sortBuffer能一次取完就是一次IO操作，否则需要看数据量了，每次取sortBuffer，需要多少次就进行多少次磁盘IO，然后多路合并）

对比 ：

其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键 和需要排序的字段放到 sort

buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

单路复用算法的优化策略：

  • 增大sort_buffer_size参数的设置。
  • 增大max_length_for_sort_data参数的设置。

提高ORDER BY排序的速度：

  • ORDER BY时使用SELECT *是大忌，查什么字段就写什么字段，这点非常重要。在这里的影响是：
    • 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会使用单路排序算法，否则使用多路排序算法。
  • 两种排序算法的数据都有可能超出sort_buffer缓冲区的容量，超出之后，会创建tmp临时文件进行合并排序，导致多次IO，但是单路排序算法的风险会更大一些，所以要增大sort_buffer_size参数的设置。
  • 尝试提高sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
  • 尝试提高max_length_for_sort_data：提高这个参数，会增加用单路排序算法的概率。但是如果设置的太高，数据总容量sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。

Mysql中单路排序和双路排序详解

3、group by 优化
  • GROUP BY实质是先排序后进行分组，遵照索引建的最佳左前缀。
  • 当无法使用索引列时，会使用Using filesort进行排序，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置，会提高性能。
  • WHERE执行顺序高于HAVING，能写在WHERE限定条件里的就不要写在HAVING中了。

3、慢查询日志

1、基本介绍

慢查询日志是什么？

  • MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。
  • long_query_time 的默认值为10，意思是运行10秒以上的语句。
  • 由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前 explain 进行全面分析。

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

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

录写入文件。

  • 查看慢查询日志是否开启：SHOW VARIABLES LIKE '%slow_query_log%';。
  • 开启慢查询日志：SET GLOBAL slow_query_log = 1;。使用该方法开启MySQL的慢查询日志只对当前数据库生效，如果MySQL重启后会失效。
# 1、查看慢查询日志是否开启
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                   |
| slow_query_log_file | /var/lib/mysql/8d87e9c530cf-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)

# 2、开启慢查询日志
mysql> SET GLOBAL slow_query_log = 1;
Query OK, 0 rows affected (0.00 sec)

#3、找到配置文件，然后为配置文件改个名！

如果要使慢查询日志永久开启，需要修改my.cnf文件，在[mysqld]下增加修改参数。

# my.cnf
[mysqld]
# 1.这个是开启慢查询。注意ON需要大写
slow_query_log=ON  

# 2.这个是存储慢查询的日志文件。这个文件不存在的话，需要自己创建,如果不创建默认会根据host_name_slow.log
slow_query_log_file=/var/lib/mysql/slow.log
  • 这个是由参数long_query_time控制的，默认情况下long_query_time的值为10秒。
  • MySQL中查看long_query_time的时间：SHOW VARIABLES LIKE 'long_query_time%';。
# 查看long_query_time 默认是10秒
# 只有SQL的执行时间>10才会被记录
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
 
mysql> set global long_query_time=3;    #修改慢查询的时间为3s
Query OK, 0 rows affected (0.00 sec)

修改long_query_time的时间，需要在my.cnf修改配置文件

[mysqld]
# 这个是设置慢查询的时间，我设置的为1秒
long_query_time=1

修改后需要重新连接或新开一个会话才能看到修改值

查新慢查询日志的总记录条数：SHOW GLOBAL STATUS LIKE '%Slow_queries%';。

mysql> select sleep(4);   #让当前sql休眠4s ;
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.00 sec)

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';    #慢SQL的记录数！
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.00 sec)

打开cat /var/lib/mysql//var/lib/mysql/sqx-slow.log

root@8d87e9c530cf:/var/lib/mysql# cat sqx-slow.log 
mysqld, Version: 5.7.35 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2021-12-13T09:45:51.298239Z
# User@Host: root[root] @ localhost []  Id:   213
# Query_time: 4.009345  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1639388751;
select sleep(4);
2、日志分析工具
# 1、mysqldumpslow --help 来查看mysqldumpslow的帮助信息
root@1dcb5644392c:/usr/bin# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default  # 按照何种方式排序
                al: average lock time		# 平均锁定时间
                ar: average rows sent		# 平均返回记录数
                at: average query time	# 平均查询时间
                 c: count  # 访问次数
                 l: lock time						# 锁定时间
                 r: rows sent						# 返回记录
                 t: query time					# 查询时间 
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries  # 返回前面多少条记录
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string  
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time
  
# 2、 案例
# 2.1、得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
 
# 2.2、得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
 
# 2.3、得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log

# 2.4、另外建议使用这些命令时结合|和more使用，否则出现爆屏的情况
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more
3、批量插入数据脚本
create database bigData ;
use bigData ; 

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,
ename 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;
# 在mysql中设置 
# log_bin_trust_function_creators 默认是关闭的 需要手动开启
mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';   #查看是否开启！

mysql> SET GLOBAL log_bin_trust_function_creators=1;    #开启！
Query OK, 0 rows affected (0.00 sec)

上述修改方式MySQL重启后会失败，在my.cnf配置文件下修改永久有效

[mysqld]
log_bin_trust_function_creators=ON
#生成随机字符串函数
delimiter $$
create function ran_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i=i+1;
end while;
return return_str;
end $$

#生成随机数函数
delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i=floor(100+rand()*10);
return i;
end $$
//存储过程
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,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),ran_string(6),'salesman',0001,curdate(),2000,400,rand_num());
until i=max_num
end repeat;
commit;
end $$

//存储过程
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),ran_string(10),ran_string(8));
until i=max_num
end repeat;
commit;
end $$
# 1、调用存储过程向dept表插入10个部门。
DELIMITER ;
CALL insert_dept(100,10);

# 2、调用存储过程向emp表插入50万条数据。
DELIMITER ;
CALL insert_emp(100001,500000);
4、Show Profile

默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

1、是否支持，看看当前的MySQL版本是否支持。

# 查看Show Profile功能是否开启
    mysql> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

2、开启Show Profile功能，默认是关闭的，使用前需要开启。

# 开启Show Profile功能
mysql> SET profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

3、运行SQL

SELECT * FROM emp GROUP BY id%10 LIMIT 150000;

SELECT * FROM emp GROUP BY id%20 ORDER BY 5;

4、查看结果，执行SHOW PROFILES;

Duration：持续时间。

mysql> SHOW PROFILES;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.00156100 | SHOW VARIABLES LIKE 'profiling'                   |
|        2 | 0.56296725 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 |
|        3 | 0.52105825 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 |
|        4 | 0.51279775 | SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5   |
+----------+------------+---------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

5、诊断SQL

SHOW PROFILE cpu,block io FOR QUERY Query_ID;   #对改SQL进行诊断
# 这里的3是第四步中的Query_ID。
# 可以在SHOW PROFILE中看到一条SQL中完整的生命周期。
mysql> SHOW PROFILE cpu,block io FOR QUERY 3;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000097 | 0.000090 |   0.000002 |            0 |             0 |
| checking permissions | 0.000010 | 0.000009 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000039 | 0.000058 |   0.000000 |            0 |             0 |
| init                 | 0.000046 | 0.000046 |   0.000000 |            0 |             0 |
| System lock          | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000023 | 0.000037 |   0.000000 |            0 |             0 |
| preparing            | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000041 | 0.000053 |   0.000000 |            0 |             0 |
| Sorting result       | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.520620 | 0.516267 |   0.000000 |            0 |             0 |
| Creating sort index  | 0.000060 | 0.000051 |   0.000000 |            0 |             0 |
| end                  | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000032 | 0.000064 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)

  • ALL：显示所有的开销信息。

  • BLOCK IO：显示块IO相关开销【通用】。

  • CONTEXT SWITCHES：上下文切换相关开销。

  • CPU：显示CPU相关开销信息【通用】。

  • IPC：显示发送和接收相关开销信息。

  • MEMORY：显示内存相关开销信息。

  • PAGE FAULTS：显示页面错误相关开销信息。

  • SOURCE：显示和Source_function。

  • SWAPS：显示交换次数相关开销的信息。

  • converting HEAP to MyISAM：查询结果太大，内存都不够用了，往磁盘上搬了。

  • Creating tmp table：创建临时表（拷贝数据到临时表，用完再删除），非常耗费数据库性能。

  • Copying to tmp table on disk：把内存中的临时表复制到磁盘，危险！！！

  • locked：死锁。

5、全局查询日志
在mysql的my.cnf中，设置如下
#开启
general_log=1

#记录日志文件的路径
general_log_file=/path/logfile

#输出格式
log_output=FILE
set global general_log=1;	 	 #开启全局查询日志
set global log_output='TABLE';   #以表的形式输出

#此后编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
select * from mysql.general_log;

四、MySQL锁机制

表级锁偏读、行级锁偏写

  • 共享锁–>读锁
  • 排他锁–>写锁

1、表锁（偏读）

create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) engine myisam;   #注意这里是基于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');
1、锁表的命令
show open tables  ;

/*为表加锁lock table [表名] read|write */

lock table mylock read , book write ;

加锁成功！

unlock tables  #解锁
2、读写锁测试

为什么，mylock表加了读锁，却无法读取其他未加锁的表呢？

这种机制是为了防止未解锁就去对别的表进行操作，最后忘了解锁这件事导致这个表一直被一个进程的读锁占据从而无法被其他线程修改

总结：当给表加了读锁之后，加锁的该进程和其余进程或者说用户，都有读的权限，都没有写的权限，或者被禁止了，或者被延迟了

总结：当表在一个进程中被写锁时，该进程本身同时拥有对该表的读写权限，而其余的进程对该表则既无读的权限，又无写的权限。

MyISAM引擎在执行查询语句SELECT之前，会自动给涉及到的所有表加读锁，在执行增删改之前，会自动给涉及的表加写锁。

MySQL的表级锁有两种模式：

  • 表共享读锁（Table Read Lock）
  • 表独占写锁（Table Write Lock）

対MyISAM表进行操作，会有以下情况

  • 対MyISAM表的读操作（加读锁），不会阻塞其他线程対同一表的读操作，但是会阻塞其他线程対同一表的写操作。只有当读锁释放之后，才会执行其他线程的写操作。
  • 対MyISAM表的写操作（加写锁），会阻塞其他线程対同一表的读和写操作，只有当写锁释放之后，才会执行其他线程的读写操作。

简言之，就是读锁会阻塞写，但不会阻塞读。而写锁会把读跟写都阻塞。

用逻辑解释一下：读锁不阻塞读，所以自己不能写，否则会不可重复读，而加写锁同样是为了数据一致性，所以要阻塞写

2、行锁（偏写）

行锁特点

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

InnoDB存储引擎和MyISAM存储引擎最大不同有两点：一是支持事务，二是采用行锁

  • Atomicity 、Consistency、Isolation、Durability
# 建表语句
CREATE TABLE `test_innodb_lock`(
`a` INT,
`b` VARCHAR(16)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='测试行锁'; 

# 插入数据
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(1, 'b2');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(2, '3');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(3, '4000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(4, '5000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(5, '6000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(6, '7000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(7, '8000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(8, '9000');

# 创建索引
CREATE INDEX idx_test_a ON `test_innodb_lock`(a);
CREATE INDEX idx_test_b ON `test_innodb_lock`(b);

1、打开SESSION1和SESSION2两个会话，都开启手动提交

# 开启MySQL数据库的手动提交
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)

2、读自己所写

# SESSION1 

# SESSION1対test_innodb_lock表做写操作，但是没有commit。
# 执行修改SQL之后，查询一下test_innodb_lock表，发现数据被修改了。
mysql> UPDATE `test_innodb_lock` SET `b` = '88' WHERE `a` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a    | b    |
+------+------+
|    1 | 88   |
|    2 | 3    |
|    3 | 4000 |
|    4 | 5000 |
|    5 | 6000 |
|    6 | 7000 |
|    7 | 8000 |
|    8 | 9000 |
+------+------+
8 rows in set (0.00 sec)

# SESSION2 

# SESSION2这时候来查询test_innodb_lock表。
# 发现SESSION2是读不到SESSION1未提交的数据的。
mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    2 | 3    |
|    3 | 4000 |
|    4 | 5000 |
|    5 | 6000 |
|    6 | 7000 |
|    7 | 8000 |
|    8 | 9000 |
+------+------+
8 rows in set (0.00 se

3、行锁两个SESSION同时对一条记录进行写操作

# SESSION1 対test_innodb_lock表的`a`=1这一行进行写操作，但是没有commit
mysql> UPDATE `test_innodb_lock` SET `b` = '99' WHERE `a` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# SESSION2 也对test_innodb_lock表的`a`=1这一行进行写操作，但是发现阻塞了！！！
# 等SESSION1执行commit语句之后，SESSION2的SQL就会执行了
mysql> UPDATE `test_innodb_lock` SET `b` = 'asdasd' WHERE `a` = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

4、行锁两个SESSION同时不同记录进行写操作

# SESSION1 対test_innodb_lock表的`a`=6这一行进行写操作，但是没有commit
mysql> UPDATE `test_innodb_lock` SET `b` = '8976' WHERE `a` = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# SESSION2 対test_innodb_lock表的`a`=4这一行进行写操作，没有阻塞！！！
# SESSION1和SESSION2同时对不同的行进行写操作互不影响
mysql> UPDATE `test_innodb_lock` SET `b` = 'Ringo' WHERE `a` = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1、索引失效行锁变表锁
# SESSION1 执行SQL语句，没有执行commit。
# 由于`b`字段是字符串，但是没有加单引号导致索引失效
mysql> UPDATE `test_innodb_lock` SET `a` = 888 WHERE `b` = 8000;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

# SESSION2 和SESSION1操作的并不是同一行，但是也被阻塞了？？？
# 由于SESSION1执行的SQL索引失效，导致行锁升级为表锁。
mysql> UPDATE `test_innodb_lock` SET `b` = '1314' WHERE `a` = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
2、间隙锁的危害

什么是间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或者排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范文内但并不存在的记录，叫做"间隙(GAP)"。

InnoDB也会对这个"间隙"加锁，这种锁的机制就是所谓的"间隙锁"

因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值不存在。

间隙锁有一个比较致命的缺点，就是**当锁定一个范围的键值后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。**在某些场景下这可能会対性能造成很大的危害。

3、如何锁定一行
#语法格式:
select * from [表名]  ... for update  #锁定某一行

4、行锁分析
mysql> SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 39000 |
| Innodb_row_lock_time_avg      | 39000 |
| Innodb_row_lock_time_max      | 39000 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
5 rows in set (0.01 sec)

対各个状态量的说明如下：

  • Innodb_row_lock_current_waits：当前正在等待锁定的数量
  • Innodb_row_lock_time：从系统启动到现在锁定总时间长度（重要）
  • Innodb_row_lock_time_avg：每次等待所花的平均时间（重要）
  • Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间
  • Innodb_row_lock_waits：系统启动后到现在总共等待的次数（重要）

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

5、优化建议
  • 尽可能让所有数据检索都通过索引来完成，避免五索引行锁升级为表锁
  • 合理设计索引，计量缩小所的范围
  • 尽可能较少检索条件，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度
  • 尽可能低级别事务隔离

由于篇幅太长，关于主从复制的文章放在下篇！

版权说明 : 本文为转载文章, 版权归原作者所有 版权申明

原文链接 : https://blog.csdn.net/m0_46571920/article/details/121939048

内容来源于网络，如有侵权，请联系作者删除！

mysql索引Database数据库数据库相关数据库锁

相关文章

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多

    目录

    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com