通用开发技能系列：MySQL进阶

云原生学习路线导航页（持续更新中）

本文是 通用开发技能系列 文章，主要对编程通用技能 MySQL进阶 内容进行学习

1.SQL基础+语法

• 通用开发技能系列：SQL基础+语法学习

2.进阶篇

2.1.存储引擎

2.1.1.MySQL体系结构：

2.1.2.存储引擎是什么

• 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。

• 存储引擎是基于表 而不是基于库的，所以存储引擎也可以被称为表引擎。

• 在mysql5.5以前，默认的存储引擎是MyISAM，mysql5.5之后默认存储引擎是InnoDB。

• 注意：索引也是存在于引擎层中的，所以不同的存储引擎对索引的实现方式不同。

• 相关操作：

  -- 查询建表语句
  show create table account;
  
  -- 建表时指定存储引擎
  CREATE TABLE 表名(
  	...
  ) ENGINE=INNODB;
  
  -- 查看当前数据库支持的存储引擎
  show engines;
  

• 我们下面主要学习三种存储引擎：InnoDB、MyISAM、Memory

2.1.3.InnoDB

• InnoDB 是一种兼顾 高可靠性（支持事务） 和 高性能（支持行级锁） 的通用存储引擎
• 在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎。
• InnoDB 特点：
  • DML 操作遵循 ACID 模型，支持事务
  • 支持 行级锁，提高并发访问性能
  • 支持 外键 约束，保证数据的完整性和正确性
• 文件：
  • xxx.ibd文件
    • InnoDB 引擎的每张表都会对应一个xxx.ibd表空间文件，其中xxx代表表名。
      • 该文件中 存储了 该表的表结构、数据、索引 信息
      • Mysql8.0以前，表结构使用 frm文件 存储，8.0之后使用 sdi文件 存储，不过sdi文件又和ibd文件融合了，因此使用InnoDB引擎的每张表都会对应一个ibd文件，该文件内部存储了 表结构、数据、索引 三部分信息
  • 参数：innodb_file_per_table
    • 用于决定多张表共享一个表空间文件，还是每张表对应一个表空间文件
    • 在Mysql8.0之后，这个开关默认是打开的，即一张表就对应一个表空间文件
    • 如何查看 innodb_file_per_table 开关是否打开？
      • 使用查看 Mysql 变量 的方式：show variables like 'innodb_file_per_table';
  • 如何从 idb文件 中 提取表结构数据：
    • 打开ibd文件所在目录，然后打开cmd运行：ibd2sdi xxx.ibd，即可从ibd文件提取出sdi文件
      
• InnoDB 逻辑存储结构：
  • .ibd文件就是 表空间 TableSpace，表空间中存储的都是 段 Segment
  • 每个段中存储的是很多的 区 Extent
  • 每个区中存储的是很多的 页 Page，在InnoDB中，Page是磁盘操作的最小单元
  • 每个页中存储的是很多的 行 Row，包括 最后一次操作事务的id、一些指针、各个字段的数据…
  • 各逻辑单元的大小：
    • 一个区 的大小固定，是1M
    • 一个页 的大小固定，是16K
    • 因此：一个区固定包含64个页
      

2.1.4.MyISAM

• MyISAM 是 MySQL 5.5以前的默认存储引擎。
• 特点：
  • 不支持事务，不支持外键
  • 支持 表锁，不支持 行锁
  • 访问速度快
• 文件：MyISAM存储引擎包含三种文件
  • xxx.sdi: 存储表结构信息，Json格式文件
  • xxx.MYD: 存储数据
  • xxx.MYI: 存储索引

2.1.5.Memory

• Memory 引擎的表数据是存储在内存中的，受硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用。
• 特点：
  • 存放在内存中，速度快
  • hash索引（默认）
• 文件：
  • xxx.sdi: 存储表结构信息

  因为数据等信息都存储于内存，所以不需要再磁盘中建立专门的文件存储

2.1.6.三种存储引擎特点对比

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁、表锁	表锁	表锁
B+tree索引	支持（默认索引）	支持（默认索引）	支持
Hash索引	-	-	支持（默认索引）
全文索引	支持（5.6版本之后）	支持	-
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	-	-

2.1.7.存储引擎的选择

• 在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。
  • InnoDB：如果应用对 事务的完整性 有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，则 InnoDB 是比较合适的选择
  • MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那这个存储引擎是非常合适的。
  • Memory：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。Memory 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性
• 电商中的足迹和评论适合使用 MyISAM 引擎，缓存适合使用 Memory 引擎。
• 绝大多数情况下，我们选择的都是InnoDB。
  • 当前，使用MyISAM、Memory的场景，很多都被Nosql数据库替代了。
  • 如MongoDB替换了MyISAM、Redis替换了Memory

2.2.索引

2.2.1.索引概述

• 索引是帮助 MySQL 高效获取数据的有序 数据结构

• 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查询算法，这种数据结构就是索引
  

• 优缺点：

  • 优点：
    • 提高数据检索效率，降低数据库的IO成本（因为数据是放在磁盘中的，检索次数减少，会减少IO成本）
    • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
  • 缺点：
    • 索引也是一种数据结构，保存索引信息当然也要占用磁盘空间，所以索引列也是要占用空间的
    • 索引大大提高了查询效率，但降低了更新的速度，比如 INSERT、UPDATE、DELETE
      • 对数据的 更新、增加、删除 等，都需要对索引也做一份更新，维护成本较高

    不过两个缺点基本都能够忽略，1）磁盘很便宜；2）大多数业务系统查询占得比重最大

2.2.2.索引结构

1、常见的4种索引结构

• MySQL的索引是在 存储引擎层 实现的，不同的存储引擎有不同的索引结构，主要包含以下几种:

  索引结构	描述
  B+Tree	最常见的索引类型，大部分引擎都支持B+树索引
  Hash	底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，性能很高，但是不支持范围查询
  R-Tree(空间索引)	空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
  Full-Text(全文索引)	是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene, Solr, ES

• 三种存储引擎 对 索引结构的支持：

  索引	InnoDB	MyISAM	Memory
  B+Tree索引	支持	支持	支持
  Hash索引	不支持	不支持	支持
  R-Tree索引	不支持	支持	不支持
  Full-text	5.6版本后支持	支持	不支持

2、二叉树

3、红黑树

• 二叉树的缺点可以用红黑树来解决：

  

• 但是红黑树本身也是一个二叉树，在大数据量情况下，也会有层级较深，检索速度慢的问题。

4、B-Tree

• 为了解决上述问题，可以使用 B-Tree 结构（B树）
• B-Tree (多路平衡查找树) 以一棵最大度数为5（5阶）的 B-Tree 为例
  • 树的度数：指一个节点的子节点个数
    • 度数为5，表示每个节点能够有5个子节点，即每个节点最多存储4个key，有5个指针
  • B树中，每个节点，既存储指针，又存储数据。

B-Tree 的数据插入过程动画参照：https://www.bilibili.com/video/BV1Kr4y1i7ru?p=68

演示地址：https://www.cs.usfca.edu/~galles/visualization/BTree.html

5、B+Tree

• 经典B+树 结构图：
  

演示地址：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

• 经典B+树 与 B-Tree 的区别：

  • 所有的数据都会出现在叶子节点，非叶子节点只起索引的作用，不会存储数据，数据只在叶子结点中存储
  • 所有叶子节点形成了一个单向链表，一个叶子结点内的所有数据节点是由单向链表串起来

• MySQL 的 B+Tree索引

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

• MySQL 的 B+Tree索引，一个叶子结点就是一个页Page，页内部存储的是Row。

  • 页与页之间是 双向链表
  • Row与Row之间是单向链表
    

