MySQL | Homeward

/maɪ ˌɛskjuːˈɛl/

一、索引#

优势：减少 I/O 次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；

劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的 1.5 倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表。

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

（1）主键索引：即主索引，不允许重复，不允许空值

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；

（2）唯一索引：用来建立索引的列的值必须是唯一的，允许空值

ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；

（3）普通索引：用表中的普通列构建的索引，没有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col')；

（4）全文索引：用大文本对象的列构建的索引

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

（5）组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

1.1 实现原理#

1. B+ 数

B 树是一种自平衡的树，能够保持数据有序。当描述一颗 B 树时需要指定它的阶数，阶数表示了一个结点最多有多少个孩子结点，一般用字母 m 表示阶数。当 m 取 2 时，就是我们常见的二叉搜索树。

B+ 树与 B 树通常用于数据库和操作系统的文件系统中。特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入，这与二叉树恰好相反，具体请参考 [2] 。

（1）B+ 树与 B 树

B+ 树与 B 树最大的不同是内部结点不保存数据，只用于索引，所有数据（或者说记录）都保存在叶子结点中，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的，查询更加稳定。

一般来说 B+ Tree 比 B Tree 更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，操作系统以页（page）为单位管理内存，一页（page）通常默认为 4K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小。

已知内存的读取速度是外存读取 I/O 速度的几百倍，根据外存的预读取原则，每次读取的时候，先把需要的数据读取到内存中，然后在内存中查找。那么提升查找速度的关键就在于尽可能少的磁盘 I/O。

每个节点中的 key 个数越多，那么树的高度越小，需要 I/O 的次数越少，因此一般来说 B+ Tree 比 B Tree 更快，因为 B+ Tree 的非叶节点中不存储 data，就可以存储更多的 key。

B+ 树相较于 B 树的优势：

磁盘读写代价更低
查询速度更稳定

（2）B+ 树与红黑树

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，这是因为使用 B+ 树访问磁盘数据有更高的性能。

为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻的节点也能够被预先载入。

B+ 树相较于红黑树的优势：

磁盘读写代价更低
更好的利用磁盘预读特性
查询速度更稳定

（3）B+ 树与哈希

最左前缀原则：找到符合最左前缀的第一项，之后的项通过向右遍历得到即可。由于在B+树结构的索引中，索引项是按照索引定义里面出现的字段顺序排序的，为查找一定范围内的数据或模糊查找提供了极大便利。

哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，如果要进行模糊查找，哈希表只能遍历整个表。同理，进行范围查找时，哈希表需要对范围内所有项一一查找。

此外，索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，就会发生哈希碰撞，如果保证链表不转化成其他结构，查找的时间就会大大增加。

（4）ID 自增

由于 B+ 树是有序的，ID 不自增而是乱序的话，那么插入时需要将后面的叶子节点进行移动，这样就会比较消耗时间。最坏情况下，如果刚好所在数据页已经满了，还需要进行页分裂操作，这样会更加糟糕。

如果主键是自增的，不需要移动位置、页分裂等操作，这样可以提高性能。

2. 哈希索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

无法用于排序与分组；
只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫 “自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+ Tree 索引之上再创建一个哈希索引，这样就让 B+ Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

3. 全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等查找条件使用 MATCH AGAINST，而不是普通的 WHERE。全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

4. 空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。必须使用 GIS 相关的函数来维护数据。

1.2 索引优化#

1. 独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。例如下面的查询不能使用 actor_id 列的索引：

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

2. 联合索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

最左原则：根据 B+ 树的最右前缀原则，如果 SQL 语句中用到了联合索引中最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询（>、<、between & like）就会停止匹配 [7] 。

3. 索引列的顺序

让选择性最强的索引列放在前面。索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;Copy to clipboardErrorCopied
   staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
               COUNT(*): 16049

4. 前缀索引

对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。前缀长度的选取需要根据索引选择性来确定。

