存储引擎MyISAM和InnoDB区别
- MyISAM是Mysql5.5默认的数据库引擎
- InnoDB是Mysql5.5以后引入的默认引擎
- MyISAM只有表级锁；InnoDB支持行级锁和表级锁（默认是行级锁）
- MyISAM强调性能，不支持事务，最大的缺陷是崩溃后无法安全恢复；InnoDB支持事务、外键、回滚、崩溃回滚等功能（假如项目是读密集的话，也可以采用MyISAM的，只要不害怕崩溃恢复问题）
- MyISAM不支持外键；InnoDB支持外键
- MyISAM不支持MVCC（多版本并发控制）；InnoDB支持MVCC
多版本控制MVCC:
- 多版本控制指的是一种提高并发的技术。
- 最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。
- 引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。
- 参考https://segmentfault.com/a/1190000012650596
索引
- MySQL的索引主要有BTree索引和哈希索引
- 哈希索引的底层数据结构就是哈希表，单条件查询的时候，可以算则哈希索引，查询速度快；其它大部分查询，可以选择BTree索引，BTree索引使用B树中的B+Tree作为数据库
- MyISAM中的BTree索引：叶子节点中的data域存放的是数据记录的地址。在检索索引的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，那么取出data的值，然后按照data中的地址去读取相应数据，这称为“非聚簇索引”
- InnoDB中的BTree索引：InnoDB中数据文件本身就是索引文件，MyISAM中的索引文件和数据文件是分离的；InnoDB中树的叶子结点保存了完整的数据记录，索引的Key就是数据表的主键，因此InnoDB表数据文件本身就是主索引，这被称为“聚簇索引”，其他的索引是辅助索引，辅助索引的data域存放相应记录的主键的值而不是地址。当使用辅助索引时，首先在辅助索引树中查找到主键值，再到主键索引树中查找数据
查询缓存
- 执行查询语句的时候，会先查询缓存
- MySQL8.0后取消，因为不太实用
事务四大特性
- 原子性（Atomic）：一个事务中的操作，要么一起执行，要么不执行
- 一致性（Consistency）：事务执行后，数据库的一致性约束没有被破坏，比如转账中账户扣除款项后必须给另一个账户加上款项
- 隔离性（Isolation）：两个并发事务互不干扰
- 持久性（Durability）：事务完成后，事务操作的数据会持久化存储
并发事务带来的问题
- 脏读：一个事物正在修改数据，数据还没提交就被另一个事务访问了
- 丢失修改：两个事务同时修改数据，那么会有一个事务提交的修改丢失了
- 不可重复读：一个事务多次访问一个数据，结果不一致
- 幻读：与不可重复读类似，主要是一个事务在读取时，另一个事务插入/删除了数据，那么造成前一个事务后面读取到了原来不存在的数据（或者原来读到的现在读不到了）
事务隔离级别
- 读取未提交：最低隔离级别，导致脏读、不可重复、读幻读
- 读取已提交：允许读取已经提交的数据，阻止脏读，导致不可重复、读幻读
- 可重复读：同一字段多次读取的结果一致，阻止脏读、不可重复读，导致幻读
- 可串行化，最高隔离级别，服从ACID，所有事物依次逐个执行，防止脏读、不可重复读、幻读
- InnoDB默认可重复读隔离级别，在分布式事务情况下，一般采用可串行化级别
锁机制与InnoDB锁算法
- 表级锁：粒度最大的一种，对整张表进行加锁，锁冲突概率高，并发度低，实现简单，资源消耗小，加锁快
- 行级锁：粒度最小的一种，对当前操作行加锁，锁冲突概率低，并发度高，实现复杂，资源消耗大，加锁慢
- 共享锁（S）：读锁
- 排他锁（X）：写锁
- 意向共享锁（IS）：当要访问的数据被排他锁锁住时，可以加一个意向共享锁，可以加多个，表级锁
- 意向排他锁（IX）：当要访问的数据被锁住的时候，可以加一个意向排他锁，仅可以加一个，表级锁
- InnoDB存储引擎的锁算法：
- Record lock：单个行记录上的锁
- Gap lock：间隙锁，锁定一个范围，不包括记录本身
- Next-key lock：record + gap，锁定一个范围，包括记录本身
- InnoDB在行查询的时候使用Next-key
- Next-key lock能解决幻读问题
- 查询的条件中使用索引单一主键时，Next-key降为Record
- Gap设计的目的是为了阻止多个事务将记录插入同一个范围之内，因为这会导致幻读
- 两种方式关闭Gap
- 将事务隔离级别设置为RC
- 将参数nonodb_locks_unsafe_for_binlog设定为1
大表优化
- 单表中数据过大时，CRUD性能会下降，需要优化：
- 限定数据范围：禁止不带限制的查询语句
- 读/写分离操作：主库写，从库读
- 垂直分区：按照数据表的相关性进行拆分（拆分属性，但是拆的不好就破坏了第一范式），尽量做到小表
- 水平分区：保持表结构不变，将数据分散到不同的表或者库中
分库分表后id主键如何处理
- 没有分库的时候，表的主键从1开始累加，但是分库分表以后，需要全局id，如下方式生成全局id：
- UUID：不适合做主键，因为太长了，且无序不可读，查询效率低，比较适合用于生成唯一名字的标识，比如文件名
- 数据库自增id：两台数据库设置不同步长，使用不同策略生成，但是需要独立部署
- 利用redis生成：灵活性能好，不依赖数据库，但是引入新组件redis后复杂
- snowflake雪花算法：
- 美团Leaf分布式ID生态系统：
池化设计思想，数据库连接池
- 池化不单在数据库中使用，java线程池、jdbc连接池、redis连接池等等
- 数据库连接本质是一个Socket连接
- 数据库服务端需要维护缓存和用户权限信息，需要占用一些内存
- 将数据库连接池看做是维护数据库连接的缓存，后面需要连接数据库时可以重新使用这些连接
- 每次用户使用数据库，都要打开和维护数据库连接的话，消耗资源
- 在连接池中，创建连接后，将其放置在池中，每次使用时直接用就行，不需要重新建立新的链接，也能减少用户等待时间
一条SQL语句如何在MySql中执行
- 连接器：主要用于身份验证和权限认证，用户账户登录成功后去权限表中查询权限
- 查询缓存：MySQL8.0后移出，执行查询的时候先查询缓存，缓存中以Key-Value存储，如果Key命中，直接返回Value
- 分析器：分为词法分析和语法分析。词法分析主要是提取关键字，比如字段名、表名、select等；语法分析是判断输入的sql语句是否正确，是否符合语法
- 优化器：使用优化器认为最优的方案去执行操作，当然，不一定是最优的，只能是优化器自己认为最优，比如多个索引的时候如何选择索引、多表查询时如何关联顺序
- 执行器：选择了优化器的执行方案后，就准备执行，执行前会检验用户有没有权限，然后再调用引擎的接口
- 查询语句执行流程
- 查询语句权限，如果没有权限，返回错误信息；
- 如果有权限，先查询缓存（8.0后不查询）
- 通过分析器进行词法分析，提取关键字，判断是否有语法错误
- 优化器确定执行方案
- 进行权限校验，没有权限就返回错误信息，有权限就调用数据库引擎接口，返回引擎执行结果
- 更新语句执行流程（InnoDB引擎）：update tb_student A set A.age='19' where A.name=' 张三 ';
- 先查询张三这条数据，如果有缓存，也会使用缓存
- 拿到查询的语句，将其age改为19，然后调用引擎API接口，写入数据
- InnoDB引擎将数据保存在内存中，同时记录redo log日志，此时redo log进入prepare状态，然后告诉执行器，执行完成了，随时可以提交
- 执行器收到通知后记录binlog，然后调用引擎接口，提交redo log为提交状态
- 更新完成
- 所以其实使用的是两阶段提交
- 为什么更新时使用两个日志模块
- 最初MySql没有InnoDB引擎，只有MyISAM引擎，所以之前是没有crash-safe数据库异常崩溃提交记录不消失功能（binlog只能用来归档）
- 并不是说一个日志模块不可以，只是InnoDB引擎通过redo log来支持事务
- 使用两阶段提交，保证不会因为两个日志的提交顺序造成问题
一条SQL语句执行得很慢的原因有哪些？
- 分为两种情况，一种是大多数情况是正常的，只是偶尔会出现很慢的情况；第二种是在数据量不变的情况下，语句一直都很慢
- 针对偶尔很慢：数据库在刷新脏页；拿不到锁
- 刷新脏页，数据库数据更新好，不会马上同步到磁盘中，而是将更新记录写到redo log中，等到空闲时在同步到磁盘中
- redo log写满了，只能暂停其他操作，先同步数据到磁盘中，这时候就会导致SQL执行得很慢
- 内存不够用了，如果一次查询的数据较多，恰好所查的页不在内存中，需要申请内存空间存储数据页，此时刚好内存不够了，那么需要淘汰内存数据页，如果是干净页，直接淘汰就可以了，如果恰好是脏页那么需要刷新脏页以后才能淘汰
- 拿不到锁，此时只能等了，没办法
- 针对一直很慢，就要好好考虑SQL语句了，分几种情况：
- 字段上没有索引，只能全表扫描
- 字段上有索引，但是却没有用上，比如在索引字段使用了运算符、函数等
- 使用其他索引而不是主键索引，要遍历两次索引树
高质量SQL建议
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
