MySQL

2019/11/08 Knowledge

参考来源

目录

一、索引

B+ Tree原理

1. 数据结构

  • B Tree

    阶数:该节点最多有多少个子节点。

    B Tree 指的是 Balance Tree,也就是平衡树。平衡树是一颗查找树,并且所有叶子节点位于同一层。

    B 树的平衡条件则有三点:

    • 叶子节点都在同一层
    • 每个节点的关键字数为子树个数减一(子树个数 k 是 [M/2, M]。M 是树的阶数,即该树每个几点最多有多少个子节点),原因是叶节点的值对应的是比根节点最左节点小到比最右节点大
    • 子树的关键字保证左小右大的顺序

    B Tree

  • B+ Tree

    B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现,它具有 B Tree 的平衡性,并且通过顺序访问指针来提高区间查询的性能。

    在 B+ Tree 中,一个节点中的 key 从左到右非递减排列,如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1,且不为 null,则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1

    一棵 B+ 树需要满足以下条件:

    • 节点的子树数和关键字数相同(B 树是关键字数比子树数少一,因为他的节点是子树的最大值)
    • 节点的关键字表示的是子树中的最大数,在子树中同样含有这个数据
    • 叶子节点包含了全部数据,同时符合左小右大的顺序

    B+ Tree

    首先第一点不用特别介绍了,在 B 树中,节点的关键字用于在查询时确定查询区间,因此关键字数比子树数少一;而在 B+ 树中,节点的关键字代表子树的最大值,因此关键字数等于子树数。

    第二点,除叶子节点外的所有节点的关键字,都在它的下一级子树中同样存在,最后所有数据都存储在叶子节点中。

    根节点的最大关键字其实就表示整个 B+ 树的最大元素。

    第三点,叶子节点包含了全部的数据,并且按顺序排列,B+ 树使用一个链表将它们排列起来,这样在查询时效率更快。

    由于 B+ 树的中间节点不含有实际数据,只有子树的最大数据和子树指针,因此磁盘页中可以容纳更多节点元素,也就是说同样数据情况下,B+ 树会 B 树更加“矮胖”,因此查询效率更快。

    B+ 树的查找必会查到叶子节点,更加稳定。

    有时候需要查询某个范围内的数据,由于 B+ 树的叶子节点是一个有序链表,只需在叶子节点上遍历即可,不用像 B 树那样挨个中序遍历比较大小。

    B+ 树的三个优点:

    • 层级更低,IO 次数更少
    • 每次都需要查询到叶子节点,查询性能稳定
    • 叶子节点形成有序链表,范围查询方便
  • 为什么用 B+ 树做 MySQL 索引?

    二叉查找树可能左右子树不平衡。

    二叉平衡树查找索引次数过多,旋转耗时。

    红黑树树太高,索引查找次数多,查找索引,磁盘 IO 次数会太多。

    与 B 树的比较:

    • 更少的 IO 次数:B+ 树的非叶节点只包含键,而不包含真实数据,因此每个节点存储的记录个数比 B 数多很多(即阶 m 更大),因此 B+ 树的高度更低,访问时所需要的 IO 次数更少。此外,由于每个节点存储的记录数更多,所以对访问局部性原理的利用更好,缓存命中率更高
    • 更适于范围查询:在 B 树中进行范围查询时,首先找到要查找的下限,然后对 B 树进行中序遍历,直到找到查找的上限;而 B+ 树的范围查询,只需要对链表进行遍历即可
    • 更稳定的查询效率:B 树的查询时间复杂度在1到树高之间(分别对应记录在根节点和叶节点),而 B+ 树的查询复杂度则稳定为树高,因为所有数据都在叶节点。
    • 增删文件(节点)时,效率更高,因为 B+ 树的叶子节点包含所有关键字,并以有序的链表结构存储,这样可很好提高增删效率。

    B+ 树也存在劣势:由于键会重复出现(每个节点保存的是范围最大值),因此会占用更多的空间。但是与带来的性能优势相比,空间劣势往往可以接受,因此 B+ 树的在数据库中的使用比B树更加广泛。

