一：B+树索引

1. 聚集索引/聚簇索引

将数据表的主键用来构造一个 B+树，并且将整张表的行记录数据存放在该 B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。

聚集索引的触发条件只能所查询条件为主键值才能发挥作用

select * form where id = 3;

如果没有定义主键索引，msyql会创建一个rowID隐含列当做主键，用这个主键来来建立聚集索引

2. 二级索引/辅助索引

在数据表中，如果某个列经常被用作查询条件，就可以对这个列建立索引

定义一个索引，就会创建一个 B+tree

二级索引的B+树不同与聚集索引的B+树，二级索引的 B+树只存放该列的数据和书签（bookmark），然后这个书签里面存放主键，用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据

3. 回表

通过二级索引获得主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录

比如: 表中的有聚集索引和二级索引 ( 例如 password )

select * form user where password=1234 这条 sql 语句就会通过二级索引获得主键，然后再通过主键索引找到完整的行记录

所以在Mysql性能优化里面 , 应该尽量避免回表
实际项目中吗，通过 Mysql 查询优化器，决定二级索引+回表还是全表扫描

4. 联合索引/复合索引

将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引

例如：index(age,orderNum) 就是将两个列组合起来构成一个索引

联合索引只会创建一个B+树

构建过程

先把各个记录按照 age 进行排序
在 age 列相同的情况下 , 采用 orderNum 排序

SQL优化 : 减少索引的数量 , 使用联合索引

5. 覆盖索引

从二级索引中可以得到的查询的记录，而不需要查询聚集索引中的记录

覆盖索引并不是一种索引，而是优化 SQL 的时候，尽量要使用到覆盖索引（索引的优化方式）
eg, select order_num form order where age = 21 这个SQL语句可以通过索引返回记录，而不需要回表
但是一旦在上面的SQL语句中加入非主键，非索引的字段，那么就还是会回表

二: 自适应 Hash 索引

B+树: 高度决定查询效率，一般来说，生产环境 Mysql 会进行优化，一般高度为3，4层

一批数据，如果经常查询，那么B+树，每一次都要查3，4次，Mysql 内部会认为这是热数据，内部会创建 Hash 索引，因为 Hash 索引只需要查询一次，就可以获得对应数据

# 通过这行命令查看innodb引擎的详细信息
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 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

哈希索引只能用来搜索等值的查询，如 SELECT* FROM table WHERE index co=xxx而对于其他查找类型,如范围查找，是不能使用哈希索引的

三: 高性能的索引创建策略

1. 索引的作用

一个索引就是一个 B+树，索引可以让查询可以快速定位和扫描到需要的数据记录上，加快查询的速度
一个 select 查询语句在执行过程中一般最多能使用一个二级索引 ( 5.7之后可以用多个索引 , 索引合并技术 )，即使在 where 条件中用了多个二级索引。

2. 索引的列的类型尽量小

TTNYINT、NEDUMNT、INT、BIGTNT 这么几种，它们占用的存储空间依次递增

能使用 INT 就不要使用 BIGINT，能使用 NEDIUMINT 就不要使用 INT

因为数据类型越小，在查询时进行的比较操作越快（ CPU 层次 ) 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I0 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议主要适用于主键，因为不仅仅聚簇索引会存储主键，二级索引和联合索引也会存储一份记录的主键值

3. 索引的选择性/离散性

索引的选择性/离散型：不重复的索引值和数据表的记录总数（ N ）的比值（范围 1/N 到1）越高则查询效率越高

SELECT count(DISTINCT name)/count(*) FORM person // 1
SELECT count(DISTINCT sex)/count(*) FORM person // 0.25
SELECT count(DISTINCT age)/count(*) FORM person // 0.625
SELECT count(DISTINCT area)/count(*) FORM person // 0.625

所以根据结果，创建索引优先选择 name，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的

4. 前缀索引

针对 blob、text、很长的 varchar 字段，mysql 不支持索引他们的全部长度，需建立前缀索引

缺点：因为前缀索引建立的索引不是完整的数据，所以前缀索引无法成为覆盖索引，另外也无法进行 order by 和 group by

通过 sql 语句查出order_note字段第几个字符开始和 count(*)的比值，也就是不重复的索引值和数据表的记录总数

