Mysql进阶总结

一、 存储引擎

1.1 MySQL体系结构

MySQL体系结构：连接层，Server层，引擎层，存储层

• 连接层：连接层负责处理客户端与MySQL服务器之间的连接和通信。它接收客户端的连接请求，并建立与客户端的网络连接。
• Server层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
• 引擎层：负责数据的存储和检索。架构模式是插件式，服务器通过API和存储引擎进行通信。支持 InnoDB、MyISAM、Memory 等多个存储引擎。
• 存储层：MYSQL的物理存储部分，负责将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询 日志、慢查询日志等)存储在磁盘上。

1.2存储引擎

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

存储引擎是基于表的，而不是 基于库的，所以存储引擎也可被称为表类型。可以在创建表的时指定选择的存储引擎，没有指定将自动选择默认的存储引擎。

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

show engines;

建表时指定存储引擎

不指定，会选择默认的存储引擎 InnoDB

CREATE TABLE  表名(
	字段1  字段1类型   [ COMMENT  字段1注释 ] ,
	 ......
	字段n  字段n类型   [COMMENT  字段n注释 ] 
) ENGINE = INNODB   [ COMMENT  表注释 ] ;

1.3存储引擎特点

InnoDB

在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。

1. 特点：

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

2. 文件

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

 show variables  like 'innodb_file_per_table';

该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。

3.逻辑存储结构

• 表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。
• 段 : 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
• 区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。
• 页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，即每次读取到内存的时候是读取一页的数据，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
• 行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段。

MyISAM

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

1. 特点

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 访问速度快
• 优点：更少的存储空间，支持全文索引，适用于读取频率较高、写入频率较低的应用场景

2. 文件

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

Memory

Memory引擎的表数据时存储在内存中，若受到硬件问题、或断电问题的影响，表中数据消失，一般用于临时缓存使用

1.特点：

• 内存存放，访问速度较快
• hash索引（默认）

2. 文件

xxx.sdi：存储表结构信息

面试题：
InnoDB引擎与MyISAM引擎的区别 ?
①. InnoDB引擎, 支持事务, 而MyISAM不支持。
②. InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。
③. InnoDB引擎, 支持外键, 而MyISAM是不支持的。

1.4 存储引擎选择

根据特点选；对于复杂系统，可以使用多种存储引擎进行组合

InnoDB： 要求并发、数据操作有插入、查询外，还有很多更新、删除操作

MyISAM： 主要是读和插入数据——评论等——MongoDB代替

MEMORY： 访问速度快，太大的不能缓存在内存中，且无法保障数据的安全性——Redis代替

二、索引

2.1索引概述

索引（index）是帮助 MySQL 高效获取数据的数据结构。

数据库系统除了维护数据之外，还维护着索引。索引指向着数据。

• 执行的SQL语句为 ：

select * from user where age = 45;

• 无索引情况：全表扫描
• 有索引：改进B+tree

索引优缺点：

• 优点：
  • 提高数据检索效率，降低数据库的I/O成本
  • 通过索引对数据进行排序，降低数据排序的成本，降低CPU消耗
• 缺点：
  • 索引占用了数据库的空间 （磁盘便宜
  • 索引提高了查询的效率，降低了更新表（Insert，update，delete）的速度，因为增删改表也需要同时维护索引。 （查询的次数远大于增删改操作的次数

2.2索引结构

B+Tree, Hash, R-tree, Full-text

MySQL的索引是在存储引擎层中实现的，不同的存储引擎有不同的索引结构：

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

不同的存储引擎对于索引结构的支持情况。

为什么 InnoDB 存储引擎选择使用 B+tree 索引结构?

相对于二叉树，层级更少，搜索效率高；
对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储 的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
相对Hash索引，B+tree支持范围匹配及排序操作；

B+Tree

• 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据
• 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据

B+Tree特点：
所有的数据都会出现在叶子节点
叶子节点形成一个单向链表
非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的

MySQL索引数据结构对经典的B+Tree进行了优化，增加一个指向相邻叶子节点的链表指针