6、Hash

• 哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
• 如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。
  
• 特点：
  • Hash索引只能用于对等比较（=、in），不支持范围查询（between、>、<、…）
  • 无法利用索引完成排序操作
  • 查询效率高，通常（不发生哈希碰撞时）只需要一次检索就可以了，效率通常要高于 B+Tree 索引
• 存储引擎支持：
  • Memory：默认索引结构就是Hash
  • InnoDB：具有自适应hash功能
    • 自适应Hash功能：根据查询条件，自动将B+Tree索引构建为Hash索引

7、索引结构面试题

• 为什么 InnoDB 存储引擎选择使用 B+Tree 索引结构？
  • 相对于二叉树，B+Tree 的层级更少，搜索效率高
  • 对于 B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，一个磁盘块（页Page）空间固定，分出一部分保存数据，那么保存的指针自然就减少了。保存同样多数据的情况下，树的高度就会增大，性能就会降低。而B+Tree只有在叶子结点中保存数据，非叶子节点专注于保存索引指针，能保存的指针更多，树的层数更小。
  • 相对于 Hash 索引，B+Tree 支持 范围匹配 及 排序操作。而Hash索引只支持等值匹配

2.2.3.索引分类

1、索引的种类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

2、根据存储形式的索引分类

• 在 InnoDB 存储引擎中，根据 索引的存储形式，又可以分为以下两种：

  分类	含义	特点
  聚集索引(Clustered Index)	将数据存储与索引放一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
  二级索引/辅助索引/非聚集索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

• 聚集索引：必须有，因为它是存储数据用的，没有聚集索引，就无法存储数据了

• 聚集索引的选取规则：

  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
  • 如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

3、聚集索引演示图

• 如图，根据主键id建立的索引 即为 聚集索引。聚集索引的叶子结点，保存的数据就是所有的行数据。
• 如果对name字段建立一个索引，只能是二级索引。二级索引的叶子结点中保存的是该项对应的id值。
  
• 下面通过一条SQL语句，看一下索引的执行流程：
  • 查询name="Arm"的数据，首先会查询由name字段构建的二级索引，索引已经是按照自然排序排好了，通过比较字母就可以找到Arm，然后获取到Arm这行数据对应的id值
  • 拿到id值，再到聚集索引中找到对应id的那个叶子结点，其中就保存着该id的行数据Row
  • 这种先查询二级索引获取主键值，然后再查询聚集索引的行为，称为 回表查询。
    

4、思考题

（1）以下 SQL 语句，哪个执行效率高？为什么？

select * from user where id = 10;
select * from user where name = 'Arm';
-- 备注：id为主键，name字段创建的有索引

答：第一条语句，因为第二条需要回表查询，相当于两个步骤。

（2）估算 InnoDB 主键索引的 B+Tree 高度为多少？

• 结论：
  • InnoDB 的 B+Tree，在树高为3时，就可以存下2000多万的bigInt数据，查询次数很少，效率非常高
  • 如果有更大的数据量，一般会考虑分表，不会都存到一张表中，后续运营篇会讲。
• 做出以下假设：
  • 一行Row数据大小为1k，那么一页中可以存储16行这样的数据。
  • InnoDB中，一个指针占用6个字节的空间，主键Key的数据类型假设为bigint，占用字节数为8.
    • 指针，即这个： 假设占用6B
    • 主键Key，即这个：，假设占用8B
• 根据以上假设，可以做出如下计算：
  • 一个非叶子节点，也是一页，大小固定为16KB。
  • 一页中，存储的key数量 比 指针数量 少 1个。设存储key数量为n，则存储指针数量为n+1，列出方程：
  • n * 8 + (n + 1) * 6 = 16 * 1024，解出n约为1170。
  • 则：一个非叶子节点，能存储key数量为1170，存储指针数量为1171。
• 另外，一个叶子结点也是一页，大小16KB，所以能够存储16个Row。因此：
  • 如果树的高度为2，那么他能存储的数据量大概为：1171 * 16 = 18736；
    • 第1行有1171个指针，能指向1171个叶子结点，一个叶子结点有16Row数据，所以相乘即为数据量
  • 如果树的高度为3，那么他能存储的数据量大概为：1171 * 1171 * 16 = 21939856。
    • 第1行有1171个指针，能指向1171个第2行非叶子结点，所以两行一共有1171*1171个指针
    • 第三行就是叶子结点，一个叶子结点有16Row数据，所以 1171 * 1171 * 16 即为数据量
• 结论：
  • InnoDB 的 B+Tree，在树高为3时，就可以存下2000多万的bigInt数据，查询次数很少，效率非常高
  • 如果有更大的数据量，一般会考虑分表，不会都存到一张表中，后续运营篇会讲。

2.2.4.语法

• 创建索引：

  CREATE [ UNIQUE | FULLTEXT ] 
  	INDEX index_name 		# 索引的名字
  ON table_name 			# 要为哪个表创建索引
   	(index_col_name, ...); 	# 要为哪一个字段创建索引
  

  • 如果 CREATE 后面不加索引类型参数，则创建的是常规索引
  • 如果索引 只关联了 一个字段，则表示为 单列索引
  • 如果索引 关联了 多个字段，则表示为 联合索引/组合索引

• 查看索引：

  SHOW INDEX FROM table_name;	# 查看指定表中的所有索引
  

• 删除索引：

  DROP INDEX index_name ON table_name;	# 删除指定表中的指定索引
  

• 案例：

  -- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引
  create index idx_user_name on tb_user(name);
  
  -- phone手机号字段的值非空，且唯一，为该字段创建唯一索引
  create unique index idx_user_phone on tb_user (phone);
  
  -- 为profession, age, status创建联合索引
  create index idx_user_pro_age_stat on tb_user(profession, age, status);
  
  -- 为email建立合适的索引来提升查询效率
  create index idx_user_email on tb_user(email);
  
  -- 删除索引
  drop index idx_user_email on tb_user;
  

2.2.5.SQL性能分析

• 学习SQL性能分析方法，是为了后续更好的做SQL优化
• 我们主要优化的是 查询语句，而 对查询语句的优化，对索引的优化占据主导地位

1、如何查看SQL语句的执行频次

• 查看SQL语句的执行频次 的作用：
  • 查看数据库执行各种sql语句的频次，判断当前数据库以什么操作为主，决定我们对哪种sql语句进行更深入的优化
• 查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次：

  SHOW GLOBAL STATUS LIKE 'Com_______';	# 查看全局的sql执行信息
  SHOW SESSION STATUS LIKE 'Com_______';	# 查看当前会话的sql执行信息
  

• 其中，Com后面的下划线，一个下划线表示一个字符。这表示模糊查询，查询指定字符数量的命令执行信息
  • 如：使用全局Global查询，Com后面跟着7个字符，就会查出下面的内容
    

2、如何查看慢查询日志

• 慢查询日志 的作用

  • 通过上面的学习，我们能够知道哪些表主要是做查询操作，我们需要对这些表进行查询优化
  • 但是，要 **对哪些查询语句进行优化呢？怎么定位要优化的查询语句？**这就需要查看慢查询日志了

• 慢查询日志是什么

  • 慢查询日志是mysql提供的，是用于记录 所有执行时间超过指定参数（long_query_time，单位秒，默认10秒）的所有SQL语句的日志。

• MySQL的慢查询日志默认没有开启，查看开关是否开启：
  

• 开启慢查询日志开关，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

  slow_query_log=1	# 开启慢查询日志开关
  

• 自定义慢查询日志的阈值为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志

  long_query_time=2	# 自定义慢查询日志的阈值为2秒
  

