高性能MySQL

1 MySQL架构

MySQL服务器逻辑架构图如下所示：

1.1 并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制问题。此处讨论MySQL在两个层面的并发控制：服务器层和存储引擎

1.1.1 读写锁

在处理并发读或并发写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题：共享锁 和 排他锁。

写锁具有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面。

1.1.2 锁粒度

锁策略就是在锁的开销和数据安全性之间寻求平衡。MySQL不同的存储引擎可以实现自己的锁策略和锁粒度。

表锁
表锁是MySQL最基本的锁策略，并且开销最小。锁定整张表

用户对表进行写操作前，需要获取表锁，这回阻塞其他用户对该表的所有读写操作。
用户对表进行读操作前，需要获取读锁，读锁之间不会互相阻塞

尽管存储引擎可以管理自己的锁，但MySQL本身还是会使用各种有效的表锁实现不同的目的。例如，服务器会为诸如ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制。

行级锁
可以最大程度得支持并发处理（意味着同时也会带来了更大的所开销）。InnoDB实现了行级锁。行级锁只在存储引擎层实现，而MySQL服务器层没有实现。

1.2 事务

事务的基本特性ACID：原子性(atomicity)、一致性(consistency)、隔离性(isolation)、持久性(durability)

1.2.1 隔离级别

MySQL可以通过执行如下命令设置隔离级：

mysql >SET [SESSION] TRANSACTION ISOLATION LEVEL [READ COMMITED]

MySQL的隔离级如下：

READ UNCOMMITED，读未提交
事务中的修改，即使没有提交，其他事务也都是可见的。即脏读。
READ COMMITED，读已提交
一个事务只能"看见"已经提交的事务所做的修改。因为同一个事务内两次执行同样的查询，可能会得到不一样的结果。因此也叫作不可重复读。
REPEATABLE READ，可重复读
保证了在同一个事务内，多次读取同样记录的结果是一致的。InnoDB利用MVCC机制解决幻读问题。所谓幻读，当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。
SERIALIZABLE，可串行化
强制事务串行执行。

1.2.2 事务日志

事务日志可以提高事务的效率。使用事务日志，存储引擎在修改的数据时只需要修改其内存拷贝，再把该修改行为记录持久化到硬盘的事务日志中，而非每次都将修改的数据持久化到磁盘。事务日志采用追加方式，因此写日志的操作是顺序IO，而非随机IO，因此使用事务日志的方式相对来说效率更高。

事务日志持久化以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。

1.2.3 MySQL的MVCC

MVCC没有统一的实现标准，因此不同数据库的实现机制不同。MySQL中MVCC的实现：
通过保存数据在某个时间点的快照来实现的，即不管事务需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

InnoDB通过在每行记录后面保持2个隐藏列来实现：行创建时间、行过期时间。存储的是版本号而非实际的时间值。每开启一个新事务，系统版本号都会自增。事务开始时刻的系统版本号作为事务版本号，用来和查询到的每行记录的版本进行比较。REPEATABLE READ隔离级下，MVCC具体操作：

SELECT

a. InnoDB只查找版本早于当前事务版本的数据行
b. 行的删除版本要么未定义，要么大于当前事务的版本。这可以确保事务读取的行，在事务开始之前未被删除

INSERT
InnoDB为新插入的每一行保存当前系统版本号作为行版本号
DELETE
InnoDB为删除的每一行保存当前系统版本号作为行删除标识
UPDATE
InnoDB插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

1.2.4 MySQL中的事务

MySQL提供了2种事务性存储引擎：InnoDB 和 NDB Cluster。

隐式和现实锁定

隐式锁定，InnoDB 会根据事务隔离级在需要的时候自动加锁

在事务执行过程中，随时可以锁定，锁只有在执行 COMMIT 或 ROLLABACK 才会被释放，并且所有的锁都在同一时刻被释放。

现实锁定，InnoDB 也支持通过特定语句进行显示锁定。这些语句不是SQL规范。

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

2 数据类型优化

选择数据类型一般遵循如下原则：
更小通常更好
需要更少的空间（磁盘、CPU缓存、内存），以及更少的cpu周期。

简单就好
越简单的数据类型，需要的cpu周期越少。如整数代表IP，MySQL内建类型保存日期和时间而非字符串，因为字符集和排序规则让其更复杂。

尽量避免使用NULL
NULL 列会使索引、索引统计和值更加复杂；NULL 需要更多的存储空间，还需要MySQL内部进行特殊处理；当可空列作为索引时，每条记录都需要一个额外的字节；在MyISAM中甚至还可能导致固定大小索引，变为可变大小的索引。
NULL 列改为not null带来的性能提升很小！因此在调优时没有必要首先在现有的schema中查找并修改掉这种情况（除非要在此列上做索引，或null会导致问题）。

