高性能 MySQL 笔记

高性能 MySQL 笔记

原文:http://www.huangxc.com/high-performance-mysql/

这几天在看《高性能 MySQL》这本书,目前看了一半左右,感觉很有收获,虽然有些内容还不太理解。这里按书的目录顺序记录下一些有用的知识点,加深记忆。

并发控制

读写锁

数据库为解决并发冲突问题而设计了「锁」,「锁」分为「读锁」和「写锁」两类。「读锁」是共享的,即互相不阻塞,因此多个用户可以在同一时刻读取同一种资源而不会导致冲突;「写锁」是排他的,即会阻塞其余的「写锁」和「读锁」,因此可以保证在同一时刻,只有唯一用户可以修改资源。

锁粒度

为提高并发性,数据库理想状态是根据实际需求进行「锁」的分配,例如如果只是修改一列数据,那最好只锁定那一列,而不是去锁定整张表,然而同时加锁又需要消耗资源,频繁的加锁、释放锁等操作会增大开销,因此需要一种锁策略来进行平衡。

不同的存储引擎通过不同的锁粒度来实现不同的锁策略,MySQL 支持多种存储引擎,每种引擎都可以实现自己的锁粒度。最常用的两种引擎大概是 MyISAM 和 InnoDB,他们分别实现了表锁和行级锁。

表锁顾名思义,会锁定整张表,当一个「写锁」被分配时,其余所有读写操作将被阻塞,这样的并发性很低,但优点是表锁的开销很小。

与之相对的是行级锁,行级锁在「写锁」操作时,只会锁定操作的那一行或几行,大大提高了并发性,但同时开销相对而言很大。

事务

当业务逻辑涉及到数据库的「打包操作」或者需要回滚时,一般会使用到事务。事务具备原子性、一致性、隔离性、持久性的特征。原子性是指一个事务就是一个不可分割的最小工作单元,整个事务操作要么全部提交成功,要么全部失败回滚;一致性是指数据库总是从一个一致性的状态转换到另一个一致性的状态;隔离性是指事务提交成功,数据修改前,对其他事务是不透明的,即外部无法知晓事务内部的具体执行过程;持久性是指一旦事务提交,其所做修改会永久保存与数据库中,即使此时系统崩溃,数据也不会丢失。MySQL 的存储引擎中,InnoDB 实现了对事务的支持,MyISAM 则不支持事务。

死锁

死锁指多个事务在同意资源上相互占用,锁定对方占用的资源,而导致恶性循环的现象。解决方法包括死锁检测和死锁超时机制。InnoDB 遇到死锁会返回一个错误,处理方法是将持有最少行级锁的事务回滚。

MVCC

MVCC 可认为是行级锁的一个变种,但它在很多情况避免了加锁,因此减小了开销。InnoDB 的 MVCC 是通过两个隐藏的列来实现,一个保存行的创建时间,一个保存过期时间,都是用版本号来代替真实时间,每开始一个新事务,版本号递增。MVCC 只在 REPEATABLE READ 和 READ COMMITTED这两种隔离级别下工作。

SELECT 时,InnoDB 根据两个条件检查记录:1.版本早于当前事务的数据行,这样确保数据要么是在事务开始前已存在,要么是事务自身插入或修改过的;2.行的删除版本要么未定义,要么大于当前事务版本,这样确保数据在事务开始前未被删除。

INSERT 时,InnoDB 为插入的每一行保存当前系统版本号作为行版本号。

DELETE 时,InnoDB 为删除的每一行保存当前系统版本号作为行删除标识。

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

存储引擎

查看表信息

使用 SHOW TABLE STATUS 命令,结果大概如下:

  1. mysql> SHOW TABLE STATUS LIKE 'test';
  2. +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
  3. | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
  4. +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
  5. | test | MyISAM | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 6291456 | 1 | 2013-09-07 00:05:48 | NULL | NULL | utf8_general_ci | NULL | | |
  6. +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
  7. 1 row in set (0.00 sec)

InnoDB

