MYSQL进阶笔记

---

此文章仅为学习笔记，并在不断学习更新中，如有错误，欢迎指出
学习视频为黑马 MYSQL教程-进阶篇，如有侵权，联系我删除

视频传送门

mysql使用的版本为8 linux版，安装以及资源包可以参考我的另一篇文章
mysql 8.0.26-linux版本安装

---

1. MYSQL体系结构

• 连接层
  • 最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它具有的操作权限
• 服务层
  • 第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如：过程、函数等
• 引擎层
  • 存储引擎真正的负责了MYSQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎
  • 注意：index（索引位于引擎层），所以不同的引擎，索引结构不一样
• 存储层
  • 主要是将数据存储在文件系统之上，并完成与存储引擎的交互

2. 存储引擎

存储引擎是mysql核心部分，是mysql特有的，是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可以被称为表类型

在建表的时候没有指定存储引擎，也会分发给一个默认的存储引擎InnoDB(mysql 5.5默认指定 InnoDB引擎)

可以通过sql语句 show create table xxx 来查看建表语句，其中有一条语句 ENGINE=InnoDB指定了默认的存储引擎

show CREATE table tbl_book

--查询到的结果
CREATE TABLE `tbl_book` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC

存储引擎的相关操作

• 在创建表时，指定存储引擎

  • CREATE TABLE 表名(
    	字段  字段类型
    )ENGINE =  INNODB
    

• 查看当前数据库支持的存储引擎

  • SHOW ENGINES
    

  • 六个字段分别为：存储引擎 是否支持 注释 事务 XA协议 保存点

  • 

案例

-- 创建表  my_myisam, 并指定MyISAM存储引擎
CREATE TABLE my_myisam(
			id INT,
			name VARCHAR(10)
)ENGINE = MyISAM
-- 创建表  my_memory, 并指定Memory存储引擎

CREATE TABLE my_memory(
			id INT,
			name VARCHAR(10)
)ENGINE = Memory

2.1 InnoDB存储引擎介绍

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎

特点：

• DML（数据的增删改）操作遵循ACID（事务四大特性）模型，支持事务；
• 行级锁，提高并发访问性能
• 支持外键FOREIGN KEY约束，保证数据的完整性和正确性

InnoDB所涉及的磁盘文件

• xxx.ibd：xxx代表的是表名，InnodDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

  • 表结构文件，早期存储在frm，在8.0之后，表结构都存储在sdi数据字典中，而sdi融入到ibd表空间文件中

  • 参数：innodb_file_per_table，决定到底是多张表共用一个共享表空间，还是每个表都对应一个表空间文件

    • mysql8.0，参数开关是打开的，那么也就是说，每张表都对应于一个表空间文件，可以通过指令来查看，SHOW VARIABLES LIKE ’ ’

    • -- 查看参数状态
      SHOW VARIABLES LIKE 'innodb_file_per_table'
      -- 查看数据库文件存储位置
      SHOW VARIABLES LIKE 'datadir'
      

    • 

    • 找到数据库存储文件后，发现是ibd格式，不能直接打开，可以借助命令 ibd2sdi xxx.ibd来查看具体内容

逻辑存储结构

• 
  
  
  
  

2.2 MyISAM存储引擎介绍

MyISAM是MySQL早期的默认存储引擎

特点

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 访问速度快

MyISAM所涉及的磁盘文件

• xxx.sdi：存储表结构信息
• xxx.MYD：存储数据
• xxx.MYI：存储索引

  • 

  • 里面的sdi文件，数字是递增的，内容格式是json格式，可以直接打开然后进行json格式的转化

2.3 Memory存储引擎介绍

Memory引擎的表数据存储在内存中，由于受到硬件问题或断电问题的影响，只能将这些表作为临时表或缓存使用

特点

• 内存存放
• hash索引（默认）

Memory所涉及的磁盘文件

• xxx.sdi：存储表结构信息

2.4 三种存储引擎的区别

2.5 存储引擎选择

存储引擎并没有好坏之分，只关心是否能够满足业务需求，在选择存储引擎时，应该根据业务的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合

适用场景

• InnoDB：mysql默认存储引擎，支持事务、外键，如果对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB比较合适
• MyISAM：如果是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么MyISAM比较合适
  • 在日常开发中使用很少，多使用MongoDB（nosql）代替