2.1 整数

知识点1
TINYINT（1字节）、SMALLINT（2字节）、MEDIUMINT（3字节）、INT（4字节）、BIGINT（8字节）。
知识点2
有符号和无符号类型使用相同的存储空间，并具有相同的性能。
知识点3
MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用来说这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（如MySQL命令行工具）用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)没有区别。

2.2 实数

MySQL支持精确类型，也支持不精确类型：

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。
DECIMAL类型用于存储精确的小数，因为CPU不支持对DECIMAL的直接计算，因此MySQL5.0及以上版本，MySQL服务器自身实现了DECIMAL的高精确计算

DECIMAL列可以指定小数点前后所允许的最大位数，这会影响列的控件消耗。MySQL5.0即以上版本，DECIMAL类型允许最多65个数字。而早期DECIMAL最多允许254个数字，并且保存为未压缩字符串（每个数字一个字节）。然而这些早期版本实际不能在计算中使用这么大的数字，因为DECIMAL只是一种存储格式，在计算中DECIMAL会转换为DOUBLE。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。在数据量较大的情况下，可以考虑使用BIGINT代替DECIMAL，最后乘或除相关倍数。

2.3 字符串

MySQL4.1开始，每个字符串列都可以定义自己的字符集和排序规则。VARCHAR和CHAR是最主要的字符串类型，很难精确的解释这些值如何存储在磁盘或内存中，因为这跟存储引擎的具体实现相关。下面描述以InnoDB或MyISAM为准。

2.3.1 CHAR和VARCHAR

VARCHAR

VARCHAR需要1~2个额外的字节记录字符串的长度：如果列的最大长度小于等于255个字节，则只使用1个字节，否则使用2个字节。
VARCHAR类型的行是变长的，在UPDATE导致行增长时，需要一些额外的工作：

若一个行占用的控件增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式不同：
MyISAM：会将行拆成不同的片段存储
InnoDB：则需要分裂页来使行可以放进页内

MySQL5.0及以上，在存储或检索时，会保留末尾空格。但4.1及之前的版本，会剔除末尾空格

CHAR
MySQL当存储CHAR时，会删除所有的末尾空行。CHAR适合存储短字符串，或所有值都接近同一个长度的字符串。

mysql> CREATE TABLE char_test(char_col CHAR(10));
mysql> INSERT INTO char_test
    -> (char_col) 
    -> VALUES
    -> ('str1'), (' str2'), ('str3 ');
mysql> SELECT CONCAT("'", char_col, "'") FROM char_test;
结果发现str3的末尾空格被截断了。如下：
'str1'
' str2'
'str3'

2.3.2 BLOB和TEXT

BLOB（二进制）、TEXT（字符串）。与其他类型相比，MySQL把每个BLOB和TEXT值当做一个独立的对象处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部区域存储实际的值。

BLOB和TEXT仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集合排序规则。

MySQL对BLOB和TEXT列进行排序和其他类型是不同的：它只对每个列的最前max_sort_length字节而不是整个字符串做排序。若只需要排序前面一小部分字符，则可以减小max_sort_length的配置，或使用ORDER BY SUBSTRING(column, length)

2.4 日志类型

MySQL提供了FROM_UNIXTIME()函数将Unix时间戳转换为日期；提供了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。MySQL存储的 最小时间粒度为秒，但可以使用微妙及粒度进行临时运算。

DATETIME：与时区无关，8个字节
TIMESTAMP：1970年 ~ 2038年。通常情况下尽量使用TIMESTAMP，因为其空间效率更高。

对于存储微秒级时间粒度：使用BIGINT、或使用DOUBLE存储秒之后的小数部分。

3 索引

MySQL的索引是在存储引擎层面实现的，所以没有统一标准。

3.1 索引类型

BTree索引

存储引擎在底层可能以不同的方式使用BTree索引，性能也各有优劣。BTREE通常意味着所有的值都是按照顺序存储，每个叶子节点到根节点距离相同。如下图为InnoDB的BTEE索引的抽象表示：

注意：叶子节点的指针指向被索引的数据。BTree 类型的索引适合的查询类型：

全键值
键值范围
键前缀：最左前缀、某列值的开头部分
精确匹配最左列，并范围匹配另外一列
只查询索引信息，即 覆盖索引

BTree索引的限制

若非索引的最左前缀，无法使用索引
不能跳过索引列中的某列。
若查询中有某个列的范围查询，其右边的所有列均无法使用索引优化查找。

哈希索引

哈希索引只有精确匹配索引的所有列才能使用到。每行数据，存储引擎会对所有的索引列计算一个哈希码。哈希索引将哈希码存储在索引中，同时在哈希表中存储指向数据行的指针。若存在哈希碰撞，则使用链表存储。