InnoDB 目前是 MySQL 的默认引擎,主要有以下一些特点:

  • 支持事务。
  • 有自动崩溃恢复功能。
  • 锁粒度为行级锁,同时也导致开销高。
  • 采用 MVCC 来支持高并发,实现了四个标准隔离级别,默认级别是 REPEATABLE READ (可重复读)。
  • 通过间隙锁策略防止幻读。
  • 聚簇索引。
  • 改变InnoDb表结构会重建整个表,包括重建所有索引。

MyISAM

MyISAM 是 MySQL 5.1 及之前版本的默认存储引擎,主要有以下一些特点:

  • 不支持事务。
  • 崩溃后无法恢复。
  • 表锁,开销小,并发小。
  • 支持全文索引。
  • 每个表存储为数据文件(.MYD)和索引文件(.MYI)。
  • 对创建后不会再进行数据修改的表,可使用 MySQL 压缩表。

选择合适的存储引擎

选择存储引擎需要考虑以下这些因素:

  • 事务
  • 备份
  • 崩溃恢复
  • 特性

转换引擎

多种方法可以转换表的存储引擎:

  • ALTER TABLE

最简单的方法是使用 ALTER TABLE 语句,如将 test 表修改为 MyISAM 可使用下面的语句:

  1. mysql> ALTER TABLE test_table ENGINE = InnoDB;
  • 导入导出

可使用工具 dump 出数据表到文件,修改文件中 CREATE TABLE 语句中的存储引擎项,同时需要修改表名。

  • 创建查询

原理是创建一个新表,再将原表数据导入到新表。数据量小时可以使用下面的语句:

  1. mysql> CREATE TABLE innodb_table LIKE myisam_table;
  2. mysql> ALTER TABLE innodb_table ENGINE = InnoDB;
  3. mysql> INSERT INTO innodb_table SELECT * FROM myisam_table;

但如果数据量大,这样执行可能会导致事务产生大量 undo,因此可以考虑使用事务分批处理(假设有主键 id,最小值为 x,最大值为 y):

  1. mysql> START TRANSACTION;
  2. mysql> INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
  3. mysql> COMMIT;

基准测试

策略

集成式:针对整个系统的整体测试。

单组件式:单独测试 MySQL。

测试指标

  • 吞吐量
  • 响应时间或延迟
  • 并发性
  • 可扩展性

工具

集成式工具:ab、http_load、JMeter。

单组件式工具:mysqlslap、MySQL Benchmark Suite、Super Smack、Database Test Suit 等。

数据类型优化

数据类型的选择应该遵守几个原则:

  • 尽量使用正确存储的最小数据类型。因为它们占用更少的磁盘、内存和 CPU 缓存。
  • 选择简单的数据类型。例如整型比字符串操作代价更低。注意应该使用 MySQL 内建的类型如 date, time 等来存储日期和时间,使用整型来存储 IP 地址。
  • 避免使用 NULL。通常情况下应该指定列为 NOT NULL。否则可能导致索引更复杂。

整型

整数类型包括整数和实数存储。证书可以使用这几种类型:TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT,他们分别使用8、16、24、32、64位存储空间,可存储值的范围从 -2N-1 到 2N-1-1 ,其中 N 表示存储空间的位数。如果使用 UNSIGNED 属性,可以使正数上限提高一倍。例如 TINYINT UNSIGNED 的存储范围是 0 到 255。整数类型的存储宽度,其实对于存储和计算并无意义,只是表示显示字符的长度,但计算还是以真是存储值为准。

DECIMAL 用于存储精确的小数。MySQL 5.0 和更高版本将数字打包保存到一个二进制字符串中(每 4 个字节存 9 个数字)。例如 DECIMAL(18,9) 小数点两边将各存储 9 个数字,一共使用 9 个字节:小数点前的数字用 4 个字节,小数点后的数字用 4 个字节,小数点本身占 1 个字节。MySQL 5.0 及更高版本中 DECIAML 类型允许最多 65 个数字。

浮点类型存储同样范围值时,比 DECIAML 使用更少的空间。FLOAT 使用 4 个字节存储,DOUBLE 使用 8 个字节。

