MySQL

面试题

<=>和=的区别

如果null也返回true，但是=返回false

MySQL 对于千万级的大表要怎么优化

千万级数据导致的问题可能是查询数据或者写数据特别慢。
第一，性价比最高，sql优化，使用索引，定位慢sql（mysqldumpslow、Percona），使用explain分析，主要看type，如果没走索引的话，就在where、join、order by用到的字段创建索引，尽量使用覆盖索引，避免回表。
第二加缓存redis；
冷热数据分离（优先推荐，成本远低于分库分表）
读写分离、
分区、分库分表。

分库分表分区

分区：将一张表文件按照指定规则(时间、id)拆分成多个小文件，表数据量特别大时，查询插入都会越来越慢，并且像那种一般只查询近几年的数据，就可以按照时间分区，删除也可以按照分区删除。分区键必须作为主键的一部分或者唯一索引。业务查询要带上分区键，否则查询所有文件。
ALTER TABLE order ADD PARTITION (
PARTITION p2027 VALUES LESS THAN (2028)
);
ALTER TABLE order DROP PARTITION p2023;
分表：垂直分表（按列拆分，id关联）和水平分表（按数据行拆分，将数据按照规则分到多张表，Sharding-JDBC），垂直分表可以把「高频核心字段」和「低频大字段」拆开

分库：数据库访问量特别大，可以使用分库。

mysql死

锁排查

隔离级别

读未提交：
读已提交：
可重复度：
序列化读：

存储引擎

负责对存储在磁盘上表文件中的数据进行提取和写入。

InnoDB

版本大于等于5.5之后，默认采用InnoDB引擎。
支持事务、支持行级锁、支持外键。
InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较高 ，而且内存大小对性能有决定性影响。

MyISAM

崩溃后无法安全恢复。
优势是访问的速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用。
适用于只读应用或者以读为主的业务。
MyISAM只缓存索引，不缓存真实数据。

视图

控制数据的访问。
对视图中的数据进行增删改操作，会影响原表数据。

数据类型

整数

有TINYINT、SMAILINT、INT、BIGINT，
INT的取值范围，有符号是20亿，无符号是40亿。
推荐 INT

小数

DECIMAL(M,D)
DECIMAL(5,2)代表长度一共有5为，小数位有2位，取值范围为-999.99~999.99

时间

DATETIME：YYYY-MM-DD HH:MM:SS
DATE：YYYY-MM-DD
YEAR：YYYY
TIME：HH:MM:SS

字符串

CHAR：定长
VARCHAR：非定长，长度不要超过5000
TEXT：大文本，推荐使用单独一张表存，否则会影响查询效率，删除时也会导致空洞，使得文件碎片增多。

自带数据库TODO

mysql

存储MySQL服务器运行所需要的各种信息。
存储了MySQL的用户信息、权限信息、存储过程、慢查询日志等。

information_schema

存储MySQL服务器其他数据库的信息 。
存储了所有的表信息、列信息、索引信息。
TODO:JRB
表名 作用 描述
INNODB_LOCKS 存储InnoDB引擎事务中正在请求的且并未获得的且同时阻塞了其他事务的锁信息 即没有发生不同事务之间的锁等待的锁信息，在这里是查看不到的。该表中的内容可以用于诊断高并发下的锁争用信息。
查询该表的用户需要具有process权限
INNODB_TRX 存储InnoDB引擎中执行的每个事务（不包括只读事务）的信息 包括事务是否正在等待锁、事务什么时间点开始、以及事务正在执行的SQL语句文本信息等信息
查询该表的用户需要具有process权限

performance_schema

这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以用来 监控 MySQL 服务的各类性能指标 。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。

sys

MySQL 系统自带的数据库，这个数据库主要是通过 视图 的形式把 information_schema 和performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。

用户权限角色管理

分为用户、权限、角色，角色拥有指定的权限，可以给用户直接指定指定的权限，也可以通过给用户分配角色从而分配权限。

函数

分为聚合函数和单行函数，
聚合函数主要有 sum()、count()、avg()、max()、min()
单行函数分为数值函数、字符串函数、时间函数、其他函数。

数值函数

ROUND(x,y)

四舍五入

