一、索引

B+ Tree原理

1.数据结构

B Tree 指的是 Balance Tree(平衡树),平衡树是一颗查找树,并且所有叶子节点位于同一层。B+ Tree是基于B Tree和叶子节点顺序访问指针进行实现,它具有B Tree的平衡性,并且通过顺序访问指针来提高区间查询的性能。

2.操作

进行查找操作时,在根节点进行二分查找,找到一个 key 所在的指针,然后递归地在指针所在指向的节点进行查找,直到查找到叶子节点,然后再叶子节点进行二分查找,找出key所对应的data。

插入删除操作会破坏平衡树的平衡性,因此在插入删除操作之后,需要对树进行一个分裂、合并、旋转等操作来维护平衡性。。

3.与红黑树的比较

  1. 更少的查找次数:红黑树太高了,所以查找次数也就更多
  2. 利用磁盘预读特性:为了减少磁盘IO操作,磁盘每次都会进行预读操作,预读过程中,磁盘进行顺序读取,利用预读特性,相邻的节点也能够被预先载入,因为B+ Tree 是有序性的。

MySQL索引

索引是在存储引擎层实现的,而不是在服务层实现的,所以不同存储引擎具有不同的索引类型和实现。

  • 聚簇索引:将数据和索引存储在一起,找到索引也就找到了数据。
  • 非聚簇索引:将数据和索引分开结构,索引结构的叶子节点指向了数据的对应行。
  • InnoDB中,在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据需要二次查找,像符合索引,前缀索引,唯一索引,辅助索引叶子节点存储的不再是行的物理位置,而是主键值。

1.B+ Tree 索引

大多数 MySQL 存储引擎的默认索引类型,因为不再需要进行全表扫描,只需要对树进行搜索即可,所以查找速度很快。因为 B+Tree 的有序性,还可以用于排序分组。

InnoDB的B+Tree索引分为主索引和辅助索引,主索引的叶子节点data域记录着完整的数据记录,这种索引方式称为聚簇索引,因为无法把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的data域记录着主键的值,因此在使用辅助索引进行查找时,需要先查找到主键值,然后再到主索引中进行查找。

2.Hash索引

哈希索引能以O(1)时间进行查找,但是失去了有序性:
- 无法用于排序和分组
- 只支持精确查找,无法用于部分查找和范围查找

3.全文索引

MyISAM存储引擎支持全文索引,用于查找文本中的关键词,MySQL5.6.4版本中也开始支持全文索引。

4.空间数据索引

MyISAM存储引擎支持空间数据索引,可以用于地理数据存储。

索引优化

1.独立的列

在进行查询时,索引列不能是表达式的一部分,也不能是函数的参数

1
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

2.多列索引

在需要使用多个列作为条件进行查询时,使用多列索引比使用多个单列索引性能更好。

3.索引列的顺序

让选择性最强的索引列放在前面。

4.前缀索引

对于BLOB、VARCHAR等类型的列,必须使用前缀索引,只索引开始的部分字符。

5.覆盖索引

索引包含所有需要查询的字段的值。对于InnoDB引擎来说,若辅助索引能够覆盖查询,则无需访问主索引。

索引的优点

  • 大大减少了服务器需要扫描的数据行数
  • 帮助服务器避免进行排序和分组
  • 将随机IO变为顺序IO

索引的使用条件

  • 对于非常小的表,大部分情况下简单的全表扫描比建立索引更高效,对于中到大型的表,索引就非常有效
  • 但是对于特大型表,建立和维护索引的代价将会随之增加,这种情况下,需要用到一种技术可以直接区分出需要查询的一组数据,而不是一条记录一条记录的匹配,例如分区技术

二、查询性能优化

使用Explain进行分析

Explain用来分析SELECT查询语句的执行计划,例如查看是否用到索引。

优化数据访问

1.减少请求的数据量

  • 最好不要使用 select *
  • 只返回必要的行,LIMIT 语句进行限制
  • 缓存重复查询的数据