因为需要额外空间和计算开销,应在只对小数进行精确计算时才采用 DECIMAL 类型。

字符串类型

VARCHAR 用于存储可变长字符串,比定长类型节省空间。VARCHAR 需要使用额外 1 或 2 个字节记录字符串长度(小于或等于 225 字节使用 1 个字节表示,否则使用 2 个字节)。UPDATE 操作时,会有额外开销。适用场合包括:字符串列的最大长度比平均长度大很多;列更新少;使用类似 UTF-8 这样复杂的字符集,每个字符都用不同长度字节存储。

CHAR 是定长的,MySQL 总是根据定义的字符串长度分配空间。MySQL 会删除 CHAR 类型数据所有的末尾空格。适用场合包括:很短的字符串;所有值都接近同一长度的字符串;经常 UPDATE 的数据。密码的 MD5 值适合使用 CHAR 存储。

日期和时间

日期有两种类型:DATETIME 和 TIMESTAMP。

DATETIME 可表示的范围从 1001 年到 9999 年,精度为秒。与时区无关,使用 8 字节存储。

TIMESTAMP 保存从 1970 年 1 月 1 日零点以来的秒数,即时间戳。依赖时区,使用 4 字节存储。MySQL 提供 FROM_UNIXTIME() 函数将 Unix 时间戳转换为日期,提供 UNIX_TIMESTAMP() 函数把日期转换为 Unix 时间戳。TIMESTAMP 默认为 NOT NULL。

范式

范式更新操作更快;更少的数据重复和冗余;范式化的表通常更小,可更好放于内存,操作更快;更少使用 DISTINCT 或者 GROUP 语句。

反范式化可以将所有数据存于一张表,避免关联;更有效的索引策略。

实际项目中视情况混用二者。

索引

索引是一种数据结构。索引优点在于大大减小服务器扫描的数据量,帮助服务器避免顺序和临时表,可以将随机 I/O 变为顺序 I/O。

B-Tree

B-Tree 适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

限制:

  • 如果不是按索引的最左列开始查找,则无法索引。
  • 不能跳过索引中的列。
  • 如果查询中有某个列的查找范围,则其右边所有列都无法使用索引优化查找。例如,查询 WHERE last_name = 'Simth' AND first_name LIKE 'J%' AND dob = '1976-12-23',则这个查询只能使用索引的前两列。

哈希索引

基于哈希表实现,只有精确匹配索引所有列的查询才有效。MySQL 中只有 Memory 引擎显示支持哈希索引。

假设有如下表:

  1. CREATE TABLE testhash(
  2. fname VARCHAR(50) NOT NULL,
  3. lname CARCHAR(50) NOT NULL,
  4. KEY USING HASH(fname)
  5. ) ENGINE=MEMORY;

表中有如下数据:

  1. mysql> SELECT * FROM testhash;
  2. +-------+----------+
  3. | fname | lname |
  4. +-------+----------+
  5. | Arjen | Lentz |
  6. | Baron | Schwartz |
  7. | Ray | Allen |
  8. | Peter | James |
  9. +-------+----------+
  10. 4 rows in set (0.00 sec)

假设使用某哈希函数f(),返回如下值(非真实数据):

  1. f('Arjen') = 2323
  2. f('Baron') = 7414
  3. f('Ray') = 8974
  4. f('Peter') = 2413

则哈希索引数据结构如下:

  1. Slot Value
  2. 232 指向第 1 行的指针
  3. 2413 指向第 4 行的指针
  4. 7414 指向第 2 行的指针
  5. 8974 指向第 3 行的指针

执行如下查询:

  1. mysql> SELECT lname FROM testhash WHERE fname = 'Peter';

MySQL先计算 Peter 的哈希值,并使用该值寻找对应记录指针。找到 f(‘Peter’) = 2413 后在索引中查找 2413,最后找到对应第 4 行的指针。

限制:

  • 索引只包含哈希值和行指针,不存储字段值,所以无法使用索引值来避免读行。
  • 无法排序。
  • 不支持部分索引匹配查找。
  • 只支持等值比较查询。不支持任何范围查询。
  • 出现哈希冲突时,遍历链表中所有行指针。维护操作代价高。