SELECT ROUND(22.1); -- 22
SELECT ROUND(22.123,2); -- 22.12

CEIL(x)

向上取整

SELECT CEIL(12.12); -- 13

FLOOR(x)

向下取整

select FLOOR(12.13); -- 12

字符串函数

CONCAT(x,col1,col2,…)

SELECT CONCAT('-',stu_name,stu_age) FROM b; -- -zs18

CONCAT_WS(x,col1,col2,…)

SELECT CONCAT_WS('-',stu_name,stu_age) FROM b; -- zs-18

TRIM(col,str1,str2)

去掉前后空格

SELECT TRIM(stu_name) FROM b;

时间函数

SELECT CURDATE(); -- 2025-04-06
SELECT CURTIME(); -- 09:02:02
SELECT NOW(); -- 2025-04-06 09:02:34
SELECT SYSDATE(); -- 2025-04-06 09:03:00
SELECT YEAR('2025-04-06 09:02:34'); -- 2025
SELECT MONTH('2025-04-06 09:02:34'); -- 4
SELECT DAY('2025-04-06 09:02:34'); -- 6
SELECT HOUR('2025-04-06 09:02:34'); -- 9
SELECT MINUTE('2025-04-06 09:02:34'); -- 2
SELECT SECOND('2025-04-06 09:02:34'); -- 34

DATE_FORMAT()
STR_TO_DATE()

其他函数

IFNULL(col,str)

select IFNULL(num,18) from study_mysql;

IF(boolean,col1,col2)

SELECT if(num>18,'成年','未成年') FROM study_mysql;

case when

SELECT stu_name,stu_age,
case when stu_age > 30 then '30多了'
when stu_age > 40 then '40多了'
ELSE '50多了' END as "detail"
FROM student1 

SELECT stu_name,stu_age,
case  stu_age when 58 then '58了'
when 28 then '28了'
ELSE 'abc' END as "detail"
FROM student1 

between … and …

在… 和… 之间的数据，包含边界

union all

union 两个select关联后的并集，会去重；
union all 两个select关联后的并集，不会去重(推荐)。

exists、not exists

exists：外层一条条遍历，里层查到数据时，外层就会展示，否则不展示。

存储过程

将一组SQL语句封装为存储过程，保存在MYSQL服务器，客户端可以对其进行调用。
创建存储过程：

DELIMITER $
CREATE PROCEDURE first_ccgc()
BEGIN
	SELECT * FROM student1;
END $
DELIMITER ;

调用存储过程

CALL first_ccgc

创建存储过程：

DELIMITER $
CREATE PROCEDURE three_ccgc(IN stuName VARCHAR(100))
BEGIN
	SELECT stu_age FROM student1 WHERE stu_name = stuName;
END $
DELIMITER ;

调用存储过程

CALL three_ccgc('姜双');

创建存储过程：

DELIMITER $
CREATE PROCEDURE four_ccgc(IN stuName VARCHAR(100),OUT stuAge INT)
BEGIN
	SELECT stu_age INTO stuAge FROM student1 WHERE stu_name = stuName;
END $
DELIMITER ;

调用存储过程

CALL four_ccgc('姜双',@abc);
SELECT @abc;

创建存储过程：

DELIMITER $
CREATE PROCEDURE five_ccgc(INOUT stuAge INT)
BEGIN
	SELECT stu_age INTO stuAge FROM student1 WHERE stu_age = stuAge;
END $
DELIMITER ;

调用存储过程

SET @def='28'
CALL five_ccgc(@def);
SELECT @def;

IF

DELIMITER //
create procedure aof()
BEGIN 
	DECLARE age INT DEFAULT 20;
	IF age > 40
		then SELECT '中老年';
	ELSEIF age > 18
		then SELECT '青少年';
	ELSEIF age > 8
		then SELECT '少年';
	ELSE
		SELECT '婴幼儿';
	END IF;
END //
DELIMITER ;

-- 青少年
CALL aof;

case

DELIMITER //
create procedure aof()
BEGIN 
	DECLARE var INT DEFAULT 2;
	CASE var
		WHEN 1 THEN SELECT 'var=1';
		WHEN 2 THEN SELECT 'var=2';
		WHEN 3 THEN SELECT 'var=3';
		ELSE SELECT 'other value';
	END CASE;
			