Hash

• 哈希索引：采用hash算法，将键值换算成新的hash值，映射到对应的槽位，存储在hash表中

采用hash算法 将键key换算成新的hash值，映射到对应槽位上，存储在hash表中。若产生冲突，可以采用拉链法（相同的key往后延申成链表），线性探测法（逐个找哈希表中的空闲位置），二次探测法（以二次函数为步长找哈希表中空闲位置），双重哈希法（对key再计算一次hash值）等解决冲突

1、Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
2、无法利用索引完成排序操作
3、查询效率高(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引）

 在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

2.3索引分类

索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

聚集索引&二级索引

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

聚集索引选取规则:

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

聚集索引和二级索引的具体结构如下：

• 聚集索引的叶子节点下挂的是这一行的数据 。
• 二级索引的叶子节点下挂的是该字段值对应的主键值。

回表查询： 先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询

以下哪个SQL语句的执行效率会更高，为什么？（id为主键，name字段创建的有索引

​ ① select * from user where id = 10;

​ ② select * from user where name = 'Arm';

答：语句①只需要一次索引扫描，语句②需要先查找主键 再回表使用聚集索引获取一整行数据 因此语句①的执行效率会更高

2.4索引语法

1. 创建索引

 CREATE  [ UNIQUE | FULLTEXT ]  INDEX  index_name  ON  table_name  ( index_col_name,... );

2. 查看索引

SHOW  INDEX  FROM  table_name;

3. 删除索引

DROP  INDEX  index_name  ON  table_name;

2.5SQL索引性能分析

SQL执行频率

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

在这个查询中，使用了SHOW GLOBAL STATUS LIKE 'Com_______';语句来获取以"Com"开头的状态变量的值。这些状态变量包括：

• Com_binlog: 二进制日志事件的数量。
• Com_commit: 提交事务的数量。
• Com_delete: 删除操作的数量。
• Com_import: 导入操作的数量。
• Com_insert: 插入操作的数量。
• Com_repair: 修复操作的数量。
• Com_revoke: 撤销权限操作的数量。
• Com_select: 选择操作的数量。
• Com_signal: 发送信号操作的数量。
• Com_update: 更新操作的数量。
• Com_xa_end: XA事务结束操作的数量。

那么通过查询SQL的执行频次，我们就能够知道当前数据库到底是增删改为主，还是查询为主。 那假如说是以查询为主，我们又该如何定位针对于那些查询语句进行优化呢？ 次数我们可以借助于慢查询日志。

慢查询日志

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

MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。

show variables like 'slow_query_log';

docker有两种修改方法,一种是通过sql语句(mysql重启后失效),另外一种是修改配置文件(永久有效)

方法一:通过sql语句

1.登入mysql

mysql -u root -p

#然后输入密码

2.查看是否开启了慢查询日志、设置的阈值、慢查询日志的存放位置

show variables like 'slow%';
#不同版本会有区别所以也可以使用第二种方法查看(两个百分号%)
show variables like '%slow%';

 如图所示,并未开启慢查询 slow_query_log为OFF

3.开启慢查询日志,设置阈值

#开启慢查询
set global slow_query_log = ON;
#修改慢sql的阈值，sql执行超过阈值的时候就会记录到慢日志的log文件中。
set global slow_launch_time = 3;//根据需要设置秒数

4.退出mysql,查看慢查询日志

#退出mysql
exit
#查看慢查询日志
cat -n /var/lib/mysql/7b7b345abeb6-slow.log

方法二:通过配置文件

修改my.cnf配置文件，在[mysqld]下的下方加入

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/tmp_slow.log //linux
long_query_time = 1

在慢查询日志中，只会记录执行时间超多我们预设时间（2s）的SQL，执行较快的SQL是不会记录的。

那这样，通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化。

• 尝试使用慢查询日志：

  • 一个进程执行 sudo tail -f /var/log/mysql/mysql-slow.log查看日志文件的尾行
  • 一个进程进入MySQL执行 SELECT BENCHMARK(1000000000, 1+1);进行长时间的压测

profile详情

show profiles 能够在做SQL优化时帮助我们了解SQL执行时间具体都耗费到哪里去了。

profile查看指令耗时。执行指令时，当查询时间超过设置时间后才会写慢查询入日志，但有些SQL语句任务简单 时间在超过的设置时间左右 是不合理的，如何发现这类SQL语句 可以使用 profile 在做SQL优化时帮助我们了解时间耗费情况

• 查看当前数据库是否支持profiles select @@have_profiling ;

• 查看当前数据库是否打开了 profiling select @@profiling;

• 开启profiling SET profiling = 1;

• 使用指令查看当前会话指令的执行耗时

  • show profiles; 查看每一条SQL的耗时基本情况
  • show profile for query query_id; 查看指定query_id的SQL语句各个阶段的耗时情况
  • show profile cpu for query query_id; 查看指定query_id的SQL语句CPU的使用情况

explain

explain 或者 desc 命令获取 MySQL 如何执行 select 语句的信息，包括在 select 语句执行过程中表如何连接和连接的顺序。

-- 直接在select语句之前加上关键字 explain / desc
 EXPLAIN   SELECT   字段列表   FROM   表名   WHERE  条件 ;

直接在select语句之前加上关键字

explain 执行计划中各个字段的含义

2.6验证索引效率

• 在未建立索引时，执行SQL语句 查看SQL耗时：

  select * from tb_sku where sn = '100000003145001';

• 对字段sn建立索引后，重新进行查询，查看SQL耗时

  create index sn_idx on tb_sku(sn);

  select * from tb_sku where sn = '100000003145001';

  show index from tb_sku;

使用explain解释和分析 查询语句

explain select * from tb_sku where sn = '100000003145001';

2.7索引失效情况

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

  • 在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时

    建议建立联合索引， 而非单列索引。

• 前缀索引

  • 字段类型为字符串(varchar,text)，若需要索引是很长的字符串，会使索引长度过长，浪费大量磁盘IO 影响查询效率。因此仅对字符串的一部分前缀建立索引，节约索引空间，提高查询效率

最左前缀法则——联合索引（复合索引）

查询从索引的最左列开始，并且不跳过索引中的列；如果跳过某一列，索引将部分失效（后面的字段索引失效）——创建的时候最左边的字段，只要存在就会启动索引，不考虑在selecet中字段排列的前后顺序

# 1. 为表中的字段：profession、age、status创建联合索引
create index  pro_age_sta_idx on tb_user(profession,age,status);
# ----------- 测试 联合索引是否失效,数字是explain的索引长度----------------
# profession、age、status三个字段都使用到了索引  57
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0'; 
# profession、age 字段使用到了索引  49
explain select * from tb_user where profession = '软件工程' and age = 31;
# profession 字段使用到了索引 47
explain select * from tb_user where profession = '软件工程';
# 全表扫描，没有使用索引，不符合最左前缀法则 NULL
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';
# profession 字段使用到了索引，跳过了age，因此age后的status字段无法使用索引 47
explain select * from tb_user where profession = '软件工程' and status = '0';
# profession、age、status三个字段都使用到了索引，与查询时的位置顺序无关 57
explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程';

联合索引的最左边的字段(即是第一个字段)必须存在，与编写SQL时，条件编写的先后顺序无关

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <

# profession、age使用到了索引， 49
explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';
# profession、age、status三个字段都使用到了索引  57
explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';

索引列运算

不要再索引列上进行运算操作，否则索引将失效

 explain  select  *  from  tb_user  where  substring(phone,10,2) = '15'; # phone索引失效

字符串不加引号

字符串不加单引号，存在隐式类型转换——自动类型转换之后，索引就失效了

• 字符串类型字段使用时，不加引号，索引将失效
• 字符串不加单引号，对于查询结果，没什么影响，但是数据库存在隐式类型转换，索引将失效

模糊查询

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

即在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字前面加了%，索引将会失效

or连接条件

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

当or连接的条件，左右两侧字段都有索引时，索引才会生效。

数据分布影响

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

2.8索引使用

SQL提示

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

use index：建议MySQL使用哪一个索引完成此次查询（仅仅是建议，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 = '软件工程';

覆盖索引

尽量使用覆盖索引，减少select *。select * 减少使用，极易出现回表查询，降低性能

那么什么是覆盖索引呢？ 覆盖索引是指查询条件使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。

接下来，我们来看一组SQL的执行计划，看看执行计划的差别，然后再来具体做一个解析。

explain select id, profession from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

述这几条SQL的执行结果为:

从上述的执行计划我们可以看到，这四条SQL语句的执行计划前面所有的指标都是一样的，看不出来差异。但是此时，我们主要关注的是后面的Extra，前面两天SQL的结果为 Using where; Using Index ; 而后面两条SQL的结果为: Using index condition 。

思考题：
一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案:
select id,username,password from tb_user where username = ‘itcast’;

答案: 针对于 username, password建立联合索引, sql为: create index idx_user_name_pass on tb_user(username,password);
这样可以避免上述的SQL语句，在查询的过程中，出现回表查询。 

前缀索引

——空间和效率的平衡

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

语法

create index  idx_xxxx on table_name(column(n));

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

# 查询使用email整个字符串的索引选择比   1.0000
select count(distinct email) / count(*) from tb_user;
# 查询使用email 使用前缀5个字符串的索引选择比  0.9583
select count(distinct substring(email,1,5)) / count(*) from tb_user ;
# 查询使用email 使用前缀2个字符串的索引选择比   0.9167
select count(distinct substring(email,1,2)) / count(*) from tb_user ;
# 对字段email建立前缀索引，前缀长度为5  
create index email_idx on tb_user(email(5));
# 查看使用email前缀索引进行查询的执行结构
explain select * from tb_user where email = 'xiaoyu666@qq.com'; 

修改n，查看索引选择性的值，在索引选择性和前缀长度 做权衡

2.9索引使用原则

三、SQL优化

3.1插入数据

insert

insert into tb_test values(1,'tom');

insert into tb_test values(2,'cat');

insert into tb_test values(3,'jerry');

.....

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

1）批量插入数据

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

2）手动控制事务

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

3）主键顺序插入，性能要高于乱序插入。

主键乱序插入 : 8  1  9  21  88  2  4  15  89  5  7  3  
主键顺序插入 : 1  2  3  4  5  7  8  9  15  21  88  89

见主键优化

大批量插入数据

• 一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低
• 使用MySQL数据库提供的load指令进行插入

3.2主键优化

顺序插入比乱序插入(页分裂，删除数据时页合并)快

InnoDB存储引擎中数据组织方式：按主键顺序组织存放，这种存储方式地表称为索引组织表（index organized table, IOT）

主键设计原则：

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

• 主键作为二级索引的叶子节点，如果太长会占用空间
• 搜索时耗费磁盘IO

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

3）尽量不要使用UUID（JAVA中生成随机字符串的一个类方法）做主键或者其他自然主键

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

3.3order by 优化

分类：

MySQL的排序有两种方式（Explian的Extra内容：

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

Using index的性能高，Using filesort的性能低，在优化排序操作时，尽量要优化为 Using index

create  index  idx_user_age_phone_aa  on  tb_user(age [asc/desc],phone [asc/desc]);
explain select  id,age,phone from tb_user order by age, phone;

order by 语句需要与索引的顺序完全匹配，创建联合索引时默认升序排列

create index age_phodes_idx on tb_user(age asc, phone desc);

优化：

1）根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则

2）尽量使用覆盖索引

3）多字段排序，一个升序一个降序，可以在联合索引创建时建立规则（ASC/DESC）

4）如果不可避免出现filesort，大量数据排序时，可以适当增大排序缓冲区的大小sort_buffer_size（默认256K）

3.4group by 优化

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

3.5 limit 优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，并仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序代价非常大

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

其实也是在回表阶段给简化了

# id 为主键索引 执行耗时11.46 sec， 低于直接limit查询的 19.39 sec
select s.* from tb_sku s, (select id from tb_sku order by id limit 9000000,10) as a wehre s.id = a.id;

3.6count 优化

问题： InnoDB引擎执行count(*)的时候，需要把数据一行一行地从引擎中读出来，然后累积计数

注： MyISAM引擎把一个表地总行数存在了磁盘上，没有where条件地时候，就会直接返回这个数，效率很高。 

select count(*) from tb_user ; 若数据量很大，在执行count操作时，是非常耗时的

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 时候会直接返回这个 数，效率很高； 但是如果是带条件的count，MyISAM也耗时
• InnoDB 引擎就麻烦了，执行 count(*) 的时候，需要把数据一行一行从引擎里面读取，然后累积计数

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加，最后返回累计值。

用法：count（*）、count（主键）、count（字段）、count（数字）

优化思路：自己计数 (可以借助于redis这样的数据库进行，但是如果是带条件的count又比较麻烦了)。

按照效率排序：

count(字段) < count(主键 id) < count(1) ≈ count(*)

count(*) 和 count(1) 不取值，count(id)取值但不用判断null，count(字段)取值还需要判断null所以尽量使用 count(*)。

 3.8update优化

没有索引就会锁住整张表

update 表名 set 字段1 where 字段2 = 'xxx'

当 WHERE 子句中的字段2没有索引时，数据库通常会使用表锁。表锁会锁定整个表，以确保在更新过程中没有其他事务可以修改表中的数据。可能会导致并发性能下降，因为其他事务无法同时访问该表的其他行。

**当 WHERE 子句中的字段有索引时，数据库通常会使用行锁。**行锁仅锁定满足条件的行，而不是整个表。这样可以提高并发性能，因为其他事务可以同时访问不受锁限制的其他行。

要根据索引字段进行更新，否则行级锁变成表级锁

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

四、视图/存储过程/触发器

4.1视图

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

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作 就落在创建这条SQL查询语句上。

视图的语法

• 创建

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

create view stu_v_1 as select id,name from student where id <= 10;

• 查看创建视图语句

SHOW CREATE VIEW 视图名称;

show create view stu_v_1;

• 查看视图数据

SELECT * FROM 视图名称 ...... ;

select * from stu_v_1;

select * from stu_v_1 where id < 3;

• 修改

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

alter view stu_v_1 as select id,name from student where id <= 10;

• 删除

DROP VIEW IF EXISTS 视图名称 [,视图名称] ...

drop view if exists stu_v_1;

•  插入、更新数据

create or replace view stu_v_1 as select id,name from student where id <= 10 ;

insert into stu_v_1 values(6,'Tom');

insert into stu_v_1 values(17,'Tom22');

• id为6和17的数据都是可以成功插入的
• 但是查询出来的数据，却没有id为17的记录
• 在创建视图的时候，指定的条件为 id<=10, id为17的数据，是不符合条件的
• 但是这条数据确实是已经成功的插入到了基表中

可以像操作表一样去操作视图，但视图并不存储数据，数据都存放在基表中

视图的检查选项

使用子句with check option创建视图时，MYSQL会通过视图检查正在更改的每行（插入，更新，删除等操作）是否符合视图的定义。MySQL允许基于一个视图创建另一个视图，并检查依赖视图中的规则以保持一致性。为了确定检查的范围，提供了两个选项

• WITH CASCADED CHECK OPTION 是指在更新视图时，会检查视图中所有相关的视图和基表的约束。如果更新操作违反了任何一个相关表的约束条件，更新将被拒绝。
• WITH LOCAL CHECK OPTION 是指在更新视图时，仅检查当前视图的约束条件。如果更新操作违反了当前视图的约束条件，更新将被拒绝。不会检查其他相关表的约束条件。

CASCADED：级联

• v2视图是基于v1视图的
• v2视图创建的时候指定了检查选项为 cascaded
• 在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1

 LOCAL：本地

• v2视图是基于v1视图的
• v2视图创建的时候指定了检查选项为 local
• 执行检查时，知会检查v2，不会检查v2的关联视图v1

视图的更新

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

A. 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
B. DISTINCT
C. GROUP BY
D. HAVING
E. UNION 或者 UNION ALL

视图作用

• 简单：经常使用的查询可以定义为视图，使得用户不必为以后的操作每次指定全部的条件
• 安全：通过视图用户只能查询和修改他们所能见到的数据
• 数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响

• 为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段

4.2存储过程

• 概念：是一段预先编译并保存在数据库中的可重复使用的代码块。存储过程由一系列的 SQL 语句和控制流语句组成，可以接受参数，并且可以返回结果。存储过程通常用于执行复杂的数据库操作，实现业务逻辑和数据处理等任务。

特点：

• 封装，复用。可以把某一业务SQL封装在存储过程中，使用时直接调用
• 可以接收参数，也可以返回数据。存储过程中，可以传递参数，也可以接收返回值
• 减少网络交互，效率提升。如果分步执行多条SQL，则每执行一次都是一次网络传输。 而如果封装在存储过程中，则只需要一次网络交互即可。

4.3触发器 Triggers

在 MySQL 中，触发器（Triggers）是一种数据库对象，它与表相关联，并在表上的特定事件发生时自动执行一系列动作。是在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还 只支持行级触发，不支持语句级触发。

old用来引用原来的记录内容，new用来引用新的记录内容

• INSERT 型触发器：NEW 表示将要或者已经新增的数据

• UPDATE 型触发器：OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据

• DELETE 型触发器： OLD 表示将要或者已经删除的数据

# 创建
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
	trigger_stmt ;
END;
# 查看
SHOW TRIGGERS ;
# 删除
DROP TRIGGER [schema_name.]trigger_name ; 
-- 如果没有指定 schema_name，默认为当前数据库

五、锁

计算机协调多个进程或线程并发访问某一资源的机制，数据库中的锁也是想让共享数据能保证并发访问的一致性和有效性

锁冲突也是影响数据库并发访问性能的一个重要因素

分类——粒度

• 全局锁： 锁住数据库中的所有表
• 表级锁： 每次操作锁住整张表
• 行级锁： 每次操作锁住对应的行数据

5.1全局锁

• 全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态
• 典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图和表格

备份Mysql数据库

可以使用Mysql自带的 mysqldump 工具来备份数据库。该工具可以生成 SQL 脚本文件，包含数据库中所有表和数据的语句。

mysqldump -u [username] -p [database_name] > [backup_file].sql
# [username] 是 MySQL ⽤户名，[database_name] 是需要备份的数据库名称，[backup_file].sql 是备份的⽂件名。

恢复Mysql数据库

如果要恢复数据库，前提要有备份的文件。可以运行以下命令：

mysql -u [username] -p [database_name] < [backup_file].sql
# [username] 是 MySQL ⽤户名，[database_name] 是需要恢复的数据库名称，[backup_file].sql 是备份的文件名。

语法

1). 加全局锁

 flush tables with read lock ;

