sql进阶

常见面试题：

  • 什么是事务，以及事务的四大特性？
  • 事务的隔离级别有哪些,MySQL默认是哪个?
  • 内连接与左外连接的区别是什么?
  • 常用的存储引擎？InnoDB与MyISAM的区别？
  • MySQL默认InnoDB引擎的索引是什么数据结构?
  • 如何查看MySQL的执行计划?
  • 索引失效的情况有哪些?
  • 什么是回表查询?
  • 什么是MVCC?
  • MySQL主从复制的原理是什么?
  • 主从复制之后的读写分离如何实现?
  • 数据库的分库分表如何实现?

日志

错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。

当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

该日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log 。查看日志位置：

show variables like '%log_error%'

二进制日志

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。

作用：①. 灾难时的数据恢复；②. MySQL的主从复制。在MySQL8版本中，默认二进制日志是开启着的，涉及到的参数如下：

show variables like '%log_bin%'

日志格式

MySql服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：

日志格式 含义 备注
STATEMENT 基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。
ROW 基于行的日志记录，记录的是每一行的数据变更。（默认）
MIXED 混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录。 
show variables like '%binlog_format%'

日志查看

由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具mysqlbinlog（直接在控制台执行）来查看，具体语法：

mysqlbinlog  [参数项] logfilename

参数选线：
	-d 指定数据库名称，只列出指定的数据库相关操作
	-o 忽略调日志的前n行
	-v 将事件（数据变更）重构为sql语句
	-w 将事件（数据变更）重构为sql语句，并输出注释信息

日志删除

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清楚，将会占用磁盘空间，可通过以下几种方式清理日志：

指令 含义
reset master 删除全部 binlog 日志，删除之后，日志编号，将从 binlog.000001重新开始
purge master logs to 'binlog.***********************' 删除 ******************编号之前的所有日志
purge master logs before 'yyyy-mm-dd hh 24:mi:ss' 删除日志为 "yyyy-mm-dd hh24:mi:ss" 之前产生的所有日志

也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。

show variables like '%binlog_expire_logs_seconds%';

查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下， 查询日志是未开启的。如果需要开启查询日志，可以设置以下配置 ：

show variables like '%general%';

修改MySQL的配置文件 /etc/my.cnf 文件，添加如下内容：

#该选项用来开启查询日志，可选值 ： 0或者1； 0代表关闭， 1代表开启
general_log=1
#设置日志的文件名 ， 如果没有指定， 默认的文件名为host_name.log
general_log_file=mysql_query.log

慢日志查询

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit的所有的SQL语句的日志，默认未开启。long_query_time 默认为 10 秒，最小为 0， 精度可以到微秒。

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

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

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和更改此行为 log_queries_not_using_indexes，如下所述。

#记录执行较慢的管理语句
log_slow_admin_statements=1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes=1

主从复制

主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从服务器中，然后从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步

MySQL 复制的有点主要包含以下三个方面：

  1. 主库出现问题，可以快速切换到从库提供服务。
  2. 实现读写分离，降低主库的访问压力。
  3. 可以在从库中执行备份，以避免备份期间影响主库服务。

原理

复制分成三步：

  1. Master主库在事务提交时，会把数据变更记录在二进制日志文件中Binlog中
  2. 从库读取主库的二进制日志文件Binlog，写入到从库的中继日志Relay Log
  3. slave重做中继日志中的事件，改变映射它为自己的数据

搭建

服务器准备

准备好两台服务器之后，在上述的两台服务器中分别安装好MySQL，并完成基础的初始化准备工作。

开放指定的3306端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

关闭服务器的防火墙
systemctl stop firewalld
systemctl disable firewalld

主库的配置

  1. 修改配置文件 /etc/my.cnf

    #mysql服务id，保证整个集群环境中唯一，取值范围：1-2^(32-1)，默认为1
server-id=1
#是否只读，1代表只读，0代表读写
read-only=0
#忽略的数据 指不需要同步的数据库
#binlog-ignore-db=mysql
#binlog-do-db=db01

  2. 重启mysql服务

    systemctl restart mysqld

  3. 登录MySQL，创建远程连接的账号，并授予主从复制权限

    #创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