END //
DELIMITER ;

-- var=2
CALL aof;

loop

while

repeat

leave

类似于break，跳出循环。

iterate

类似于continue。

存储函数

SET GLOBAL log_bin_trust_function_creators = 1;

创建存储函数

DELIMITER //
CREATE FUNCTION first_fun()
RETURNS INT
BEGIN
	RETURN (SELECT stu_age FROM student1 WHERE stu_name = '姜双');
END //
DELIMITER ;

调用存储函数

SELECT first_fun();

触发器

优点：
在应用层外确保数据的完整性；
可以记录日志；
缺点：
可读性差。由于在应用层外控制的，所以可能在往某个表插入数据的时候，出现了其他表操作的错误，而又无法在代码中定位到。

变量

全局变量

针对于所有会话（连接）有效，但不能跨重启
查看所有全局变量：SHOW GLOBAL VARIABLES;
查看满足条件的：SHOW GLOBAL VARIABLES LIKE ‘admin_%’;
查看指定系统变量：SELECT @@admin_port
修改变量值：SET GLOBAL 变量名=变量值;
全局系统变量针对于所有会话（连接）有效，但 不能跨重启
设置全局变量的持久化：

会话(local)变量

仅针对于当前会话（连接）有效。
查看所有会话变量：SHOW SESSION VARIABLES;
修改变量值：SET SESSION 变量名=变量值;

用户变量

包括会话用户变量（作用域和会话变量一样，只对当前连接会话有效）和局部变量（只在 BEGIN 和 END 语句块中有效，局部变量只能在存储过程和函数中使用）。
会话用户变量值：
方式一：SET @用户变量 = 值;
方式二：SELECT 表达式 INTO @用户变量 [FROM 等子句];
例子：SELECT AVG(salary) INTO @avgsalary FROM employees;
查询变量值：select @avgsalary
局部变量：

逻辑架构

1、连接MySQL服务端

客户端（如JDBC）访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接，经过三次握手建立连接成功后， MySQL 服务器会对用户名密码以及权限进行校验。

2、查询缓存

8.0版本无。
查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。
缓存的命中率极低。

3、解析器解析

先词法分析再语法分析。
词法分析是指分析这条 SQL 语句中的每个单词、字符分别是什么，代表什么，如从 select 这个关键字识别出来，这是一个查询语句，把字符串“ID”识别成“列 ID”。
语法分析是指根据语法规则，判断输入的这个 SQL 语句是否 满足 MySQL 语法，如果分析完都正确，则会生成一个语法树。

4、优化器优化

在优化器中会确定 SQL 语句的执行路径，比如是根据 全表检索 ，还是根据 索引检索 等，最终生成SQL语句的执行计划。

5、执行器执行

按照优化器的执行计划中的步骤调用存储引擎提供的方法访问磁盘上的表数据，将最终结果返回客户端。

窗口函数

窗口函数类似于group by，对数据进行分组，但是函数结果不同于group by每组只返一条，而是多条。

CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
category_id INT,
category VARCHAR(15),
NAME VARCHAR(30),
price DECIMAL(10,2),
stock INT,
upper_time DATETIME
);
INSERT INTO goods(category_id,category,NAME,price,stock,upper_time)
VALUES
(1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00'),
(2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00'),
(2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00'),
(2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
(2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00'),
(2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00'),
(2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00');

序号函数

ROW_NUMBER()

对表中的数据进行排序展示，顺序列从1开始。
例子：查询goods数据表中每个商品分类下价格最高的三种商品信息。

SELECT * FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
id,category_id,category,NAME,price,stock
FROM goods
) t
WHERE row_num <=3

RANK()

使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3
举例：使用RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的2款商品信息。

SELECT * FROM (
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
id,category_id,category,NAME,price,stock
FROM goods
) t
WHERE category_id = 1 and row_num <3

DENSE_RANK()

DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号，比如序号为1、1、2
举例：使用DENSE_RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。

SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
id,category_id,category,NAME,price,stock
FROM goods

分布函数

PERCENT_RANK()

举例：计算 goods 数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值

#写法一：
SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,
PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1;
#写法二：
SELECT RANK() OVER w AS r,
PERCENT_RANK() OVER w AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);

CUME_DIST()

主要用于查询小于或等于某个值的比例。
举例：查询goods数据表中小于或等于当前价格的比例。

SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,
id, category, NAME, price
FROM goods;

前后函数

LAG(expr,n)

例子：以商品类型分组、价格升序排序，查询每个商品以及该商品的上n个商品价格(可以计算差值)。

SELECT id, category, NAME, 
price,LAG(price,1) OVER (PARTITION BY category_id ORDER BY price) AS pre_price
FROM goods

举例：查询goods数据表中前一个商品价格与当前商品价格的差值。

SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
FROM (SELECT id, category, NAME, price,LAG(price,1) OVER (PARTITION BY category_id ORDER BY price) AS pre_price
FROM goods) t

LEAD(expr,n)

与LAG(expr,n)相反。

首尾函数

FIRST_VALUE(expr)

返回第一个值

SELECT id, category, NAME, price, stock,
FIRST_VALUE(price) OVER (PARTITION BY category_id ORDER BY price) AS first_price
FROM goods; 

LAST_VALUE(expr)

返回最后一个expr的值

SELECT id, category, NAME, price, stock,
LAST_VALUE(price) OVER (PARTITION BY category_id ORDER BY price) AS last_val
FROM goods; 

十、InnoDB索引

索引是排好序的快速查找数据结构，提高数据的检索效率，降低数据库的IO成本。
索引缺点：
1）空间：索引也需要占磁盘空间，每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页（mysql中物理磁盘与内存进行IO交互的最小单位，即一次IO操作就是将物理磁盘中的一个数据页加载到内存中），一个页默认会占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大一片存储空间。
2）时间：降低更新表的速度，对表中数据进行修改时，索引也要动态地维护，需要去修改各个B+树索引。

1、目录项

为了快速的定位到需要查找的记录在哪些数据页，为每一个数据页建立一个目录项（前提是下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值）
其中每个目录项记录某个数据页以及该页的最小key。

2、迭代1次：记录目录项的目录项页

不记录表中所有数据，记录每个数据页的页数与最小主键值。
目录项记录只有 主键值和页的编号 两个列。
此时现在以查找主键为 20 的记录为例：
1）先到目录项页中通过 二分法 快速定位到对应目录项，因为 12 < 20 <209 ，定位到数据页9。
2）再到数据页9中根据 二分法 快速定位到主键值为 20 的行记录。

迭代2次：多个目录项页

假设每个目录项页只能记录四个目录页，随着数据的增多，也需要建立多个目录项页。
测试现在以查找主键值为 20 的记录为例：
1）根据二分法快速定位到指定的目录项页
2）先到目录项页中通过 二分法 快速定位到对应目录项，因为 12 < 20 <209 ，定位到数据页9。
3）再到数据页9中根据 二分法 快速定位到主键值为 20 的行记录。

迭代3次：记录目录项页的目录页

1）定位具体的目录页
2）根据二分法快速定位到指定的目录项页
3）先到目录项页中通过 二分法 快速定位到对应目录项，因为 12 < 20 <209 ，定位到数据页9。
4）再到数据页9中根据 二分法 快速定位到主键值为 20 的行记录。

引出B+Tree

B+Tree最下边的那层用来存放表中的具体数据，为第 0 层。
一般情况下，用到的B+树都不会超过4层。
目录页位置万年不动，且会一直在内存中
B+树的构建是自上往下构建的
保证每个目录项页中的每个数据页的唯一性（如果两个数据页的最小索引列的列值相等，目录项页中的每个数据页再保存主键，从而确保每个数据页的唯一性）
为了提高查询效率，就需要 减少磁盘IO数 。为了减少磁盘IO的次数，就需要尽量 降低树的高度 ，需要把原来“瘦高”的树结构变的“矮胖”，树的每层的分叉越多越好。
B树的非叶子节点也存放数据，所以层级越多。

聚簇索引与非聚簇索引

聚簇索引

特点：
1.使用主键值的大小来进行记录和页的排序
1）每个数据页内的记录是按照主键的大小顺序排成一个单向链表 。
2）不同的数据页根据页中数据的最小主键的大小顺序排成一个双向链表 。
3）不同的目录项页也是根据数据页中的最小主键的大小顺序排成一个双向链表 。
2.只有B+树的叶子节点（第0层）存储的是完整的表数据（所有列值包括隐藏列）。
优点：
1.数据访问更快 ，聚簇索引是索引和数据同时保存在B+树中，可以直接从B+Tree中获取数据。
2.对于主键的 order by 与 group by 的查询速度非常快（IO操作少）
缺点：
1.插入速度严重依赖于插入顺序，如果主键ID不是自增的，那插入数据时可能因造成原来数据页的变动。

非聚簇索引

特点：
1.使用非主键的列来进行记录和页的排序
2.叶子节点只保存索引的列跟主键的值。
回表 根据以非主键列大小排序的B+树只能确定要查找记录的主键值，如果要根据该列的值查找到完整的行记录，仍然需要到 聚簇索引中再查一遍，这个过程称为回表 。也就是根据该列的值查询一条完整的行记录需要使用到 2 棵B+树。

索引分类

主键索引、唯一索引、普通索引、组合索引

哪些情况适合创建索引

有唯一要求的列

频繁作为where查询条件的列

经常 GROUP BY 和 ORDER BY 的列

需要去重(distinct)的列

关联查询时(join)，关联字段创建索引，且字段类型要一致

SQL执行性能分析

开启慢查询日志参数

set global slow_query_log=‘ON’;
show variables like ‘%slow_query_log%’;
修改慢查询时间阈值：
#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并
执行下述语句
mysql > set global long_query_time = 1;
mysql> show global variables like ‘%long_query_time%’;
mysql> set long_query_time=1;
mysql> show variables like ‘%long_query_time%’;

SHOW GLOBAL STATUS LIKE ‘%Slow_queries%’;
查看慢查询sql：mysqldumpslow

SHOW VARIABLES LIKE ‘slow_query_log_file’; – 日志文件路径

explain分析查询语句

查看SQL语句的执行计划。
explain select name from student;
二、key_len
实际使用的索引的长度，单位是字节。可以帮助你检查是否充分利用了索引，主要针对联合索引具有一定的参考，对同一索引来说，key_len 值越大越好。
四、rows
预估的需要读取的记录条目数，条目数越小越好。这是因为值越小，加载I/O的页数就越少。
六、type：
对某个表执行查询时的访问方法。
SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts 级别（阿里巴巴开发手册要求）
system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL。
system：
当表中只有一条记录，并且该表中存储引擎统计数据是精确的，比如 MYISAM，Memory。
const：
对主键索引与唯一索引进行等值匹配：SELECT SQL_NO_CACHE * FROM student_info where id =833244 ;
eq_ref：
在进行连接查询时，如果被驱动表是通过主键或者唯一二级索引等值匹配的方式进行查询的，那么被驱动表的访问方式是 eq_ref。
select * from t1 join t2 on t1.id=t2.id; # 由于t2的id是主键索引，所以t2表的执行查询的访问方法是eq_ref。
ref：
当使用普通的二级索引与常量进行等值匹配时，type 是 ref。
EXPLAIN SELECT SQL_NO_CACHE * FROM student_info where class_id =10084 ;
ref_or_null：
当使用普通的二级索引进行等值匹配时，当索引值可以是 Null 时，type 是 ref_or_null。
EXPLAIN SELECT SQL_NO_CACHE * FROM student_info where class_id =10084 or class_id is null;
index_merge：
当进行单表访问时，如果多个查询字段分别建立了单列索引，使用 OR 连接，其访问类型是 index_merge。
EXPLAIN SELECT SQL_NO_CACHE * FROM student_info where class_id =10084 or course_id = 2323;
range：
使用索引获取某些范围区间的记录。
EXPLAIN SELECT SQL_NO_CACHE * FROM student_info where class_id in (1,2,3);
EXPLAIN SELECT SQL_NO_CACHE * FROM student_info where class_id between 1 and 10;
index：
当使用索引覆盖，但是需要扫描的全部的索引记录时，该表的访问方式就是 index。
在查找数据时可以索引的列（select 后面只包含索引的列和主键列），而不用进行回表操作，这种情况即索引覆盖。
all：
全表扫描。

索引失效

索引失效

1.函数运算
2.类型装换
3.通过索引查询到的数据超过总数的三分之一吧，也会放弃索引（回表太多次不如直接全表）
4.is not null 与 != 判断
5.like 以通配符 % 开头索引失效
6.OR 前后存在非索引的列，索引失效
7.联合索引时，如果某一列是范围条件查询（大于小于这种），后面的列无法使用索引；
联合索引时，遵循最左匹配原则，联合索引中的第二个字段无法直接使用该联合索引
8.is null可以使用索引，is not null无法使用索引

SQL优化

join优化

驱动表是连接操作中首先被访问的表，MySQL 会对驱动表执行完整的扫描（全表扫描或索引扫描），并为每一行数据去匹配被驱动表中的记录。驱动表的行数直接影响连接操作的次数（例如，驱动表有 100 行，被驱动表有 1000 行，则可能需要执行 100×1000 次匹配）。
通常选择数据量较小或过滤后结果集更小的表作为驱动表，以减少总匹配次数。
LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以 右边是我们的关键点,一定需要建立索引 。
join 语句原理：
实现方式一：简单嵌套循环连接
从驱动表（小表）取出一条数据，跟被驱动表（大表）中的所有元素比较，大表IO次数过多。
实现方式二：索引嵌套循环连接
使用被驱动表的on条件上的列的索引去匹配其驱动表的数据。
实现方式三：快嵌套循环连接
将驱动表 join 相关的部分数据列（大小受 join buffer 的限制）缓存到 join buffer 中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和 join buffer 中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了被动表的访问频率。

避免使用子查询

因为MySQL 需要为子查询结果建立一个临时表 ，查询完毕后，再撤销这些临时表 。这样会消耗过多的 CPU 和 IO 资源。可以使用连接（JOIN）查询来替代子查询
尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

排序操作尽量在代码中实现

在 MySQL 中，支持两种排序方式，分别是 FileSort 和 Index 排序。

尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。

减少使用 order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct 这些语句较为耗费 CPU，数据库的 CPU 资源是极其宝贵的。

使用覆盖索引

select 后面的列仅包含索引列、主键、聚合函数，不含其他数据列，此时索引就为覆盖索引，不用进行回表操作。

优化分页查询

三范式

一范式：每列值具有原子性（地址可以拆分省市区）

二范式：非主键属性只依赖主键(分数不能放到学生表里)

三范式：非主键之间不能有关联关系

事务

多个操作，同时成功、同时失败
原子性：同时成功、同时失败
一致性：事务中的某一步操作失败，返回事务之前的状态
隔离性：事务之间相互隔离
持久性：事务提交后，数据持久化

Fd3600f17dcf647fbb9abb844278581cb.png&pos_id=img-ouBXYIWp-1761574456133)

MVCC （Multiversion Concurrency Control），多版本并发控制 TODO:JRB

行锁

需要放在事务中。select … for update 其他事务不能修改、删除。

索引下推

通过explain Extra列中出现Using index condition表示 索引下推(ICP) 已生效。
对于复合索引，查询语句中两个索引列都用到了，筛选后再回表，表示用到了索引下推，可以大幅较少回表次数，从而减少IO次数。
如果复合索引只用到了一个，然后回表，再对第二个列进行筛选，就没用到复合索引。

订单表数据量越来越大导致查询缓慢, 如何处理

PS

drop table、alter table、create table都会自动执行一次commit，事务就不好用了。

sql_mode：

宽松模式： 插入数据的时候，即便是给了一个错误的数据，也可能会被接受，并且不报错，主要用于数据库数据的迁移。

严格模式： 在生产等环境中，我们必须采用的是严格模式，进而 开发、测试环境 的数据库也必须要设置。
定义条件与处理程序
自测：
update student1 set stu_age = null where id = 6;这个sql会报1048错误，我们让他继续执行

DELIMITER //
create procedure kkkkk()
BEGIN 
    DECLARE CONTINUE HANDLER FOR 1048 SET @prc_value = -2;
	 SET @X = 1;
    update student1 set stu_age = null where id = 6;
    SET @X = 2;
    update student1 set stu_age = 17 where id = 6;   
    SET @X = 3;
END //
DELIMITER ;

CALL kkkkk();

-- 3 -2
SELECT @X,@prc_value