2. 操作

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

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

3. 与红黑树的比较

红黑树等平衡树也可以用来实现索引,但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构,主要有以下两个原因:

  • 更少的查找次数

    平衡树查找操作的时间复杂度和树高 h 相关,O(h)=O(logdN),其中 d 为每个节点的出度。

    红黑树的出度(叶子结点数))为 2,而 B+ Tree 的出度一般都非常大,所以红黑树的树高 h 很明显比 B+ Tree 大非常多,查找的次数也就更多。

  • 利用磁盘预读特性

    为了减少磁盘 I/O 操作,磁盘往往不是严格按需读取,而是每次都会预读。预读过程中,磁盘进行顺序读取,顺序读取不需要进行磁盘寻道,并且只需要很短的磁盘旋转时间,速度会非常快。

    操作系统一般将内存和磁盘分割成固定大小的块,每一块称为一页,内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小,使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性,相邻的节点也能够被预先载入。

    在磁盘逻辑上,深度的差值实际上可能存储结构就离得非常远,无法利用局部性,所以红黑树的I/O渐进复杂度也为O(h),效率明显比B-Tree差很多。

4. 为什么不用二叉查找树

虽然都是 O(logN),但索引一般很大,不可能一次插入到内存中,必须分页加载。可以把磁盘页对应成树的节点。二叉树最坏的情况下的查找次数可能就会是树的高度。而 B 树矮胖,每一个节点最多包含 k(B 树的阶)个孩子。k 大小可以取决于磁盘页的大小。

MySQL 索引

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

什么时候建索引

  1. 较频繁地作为查询条件的字段:经常需要查找的字段。

  2. 唯一性太差的字段不适合建立索引:如果一个字段就几种可能性(类型字段),无需建索引,因为挨个查就好。

  3. 更新太频繁地字段不适合创建索引:索引的更新耗时耗力,字段更新太频繁对性能会有极大影响。

  4. 不会出现在where条件中的字段不该建立索引:不当条件,建索引干嘛?

索引的分类

  • 数据结构的角度
    • B+ 树索引(O(log(n)))
    • HASH 索引
    • FULLTEXT 索引
    • 空间数据索引(R-Tree索引)
  • 从物理存储角度
    • 聚集索引(clustered index):存储全部数据
    • 非聚集索引(non-clustered index):存储主键
  • 从逻辑角度(人为角度划分的)
    • 主键索引:主键索引是一种特殊的唯一索引,不允许有空值
    • 普通索引或者单列索引
    • 多列索引(复合索引):复合索引指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用复合索引时遵循最左前缀集合
    • 唯一索引或者非唯一索引
    • 空间索引:空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。

带索引的建表语句:

CREATE TABLE table_name[col_name data type] [unique|fulltext|spatial][index|key]index_name[asc|desc]

CREATE  table address(
id  int(11)  auto_increment  primary  key  not  null,
name  varchar(50),
address  varchar(200),
UNIQUE  INDEX  address(id  ASC)
);

1. B+Tree 索引

是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描,只需要对树进行搜索即可,所以查找速度快很多。

因为 B+ Tree 的有序性,所以除了用于查找,还可以用于排序和分组。

可以指定多个列作为索引列,多个索引列共同组成键。

适用于全键值、键值范围和键前缀查找,其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找,则无法使用索引。

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

聚簇索引

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

辅助索引

2. 哈希索引

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

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

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

3. 全文索引

MyISAM 存储引擎支持全文索引,用于查找文本中的关键词,而不是直接比较是否相等。

查找条件使用 MATCH AGAINST,而不是普通的 WHERE。

全文索引使用倒排索引实现,它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

4. 空间数据索引

MyISAM 存储引擎支持空间数据索引(R-Tree),可以用于地理数据存储。空间数据索引会从所有维度来索引数据,可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据。

索引优化

1. 独立的列

在进行查询时,索引列不能是表达式的一部分,也不能是函数的参数,否则无法使用索引。