• Memory：将所有数据保存到内存中，访问速度快，适用于临时表及缓存。
  • 缺陷：对表的大小有限制，太大的表无法和缓存在内存中，而且无法保障数据的安全性
  • 在日常开发中使用很少，多使用redis（nosql）代替

3. 索引

3.1 概述

索引（index）是帮助MYSQL高效获取数据的数据结构，通过索引能够实现快速查找数据的功能，常见有哈希索引、b+树索引等。在数据之外，数据库系统维护满足特定查找算法的数据结构，这些数据结构一某种方式引用数据（在内存中，索引是通过指针的形式存在），这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引
可以参考我的另一篇文章，对数据库索引有较简短的介绍

3.2 索引结构

MYSQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要的索引有以下四种：

3.2.1 各个存储引擎对索引结构的支持情况

3.2.2 B+ Tree

在之前的文章已经介绍过索引，b树还有b+树，作为最常用的b+ tree索引，mysql的b+树和传统的b+树有所不同，mysql做了优化，在相邻节点也增加了链表指针

经典二叉树

mysql进行优化后的二叉树

3.2.3 Hash

哈希索引就是采用一定的Hash算法，将键值换算成新的Hash值，映射到对应的槽位上，然后存储在Hash表中

特点

• Hash索引只用由于对等比较（=，in），不支持范围查询（between，>，<，…）
• 无法利用索引完成排序操作
• 查询效率高，通常只需要一次检索就可以了（即在不出现Hash碰撞的情况下，否则还需要从链表去查找数据），效率通常要高于B+tree索引

存储引擎支持

在MYSQL中，支持Hash索引的是Memory引擎（唯一支持），而InnoDB中具有自适应Hash功能，Hash索引是存储引擎根据B+ Tree索引在指定条件下自动构建的

3.3 索引分类

索引通常会分为以下四类，其实可以发现，主键、唯一索引的关键字和之前的约束一致，也就是说在创建唯一/主键约束的时候，同时也会产生索引

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

3.3.1 聚集索引选取规则

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

3.3.2 聚集索引和二级索引示意图

聚集索引叶子节点，存储的是一行的数据，而根据name生成的二级索引，叶子结点就不再试一整行的数据，那样数据库的数据存储特别冗余，因此只有id

假如有条sql为

select * from user where name = 'Arm';

那么首先会从二级索引进行查找，查找到Arm对应的id为10，而此时所需要查询的是 * ，那么会继续往聚集索引进行查找，找到id为10的这一行的所有数据，这个过程叫做回表查询

3.3.3 回表查询

回表查询：先从二级索引找到对应的主键值，再从聚集索引去找到当前主键所对应的行数据

3.3.4 InnoDB主键索引的B+ tree高度

假设：一行数据大小为1K，一页中可以存储16行这样的数据（固定大小16K）。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8（主键占用字节大小根据主键类型来定，普通int类型占用4个字节）

高度为2：

​ nx8+（n+1)x6 = 16*1024 n约为1170

​ 1171 * 16 = 18736

高度为3：

​ 1171x1171x16 = 21939856

3.4 索引语法

3.4.1 创建索引

可选择索引的类型，如果选择 UNIQUE，创建的是唯一索引，如果选择的是FULLTEXT，创建的是全文索引，如果都不选择，创建的是常规索引，并且一个索引可以关联多个字段：

• 如果一个索引只关联一个字段，称为单列索引
• 如果一个索引关联多个字段，称为联合索引/组合索引

CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 ON 表名(字段名...);

3.4.2 查看索引

SHOW INDEX FROM 表名;

3.4.3 删除索引

DROP INDEX 索引名称 ON 表名;

3.4.4 案例

需求：

• name字段为姓名字段，该字段的值可能会重复，为该字段创建索引
• phone字段为手机号字段，是非空且唯一的，为该字段创建唯一索引
• 为profession、age、status创建联合索引
• 为email简历合适的索引来提升查询效率

索引名称的一般规范为：idx _ 表名 _字段

--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_sta on tb_user(profession,age,status);

--为email简历合适的索引来提升查询效率
create index idx_user_email on tb_user(email);

创建成功后的索引截图：

3.5 性能分析

SQL优化主要是优化查询语句，性能分析也就是为了SQL优化做准备

3.5.1 SQL执行频率