MySQL只有Memory引擎显示支持哈希索引。InnoDB有个特殊功能 自适应哈希索引。当InnoDB注意到某些索引值被使用的非常频繁时，会在内存中基于BTree索引上再创建一个哈希索引，如此可以让BTree索引具有哈希索引的优点。

创建自定义哈希索引: 若存储引擎不支持哈希索引，可以模拟InnoDB创建哈希索引：在BTree基础上创建伪哈希索引，不是真正的哈希索引，因为还是使用BTree进行查找，但它使用哈希值，而非键本身进行索引查找。如下：

应用需要存储URL，并根据URL查找，若利用BTree存储URL，存储内容极大（URL较长）。因此可以选择删除URL的索引，利用哈希算法，新增一个url_hash列，在该列上建立BTree索引。如此性能更高。
SELECT * FROM url_tb WHERE url_hash='xxx' AND url='http://xxx'

空间数据索引（RTree）
MyISAM表支持空间索引，可用作地理数据存储。省略。

全文索引
略

3.2 高性能索引

若不恰当使用索引，则MySQL无法使用已有的索引进行查找

3.2.1 独立的列

索引列 不能是表达式的一部分，也不能是函数的参数。如下：

mysql> SELECT * FROM tb1 WHERE col_index + 1 = 5;
mysql> SELECT * FROM tb1 WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(col_index) <= 10;

3.2.2 索引选择性

若要索引很长的字符列，会让索引变大且慢：

策略1：模拟哈希索引。
策略2：可一直只索引开始的部分字符串：会增加哈希碰撞的概率，降低索引的选择性。

此处介绍策略2，策略2中诀窍在于选择足够长的前缀以保证较高的索引选择性，同时又不能太长。可以通过如下SQL进行选择：

mysql> CREATE TABLE city_demo(city VVARCHAR(50) NOT NULL);
mysql> SELECT COUNT(*) AS cnt, city FROM city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;
-----------------
cnt | city
65  | London
49  | Hiroshima
   ...
------------------
注意：上面每个值都出现了45~65次，现在查找到最频繁出现的城市前缀，先从3个前缀字幕开始。直至找到前缀出现次数接近完整列的情况：
mysql> SELECT COUNT(*) AS cnt, LEFT(city,3) as pref FROM city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;
-----------------
cnt | pref
483 | San
195 | Cha
   ...
------------------

mysql> SELECT COUNT(*) AS cnt, LEFT(city,7) as pref FROM city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;
-----------------
cnt | pref
70  | Santiag
68  | San Fel
   ...
------------------

或者使用如下办法：计算完整列的选择性，并使用前缀的选择性接近完整列的选择性：

mysql> SELECT COUNT(DISTINCT city)/COUNT(*) AS cnx FROM city_demo;
---------
cnx
0.0312
---------

mysql> SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
    ->      COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
    ->      COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
    ->      COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
    ->      COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
    -> FROM city_demo;
------------------------------------------------
sel3   |  sel4   |  sel5   |  sel6   |  sel7
0.0239 |  0.0293 |  0.0305 |  0.0309 |  0.0310
------------------------------------------------

3.2.3 聚簇索引

聚簇索引是一种数据存储的方式，聚簇：表示数据行和相邻的键值紧凑的存储在一起。
InnoDB的聚簇索引实际上在同一个结构中保存了BTree索引和数据行。当表有聚簇索引时，它的 数据行 实际存放在叶子页上。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。InnoDB通过主键聚集数据，若没有定义主键，InnoDB会选择唯一的非空索引代替。若仍然没有，则InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇索引的优点：

将数据保存在一起，减少磁盘读取少数数据页。若没有聚簇索引，每条数据行都可能导致一次磁盘IO
使用覆盖索引扫描的查询，可以直接使用页节点的主键值

聚簇索引的缺点：

若数据全部放在内存中，则访问的顺序就没那么重要，聚簇索引也就没有优势
插入记录的速度严重依赖于插入的顺序。按主键索引的顺序插入是速度最快的方式，插入完成后，建议使用OPTIMIZE TABLE 将数据在磁盘的存储进行优化
更新聚簇索引代价高昂，因为会强制InnoDB将每个被更新的行移动到新的位置
插入新行，若这一行数据插入到已满的页中时，存储引擎会将该页分裂成2个页来容纳该行，页分裂的操作导致表占用更多的磁盘空间
二级索引可能比想象中的更大，因为二级索引的叶子节点包含了引用行的主键列
二级索引访问需要2次索引查找：二级索引保存的是行的主键值，而非指向行的物理位置的指针。