例如下面的查询不能使用 actor_id 列的索引:

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

应该更改为:

SELECT `actor_id` FROM sakila.actor WHERE actor_id = 4;

应该养成简化 WHERE条件的习惯, 始终将索引列单独放在比较符号的一侧。

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

上面这样这样也是可以的。

2. 多列索引

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

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

索引建立方式(两种方式等价):

create INDEX actorid_filmid on film_actor(actor_id, film_id);
ALTER table film_actor ADD INDEX actorid_filmid(actor_id, film_id)

注意 1 : 建立索引 actor_id 和 film_id 的顺序是有区别的,谁在前就是先查谁的索引。

注意 2 : 比如 (a,b,c) 作为联合索引,如果直接查询 b 和 c 这样是没有效果的,因为先得拿到 a 的索引,才能继续找 b 和 c 的索引。而如果查询 a 和 c 则索引效果只能到查询完 a,因为索引下没有 b 的查询了。

索引的字段可以是任意顺序的,如:

SELECT * FROM test WHERE col1=1 AND clo2=2
SELECT * FROM test WHERE col2=2 AND clo1=1

这两个查询语句都会用到索引 (col1,col2),mysql 创建联合索引的规则是首先会对联合合索引的最左边的,也就是第一个字段 col1 的数据进行排序,在第一个字段的排序基础上,然后再对后面第二个字段 col2 进行排序。其实就相当于实现了类似 order by col1 col2 这样一种排序规则。

有人会疑惑第二个查询语句不符合最左前缀匹配:首先可以肯定是两个查询语句都包含索引 (col1,col2) 中的 col1、col2 两个字段,只是顺序不一样,查询条件一样,最后所查询的结果肯定是一样的。既然结果是一样的,到底以何种顺序的查询方式最好呢?此时我们可以借助 mysql 查询优化器 explain,explain 会纠正 sql 语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。

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;
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049

所以索引这么建:

create index customerid_staffid on payment(customer_id, staff_id);

简单想法就是先筛选标签数多的,这样筛出来的数量比较少,之后再查也就好查了

4. 前缀索引

如果想要索引 BLOB、TEXT 和 VARCHAR 类型的列,必须使用前缀索引,只索引开始的部分字符。

前缀长度的选取需要根据索引选择性来确定。MySQL不允许索引这些列的完整长度。诀窍在于要选择足够长的前缀。

下面代码是为 x_name 创建长度为 1 的前缀索引。

alter table x_test add index(x_name(1))

5. 覆盖索引(索引涵盖全部查询字段)

索引包含所有需要查询的字段的值。

具有以下优点:

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

如:

create INDEX actorid_filmid on film_actor(actor_id, film_id);
SELECT film_id, actor_ id FROM film_actor
WHERE actor_id = 1 AND film_id = 1;

即为覆盖索引。

最左前缀匹配原则

在 mysql 建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,示例: 对列 col1、列 col2 和列 col3 建一个联合索引

create index on xxx(col1,col2,col3);

联合索引 test_col1_col2_col3 实际建立了 (col1)、(col1,col2)、(col,col2,col3) 三个索引。

SELECT * FROM xxx WHERE col1=1 AND clo2=2 AND clo4=4

上面这个查询语句执行时会依照最左前缀匹配原则,检索时会使用索引(col1,col2)进行数据匹配。

但如果是

SELECT * FROM xxx WHERE clo2=2

则无法使用任何索引。

索引的优点

  • 大大减少了服务器需要扫描的数据行数。
  • 帮助服务器避免进行排序和分组,以及避免创建临时表(B+Tree 索引是有序的,可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建,不需要排序和分组,也就不需要创建临时表)。
  • 将随机 I/O 变为顺序 I/O(B+Tree 索引是有序的,会将相邻的数据都存储在一起)。

索引的适用情况

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

二、 查询性能优化

使用 Explain 进行分析

Explain 用来分析 SELECT 查询语句,开发人员可以通过分析 Explain 结果来优化查询语句。