2). 数据备份

 mysqldump  -uroot –p1234  itcast > itcast.sql

3). 释放锁

 unlock tables ;

特点

数据库中加全局锁，是一个比较重的操作，存在以下问题：

• 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
• 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。

 mysqldump  --single-transaction  -uroot –p123456  itcast > itcast.sql

5.2表级锁

锁定粒度大，发生锁冲突的概率最高，并发度最低

应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类：

• 表锁
• 元数据锁（meta data lock，MDL）
• 意向锁

表锁

• 对于表锁，分为两类：
• 表共享读锁（read lock）(所有客户端都只能读数据，不能写数据)
• 表独占写锁（write lock）(只有上锁的客户端可以读写数据，其他都不能读写数据。)

语法：

-- 加锁：
lock tables 表名 read/write

--释放锁：
unlock tables

元数据锁

• 当我们进行增删改查的时候，此时是不希望表的结构发生改变的（例如：DDL），所以系统自动加上了MDL读锁(共享)。此时对于其他客户端的增删改查来说是共享的，都不会影响表的结构。但对于DDL操作来说是互斥的。
• 当我们需要修改表的结构的时候，此时是不希望客户端来读取表的数据或是修改表的结构。所以系统自动加上了MDL写锁(排他)。

# 查看元数据锁的语法
select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.metadata_locks;