create user 'itcast'@'%' identified with mysql_native_password by 'Root@123456';
#为itcast@%用户分配主从复制权限
grant replication slave on *.* to 'itcast'@'%';

  4. 通过指令，查看二进制日志坐标

    show master status;

    +-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| SZ-72185626D-bin.000087 |      477 |              |                  |                   |
+-------------------------+----------+--------------+------------------+-------------------+

字段含义说明：
	1. file：从哪个日志文件开始推送日志文件
	2. position：从哪个位置开始推送日志
	3. binlog_ignore_db : 指定不需要同步的数据库

从库的配置

1.修改配置文件/etc/my.cnf

#mysql服务id，保证整个集群环境中唯一，取值范围：1-2^(32-1)，和主库不一样即可
server-id=2
#是否只读，1代表只读，0代表读写
read-only=1

2.重启MySQL服务

systemctl restart mysqld

3.登录MySQL，设置主库配置

change replication source to source_host='192.168.10.102',source_user='itcast',source_password='Root@123456',source_log_file='binlog.000004',source_log_pos=1985;

上述是8.0.23中的语法，如果MySQL是8.0.23之前版本，执行如下SQL：

change master to master_host='192.168.10.102',master_user='itcast',master_password='Root@123456',master_log_file='binlog.000004',master_log_pos=743;
参数名 含义 8.0.23之前
source_host 主库IP地址 master_host
source_user 连接主库的用户名 master_user
source_password 连接主库的密码 master_password
source_log_file binlog日志文件名 master_log_file
source_log_pos binlog日志文件位置 master_log_pos

4.开启同步操作

start replica; #after 8.0.22
start slave; #before 8.0.22

5.查看主从同步状态

show replica status\G; #after 8.0.22
show slave status; #before 8.0.22

分库分表

介绍

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈：

  1. IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。 请求数据太多，带宽不够，网络IO瓶颈。

  2. CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。

策略：

  • 垂直分库：以表为依据，根据业务将不同表拆分到不同的库中
    • 每个库的表结构都不一样
    • 每个库的数据也不一样
    • 所有库的并集是全量数据
  • 垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中
    • 每个表的结构都不一样
    • 每个表的数据也不一样，一般通过一列（主键/外键）关联
    • 所有表的并集是全量数据
  • 水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中
    • 每个库的表结构都一样
    • 每个库的数据都不一样
    • 所有库的并集是全量数据
  • 水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中
    • 每个表的表结构都一样
    • 每个表的数据都不一样
    • 所有表的并集是全量数据

实现技术

shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言，性能较高。

MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。

MyCat概述

mycat是开源的，活跃的，基于java语言编写的mysql数据库中间件，可以像使用mysql一样来使用mycat，对于开发人员来说根本感觉不到mycat的存在

优势:

  • 性能可靠稳定
  • 强大的技术团队
  • 体系完善
  • 社区活跃

下载地址：http://dl.mycat.org.cn/

安装

Mycat是采用java语言开发的开源的数据库中间件，支持Windows和Linux运行环境，下面介绍MyCat的Linux中的环境搭建。我们需要在准备好的服务器中安装如下软件。

服务器 安装软件 说明
192.168.10.100 JDK、MyCat MyCat中间件服务器
192.168.10.102 MySQL 分片服务器
192.168.10.103 MySQL 分片服务器
192.168.10.104 MySQL 分片服务器
mycat目录结构

bin : 存放可执行文件，用于启动停止mycat

conf：存放mycat的配置文件

lib：存放mycat的项目依赖包（jar）

logs：存放mycat的日志文件

Mycat入门

需求

由于tb_order表中数据量很大，磁盘IO及容量都达到了瓶颈，现在需要对tb_order表进行数据切片，分为三个数据节点，每个节点位于不同的服务器上