-- LEFT 截取某个字段的第几个字符
SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(order_note,4))/COUNT(*)AS sel4,
COUNT(DISTINCT LEFT(order_note,5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6,
COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7,
COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8,
COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9,
COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10,
COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11,
COUNT(DISTINCT LEFT(order_note, 12))/COUNT(*) As sel12,
COUNT(DISTINCT LEFT(order_note, 13))/COUNT(*) As sel13,
COUNT(DISTINCT LEFT(order_note, 14))/COUNT(*) As sel14,
COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) As sel15,
COUNT(DISTINCT order_note)/COUNT(*) As total
FROM order_exp;

所以选择索引就应该从 select13 到 total 这之间选

ALTER TABLE order_exp ADD KEY (order_note(14));

5. 后缀索引

msyql 不支持后缀索引

可以通过在表中添加一个新列，用于保存要被建立后缀索引的字段到排值，然后建立前缀索引

场景：查询邮箱后缀

6. 只为了搜索，排序，分组的列创建的索引

只为出现在 WHERE 子句中的列，连接子句中的连接列创建索引

SELECT * FROM order_exp ORDER BY insert_time, order_status,expire_time;

可以为这三个列创建索引

7. 多列索引 ( 联合索引 )

将选择性最高的列放到索引最前列

SELECT count(DISTINCT name)/count(*) FORM person // 1
SELECT count(DISTINCT sex)/count(*) FORM person // 0.25
SELECT count(DISTINCT age)/count(*) FORM person // 0.625
SELECT count(DISTINCT area)/count(*) FORM person // 0.625

可以按照 index(name,age,area)的顺序创建索引

根据那些运行频率最高的查询来调整索引列的顺序

-- 如果这个sql语句用频率高,那么就可以用这个
select * FORM Person order by name,sex,age
index(name,sex,age

优化性能时，需要使用相同的列但顺序不同的索引来满足不同类型的查询需求

-- 根据不同的SQL语句创建不同的索引
select * FORM Person order by name,sex,age
index(name,sex,age)

select * FORM Person order by name,age,sex
index(name,age,sex)

四: 三星索引及实战

对于一个查询来说，三星索引可能是最好的索引

满足的条件如下：

索引将相关的记录放到一起则获得一星（比重27%）
如果索引中的数据顺序和查找中的排列顺序一致则获得二星（排序星）（比重27%）
如果索引中的列包含了查询中需要的全部列则获得三星（宽索引星）（比重50%）

一星：

一星的意思就是：如果一个查询相关的索引行是相邻的或者至少相距足够靠近的话，必须扫描的索引片宽度就会缩至最短，也就是说，让索引片尽量变窄，也就是我们所说的索引的扫描范围越小越好。

二星（排序星） ：

在满足一星的情况下，当查询需要排序，group by、 order by，如果查询所需的顺序与索引是一致的（索引本身是有序的），是不是就可以不用再另外排序了，一般来说排序可是影响性能的关键因素。

三星（宽索引星） ：

在满足了二星的情况下，如果索引中所包含了这个查询所需的所有列（包括 where 子句和 select 子句中所需的列，也就是覆盖索引），这样一来，查询就不再需要回表了，减少了查询的步骤和IO请求次数，性能几乎可以提升一倍。

1. 表一

CREATE TABLE customer (
    cno INT,
    lname VARCHAR (10),
    fname VARCHAR (10),
    sex INT,
    weight INT,
    city VARCHAR (10)
);
-- 对应这行查询语句什么样的索引是三星索引
select cno,fname from customer where lname=’xx’ and city =’yy’ order by fname;

这个索引是三星索引

CREATE INDEX idx_cust ON customer (city, lname, fname, cno);

第一颗星：所有等值谓词的列，是组合索引的开头的列，可以把索引片缩得很窄，符合。已经把搜索范围搜到很窄了

第二颗星：order by 的 fname 字段在组合索引中且是索引自动排序好的，符合。

第三颗星：select 中的 cno 字段、fname 字段在组合索引中存在，符合。

2. 表二

CREATE TABLE `test` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `user_name` VARCHAR (100) DEFAULT NULL,
    `sex` INT (11) DEFAULT NULL,
    `age` INT (11) DEFAULT NULL,
    `c_date` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),

) ENGINE = INNODB AUTO_INCREMENT = 12 DEFAULT CHARSET = utf8;

-- 对应这行查询语句什么样的索引是三星索引
select user_name,sex,age from test where user_name like 'test%'  and sex =1 ORDER BY age

第一种索引

CREATE INDEX idx_cust ON customer (user_name, sex, age);

第三颗星，满足

第一颗星，满足

第二颗星，不满足，user_name 采用了范围匹配，sex 是过滤列，此时 age 列无法保证有序的。

第二种索引

CREATE INDEX idx_cust ON customer (sex, age, user_name);

第一颗星，不满足，只可以匹配到 sex，sex 选择性很差，意味着是一个宽索引片 ( 同时因为 age 也会导致排序选择的碎片问题)

第二颗星，满足，等值 sex 的情况下，age 是有序的，

第三颗星，满足，select 查询的列都在索引列中，

目录CONTENT

Mysql索引