object_type表示对象类型，object_schema表示对象所在的模式（数据库），object_name表示对象的名称，lock_type表示锁的类型，lock_duration表示锁的持续时间。

常见的SQL操作时，所添加的元数据锁：

意向锁

解决：在加入行锁的时候对表加上意向锁，再来一个线程想要对该表进行加锁，那么就检查想要加的锁和意向锁列表能不能是兼容的，能兼容就加，互斥的情况就阻塞等待着

意向锁分类

• 意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁排他锁（write）互斥
• 意向排他锁（IX）：与表锁共享锁（read）及排他锁（write）都互斥。意向锁之间不会互斥

简单理解：

• 当执行 select ... lock in share mode 语句时，系统会自动给查询的表添加意向共享锁，意向共享锁与表共享读锁是兼容的，与表独占写互斥。
• 意向排他锁(IX): 由insert、update、delete、select…for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥
• 一旦事务提交了，意向共享锁、意向排他锁，都会自动释放

• 可以通过以下SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;

5.3行级锁

行级锁每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突概率最低，并发度最高。

行级锁应用在 InnoDB存储引擎中，InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

• 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行 update 和 delete。在RC、RR隔离级别下都支持。
• 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在 RR 隔离级别下都支持。
• 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

六、InnoDB引擎（了解）

