MySQL表设计和高性能索引
MySQL数据类型
整数类型
类型 | TINYINT | SMALLINT | MEDIUMINT | INT | BIGINT |
---|---|---|---|---|---|
位数(N) | 8 | 16 | 24 | 32 | 64 |
范围 | − 2 N − 1 到 2 N − 1 − 1 -2^{N-1}到2^{N-1}-1 −2N−1到2N−1−1 | − 2 N − 1 到 2 N − 1 − 1 -2^{N-1}到2^{N-1}-1 −2N−1到2N−1−1 | − 2 N − 1 到 2 N − 1 − 1 -2^{N-1}到2^{N-1}-1 −2N−1到2N−1−1 | − 2 N − 1 到 2 N − 1 − 1 -2^{N-1}到2^{N-1}-1 −2N−1到2N−1−1 | − 2 N − 1 到 2 N − 1 − 1 -2^{N-1}到2^{N-1}-1 −2N−1到2N−1−1 |
UNSIGNED:表示不允许负值,这大致可以使正数的上限提高一倍,例如TINYINT UNSIGNED可以存储的范围是0到255,而TINYINT的存储范围是-128到127;
指定宽度:如INT(11),但是对于存储和计算是无效的,只对交互工具有效。
实数类型
FLOAT和DOUBLE:支持使用标准的浮点运算进行近似计算;
DECIMAL:支持精确计算,但是DECIMAL只是一种存储格式,计算中DECIMAL会转换为DOUBLE类型;
BIGINT代替DECIMAL:根据小数点位数乘以相应的倍数即可。
VARCHAR 和 CHAR
VARCHAR:存储可变长字符串,使用额外的空间存储长度信息,当执行update操作时,在InnoDB中需要分裂页来使行可以放进页内,当存储列的最大长度比平均长度大很多时,可避免碎片问题;
CHAR:根据定义的字符串长度分配足够的空间,适合存储很短的字符串;
存储空间编码方式相关:
GB2312 | GBK | GB18030 | ISO-8859-1 | UTF-8 | UTF-16 | UTF-16BE | UTF-16LE | |
---|---|---|---|---|---|---|---|---|
字母(字节) | 1 | 1 | 1 | 1 | 1 | 4 | 2 | 2 |
汉字(字节) | 2 | 2 | 2 | 1 | 3 | 4 | 2 | 2 |
BLOB和TEXT
存储很大的字符串数据类型,分别采用二进制和字符方式存储,当BLOB和TEXT值太大时,InnoDB会专门的"外部"存储区域来进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后再外部存储区域存储实际的值,会导致额外的性能开销,所以尽量避免使用BLOB和TEXT类型。
日期类型
DATETIME:从1001年到9999年,精度为秒,把日期和实际封装到YYYYMMDDHHMMSS的整数中,与时区无关,使用8个字节存储;
TIMESTAMP:从1970到2038年,使用4个字节存储,默认NOT NULL。
位数据类型
BIT:包含二进制0或1值的字符串,而不是ASCII码的“0”或“1”;
SET:以一系列打包的位的集合来表示的,有效的利用了存储空间。
MySQL表结构设计
表设计的一些原则
- 使用更小的数据类型,占用更少的磁盘、内存和CPU缓存,处理时需要的CPUI周期更少;
- 简单的数据类型,整数比字符操作代价更低;
- 尽量避免NULL;
- 确保关联表中都使用同样的类型,类型之间需要精确匹配,包括像UNSIGNED这样的属性;
- 存储UUID值,使用UNHEX()函数转换UUID值为16字节的数字;
- 存储IP,使用INET_ATON()和INET_NTOA()函数转换;
- 避免太多的列,从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。
范式设计
优点和缺点
优点
- 范式化的更新操作通常比反范式化更快
- 当数据更好的范式化时,就只有很少或者没有重复数据,所以修改的数据更少;
- 范式化的表通常更小,可以更好的放在内存,所以执行操作更快;
- 很少的多余数据,更少的需要DISTINCT或GROUP BY。
缺点
- 通常需要关联查询。
混用范式化和反范式化
冗余不会频繁更新的字段,利于查询,不利于更新。
缓存表和汇总表
在使用缓存表和汇总表时,必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序,但是定期重建并不是节省资源
计数器表
CREATE TABLE hit_counter (
slot TINYINT UNSIGNED NOT NULL PRIMARY KEY,
cnt INT UNSIGNED NOT NULL
) ENGINE = INNODB;
UPDATE hit_counter SET cnt = cnt + 1 WHERE solt = RAND() * 100;
增加随机的槽进行更新,减少表的行数
高性能索引
索引的优点
- 减少服务器需要扫描的数据量
- 避免排序和临时表
- 随机IO变为顺序IO
B-Tree索引
值都是按照顺序存储的,并且每一个叶子页到根的距离相同,存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针,存储引擎根据这些指针进入下层子节点。
可以使用B-Tree索引的查询类型
- 全值匹配
- 匹配最左前缀
- 匹配列前缀
- 匹配范围值
- 精确匹配某一行并范围匹配另一列
- 只访问索引的查询
- 避免多个范围查询
B-Tree索引的限制
- 如果不是按照索引的最左列开始查找,则无法使用索引
- 不能跳过索引中的列
- 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引
哈希索引
哈希索引的限制
- 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行
- 哈希索引不是顺序存储,所以无法用于排序
- 哈希索引不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的
- 哈希索引只支持等值查询
- 哈希冲突时必须遍历链表中所有的行指针
- 哈希冲突越多代价越大
- 避免使用SHA1()和MD5()作为哈希函数,这两个函数计算的哈希值字符串太长,浪费空间,比较更慢
InnoDB注意到某些索引值被使用得非常频繁时,他会在内存中基于B-Tree索引之上在创建一个哈希索引,这样就让B-Tree索引也具有哈希索引的一些优点
实例:使用B-Tree来存储url
SELECT id FROM url WHERE url = 'www.baidu.com';
优化:新增一个url_crc列,使用CRC32做哈希
SELECT id FROM url WHERE url = 'www.baidu.com' AND url_crc = CRC32('www.baidu.com');
高性能的索引策略
- 独立的列,索引列不能是表达式的一部分,也不能是函数的参数;
- 前缀索引和索引选择性,索引选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查询时过来掉更多的行;
- 多列索引,MySQL引入了索引合并的策略,会消耗CPU和内存资源在算法的缓存、排序和合并操作上,优化器不会把这些消耗计算到查询成本中,所以需要使用合理的联合索引;
- 选择合适的索引列顺序,选择性最高的列放在前面通常是最好的;
- 使用覆盖索引,如果一个索引包含所有需要查询的字段的值,被称为覆盖索引,使用覆盖索引可避免二次回表,减少一次IO
聚簇索引和非聚簇索引
当表有聚簇索引时,它的数据行实际上存放在索引的叶子页中,InnoDB通过主键聚集数据,如果没有定义主键,InnoDB会选择一个唯一的非空索引代替。
聚簇索引和非聚簇索引分布:
优点:
- 可以把相关数据保存在一起
- 数据访问更快
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
缺点:
- 插入速度依赖插入顺序
- 更新列的代价很高
- 聚簇索引插入行可能导致页分裂
- 页分裂导致数据连续全表扫描代价很大
- 二级索引包含了行的主键列,导致二级索引更大,二级索引需要两次查找
InnoDB中使用聚簇索引插入行
因为主键的值是顺序的,所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时,下一条记录就会写入到新的页中,InnoDB默认的最大填充因子时页大小的15/16