mysql double 索引_Mysql索引

HASH

在MySQL中，只有HEAP/MEMORY引擎表才能显式支持哈希索引

简单地说，哈希索引就是采用一定的哈希算法，

把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，

只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

从上面的图来看，B+树索引和哈希索引的明显区别是：

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；

如果是范围查询检索，这时候哈希索引就毫无用武之地了，

因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；

哈希索引也没办法利用索引完成排序，以及

like ‘xxx%’ 这样的部分模糊查询(这种部分模糊查询，其实本质上也是范围查询)；

哈希索引也不支持多列联合索引的最左匹配规则；

B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，

在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

B+索引

叶子节点用联表连接

说明这个原理，首先要知道局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

有了局部性原理，操作系统磁盘的访问方式就改变了，访问数据的时候不是一个字节一个字节地读出来，而是一次读出一个页(一般为4k或8k)的数据，这些数据是物理相邻的，这就是由局部性原理引申出的磁盘预读。

B-Tree作为索引(mysql在实现上实际是B+-Tree)能够提高查询效率主要有两点：

1、 B-Tree是多路搜索树，查询B-Tree中任意一个节点，最多需要访问h个节点(h为树高)，无疑比遍历全表找到所需数据效率要高。

2、我们都知道IO操作是非常耗时的，而因为数据的庞大，数据库中数据都是以文件的形式存储的(如果是innoDB的存储引擎则要读数据文件，如果是myISAM只读索引文件)，如果每访问一个节点中的一个数据就进行一次IO，损耗将非常大，而mysql巧妙地将一个节点的大小设置了一页的大小(利用磁盘预读的特点)，每次磁盘读取一页的数据就能在一次 IO 的情况下将整个节点的数据读出来。

此外，mysql建立的B-Tree高度一般不超过3，因为100阶的高度为3的B-Tree，可以存100*100*100=10^6数据，所以基本上3次 IO 就可以完成索引。

为什么不能采用红黑树作为索引结构呢？

因为红黑树是二叉树，存储大量数据的时候树的高度会非常大，会造成多次 IO 读取，性能很差，因此红黑树不适合作为外存索引。

多列联合索引的最左匹配

CREATE TABLE test (

id INT NOT NULL,

last_name CHAR(30) NOT NULL,

first_name CHAR(30) NOT NULL,

PRIMARY KEY (id),

INDEX name (last_name,first_name)

);

一个多列索引可以认为是包含通过合并(concatenate)索引列值创建的值的一个排序数组。 当查询语句的条件中包含last_name 和 first_name时，例如：

SELECT * FROM test WHERE last_name='Kun' AND first_name='Li';

sql会先过滤出last_name符合条件的记录，在其基础上在过滤first_name符合条件的记录。那如果我们分别在last_name和first_name上创建两个列索引，mysql的处理方式就不一样了，它会选择一个最严格的索引来进行检索，可以理解为检索能力最强的那个索引来检索，另外一个利用不上了，这样效果就不如多列索引了。

但是多列索引的利用也是需要条件的，以下形式的查询语句能够利用上多列索引：

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test WHERE last_name='Widenius' AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test WHERE last_name='Widenius' AND first_name >='M' AND first_name < 'N';

以下形式的查询语句利用不上多列索引：

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael';

多列建索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。

自适应哈希

innodb引擎有一个特殊的功能叫做自适应哈希索引，当innodb注意到某些索引值被使用的非常频繁时，它会在内存中基于btree索引之上再创建一个哈希索引，这样就让btree索引也具有哈希索引的一些优点，比如：快速的哈希查找，这是一个全自动的，内部的行为，用户无法控制或者配置，不过如果有必要，可以选择关闭这个功能(innodb_adaptive_hash_index=OFF，默认为ON)。

1、原理过程

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升，则：

1、自适应hash索引功能被打开

mysql> show variables like '%ap%hash_index';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| innodb_adaptive_hash_index | ON |

+----------------------------+-------+

1 row in set (0.01 sec)

2、经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。

myisam与innodb的5点不同

1>.InnoDB支持事物，而MyISAM不支持事物

2>.InnoDB支持行级锁，而MyISAM支持表级锁

3>.InnoDB支持MVCC, 而MyISAM不支持

4>.InnoDB支持外键，而MyISAM不支持

5>.InnoDB不支持全文索引，而MyISAM支持。

2.innodb引擎的4大特性

插入缓冲(insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)

3.selectcount(*)哪个更快,为什么？

myisam更快，因为myisam内部维护了一个计数器，可以直接调取。