• 更改后记得重启MySQL服务

• Linux下，慢查询日志文件位置

  /var/lib/mysql/localhost-slow.log
  

  

3、profile详情

• profile详情的作用

  • 通过上面的学习，我们能够定位慢查询sql语句

  • 但是，慢查询语句只会记录超过阈值的sql语句，如果一些语句执行时间很接近阈值，但是没有超过，就不会记录。事实上，这些语句也应该优化。

  • 那么如何定位这些语句呢？

  • 使用profile查看sql语句的执行详情，能在做SQL优化时帮我们了解一条sql语句的时间都耗费在了哪里。

• 如何查看当前Mysql是否支持profile 操作？

  SELECT @@have_profiling;
  

  

• profiling 默认关闭，可以通过set语句在session/global级别开启 profiling：

  • 直接执行命令：SET profiling = 1;，即可开启开关

• 查看语句耗时的命令如下：

  • 查看所有语句的耗时：show profiles;

    

  • 查看指定query_id的SQL语句各个阶段的耗时：show profile for query query_id;

    

  • 查看指定query_id的SQL语句，每个阶段对CPU使用情况：show profile cpu for query query_id;

    

4、explain/desc执行计划

• explain/desc 的作用

  • 前面分析sql语句的性能，都是按照执行时间长短进行的，这种方式其实不是很恰当，只是粗略的估计
  • 如何更深入的分析sql语句的性能？
  • 使用explain或desc命令，可以获取sql语句的执行计划，包括sql用没用到索引、表连接方式、表连接顺序等。两个命令的执行效果是一样的

• 语法：直接在select语句之前加上关键字 explain / desc，就可以获取该sql语句的执行计划

  

• explain / desc 各字段含义：

  • id：select 查询的序列号

    • 表示查询中执行 select 子句或者操作表的顺序（id相同，执行顺序从上到下；id不同，值越大越先执行）

    • 上图是单表查询，只有一条语句，id=1；如果是多表查询，就会有执行顺序了

      

  • select_type：表示 SELECT 的类型，常见取值有：

    • SIMPLE（简单表，即不适用表连接或者子查询）
    • PRIMARY（主查询，即外层的查询）
    • UNION（UNION中的第二个或者后面的查询语句）
    • SUBQUERY（SELECT/WHERE之后包含了子查询）
    • 等

  • type：表示连接类型（判断sql语句性能的重要指标）

    • 性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all
    • 优化的时候，尽量把sql语句的type优化成更靠前的类型
      • 一般来说，不大可能优化成NULL，在你不访问任何表的时候，type=NULL，业务中基本不可能存在这种情况。
      • system表示访问了一张系统表，性能也很好
      • 根据 主键、唯一索引 查询，一般为 type = const
      • 根据 非唯一性索引 查询，一般为 type = ref
      • type = index，一般是用了索引，但是对索引的数据进行了全部扫描，性能也不高
      • type = all，一般为全表扫描，性能最低

  • possible_key：可能 应用在这张表上的索引，有一个或多个

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

  • Key_len：表示索引中使用的字节数

    • 该值为索引字段最大可能长度，并非实际使用长度
    • 在不损失精确性的前提下，长度越短越好

  • rows：MySQL认为必须要执行的行数

    • 在InnoDB引擎的表中，是一个估计值，可能并不总是准确的

  • filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

    • 如：根据主键id去查，我们找到1行，也是返回1行，那么filtered=100%，最好了

  • Extra：额外的信息

5、慢查询分析流程总结

分成三步：

• 开启慢查询日志、设置慢查询阈值
• 通过 profile 查看sql语句的执行详情
• 通过 explain 查看sql语句的执行计划

详细介绍：

• 首先，可以开启慢查询日志。

  • 慢查询日志默认是不开启的，我们可以在mysql配置文件（/etc/my.cnf）中

    • 设置 slow_query_log=1 ，即可开启慢查询日志开关
    • 设置 long_query_time=2，即可 自定义慢查询日志 的 阈值 为2秒
    • 然后重启服务

  • 也可以直接执行命令：

    • SELECT @@slow_query_log; 查看变量值

    

    • set global slow_query_log = 1; 设置slow_query_log 变量值
    • set session long_query_time= 2; 设置long_query_time变量值

  • 慢查询日志，默认会存在于mysql目录下，生成一个 localhost-slow.log 文件，可以使用 tail -f localhost 实时监测该文件。

  • 慢查询日志，会显示出 sql语句、执行时间、来自客户端、总执行耗时、等待锁耗时、返回行数 等

• 其次，我们可以通过 profile 查看sql语句的执行详情，能在做SQL优化时帮我们了解一条sql语句的时间都耗费在了哪里。

  • profiling 默认也是关闭，可以通过 set profiling = 1; 开启

  • profile关键字可以查看一条sql语句在 各个阶段的详细耗时、对CPU使用情况 等：

    • 查看 执行过的 所有语句的耗时： 会显示每一条语句的 总耗时、query_id

      • show profiles;

        

    • 根据 一个query_id，还可以查看 具体 一条 语句的各个阶段详细执行时间：

      • show profile for query query_id;

        

    • 查看指定query_id的SQL语句，每个阶段对CPU使用情况：

      • show profile cpu for query query_id;

      

• 使用explain或desc命令，可以获取sql语句的执行计划

  • 使用前面分析sql语句的性能，都是按照执行时间长短进行的。explain可以进行更深入的分析
  • 在 sql语句 前加上explain关键字，可以查看一条sql语句的执行计划
  • 包括：sql语句可能使用的索引、实际使用的索引、索引生效的字节数（联合索引中部分索引可能未生效）、连接的类型
  • 我们可以看到 这条SQL语句，是否发生了索引失效，是否是全表扫描 等。
  • 进而 可以从索引层面进行优化

• 如果sql语句执行没问题，索引也正常使用。

  • 那么还查询慢的话，要看看sql语句中有没有什么冗余的字段，该去掉的去掉，尽量保证使用覆盖索引

• 如果还不行，那就可能是你的表中数据太多，查询比较慢，可以考虑分库分表

• 如果还不行，就要考虑是不是机器的问题，要不要对机器进行升级，或者 部署主从集群，进行读写分离

2.2.6.索引对于查询效率的提升

• 对于一张有一千万条数据的表，其中有一个name字段
• 如果对name字段，没有建立索引，查询某一个name值的所有信息：
  • select * from tb_sku where name = "123";
  • 耗时：约21s
• 然后对name字段，建立了一个索引，再执行同一条SQL语句：
  • select * from tb_sku where name = "123";
  • 耗时：约0.01s
• 可以看出，建立索引对sql语句性能的提升非常巨大

2.2.7.索引使用规则

索引的合理使用，才能对性能提升

1、最左前缀法则

（1）什么是最左前缀法则

• 如果索引关联了多列（联合索引），要遵守最左前缀法则

• 最左前缀法则指的是：查询应该从索引的最左列开始，并且不跳过索引中的列。

  • 从索引的最左列开始，指的是包含最左列，在sql语句中不一定写在最左边

• 索引失效两种情况：

  • 如果跳跃某一列，索引将部分失效（跳跃的那一列之后的字段索引失效）。
  • 联合索引中，出现范围查询（<, >），范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题。

（2）举例：满足最左前缀的三种情况

• 比如我们创建了一个索引，关联了三列，在写create index 语句的时候，三列先后顺序写的是profession、age、status，那么三列就相当于我们已经排了序，从左到右为：profession、age、status

  

