主键 Primary Key

主键是对某一条记录的唯一标识 主键唯一，且一张表只能有一个主键 主键不能为空，可以是联合主键

唯一键Unique Key

保证某个键的值不能重复(不是主键) 主键主要用于唯一标识记录；唯一键用于保证业务字段唯一。 唯一建可以为NULL

普通索引

索引主要是为了提高查询速度，不唯一

外键

表示表和表之间的关联关系 一个表中的字段引用另一个表的主键或唯一键，用来保证两个表之间的数据关系合法。

全文索引

主要用于文本搜索

查询优化器

查询优化器： MySQL 一条 SQL，查询优化器负责分析这条 SQL，然后选择一个它认为执行成本更低的执行方案。 收起

SQL

索引

索引是帮助SQL高效获取数据的有序数据结构

• 利用索引可以提高数据检索的效率,降低io成本

• 通过索引对数据进行排序,减少cpu消耗

• 索引占一部分空间

• 索引降低了更新表的速度，对表进行插入删除的时候成本提高

索引类型

• B+树索引
• Hash索引：底层用哈希表实现，只支持精确匹配不支持范围查询
• R-Tree空间索引
• Full-text全文索引

索引结构

InnoDB MYISAM Memory

Q：为什么用B+树不用二叉树，红黑树，B树

A：顺序查找会形成单向链表，性能大大降低，每个节点最多只能存两个子节点，大数据量情况下检索效率低，红黑树在大数据量情况下检索效率也没有提高。而B树无论是叶子节点还是非叶子节点都要保存数据和指针，这样导致一层中能够保存的建值变少，指针跟着减少，要保存大量数据只能增加树的高度，导致性能降低

B+树

非叶子节点为索引，叶子节点存放数据，叶子节点是单向链表

MYSQL的B+树对B+树进行优化，叶子节点形成了双向链表

索引分类

• 主建索引——只能有一个 PRIMARY，主键索引不能重复，且主键索引不能为空
• 唯一索引——可以有多个 UNIQUE，唯一索引可以为空，但是不能重复
• 普通索引——建立在普通字段上的索引，可以重复页，可以为空
• 前缀索引——对字符类型字段的前几个字符建立的索引，而不是建立在某个字段上的索引，减少索引占用的内存空间
• 常规索引…
• 全文索引…

InnoDB索引类型：

• 聚焦索引：数据和索引在一起，索引的叶子节点保存了行数据——必须有且只有一个
• 二级索引：数据和索引分开存储，索引结构的叶子节点关联的是对应的主键——可以存在多个

                    InnoDB
                       │
             ┌─────────┴─────────┐
             ↓                   ↓
         聚簇索引             二级索引
             │                   │
      通常就是主键索引       name / age / email...
             │                   │
             ↓                   ↓
         整行数据              主键值

二级索引要回表查询,具体过程类似下图：

                查询 name
                    ↓
              二级索引 B+Tree
                    ↓
              找到主键 id
                    ↓
             ┌──────┴──────┐
             ↓             ↓
         需要其他字段     不需要其他字段
             ↓             ↓
          回表          直接返回
             ↓
       聚簇索引 B+Tree
             ↓
          完整数据

如果一张表存在主建，主键索引就是聚集索引 如果不存在主键，那么第一个索引UNIQUE为聚集索引 如果不存在主键也没有合适的索引，那么自动生成一个隐藏的rowid作为索引

联合索引

除了上文提到了建立在一个列上的单列索引，有时候会将多个字段组合建立联合索引 使用联合索引的时候，遵循最左匹配原则 也就是说，会先比较在左边的索引，在符合范围的数据内，匹配右边的索引 在这种情况下如果只依靠右边索引查询效率会变低

有一类特殊情况，并不是查询过程使用了联合查询，就代表联合索引中的所有字段都用到了联合查询。这种特殊情况发生在范围查询。范围查询的字段可以使用联合索引，但是在范围查询后面的字段无法使用联合索引，因为范围查询之后的数据不保证有序，此时索引存在的意义不大，只能通过遍历比较这一部分数据来查询匹配项。

索引失效的情况

1. 左右列模糊匹配
2. 对索引使用函数
3. 对索引进行表达式计算
4. 字符串和数字比较，如果字符串是索引列，会发生隐式类型转换，相当于对索引使用函数
5. 未满足最左匹配原则
6. where连接的查询语句中，有一列没有建立索引，也会导致失效

什么情况适合建立索引

• 字段有唯一性限制
• 经常用where查询的字段，建立索引可以提高查询速度，如果目标不是一个字段，可以建立联合索引
• 经常用order by和group by的字段，建立索引的话查询就不需要做排序，因为b+树里面的索引排序好了

什么情况不适合建立索引

• Where，Orderby，Groupby里面用不到的字段，如果建立索引会占用大量空间
• 表数据太少的时候也不需要索引
• 字段中出现大量重复数据，比如性别。MYSQL的优化器在进行优化的时候如果发现某个数据出现重复率很高，会默认不使用索引。
• 经常更新的字段也不需要建立索引，会导致B+树频繁进行页分裂，指针维护

怎么样对索引进行优化？

• 前缀索引优化，再进行一些大的字符串索引的时候，可以利用前缀索引，减少索引项的大小。
• 覆盖索引优化，减少回表查询次数，提高性能
• 主键索引最好自增，这样方便范围查询，而且加入新数据是插入操作不需要移动数据，性能非常高。如果主键不自增，那么添加的新数据有可能插入现有数据页的某个位置，导致页的复制或者内存碎片
• 防止索引失效，正确使用最左匹配原则，防止对索引使用函数

SQL日志都有哪些？

• redolog，引擎层日志，用于SQL掉电恢复
• undolog，引擎层日志，用于事务回滚和MVCC
• binlog，server层日志，用于数据备份和主从复制
• relaylog，中继日志，主从复制场景下，slave拷贝master的本地binlog之后生成的日志
• 慢查询日志：需要手动开启，记录执行时间过长的SQL

binlog

在执行完一条更新操作之后，server会生成一条binlog，等之后事务提交的时候，将本次事务中生成的所有binlog写入binlog日志。这个日志所有存储引擎都可以用。而且binlog是追加写，不会覆盖以前的日志。 binlog主要有三种格式：Statment，Row，Mixed，现在默认ROW

• Statment记录每一次修改操作，但是如果在执行的时候使用动态函数，可能导致同一条语句执行结果不同的情况。比如uuid，now这些函数。
• ROW，记录行数据最终被修改成什么样了。但是ROW的缺点是更新多少行就会产生多少条数据，binlog文件过大，如果是Statment的话只会记录一个update语句
• Mixed是以上两种的混合，根据不同情况自动选择使用模式

undolog

在事务提交之前，会先将要回退的操作记录在undolog之中。以便事务失败的回滚

redolog

redolog和undolog的区别是，redolog记录的是内存偏移量的操作。redolog记录了对哪个物理页做了什么修改，对XX表中的YY物理页ZZ偏移做了AA更新。事务提交的时候，redolog被持久化到磁盘，这个时候断电，虽然数据还没有被持久化到磁盘，但是物理页的偏移已经被记录，可以通过redolog恢复。 对redolog的操作是追加写，相比较磁盘的随机写，提高了执行性能。综上所述，redolog实际实现了：

• 将随机写改为追加写
• 实现事务的持久性