5. 覆盖索引

索引包含所有需要查询的字段的值。具有以下优点：

索引通常远小于数据行的大小，只读取索引能大大减少数据访问量；
一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）；
对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

1.3 索引的使用条件#

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

以下情况会使得索引失效：

条件中有 or，即使有条件带索引也不会使用；要想使用 or，又想让索引生效，只能将 or 条件中的每个列都加上索引；
对于多列索引，如果不是使用的第一部分，则不会使用索引；
like 查询是以 % 开头的，不会用到索引；
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引。

二、查询性能优化#

2.1 表结构优化#

表设计时尽量符合三范式：

第一范式：表中列具有原子性，即列的信息，不能分解，关系型数据库自动满足；
第二范式：表中的记录是唯一的，通常设计一个主键来实现；
第三范式：表中不要有冗余数据，即表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放；

有些系统为了提高运行效率，就必须降低范式标准，适当保留冗余数据。此外可以满足以下规则：

根据自己的业务选择合适的引擎；
表的字段尽可能使用 NOT NULL；
如果知道字符串固定长度，那么就用 char 型，不要用 varchar 型；
主从分离，读从库，写主库；
当表的字段过多时，进行垂直分割；如果数据过多时，进行水平分割。

2.2 SQL语句优化#

不使用 Select *，只查询需要的字段，查询所有占用内存；
使用 LIMIT 语句来限制返回的数据，当数据量很大时避免使用 OFFSET ；
多表连接时，尽量小表驱动大表，即小表 join 大表；
使用缓存存储过程；
使用枚举或整数代替字符串类型；
开启慢查询，对慢 SQL 使用 explain 或 desc 进行性能分析，并优化 SQL。

2.3 重构查询方式#

1. 切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

2. 分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用；
分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询；
减少锁竞争；
在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩；
查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

三、切分#

3.1 水平切分#

水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

3.2 垂直切分#

垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

四、复制#

4.1 主从复制#

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

binlog 线程 ：将主服务器上的数据更改写入二进制日志（Binary log）中；
I/O 线程 ：从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）；
SQL 线程 ：读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

4.2 读写分离#

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。读写分离能提高性能的原因在于：

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

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

五、储存引擎#

事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句；
索引：InnoDB 主键使用聚簇索引，MyISAM 主键索引和二级索引使用非聚簇索引。
并发：InnoDB 支持行级锁和表级锁，MyISAM 只支持表级锁；
外键：InnoDB 支持外键；
备份：InnoDB 支持在线热备份；
崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，恢复的速度也更慢；
其它特性：MyISAM 支持压缩表和空间数据索引。

5.1 聚簇索引#

主键索引的叶子节点存放的是整行数据，而非主键索引的叶子节点存放的是主键的值。主键索引也被称为聚簇索引，而非主键索引也被称为二级索引。

主键查询：只需要搜索主键的 B+ 树，可直接得到；
非主键查询：先搜索索引树，得到主键的值，再到主键索引树搜索结果，也称作回表。

聚簇索引：表数据是和主键一起存储的，主键索引的叶结点存储行数据（包含了主键值），二级索引的叶结点存储行的主键值。使用的是 B+ 树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录（数据内容）。

非聚簇索引：表数据和索引是分成两部分存储的，主键索引和二级索引存储上没有任何区别。使用 B+ 树作为索引的存储结构，所有的节点都是索引，叶子节点存储的是索引加上索引对应的记录的数据。

1. 聚簇索引的优点

当需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好；
当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址,即多一次 I/O；
使用覆盖索引扫描的查询可以直接使用叶子节点中的主键值。

2. 聚簇索引的缺点

插入速度严重依赖于插入顺序：按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，我们一般会定义一个自增的 ID 列为主键。
更新主键的代价很高，因为将会导致被更新的行移动：对于 InnoDB 表，一般定义主键为不可更新。
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据：二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。
采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多：因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的 I/O 代价。