数据库优化

1.选择优化的数据类型

• 更小的通常更好，但是如果是int（8）的话内存空间就是默认的int大小，只是显示的时候显示8位数
• 能用整数就尽量用整数，因为整数只需要比较一次，而字符串是每个字符进行比较，一般 Ip就用整型
• 尽量避免 NULL：会使得索引、索引统计 和 值比较变得复杂。InnoDB 使用单独的位（ｂｉｔ）来存储 ＮＵＬＬ ，其实可以使用0、特殊值或者空字符串作为代替
• 其中，Integer、BOOL 以及 NUMERIC 都只是别名，本质是基本类型

整数类型

• tinyint：8位
• smallint：16位
• mediumint：24位
• int：32 位
• bigint：64位
• 对整数指定长度是无意义的，其存储空间大小不变

实数类型

• float：4字节
• double：8字节
• Decimal：用于存储精确的小数允许最多 65 个数字

字符串类型

• varchar：可变字符串，需要用额外的字节来记录字符串的长度，因为行是变长，在更新时可能使行比原来更长。如果在页内没有更多的空间可以存储，则 MyISAM 会将行拆成不同的片段进行存储，InnoDB 会分裂页来使行可以放进页内。在存储和检索时会保留末尾空格
  • 什么时候使用varchar：字符串的最大长度比平均长度大得多；列的更新很少（很多的话会造成碎片问题）；
• char：定长，会删除末尾测空格，适合存储很短的字符串，或者所有值都接近同一个长度，或者经常变更的数据
• binary & varbinary：存储的是二进制字符串，按照一个字节一个字节的进行比较，而不是一个字符的比较
• Blob：存储很大的二进制字符串，没有排序规则或字符集
• Text：存储很大的字符，有字符集和排序规则

使用枚举代替字符串类型

• 枚举类型，实际上存储的是整数，其按照的是内部存储的整数进行排序的，而不是字符串进行排序的。
• 缺点：字符串列表是固定的，添加或删除字符串必须使用 alter table
• 优点：把列转换成 Enum 以后，关联变得很快；还可以让表的大小缩小 1/3

日期 和 字符类型

• datetime：精度为秒，8字节
• timestamp：4字节，依赖于时区，默认为 not null，插入时未指定值，则默认为当前时间
• 建议使用 timestamp ：因为其空间效率更高
• 若存储 UUID 的值，则应该移除“-”符号，说着使用 UNHEX（）函数转换 UUID 值为16字节的数字，并且存储在一个 Binary （16） 的列中
• 应该用无符号整数存储 ip 地址，可以使用 Inet_aton 与 inet_ntoa函数进行转换

---

范式与反范式

1.范式

• 优点：范式化的更新操作通常比反范要快，范式化的表通常更小可以更好的放在内存里，所以执行操作会更快。只有很少或者没有的重复数据。
• 缺点：通常需要关联，代价高，还可能使一些索引策略无效

2.反范式

• 优点：所有数据都在一张表中，可以很好的避免关联

加快 alter table 操作的速度

• MySQL执行修改表结构的方式：用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表—-可能会花费很长时间
• 使用的技巧：
  • 先在一台不提供服务的机器上执行 alter table 操作，然后和提供服务的主库进行切换；第二种方式：
  • 用要求的表结构创建一张和源表无关的新表，然后通过重命名和删除表操作交换两张表。
  • 第三种技巧：修改 .frm文件（存放了列的默认值）
• 修改 .frm 文件：没有官方支持，需要自己承担风险
  • 创建一张相同结构的空表，并进行所需要的修改
  • 执行 flush tables with read lock，这将会关闭所有正在使用的表，并且禁止任何表被打开
  • 交换.frm文件
  • 执行 unlock tables 来释放锁

---

2.创建高性能的索引

• 定义：是存储引擎用于快速找到记录的一种数据结构，索引是在存储引擎层而不是服务器层实现的，不同的存储引擎的索引的工作方式并不一样。NDB 用的是 B-tree 实现的（所有的值都是按照顺序存储的，并且每一个叶子页到根的距离相同），而 InnoDB 是用的 B+ Tree 实现的（B+ Tree 索引的每一个叶子节点都包含指向下一个叶子节点的指针）
  

• 索引的优点：

  • 索引可以减少服务器需要扫描的行
  • 可以帮组服务器避免排序和临时表
  • 索引可以将随机 I/O 变为顺序 I/O

• 索引中的三星原则：

  • 索引将相关记录放在一起获得一星，如Hash索引
  • 索引中的数据顺序和查找中的排序顺序一致则获得二星，
  • 如果索引中包含的列包含了查询中的需要全部的列则获得三星—聚簇索引

• 哪些情况下可以使用索引：

  • 全值查找：是指和索引中的所有列进行匹配
  • 匹配最左前缀：也就是组合索引（key1 ，key2，key3…），当匹配项为key1，key2 依次时，可以使用索引
  • 匹配列前缀：也就是匹配某一列值的开头部分
  • 精确匹配某一列并范围匹配另外一列

• 哪些情况会使索引失效：有些限制并不是 B-Tree 本身导致的，而是 MySQL优化器 和 存储引擎使用索引的方式导致的。

  • 如果不是按照索引的最左列开始查找，则无法使用索引
  • 不能跳过索引中的列，也就是不能让一个组合索引中的某个中间列使用不了而用它左右的列
  • 如果查询中有某个列的范围查找，其右边所有列都无法使用索引优化查找

哈希索引

• 定义：哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。只有精确匹配索引的所有列的查询才会有效，因为它是对所有的索引列进行计算得出一个哈希码
• 数据结构：
  
