3.1.0 MYSQL查询语句的书写顺序 ?
MYSQL基本查询的书写顺序主要包括几个关键词, 分别是 :
select [distinct] <字段名称> from 表名 ****where <查询条件> group by <字段> having <分组后筛选条件> order by 排序字段 limit 查询起始行号 , 查询数量
MYSQL 去重的方式有哪些 ?
- distinct : 对结果集去重
- group by : 根据某些字段分组 , 该字段在结果集中只会出现一次
where 和 having 区别 ?
- where : 对分组之前的数据进行筛选
- having : 对分组之后的数据进行筛选
3.1.1 MYSQL查询语句的执行顺序 ?
MYSQL查询语句的执行顺序和书写顺序并不一致 , 查询顺序为 :
select [distinct] <字段名称> from where <查询条件> group by <分组字段> having <分组后筛选条件> order by 排序字段 limit 查询起始行号 , 查询数量
执行顺序是 :
from 表名 where <查询条件> group by <分组字段> having <分组后筛选条件> select [distinct] <字段名称> order by 排序字段 limit 查询起始行号 , 查询数量
3.1.3 如何删除一张表的所有数据👍
MYSQL删除一张表的所有数据有三种方式 , 分别是
- delete from 表名 : 将Mysql表中的数据一行一行的删除，不删除表的结构，也不释放表的空间，可以回滚（rollback）
- drop table 表名 : 将表的数据直接删除，以及删除表的结构同时释放空间，删除数据后无法找回
- truncate table 表名 : 删除表中的所有数据，释放空间，但是保留表的结构 , 删除数据后不可以回滚。
3.1.4 MYSQL常用的函数有哪些 ?
常用的数值处理函数 :
| 函数名称 | 作 用 |
|---|---|
| MOD | 取模 |
| CEIL | 向上取整 |
| FLOOR | 向下取整 |
| RAND | 生成一个0~1之间的随机数 |
| ROUND | 四舍五入 |
常用的字符串处理函数 :
| 函数名称 | 作 用 |
|---|---|
| LENGTH | 计算字符串长度函数，返回字符串的字节长度 |
| CONCAT | 拼接字符串 |
| INSERT | 替换字符串函数 |
| LOWER | 将字符串中的字母转换为小写 |
| UPPER | 将字符串中的字母转换为大写 |
| TRIM | 删除字符串左右两侧的空格 |
| REPLACE | 字符串替换函数，返回替换后的新字符串 |
| SUBSTRING | 截取字符串，返回从指定位置开始的指定长度的字符换 |
常用的时间日期处理函数 :
| 函数名称 | 作 用 |
|---|---|
| CURRENT_DATE | 两个函数作用相同，返回当前系统的日期值 |
| CURRENT_TIME | 两个函数作用相同，返回当前系统的时间值 |
| NOW 和 SYSDATE | 两个函数作用相同，返回当前系统的日期和时间值 |
| MONTH | 获取指定日期中的月份 |
| WEEK | 获取指定日期是一年中的第几周 |
| YEAR | 获取年份，返回值范围是 1970〜2069 |
| TIME_TO_SEC | 将时间参数转换为秒数 |
| SEC_TO_TIME | 将秒数转换为时间，与TIME_TO_SEC 互为反函数 |
| DATE_ADD | 两个函数功能相同，都是向日期添加指定的时间间隔 |
| DATEDIFF | 获取两个日期之间间隔，返回参数 1 减去参数 2 的值 |
| DATE_FORMAT | 格式化指定的日期，根据参数返回指定格式的值 |
常用的流程处理函数 :
| 函数名称 | 作用 |
|---|---|
| IF | 判断，流程控制 |
| IFNULL | 判断是否为空 |
| CASE | case when then |
MYSQL常用开窗函数/分析函数
窗口函数也称为分析函数，意思是对数据库数据进行实时分析处理的函数 , 开窗函数大体可以分为以下两种：
1能够作为开窗函数的聚合函数（sum，avg，count，max，min）
2rank，dense_rank，row_number 等专用开窗函数。
●row_number : 为组内每一条数据生成行号 , 按行号进行排序 , 相同的数据也按顺序排
●rank : 为组内每一条数据生成排名(数据相同并列) , 下一跳数据不连续(1,2,2,4)
●dense_rank : 为组内每一条数据生成排名(数据相同并列) ,下一跳数据连续(1,2,2,3)
有时候一组数据只返回一组值是不能满足需求的，如我们经常想知道各个地区的前几名、各个班或各个学科的前几名。这时候需要每一组返回多个值。用开窗函数解决这类问题非常方便。
开窗函数和聚合函数的区别
- 聚合函数每组只返回一个值，开窗函数每组可返回多个值
- SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。
注：常见主流数据库目前都支持开窗函数，MYSQL数据库8.0版本开始支持开窗函数
开窗函数的基本语法 :
<开窗函数> over ([partition by <列清单>] order by <排序用列清单>)
开窗函数示例 :
create database test07 charset utf8 ;
use test07;
CREATE TABLE `student_score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(10) DEFAULT NULL,
`course` char(10) DEFAULT NULL,
`score` int (2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小张','语文',80);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小李','语文',90);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小王','语文',70);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小赵','语文',56);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小杨','语文',70);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小郭','语文',99);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小钱','语文',86);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小谭','语文',87);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小何','语文',80);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小张','数学',70);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小李','数学',100);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小王','数学',80);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小赵','数学',76);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小杨','数学',55);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小郭','数学',80);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小钱','数学',79);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小谭','数学',54);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小何','数学',30);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小张','英语',90);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小李','英语',85);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小王','英语',99);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小赵','英语',86);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小杨','英语',95);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小郭','英语',60);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小钱','英语',89);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小谭','英语',84);
INSERT INTO `student_score`(`id`, `name`,`course`,`score`) VALUES (null, '小何','英语',70);
# 用户登录表
create table user_login
(
user_id varchar(100),
login_time datetime
);
# 插入用户登录数据
insert into user_login values
(1,'2022-11-25 13:21:12'),
(1,'2022-11-24 13:15:22'),
(1,'2022-11-24 10:30:15'),
(1,'2022-11-24 09:18:27'),
(1,'2022-11-23 07:43:54'),
(1,'2022-11-10 09:48:36'),
(1,'2022-11-09 03:30:22'),
(1,'2022-11-01 15:28:29'),
(1,'2022-10-31 09:37:45'),
(2,'2022-11-25 13:54:40'),
(2,'2022-11-24 13:22:32'),
(2,'2022-11-23 10:55:52'),
(2,'2022-11-22 06:30:09'),
(2,'2022-11-21 08:33:15'),
(2,'2022-11-20 05:38:18'),
(2,'2022-11-19 09:21:42'),
(2,'2022-11-02 00:19:38'),
(2,'2022-11-01 09:03:11'),
(2,'2022-10-31 07:44:55'),
(2,'2022-10-30 08:56:33'),
(2,'2022-10-29 09:30:28');
需求一 : 查询每门课的学生考试成绩排名
-- 需求一 : 查询每门课的学生考试成绩排名
-- 无法生成排名
select * from student_score order by course , score desc ;
-- row_number : 组内按照顺序生成行号
SELECT
`name`,
`course`,
`score`,
rank() over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank
FROM
student_score ;
核心概念解释：
-
窗口函数（
rank()） ：RANK()是一种窗口函数，用来为每行生成一个排名。它与ROW_NUMBER()类似，但有一个不同之处：如果有并列的相同成绩，RANK()会为它们分配相同的排名，下一名次会跳过。例如，如果两个学生并列第一名，接下来的学生会排第三名，而不是第二名。
-
PARTITION BY：PARTITION BY用于将数据按照某一列（或多列）分组。在本例中，它是根据课程(course)分组，意思是：每个课程的数据会独立进行排名。
-
ORDER BY score DESC：- 在每个课程的分组中，按照成绩(
score)的降序进行排序。这样可以确保成绩最高的学生排名靠前。
- 在每个课程的分组中，按照成绩(
具体执行过程：
PARTITION BY course：首先根据course（课程）将学生成绩分组。不同课程的成绩是相互独立的。ORDER BY score DESC：在每个课程分组内，按照学生的成绩降序排序，成绩最高的学生排在前面。RANK()：在每个课程分组内，根据排序结果生成排名。相同分数的学生将会获得相同的排名，而排名会出现跳跃。
需求二 : 查询每门课 , 考试成绩最好的学生信息
select * from (
SELECT
`name`,
`course`,
`score`,
rank() over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank
FROM
student_score
) t where t.score_rank = 1
需求三 : 查询每门课 , 考试成绩前三名的学生信息
select * from (
SELECT
`name`,
`course`,
`score`,
rank() over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank
FROM
student_score
) t where t.score_rank <=3
需求四 : 查询所有学生各科学生的总分排名
select * ,row_number() over () r from (
select name , sum(score) s from student_score group by name order by s desc
) t ;
需求五 : 查询所有学生各科学生的总分前3名
-- 需求五 : 查询所有学生各科学生的总分排名
select * from (
select t.* , row_number() over () 排名 from (
select name,sum(score) 总分 from student_score group by name order by sum(score) desc
) t
) t2 where t2.排名 <=3 ;
需求六 : 查询连续登录超过5天的用户信息
select distinct user_id from (
-- 登录日期减去排名得到最早登录日期的前一天 , 如果剪完之后日期相同代表是连续的
select t2.user_id, login_date, date_add(login_date,interval -irank day ) idate from (
-- 根据登录时间对用户登录日期进行排序 , 加排名
select t.user_id, t.login_date ,rank() over (partition by user_id order by login_date) irank from (
-- 将登录时间转化为 日期格式(年月日)
select distinct user_id,date(login_time) login_date from user_login
) t
) t2
) t3 group by user_id , idate having count(idate) >= 5 ;
- 对用户登录时间去重
- 根据用户登录日期进行排序, 使用rank()开窗函数加上排名
- 使用登录日期减去排名会得到连续最早登录的前一天的时间 , 如果减完之后时间相同的数据就代表连续的数据
- 对用户和时间进行分组, 获取组内数量 >=5 的用户信息
需求六 : 查询每个用户连续登陆的最大天数
select t4.user_id,max(c) maxDay from (
select distinct user_id , count(idate) c from (
select t2.user_id, login_date, date_add(login_date,interval -irank day ) idate from (
select t.user_id, t.login_date ,rank() over (partition by user_id order by login_date) irank from (
select distinct user_id,date(login_time) login_date from user_login
) t
) t2
) t3 group by user_id , idate
) t4 group by t4.user_id
- 对用户登录时间去重
- 根据用户登录日期进行排序, 使用rank()开窗函数加上排名
- 使用登录日期减去排名会得到连续最早登录的前一天的时间 , 如果减完之后时间相同的数据就代表连续的数据
- 对用户和时间进行分组, 获取组内数量
- 再对用户进行分组 , 获取组内数量最大值, 就是连续登录的最大天数
3.1.5 MYSQL如何实现行列转化查询
使用CASE ... WHEN ...THEN 将列转化为行
3.1.6 MYSQL如何实现多表查询 ?👍
MYSQL多表查询主要使用连接查询 , 连接查询的方式主要有 :
●内连接
○隐式内连接 : Select 字段 From 表A , 表B where 连接条件
○显式内连接 : Select 字段 From 表A inner join 表B on 连接条件
●外连接
○左外连接 : Select 字段 From 表A left join 表B on 连接条件
○右外连接 : Select 字段 From 表A right join 表B on 连接条件
●子查询 : 先查询一部分数据作为临时表 , 再进行关联查询
3.1.7 内连接和外连接的区别 ? 👍
1语法不同 , 内连接语法为 inner join , 也可以省略写成隐式内连接 , 外连接语法是 outer join
2结果集不同
●内连接查询 : 内连接查询的是满足条件的二张表的数据组合
●左外连接 : 左外连接查询左表所有数据以及满足连接条件的二张表数据组合
●右外连接 : 右外连接右表所有数据以及满足连接条件的二张表数据组合
3使用场景不同
●内连接只能查询二张表交集数据, 外连接可以查询一张表的所有数据, 如果需要查询一张表所有数据, 不管满足条件与否, 就必须使用外连接
3.1.8 MYSQL的存储引擎了解过吗👍
MYSQL中存储引擎有很多中 , 例如 : InnoDB , MyISAM , Memory , Archive , 其中比较常用的就是InnoDB , MyISAM , 他们之间主要有几个区别 :
1InnoDB引擎, 支持事务, 而MyISAM不支持
2InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁
3InnoDB引擎, 支持外键, 而MyISAM是不支持的
4InnoDB引擎支持的最大容量是64T , MyISAM存储引擎支持最大256T
5索引组织的形式不同
●MyISAM采用非聚集索引 , 索引和数据分开存放 , 叶子节点中存储索引列的值以及索引所在行的磁盘地址
●InnerDB引擎主键采用聚集索引 , 数据存放在聚集索引的叶子节点上
3.1.9 MySQL中有哪些索引？有什么特点？👍
在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、普通索引、全文索引
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建, 只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 普通索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
其中根据索引字段又分为, 单列索引和复合索引
单列索引 : 在MYSQL一个列上创建的索引, 就是一个单列索引
复合索引 : 在MYSQL多列上创建的索引, 就是一个复合索引(组合索引)
3.1.10 B树和B+树区别
B树又名平衡多路查找树，不同于常见的二叉树，它是一种多叉树，我们常见的使用场景一般是在数据库索引技术里，大量使用B树和B+树的数据结构。
B树大多用在磁盘上用于查找磁盘的地址。因为磁盘会有大量的数据，有可能没有办法一次将需要的所有数据加入到内存中，所以只能逐一加载磁盘页，每个磁盘页就对应一个节点，而对于B树来说，B树很好的将树的高度降低了，这样就会减少IO查询次数，虽然一次加载到内存的数据变多了，但速度绝对快于二插查找数或是红黑树的。
B树和B+树的最大区别在于非叶子节点是否存储数据。
●B树是非叶子节点和叶子节点都会存储数据。所以存储相同的数据量层级更高
●B+树只有叶子节点才会存储数据，非叶子节点存储的是索引不存储数据
●MYSQLB+数叶子节点存储的数据会形成一个双向循环链表 , 节点之间通过指针连接
3.1.11 MYSQL为什么采用B+树作为索引 👍
1相对于二叉树 , B树和B+树是一个多叉树，存储相同的数据B数和B+数层级更少，查找路径更短 , 查询效率更高
2对于Btree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页(16KB)中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致查询性能降低
3相对Hash索引，B+tree支持范围匹配及排序操作 , Hash索引不支持范围查询
3.1.12 说一下什么是聚集索引与非聚集索引 👍
聚集索引(聚簇索引)是将索引列字段和行记录数据维护在了一起, 它的叶子节点存储的是 索引列字段值 + 完整的行记录数据 , 通过聚集索引能直接获取到整行数据 , 简单来说就是数据和索引存储在一起就叫聚集索引
例如 : 有一张User表
ID
NAME
AGE
3
27
BOB
36
5
OLIYA
7
ALICE
36
27
ZET
10
41
SLICE
13
23
15
LUNA
19
16
ANNA
那么基于这张表的主键 id 建立的聚集索引 , 他的结构如下所示 :
指针:MYSQL指针默认大小为6B
15
索引值:B+树的KEY 值
PAGE:MYSQL默认的PAGE页大小为16KB
10
15
19
5
15
13
10
ZET
OLIYA
SLICE
ALICE
BOB
LUNA
ANNA
27
36
41
16
23
36
27
INNODB存储引擎聚集索引示意图
因为表中存储的数据是通过聚集索引组织在一起的,所以聚集索引必须要有, 否则我们无法获取到表中的行数据 ,并且聚集索引还只能存在一个
既然聚集索引必须要有,可是有时候我们创建表的时候并没有设置主键,表照样创建成功,那么还有没有聚集索引呢? 如果没有聚集索引 Innodb 的数据靠什么来组织维护呢 ?
1如果存在主键,那么主键索引就是聚集索引
2如果不存在主键,将会使用第一个唯一(UNIQUE)、非空的索引作为聚集索引
3如果表中既没有主键索引,又没有合适的唯一索引,那么 Innodb 会自动维护一个 row_id(默认大小为 6B)来作为隐藏的聚集索引
非聚集索引(非聚簇索引)是相比较于聚集索引来说 , 它是把索引和行数据分开维护 , 叶子节点并没有包含完整的数据记录(叶子节点存储的是聚集索引的 id 或 数据的磁盘地址)Mysql 非聚集索引底层的数据结构也是 b+ 树, 例如 MyISAM 的索引、Innodb 的辅助索引 , 简单来说就是索引和数据分开存储就是非聚集索引
例如 : 有一张User表
ID
NAME
AGE
3
27
BOB
36
5
OLIYA
7
ALICE
36
27
ZET
10
41
SLICE
13
23
15
LUNA
19
16
ANNA
假如这个表采用MyISAM存储引擎存储, 那么底层采用的就是非聚集索引的形式来组织索引 , 结构如下 :
指针:MYSQL指针默认大小为6B
索引值:B+树的KEY 值
PAGE:MYSQL默认的 PAGE 页大小为 16KB
10
15
13
15
10
19
0X13
OXCA
OXBA
0X15
OXAF
0X36
0X23
ID
AGE
NAME
3
0X13
27
BOB
5
36
OLIYA
OXAF
7
36
ALICE
0X15
27
ZET
10
OXBA
SLICE
13
41
OXCA
0X23
23
15
LUNA
19
16
0X36
ANNA
MYISAM索引示意图
3.1.13 说一下什么是回表查询👍
假如用户表 , 采用Innodb存储引擎 , id为主键 同时为age列创建索引 , 那么age列的索引结构如下 :
指针:MYSQL指针默认大小为6B
36
索引值:B+树的KEY值
PAGE:MYSQL默认的 PAGE 页大小为16KB
27
36
主键ID
27
41
36
27
36
16
23
13
19
5
15
10
INNODB存
存储引擎非聚集索引示意图
这个时候执行SQL语句select * from user where age = 41 , 它的查找过程是什么样的呢?
首先由于 age 是索引,并且 where 中使用了索引作为条件,我们需要从辅助索引中查询age=41的数据节点 , 获取数据节点上的数据 id = 13
然后用id值 , 再去主键索引中查询id=13的数据, 获取数据记录
当通过非聚集索引来查询数据时,存储引擎会根据索引字段定位到最底层的叶子节点,并通过叶子节点获得指向主键索引的主键 id,然后通过主键 id 去主键索引(聚集索引)上找到一个完整的行记录.这个过程被称为回表查询
3.1.14 说一下什么是覆盖索引👍
覆盖索引是指在执行查询语句的时候, 在一棵索引树上就能获取到要查询的所有列, 这样不需要回表查询效率更高
例如 : 执行查询语句select * from user where age = 41 , 这个语句需要先从辅助索引中查找主键id值 , 再去主键索引中获取数据 , 需要回表
但是如果我们写SQL语句的时候指定了查询字段就需要id和age两列 ,这个时候的SQL语句select id , age from user where age = 41这个语句在执行的过程中从辅助索引就能获取到需要的所有的数据 , 就不需要回表, 也就实现了覆盖索引 , 所以就要求我们编写SQL语句的时候尽量不要写select * , 需要用到哪个字段就查询哪个字段
假如我们查询语句就需要返回id , name 和 age字段 , 又该如何实现覆盖索引呢 ?
这就需要索引树中包含我们需要查询的字段就可以了 , 可以为name和age字段创建组合索引
SECONDARY KEY
A1ICE
ZARK
ERIC
ALICE
ROSE
JIM
ANNA
ERIC
ROSE
TOM
ALICE
77
5
34
22
91
68
18
30
15
20
50
49
这样从辅助索引中就可以获取到需要的所有数据 , 不需要回表查询 , 实现了覆盖索引
所以实现覆盖索引最简单的方式就是为经常需要查询的列建立组合索引 , 使用组合索引代替多个单列索引
3.1.15 说一下什么是索引下推👍
索引下推 (Index Condition Pushdown，索引条件下推，简称 ICP)，是 MySQL5.6 版本的新特性，它可以在对联合索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，能有效的减少回表次数 , 提高查询效率
例如 : 有一张用户表 , 为name和age列创建联合索引 (name,age)
执行一条SQL语句select * from user where name like 'A%' and age = 16
不使用索引下推
首先存储引擎读取索引记录
然后根据索引中的主键值，定位并读取完整的行记录
之后存储引擎把记录交给 Server 层去检测该记录是否满足 WHERE 条件
使用索引下推
存储引擎读取索引记录
判断 WHERE 条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
存储引擎把记录交给 Server 层，Server 层检测该记录是否满足 WHERE 条件的其余部分
3.1.16 说一下什么是左前缀法则 ? 👍
左前缀法则：在通过联合索引检索数据时，从索引中最左边的列开始，一直向右匹配，如果遇到范围查询(>、<、between、like等) 或者中间跳过索引的某一列 就停止后边的匹配
例如 : 为一张用户表创建组合索引
上述SQL语句对name,age,sex建一个组合索引index_age_name_sex,实际上这条语句相当于建立了三个索引 : (name) , (name,age) , (name,age,sex)
3.1.17 说一下索引失效的场景有哪些 👍
如果索引使用不当, 在很多情况下都有可能会出现索引失效 , 常见的有如下场景 :
1联合索引中不满足左前缀法则 , 索引会失效或者部分失效
2在索引列上使用函数和进行运算会导致索引失效
3使用 != 或 not in等否定操作符会导致后面的索引失效
4连接条件or关键词二边 , 只要有一个条件不满足索引, 就会全表扫描 , 索引失效
5使用 > , < 等比较运算符号 , 比较运算符后面的条件索引会失效
6当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描
7like 语句的索引失效问题like 的方式进行查询，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询
8数据库在执行的过程中, 如果判断执行索引的效率还没有全表扫描的效率高, 也会走全表扫描
3.1.18 说一下索引是越多越好吗 👍
索引不是越多越好 , 虽然索引能够提高查询的效率 , 但是也会占用更多的存储空间 , 而且维护索引需要耗费额外的性能 , 所以我们在创建索引的时候一定要考虑清楚 , 到底有没有必要创建索引
需要创建索引情况
1主键自动建立主键索引
2频繁作为查询条件的字段应该创建索引
3查询中排序的字段，应该创建索引
4频繁查找字段(经常写在select后面的字段) , 应该创建索引 , 易于实现覆盖索引, 避免回表查询 提高查询效率
5查询中统计或者分组字段，应该创建索引
6多表关联查询中，关联字段应该创建索引 (on 两边都要创建索引)
不要创建索引情况
1表记录太少
2经常进行增删改操作的表
3频繁更新的字段
4where条件里使用频率不高的字段
5区分度不高的字段 , 例如 : sex , status
3.1.19 一个SQL语句执行很慢, 如何分析优化 👍
首先可以开启慢查询, 慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志 , 通过慢查询日志或者命令, 获取到执行慢的SQL语句!
临时配置慢查询 :
关闭数据库连接 , 重新连接数据库, 慢查询才会生效
TAIL-F LOCALHOST-SLOW.LOG
[ROOT@LOCALHOST MYSQL]#
/USI/SBIN/MYSQLD, VERSION: 8.0.26 (MYSQL COMMUNITY SERVER - GPI). STAITED
WITH:
UNIX SOCKET://VAR/LIB/MYSQL/MYSQL.SOCK
3306
PORT:
TCP
ID
TIME
ARGUMENT
COMMAND
2021-10-
28T15:45:39.6886792
T1ME:
]@LOCALHOST[]
8
ID:
USERQHOST:
ROOT[ROOT]
13.350650 LOCK TIME: 0.000358 ROWS SENT:1 ROWS EXAMINED: 0
TIME:
QUERY
ITCAST;
USE
1635435926;
SET
TIMESTAMP1
SELECT COUNT(*) FROM TB SKU;
然后可以使用EXLPAIN命令分析SQL语句的执行过程
FILTERED
ID
SELECT TYPE
EXTRA
TABLE
REF
KEY LEN
TYPE
POSSIBLE KEYS
PARTITIONS
KEY
ROWS
1 SIMPLE
100.00(NULL)
42
INDEX PROFESSION AGE GENDER,INDEX P INDEX PROFESSION AGE QENDER
CONST,CONST,CONST
(NULT)
REF
TBUSER
列名
说明
执行编号,标识SELECT所属的行.如果在语句中没子
查询或关联查询,只有唯一的SELECT,每行都将显示
ID
1.否则,内层的SELECT语句一般会顺序编号,对应
于其在原始语句中的位置
显示本行是简单或复杂SELECT.如果查询有任何复杂
SELECT_T
的子查询,则最外层标记为PRIMARY(DERIVED,
YPE
UNION,UNION RESUIT)
访问引用哪个表(引用某个查询,如"DERIVED3")
TABLE
数据访问/读取操作类型(ALL,INDEX,RANGE,REF,
TYPE
EQ_REF,CONST/SYSTEM,NULL)
POSSIBLE
揭示哪一些索引可能有利于高效的查找
-KEYS
显示MYSQL决定采用哪个索引来优化查询
KEY
显示MYSQL在索引里使用的字节数
KEY_LEN
显示了之前的表在KEY列记录的索引中查找值所用的
REF
列或常量
为了找到所需的行而需要读取的行数,估算值,不精
确.通过把所有ROWS列值相乘,可粗略估算整个查
ROWS
询会检查的行数
额外信息,如USING INDEX,FILESORT等
EXTRA
通过EXPLAIN就能知道问题具体出现在哪里 , 进行针对性的修复就可以了 , 例如 :
1key字段为空代表没有走索引 ,可以考虑添加索引
2type字段如果出现了index, all, index_merge等值, 这个查询的效率可能比较低 , 是否可以调整语句结构
●index : 把索引从头到尾扫⼀遍
●all : 全表扫描数据⽂件，然后再在server层进⾏过滤返回符合要求的记录
●index_merge : 表示查询使⽤了两个以上的索引，最后取交集或者并集 , 由于要读取多个索引，性能不好 , 可以考虑使用组合索引
3Extra : 额外信息
●如果出现 using filesort 代表排序时⽆法使⽤到索引 , 需要考虑为排序字段创建索引
●如果出现 using join buffer 代表多表查询时被驱动的表没有索引, 考虑建立索引
●如果出现 using where表示存储引擎返回的记录并不是所有的都满⾜查询条件，需要在server层进⾏过滤
3.1.20 MYSQL超大分页怎么处理 ?👍
MYSQL中分页的逻辑是将from + size条数据全部加载出来 , 然后过滤掉前from条数据 , 保留最后size条 , 这样当页码比较大的时候from的值就比较大 , 加载的数据比较多效率很低
1从业务上进行限制 , 只让用户访问前100或者200页数据 , 像百度和JD都是这样的 , 翻到一定的页码后就不允继续查询了
2采用Search After机制 , 对业务数据进行排序 , 每次查询下一页的时候, 根据上一页的最后一条结果的值开始向下进行查询 , 这样就通过条件将前面的数据过滤掉了 ,起始索引永远是0 , 一般用于手机上的滚动查询
3使用覆盖索引先获取到数据的id , 再根据id查询具体的数据内容
3.1.21 有没有做过MySQL 的性能优化👍
数据库和表设计的优化
1选择最合适的字段属性
例如 : 定长字符串用char , 不定长用varchr状态, 性别 , 状态等有限数量值的用tinyint
2尽量把字段设置为NOT NULL , 当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询
3尽量遵循范式设计 , 但是考虑到性能, 可以对查询比较多的字段适当设置冗余字段 , 避免连表查询
4为合适的表和字段创建索引
5如果一个字段只存储数字 , 尽量使用数字型字段 , 不使用字符串
索引优化
1要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
2尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
3尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
4在组合/联合索引中，将有区分度高的索引放在前面
SQL语句优化
1编写SQL尽量规避一些索引失效的情况
2避免 select * 写法 , 因为执行 SQL 时优化器需要将 * 转成具体的列；每次查询都要回表，不能走覆盖索引。
3避免复杂 SQL 语句 , 提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理
4用IN来替换OR
5避免数据类型不一致 , 数据类型不一致会发生隐式类型转化导致索引失效
6对经常排序和分组的字段创建索引
7尽量避免使用子查询 , 可以转化为连接查询
8将多次插入换成批量Insert插入 , 插入的时候有主键的话, 尽量主键顺序插入
9多表查询中用用小结果集驱动大结果集
10对于分页查询 , 考虑到页码较大时的深度分页问题 , 提前处理
11修改语句中经常作为条件的字段要创建索引 , 防止行锁升级为表锁, 影响执行效率
update tb_user set age = 18 where name = '张三'
-- 加行锁 , MYSQL中行锁是加在索引上的如果字段没有建立索引, 这个时候行锁就会自动升级为表锁
架构优化(选说)
1搭建主从集群 , 主库负责写, 从库负责读 , 实现读写分离和负载均衡 , 提高MYSQL处理能力
2对于列比较多的表进行垂直拆表 , 拆分成多张表
3对于行比较多的表进行水平分表 , 拆分成多张表(经常按照时间拆分, 按照区域拆分, 按照数据标识拆分)
4对于一个库中表比较多 , 数据比较多可以进行分库(微服务天然分库)
5引入缓存对经常查询的数据进行缓存 , 减少数据库操作的次数
3.1.22 什么是数据库范式，为什么要反范式？
所谓数据库范式，其实就是数据库的设计上的一些规范；这些规范可以让数据库的设计更加简洁、清晰；同时也会更加好的可以保证一致性。
三个常用的范式：
第一范式(1N)是说，数据库表中的属性的原子性的，要求属性具有原子性，不可再被拆分；比如地址如果都细化拆分成省、市、区、街道、小区等等多个字段这就是符合第一范式的，如果地址就是一个字段，那就不符合了。
第二范式(2N)是说，数据库表中的每个实例或记录必须可以被唯一地区分，说白了就是要有主键，其他的字段都依赖于主键。
第三范式(3N)是说，任何非主属性不依赖于其它非主属性，也就是说，非主键外的所有字段必须互不依赖
如果我们在做表结构设计的时候，完全遵守数据库三范式，确实可以避免一些写时异常，提升一些写入性能，但是同时也会丢失一些读取性能
因为在遵守范式的数据库设计中，表中不能有任何冗余字段，这就使得查询的时候就会经常有多表关联查询，这无疑是比较耗时的。于是就有了反范式化。
所谓反范式化，是一种针对遵从设计范式的数据库的性能优化策略。也就是说，反范式化不等于非范式化，反范式化一定发生在满足范式设计的基础之上。前者相当于先遵守所有规则，再进行局部调整。
3.1.23 为什么大厂不建议使用多表join？
之所以不建议使用join查询，最主要的原因就是join的效率比较低。
MySQL是使用了嵌套循环的方式来实现关联查询的，简单点说就是要通过两层循环，用身一张表做外循环，第二张表做内循环，外循环的每一条记录跟内循环中的记录作比较，符合条件的就输出。随着表越多，表中的数据量越多，JON的效率会呈指数级下降。
如果不能通过数据库做关联查询，那么需要查询多表的数据的时候要怎么故呢？
主要有两种做法：
1在内存中自己做关联即先从数据库中把数据查出来之后，我们在代码中再进行二次查询，然后再进行关联。
2数据冗余那就是把一些重要的数据在表中做冗余，这样就可以避免关联查询了。
3.1.24 说一说MySQL一条SQL语句的执行过程
如一条简单的查间语句：select * from users where age='18' and name='zhangsan';
执行过程如下所示 :
客户端/服务器
通信协议
SQL
语法解析
解析树
客户端
解析器
查询缓存
结果
返回结果
新解析树
预处理器
缓存结果
执行计划
查询执行引擎
查询优化器
API接口查询
MYISAM
存储引擎
INNODB
数据
结合上面的说明，我们分析下这个语句的执行流程：
1使用连接器，通过客户端/服务器通信议与小ySQL建立连接。并查询是否有权限
2Mysqla8.0之前检查是否开启缓存，开启了Query Cache且命中完全相同的SQL语句，则将查询结果直接返回给客户端；
3由解析器（分析器）进行语法分析和语义分析，并生成解析树。如查询是select、表名users、条件是 age='18'and name='zhangsan'
4由优化器生成执行计划。根据索引看看是否可以优化
5执行器来执行SQL语句，这里具体的执行会操作MySQL的存储引擎来执行SQL语句，根据存储引擎类型，得到查询结果。若开启了Query Cache,则缓存，否则直接返回。
30 人点赞
30
