MySQL索引-上

• 索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。
  在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。
• 常见的索引模型
• 哈希表
  是一种以键-值（key-value）存储数据的结果，我们只要输入待查找的值即key，就可以找到对应的值即Value。
  哈希表的思路很简单，用哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置上。
  出现多个key值换算冲突时，处理方法时拉出一个链表。
  
• 用哈希表的好处是增加新的User时速度会很快，只需要往后追加。缺点是做区间查询很慢。
• 哈希表这种结构适用于只有等值查询的场景，比如Memcached及其他一些NoSQL引擎。
• 有序数组
• 有序数组在等值查询和范围查询场景中的性能都非常优秀。
  
• 在更新数据的时候成本太高。所以只适用于静态存储引擎。
• 搜索树
• 二叉搜索树实现
  
• 查询和更新的时间负责度都是O(log(N))
• 但是实际上大多数的数据库并不用二叉树。
• 为了让一个查询尽量少的读磁盘，就必须查询过程访问尽量少的数据快，所以会使用”N叉“树，N取决于数据块的大小。
  以InnoDB的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经是17亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。
• 跳表、LSM树等数据结构也被用于引擎设计中。数据库底层存储的核心就是基于这些数据模型的。每碰到一个新数据库，我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库的适用场景。
• InnoDB的索引模型
• 在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。
  因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在B+树中的。
• 每一个索引在InnoDB里面对应一颗B+树。
  假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k上有索引。
  这个表的建表语句是：
  mysql> create table T(
  id int primary key, 
  k int not null, 
  name varchar(16),
  index (k))engine=InnoDB;
  表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6），两棵树的示例示意图如下。
  
• 从上图中不难看出，根据叶子节点的内容，索引类型会分为主键索引和非主键索引。
• 主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index)。
• 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index)。
• 根据上面的索引结构说明，讨论：基于主键索引和普通索引的查询有什么区别？
• 如果语句是select * from T where ID=500 ,即主键查询方式，则只需要索引 ID这颗B+树；
• 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID的值为500，再到 ID索引树搜索一次。这个过程称为回表。
• 索引维护
  B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据。
  而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂
• 页分裂，数据页满的时候申请一个新的数据页
  除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。
• 讨论一个案例
  你可能在一些建表规范里面见到过类似的描述，要求建表语句里一定要有自增主键。当然事无绝对，分析一下哪些场景下应该使用自增主键，而哪些场景下不应该。
• 自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的：NOT NULL PRIMARY KEY AUTO_INCREMENT。
• 自增主键由于是递增插入的场景，所以不会出现挪动其他记录的情况，也不会出现叶子节点的分裂。
• 逻辑业务的字段做主键，则不会保证有序插入，这样写数据成本相对较高。
• 从存储空间角度看，主键的长度越小，普通索引的叶子节点也就越小，普通索引占用的空间也就越小。
  由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键
  ，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。
• 所以从性能和存储空间上来看，自增主键往往是合理的选择。
• 哪些场景适合用业务字段直接做主键呢？比如：典型的K - V场景，由于没有其他索引，所以也就不考虑其他索引的叶子节点大小的问题。
• 1、只有一个索引；
• 2、该索引必须是唯一索引；
• 思考
• 对于上面例子中的 InnoDB 表 T，如果你要重建索引 k，你的两个 SQL 语句可以这么写：
  alter table T drop index k;
  alter table T add index(k);
• 如果你要重建主键索引，也可以这么写：
  alter table T drop primary key;
  alter table T add primary key(id);
• 问题是，对于上面这两个重建索引的作法，说出你的理解。如果有不合适的，为什么?更好的方法是什么？
  重建索引 K 的做法合理，可以达到节省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都将整个表重建。所以连着执行这个这两个语句的话，第一个语句就白做了，这两个语句，可以用：alter table T engine = InnoDB。