• 解决哈希冲突的方法：链地址法。在查询时要避免冲突问题，必4+须在where条件中带入哈希值和对应的列值
• 特点：只需要存储对应的哈希值，所以索引的结构十分紧凑，查找速度快
• 限制：
  • 因为索引并不是按照索引值排序存储的（是按照哈希值排序的），所以也无法用于排序
  • 不支持部分索引查找
  • 只支持等值比较查找
• InnoDB 引擎的自适应哈希：当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于 B-Tree索引之上再创建一个哈希索引，这是一个完全自动的、内部的行为，用户无法控制或配置。

聚簇索引

• 定义：聚簇索引是一种数据存储方式，InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行。其数据行实际上存放在索引上的叶子页，一个表当中只能有一个聚簇索引

• 结构：
  

• 优点：

  • 可以把相关的数据保存在一起，这样可以通过一次磁盘 IO 就能获取全部信息，而不用一条记录就进行一次IO
  • 数据访问更快，聚簇索引将索引和数据保存在同一B-Tree中
• 缺点：
  • 插入速度严重依赖于插入顺序（按照主键的顺序最快，因为插入在中间会导致页面分裂，调整）
  • 页分裂问题
  • 二级索引（非聚簇索引）比想象中大，因为会包含主键列（而不是指向行的物理地址）。并且二级索引需要两次索引查找，先查找到主键值，再通过主键值在一级索引中查找对应的值

• 如果没有主键列，可以定义一个代理键作为主键，即便和应用无关，但是通过这种自增列，可以保证数据行是按照顺序写入。这样每次插入总是会插到上一次记录的后面，当达到最大填充因子是页的大小 15/16 时，会留出部分空间用于以后修改，则插入的记录会写入新的页中。如果不按照顺序插入，则需要为新的行找到合适的位置，这样的缺点有：

  • 如果目标页已经刷到磁盘上并从缓存中删除，或者还没有被加载到缓存中，则会导致大量的随机 IO
  • 因为写入的乱序的，InnoDB 不得不频繁的作页分裂操作，以便为新的列分配空间，这会导致大量的数据移动
  • 由于频繁的页分裂，会导致页变得稀疏并被不规则填充，最终数据会有碎片。关于页分裂，建议看这篇博客：http://hedengcheng.com/?p=525

• 注意：InnoDB 在二级索引上使用共享（读）锁，在主键索引（一级）需要排他（写）锁

InnoDB 与 MyISAM 的数据分布对比

• MyISAM：
  • MyISAM按照数据的插入顺序存储在磁盘上
  • 索引和行号直接对应，其二级索引也是索引和行号直接对应
• InnoDB
  • 在 InnoDB 中，聚簇索引就是“表”，包含了所有的列
  • 其二级索引的叶子节点存储的不是行指针，而是主键值。这样减少了当出现行移动 或者 数据页分裂是二级索引的维护工作。但是会占用更多的空间

覆盖索引

• 定义：包含了所有需要查询的字段的值的索引，也就是索引包含了select、join 和 where 子句用到的所有列。Mysql 只能使用 B-Tree 来做覆盖索引
• 好处：
  • 减少数据的访问量，因为索引比数据小，更容易全部放在内存中
  • 索引是按照列值存储的，对于密集型的范围查询会比随机从磁盘读取每一行的数据的 I/O 少得多
  • InnoDB 的二级索引如果是覆盖索引，则可以避免二次查询

压缩索引

• MyISAM 使用压缩索引来减少索引的大小，从而让更多的索引可以放入内存中。但是代价可能是某些操作更慢，因为每个值的压缩前缀都依赖前面的值，所以 MyISAM 查找时无法在索引块使用二分查找而只能从头开始扫描。（索引块中第一个值是 abcde，第二个是 abcdefg，则第二个值的前缀压缩值为[5，fg]）

索引优化建议

• 尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。
• 避免多个范围条件，使用范围查找后无法使用其后的索引列。但是对于多个等值条件查询则没有这个限制（in 就是等值条件查询）

---

3.查询优化

1. 为什么查询会慢？

• 因为查询需要在不同的地方花时间，包括网络、CPU计算，生成统计信息和执行计划、锁等待等操作，尤其事项底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的 I/O 操作上消耗时间

2.优化数据访问

• 使用 limit 得到需要的记录
• 不要使用 select * ，取出全部的列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的 I/O 、内存 和 CPU 的消耗

• mysql 使用 where 条件，从好到坏
  • 在索引中使用where 条件，这是在存储引擎层完成的
  • 使用覆盖索引来返回记录，直接从索引中过滤不需要的记录并返回命中的结果，这是在服务器层完成的，无需再回表查询记录—using index
  • mysql 服务器层完成，mysql需要先从数据表中读出记录然后过滤 — using where

查询执行的步骤

1. 客户端发送一条查询给服务器
2. 服务器先检查查询缓存，如果命中了缓存，则立即返回存储子啊缓存中的数据（会检查用户权限），否则，进入下一步
3. 服务其进行 sql 解析、预处理（预处理器会检查解析树是否合法，还会进行权限验证），再由优化器生成对应的执行计划（找到最好的执行计划）
4. mysql 根据优化器生成执行计划，调用存储引擎的 API 来执行查询
5. 将结果返回给客户端
   

• 在mysql 中 ，in 和 or 是不等价的，mysql将 in 列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个 logn 的复杂度的操作
• 对于 union 查询，mysql 先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成 union 查询
• 在 mysql 中，form 子句中遇到子查询时，会先执行子查询并将结果放到一个临时表中（没有索引）