2.减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询

重构查询方式

  1. 切分大查询
    • 一个大查询如果一次性执行的话,可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
  2. 分解大连接查询
    • 让缓存更高效
    • 分解成多个单表查询,减少冗余记录的查询
    • 减少锁竞争

三、存储引擎

InnoDB

是MySQL默认的事务型存储引擎,实现了四个标准的隔离级别,默认级别是可重复读,在可重复读隔离级别下,通过MVCC+行锁防止幻影读。

主索引是聚簇索引,在索引中保存数据,避免直接读取磁盘。

MyISAM

设计简单,数据以紧密格式存储,提供了大量的特性,包括压缩表、空间数据索引等,不支持事务,对于锁,只支持表锁。

比较

  • 事务:InnoDB是事务型,可以使用 Commit 和ROLLBACK语句
  • 并发:MyISAM只支持表级锁,而InnoDB还支持行锁
  • 外键:InnoDB支持外键
  • 备份:InnoDB支持在线热备份
  • 崩溃恢复:MyISAM崩溃后概率比InnoDB高,而且恢复速度也较慢
  • 其他特性:MyISAM支持压缩表和空间数据索引

四、复制

主从复制

主要涉及三个线程:binlog线程、I/O线程和SQL线程

  • binlog 线程:负责将主服务器上的数据更改写入二进制日志中。
  • I/O 线程:负责从主服务器上读取二进制日志,并写入从服务器的中继日志。
  • SQL 线程:负责读取中继日志,解析出主服务器已经执行的数据更改并在从服务器中重放。

读写分离

主服务器处理写操作以及实时性要求比较高的读操作,而从服务器处理读操作。

提高性能的原因在于:
- 主从服务器负责各自的读和写,极大程度缓解了锁的争用
- 从服务器可以使用MyISAM,提升查询性能以及节约系统开销
- 增加冗余,提高可用性

五、事务

概念

事务指的是满足ACID特性的一组操作,可以通过 commit 提交事务,也可以通过 rollback 回滚事务

  • 原子性:事务被视为不可分割的最小单元,要么成功要么失败,回滚可以用回滚日志(Undo log)来实现。
  • 一致性:在一致性状态下,所有事务对同一个数据的读取结果都是相同的。
  • 隔离性:一个事务所做的修改在最终提交前,对其他事务是不可见的。
  • 持久性:一旦事务提交,则所做的修改将会永久保存在数据库中,数据库崩溃了可以通过重做日志(redo log)来实现。

若MySQL不显式的开启一个事务,则采用的是默认提交方式。

事务并发执行遇到的问题

  • 脏写:一个事务修改了另外一个未提交事务修改过的数据
  • 脏读:一个事务读了另外一个未提交事务修改过的数据
  • 不可重复读:一个事务只能读到另外一个已经提交的事务修改过的数据,并且其他事务每对该数据进行一次修改并提交后,该事务都能查到最新值
  • 幻读:一个事务先根据某些条件查询,查出结果后,再次根据之前条件查询,能把另一个事务插入的记录也读出来

事务的隔离级别

  • READ UNCOMMITTED隔离级别下,可能发生脏读、不可重复读和幻读问题。
  • READ COMMITTED隔离级别下,可能发生不可重复读和幻读问题,但是不可以发生脏读问题。
  • REPEATABLE READ隔离级别下,可能发生幻读问题,但是不可以发生脏读和不可重复读的问题。
  • SERIALIZABLE隔离级别下,各种问题都不可以发生。

MVCC

所谓的MVCC(多版本并发控制)指的就是在使用READ COMMITTD、REPEATABLE READ 这两种隔离级别的事务在执行普通的SELECT操作时访问记录的版本链的过程,这样子可以使不同事务的读-写、写-读操作并发执行,从而提升系统性能。READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是:生成ReadView的时机不同,READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView,而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView,之后的查询操作都重复使用这个ReadView就好了

参考文章
参考小册