Mysql原理总结

一、存储引擎

        MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，⽽且提供了⼤量的特性，包括全⽂索引、压缩、空间函数等，但MyISAM不⽀持事务和⾏级锁，⽽且最⼤的缺陷就是崩溃后⽆法安全恢复。MySQL 5.5版本后默认的存储引擎为InnoDB（事务性数据库引擎）。不考虑事务情况下可以考虑使用MyISAm

两者对比：

• 是否⽀持⾏级锁 : MyISAM 只有表级锁(table-level locking)，⽽InnoDB ⽀持⾏级锁(rowlevel locking)和表级锁，默认为⾏级锁。
• 是否⽀持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原⼦性,其执⾏速度⽐InnoDB类型更快，但是不提供事务⽀持。但是InnoDB 提供事务⽀持事务，外部键等⾼级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能⼒(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
• 是否⽀持外键： MyISAM不⽀持，⽽InnoDB⽀持。
• 是否⽀持MVCC ：仅 InnoDB ⽀持。应对⾼并发事务, MVCC⽐单纯的加锁更⾼效;MVCC只在READ COMMITTED 和 REPEATABLE READ 两个隔离级别下⼯作;MVCC可以使⽤ 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统⼀。

二、索引

        MySQL索引使⽤的数据结构主要有BTree索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝⼤多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余⼤部分场景，建议选择BTree索引。

        MySQL的BTree索引使⽤的是B树中的B+Tree，但对于主要的两种存储引擎的实现⽅式是不同的。

• MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，⾸先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“⾮聚簇索引”。
• InnoDB: 其数据⽂件本身就是索引⽂件。相⽐MyISAM，索引⽂件和数据⽂件是分离的，其表数据⽂件本身就是按B+Tree组织的⼀个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据⽂件本身就是主索引。这被称为“聚簇索引（或聚集索引） ”。⽽其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值⽽不是地址，这也是和MyISAM不同的地⽅。 在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再⾛⼀遍主索引。 因此，在设计表的时候，不建议使⽤过⻓的字段作为主键，也不建议使⽤⾮单调的字段作为主键，这样会造成主索引频繁分裂。

1、索引的种类

• 聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
• 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

（1）innodb中的聚簇索引

        在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

1. InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

2. 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

• 聚集索引（聚簇索引），表中记录的物理顺序与键值的索引顺序相同。一个表只能有一个聚集索引。
• 当定义了主键后，InnoDB会利用主键来生成其聚簇索引；
• 如果没有主键，InnoDB会选择一个非空的唯一索引来创建聚簇索引；
• 如果这也没有，InnoDB会隐式的创建一个自增的列来作为聚簇索引。

（2）MyISAM中的非聚簇索引

MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

 

扩展：聚集索引和非聚集索引的区别？分别在什么情况下使用？

聚集索引和非聚集索引的根本区别是表中记录的物理顺序和索引的排列顺序是否一致。

 

1）聚集索引的表中记录的物理顺序与索引的排列顺序一致。

• 优点是查询速度快，因为一旦具有第一个索引值的记录被找到，具有连续索引值的记录也一定物理的紧跟其后。
• 缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的 相应位置，必须在数据页中进行数据重排，降低了执行速度。在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效。

建议使用聚集索引的场合为：

• 某列包含了小数目的不同值。
• 排序和范围查找。

2）非聚集索引的记录的物理顺序和索引的顺序不一致。

其他方面的区别：

• 聚集索引和非聚集索引都采用了 B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点。
• 非聚集索引添加记录时，不会引起数据顺序的重组。

建议使用非聚集索引的场合为： 

• 此列包含了大数目的不同值；
• 频繁更新的列

看上去聚簇索引的效率明显要低于非聚簇索引， 因为每次使用辅助索引检索都要经过两次 B+树查找， 这不是多此一举吗？ 聚簇索引的优势在哪？

1）由于行数据和叶子节点存储在一起， 这样主键和行数据是一起被载入内存的， 找到叶子节点就可以立刻将行数据返回了， 如果按照主键 Id 来组织数据， 获得数据更快。

2）辅助索引使用主键作为"指针", 而不是使用地址值作为指针的好处是， 减少了当出现行移动或者数据页分裂时,辅助索引的维护工作， InnoDB 在移动行时无须更新辅助索引中的这个"指针"。 也就是说行的位置会随着数据库里数据的修改而发生变化， 使用聚簇索引就可以保证不管这个主键 B+树的节点如何变化， 辅助索引树都不受影响。

 

（3）innodb中的非聚簇索引（辅助索引）

• 唯一索引：由表上一个或者多个字段组合建立的索引，这个或者这些字段的值组合起来在表中不可以重复。如学生表中的’学号‘
• 非唯一索引：由表上一个或者多个字段组合建立的索引，这个或者这些字段的值组合起来在表中可以重复，不要求唯一。如学生表中的‘成绩’
• 主键索引（主索引）：是唯一索引的特定类型。表中创建主键时自动创建的索引 。一个表只能建立一个主索引。

2、索引匹配方式

（1）索引的匹配方式：  name、age、pos加了索引

• 全值匹配：和索引中的所有列都进行匹配：select * from table where name=? and age=? and pos=?
• 匹配最左前缀：只匹配前面的几列：select * from table where name=? and age=? 
• 匹配列前缀：select * from table where name like 'ju%'
• 匹配范围值：select * from table where name > ?    和    select * from table where name=? and age>?
• 只访问索引的查询：只查询具有索引的列，本质上是索引覆盖：select name,age,pos from table where name=? and age=? and pos=?

（2）索引的最左匹配特性：从数据块的左边开始匹配，再匹配右边的。

        当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，

• (张三,20,F)，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；
• (20,F)，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。
• (张三,F)，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了。

（3）索引失效

• 在最左匹配原则中，跳过中间列会导致后面的列索引失效
• 在索引上进行计算、函数、类型转换，会导致索引失效而进行全盘扫描
• 使用不等于（!=或<>）、is null、is not null都会导致索引失效
• like以通配符“%abc%”开头
• 使用or，查询没有索引的列

（4）回表

        在聚集索引中，select * from table where name=?  会使用两次索引查询，首先根据name索引找到id，再根据id索引找到这个值。当表小的时候，可能不用索引比用索引更快。适合用在表大的时候。

（5）索引覆盖

        在聚集索引中，不用select *，而是用select id，这样在name表中找到id时，直接把id返回，就不用了再根据id索引找了。

（6）索引下推

        select * from table where name=? and age=?   此时的查询过程是，首先在磁盘中查找到=name的元素，把这些元素读取到磁盘中，再根据age筛选剩余的元素。这样不好，与磁盘IO次数太多

        使用索引下推就是把name和age都放到磁盘中去读。原来的筛选是放在内存中，现在放到了磁盘中。

3、索引优化

1. 索引的列不允许为null
2. 使用短索引，单索引字段数不允许超过5个（组合索引），单表索引建议控制在5个以内
3. 使用索引扫描来做排序：当索引的列顺序和order by子句的顺序完全一致，且所有列的排序方式都一样时，mysql才能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当orderby子句引用的字段全部为第一张表时，才能使用索引做排序。
4. like语句操作：like“%aaa%”不会使用索引而like“aaa%”可以使用索引。
5. 不要在列上进行运算：select actor_id from actor where actor_id+1=5;
6. 尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询
7. union all,in,or都能够使用索引，但是推荐使用in
• explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
• explain select * from actor where actor_id in (1,2);
• explain select * from actor where actor_id = 1 or actor_id =2;
8. 范围列可以用到索引
• 范围条件是：<、<=、>、>=、between
• 范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列
9. 强制类型转换会全表扫描
• explain select * from user where phone=13800001234：不会触发索引
• explain select * from user where phone='13800001234'：触发索引
10. 更新十分频繁，数据区分度不高的字段上不宜建立索引
• 更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能
• 类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据，
• 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算
11. 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致
12. 创建索引的时候应该避免以下错误概念
• 索引越多越好
• 过早优化，在不了解系统的情况下进行优化

三、事务

1、ACID

• 原⼦性（Atomicity）： 事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么全部完成，要么完全不起作⽤；
• ⼀致性（Consistency）： 执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的；
• 隔离性（Isolation）： 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；
• 持久性（Durability）： ⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何影响。

2、并发事务带来的问题

• 第一类更新丢失：事务A、B同时更新数据，B先提交，A后回滚，导致B修改数据丢失
• 脏读（读到了未提交的数据）：事务A读取，事务B修改了数据并未提交，A读取到B修改后的数据，结束了事务，结果B回滚了。A读到了错误的数据
• 不可重复读（多次读取数据内容不一致）：事务A先读取数据，事务B修改了数据并提交，A再次读取内容不一致。
• 幻读（多次读书数据数量不一致）：事务A先读取数据，事务B添加或删除了数据并提交，A再次读取数据数量不一致。
• 第二类更新丢失：事务A、B同时更新数据，B先提交，A后提交，导致B修改数据丢失。

3、四种隔离级别

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更， 可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据， 可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。
• REPEATABLE-READ(可重复读)： 对同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改， 可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣。
• SERIALIZABLE(可串⾏化)： 最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，可以防⽌脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读）

四、锁

MyISAM和InnoDB存储引擎使⽤的锁：

• MyISAM采⽤表级锁(table-level locking)。
• InnoDB⽀持⾏级锁(row-level locking)和表级锁,默认为⾏级锁

1、锁分类

（1）兼容性

• 共享锁||读锁||S 锁(share lock)：其他事务可以读，但不能写。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁||写锁||X 锁(exclusive） ：其他事务不能读取，也不能写。允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
• 类型细分：
• 意向共享锁(IS Lock/intent share lock)
• 意向排他锁||互斥锁(IX Lock/intent exclusive lock)

（2）加锁机制

• 悲观锁||保守锁（pessimistic locking）:假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
• 悲观锁是数据库层面加锁，都会阻塞去等待锁。
• 乐观锁(optimistic locking)：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
• 乐观锁是一种思想，具体实现是，表中有一个版本字段，第一次读的时候，获取到这个字段。处理完业务逻辑开始更新的时候，需要再次查看该字段的值是否和第一次的一样。如果一样更新，反之拒绝。之所以叫乐观，因为这个模式有从数据库加锁，等到更新的时候再判断是否可以更新。
• 缺点：并发很高的时候，多了很多无用的重试。乐观锁，不能解决脏读的问题。

（3）锁策略

        锁的开销是较为昂贵的，锁策略其实就是保证了线程安全的同时获取最大的性能之间的平衡策略。

• 共享锁：lock in share mode，一个事务开启了共享锁，其他事务可读，但是不能改。
• 排他锁：开启事务后，update操作自动会开启排他锁（是因为隔离级别？）；或者后面加for update。一个事务开启了排他锁，其他事务不能进行任何操作
• 意向共享锁：意向锁是数据库自动操作的
• 意向排他锁
• 自增锁：主键id自增，删除之后还是接着往后自增，并不是一定连续的
• 临键锁：查询多条数据，做范围查询（between）时加了共享锁。那么在在这个范围内所有元素，及其范围外两端的两个元素，按照左开右闭的原则，加锁。
• 要求是范围查询
• 查询的列有索引。如果没有索引的话就锁住了表
• 间隙锁：做范围查询时，如果在范围内没有查询到任何结果，就会将临键锁退化为间隙锁
• 锁的范围和临键锁一致，那这样间隙锁和临键锁的区别在哪里？

锁与隔离级别的关系

• 脏读：排他锁
• 不可重复读：共享锁
• 幻读：间隙锁

五、日志

（1）错误日志(error log)

        记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。

        可以使用" --log-error=[file_name] "来指定mysqld记录的错误日志文件，如果没有指定file_name，则默认的错误日志文件为datadir目录下的 `hostname`.err

（2）查询日志(general log)

        记录建立的客户端连接和执行的语句。在超时时间内完成的查询是一般查询，可以将其记录到一般查询日志中，但是建议关闭这种日志（默认是关闭的），超出时间的查询是慢查询，可以将其记录到慢查询日志中。

（3）二进制日志(bin log)

        记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

        二进制日志包含了引起或可能引起数据库改变(如delete语句但没有匹配行)的事件信息，但绝不会包括select和show这样的查询语句。语句以"事件"的形式保存，所以包含了时间、事件开始和结束位置等信息。

        二进制日志是以事件形式记录的，不是事务日志(但可能是基于事务来记录二进制日志)，不代表它只记录innodb日志，myisam表也一样有二进制日志。

        对于事务表的操作，二进制日志只在事务提交的时候一次性写入(基于事务的innodb二进制日志)，提交前的每个二进制日志记录都先cache，提交时写入。

（4）慢查询日志(slow log)

        记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。

（5）中继日志(relay log)

        主从复制时使用的日志。