分片配置( schema.xml)
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<!--逻辑库-->
	<schema name="dcp" checkSQLschema="true" sqlMaxLimit="100">
		<!--逻辑表
		rule：分片规则
		插入数据在dataNode节点进行分布，取决于rule
		引用rule.xml值
		-->
		<table name="tb_order" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	</schema>

	<dataNode name="dn1" dataHost="dhost1" database="dcp" />
	<dataNode name="dn2" dataHost="dhost2" database="dcp" />
	<dataNode name="dn3" dataHost="dhost3" database="dcp" />

	<!--节点主机
		dbDriver：native (MySQL8.0不支持)、jdbc
	-->
	<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<!--数据库连接信息-->
		<writeHost host="master" url="jdbc:mysql://192.168.10.102:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root"
				   password="1234" />
	</dataHost>
	
	<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<!--数据库连接信息-->
		<writeHost host="master" url="jdbc:mysql://192.168.10.103:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root"
				   password="1234" />
	</dataHost>
	
	<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<!--数据库连接信息-->
		<writeHost host="master" url="jdbc:mysql://192.168.10.104:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root"
				   password="1234" />
	</dataHost>
</mycat:schema>

配置mycat的用户及用户的权限信息server.xml

<user name="root" defaultAccount="true">
		<property name="password">1234</property>
		<!-- 访问的逻辑库 -->
		<property name="schemas">dcp</property>
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>

	<user name="user">
		<property name="password">1234</property>
		<property name="schemas">dcp</property>
		<property name="readOnly">true</property>
	</user>
启动服务

切换到Mycat的安装目录，执行如下指令，启动Mycat：

#启动
bin/mycat start
#停止
bin/mycat stop

Mycat启动之后，占用端口号 8066。

启动完毕之后，可以查看logs目录下的启动日志，查看Mycat是否启动完成。

分片测试

通过如下指令，就可以连接并登陆MyCat。

mysql -h 192.168.200.210 -P 8066 -uroot -p123456

然后就可以在MyCat中来创建表，并往表结构中插入数据，查看数据在MySQL中的分布情况。