6.1逻辑存储结构

InnoDB的逻辑存储结构如下图所示:

6.2架构

概述

MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。

内存结构

Buffer Pool： 缓冲池，缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（如果缓冲池中没有，就从磁盘加载并缓存），然后再以一定的频率刷新到磁盘，从而减少磁盘IO，加快处理速度

缓冲池是以page页为单位，底层采用链表数据结构管理Page（不同小块的颜色表示不同的Page类型：free page表示空闲page，未被使用；clean page表示被使用page，数据没有被修改过；dirty page表示脏页，是被使用过的，数据被修改过，并且与磁盘数据产生了不一致）

Change Buffer： 更改缓冲区（针对于非唯一二级索引页——>插入顺序是相对随机的，删除和更新可能会影响索引树中不相邻的二级索引页）也就是如果操作的数据没在Buffer pool那就先修改到change Buffer中，等到bufferpool中有了，再一起合并到Buffer pool，然后等待机会刷新到磁盘中

Adaptive Hash Index： 自适应哈希索引，InnoDB是不支持这个索引的，他是用来优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果察觉到hash可以提升速度没那就建立hash索引，无需人工干预。参数是：adaptive_hash_index

log buffer: 日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log、undo log），默认大小是16MB，日志缓冲区的日志会定期刷新到磁盘中。如果更新、插入、删除操作比较多，增加日志缓冲区的大小可以节省磁盘I/O。参数innodb_log_buffer_size:缓冲区大小；innodb_flush_log_at_trx_commit：日志刷新到磁盘的时机