• 如果执行下面sql语句：

  # 查看指定sql语句的执行计划
  explain select * from tb_user where age=31 and profession='软件工程' and status='0'
  

  • 输出：

    

  • 该sql语句符合最左前缀法则。

    • 包含最左列profession列，只要包含就行，在sql语句中不要写在最左边
    • 没有跳跃某一列，包含profession，接着包含第二列age，接着包含第三列status

• 如果执行下面sql语句：

  # 查看指定sql语句的执行计划
  explain select * from tb_user where age=31 and profession='软件工程'
  

  • 输出：

    

  • 该sql语句符合最左前缀法则。

    • 包含最左列profession列，只要包含就行，在sql语句中不要写在最左边
    • 没有跳跃某一列，包含profession，接着包含第二列age

• 如果执行下面sql语句：

  # 查看指定sql语句的执行计划
  explain select * from tb_user where profession='软件工程'
  

  • 输出：

    

  • 该sql语句符合最左前缀法则。

    • 包含最左列profession列，只要包含就行，在sql语句中不要写在最左边
    • 没有跳跃某一列，包含第一列profession

由以上三个案例可以推出：profession列索引长度为47，age列索引长度为2，status列索引长度为5

（3）举例：未包含 最左侧索引，导致整个索引失效

• 如果执行下面sql语句：

  # 查看指定sql语句的执行计划
  explain select * from tb_user where age=31 and status='0'
  

  • 输出：

    

（4）举例：跳跃某一列，导致后续索引失效

• 如果执行下面sql语句：

  # 查看指定sql语句的执行计划
  explain select * from tb_user where profession='软件工程' and status='0'
  

  • 输出：

    

  • 结论：

    • 虽然用到了这个联合索引，但是索引长度为47，即只有其中的profession索引生效，status失效。原因就是 跳过了status前面的age列，导致age列后续的status失效

（5）举例：范围查询（<, >）导致右侧索引失效

• 如果执行下面sql语句：

  # 查看指定sql语句的执行计划
  explain select * from tb_user where profession='软件工程' and age > 30 and status='0'
  

  • 输出：

    

  • 结论：

    • 用到了索引，索引长度为49，说明只有profession、age索引生效，status索引失效。这是因为age使用了范围查询（<, >），导致右侧的列索引全部失效。

  • 解决方法：

    • 开发中我们应该尽力规避范围查询导致的索引失效，在业务允许的情况下，尽量使用（>=、<=）代替范围查询。

2、索引失效情况

• 联合索引
  • 不满足最左前缀法则
    • 未包含 最左侧索引，导致整个索引失效
    • 跳过了联合索引的某一列，那么该列之后的字段都将失效
  • 范围查询（<, >）导致右侧索引失效，可以使用 >=、<= 替换
• 在索引列上进行运算操作，索引将失效。
  • 如：使用函数 explain select * from tb_user where substring(phone, 10, 2) = '15';
• 字符串类型的字段，在使用时如果不加引号，索引将失效。
  • 如：explain select * from tb_user where phone = 17799990015;，
  • phone本来是varchar类型，但是此处phone的值没有加引号，phone索引将会失效
• 模糊查询中，如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效 。如：
  • explain select * from tb_user where profession like '工程%';，后面有% 不会失效
  • explain select * from tb_user where profession like '%工程';，前面有% 会失效
  • explain select * from tb_user where profession like '%工程%';，前后都有 % 也会失效
• 用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。
  • 如：explain select * from tb_user where id=10 or age=23;
  • 其中id有索引、age没有索引，那么所有的索引都不会生效，id索引也会失效
  • 因此，需要用到多个查询的时候，推荐使用 联合查询union、union all
• 如果 MySQL 评估使用索引比全表更慢，则不使用索引。
  • 如：在使用 >= 或 <= 时，发现最后要返回的数据量也是全部数据，那么就会使用全局扫描，不会使用索引
• 还有几种可能导致索引失效的情况
  • 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
  • 索引字段上使用 is null， is not null，可能导致索引失效。
  • 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。
• Between and

3、SQL 提示

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

• 举例：

  • 建议使用哪个索引：

    • explain select * from tb_user use index(idx_user_pro) where profession="软件工程";

  • 设置不使用哪个索引：

    • explain select * from tb_user ignore index(idx_user_pro) where profession="软件工程";

  • 设置必须使用哪个索引：

    • explain select * from tb_user force index(idx_user_pro) where profession="软件工程";

• use 是建议，实际使用哪个索引 MySQL 还会自己权衡运行速度去更改，force就是无论如何都强制使用该索引。

4、覆盖索引&回表查询

• 什么是覆盖索引

  • 覆盖索引 是索引的一种，指的是用户想要查询的字段，在一个索引中全部都有保存，因此查询一次索引就可以把数据全部返回，不需要再进行回表查询。
  • 举例：
    • 事先创建好了一个 二级索引 idx_name_age，包含2个字段：name、age
    • 然后执行下面sql语句：select id, name, age from user where name = '张三';
    • 执行流程分析：
      • 按照name字段查询，会使用到索引 idx_name_age，找到对应name的那一项；
      • 找到之后，因为索引中也包括age，所以age也拿到了；
      • 又因为 二级索引 叶子结点保存的数据是对应的id值，所以id值也能拿到
    • 综上，查询一次二级索引 idx_name_age，就可以拿到用户想要的 id, name, age 所有数据，那么索引 idx_name_age 对于当前sql语句来说，就是一个覆盖索引。

• 不满足覆盖索引的情况：

  • 先创建好了一个 二级索引 idx_name_age，包含2个字段：name、age
  • 然后执行下面sql语句：select id, name, age, profession from user where name = '张三';
  • 易知，id、name、age都能在 索引 idx_name_age 中拿到，但是 profession 拿不到，就需要根据id再去聚集索引中进行回表查询，这就不满足覆盖索引了。

• explain命令输出结果对 覆盖索引 的描述：

  • 使用 explain 查看sql语句的执行计划时，最后一列 extra 字段可能会描述出当前使用索引的情况
  • extra 字段 值为 using index condition：表示查找使用了索引，但是需要回表查询数据
  • extra 字段 值为 using where; using index;：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询

• 结论：

  • 我们应该尽量使用覆盖索引，减少 select * 的使用
  • 因为 select * 很可能出现索引中不包括的列，进而造成回表查询，降低效率，除非有联合索引包含了所有字段。

• 面试题：

  • 一张表，有四个字段（id, username, password, status），由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案：

    • select id, username, password from tb_user where username='itcast';

  • 解：给username和password字段建立联合索引，则不需要回表查询，直接覆盖索引。键为username和password，值为id，3个字段的值就都有了，覆盖索引

5、前缀索引

• 当字段类型为字符串（varchar, text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

• 语法：create index idx_xxxx on table_name(columnn(n));

• 前缀长度：

  • 前缀长度可以根据索引的选择性来决定
  • 选择性：指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高。
  • 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

• 求选择性公式：

  # count(distinct email) 计算的是email字段不重复的索引值
  # count(*) 计算的是tb_user表的记录总数
  # 直接比值，得到的是 整个enail字段 的选择性
  select count(distinct email) / count(*) from tb_user;
  
  # count(distinct email) 计算的是email字段的 前5个字符 不重复的索引值
  # count(*) 计算的是tb_user表的记录总数
  # 直接比值，得到的是 enail字段前5个字符 的选择性
  select count(distinct substring(email, 1, 5)) / count(*) from tb_user;
  
  # substring(field, start, len)函数，表示截取field字段，从第start个字符开始，截取长度为len
  

• show index 里面的 sub_part 可以看到截取字段的长度，为哪一段建立的索引

  

• 前缀索引的查询流程：

  

6、单列索引&联合索引

