-
事务
1.1 事务的四大特性
原子性（Atomicity）：事务包含的所有操作要么全部成功，要么全部失败回滚
一致性（Consistency）：一个事务执行之前和执行之后都必须处于一致性状态
隔离性（Isolation）：跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改
持久性（Durability） ：一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的
1.2 三个概念：脏读、不可重复读、幻读
事务并发引起的读取的数据与预期不一致的情况
脏读
A事务读取到了B事务未提交的内容,而B事务后面进行了回滚.
不可重复读
在对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，另一个事务修改了数据并提交了。重点在于数据修改引起
幻读
A事务读取了一个范围的内容,而同时B事务在此期间插入了一条数据.造成"幻觉"。重点在于数据新增或删除
1.3 事务隔离级别
Repeatable read （可重复读）
确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，主要解决不可重复读的问题
Read committed （读已提交）
一个事务智能看见已经提交的事务所做的改变，主要解决的是脏读的问题
Serializable （串行化）
强制对事务排序，确保不会发生冲突，主要解决的是幻读的问题
Read uncommitted （读未提交）
所有事务能看到未提交事务的执行结果
1.4 MySQL是如何避免幻读的
在快照读情况下，MySQL通过mvcc来避免幻读。
在当前读情况下，MySQL通过next-key来避免幻读（加行锁和间隙锁来实现的）。
next-key包括两部分：行锁和间隙锁。行锁是加在索引上的锁，间隙锁是加在索引之间的。
Serializable隔离级别也可以避免幻读，会锁住整张表，并发性极低，一般不会使用。 -
索引
2.1 索引的数据结构
MySQL索引有两种数据结构：B-Tree(B+树)索引、 Hash索引
B-Tree索引
https://zhuanlan.zhihu.com/p/495578631#:~:text=Mysql索引机制,p/10341114.html
B-tree是一种平衡的m-way查找树，它可以利用多个分支节点（子树节点）来减少查询数据时所经历的节点数，从而达到节省存取时间的目的。m称为B-Tree的度。
B-tree的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历。在查找时存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。
Hash索引
基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。
2.2 索引的作用
数据通常是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。
索引能极大的减少存储引擎需要扫描的数据量，索引可以把随机IO变成顺序IO；索引可以帮助我们在进行分组、排序等操作时，避免使用临时表。
2.3 索引的优缺点
优点：加快查询速度、表连接速度、分组和排序速度
缺点：
（1）索引需要额外占用物理空间；
（2）降低表的增删改的效率，因为每次增删改需要动态维护索引，导致增删改时间变长
2.4 索引的使用场景
建议使用索引的场景：经常用于查询、表连接、排序的字段
不建议使用索引的场景：
（1）表记录少；
（2）经常需要增删改（会导致索引动态维护）
（3）区分度不高的字段，比如字段取值只有几个，如性别
（4）where条件中用不到的字段不需要建立索引
2.5 索引的设计原则
索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。
尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。
适量索引，不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。
利用最左前缀原则
2.6 索引失效
对于组合索引，不是使用组合索引最左边的字段，则不会使用索引
以%开头的like查询如%abc，无法使用索引；非%开头的like查询如abc%，相当于范围查询，会使用索引
查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效
判断索引列是否不等于某个值时
查询条件使用or连接，也会导致索引失效
2.7 最左匹配原则
对索引中关键字进行计算（对比），一定是从左往右依次进行，且不可跳过，在创建数据库的时候需要选择字符集及排序规则，这都是有用的 ，比如一棵B-tree中的根节点为一个字符串 abc ，那么我现在要搜索一个为 adc的索引关键字的数据，根节点abc的ASCII 码为 97 98 99，而 adc的为 97 100 99，那么和3个数字会逐一比对，且100>98，接下去一定会走右子树。
当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引。
2.8 索引相关问题
问：为什么官方建议使用自增长主键作为索引？
结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。
问：为什么索引结构默认使用B-Tree，而不是hash，二叉树，红黑树？
hash：虽然可以快速定位，但是没有顺序，IO复杂度高。
二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
红黑树：树的高度随着数据量增加而增加，IO代价高。
问：b-tree索引和hash索引相比的优缺点？
hash 索引
优点：
Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
缺点：
Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。
Hash 索引无法被用来避免数据的排序操作。
Hash 索引不能利用部分索引键查询。
Hash 索引在任何时候都不能避免表扫描。
Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
b-tree索引
优点：
数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点需要一次I/O就可以完全载入，大大减少了磁盘I/O
B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据
MySql索引数据结构对经典的B+Tree进行了优化。在原B-Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，只要顺着节点和指针顺序遍历就可以访问到所有数据节点，提高区间访问的性能。
3. 引擎
MyISAM
默认表锁
InnoDB
优点：（1）支持事务和崩溃修复能力；（2）引入了行级锁和外键约束
缺点：占用的数据空间相对较大
适用场景：需要事务支持，且有较高的并发读写频率
默认行锁
MEMORY
ARCHIVE
4. 日志
redo log
innodb引擎级别，用来记录innodb存储引擎的事务日志，不管事务是否提交都会记录下来
主要用于数据恢复
bin log
MySQL数据库级别的文件，记录对MySQL数据库执行修改的所有操作，不会记录select和show语句
主要用于恢复数据库和同步数据库
undo log
除了记录redo log外，当进行数据修改时还会记录undo log，undo log用于数据的撤回操作，它保留了记录修改前的内容
事务回滚，并且可以根据undo log回溯版本到某个特定的版本的数据
5. 架构
5.1 整体架构
MySQL主要分为 Server 层和存储引擎层：
Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
存储引擎： 主要负责数据的存储和读取。server 层通过api与存储引擎进行通信。
Server 层基本组件
连接器： 当客户端连接 MySQL 时，server层会对其进行身份认证和权限校验。
查询缓存: 执行查询语句的时候，会先查询缓存，先校验这个 sql 是否执行过，如果有缓存这个 sql，就会直接返回给客户端，如果没有命中，就会执行后续的操作。
分析器: 没有命中缓存的话，SQL 语句就会经过分析器，主要分为两步，词法分析和语法分析，先看 SQL 语句要做什么，再检查 SQL 语句语法是否正确。
优化器： 优化器对查询进行优化，包括重写查询、决定表的读写顺序以及选择合适的索引等，生成执行计划。
执行器： 首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会根据执行计划去调用引擎的接口，返回结果。
整体架构如下图所示：
5.2 查询执行过程
首先检查权限，没有权限则返回错误；
MySQL8.0以前会查询缓存，缓存命中则直接返回，没有则执行下一步；
词法分析和语法分析。提取表名、查询条件，检查语法是否有错误；
两种执行方案，先查 id > 1 还是 name = '大彬'，优化器根据自己的优化算法选择执行效率最好的方案；
校验权限，有权限就调用数据库引擎接口，返回引擎的执行结果。
5.3 更新执行过程
1.先查询到 id 为1的记录，有缓存会使用缓存。
2.拿到查询结果，将 name 更新为大彬，然后调用引擎接口，写入更新数据，innodb 引擎将数据保存在内存中，同时记录redo log，此时redo log进入 prepare状态。
3.执行器收到通知后记录binlog，然后调用引擎接口，提交redo log为commit状态。
4.更新完成。
问：为什么记录完redo log，不直接提交，而是先进入prepare状态？
假设先写redo log直接提交，然后写binlog，写完redo log后，机器挂了，binlog日志没有被写入，那么机器重启后，这台机器会通过redo log恢复数据，但是这个时候binlog并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
- SQL语法
7.分区表
分区表有三种类型：范围分区、list分区、hash分区，示例如下：
8. 锁
8.1 锁机制
当数据库有并发事务的时候,可能会产生数据的不一致,这时候需要一些机制来保证访问的次序,锁机制就是这样的一个机制。详细的介绍见如下链接：https://link.zhihu.com/?target=https%3A//learnku.com/articles/39212%3Forder_by%3Dvote_count%26
从类别上将，主要有共享锁（读锁）和排他锁（写锁）。
共享锁：又叫做读锁. 当用户要进行数据的读取时,对数据加上共享锁.共享锁可以同时加上多个
排他锁：又叫做写锁. 当用户要进行数据的写入时,对数据加上排他锁。排他锁只可以加一个,和其他的排他锁、共享锁都相斥。
select * from table where id<6 lock in share mode;--共享锁
select * from table where id<6 for update;--排他锁
这两种方式主要的不同在于LOCK IN SHARE MODE 多个事务同时更新同一个表单时很容易造成死锁。
申请排他锁的前提是，没有线程对该结果集的任何行数据使用排它锁或者共享锁，否则申请会受到阻塞。在进行事务操作时，MySQL会对查询结果集的每行数据添加排它锁，其他线程对这些数据的更改或删除操作会被阻塞（只能读操作），直到该语句的事务被commit语句或rollback语句结束为止。
SELECT... FOR UPDATE 使用注意事项：
for update 仅适用于innodb，且必须在事务范围内才能生效。
根据主键进行查询，查询条件为like或者不等于，主键字段产生表锁。
根据非索引字段进行查询，会产生表锁。
8.2 乐观锁和悲观锁
9. 三大范式
第一范式: 每个列都不可以再拆分.
第二范式: 非主键列完全依赖于主键,而不能是依赖于主键的一部分.
第三范式: 非主键列只依赖于主键,不依赖于其他非主键.
10. 优化
10.1 大表优化
- 优化sql语句和索引
时间类型转化为时间戳格式,用int类型储存,建索引增加查询效率
建议字段定义not null，null值很难查询优化且占用额外的索引空间
使用TINYINT类型代替枚举ENUM
存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE
字段长度严重根据业务需求来,不要设置过大
尽量不要使用TEXT类型，如必须使用建议将不常用的大字段拆分到其它表
MySQL对索引字段长度是有限制的, innodb引擎的每个索引列长度默认限制为767字节（bytes），所有组成索引列的长度和不能大于3072字节(mysql8.0单索引可以创建1024字符)
2. 考虑加缓存，memcache或redis
失效：先从cache取数据，没有得到，则从数据库取数据，成功后，放到缓存中
命中：从cache取数据，取到后返回
更新：先把数据存到数据库中，成功后，马上让缓存失效
3. 读写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
读写分离，使数据库能支撑更大的并发。
在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。
数据备份，保证数据的安全。
主从同步主要配置：二进制日志文件名和位置，独立ID
-
考虑使用分区表（见章节7.0）
-
分库分表，先考虑垂直拆分，其次考虑水平拆分
10.2 慢查询优化
首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写.
分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引.
如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表.
10.3 分页优化
limit限制查询范围+索引
依靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可
10.4 日常用过的一些优化
创建部分索引：经常用于查询和连接的字段做了索引
text类型字段单独建表
大表分区，按照月份做了范围分区
主从同步mysql：主表写，从表读
转载：https://zhuanlan.zhihu.com/p/495578631
标签：事务,缓存,log,基础知识,索引,mysql,整理,查询,数据 From： https://www.cnblogs.com/blog-cxyzy/p/18063187