mysql 索引

一.B+树索引
1.聚集索引
innodb存储引擎表是索引组织表，即表中数据按照主键顺序存放。
而聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。每个数据页都通过一个双向链表来进行连接。
由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。
在多数情况下，优化器倾向于采用聚集索引，因为能够在B+树索引的叶子节点上直接找到数据。
由于定义了数据的逻辑顺序，聚集索引能够特别快的访问针对范围值得查询和主键的排序查询。

2.辅助索引
叶子节点不包含行记录的全部数据，叶子节点除了包含键值和对应的表的聚集索引键。
对于非聚集索引的离散读取，索引组织表上的非聚集索引会比堆表上的聚集索引慢一些。

二.B+树索引的管理
1.创建索引
alter table tbl_name add {INDEX | KEY} [index_name] [index_type] (index_col_name,...) [index_option]..
ALGORITHM = {DEFAULT|INPLACE|COPY}
LOCK = {DEFAULT|NONE|SHARED|}

ALGORITHM:
-copy 表示为创建临时表的方式建立index
-inplace 表示以FIC的方式建立index
-default表示根据参数old_alter_table来判断，默认为采用inplace的方式。

LOCK：
-none:执行索引创建或者删除操作时，对目标表不添加任何的锁，即事务仍然可以进行读写操作，不会阻塞
-share:对目标表加上一个S锁，可以进行读事务，不能进行写事务。
-exclusive:对目标表加上一个X锁，读写事务都不能进行。
-DEFAULT:对上述几种情况依次进行判断。

2.其他命令:
-删除索引:
alter table tbl_name drop primary key | drop {index|key} [index_name]
-对字段前100个字符添加索引
alter table tbl_name add key index_name(colmun_name(100));
-查看表上的index
show index from tbl_name
        Table: t3
   Non_unique: 1
     Key_name: idx-1
 Seq_in_index: 1
  Column_name: idt4
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:

三.存储引擎的cardinality统计机制
1.统计条件:
-表中1/16的数据已发生变化
-stat_modified_counter>2000000000

2.统计采样
innodb_stats_transient_sample_pages  表示每次采样页的数量
innodb_stats_persistent  表示analyze table 计算得到的cardinality值是否存放在磁盘上。默认OFF
innodb_stats_persistent_sample_pages  表示执行analyze table时采样页的数量
innodb_stats_on_metadata  表示通过命令show table status ， show index 及访问information_schema架构下的表tables和statistics时，是否需要重新计算索引的cardinality值。

四.索引的使用
1.覆盖索引
从辅助索引中就可以得到查询的结果，而不需要查询聚集索引中的记录。
优点:
-辅助索引不包含郑航记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
-针对于count(*)操作，优化器不会选择通过查询聚集索引来进行统计，而是会选择覆盖了的辅助索引。
例如联合辅助索引(userid,buy_date)
select count(*) from buy_log where buy_date >= xxx and buy_date<xxx;
 < xxx

<xxx< xxx</xxx<></xxx;

五.自适应哈希索引
自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速，如
select * from table where index_col='xxx'. 但对于范围查找则无能为力。
通过show engine innodb status; 可以看到当前自适应哈希索引的使用状况
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 0 buffer(s)
Hash table size 276671, node heap has 0 buffer(s)
Hash table size 276671, node heap has 0 buffer(s)
Hash table size 276671, node heap has 0 buffer(s)
Hash table size 276671, node heap has 0 buffer(s)
Hash table size 276671, node heap has 0 buffer(s)
Hash table size 276671, node heap has 0 buffer(s)
Hash table size 276671, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

六.全文检索
应对这种类型SQL的查询情况:select * from www where text like '%xxx%';
innodb 1.2.x版本开始，innodb存储引擎开始支持全文检索

参考书籍:
MySQL技术内幕：InnoDB存储引擎（第2版）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15412087/viewspace-2152948/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15412087/viewspace-2152948/