• 按包含列数分类，索引分为两类：

  • 单列索引：即一个索引只包含单个列，如idx_phone，只包含phone列
  • 联合索引/组合索引：即一个索引包含了多个列，如idx_phone_name，包含phone、name两列

• 看下面的情况：

  • 我们事先建立了两个索引 idx_phone, idx_name

  • 执行这个sql语句：

    • select id,name,phone from tb_user where phone='15023456543' and name='zhangsan'

  • 查看它的执行计划，发现实际只用到了 idx_phone ，没有使用 idx_name

  • 结论：在有多个单列索引，并且进行多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询，并不会使用全部单列索引。而phone中不可能找到name，所以必然会造成回表查询，性能降低

• 因此，在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引，尽可能地避免回表查询。

  • 如上例中，我们可以建立一个联合索引：idx_phone_name
  • 然后执行这条sql语句，不过可能还用不到 idx_phone_name，因为可能受到 idx_phone 单列索引的影响
    • select id,name,phone from tb_user where phone='15023456543' and name='zhangsan'
  • 我们可以使用use、force等关键字，手动指定要使用的索引
    • select id,name,phone from tb_user use index(idx_phone_name) where phone='15023456543' and name='zhangsan'
    • 当然也可以直接删除 单列索引 idx_phone，这样就不会受到干扰了
  • 使用联合索引 idx_phone_name 后，还成为了覆盖索引，一次查询就拿到了id、name、phone全部信息，避免了回表查询。

• 联合索引的执行流程：

  

2.2.8.索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引

   • 数据量较大：百万级数据量
   • 只用来插入数据的表不用建索引，索引提高的是查询性能

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

   • where、order by、group by 后面的字段，一般使用索引能够提高性能
   • 并且尽量使用联合索引

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

   • 如性别字段、状态字段等，重复量太大，建了索引效率也不高

4. 如果是字符串类型的字段，字段长度较长，尤其是大文本字段，可以针对于字段的特点，建立前缀索引

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

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

7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

2.2.9.索引下推

• 索引下推（Index Condition Pushdown，ICP） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

• 原理：

  • MySQL服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

  • 索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

  • 我们来具体看一下，在没有使用ICP的情况下，MySQL的查询：

    • 存储引擎读取索引记录；

    • 根据索引中的主键值，定位并读取完整的行记录；

    • 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

  • 使用ICP的情况下，查询过程：

    • 存储引擎读取索引记录（不是完整的行记录）；

    • 判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；

    • 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；

    • 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

2.2.10.索引总结

2.3.SQL 优化

2.3.1.插入数据

1、插入数据的3种优化方式

• 多次插入数据，尽量采用批量插入。

  • 单条插入：一条一条insert语句的执行，每次都要 建立数据库连接、数据的网络传输，性能较低

  • 批量插入：将要插入的多条数据，使用一条insert语句执行，只需要建立一条数据库连接，传输一次数据

    • 不过，建议一次插入的数据不建议超过1000条

• 手动提交事务

  • 默认情况下，事务是自动控制的，每执行一条insert语句，就会开启一个事务，提交一个事务，比较消耗资源

  • 因此我们可以手动提交事务，在执行插入操作前，开启事务，当所有insert语句执行之后，再手动提交事务

    

• 主键顺序插入

  • 按照主键顺序插入，性能高于 乱序插入，原因与mysql数据组织结构有关，后续讲解。

    

2、大批量插入

• 如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令插入。

• load指令的使用方式：

  # 客户端连接服务端时，加上参数 --local-infile（这一行在bash/cmd界面输入）
  mysql --local-infile -u root -p
  
  # 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
  set global local_infile = 1;
  select @@local_infile;
  
  # 执行load指令将准备好的数据，加载到表结构中
  load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';
  

2.3.2.主键优化

1、InnoDB存储引擎 的 数据组织方式

• 在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，如下图：数据行Row都是存在于聚集索引中

  

• 这种存储方式的表称为 索引组织表（Index organized table, IOT）

• 在聚集索引中，叶子结点中保存的id，是按照顺序排列的。

2、页分裂

• 首先回忆一下 InnoDB存储引擎 的 逻辑存储结构

  • 索引在磁盘中的最小管理单位是 页Page，即这么一块：
    

  • 一个Page中，能存储很多 行数据，即这么一块：
    

  • 如果一行数据太大，大于Page，就会 行溢出。

  • 我们在向OS申请磁盘块的时候，以Page为单位申请。

• 主键顺序插入

  • 如果用户插入数据时，数据的主键是有序的，那么按照id的顺序插入，可以存满一个块，再申请下一个块
  • 每次数据都是插入到最后，不存在插入到某页的中间位置，就不会造成页的分裂情况

• 主键乱序插入

  • 如果用户插入数据时，数据的主键是乱序的，那么按照数据的顺序插入，就可能出现插入某个数据到页中部位置的情况
  • 如果该页已经存满了，那么再插入数据到它内部，这一页就存不下了，就需要申请一个新的页，将数据从中间位置分成两部分，分别存入两个页中，然后改变双向链表的指向，这就是 页分裂

3、页合并

• 当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除，并且它的空间变得允许被其他记录声明使用。

• 当某一页中，删除的记录到达阈值 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前后），看看是否可以将这两个页合并成一页，以优化空间使用。

• MERGE_THRESHOLD：判断是否需要合并页的阈值，可以自己设置，在 创建表 或 创建索引 时指定。

4、主键设计原则

• 满足业务需求的情况下，尽量降低主键的长度

  • 一般，辅助索引（二级索引）会比较多，每个二级索引的叶子结点中存储的都是 主键id值，如果主键很长，就会占用大量的空间

• 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键

  • 这是为了 减少 页分裂 的发生，提高效率和空间使用率

• 尽量不要使用 UUID 做主键或者是其他的自然主键，如身份证号

  • 一般UUID、身份证号都不是有序的，以它们为主键，则属于 主键乱序插入，页分裂的情况可能比较多
  • UUID一般很长，不适合做主键

• 业务操作时，避免对主键的修改

  • 修改主键也会造成索引的修改，维护不方便，且影响效率

2.3.3.order by优化

1、排序的两种方式

• Using index

  • 通过有序索引，进行顺序扫描，然后直接返回获取到的有序数据，这种情况即为 using index，不需要额外排序，因为拿到的数据就是有序的，操作效率高

• Using filesort

  • 通过表的索引或全表扫描，读取满足条件的数据行，因为拿到的数据不能满足或不完全满足要求，需要在 排序缓冲区 sort buffer 中完成排序操作，然后再返回数据
  • 所有不是通过索引直接返回排序结果的排序，都叫 FileSort 排序

• 会在 explain查看sql语句的执行计划中，extra字段表明当前sql语句用到的排序方式

  

2、order by 字段 配合索引使用

• order by关键字，默认是按照升序排列

  • 如果order by后面跟着一个字段，就按照这个字段值进行排列
  • 如果order by后面跟着多个字段，那么就会首先按照前面的字段排序，前面的字段值相同，再按后面的字段值排序
  • 其中 asc为升序（默认）、desc为降序

• 创建索引时，建立B+树的排序规则

  • 默认是按照字段值升序排列，联合索引，会先按前面字段升序，前面字段相同，再按后面字段升序

    • 举例：先创建一个索引，包含age、phone列，建立B+树时默认先按照age递增，age相同再按照phone递增

    • create index idx_user_age_phone_ad on tb_user(age, phone);

  • 也可以手动指定排序规则：联合索引中部分字段想要按照降序排列，可以手动指定

    • 举例：创建一个联合索引，包含age、phone列，要求先按age升序排序，age相同时，再按phone降序排列
    • create index idx_user_age_phone_ad on tb_user(age asc, phone desc);