比较重要的字段有:

  • select_type : 查询类型,有简单查询、联合查询、子查询等
  • key : 使用的索引
  • rows : 扫描的行数
EXPLAIN SELECT * FROM app WHERE id = 1;

结果:

EXPLAIN 结果

优化数据访问

1. 减少请求的数据量

  • 只返回必要的列:最好不要使用 SELECT * 语句,如果只关注数据条数并且查询条件没有主键(有主键会 logN 更快),select count(1) 会更好,这个数字不重要,只是返回多少行。
  • 只返回必要的行:使用 LIMIT 语句来限制返回的数据。
  • 缓存重复查询的数据:使用缓存可以避免在数据库中进行查询,+ 特别在要查询的数据经常被重复查询时,缓存带来的查询性能提升将会是非常明显的。(MySQL 默认开启)

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

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

重构查询方式

1. 切分大查询

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

删除旧的数据就是一个很好地例子。定期清理旧数据时,如果一条sql涉及了大量的数据时,可能会一次性锁住多个表或行,耗费了大量的系统资源,却阻塞了其他很多小的但重要的查询。将一个大得DELETE语句切分为较小的查询时,可以尽量减少影响msql的性能,同时减少mysql复制造成的延迟。

例如,每个月会运行一次的语句,以清理三个月前的数据:

DELETE FROM messages WHERE dt<DATE_SUB(NOW(), INTERVAL 3 MONTH);

应该用循环方式,减少长时间的锁表,所以修改为:

rows_affected = 0
do {
   rows_affected = do_query("DELETE FROM messages WHERE dt<DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
}while rows_affected>0

2. 分解大连接查询

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

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

应转换为:

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

三、存储引擎

InnoDB

B+ 树作为索引。

是 MySQL 默认的事务型存储引擎,只有在需要它不支持的特性时,才考虑使用其它存储引擎。

实现了四个标准的隔离级别,默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下,通过多版本并发控制(MVCC)+ Next-Key Locking 防止幻影读。

主索引是聚簇索引,在索引中保存了数据,从而避免直接读取磁盘,因此对查询性能有很大的提升。

内部做了很多优化,包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份,要获取一致性视图需要停止对所有表的写入,而在读写混合场景中,停止写入可能也意味着停止读取。

InnoDB 的行锁,只是在 WHERE 的主键是有效的,非主键的 WHERE 都会锁全表的。

MyISAM

MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分。

设计简单,数据以紧密格式存储。对于只读数据,或者表比较小、可以容忍修复操作,则依然可以使用它。

提供了大量的特性,包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁,只能对整张表加锁,读取时会对需要读到的所有表加共享锁,写入时则对表加排它锁。但在表有读取操作的同时,也可以往表中插入新的记录,这被称为并发插入(CONCURRENT INSERT)。

可以手工或者自动执行检查和修复操作,但是和事务恢复以及崩溃恢复不同,可能导致一些数据丢失,而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项,在每次修改执行完成时,不会立即将修改的索引数据写入磁盘,而是会写到内存中的键缓冲区,只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能,但是在数据库或者主机崩溃时会造成索引损坏,需要执行修复操作。

表的具体行数:MyISAM:保存有表的总行数,如果 select count(*) from table; 会直接取出出该值;InnoDB:没有保存表的总行数(只能遍历),如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了 where条件后,myisam 和 innodb 处理的方式都一样。

两者比较

  • 事务:InnoDB 是事务型的,可以使用 Commit 和 Rollback 语句。

  • 并发:MyISAM 只支持表级锁,而 InnoDB 还支持行级锁。

  • 外键:InnoDB 支持外键。

  • 备份:InnoDB 支持在线热备份。

  • 崩溃恢复:MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多,而且恢复的速度也更慢。

  • 其它特性:MyISAM 支持压缩表和空间数据索引。

何时使用 INNODB,何时使用 MyISAM

如果你的应用程序一定要使用事务,毫无疑问你要选择 INNODB 引擎。但要注意,INNODB的 行级锁是有条件的。在 where 条件没有使用主键时,照样会锁全表。比如 DELETE FROM mytable 这样的删除语句。

如果你的应用程序对查询性能要求较高,就要使用 MYISAM 了。MYISAM 索引和数据是分开的,而且其索引是压缩的,可以更好地利用内存。所以它的查询性能明显优于 INNODB。压缩后的索引也能节约一些磁盘空间。MYISAM 拥有全文索引(InnoDB 后面的版本也有了)的功能,这可以极大地优化LIKE查询的效率。

Memory 和 Archive

MEMORY 存储引擎将表中的数据存储到内存中,未查询和引用其他表数据提供快速访问。

Arcive 只有INSERT和SELECT操作,可以选择 Archive,Archive 支持高并发的插入操作,但是本身不是事务安全的。Archive 非常适合存储归档数据,如记录日志信息可以使用Archive。

对比图

对比引擎

四、数据类型

整形

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间,一般情况下越小的列越好。

INT(11) 中的数字只是规定了交互工具显示字符的个数,对于存储和计算来说是没有意义的。

浮点数

FLOAT 和 DOUBLE 为浮点类型,DECIMAL 为高精度小数类型。CPU 原生支持浮点运算,但是不支持 DECIMAl 类型的计算,因此 DECIMAL 的计算比浮点类型需要更高的代价。

FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽,例如 DECIMAL(18, 9) 表示总共 18 位,取 9 位存储小数部分,剩下 9 位存储整数部分。

float 数值类型用于表示单精度浮点数值,而 double 数值类型用于表示双精度浮点数值,float 和 double 都是浮点型,而 decimal 是定点型;

MySQL 浮点型和定点型可以用类型名称后加(M,D)来表示,M表示该值的总共长度,D 表示小数点后面的长度,M 和 D 又称为精度和标度,如 float(7,4) 的可显示为 -999.9999,MySQL 保存值时进行四舍五入,如果插入 999.00009,则结果为 999.0001。

FLOAT 和 DOUBLE 在不指定精度时,默认会按照实际的精度来显示,而 DECIMAL 在不指定精度时,默认整数为 10,小数为 0。

字符串

char 和 varchar

主要有 CHAR 和 VARCHAR 两种类型,一种是定长的,一种是变长的。

VARCHAR 这种变长类型能够节省空间,因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长,当超出一个页所能容纳的大小时,就要执行额外的操作。MyISAM 会将行拆成不同的片段存储,而 InnoDB 则需要分裂页来使行放进页内。

在进行存储和检索时,会保留 VARCHAR 末尾的空格,而会删除 CHAR 末尾的空格。

char(n)和varchar(n)中括号中n代表字符的个数,并不代表字节个数,所以当使用了中文的时候(UTF8)意味着可以插入n个中文,但是实际会占用n*3个字节。

  • char:存储定长数据很方便,CHAR字段上的索引效率级高,必须在括号里定义长度,可以有默认值,比如定义char(10),那么不论你存储的数据是否达到了10个字符,都要占去10个字符的空间(自动用空格填充),且在检索的时候后面的空格会隐藏掉,所以检索出来的数据需要记得用什么trim之类的函数去过滤空格。

  • varchar:存储变长数据,但存储效率没有CHAR高,必须在括号里定义长度,可以有默认值。保存数据的时候,不进行空格自动填充,而且如果数据存在空格时,当值保存和检索时尾部的空格仍会保留。另外,varchar类型的实际长度是它的值的实际长度+1,这一个字节用于保存实际使用了多大的长度。

  • text:存储可变长度的非Unicode数据,最大长度为2^31-1个字符。text列不能有默认值,存储或检索过程中,不存在大小写转换,后面如果指定长度,不会报错误,但是这个长度是不起作用的,意思就是你插入数据的时候,超过你指定的长度还是可以正常插入。

VARCHAR(100)与VARCHAR(200)真的相同吗?结果是否定的。虽然他们用来存储90个字符的数据,其存储空间相同。但是对于内存的消耗是不同的。对于VARCHAR数据类型来说,硬盘上的存储空间虽然都是根据实际字符长度来分配存储空间的,但是对于内存来说,则不是。其实使用固定大小的内存块来保存值。

时间和日期

MySQL 提供了两种相似的日期时间类型:DATETIME 和 TIMESTAMP。

  1. DATETIME

    能够保存从 1000 年到 9999 年的日期和时间,精度为秒,使用 8 字节的存储空间。

    它与时区无关。

    默认情况下,MySQL 以一种可排序的、无歧义的格式显示 DATETIME 值,例如“2008-01-16 22:37:08”,这是 ANSI 标准定义的日期和时间表示方法。

  2. TIMESTAMP

    和 UNIX 时间戳相同,保存从 1970 年 1 月 1 日午夜(格林威治时间)以来的秒数,使用 4 个字节,只能表示从 1970 年到 2038 年。

    它和时区有关,也就是说一个时间戳在不同的时区所代表的具体时间是不同的。

    MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期,并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

    默认情况下,如果插入时没有指定 TIMESTAMP 列的值,会将这个值设置为当前时间。

    应该尽量使用 TIMESTAMP,因为它比 DATETIME 空间效率更高。

五、切分

水平切分(类似于分区的概念,但他更广义,可以不同节点)

水平切分又称为 Sharding,它是将同一个表中的记录拆分到多个结构相同的表中。

当一个表的数据不断增多时,Sharding 是必然的选择,它可以将数据分布到集群的不同节点上,从而缓存单个数据库的压力。

水平切分

垂直切分

垂直切分是将一张表按列切分成多个表,通常是按照列的关系密集程度进行切分,也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中,例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

垂直切分

水平切分策略

  • 哈希取模:hash(key) % N;
  • 范围:可以是 ID 范围也可以是时间范围;
  • 映射表:使用单独的一个数据库来存储映射关系。

水平切分存在的问题

1. 事务问题

Sharding 存在的问题

2. 连接

可以将原来的连接分解成多个单表查询,然后在用户程序中进行连接。

3. ID 唯一性

  • 使用全局唯一 ID(GUID)
  • 为每个分片指定一个 ID 范围
  • 分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)

