第四章 Schema与数据类型优化
4.1 选择优化的数据类型
几个简单的原则
更小的通常更好
eg：能用varchar(1)就不用开varchar(10)
简单就好
eg:能用整型就不要用字符串；使用mysql内建的类型来存储日期，而不是字符串；用整型来存储ip地址
尽量避免使用NULL
计划在某列上建索引，避免设计成可为NULL的列
值得一提的是InnoDB使用单独的位来存储NULL值，适合稀疏数据
4.1.1 整数类型
| TINYINT | 很小的整数 | 1个字节 |
|---|---|---|
| SMALLINT | 小的整数 | 2个宇节 |
| MEDIUMINT | 中等大小的整数 | 3个字节 |
| INT (INTEGHR) | 普通大小的整数 | 4个字节 |
| BIGINT | 大整数 | 8个字节 |
对于存储和计算来说INT(1)和INT(11)是相同的，不会限制值得合法范围，只是显示字符的个数不同
4.1.2 实数类型
MySQL中支持浮点数的类型有FLOAT、DOUBLE和DECIMAL类型，DECIMAL 类型不同于FLOAT和DOUBLE，DECIMAL 实际是以串存放的。对于精度比较高的东西，比如money，建议使用decimal类型。但decimal需要额外的空间和计算开销。
数据量比较大的时候也可以用BIGINT代替decimal，先乘以相应的倍数存入数据库，这也可以同时避免浮点数计算不准确和decimal计算开销的问题
4.1.3 字符串类型
char：定长，效率高，一般用于固定长度的表单提交数据存储 ；例如：身份证号，手机号，密码的hash值，性别等
varchar：不定长，效率偏低，需要额外的字节来记录长度，255中介线，varchar(10)需要11个字节来存储，varchar(1000)需要1002个字节来存储
而在保存较大文本时，通常会选择使用TEXT或者BLOB。二者之间的主要差别是BLOB能用来保存二进制数据，比如照片；而TEXT只能保存字符数据，比如一遍文章或日记。
4.1.4 日期和时间类型
| 类型 | 占据字节 | 表示形式 |
|---|---|---|
| datetime | 8 字节 | yyyy-mm-dd hh:mm:ss |
| timestamp | 4 字节 | yyyy-mm-dd hh:mm:ss |
| 类型 | 表示范围 |
|---|---|
| datetime | ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’ |
| timestamp | ‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999’ |
timestamp 只占 4 个字节，而且是以utc的格式储存， 它会自动检索当前时区并进行转换。
datetime以 8 个字节储存，不会进行时区的检索.
也就是说，对于timestamp来说，如果储存时的时区和检索时的时区不一样，那么拿出来的数据也不一样。对于datetime来说，存什么拿到的就是什么。
还有一个区别就是如果存进去的是NULL，timestamp会自动储存当前时间，而 datetime会储存 NULL。
如果在时间上要超过Linux时间的，或者服务器时区不一样的就建议选择 datetime。
如果是想要使用自动插入时间或者自动更新时间功能的，可以使用timestamp。
如果只是想表示年、日期、时间的还可以使用 year、 date、 time，它们分别占据 1、3、3 字节，而datetime就是它们的集合。
4.1.5 位数据类型
4.1.6 选择标识符
4.1.7 特殊类型数据
如果是IPv4类型，那么将IP转为INT UNSIGNED存储在数据库能节约内存，INET_ATON()和INET_NTOA()可以管理ip地址在字符串和数字值之间的转换。
UUID的长度为36个字符，UNHEX()将UUID转换为16字节的数据,存储到BINARY(16) 种，比人类可读形式（“文本”形式）使用的VARCHAR（36）小的多.HEX()
mysql数据表中的一个status字段 为tinyint类型，长度为1 ，comment为：0未审核 1 审核通过 -1 审核失败’,
4.3 范式与反范式
- 第一范式：要求数据库表的每一列都是不可分割的原子数据项。
- 第二范式：（2NF）属性完全依赖于主键
- 第三范式：（3NF）属性不依赖于其它非主属性，在2NF基础上消除传递依赖 BCNF 在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖。
4.4 缓存表与汇总表
有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表。
缓存表和汇总表与冗余的列有异曲同工之妙，只不过实现在单独的表中。
缓存表中存储与其它的表逻辑冗余的数据，将大表中活跃的数据单独拿出来构建成一张小表，通过减少单个表的数据量来提高查询性能，即所谓的热数据分离，尤其是在大表中的某些小部分数据被频繁访问时更能体现其带来的好处。
汇总表中存储与其它的表逻辑不同的衍生数据，通过减少聚合次数来提高查询性能，同样，这些数据也经常被访问。
但两者都必须面对数据的同步问题，对于允许最终一致性的场景来说，建立缓存表或者汇总表，无疑是一个好主意，但是如果需要实时更新，那就得多花一些心思来斟酌一二了。
优缺点
- 缓存表用来存储那些每次获取速度比较慢（其他表中活跃）的数据的表
- 汇总表保存你的是使用groupby语句聚合数据的表
二者的目的都是显著提高查询性能，但也会增加写查询的负担，需要额外的维护任务。
然而，写操作变慢并不是读操作变得更快所付出的唯一代价，还可能同时增加读操作和写操作的开发难度
实际例子
仍然以网站为例，假设需要计算之前24小时内发送的消息数。在一个很繁忙的网站不可能维护一个实时精确的计数器。作为替代方案，可以每小时生成一张汇总表。这样也许一条简单的查询就可以做到，并且比实时维护计数器要高效得多。缺点是计数器并不是100%精确。
如果必须获得过去24小时准确的消息发送数量(没有遗漏)，有另外一种选择。 以每小时汇总表为基础，把前23个完整的小时的统计表中的计数全部加起来，最后再加上开始阶段和结束阶段不完整的小时内的计数。
不严格的计数或通过小范围查询填满间隙的严格计数都比计算message表的所有行要有效得多。这是建立汇总表的最关键原因。实时计算统计值是很昂贵的操作，因为要么需要扫描表中的大部分数据，要么查询语句只能在某些特定的索引上才能有效运行，而这类特定索引-般会对 UPDATE操作有影响，所以一般不希望创建这样的索引。计算最活跃的用户或者最常见的“标签”是这种操作的典型例子。
常见应用-计数器表
- 如果应用在表中保存计数器，则在更新计数器时可能遇到并发问题。
- 计数器表在Web应用中很常见。可以用这种表缓存一个用户的朋友数、文件下载次数等。
- 创建一张独立的表存储计数器通常是个好办法，这样可使计数器表小且快。使用独立的表可以帮助避免查询缓存失效
实际示例：
假设有一个计数器表，只有一行数据，记录网站的点击次数
网站的每次点击都会导致对计数器进行更新
问题在于，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁（mutex）。这会使得这些事务只能串行执行。要获得更高的并发更新性能，也可以将计数器保存在多行中，每次随机选择一行进行更新。这样做需要对计数器表进行如下修改：
然后预先在这张表增加100行数据。现在选择一个随机的槽（slot）进行更新：
要获得统计结果，需要使用下面这样的聚合：
一个常见的需求是每隔一段时间开始一个新的计数器（例如，每天一个）。如果需要这么做，则可以简单修改一下表设计：
在这个场景中，可以不用像前面的例子那样预先生成行，而用ON DUPLICATE KEY UPDATE 代替：
如果希望减少表的行数，以避免表变得太大，可以写一个周期执行的任务，合并所有结果到0号槽，并且删除其他所有的槽
4.5 加快ALTER TABLE的速度
ALTER TABLE操作的性能对来说是个大问题。MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。
一般而言，大部分ALTER TABLE操作将导致MySQL服务中断。对常见的场景，能使用的 技巧只有两种： 1.1. 一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；
1.2. 另外一种技巧是“影子拷贝”。影子拷贝的技术是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。
只修改.frm文件
修改表的.frm文件是很快的，但MySQL有时候会在没有必要的时候也重建表。如果愿意冒一些风险，可以让MySQL做一些其他类型的修改而不用重建表。
我们下面要演示的技巧是不受官方支持的，也没有文档记录，并且也可能不能正常工作，采用这些技术需要自己承担风险。建议在执行之前 首先备份数据！
下面这些操作是有可能不需要重建表的：
移除(不是增加)一列的AUTO_INCREMENT属性。 增加、移除，或更改ENUM和SET常量。如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字串值。
基本技术是为想要的表结构创建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm文件，像下面这样： 1. 创建一张有相同结构的空表，并进行所需要的修改（例如增加ENUM常量）。 2. 执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开。 3. 交换.frm文件（重全名）。 4. 执行UNLOCK TABLES来释放第2步的读锁。
InnoDB快速载入数据
在MyISAM中有一个常用的技巧是禁用索引（只能禁用非唯一索引）、载入数据，然后重新启用索引。在现代版本的InnoDB版本中，有一个类似的技巧，这依赖于InnoDB的快速在线索引创建功能。这个技巧是，先删除所有的非唯一索引，然后增加新的列，最后重新创建删除掉的索引。Percona Server可以自动完成这些操作步骤。 也可以使用像前面据说的ALTER TABLE的骇客访求来加速这个操作，但需要多做一些工作并且承担一定的风险。这对从备份中载入数据是很有用的，例如，当已经知道所有数据都是有效的并且没有必要做唯一性检查时就可以操作。
下面是操作步骤：
用需要的表结构创建一张表，但不包括索引。 载入数据到表中以构建.MYD文件。 按照需要的结构创建另外一张空表，这次要包含索引。这会创建需要的.frm和.MYI文件。 获取读锁并刷新表。 重命名第二张表的.frm和.MYI文件，让MySQL认为是第一张表文件。 释放读锁。 使用REPAIR TABLE来重建表的索引。该操作会通过排序来构建所有索引，包括唯一索引。 这个操作步骤对大表来说会快很多。
总结
第五章 创建高性能的索引
5.1 索引基础
5.1.1索引的类型
索引位于存储引擎层，不同存储引擎中索引的工作方式也并不一样，也不是所有存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。
B-Tree 索引
B-Tree 索引的数据都是顺序存储的，所以很适合查询范围数据，以及对数据进行排序。
InnoDB 底层使用的是 B+Tree 数据结构
假设索引为key(last_name,first_name,dob) 索引列的顺序很重要
对索引有效的查询类型：
全值匹配 匹配最左前缀（组合索引） 匹配列前缀 匹配范围值 精确匹配某一列并范围匹配另外一列 只访问索引的查询
对索引无效的查询：
组合索引中，不按照索引的最左列开始查询，无法使用索引 组合索引中，不能跳过索引中的列 如果查询中包含某列的范围查询，则其右边所有列都无法使用索引优化查找
哈希索引
哈希索引（Hash Index）是基于哈希表实现的，只有精确匹配索引所有列的查询才有效。
存储引擎会为每一行数据的索引列计算出一个哈希码（Hash Code），并将哈希码存储在索引中，同时保存了指向该行的指针。 🌹 在 MySQL 中，只有 Memory 引擎显式地支持哈希索引，是该引擎的默认索引类型。
- 索引中的数据并不是按照索引值顺序存储的，无法用于排序
- 不支持部分索引匹配查询
- 只支持等值比较查询，包括：
=、IN()、< = > - 哈希冲突时，存储引擎会遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行；如果哈希冲突很多的话，一些索引维护操作的代码也会很高。
ps：也可以在B-Tree基础上创建一个伪哈希索引
R-Tree 索引
R-Tree 索引也叫做空间索引，可以用于地理数据存储，目前 MyISAM 存储引擎支持该索引。
空间索引会从所有维度来索引数据，查询时，可以有效地使用任意维度来组合查询。
必须使用 MySQL 的 GIS 相关函数来维护数据。MySQL 的 GIS 支持并不完善，所以大部分人不会使用这个特性。
全文索引
全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。
全文搜索和其他几类索引的匹配方式完全不一样，它更类似于搜索引擎。
5.2 索引的优点
索引能大幅度提升查询性能，大部分索引时指的b-tree。
1，索引事扫描全表变得扫描部分。
2，索引使随机排序变成顺寻排序
3，索引可以使服务器避免排序和临时表。
当然对大多数小数据表，全表扫描时优于索引的，索引对于大型表的查询提升是有效的，但随之带来表插入速度的变慢等代价。
对于某些业务可以使用分区技术来解决，例如日期是查询必不可少的条件。