sql唯一索引异常_MySQL系列 一 引擎和索引

MySQL都有哪些引擎？

常见的有三种，InnoDB、MyISAM、MEMORY。

InnoDB有以下的特性：

• 支持事务，支持回滚等
• 支持外键
• 支持并发性
• 支持行级锁
• 不支持全文索引

InnoDB读写效率相对较差，数据存储占用空间大。

MyISAM特性有：

• 不支持事务
• 不支持外键
• 不支持并发
• 仅支持表级锁
• 支持全文索引

MyISAM占用空间小，处理速度快。

MyISAM自己维护了计数器，因此对于 select count(*) 查询速度更快。

MEMORY的数据全部放在内存中，速率最快，关机和重启均会丢失数据。

MySQL的索引类型？

以常见的InnoDB为例：

• 聚簇索引（聚集索引）：和数据存储在一起（同一个B+树中）
• 唯一索引
• 联合索引
• 普通索引

后三者称为“二级索引”，它记录的是主键，而不是数据存储的位置。

InnoDB的四大特性？

1、插入缓冲（insert buffer）

每次的插入操作会放入缓冲池中，然后按照一定的频率进行合并操作，再进行IO操作。

主要用于提升IO性能。但只对聚集索引（唯一索引）无效。

2、二次写（double write）

图片来源见上图水印

如上图所示，InnoDB将数据从缓存写入磁盘时，分成以下几步：

1. 将Page（数据页）汇总到缓存中
2. 写入硬盘上的共享表空间。这里性能好
3. 写入硬盘的目标位置
4. 如果第三步失败或发生异常，则会从共享表空间中恢复，再次写入

3、自适应哈希索引（ahi）

如果发现某二级索引被频繁访问时，会将该索引放入hash缓存中。hash查找非常快，因此可以提高索引的效率。

4、预读（read ahead）

有两种预读算法：

• 线性预读：即根据前面访问的地址继续向后读取。
• 随机预读：同一个数据块的不同page被读取时，会把该数据块的剩余page一并读入缓存。不过该方式已经在5.5中被废弃，因为比较复杂和不稳定。

InnoDB有哪些日志？

• 错误日志：记录出错的信息，也记录警告信息或正确信息
• 查询日志：记录所有对数据库的请求，无论是否被正确执行
• 慢查询日志：根据设置的阈值，将超时的sql记录在此
• 二进制日志：记录对数据库执行更改的所有操作
• 中继日志：也是二进制文件，slave用来同步和恢复
• 事务日志：记录事务执行。事务执行时会先写日志，事务提交后将日志缓冲写入磁盘

二进制日志（binlog）录入格式有哪些？有什么区别？

大致有三种方式：Statement、Row、Mixedlevel。

Statement：记录每一条会修改数据的sql。

优点：不需要记录每一行的具体变化，日志量少，性能高。

缺点：为了让slave能正确执行，还必须记录执行时的相关信息。而且UUID()等语句会产生一致性问题。

Row：不记录sql语句的上下文信息，只记录哪条记录被修改。

优点：记录内容简单，准确性高。因为记录的是最终被修改的值；

缺点：记录的量非常大。比如表结构被修改后，每一行的数据都会被记录。

Mixedlevel：上述两种的混合模式。避开了各自的缺点，不同的操作使用不同的方式记录。