• 使用 order by 时，会根据order by的要求，去寻找合适的索引使用

  • 如果找到了就可以用 Using index 的方式返回数据，性能高
  • 如果找不到，就需要把数据读到 排序缓冲区，按照要求排序后再返回，性能较低

• 举例：

  • 比如我们已经事先创建好了两个索引：
    • age、phone都是升序：create index idx_user_age_phone on tb_user(age, phone);
    • age升序、phone降序：create index idx_user_age_phone_ad on tb_user(age asc, phone desc);
  • 例1：执行sql语句
    • select id, age, phone from tb_user order by age, phone;
    • 会使用 索引 idx_user_age_phone，因为索引中数据的顺序本身就都是递增的，所以排序方式为Using index
  • 例2：执行sql语句：
    • select id, age, phone from tb_user order by age desc, phone desc;
    • 会使用 索引 idx_user_age_phone，因为索引中数据的顺序本身就都是递增的，所以只需要 反向 遍历即可获取需要的数据，不需要额外排序，排序方式为Using index
  • 例3：执行sql语句
    • select id, age, phone from tb_user order by phone, age;
    • 会使用 索引 idx_user_age_phone，但是因为索引最左侧列为age，但是这里使用order by先按phone排序，所以违背了最左前缀法则，排序方式为Using index + Using filesort
  • 例4：执行sql语句：
    • select id, age, phone from tb_user order by age asc, phone desc;
    • 会使用 索引 idx_user_age_phone_ad，因为索引中数据的顺序本身就是先按age递增，age相同的按phone递减，所以排序方式为Using index

3、排序缓冲区大小

• 排序缓冲区大小 默认是 256K，可以使用命令查看

  

• 如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size

• 如果 排序缓冲区大小 不够用了，数据量比较大，那么就会使用磁盘空间 进行缓冲，性能就低了

4、总结

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
• 尽量使用覆盖索引
• 多字段排序，一个升序一个降序，此时需要注意 联合索引 在创建时 的规则（ASC/DESC）
• 如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）

2.3.4.group by优化

• 在分组操作时，可以通过索引来提高效率
• 分组操作时，索引的使用也是满足最左前缀法则的

如索引为idx_user_pro_age_stat，则句式可以是select ... where profession order by age，这样也符合最左前缀法则

2.3.5.limit优化

• 在大数据量的情况下，进行分页，页数越往后，查询耗时越久。如：

  • 表中有一千万条记录，查询第一页，一页10条记录，limit 0, 10，速度0.00s
  • 查询第2页，limit 10, 10，从第10条开始查，查询10条，速度0.00s
  • 查询非常大的页数，limit 1000000,10，从第一百万条开始查，查询10条，耗时10s多
  • 查询非常大的页数，limit 9000000,10，从第九百万条开始查，查询10条，耗时19s多

• 问题原因：

  • 如 limit 2000000,10，此时MySQL需要排序 前2000010条记录，但是仅仅返回2000000 -2000010的记录，其他记录丢弃，查询排序的代价非常大。

• 优化方案：

  • 一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过 覆盖索引加子查询 形式进行优化。

  • 举例：

    • select * from tb_sku limit 9000000,10; 直接从第9000000条数据开始，查询10条，耗时19s

    • 优化：

      • 我们可以先按照id排序，拿到第 9000000~9000010 条数据的id，然后再根据id查找数据（子查询）

      • 拆分成两条sql语句：

        select id from tb_sku order by id limit 9000000,10; # 拿到欲获取的10条数据id
        select * from tb_sku where id=#{id} # 根据id查询行数据
        

      • 两条sql语句可以合并成一条：

        # 把事先获取到的10条id，作为一个表
        select s.* from tb_sku s, (select id from tb_sku order by id limit 9000000, 10) a where s.id = a.id;
        

      • 经过测试，优化后，sql语句耗时11s

例如：

-- 此语句耗时很长
select * from tb_sku limit 9000000, 10;
-- 通过覆盖索引加快速度，直接通过主键索引进行排序及查询
select id from tb_sku order by id limit 9000000, 10;
-- 下面的语句是错误的，因为 MySQL 不支持 in 里面使用 limit
-- select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);
-- 通过连表查询即可实现第一句的效果，并且能达到第二句的速度
select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;

2.3.6.count优化

• InnoDB 和 MyISAM 对 count(*) 的处理方式不同

  • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高（前提是不使用where）；

  • InnoDB 在执行 count(*) 时，需要把数据一行一行地从引擎里面读出来，然后累计计数。

• 优化方案：

  • 没有特别好的策略，只能自己计数
  • 如 创建一个key-value结构的表，存储在内存或硬盘，里面维护各种表的计数，当插入或删除一行时，对这个key-value数据结构进行维护，+1或-1，比如使用redis

• count的几种用法：

  • count()是一个聚合函数，参数是要统计的字段名。

  • count函数会取出每一行数据，判断参数字段是否为NULL，不为NULL则计数+1，为NULL则不加

  • 用法：count(*)、count(主键)、count(字段)、count(1)

    • count(主键)跟count(*)一样，因为主键不能为空；
    • count(字段)只计算字段值不为NULL的行；
    • count(1)，InnoDB 引擎会为每行添加一个1，然后就count这个1，返回结果也跟count(*)一样；当然这里放任何值的效果都是一样的
    • count(null)返回0，因为null不会计数

• 各种用法的性能：

  • count(主键)：

    • InnoDB引擎会遍历整张表，把每行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为空）

  • count(字段)：

    • 没有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加；
    • 有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加

  • count(1)：

    • InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一层，放一个数字 1 进去，直接按行进行累加

  • count(*)：

    • InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

  按效率由高到低排序：count(*) ≈ count(1) > count(主键) > count(字段)，所以尽量使用 count(*)

  原因：

  • count(*)不取值，直接累加；
  • count(1)不取值，放入1直接累加；
  • count(主键)取主键值，直接累加；
  • count(字段)取字段值，还要判断

  null值计算：

  • count(列名) 是不会把 null 值计算出来的
  • count(*) , count(1) 等方式是会计算null的
  • count(主键)，主键不可能为null

2.3.7.update优化（避免行锁升级为表锁）

• InnoDB 的 行锁与表锁

  • InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效
  • 查询条件没有索引，或者索引失效时，就会从行锁升级为表锁，我们应该避免这种情况。

• 案例：

  • 执行这条sql语句，只会锁住id=1的那一行，因为where条件后的 id 有索引，行锁可以加到聚集索引上

    

  • 执行这条sql语句，会锁住整张表，因为where条件后的name没有索引，行锁无处可加，只能升级为表锁

    

• 如果加的是行锁，那么在并发下，在本sql语句提交前 ，除了这一行，本张表的其他行 都可以访问

• 如果加的是表锁，那么在并发下 ，在本sql语句提交前，本张表不可访问，并发能力下降

• 优化方案：

  • 添加合适的索引，保证where后的条件有索引，这样就会加行锁，避免升级为表锁

2.3.8.SQL优化总结

2.4.视图/存储过程/触发器

• 本章内容

  • 视图
  • 存储过程
  • 存储函数
  • 触发器

  统称为Mysql中的存储对象

2.4.1.视图

• 视图（View）是一种虚拟存在的表。
• 视图中的数据并不在数据库中实际存在，行和列数据来自 定义视图的sql语句查询到的表，视图的表，是在使用视图时动态生成的。
• 视图只保存了查询的SQL逻辑，不保存查询结果。即保存了构建视图的sql语句，用到视图时，直接执行sql语句，查询结果建成的表 即为视图
• 所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