CREATE TABLE tb_order (
	id BIGINT (20) PRIMARY KEY NOT NULL auto_increment,
	title VARCHAR (100) NOT NULL,
	PRIMARY KEY (id)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
INSERT INTO tb_order(id, title) VALUES(1, 'goods1');
INSERT INTO tb_order(id, title) VALUES(1, 'goods2');
INSERT INTO tb_order(id, title) VALUES(1, 'goods3');
INSERT INTO tb_order(id, title) VALUES(1000000, 'goods1000000');
INSERT INTO tb_order(id, title) VALUES(10000000, 'goods10000000');
Mycat配置

schema.xml

schema.xml 作为MyCat中最重要的配置文件之一 , 涵盖了MyCat的逻辑库 、 逻辑表 、 分片规则、分片节点及数据源的配置。

主要包含以下三组标签：

➢ schema标签

➢ datanode标签

➢ datahost标签

➢ schema标签

schema 标签用于定义 MyCat实例中的逻辑库 , 一个MyCat实例中, 可以有多个逻辑库 , 可以通过 schema 标签来划分不同的逻辑库。

MyCat中的逻辑库的概念 ， 等同于MySQL中的database概念 , 需要操作某个逻辑库下的表时, 也需要切换逻辑库(use xxx)。

核心属性：
name：指定自定义的逻辑库库名
checkSQLschema：在SQL预计操作时指定了数据库名称，执行时是否自动去除；true：自动去除；false：不自动去除
sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录

➢ schema标签(table)

table 标签定义了MyCat中逻辑库schema下的逻辑表 , 所有需要拆分的表都需要在table标签中定义

核心属性：
name：定义逻辑表表名，在该逻辑库下唯一
dataNode：定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应
rule：分片规则的名字，分片规则名字是在rule.xml中定义的
primaryKey：逻辑表对应真实表的主键
type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为global

➢ dataNode标签

<dataNode name="dn1" dataHost="dhost1" database="db01"/>

dataNode标签中定义了MyCat中的数据节点, 也就是我们通常说的数据分片。一个dataNode标签就是一个独立的数据分片。

核心属性：
name：定义数据节点名称
dataHost：数据库实例主机名称，引用自dataHost标签中的name属性
database：定义分片所属数据库

➢ dataHost标签

<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc">
    <heartbeat>select user()</heartbeat>
    <writeHost host="master" url="jdbc:mysql://192.168.200.210:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234">
    </writeHost>
</dataHost>

该标签在MyCat逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。

核心属性：
name：唯一标识，供上层标签使用
maxCon/minCon：最大连接数/最小连接数
balance：负载均衡策略，取值0，1，2，3
writeType：写操作分发方式（
	0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；
	1：写操作随机分发到配置的writeHost）
dbDriver：数据库驱动，支持native、jdbc

Mysql体系结构

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

存储引擎简介

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

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

create table 表名(
    字段1 字段1类型 [comment 字段1注释],
    ...
    字段n 字段n类型 [comment 字段n注释],
)engine=innodb [comment 表注释];

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

show engines;

存储引擎的特点

  • InnoDB：是一种兼顾高可靠性和高性能的通用存储引擎。
    • DML操作遵循ACID模型，支持事务
    • 行级锁，提高并发访问性能
    • 支持外键 foreign key 约束，保证数据的完整性和正确性
  • MyISAM：
    • 不支持事务，不支持外键
    • 支持表锁，不支持行锁
    • 访问速度快
  • Memory：引擎的表数据是存储在内存中的，由于收到硬件问题，或断电问题的影响，只能将这些表作为临时表或缓存使用
    • 内存存放
    • hash索引（默认）

存储引擎选择

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

索引

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

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

  • 二叉树

    • 二叉树缺点：顺序插入式，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢
    • 红黑树：大数据量情况下，层级较深，检索速度慢

  • B-Tree（多路平衡查找树）

  • B+Tree

    • 相对于B-Tree区别:

      ①. 所有的数据都会出现在叶子节点

      ②. 叶子节点形成一个单向链表

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

SQL性能分析

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

show global status like 'Com_______'; #7个_

  • 慢日志查询

  • profile详情

    show profiles能够在做sql优化时帮助我们了解时间都耗费到了哪里。通过have_profiling参数，能够看到当前Mysql是否支持

    profile操作：

    select @@have_profiling;

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

    set profiling = 1;

    执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

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

  • explain执行计划

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

    语法：

    #直接在select语句之前加上关键字explain/desc
explain select 字段列表 from 表名 where 条件;

    各字段含义：

    • id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。
    • select_type：表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
    • type：表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。
    • possible_key：显示可能应用在这张表上的索引，一个或多个。

  • EXPLAIN 执行计划各字段含义：

    ➢ Key

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

    ➢ Key_len

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

    ➢ rows

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

    ➢ filtered

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

索引设计原则

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

SQL优化

插入数据

  • insert优化

    insert into tb_test values(1, '朱厚照');
insert into tb_test values(2, '朱允炆');

    手动提交事务

    start transaction;
insert into tb_test values(1, '朱厚照'),(3, '朱百六');
insert into tb_test values(2, '朱允炆'),(4, '朱初一');
commit;

    大批量插入数据

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

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

    主键顺序插入性能高于乱序插入

  • 主键优化

    主键设计原则

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

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

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

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

  • order by 优化

    using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。

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

    #没有创建索引时，根据age,phone进行排序
explain select id, age, phone from tb_user order by age, phone;
#创建索引
create index idx_user_age_phone_aa on tb_user(age, phone);
#创建索引后，根据age, phone进行升序排序
explain select id, age, phone from tb_user order by age,phone;
#创建索引后，根据age, phone进行降序排序
explain select id, age, phone from tb_user order by age desc, phone desc;
#根据age, phone进行降序一个升序，一个降序
explain select id, age, phone from tb_user order by age asc , phone desc;
#创建索引
create index idx_user_age_phone_ad on tb_user(age asc, phone desc);
#根据age, phone进行降序一个升序，一个降序
explain select id, age, phone from tb_user order by age asc , phone desc;

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

    ➢ 尽量使用覆盖索引。

    ➢ 多字段排序 一个升序一个降序，此时需要注意联合索引在创建时的规则（ asc/desc）。

    ➢ 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)

  • group by 优化

    #删除掉目前的联合索引idx_user_pro_age_sta
