上章讲解了mysql服务器的逻辑架构以及mysql的并发控制，本章来聊聊基于mysql的数据库设计以及索引相关。几个简单的原则：
(1) 更小的更好。
(2) 越简单越好。
(3) 避免null。
一般来说，数据类型越小，占用的磁盘，内存和CPU缓存也越少，，而且CPU处理周期也越少。
越简单的数据类型操作需要的CPU周期也越少，相比之下，整型比字符型操作代价更低。
可为NULL值的列在存储上会消耗更多的存储空间，也会导致索引更计算和统计更复杂，不过把null值列改为非null值列带来的性能提升较小，除非在该列上建立索引，否则没有必要去特意修改为非null。除此之外，InnoDB使用单独的bit来存储NULL值，这种情况下对稀疏数据也依然有很好的空间效率。
TIPS:
大部分alter table 将使mysql服务中断，因此可以通过一些技巧进行优化,比如主备机器,切换达到alter的操作，或者银子曹蓓，通过建立新表并重命名的操作来交换两张表的数据，修改字段的默认值不适用modify而适用alter column 操作达到不重建表和数据的效果，因为默认值是存储于.frm文件中的。
索引的高性能：
MYSQL中索引是存储引擎层实现的，不同的存储引擎索引的工作方式也是不一样的。mysql中InnoDB最主要支持两种索引类型,B-Tree索引、Hash索引。
B-Tree索引：索引如其名，使用B-Tree进行数据的存储，InnoDB实际使用的是B+Tree索引进行存储的。B+Tree的特点是所有的值是顺序存储的,且每个叶子节点到根的距离是相同的，数据都存储在叶子节点上，并且每个叶子节点指向相邻的叶子节点的地址，这种结构下，范围查找的情况下只需要获取两个节点并进行遍历即可。
鉴于此，B-Tree索引对全值匹配、最左原则、列前缀、范围值、精准某列并范围另一列、索引覆盖的情况下更为有效。
以下情况将无法使用索引: 不符合最左前缀原则，联合索引跳过其中列、如果某个列为范围查找，则右边所有列无法使用索引优化查找。
Hash索引：基于哈希表实现，只有精准匹配索引列的情况下有效。通过对每行的索引列计算出哈希码，索引只存储对应的哈希值，因此索引的结构相当紧凑，查找速度非常快，但是同样存在限制。
hash索引由于索引只存在哈希值和行指针，无法实现类似索引覆盖的操作，不过访问内存中的行速度非常快，性能影响不大。
hash索引并不是按照索引顺序存储的，因此不能用于排序。
hash索引也无法支持索引部分列匹配查找，因为hash码是通过索引所有列进行计算的，因此无法通过最左前缀类似这种部分列进行匹配查找。
同样的由于hash码的计算，因此hash索引只支持等值查询。
在很多hash冲突的情况下，存储引擎必须遍历链表中所有的行指针，逐行进行比较，索引的维护操作也同样代价高，例如删除行时，同样要遍历链表中所有的行进行删除数据。
TIPS：InnoDB存储引擎特性——自适应哈希索引，InnoDB注意到某些索引值访问特别频繁时，会在内存中基于B+Tree之上再创建一个hash索引，这个操作是完全透明的。
一个优秀的索引应该符合查询的三点：将相关的记录放在一起，数据顺序和查找中的排序顺序一致，索引列包含了查询的所有列。
索引合并策略：有时候查询会使用两个单列，单列中都有建立索引，这种情况下，Mysql会同时使用两个索引进行查找并对结果进行合并。可以通过explain sql语句进行查看，Extra列中能看到嵌套操作。出现这种情况下，意味着表的索引建立的并不是很理想，需要建立一个包含所有相关列的多列索引，因为在进行结果合并的时候，需要消耗大量的CPU和内存资源进行缓存、排序、合并。
一个优秀的多列索引，索引的列顺序至关重要，一般来说，我们把选择性最高的列放最左边。但是同样也要考虑到排序和范围查找、分组的场景。
聚簇索引，在InnoDB中，默认是通过主键进行聚集数据的，如果没有主键，则会选择一个唯一的非空索引替代，如果还没有，则会隐式定义一个主键作为聚簇索引。聚簇索引并不是索引类型，其实是数据的存储结构。表示数据行和键值紧密的存储在一起，行数据实际放在索引的叶子页。
覆盖索引，即索引覆盖了查询所需要的列，在InnoDB下，可减少二级索引再次回表，对于查询效率有极大的提升。
网友评论