InnoDB 有「自适应哈希索引」的功能。

全文索引

全文索引查找文本中的关键词而不是直接比较索引中的值。适用于 MATCH AGAINST 操作而不是普通的 WHERE 条件查询。

高性能索引

独立列

索引列不可以是表达式的一部分,也不能是函数参数。例如下面的这些查询都将无法正常使用索引:

  1. mysql> SELECT actor_id FROM test_table WHERE actor_id + 1 = 5;
  2. mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

前缀索引

只索引部分字符可以节省索引空间,以提高效率。对于 BLOB 或 TEXT 或者很长的 VARCHAR 类型的列只有使用前缀索引。

需要在足够长(保证足够高的选择性)和不能过长(保证索引效率)之间寻求平衡。

例如有如下表数据:

  1. mysql> SELECT COUNT(*) AS cnt, city_table FROM city GROUP BY city
  2. -> ORDER BY cnt DESC LIMIT 10;
  3. +--------+-------------+
  4. | cnt | city |
  5. +--------+-------------+
  6. | 65 | London |
  7. | 49 | Hiroshima |
  8. | 48 | Teboksary |
  9. | 48 | Pak Kret |
  10. | 48 | Yaound |
  11. | 47 | Tel Aviv |
  12. | 47 | Shimoga |
  13. | 45 | Cabuyao |
  14. | 45 | Callao |
  15. | 45 | Bislig |
  16. +--------+-------------+

通过 3 个前缀字母索引查找:

  1. mysql> SELECT COUNT(*) cnt, LEFT(city, 3) AS pref FROM city_table
  2. -> GROUP BY pref ORDER BY cnt DESC LIMIT 10;
  3. +--------+-------+
  4. | cnt | pref |
  5. +--------+-------+
  6. | 483 | San |
  7. | 195 | Cha |
  8. | 177 | Tan |
  9. | 167 | Sou |
  10. | 163 | al- |
  11. | 163 | Sal |
  12. | 146 | Shi |
  13. | 136 | Hal |
  14. | 130 | Val |
  15. | 129 | Bat |
  16. +--------+-------+

因为每个前缀都比原城市多,因此唯一前缀比唯一城市少很多。增加前缀长度至 7 后查找结果如下:

  1. mysql> SELECT COUNT(*) cnt, LEFT(city, 7) AS pref FROM city_table
  2. -> GROUP BY pref ORDER BY cnt DESC LIMIT 10;
  3. +--------+-------------+
  4. | cnt | pref |
  5. +--------+-------------+
  6. | 70 | Santiag |
  7. | 68 | San Fel |
  8. | 65 | London |
  9. | 61 | Valle d |
  10. | 49 | Hiroshi |
  11. | 48 | Teboksa |
  12. | 48 | Pak Kre |
  13. | 48 | Yaound |
  14. | 47 | Tel Avi |
  15. | 47 | Shimoga |
  16. +--------+-------------+

计算完整列的选择性方式如下:

  1. mysql> SELECT COUNT(DISTINCT city)/COUNT(*) FROM city_table;
  2. +---------------------------------+
  3. | COUNT(DISTINCT city)/COUNT(*) |
  4. +---------------------------------+
  5. | 0.0312 |
  6. +---------------------------------+

在同一个查询中计算不同前缀长度的选择性:

  1. mysql> SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
  2. -> COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
  3. -> COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
  4. -> COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
  5. -> COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
  6. -> FROM city_table;
  7. +--------+--------+--------+--------+--------+
  8. | sel3 | sel4 | sel5 | sel6 | sel7 |
  9. +--------+--------+--------+--------+--------+
  10. | 0.0239 | 0.0293 | 0.0305 | 0.0309 | 0.0310 |
  11. +--------+--------+--------+--------+--------+

创建前缀索引:

  1. mysql> ALTER TABLE city_table ADD KEY(city(7));

前缀索引缺点在于无法做 ORDER BY 和 GROUP BY,也无法使用前缀索引做覆盖扫描。

Comments are closed.