MySql 索引 上

索引目的

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

磁盘IO与预读

前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

索引的类型

前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？

为什么不用二叉树

如果用二叉树，给col1 建索引的话，这种场景效率很差。

为什么不是红黑树（平衡二叉树）

红黑树本质还是二叉树，如果500W的数据查找的数据在叶子节点，那要查找30次左右，没法满足索引高效查找。
如何h<5? 横向扩展

B-Tree

• 叶子节点具有相同的深度，叶子节点指针为空。
• 所有索引元素不为空。
• 节点中的数据索引从左到右递增排列。

B+Tree（B-Tree变种，多叉平衡树）

• 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引。
• 叶子节点包含所有索引字段。
• 叶子节点用指针连接，提高区间访问的性能。
  
  mysql一次I/O 到内存ram 16K （ show global status like ‘innodb_page_size’;）
  15 bigint 类型 8B,指向下个节点大小6B
  16K/(8B+6B) =1170
  假设每个叶子节点1KB，h=3
  1170117016 = 2000W+

哈希索引

MySql中只有HEAP/MEMORY引擎表才能显式支持哈希索引。

简单地说， 哈希索引就是采用一定的哈希算法 ，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

缺点

1. 因为Hash索引比较的是经过Hash计算的值，所以只能进行等式比较，不能用于范围查询。
2. 每次都要全表扫描。
3. 由于哈希值是按照顺序排列的，但是哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法利用Hash索引来加速任何排序操作。
4. 不能用部分索引键来搜索，因为组合索引在计算哈希值的时候是一起计算的。
5. 当哈希值大量重复且数据量非常大时，其检索效率并没有Btree索引高的。

索引作用

在索引列上，除了上面提到的有序查找之外，数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。
例如，有3个未索引的表t1、t2、t3，分别只包含列c1、c2、c3，每个表分别含有1000行数据组成，指为1～1000的数值，查找对应值相等行的查询如下所示。
SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3
此查询结果应该为1000行，每行包含3个相等的值。在无索引的情况下处理此查询，必须寻找3个表所有的组合，以便得出与WHERE子句相配的那些行。而可能的组合数目为1000×1000×1000（十亿），显然查询将会非常慢。
如果对每个表进行索引，就能极大地加速查询进程。利用索引的查询处理如下。

1. 从表t1中选择第一行，查看此行所包含的数据。
2. 使用表t2上的索引，直接定位t2中与t1的值匹配的行。类似，利用表t3上的索引，直接定位t3中与来自t1的值匹配的行。
3. 扫描表t1的下一行并重复前面的过程，直到遍历t1中所有的行。
   在此情形下，仍然对表t1执行了一个完全扫描，但能够在表t2和t3上进行索引查找直接取出这些表中的行，比未用索引时要快一百万倍，只扫描t1共1000行。
   利用索引，MySQL加速了WHERE子句满足条件行的搜索，而在多表连接查询时，在执行连接时加快了与其他表中的行匹配的速度。

创建索引

1. 普通索引 添加INDEX
   ALTER TABLE table_name ADD INDEX index_name ( column )
2. 主键索引 添加PRIMARY KEY
   ALTER TABLE table_name ADD PRIMARY KEY ( column )
3. 唯一索引 添加UNIQUE
   ALTER TABLE table_name ADD UNIQUE ( column )
4. 全文索引 添加FULLTEXT
   ALTER TABLE table_name ADD FULLTEXT ( column)
   如何添加多列索引
5. ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

查看索引

1. show index from tblname;
2. show keys from tblname;

选择索引的数据类型

MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：

1. 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
2. 简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
3. 尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

参考：
https://www.cnblogs.com/zhidongjian/p/10414129.html （hash索引和B+tree索引区别）
http://blog.jobbole.com/86594/
http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html
http://blog.itpub.net/9842/viewspace-312607/
http://baike.baidu.com/link?url=H2nbhcfoHrWNHGZbxBEYSDw5sTQ-fzBP-tB-6QfwR39G-3UhQN_6_qwhN6SeMKKckBAr-kso7UB1Xgnp_TFgXa
http://baike.baidu.com/view/1168762.htm
http://www.cnblogs.com/v-July-v/archive/2011/06/07/2075992.html(从B 树、B+ 树、B* 树谈到R 树)