磁盘结构（略）

后台线程

内存中的数据和磁盘的数据 是怎么 写入和读取的呢？后台线程

6.3事务原理

事务：一组操作的集合，要么全部成功，要么全部失败：

事务的四大特性：（ACID）原子性，一致性，隔离性，持久性

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
   

而对于这四大特性，实际上分为两个部分。 其中的原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。 而持久性是通过数据库的锁，加上MVCC来保证的。

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, redo log buffer用于刷新脏页到磁盘。发生错误时, 使用redo log file进行数据恢复使用。

为什么每一次提交事务，要刷新redo log 到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘呢 ?

因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。 而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。 这种先写日志的方式，称之为 WAL（Write-Ahead Logging）。

undo log

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和 MVCC(多版本并发控制) 。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含1024个undo log segment。

总结

• redo log 能保证数据库发生错误时，将数据进行恢复。持久性
• undo log 能保证事务发生回滚时，将数据还原成开始事务之前的数据。一致性

6.4MVCC——高频面试

基本概念

当前读： 读取的是记录的最新版本，读取时要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁（select…lock in share mode、select …for update\update\insert\delet，都是一种当前读）——如果不是当前读，另一个事务修改了数据，是读不到最新数据的

快照读： 简单的select(不加锁)就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读

• Read Committed：每次select，都生成一个快照读
• Repeatable Read：开启事务后第一个select语句是快照读的地方
• Serializable：快照读会退化为当前读