1、创建视图

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT 语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

-- [OR REPLACE]:在替换某个视图的时候使用
-- 

• 例： create or replace view stu_wll as select id, name from student where id<=10;
  • 其中，视图是从 student 表中创建出来的，student可以称为 基表

2、查询视图

• 查看某个视图被创建时执行的sql语句： SHOW CREATE VIEW 视图名称;

• 查看视图中的数据：SELECT * FROM 视图名称;

  • 就把视图当成一张表，像操作表一样操作视图就行
  • 如果查询条件，直接加在后边就行，就像操作表一样

3、修改视图

• 方式一：替换视图
  • CREATE[OR REPLACE] VIEW 视图名称[（列名列表)）] AS SELECT 语句[ WITH[ CASCADED | LOCAL ] CHECK OPTION ]
• 方式二：修改视图
  • ALTER VIEW 视图名称 [（列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]

4、删除视图

• DROP VIEW [IF EXISTS] 视图名称 [视图名称]

5、视图检查选项

1、问题描述

• 既然我们可以把视图当做一张表，自然就可以往里面插入数据。可视图本身不存储数据，因此直接插入数据会插入视图对应的 基表 中去。

• 举例：注意这个视图的sql语句中，查询条件为id <= 20

  

  

• 注意这个视图的sql语句中，查询条件为id <= 20，那么如果我插入一个id > 20的数据，能插入成功吗？

  • 答：可以插入成功，但是再查询视图数据的时候，发现查不出来，因为给过滤掉了。

• 为了避免这种，在当前视图插入的数据，当前视图反而查不出来的问题，创建视图时提供了几个选项

  • 在sql语句最后，加上 with cascaded check option 或者 with local check option
  • 保证在插入数据到视图时，先进行检查，检查通过才会插入。
  • 由此产生了视图检查选项

2、视图检查选项

• 当使用 WITH CHECK QPTION 子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。

• MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。

• 为了确定检查的范围，mysql提供了两个选项：CASCADED 和 LOCAL ，。

  • 直接写 WITH CHECK QPTION，默认值为 CASCADED

• NOTE：如果没有开检查选项就不会进行检查。不同版本是不同含义的，要看版本。

3、CASCADED

• 级联，一旦选择了这个选项，除了会检查创建视图时候的条件，还会检查当前视图所依赖的所有视图的条件是否满足

• 举例：

  • 创建stu_V_l 视图，id是小于等于 20的。

    • create or replace view stu_V_l as select id,name from student where id <=20;

  • 再创建 stu_v_2 视图，20 >= id >=10。

    • create or replace view stu_v_2 as select id,name from stu_v_1 where id >=10 with cascaded check option;

  • 再创建 stu_v_3 视图。

    • create or replace view stu_v_3 as select id,name from stu_v_2 where id<=15;

  • 这条数据能够插入成功，因为 stu_v_3 创建的时候没有加检查选项，所以 插入数据时 不会 去判断 id 是否小于等于15，而是直接检查 是否满足 基表 stu_v_2 的条件，满足就直接插入stu_v_2。

    • insert into stu_v_3 values(17,'Tom');

  • 而对于 stu_v_2 视图，创建的时候，sql语句依赖于stu_v_1，而且加上了 CASCADED 参数。

    • 所以如果往stu_v_2 视图中插入数据，会检查 id 是否 >=10，而且还会检查它所依赖的 stu_v_1 的 id <=20 是否满足，即相当于在stu_v_1 上也加上了 with cascaded check option

    

4、LOCAL

• 本地的条件也会检查，还会向上检查。
• 在向上找的时候，就要看是否上面开了检查选项，如果没开就不检查。
• 和 CASCADED 的区别就是 CASCADED 不管上面开没开检查选项都会进行检查。

6、更新及作用

• 要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新

  • 聚合函数或窗口函数 ( SUM()、MIN()、MAX()、COUNT() 等 )

  • DISTINCT

  • GROUP BY

  • HAVING

  • UNION 或者UNION ALL

例子： 使用了聚合函数，插入会失败。
create view stu_v_count as select count(*) from student;
insert into stu_v_count values(10);

• 作用

  • 视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

  • 安全

    • 数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据

  • 数据独立

    • 视图可帮助用户屏蔽真实表结构变化带来的影响。

  • 总而言之，视图类似于给表加上了一个外壳，通过这个外壳访问表的时候，只能按照所设计的方式进行访问与更新。

2.4.2.存储过程

1、什么是存储过程

• 存储过程是事先经过编译并存储在数据库中的一段SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

• 存储过程思想上很简单，就是数据库SQL 语言层面的代码封装与重用。

• 特点

  • 封装

  • 复用

  • 可以接收参数，也可以返回数据减少网络交互，效率提升

2、创建存储过程

CREATE PROCEDURE 存储过程名称( [参数列表] ) 
BEGIN
	 SQL 语句 
END;

• NOTE
  • 在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。默认是 分号作为结束符。
• delimiter $ ，则 $ 符作为结束符。

3、调用存储过程

• CALL 名称 ( [参数])

4、查看存储过程

• 查询指定数据库的存储过程及状态信息

  • SELECT* FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'

• 存储过程名称；–查询某个存储过程的定义

  • SHOW CREATE PROCEDURE

5、删除存储过程

• DROP PROCEDURE [ IFEXISTS ] 存储过程名称

6、存储过程的游标

游标（CURSOR）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE，其语法分别如下。

声明游标：
DECLARE 游标名称 CURSOR FOR 查询语句

打开游标：
OPEN 游标名称

获取游标记录：
FETCH 游标名称INTO变量[变量]

条件处理程序：
条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

DECLARE handler action HANDLER FOR condition value L condition value]…statement

handler_action CONTINUE：继续执行当前程序

EXIT：终止执行当前程序

condition_value :

SQLSTATE sqlstate_value：状态码，如02000

SQLWARNING：所有以01开头的SQLSTATE代码的简写

NOT FOUND：所有以02开头的SQLSTATE代码的简写

SQLEXCEPTION：所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

例子：

NOTE：要先声明普通变量，再申请游标。

要求：
根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表（id，name，profession）中。

	create procedure p1l(in uage int)
	
		begin
	
			declare uname varchar(100); 
	
			decLare upro varchar(100);
	
			declare u_cursor cursor for select name,profession from tb_user where age <= uage; 
	
			当 条件处理程序的处理的状态码为02000的时候，就会退出。
			declare exit handler for SQLSTATE '02000'close u_cursor;
	
			drop table if exists tb_user_pro; 
	
			create table if not exists tb_user_pro(
	
			id int primary key auto_increment, 
	
			name varchar(100), 
	
			profession varchar(100)
	
			);
	
			open u_cursor; 
	
			while true do 
	
			fetch u_cursor into uname,Upro; 
	
			insert into tb_user_pro values(null,uname,Upro); 
	
			end while;
	
			close u_cursor; 
	
		end;

2.4.3.触发器

1、介绍

• 触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。
• 触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
• 使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。
• 现在触发器还只支持行级触发（比如说 一条语句影响了 5 行 则会被触发 5 次），不支持语句级触发（比如说 一条语句影响了 5 行 则会被触发 1 次）。

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

2.5.锁

2.5.1.什么是锁

• 锁是计算机协调多个进程或线程并发访问某一资源的机制。

• 在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源

• 如何 保证数据并发访问的一致性、有效性 是所有数据库必须解决的一个问题，锁冲突 也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

• 【注】：针对事务才有加锁的意义。

2.5.2.MySQL中锁的分类

