MySQL 笔记整理（4） --深入浅出索引（上）

笔记记录自林晓斌（丁奇）老师的《mysql实战45讲》

4） --深入浅出索引（上）

　　一句话简单来说，索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

索引的常见模型

　　哈希表：哈希表是一种以key-value存储数据的结构，只要输入key，就可以找到对应的value。哈希的思路很简单， 把值放在数组里，有一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。不可避免地多个key值经过hash计算可能会出现同一个值，处理这种情况的一种方法是，拉出一个链表。查找时先通过key计算hash值找到这个链表，然后按顺序遍历链表。需要注意的是，hash存储的value并不是递增的，所以哈希索引做区间查询的速度很慢。所以，哈希表这种结构适用于只有等值查询的场景。比如memcached及其他一些nosql引擎。

　　有序数组：有序数组在等值查询和范围查询场景中的性能都很优秀。如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据时就很麻烦，在有序数组中间插入一个记录，就必须挪动后面所有的记录，成本太高。

　　二叉搜索树：二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。当然为了维持o(log(n))的查询复杂度，需要保证这棵树是平衡二叉树,为了保证是平衡二叉树所做的操作的时间复杂度也是o(log(n))。树可以有二叉，也可以有多叉。多叉树保证儿子从左到右递增。二叉树是搜索效率最高的，但是实际上大多数数据库存储并不使用二叉树。其原因是，索引不止存在于内存中，还要写到磁盘上。n叉树由于在读写性能上的优点，以及适配磁盘的访问模式，已经被广泛应用。以innodb为例，这个n的值差不多是1200.当树高是4时，已经可以存储17亿左右的数据了。

 

innodb索引模型：

　　innodb使用了b+树的索引模型。索引类型可以分为主键索引和非主键索引。

　　主键索引的叶子节点存储的是整行数据。在innodb中，主键索引也被称为聚簇索引（clustered index）

　　非主键索引的叶子节点内容是主键的值，在innodb中，非主键索引也被称为二级索引（secondary index）

　　因此，基于主键索引和普通索引的查询有很大的区别。如果使用主键索引id来查询，只需要搜索id对应的b+树。而如果使用非主键索引k来进行查询，需要先搜索k索引树，得到主键索引的值，再到主键索引树种进行搜索。这个过程称为回表。

索引维护：

　　b+树为了维护索引有序性，在插入新值的时候，需要做必要的维护。如果可以直接插入到末尾就会直接插入，否则则需要逻辑上挪动后面的数据，空出位置来。而如果要插入的位置的数据页已经满了，根据b+树的算法，需要申请一个新的数据页，然后挪动部分数据到新的页上，这个过程称为页分裂。整体空间利用率及性能都会受到相应影响。当然有分裂也有合并，暂且不谈了。基于以上索引维护内容，解释了为什么大多数建表语句都要求有自增主键。not null primary key auto_increment。这样每次操作都会是追加操作，直接插入到末尾。另外，如果使用别的有业务逻辑的字段来做主键一是很难保证有序性。二来由于非主键索引储存的是主键的值，如果用较长的字段做主键，则普通索引叶子节点就会相应较大，普通索引所占用的空间也会变大。

　　当然事无绝对，在特定场景下也可能使用业务字段做主键更合适。如：1.只有一个索引，2该索引是唯一索引。即典型的key-value场景。

上篇问题答案：

如果你是数据库负责人，你有什么方案来避免长事务呢？

　　从应用端来说：1.确认是否使用了 set autocommit=0，你应该保证这个值为1。2.确认是否有不必要的只读事务。3业务连接数据库时，根据业务本身的预估，通过 set max_execution_time命令控制每个语句的最长执行时间。

　　从数据库端来说： 1.监控 information_schema.innodb_trx表，设置长事务阈值，超过就报警或kill掉。2.percona的pt-kill工具不错（这个笔者也不知道是什么东东，感兴趣可以搜一下）3.在业务功能的测试阶段要求输出所有general_log，分析日志。4.如果使用的是mysql5.6或更新版本，将innodb_undo_tablespaces设置成2（或更大的值），这样即使真的回滚段过大，清理也更方便。（不明白）

问题：

　　对于普通索引k，重建时可以这么写：

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);

　　对于上面的重建索引的作法，说出你的理解。如果有不合时的，为什么?更好的作法是什么？