10.sql语句执行顺行
11.sql50句
12.select count(1) 和 count(*) 区别
1、 一般情况下，Select Count (_)和Select Count(1)两着返回结果是一样的
2、 假如表沒有主键(Primary key), 那么count(1)比count(_)快，
3、 如果有主键的話，那主键作为count的条件时候count(主键)最快
4、 如果你的表只有一个字段的话那count(_)就是最快的
5、count(_) 跟 count(1) 的结果一样，都包括对NULL的统计，而count(column) 是不包括NULL的统计
1.什么是数据库事务、数据库事务的四个特性是什么。
事务的概念
事务的四个特性（ACID）
1.Atomicity（原子性）
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。
2.Consistency（一致性）
数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的。
数据库总是从一个一致性状态转换到另一个一致状态。
举例：在银行转账的时候，A账户上有金额300元，B账户上有200元，A给B转账100，AB账户上的金额总和依然是500元
3.Isolation（隔离性）
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
4.Durability（持久性）
一旦事务提交，则其所做的修改就会永久保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。使用重做日志来保证持久性。
2.请分别举例说明幻读和不可重复读、并描述一下它们之间的区别。
脏读（读取未提交数据）
不可重复读（前后多次读取，数据内容不一致）
幻读（前后多次读取，数据总量不一致）
3.MySQL 的默认隔离级别是什么
4.事务覆盖问题
示例
上面的两种情况就是对于一条数据，多个事务同时操作可能会产生的问题，会出现某个事务的操作被覆盖而导致数据丢失。
解决方案
1.LBCC 解决数据丢失
2.MVCC 解决数据丢失
5.MVCC
概念
MVCC全称Multiple Version Concurrency Control，也就是多版本并发控制，重点在多版本，简单来说，它为每个事务生成了一个快照，保证每个事务只能读到自己的快照数据，不论其他事务如何更新一条记录，这个事务所读到的数据都不会产生变化，也就是说，会为一条记录保留多个版本，多个事务读到的版本不同，MVCC代替了读锁，实现了读-写不阻塞。
举例
使用版本来控制并发情况下的数据问题，在B事务开始修改账户且事务未提交时，当A事务需要读取账户余额时，此时会读取到B事务修改操作之前的账户余额的副本数据，但是如果A事务需要修改账户余额数据就必须要等待B事务提交事务。
原理
MVCC使得数据库读不会对数据加锁，普通的SELECT请求不会加锁，提高了数据库的并发处理能力。借助MVCC，数据库可以实现READ COMMITTED，REPEATABLE READ等隔离级别，用户可以查看当前数据的前一个或者前几个历史版本，保证了ACID中的I特性（隔离性)。
InnoDB的MVCC实现逻辑
版本链
Read View
注意
MVCC只在REPEATABLE READ和READ COMMITIED两个隔离级别下工作。其他两个隔离级别都和 MVCC不兼容 ，因为READ UNCOMMITIED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。
1.前置知识
1.1Mysql的体系结构
连接层
服务层
1.Management Serveices & Utilities
2.SQL Interface: SQL接口
3.Parser: 解析器
4.Optimizer: 查询优化器
5.Cache和Buffer： 查询缓存
引擎层
存储层
1.2Mysql的查询流程
2.存储引擎概念
3.各种存储引擎特点对比
4.常见存储引擎
InnoDB
概述
InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
存储方式
内存架构
InnoDB In-Memory Structures（内存结构）
Buffer Pool(缓冲池)
1.MySQL 不会直接去修改磁盘的数据，因为这样做太慢了，MySQL 会先改内存，然后记录 redo log，等有空了再刷磁盘，如果内存里没有数据，就去磁盘 load。
而这些数据存放的地方，就是 Buffer Pool。
2.我们平时开发时，会用 redis 来做缓存，缓解数据库压力，其实 MySQL 自己也做了一层类似缓存的东西。
3.MySQL 是以「页」（page）为单位从磁盘读取数据的，Buffer Pool 里的数据也是如此，实际上，Buffer Pool 是a linked list of pages，一个以页为元素的链表。
4.Buffer Pool 采用基于 LRU（least recently used） 的算法来管理内存
Change Buffer(写缓冲)
1.上面提到过，如果内存里没有对应「页」的数据，MySQL 就会去把数据从磁盘里 load 出来，如果每次需要的「页」都不同，或者不是相邻的「页」，那么每次 MySQL 都要去 load，这样就很慢了。
2.于是如果 MySQL 发现你要修改的页，不在内存里，就把你要对页的修改，先记到一个叫 Change Buffer 的地方，同时记录 redo log，然后再慢慢把数据 load 到内存，load 过来后，再把 Change Buffer 里记录的修改，应用到内存（Buffer Pool）中，这个动作叫做 merge；而把内存数据刷到磁盘的动作，叫 purge
Adaptive Hash Index
1.我们每次从辅助索引查询到对应记录的主键,然后还要用主键作为search key去搜索主键B+tree才能找到记录.
当这种搜索变多了,innoDB引擎会进行优化.
2.维护索引叶页面中所有记录的索引键值(或键值前缀)到索引叶页面位置的Hash映射关系,
能够根据索引键值(前缀)快速定位到叶页面满足条件记录的Offset，减少了B+树Search Path的代价，将B+树从Root页面至Leaf页面的路径定位，优化为Hash Index的快速查询。
Log Buffer（日志缓冲区）
redo log buffer是一块内存区域，这块区域持有将要写入redo log的数据。
Operating System Cache
不属于 InnoDB 的能力，而是操作系统为了提升性能，在磁盘前面加的一层高速缓存
InnoDB On-Disk Structures（磁盘结构）
表空间（Tablespaces）
我们平时创建的表的数据，可以存放到 The System Tablespace 、File-Per-Table Tablespaces、General Tablespace 三者中的任意一个地方，具体取决于你的配置和创建表时的 sql 语句。
Doublewrite Buffer
1.Doublewrite Buffer 就是保证数据页的可靠性
2.假设在某一次从内存刷新到磁盘的过程中，一个「页」刷了一半，突然操作系统或者 MySQL 进程奔溃了，这时候，内存里的页数据被清除了，而磁盘里的页数据，刷了一半，处于一个中间状态，不尴不尬，可以说是一个「不完整」，甚至是「坏掉的」的页。
3.MySQL 在刷数据到磁盘之前，要先把数据写到另外一个地方，也就是 Doublewrite Buffer，写完后，再开始写磁盘。Doublewrite Buffer 可以理解为是一个备份（recovery），万一真的发生 crash，就可以利用 Doublewrite Buffer 来修复磁盘里的数据。
MyISAM
概述
MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。
存储方式
MEMORY
MERGE
5.InnoDB、MyISAM存储引擎对比
6.存储引擎的选择
1.索引的本质
索引的本质是一种排好序的数据结构。
2.为什么要使用索引
1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
3.索引这么多优点，为什么不对表中每个字段都创建索引呢
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
4.索引是如何提升查询速度的
MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。
局部性原理与磁盘预读
1.由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘 I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。
2.由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高 I/O 效率。
3.预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为 4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。
5.常见索引
数据结构知识点补充
1.hash表
哈希表的特点是可以快速的精确查询，但是不支持范围查询
例如
select * from sanguo where name>‘鸡蛋’
这种查询哈希表是无能为力的
适用场景
等值查询的场景，就只有KV（Key，Value）的情况，例如Redis、Memcached等这些NoSQL的中间件。
2.有序数组
有序数组在等值查询的和范围查询时都不错
但是适合静态数据，因为如果我们新增、删除、修改数据的时候就会改变他的结构，成本比较高。
适用场景
可以用来做静态存储引擎，保存静态数据，例如你2019年的支付宝账单，2019年的淘宝购物记录等等都是很合适的，都是不会变动的历史数据。
3.平衡二叉树
有序，支持范围查询。
缺点：如果数据多了，树高会很高，查询的成本就会随着树高的增加而增加。
4.B树
同样的元素，B树的表示要比完全平衡二叉树要“矮”，原因在于B树中的一个节点可以存储多个元素。
5.B+树和B树相比的优势
B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。
提高了的原因也无非是会有指针指向下一个节点的叶子节点。
到这里可以总结出来，Mysql选用B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。
6.B+树的一些细节
B+树中一个节点为一页或页的倍数最为合适。
Mysql的基本存储结构是页(记录都存在页里边)
举例
回表的概念
覆盖索引的概念
最左匹配原则
联合索引的概念
索引可以简单如一个列 (a)，也可以复杂如多个列 (a,b,c,d)，即联合索引。
1.如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。
2.因此，列的排列顺序决定了可命中索引的列数。
使用索引注意事项
6.索引的类型
1.主键索引
索引列中的值必须是唯一的，不允许有空值。
2.普通索引
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
3.唯一索引
索引列中的值必须是唯一的，但是允许为空值。
4.全文索引
只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引。
5.空间索引
MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
6.前缀索引
在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。
B树和B+树的主要区别
B树：非叶子节点和叶子节点都会存储数据。
B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。
MyIsam索引
MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。
辅助索引
InnoDB索引
主键索引（聚簇索引）
每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。
辅助索引
除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。
辅助索引查询过程
根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。
组合索引
数据扫描方式
全表扫描
索引扫描
7.索引优化方式
1.分页查询优化
业务要根据时间范围查询交易记录，接口原始的SQL如下：
select * from trade_info where status = 0 and create_time >= ‘2020-10-01 00:00:00’ and create_time <= ‘2020-10-07 23:59:59’ order by id desc limit 102120, 20;
select * from trade_info a ,
(select id from trade_info where status = 0 and create_time >= ‘2020-10-01 00:00:00’ and create_time <= ‘2020-10-07 23:59:59’ order by id desc limit 102120, 20) as b //这一步走的是索引覆盖扫描，不需要回表
where a.id = b.id;
2.分而治之
营销系统有一批过期的优惠卷要失效，核心SQL如下：
– 需要更新的数据量500w
update coupons set status = 1 where status =0 and create_time >= ‘2020-10-01 00:00:00’ and create_time <= ‘2020-10-07 23:59:59’;
mysql> explain select min(id) min_id, max(id) max_id from coupons where status =0 and create_time >= ‘2020-10-01 00:00:00’ and create_time <= ‘2020-10-07 23:59:59’;
±—±------------±------±-----------±------±-----------------------±-----------------------±--------±–
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±------±-----------------------±-----------------------±--------±–
| 1 | SIMPLE | users | NULL | range | idx_status_create_time | idx_status_create_time | 6 | NULL | 180300 | 100.00 | Using where; Using index |
current_id = min_id;
for current_id < max_id do
update coupons set status = 1 where id >=current_id and id <= current_id + 1000; //通过主键id更新1000条很快
commit;
current_id += 1000;
done
8.索引失效的几种情况
1.查询条件包含or，可能导致索引失效
2.like通配符可能导致索引失效
3.联合索引，查询时的条件列不是联合索引中的第一个列，索引失效
4.对索引列运算（如，+、-、*、/），索引失效
5.索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效
6.mysql估计使用全表扫描要比使用索引快,则不使用索引
1.锁的概念
2.锁的分类
3.数据库中的乐观锁和悲观锁
悲观锁的概念
乐观锁的概念
乐观锁和悲观锁的使用场景
乐观锁的实现方式
1.版本号
2.时间戳
4.InnoDB存储引擎锁的算法
1.redo log
概念
记录方式
使用场景
用于系统奔溃恢复(crash-safe)
2.bin log
概念
刷盘机制
使用场景
3.undo log
概念
使用场景
MVCC多版本控制中使用undo log
4.bin log/redo log/undo log区别
1.整体优化思路
2.show status
show status 命令用于查询 Mysql 状态变量相关统计信息，主要关注查询次数、线程连接数和线程运行数。
3.获取需要优化的 SQL 语句
方式一：查看运行的线程
执行命令：
show processlist
mysql> show processlist;
±—±-----±----------±-----±--------±-----±---------±-----------------+
| Id | User | Host | db | Command | Time | State | Info |
±—±-----±----------±-----±--------±-----±---------±-----------------+
| 9 | root | localhost | test | Query | 0 | starting | show processlist |
±—±-----±----------±-----±--------±-----±---------±-----------------+
1 row in set (0.00 sec)
返回的 State 的值是我们判断性能好坏的关键
例如出现以下值，则该行记录的 SQL 语句需要优化
Converting HEAP to MyISAM # 查询结果太大时，把结果放到磁盘，严重
Create tmp table #创建临时表，严重
Copying to tmp table on disk #把内存临时表复制到磁盘，严重
locked #被其他查询锁住，严重
loggin slow query #记录慢查询
Sorting result #排序
方式二：开启慢查询日志
4.explain相关问题
当我们在查询前能否预先估计查询究竟要涉及多少行、使用哪些索引、运行时间呢？答案是能的，mysql提供了相应的功能和语法来实现该功能。
是什么
1.Explain被称为执行计划，在语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，模拟MySQL优化器来执行SQL语句，执行查询时，会返回执行计划的信息，并不执行这条SQL。
2.Explain可以用来分析SQL语句和表结构的性能瓶颈。通过explain的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。
explain结果列说明
1.id列
1.id列是一个有顺序的编号，是查询的顺序号，有几个select就显示几行。
2.id的顺序是按 select 出现的顺序增长的。
3.id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。
id相同
执行顺序从上至下
例子：
explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;
读取顺序：subject > teacher > student_score
id不同
如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行
例子：
explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));
读取顺序：teacher > subject > student_score
小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。
深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！
因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频
如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）
-blog.csdnimg.cn/20210517173305910.png#pic_center)
1.id列
1.id列是一个有顺序的编号，是查询的顺序号，有几个select就显示几行。
2.id的顺序是按 select 出现的顺序增长的。
3.id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。
id相同
执行顺序从上至下
例子：
explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;
读取顺序：subject > teacher > student_score
id不同
如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行
例子：
explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));
读取顺序：teacher > subject > student_score
小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。
深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！
因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
[外链图片转存中…(img-t5e7Ok5R-1710836375273)]
[外链图片转存中…(img-QPZCHzlI-1710836375274)]
[外链图片转存中…(img-OQbyj8SO-1710836375274)]
由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频
如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）
[外链图片转存中…(img-ef84enuq-1710836375275)]
429
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