• MySQL中的锁，按照锁的粒度分，分为以下三类：

  • 全局锁：锁定数据库中的所有表。

  • 表级锁：每次操作锁住整张表。

  • 行级锁：每次操作锁住对应的行数据。

1、全局锁

（1）全局锁是什么

• 全局锁就是对 整个数据库实例（整个Mysql服务中的所有数据库） 加锁，加锁后 整个Mysql服务的所有数据库的所有表 都处于只读状态，后续的DML的写语句，DDL语句，以及更新操作的事务提交语句都将被阻塞。

（2）应用场景：全库的逻辑备份

• 其典型的使用场景是做 全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

• 为什么 全库的逻辑备份 需要加全局锁？

  • 存在这种场景：数据库备份业务、用户下单业务 同步执行
  • 如果我们已经备份了库存数据，又有用户下单扣减了库存，则 备份数据、数据库后来的数据 不一致
  • 但如果后续用户订单业务执行的快，订单表、订单日志表中都插入了本次下单的数据，那么 备份数据 中就会包含本次下单的订单信息
  • 然而：库存是下单前的、订单表和订单日志却是下单后的，就出现了数据的不一致

  

  • 如果加了全局锁呢？

    

（3）全局锁的sql语法

• 加锁

  

• 执行全库逻辑备份

  • 最后的文件名，其实是要备份到的文件路径

  

• 解锁

  

（4）全局锁的问题

2、表级锁

（1）什么是表级锁

• 表级锁，每次操作 锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。在MyISAM、InnoDB、BDB等存储引擎中都支持表级锁。

（2）表级锁的分类

• 表锁
  • 表共享读锁 （读锁，read lock）
  • 表独占写锁 （写锁，write lock）
• 元数据锁：meta data lock，MDL
  • 元数据 共享读锁
  • 元数据 排他写锁
• 意向锁：Intention lock
  • 意向共享锁（Intention Shared lock，IS）
  • 意向排它锁（Intention Exclusive lock，IX）

（3）表锁

• 表锁。又分成两类：

  • 表共享 读锁（read lock），所有的客户端都只能读，不能写，包括当前加锁的客户端，自己和别人都是只能读、不能写

    

    

  • 表独占 写锁（write lock），当前加锁的客户端，可读可写；而其他的客户端，不可读也不可写。

    

• 读写锁的区别：

  • 读锁不会阻塞 所有客户端 的读，但是会阻塞 所有客户端 的写。
  • 写锁会阻塞其他客户端的 所有读写操作

• 语法：加锁与解锁

（4）元数据锁（meta data lock，MDL）

• 什么是元数据？

  • 元数据，即 表结构。
  • 对元数据加锁，即 对表结构加锁，不能随意的改变表的结构。

• 元数据锁的作用

  • 维护表结构的数据一致性，即 当表上存在活动的事务时，不可以对表结构进行修改。

• MDL加锁过程是 系统自动控制，无需显式使用，在访问一张表的时候会自动加上。

• 在MySQL5.5中引入了 MDL

  • 当对一张表进行 增删改查 的时候，加MDL读锁（共享），可以读取表结构，但不能修改。

  • 当对 表结构进行 变更操作 的时候，加MDL写锁（排他），在修改结束前，读/写表结构都不行。

  • 下面这张表，看不懂就跳过吧

    

（5）意向锁

• 为什么要用意向锁？

  • 存在这种问题：

    • 有一个user表，里面有很多行数据。

    • 现在 线程A 来对 id=25 的 行进行DML语句操作，已经 对第25行加上行锁。

    • 此时，线程B又来操作表，想要对表的相关信息进行修改，需要加表锁。

      • 可第25行已经加上了行锁，再加表锁的话就会发生冲突，如何解决？
        • 方法一：逐行扫描
          • 在 线程B 加表锁 之前，对整张表逐行扫描，判断是否有某一行加上了行锁。
          • 如果有，则当前线程加表锁会阻塞等待；如果没有，才会加表锁。
        • 方法二：提出了意向锁
          • 在 线程A 加行锁时，同时对整张表加上一个 意向锁。
          • 当 线程B 加表锁时，直接判断 表是否被加了意向锁，如果是，则代表有某一/多行加了行锁，表锁加锁失败，不会发生冲突。

• 意向锁的作用：

  • 为了避免DML语句在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少 加表锁时的检查。

  • 一个客户端对某一行加上了行锁，那么系统也会对其加上一个意向锁，当别的客户端来想要对其加上表锁时，便会检查意向锁是否兼容，若是不兼容，便会阻塞直到意向锁释放。

  • 意向锁的分类：

    • 意向共享锁（IS）：即意向读锁，不会阻塞其他线程获取意向读锁的操作，但会阻塞获取意向写锁的操作。与表锁共享锁（read）兼容，与表锁排它锁（write）互斥。
    • 意向排他锁（lX）：即意向写锁，会阻塞其他线程获取意向读锁、写锁的操作。与表锁共享锁（read）及排它锁（write）都互斥。意向锁之间不会互斥。

  

3、行级锁

（1）什么是行级锁

• 行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
• InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

（2）行级锁的分类

• 分类：
  • 行锁（Record Lock）
  • 间隙锁（GapLock）
  • 临键锁（Next-Key Lock）

（3）行锁（Record Lock）

• 锁定单个行记录的锁，防止其他事务对此行进行update和delete。

  

• 在RC（read commit ）、RR（repeat read）隔离级别下都支持。

• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

• 注：InnoDB的行锁是针对于索引加的锁，如果不使用索引检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

（4）间隙锁（GapLock）

• 锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。

• 在RR（repeat read）隔离级别下支持。

  • 比如说 两个临近叶子节点为 15 23，那么间隙就是指 [15 , 23]，锁的是这个间隙。

• 间隙锁唯一目的：防止其他事务插入间隙。

• 间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

  

（5）临键锁（Next-Key Lock）

• 行锁和间隙锁组合，会同时锁住行数据、该行之前的所有间隙Gap。

• 在RR（repeat read）隔离级别下支持。

  

（6）InnoDB的行锁

• InnoDB实现了以下两种类型的行锁：

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。

    • 简单地说，就是一个事务获取到了某行的共享锁，其他事务依旧可以获取共享锁，但不能获取排它锁

  • 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

    • 简单地说，就是一个事务获取到了某行的排他锁，其他事务就不能获取共享锁和排它锁了

• 下表列举了不同操作加行锁的类型：

  SQL	行锁类型	说明
  insert	排他锁	自动加锁
  update	排他锁	自动加锁
  delete	排他锁	自动加锁
  select	不加任何锁
  select … lock in share mode	排他锁	需要手动在SELECT之后加LOCK IN SHARE MODE
  select … for update	排他锁	需要手动在SELECT之后加FOR UPDATE

• 行锁 - 演示

  • 默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key 临键锁进行搜索和索引扫描，以防止幻读。

（7）间隙锁/临键锁演示

• 默认情况下，InnoDB 是在 REPEATABLE READ 可重复读事务隔离级别下 运行，InnoDB使用next-key 临键锁进行搜索和索引扫描，以防止幻读。

  • 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。

  • 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock 临键锁退化为间隙锁。

  • 索引上的范围查询（唯一索引）-- 会访问到不满足条件的第一个值为止。

2.5.3.锁的总结

• 总结

  • 增删改自动加互斥锁
  • 查的时候后面加 lock in share mode 的话就加共享锁，后面加for update的话就加互斥锁

• 全局锁：

  • flush tables with read lock
  • unlock tables

• 表级锁

  • lock tables 表名 read/write
  • unlock tables

• 行级锁

  • 排它锁：select ... for update
  • 共享锁：select ... for share

2.6.InnoDB 引擎（TODO）