drop index idx_user_pro_age_sta on tb_user;
#执行分组操作，根据profession字段分组
explain select profession,count(*) from tb_user group by profession;
#创建索引
create index idx_user_pro_age_sta on tb_user(profession, age, status);
#执行分组操作，根据profession字段分组
explain select profession,count(*) from tb_user group by profession;
#执行分组操作，根据profession字段分组
explain select profession,count(*) from tb_user group by profession, age;

    在分组操作时，可以通过索引来提高效率

    分组操作时，索引的使用也是可以满足最左前缀法则的

  • limit优化

    一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

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

    explain select * from tb_sku t, (select id from tb_sku order by id limit 2000000, 10) a where t.id=a.id

视图/存储过程/触发器

视图

是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图是动态生成的。（通俗的讲，视图只保存了查询的sql逻辑，不保存查询结果）

创建视图

create [or replace] view 视图名称[(列表名称)] as select语句 [with [cascaded | local] check option]

查询

查看创建视图语句：show create view 视图名称
查看视图数据：select * from 视图名称...;

修改

方式一：create [or replace] view 视图名称[(列名列表)] as select 语句 [with [cascaded | local] check option] 
方式二：alter view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]

删除

drop view [if exists] 视图名称[,视图名称]...

存储过程

是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

  • 特点：

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

  • 创建

    create procedure 存储过程名称([参数列表])
begin 
    -- sql语句
end;

  • 调用

    call 名称([参数]);

  • 查看

    select * from information_schema.ROUTINES where ROUTINE_SCHEMA='xxx';-- 查询指定数据库的存储过程及状态信息
show create procedure 存储过程名称; -- 查询某个存储过程的定义

  • 删除

    drop procedure [if exists] 存储过程名称;

锁

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

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

  1. 全局锁：锁定数据库中的所有表。
  2. 表级锁：每次操作锁住整张表。
  3. 行级锁：每次操作锁住对应的行数据。

全局锁

对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句DDL语句，以及更新操作的事务都将被阻塞。

数据库中加全局锁，是一个比较重的操作，存在以下问题： 1. 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。 2. 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。 在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。

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

表级锁

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

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

  1. 表锁 2. 元数据锁（meta data lock，MDL） 3. 意向锁

⚫ 表锁 对于表锁，分为两类：

  1. 表共享读锁（read lock）
  2. 表独占写锁（write lock） 语法：
  3. 加锁：lock tables 表名... read/write。
  4. 释放锁：unlock tables / 客户端断开连接 。

读锁不会阻塞其他客户端的读，但是会阻塞写。

写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

⚫ 元数据锁（ meta data lock， MDL） MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表 上有活动事务的时候，不可以对元数据进行写入操作。 为了避免DML与DDL冲突，保证读写的正确性。

行锁

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

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

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

SQL 行锁类型 说明
INSERT ... 排他锁 自动加锁
UPDATE ... 排他锁 自动加锁
DELETE ... 排他锁 自动加锁
SELECT（正常） 不加任何锁
SELECT ... LOCK IN SHARE MODE 共享锁 需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT ... FOR UPDATE 排他锁 需要手动在SELECT之后加FOR UPDATE

MVCC

⚫ 当前读 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如： select ... lock in share mode(共享锁)，select ... for update、update、insert、delete(排他锁)都是一种当前读。

⚫ 快照读 简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。 • Read Committed：每次select，都生成一个快照读。 • Repeatable Read：开启事务后第一个select语句才是快照读的地方。 • Serializable：快照读会退化为当前读。

⚫ MVCC 全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现 MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

MVCC-实现原理

⚫ undo log 回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。 当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。 而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。 ⚫ undo log版本链

不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。 ⚫ readview ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的)id。 ReadView中包含了四个核心字段：

字段 含义
m_ids 当前活跃的事务ID集合
min_trx_id 最小活跃事务ID
max_trx_id 预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id ReadView创建者的事务ID

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

➢ READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。

➢ REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。 RC隔离级别下，在事务中每一次执行快照读时生成ReadView。
https://github.com/AlibabaP8Developer/knowledge/blob/master/docs/database/mysql1/Mysql面试题.md

posted @ 2025-06-17 22:28  小郑[努力版]  阅读(21)  评论(0)    收藏  举报