MVCC（Multi-Version Concurrency Control, 多版本并发控制）：维护一个数据的多个版本，使读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。实现依赖于数据库中的三个隐式字段、undo log日志、readView

三个隐藏字段——每一条记录

当我们创建了上面的这张表，我们在查看表结构的时候，就可以显式的看到这三个字段。 实际上除了这三个字段以外，InnoDB还会自动的给我们添加三个隐藏字段及其含义分别是：

如果当前表有主键，则不会生成 DB_ROW_ID 字段

undo log日志

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。

而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

版本链

最终不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条 记录版本链表。

链表的头部是最新的旧记录，链表尾部是最早的旧记录。

那么每次查询的时候，返回哪一个版本的记录呢？ReadView的作用

有一张表原始数据为：

DB_TRX_ID : 代表最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID，是自增的。
DB_ROLL_PTR ： 由于这条数据是才插入的，没有被更新过，所以该字段值为null。

readview

ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id

ReadView中包含了四个核心字段：

而在readview中就规定了版本链数据的访问规则：

trx_id 代表当前undolog版本链对应事务ID

不同的隔离级别，生成ReadView的时机不同：

• READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
• REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

七、MySQL管理

7.1 系统数据库

1）mysql：存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等）

2）information_schema：提供了访问数据库元数据的各种表和视图，包括数据库、表、字段类型及访问权限等

