MySQL创建高性能的索引

MySQL创建高性能的索引

在存储引擎中用于加快检索的数据结构
磁盘IO和预读：当一次IO的时候，不关把当前磁盘地址的数据，而是把相邻的数据也读取到内存缓冲区中。

索引类型

B-Tree 索引

1. 存储引擎带来的不同。

    1. 底层存储存储引擎不同使用的数据结构不同，NDB使用T-Tree，即使他的名称是BTree，InnoDB使用的是B+Tree。
   
    2. 不同引擎也会以不同的方式使用B+Tree，myisam使用前缀压缩技术使得索引更加小，Innodb原数据格式进行存储。
       myisam索引通过**数据的物理位置**引用被索引的行，而Innodb根据**主键的引用**被索引的行
   

2. 数据结构B+树
   1. 内存：磁盘块：数据项指针
   2. 性质：

    	1. 索引字段尽量小：IO次数取决于B+树的高度，当数据数量为m的时候，数据项为n的时候，h=log(m+1)n。
    	   当数据量不变，数据项越多则树的高度越小，因为磁盘块的大小固定，所以索引字段尽量小可以有更多的，这就是为什
    	   么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点数据项的数量就会减少，导致树的增高，
    	   数据项为1将会退化为线性表。
    	
    	2. 索引的最左匹配特性（从左往右）：当b+树是复合的数据结构，比如（name，age，sex）的时候，b+树是从左往右的
    	   建立索引的，这就相当于建立了三个索引 (name,age,sex)，(name，age)，（name）
   

3. 索引分类

   1. 普通索引：加速查找
   2. 唯一索引
   3. 联合索引:l 联合主键索引，联合唯一索引，联合普通索引
   4. 全文索引：搜索长篇文章

4. 聚簇索引和非聚簇索引

   1. 聚簇索引：叶子节点存储一行数据
   2. 非聚簇索引：叶子节点存储主键的值
   3. 覆盖索引：一个查询语句只需要从索引获取相关的数据，而不需要进行回表的操作，减少了I/O的效率。
   4. 命中索引和覆盖索引：select 列、列（覆盖索引）where id=“123”（命中索引）

5. 添加索引的规则

   1. 最左匹配原则
   2. 重复列少的，字段类型错误
   3. 索引列不参与计算，少用like % not in or
   4. 少用* 使用join代替连接
   5. 尽量使用短索引

6. 判断索引是否生效

   1. 使用explain 查看sql的执行情况，通过执行计划判断索引的使用情况（key）

Hash 索引

1. 存储结构
   1. 主要通过hash算法（常见的有开放定地址法，平方取中法）
2. hash索引的弊端
   1. 重复列导致hash碰撞
   2. hash不能在范围查找
   3. hash不能被排序
   4. 不能避免全表扫描

Full-Text 索引

1. 概述：用于代替效率比较低的like模糊匹配操作，通过全文索引一次匹配多个字段
2. 存储结构：分隔字段数据再进行索引，索引文件存储的分隔前的字符串集合，与分隔后的索引信息。