通过SQL执行频率来确定，当前数据增删改查比例，从而确定对应优化方案，如果增、删、改为主，优化程度可以放轻

MYSQL客户端连接成功后，通过show [session|global] status 命令可以提供服务器（当前会话/全局）状态信息，通过如下指令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT的访问频次```

SHOW GLOBAL STATUS LIKE 'Com_______'

每个下划线代表一个字符，一般有7根下划线，因为名称后面有下划线加6个字符，比如查询： Com_select

如果查询占据了大部分，那么就需要针对这类的数据库进行sql优化

3.5.2 慢查询日志

SQL执行频率只能判断当前数据库哪种操作占比高，但是如果要定位某条需要优化的sql语句，那么就需要借助慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志

MYSQL的慢查询日志默认没有开启，可以通过sql语句来查看是否打开日志 show variables like ‘slow_query_log’;

需要在MYSQL的配置文件（/etc/my.cnf）中配置如下信息：

#慢查询日志
slow_query_log=1
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MYSQL服务器进行测试

systemctl restart mysqld

查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log

在实践过程中，可以通过命令 tail -f localhost-slow.log 实时看到日志文件追加的内容

linux中，mysql加载上千万数据的方法：

tb_sku表中存在1千万数据，分为5个文件进行导入

--登录mysql，后缀保证能够加载本地文件
mysql -u root -p --local-infile

--保证能使用Loading local data，但是是一次性的方法，下次使用需要重设
set global local_infile=1;

--切换数据库
use itcast

--加载数据
load data local infile '文件路径' into table `表名` fields terminated by '分隔符' lines terminated by '\n';

--例：通过pwd找到sql文件路径，数据需要导入到tb_sku表，数据分隔符是，存在换行
load data local infile '/home/hexie/sql/tb_sku2.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

3.5.3 show profiles

慢查询只能记录超过指定时间的SQL指令，之前预设时间是2秒，那么只会记录超过2秒的指令，但是假如一个业务简单，但是他的查询时间却到了1.9s，那这也是个比较低效的SQL

show profiles能够在做SQL优化时帮助我们了解时间的耗费。通过have_profiling参数，能够看到当前MYSQL是否支持profile操作：

select @@have_profiling;

可以看到当前数据库支持profiling，但数据库默认关闭profiling，可以通过set语句在session/global级别开启

--查看profiling是否开启
select @@profiling;

--设置profiling
SET profiling = 1;

通过如下指令查看指令的执行耗时：

#查看每一条SQL的耗时基本情况
show profiles;

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

#查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

show profiles查到的耗时情况

查询query_id为5的sql语句各个阶段的耗时情况

查询cpu的使用情况

3.5.4 explain
以上三种都是通过时间来对SQL的性能进行判断，但是比较粗略。

explain是比较常用的手段，EXPLAIN或者DESC命令获取MYSQL如何执行SELECT语句的信息，包括SELECT语句执行过程中表如何连接和链接的顺序

语法：

#直接在查询语句前面加上explain或者desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

常用的是explain，desc的执行效果和explain一样

explain展示的各个字段的含义：

• id

  • 表示SQL语句或者是表的执行顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）

• select_tyoe

  • 只表示SELECT类型，了解即可，常见的取值有SIMPLE（简单表，即不适用表连接或者子查询、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含子查询）等）

• type

  • 表示访问/连接类型，性能由好刀叉的连接类型为 NULL、system、const、eq_ref、ref、range、index、all
  • 在优化的时候，尽量往前优化，但是NULL级别基本不会出现在业务中，只有当不访问任何表，如 select ‘A’，这样的sql语句才能达到NULL级别
  • 一般在访问系统表的时候会出现system级别
  • 一般是根据主键或者唯一性索引进行查询，如where id = 1 这样的sql会出现const级别
  • 使用非唯一性索引进行查询时，会出现ref

• possible_keys

  • 显示这张表中可能用到的索引，一个或多个

• key

  • 实际用到的索引，如果为NULL，表示没有使用索引

• key_len

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

• rows

  • MYSQL认为必须要执行查询的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的

• filtered

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

3.6 索引使用

创建了索引，那么它是否能真正发挥作用，对查询效率有所提升呢？索引的使用就显得很重要了

在介绍索引的使用之前，需要先了解索引效率

3.6.1 验证索引效率

在未建立索引之前，执行如下SQL语句，查看SQL的耗时

SELECT * FROM tb_sku WHERE sn = '100000003145001'

此时发现，耗时很久，因为sku表并没有针对sn字段简历索引

针对字段创建索引,但是此时会发现耗时特别长，因为索引是一种数据结构，创建索引也就是去构建这种数据结构，针对10000000w条数据去构建索引，耗时很久

create index idx_sku_sn on tb_sku(sn);

创建了索引再去执行相同的sql，第一次执行的耗时为20多秒，添加了索引就只为0.00秒，查找效率特别高

3.6.2 索引法则

最左前缀法则

最左前缀法则主要针对联合索引，即索引了多列/多个字段；最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列

• 如果跳过了某一列，索引将部分失效（后面的字段索引失效）
• 如果只是跳过最后一列，索引依然生效，只是索引长度不同
• 如果最左边的列不存在，索引就会全部失效
• 在书写SQL语句是，最左边的列存在即可，和书写的顺序没有关系

范围查询

联合索引中，出现范围查询（>，<），范围查询右侧的列索引失效，比如下面的sql，原始长度为54，status字段长度为5，age字段长度为2，此时执行结果表示索引长度为49，即只有status失效

解决方法：

• 在业务允许的条件下，尽量使用>=或者<=这样的范围查询

3.6.3 索引失效

• 索引列运算
  • 不要在索引列上进行运算操作，否则索引将失效
    • 比如对手机号有唯一索引，现在想查询手机尾号为15的用户，会考虑去使用截取字段，substring(phone,10,2) = 15，虽然此时查到了用户，但是查询走的是权标查询
• 字符串不加引号
  • 字符串类型字段使用时，如果不加引号，索引将失效
    • 字符串不加单引号，存在隐式类型转换
• 模糊查询
  • 如果仅仅是尾部模糊查询匹配，索引不会失效。如果是头部模糊查询匹配，索引失效
• or连接的条件
  • 使用or进行条件连接时，只有两侧条件都有索引，索引才不会失效
• 数据分布影响

  • 如果MYSQL评估，使用索引比全表查询更慢，则不会使用索引

  • 

  • 

  • 通过测试可以得到一个结论，如果查询结果是表中的绝大部分数据，甚至是全部，是不会走索引的，就比如上表中，当phone>=00 或者 >= 10这样的查询结果，都是大部分数据，那么是不会去走索引的

3.6.4 SQL提示

对于一个字段，存在单列索引和联合索引，在MYSQL优化器的选择下，会使用联合索引，而自己是可以进行选择的，SQL提示，是优化数据库的一个重要字段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的，简单来说就是告诉数据库使用哪一个索引

• use index；

  • 告诉数据库使用哪个索引,但相当于只是给MYSQL一个建议

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

• ignore index;

  • 告诉数据库忽略哪个索引

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

• force index;

  • 告诉数据库必须使用哪个索引

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

3.6.5 覆盖索引

尽量使用覆盖索引（查询过程使用了索引，并且查询所需要返回的列，在该索引中已经全部能够找到），减少 select *

比如二级索引 name，他的节点有 name，有id，当进行sql查询 select id ,name，的时候，该索引直接就能返回这两列，不需要进行回表查询

注:

• 如果执行计划中，extra中出现的情况
  • using inxdx condition：查找使用了索引，但是需要回表查询数据
  • using where;using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

3.6.6 前缀索引

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

语法：

create index ix_xxxx on table_name(column(n))

n表示前几个字符，n为5表示前五个字符

前缀长度：

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

--计算不重复的字段数据量
select count(distinct email) from tb_user;

--计算选择性
select count(distinct email)/count(*) from tb_user;

--计算n，也就是通过截取函数，截取一定长度的数，计算选择性
select count(distinct substring(email,1,10))/count(*) from tb_user;

例如：

对email字段创建一个长度为5的前缀索引

create index idx_email_5 on tb_user(email(5));

3.6.7 单列索引和联合索引

• 单列索引
  • 一个索引只包含单个列
• 联合索引
  • 一个索引包含了多个列

如果存在多个查询条件，考虑针对于查询字段简历索引时，建议建立联合索引，而非单列索引，可以规避回表查询

3.7 索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引（超过100多w考虑建立索引，几千几万的数据，不建立索引也能高效查询）
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引效率越高
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束。当优化器知道每列是否包含NULL值时，它可以更好确定哪个索引最有效地用于查询

4. SQL优化

4.1 插入数据

4.1.1 insert优化

• 批量插入

  • 每条insert语句插入多个数据，但是数据量建议在1000条左右

  • insert into xxx values(1,'Tom'),(2,'Cat'),(3.'Jerry')
    

• 手动提交事务

  • 默认情况下是自动提交的，意味着每次执行insert语句，会开启关闭一次事务，执行多条时就会反反复复开启关闭事务，此时在语句前开启事务，在语句结束后关闭事务

  • start transaction;
    insert into xxx values(1,'Tom'),(2,'Cat'),(3,'Jerry');
    insert into xxx values(4,'Tom'),(5,'Cat'),(6,'Jerry');
    insert into xxx values(7,'Tom'),(8,'Cat'),(9,'Jerry');
    commit;
    

• 主键顺序插入

  • 主键顺序插入会比乱序插入效率更高
  • 

4.1.2 大批量数据插入

如果一次性需要插入大批量数据，使用insert语句插入性能更低，此时可以使用MYSQL数据库提供的load指令进行插入，在使用load指令时，也需要使用顺序插入

--客户端连接服务器时，加上参数 --local-infile，加载本地文件
mysql -u root -p --local-infile

--设置全局参数local_infile为1，开启从本地加载文件导入数据开关
set global local_infile=1;

--切换数据库
use itcast

--加载数据
load data local infile '文件路径' into table `表名` fields terminated by '分隔符' lines terminated by '\n';

--例：通过pwd找到sql文件路径，数据需要导入到tb_sku表，数据分隔符是，存在换行
load data local infile '/home/hexie/sql/tb_sku2.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

4.2 主键优化

4.2.1 数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表成为索引组织表(index organized table IOT)，如下图所示：

回顾一下InnoDB的逻辑存储空间：

• 最外层是表空间(Tablespace)，表空间里面存储的是段(Segment)，段当中存放的是区(Extent)，固定大小为1M，在区当中存放的是页(Page)，而页是InnoDB磁盘管理的最小单元，默认大小为16K，在页里面存放的是行(Row)，行存储的才是数据

4.2.2 页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据大，会行溢出），根据主键排列

页中的数据顺序插入数据后是这样的

此时新来了个数据为50

根据MYSQL的存储结构，50这个数据不会直接去开辟一块新的空间，它应该插入到47之后，但是这个时候插入不上，那么MYSQL就会去做一个操作，开辟新的一页，第三页，然后将第一页超过50%的数据 23 47，连同新数据50放入第三页中，这个时候第一页的指向也不应该是第二页，而是第三页

重新设置链表指针后的页示意图：

以上这种现象就叫做页分裂

4.2.3 页合并

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

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

页合并以后

此时再插入一个数据为20，他会插入到第三页

注：

• MERGE_THRESHOLD：合并页的阈值，默认是50%，可以自己设置，在创建表或者创建索引时指定

4.2.4 主键设计原则

• 满足业务需求的情况下，尽量降低主键的长度
• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号
• 业务操作时，避免对主键的修改

4.3 order by优化

MYSQL的排序方式有两种：

• Using FileSort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort
• Using Index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高

using index效率高，所以在进行排序优化时，尽量优化为using index

例：

• 表中没有索引
  • 执行SQL explain select id,age,phone from tb_user order by age,phone
  • 此时执行计划为 using filesort，因为排序字段不存在索引
• 针对age，phone建立联合索引
  • 执行SQL explain select id,age,phone from tb_user order by age,phone
  • 或者SQL explain select id,age,phone from tb_user order by age
  • 执行计划都是 using index
• 存在age,phone的联合索引
  • 执行SQL explain select id,age,phone from tb_user order by age desc,phone desc
  • 执行计划为 Backward index scan；using indx
    • 即倒序排列，反向扫描索引
• 存在age，phone的联合索引
  • 执行SQL explain select id,age,phone from tb_user order by phone，age
  • 执行计划为 using index，using filesort
  • 个人理解为，进行排序操作的时候是先对phone进行排序，此时不满足最左前缀法则，走全文索引
• 存在age，phone的联合索引
  • 执行SQL explain select id,age,phone from tb_user order by age asc，phone desc
  • 执行计划为 using index；using filesort
  • 原因：在创建索引的时候没有指定顺序，默认是按照升序的，也就是说，先按照age进行升序排列，如果age相同，再按照phone进行升序排列，而此时对phone按照倒序排列，就需要进行额外的排序操作
  • 优化：如果就想进行age升序，phone倒序排列，并且优化为using index，可以针对它们创建索引，具体表现为
    • create index idx_user_age_pho_ad on tb_user(age asc,phone desc)

两种索引示意图：

注：上述order by优化的前提是覆盖索引，如果不是覆盖索引，比如对user表写select *，那就会出现filesort

总结：

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

4.4 group by优化

• 根据专业来进行分组

  • SQL：explain select profession，count(*) from tb_user group by profession;
  • 没有用到任何索引，extra中显示 using temporary，使用临时表，性能比较低

• 创建针对 profession、age、status的联合索引

  • SQL：explain select profession，count(*) from tb_user group by profession;
  • 使用了索引

• 创建针对 profession、age、status的联合索引，并对age字段进行分组

  • SQL：explain select age，count(*) from tb_user group by age;
  • 使用了索引和临时表

• 创建针对 profession、age、status的联合索引，并对profession、age字段进行分组

  • SQL：explain select profession,age，count(*) from tb_user group by profession,age;
  • 使用了索引
  
  
  

4.5 limit优化

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

例：

• SQL：select * from tb_sku limit 900000,10
  • 耗时：约19s
• SQL：select id from tb_sku order by id limit 900000,10
  • 耗时：约11s
• 最终方案：select s.* from tb_sku s,(select id from tb_sku order by id limit 900000,10) a where s.id = a.id
  • 耗时：约11s

注：mysql8版本，不能在子查询中使用limit字段

• 错误SQL：select * from tb_sku where id in (select id from tb_sku order by id limit 900000,10)

4.6 count优化

在对tb_sku表进行count(*)操作时，发现非常耗时，这是取决于InnoDB的解决方案

• MYISAM 引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高，前提是后面没有where条件，否则MYISAM的查询效率也很低
• InnoDB引擎比较麻烦，它在执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积技术
• 优化思路：自己计数

4.6.1 count的几种用法

• count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值
• 用法：count(*)、count(主键)、count(字段)、count(1)
• count(主键)
  • InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）
• count(字段)
  • 如果没有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加
  • 如果有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
• count(数字)
  • InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字进去，直接按行进行累加
• count(*)
  • InnoDB引擎并不会把所有字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加
• 按照效率排序的。count(字段)<count(主键)<count(1)≈count(*)
• 所有尽量使用count(*)

4.7 update优化

在使用update进行更新的时候，where条件尽量使用索引进行更新，否则会引起表锁，引起表锁的时候，如果当前事务没有提交，那么其他事物进入堵塞阶段，无法进行更新

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能是小，否则会从行锁升级为表锁

5. 视图

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自 定义视图的时候使用的表，并且是在使用视图时动态生成的

即视图只保存查询逻辑，不保存查询结果。索引在创建视图的时候，主要工作落在创建SQL查询语句上

5.1 基本语法

创建视图时 or replace可以省略，但是修改的时候主要就是基于 or replace

例：

针对student表创建一个名为stu_v_1的视图，并进行查询、修改、删除操作

##创建视图
CREATE 
	OR REPLACE VIEW stu_v_1 AS SELECT
	ID,
NAME 
FROM
	student 
WHERE
	ID <= 10;
	
	
##查询视图
	## 查看创建视图语句
show create view stu_v_1;
	## 查看视图数据
select * from stu_v_1;	


##修改视图
CREATE 
	OR REPLACE VIEW stu_v_1 AS SELECT
	ID,
	NAME,
	NO
FROM
	student 
WHERE
	ID <= 10;
	
##或者
ALTER VIEW stu_v_1 AS SELECT
	ID
FROM
	student 
WHERE
	ID <= 10;	


##删除视图
DROP VIEW IF EXISTS stu_v_1;

5.2 检查选项

5.2.1 cascaded

视图不进行数据存储的，它是基于表的，也就是说，当进行插入操作时，数据会插入到表中，然后再由视图显现出来

当使用 with cascaded check option 子句创建视图时，MYSQL会通过视图检查正在更改的每个行，例如 插入、更新、删除，以使其符合视图的定义。MYSQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysqk提供了两个选项：CASCADED和LOCAL，默认值是CASCADED

cascaded也就是级联，当使用该语句建视图时，不仅当前视图加上检查，它所基于的（表/视图）也会加上检查

当前加入检查选项之后，两张视图都会进行检查

如果此时在来一张，基于v2视图，但是不加检查选项的v3视图

此时，id为17的数据是能够进行插入的，因为v3不具有检查选项，mysql会对v2进行检查，发现17小于20，满足，再去检查v1，发现也满足，能够插入

例如：

创建一个id<=20的视图，如果没有检查选项，那么30也是正常能够插入到表中（视图没有显示），但是加了选项以后，就会进行报错

create or replace view stu_v_1 as select id,name from student where id <= 20 with cascaded check option;

select * from  stu_v_1;
	
INSERT INTO stu_v_1 VALUES(6,'TOMaa');

INSERT INTO stu_v_1 VALUES(30,'TOMaa');

插入id为30时的报错：

5.2.2 local

此时对v2视图进行数据添加，它会先检查自己的条件，再去检查所依赖的v1视图所带的条件（如果v1定义有检查选项，否则不检查），但是不会给v1视图加上检查

5.3 更新及作用

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

不能更新的情况：

• 聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT()等）
• DISTINCT
• GROUP BY
• HAVING
• UNION或者 UNION ALL

视图的作用：

• 简单
  • 视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，使得用户不必为以后的操作每次都指定全部的条件
• 安全
  • 数据库可以授权，但不能授权到数据库特定的行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
• 数据独立
  • 视图可帮助用户屏蔽真实表结构变化带来的影响

6. 存储过程

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

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

• 特点
  • 封装，复用
  • 可以接收参数，也可以返回数据
  • 减少网络交互，效率提升

6.1 基本语法

6.1.1 创建

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

6.1.2 调用

CALL 名称 ([参数])

6.1.3 查看

-- 从mysql自带的ROUTINES表进行查看
-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名称'; 

-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程

6.1.4 删除

DROP PROCEDURE [IF EXISTS] 存储过程

例：

##存储过程基本语法
## 创建
create procedure p1()
begin
	select count(*) from student;
end;

## 调用
call p1();

## 查看
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'itcast';
SHOW CREATE PROCEDURE p1

## 删除
drop procedure p1

注：

• 在命令行进行创建可能会出现问题
  • 在命令行中操作，一旦见到分号，就认为SQL语句结束
    • 需要通过关键字delimiter指定SQL语句的结束符
    • 

6.2 变量

6.2.1 系统变量

系统变量是MYSQL服务器提供的，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）

• 查看系统变量

  • SHOW [SESSION|GLOBAL] VARIABLES;       --查看所有系统变量
    SHOW [SESSION|GLOBAL] VARIABLES;  LIKE '....'  --可以通过LIKE模糊匹配的方式查找变量
    SELECT @@[SESSION|GLOBAL].系统变量名     -- 查看指定变量的值
    

• 设置系统变量

  • SET [SESSION|GLOBAL] 系统变量名 = 值；
    SET @@[SESSION|GLOBAL] 系统变量名 = 值；
    

注意：

如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量
MySQL服务重新启动之后，所设置的全局变量会失效，要想不失效，可以在/etc/my.cnf中配置

6.2.2 用户定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。

• 赋值

  • SET @变量名 = expr [, @变量名 = expr]...;
    SET @变量名 := expr [, @变量名 = expr]...;
    

  • SELECT @变量名 := expr [, @变量名 := expr]...;
    SELECT 字段名 INTO @变量名 FROM 表名；   --指定SQL查询的结果赋值给某个变量
    

• 使用

  • SELECT @var_name;
    

例：

赋值推荐使用 := ，因为mysql没有==这样的符号来进行比较，它的比较符就是=

## 变量：用户变量
## 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby := 'java';

select @mycolor := 'red';
## 把select count(*) from tb_user;写入到变量
select count(*) into @mycount from tb_user

## 使用
select @myname,@myage,@mygender,@myhobby,@mycolor,@mycount;

注意：
用户定义的变量无序对其进行声明或初始化，直接赋值，如果不赋值，得到的值为NULL

6.3.3 局部变量