3）performance_schema：为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数

4）sys：包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图

7.2 常用工具

mysql(客户端工具)

该mysql不是指mysql服务而是指mysql的客户端工具。

语法 ：
mysql [options] [database]
选项 ：
-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口
-e, --execute=name #执行SQL语句并退出

-e 选项可以在mysql客户端不登录的情况下执行sql语句。对于一些批处理脚本，这种方式尤其方便。

示例：

mysql -uroot –p123456 db01 -e "select * from stu";

mysqladmin(服务器状态)

mysqladmin 是一个执行管理操作的客户端程序，可以用它来检查服务器的配置和当前状态，创建并删除数据库等。

--通过帮助文档查看选项：
mysqladmin --help

语法:
mysqladmin [options] command ...
选项:
-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口

示例：

--删除数据库
mysqladmin -uroot –p1234 drop 'test01';
--查看数据库版本信息
mysqladmin -uroot –p1234 version;

 mysqldump(备份/导出数据)

mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表以及插入表的sql语句。

语法 ：
mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A
连接选项 ：
-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器ip或域名
-P, --port=# 指定连接端口
输出选项：
--add-drop-database 在每个数据库创建语句前加上 drop database 语句
--add-drop-table 在每个表创建语句前加上 drop table 语句 , 默认开启 ; 不
开启 (--skip-add-drop-table)
-n, --no-create-db 不包含数据库的创建语句
-t, --no-create-info 不包含数据表的创建语句
-d --no-data 不包含数据
-T, --tab=name 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件

示例

--备份db01数据库(删表，建表,数据插入语句)
mysqldump -uroot -p1234 db01 > db01.sql

--备份db01数据库(不备份表结构)
mysqldump -uroot -p1234 -t db01 > db02.sql

--将db01数据库中的score表结构与数据分开备份(生成两个文件)
mysqldump -uroot -p1234 -T /root db01 score