所以，切忌使用UUID作为聚簇索引的主键，它会使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集的特性。

如下图所示：

3.2.4 覆盖索引

如果索引的叶子节点已经包含了要查询的数据，就没有必要回表查询。即：若一个索引覆盖所有需要查询的字段的值，就称为 覆盖索引。优势：

索引条目通常较小，因此减少MySQL的数据访问量
MyISAM存储引擎在内存中只缓存索引，数据则依赖操作系统缓存，因此要访问数据会进行一次系统调用，可能导致严重性能问题
InnoDB是聚簇索引，更加适合覆盖索引的查询（二级索引存储主键的值）。

注意：覆盖索引必须存储索引列的值，而哈希索引、空间索引、全文索引都不存在索引列的值，因此MySQL只能使用BTree索引做覆盖索引。

附录1：Explain命令

EXPLAIN命令是查看查询优化器如何决定执行查询的主要方法，但这个功能由局限性，并非总会说出真相，但他的输出是可以获取的最好的信息，值得花时间了解，因为可以学习到查询是如何执行的。

mysql> EXPLAIN SELECT 1\G
************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used

Type列

ALL，全表扫描
通常意味着MySQL必须扫描整张表（也有例外，例如在查询中使用LIMIT，或者在Extra列中显示“Using distnct/not exists”）。
index
和全表扫描一样，只是MySQL扫描表时按索引次序进行而不是行。主要优点是避免了排序；最大缺点是要承担按索引次序读取整个表的开销，这通常意味着若是按随机次序访问行，开销将会非常大。
若在Extra列中看到“Using index”，说明MySQL正在使用覆盖索引，它只扫描索引的数据，而不是按照索引次序的每一行。它比按索引次序全表扫描开销要少的多。
range
即一个有限制的索引扫描。显而易见的范围扫描是带有BETWEEN或WHERE字句里带有>的查询。
注意，当MySQL使用索引去查找一系列值时，如IN()或OR列表，也会显示为范围扫描，但这两者其实是相当不同的访问类型，在性能上有显著差异。
ref
一种索引访问，它返回所有匹配某个值的行。然而他可能会找到多个符合条件的行，因此它是查找和扫描的混合体。此类索引访问只有当使用非唯一性索引或唯一性前缀时才会发生。
注意，ref_or_null，是ref上的一个变体，它意味着MySQL必须在初次查找的结果里进行第二次查找以找出NULL条件
eq_ref
使用这种索引查找，MySQL直到最多只返回一条符合条件的记录。主键、唯一性索引查找时看到
const、system
当MySQL能对查询的某部分进行优化将讲其转换成一个常量时，就会使用这些访问类型。如，你通过将某一行的主键放入WHERE子句里的方式选择此行的主键，MySQL就能把这个查询转换为一个常量
NULL
这种访问方式意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着在访问表或索引。例如，从一个索引列里选取最小值可以通过单独查

Extra列
这一列包含的是不适合在其他列显示的额外信息。常见的最重要的值如下：

Using index
此值表示MySQL将使用覆盖索引，以避免访问表
Using where
这意味MySQL服务器将在存储引擎检索行后再进行过滤。许多WHERE条件里涉及索引列中的列，当它读取索引时，就能被存储引擎检验，因此不是所有带WHERE子句的查询都会显示Using where。
Using temporary
这意味着MySQL在对查询结果排序时会使用一个临时表
Using filesort
这意味着MySQL会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。MySQL有2种文件排序算法，两种方式都可以在内存或磁盘上完成。EXPALIN不会告诉你MySQL将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
Range checked for each record
这意味着没有好用的索引，新的索引将在联接的每一行上重新估算。

key列
显示了MySQL决定采用哪个索引来优化对该表的访问。若该索引没有出现在possible_keys列中，那么MySQL选用它是出于另外的原因。例如，他可能选择了一个覆盖索引，哪怕没有WHERE子句。
key显示的是优化采用哪一个索引可以最小化查询成本。
key_len列
该列显示了MySQL在索引里使用的字节数。MySQL5.5即之前版本，只能使用索引的最左前缀。
ref列
显示了之前的表在key列记录的索引中查找值所用的列或常量。

key列只是参考，key_len列才能具体说明使用了哪个索引。

rows列
MySQL为找到所需的行而要读取的行数。即它不是MySQL认为它最终要从表里读取的行数，而是MySQL为了找到符合查询的每一点上标准的那些行而必须读取的行的平均数
此估算值不是很精确。在MySQL5.0即更早版本里，它也反映不出LIMIT子句。举例来说，下面这个查询不会真的检查1022行。

posted @ 2019-03-12 22:45 wolf_w 阅读(563) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

wolf_w