### 引擎 **InnoDB**：适用于处理大数据，崩溃后可以利用事务日志很好地恢复；支持聚簇、B-Tree和全文索引，不支持Hash索引；支持数据缓存（访问数据时，表和索引数据会被缓存在缓冲池）、外键、行锁、事务和MVCC；可以混合查询InnoDB引擎的表和其他引擎的表；表文件：表名.frm（表结构文件）、表名.ibd（数据和索引文件）；速度较MyISAM慢； **MyISAM**：查询效率很高，适合大量读场景；支持B-Tree和全文索引，不支持聚簇、Hash索引；不支持数据缓存、外键、事务、行锁（支持表锁）；表文件：表名.frm（表结构文件）、表名.myd（数据文件）、表名.myi（索引文件）； **其他引擎**：**ARCHIVE**：档案，适用于存储大量独立的作为历史数据的数据，插入快，查询差；不支持索引，存储大小无限制，数据压缩较好，不支持delete和update；**PERFORMANCE\_SCHEMA**：性能，专注收集MySQL运行性能数据；数据在内存中，重启后丢失；大多数表只读，用户不能创建；**MEMORY**：内存，存储临时不重要的数据，例如作为缓存，适合大量读的情形；数据在内存中，重启后丢失；**CSV**：数据存储在CSV文件；**FEDERATED**：联盟，适用于分布式；MRG\_MYISAM：Merge多个相同定义的MyISAM表；**BLACKHOLE**：黑洞，像黑洞一样接受数据但不存储，适用于转发和过滤给Slave服务器； * * * #### 索引 **概念**：优点：减少I/O次数，加快检索、分组和排序速度；缺点：索引也是表，会占用磁盘空间，维护需要时间成本，降低新增修改数据效率，创建索引时需要对表加锁； **索引类型**：主键索引（不可重复，不可为空）、唯一索引（不可重复，可以空）、普通索引（无限制）、全文索引（对大文本text、blob的索引）、空间索引（GIS数据）、组合索引（对多列建立的索引，遵循最左前缀原则，ABC索引相当于建了A、AB、ABC3个索引）； **索引方法**：**哈希索引**：只有内存存储引擎才支持；实现原理：根据索引列的值计算hashcode，在hashcode的位置存储该行数据的物理位置实现；一个值对应一个hashcode，所以不支持排序和范围查询；**全文索引**：仅用于MyISAM和InnoDb（5.6后支持）；实现原理：对大文本字段分词，生成一份单词清单，索引时根据单词清单来；使用：不能like，使用match..against查询；5.7前只支持英文，5.7后通过ngram插件支持中文；查询字符串不能太短，至少4字节；**BTree索引**：每个节点包含索引键和数据，可以使用二分查找；**B+Tree索引**：数据都在叶子节点，其他节点只有索引键，磁盘读写代价更低； **索引实现结构**：主键索引为主索引，其他为辅助索引；**MyISAM实现非聚簇索引**：索引顺序与数据物理排列顺序无关，数据表和索引表分表存储，主索引和辅助索引叶子节点的key都存储键对应的数据物理地址；**InnoDb实现聚簇索引**：聚簇索引的顺序就是数据的物理存储顺序，数据在主索引中存储，主索引叶子节点的key存储键对应的数据，辅助索引叶子节点的key存储主键值；**没有主键：**一个表只有一个聚簇索引，没有主键，会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引； **索引使用**：**什么时候用**：主键自动建索引，查询条件，排序，外键，关联字段应该建索引；**什么时候不用**：经常增删改的列，大量重复的列，记录太少的表；**索引失效情况**：组合索引的NULL列、like %xx%、索引列有函数和表达式、使用不等于(<>!=，但主键和整型不会失效)、IS (NOT) NULL、字段类型不一致（包括字符串不加单引号）、OR（非每个条件都有索引时）；**索引优化**：最左前缀、模糊查询优化、全文索引、短索引（对前N个字符建索引）； * * * #### 锁 **分类**：**行锁**：只能作用在索引上；**间隙锁**：锁索引前面的间隙，开区间如（3，5），间隙锁是不互斥的，RU和RC隔离级别没有间隙锁；**临键锁**：行锁+间隙锁，即左开右闭的区间如（3，5\]；**共享锁S/排他锁X**：都是行锁，共享锁用于并发读，排他锁用于更新删除（不包括插入），事务请求时会根据隔离级别和数据是否存在可能获取到行锁、间隙锁、临建锁之一；**意向共享锁IS/意向排他锁IX**：属于表锁，用于判断锁冲突；**插入意向锁**：特殊的间隙锁，锁住一行数据，用于并发插入； **加锁分析**：不同隔离级别、查询记录是否存在，存在多条还是一条，加锁情况不一样； 2个事务可以同时获得IX锁、间隙锁，但不能同时获取X锁；A事务获取了间隙锁，B事务插入的数据在间隙区间内时B获取不到插入锁； 查询时若记录不存在则获取间隙锁，存在则获取行锁；A事务加了S锁则B事务可读不可改；A事务加了X锁则B事务不可读不可改； select...where id = ?：快照读，不加锁；select...lock in share mode：当前读，加S锁（若有多条记录还会对区间加间隙锁）；select...for update：当前读，加X锁（若有多条记录还会对区间加间隙锁）； 隔离级别RU和RC，无论条件列上是否有索引都不会锁表，只锁行；RR和串行化通过行锁+间隙锁实现锁表；事务获取X/S锁前一定会对表加上IX/IS锁； * * * #### 事务 **事务命令**：BEGIN、COMMIT、SAVEPOINT（设置回滚点）、ROLLBACK（默认回滚到BEGIN）、ROLLBACK TO SAVEPOINT（部分回滚，回滚到指定点）、SELECT @@AUTOCOMMIT/SET AUTOCOMMIT（查看设置自动提交）、SET TRANSACTION ISOLATION LEVEL（设置隔离级别）； **事务并发问题**：**脏读**：A读了B更新的数据，B回滚后是脏数据；**不可重复读**：A多次读取同一数据，B过程中更新并提交了数据，导致读取结果不一致；**幻读**：类似不可重复读，不可重复读侧重于修改，幻读侧重于新增或删除，导致少读或多读了数据； **事务隔离级别**：描述事务处理之间的相互影响。**读未提交**：A事务可读到B事务已更新未提交的数据，存在3个问题；**读提交**：A事务只能读到B事务已更新已提交的数据，只解决了脏读；**可重复读**：默认级别，A事务不能读到B事务已更新已提交的数据，使用MVCC加版本控制，select不更新版本号，读的历史版本数据是快照读，insert/update/delete会更新版本号是当前读，解决了脏读和不可重复读；**串行化**：会锁表，3个问题都解决了，但并发低； **MVCC**：多版本并发控制协议，大多数情况下代替行锁，通过MVCC+行锁提高并发，降低系统开销；**实现原理**：每行记录增加2个隐藏列：创建版本号和删除版本号（即事务ID）；select：查询创建版本小于等于当前事务版本并且删除版本未定义或大于当前版本的行数据；delete：更新当前行的删除版本号为当前事务ID；insert：更新当前行的创建版本号为当前事务ID，删除版本号为undefined；update：先拷贝insert，再delete旧行； **分布式事务**：允许多个数据库实例参与一个全局事务；**XA**：分布式事务，包括应用程序AP、资源管理器RM、事务管理器TM；MySQL**命令**：采用2PC，XA START/END/PREPARE/COMMIT/ROLLBACK xid（开启、表示事务内SQl完成、准备、提交、回滚事务）; **快照读**：**快照读**：简单的select，通过mvcc实现；**当前读**：select ... lock in share mode、select ... for update、insert、update、delete，通过行锁和间隙锁实现； * * * #### 日志 **分类**：**错误日志**：默认开启，文件名称为hostname.err，记录服务器启动关闭、运行错误、事件信息等；**查询日志**：默认关闭，记录用户所有的增删改查操作，耗性能和IO；**慢日志**：默认关闭，记录执行时间超过指定时间的查询语句，用于性能定位；**事务日志**：InnoDb特有，持久化到磁盘，提高事务效率（改表数据只改内存，记事务日志，后续通过日志慢慢刷到磁盘），崩溃恢复，采用追加写；**二进制日志**：默认关闭，变更日志，记录修改数据的SQL语句及时间、数据等信息； **事务日志**：InnoDb引擎实现，包括**redo log**：重做日志，提供前滚操作，物理日志，记录数据页的物理修改，而不是行数据的修改；**undo log**：回滚日志，提供回滚操作，逻辑日志，根据每行数据进行记录（反着记，insert时undolog记录delete）；刷盘：redo/undolog buffer到os buffer，调用fsync将os buffer写到磁盘redo/undolog file，2个阶段的策略可通过参数控制（每次提交时刷，每秒刷，buffer内存超过一半刷等）； **二进制日志**：数据库实现，也可用于数据库恢复，3种记录方式：statement（记录SQL）、row（记录改的数据行）、mixed（简单的记SQL，复杂的记行）；查看日志：show binlog events或者使用mysqlbinlog工具； * * * #### 缓存 **存储**：Key（SQL+数据库+客户端协议）Value（缓存结果）hash表存储在内存中；相同SQL直接取缓存；**缓存失效**：对数据和表结构的任何修改会清空表的缓存，表数据修改中提交前缓存失效；**不存缓存**：包含不确定查询结果时不会存缓存（like、now()等）；**不使用缓存**：事务ID比当前数据库最大事务ID大的才使用缓存；InnoDB中有加锁操作的事务不使用缓存； * * * #### 备份 **分类**：完全备份、部分备份；全量备份、增量备份、差异备份；热备、温备、冷备；物理备份、逻辑备份；**备份策略**：全量+差异+binlogs二进制日志时间点还原、全量+增量+binlogs二进制日志时间点还原；**备份内容**：数据、二进制日志、事务日志、代码（存储过程、存储函数、触发器、事件调度器）、服务器的配置文件；**备份工具**：mysqldump、cp（拷贝文件）； * * * #### 性能优化 **参数优化**：wait-timeout（服务器关闭非交互连接之前等待活动的秒数）、interactive\_timeout（服务器关闭交互式连接前等待活动的秒数）、max\_connections（并发链接数）、max\_user\_connections（单用户最大连接数）、thread\_concurrency（并发线程数CPU\*2）、skip-name-resolve（禁止DNS解析，IP连接时关闭）、innodb\_buffer\_pool\_size（缓存大小）、innodb\_log\_buffer\_size （事务日志缓存大小）、query\_cache\_size（查询缓存大小）； **执行计划**：主要优化type访问类型；system：标志有一行记录，const：通过索引一次找到，eq\_ref：唯一索引，ref：非唯一索引，可能多行，range：范围索引，index：全索引扫描，all：全表扫描； **分库分表**：垂直拆分（按列拆，大表拆小表）、水平拆分（按行拆，大表拆小表）；拆分策略：Hash取模、范围分片、地理位置分片、时间分片；问题：分布式事务、跨库Join、数据合并、横向扩容；分库分表中间件：mycat、Sharding-JDBC； **其他优化**：服务器硬件、数据库配置、操作系统配置、索引优化、SQL优化等； 作者：星冉子 链接：https://www.jianshu.com/p/19c6e049bca3 来源：简书 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。