水平分区

  • Range(范围):这种模式允许将数据划分不同范围。
  • Hash(哈希):许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。
  • Key(键值):上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。
  • List(预定义列表):通过定义的列表的值所对应的行数据进行分割。
  • Composite(复合模式):以上模式的组合使用而已。
create table student_hash(
    id int unsigned not null auto_increment,
    birthday date,
    PRIMARY KEY(id,birthday);
) engine=myisam
partition by hash (month(birthday)) patitions 12;

六、复制

复制能解决什么问题

一、高可用和故障切换

复制能够帮避免MySql单点失败,因为数据都是相同的,所以当Master挂掉后,可以指定一台Slave充当Master继续保证服务运行,因为数据是一致性的(如果当插入Master就挂掉,可能不一致,因为同步也需要时间),当然这种配置不是简单的把一台Slave充当Master,毕竟还要考虑后续的Salve同步Master

二、负载均衡

因为读写分离也算是负载均衡的一种,所以就不单独写了,因为一般都是有多台Slave的,所以可以将读操作指定到Slave服务器上(需要代码控制),然后再用负载均衡来选择那台Slave来提供服务,同时也可以吧一些大量计算的查询指定到某台Slave,这样就不会影响Master的写入以及其他查询

三、数据备份

一般我们都会做数据备份,可能是写定时任务,一些特殊行业可能还需要手动备份,有些行业要求备份和原数据不能在同一个地方,所以主从就能很好的解决这个问题,不仅备份及时,而且还可以多地备份,保证数据的安全

四、业务模块化

可以一个业务模块读取一个Slave,再针对不同的业务场景进行数据库的索引创建和根据业务选择MySQL存储引擎,不同的slave可以根据不同需求设置不同索引和存储引擎

主从复制

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

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

主从复制

读写分离

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

读写分离能提高性能的原因在于:

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

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

读写分离

Search

    Table of Contents