MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类，理解聚集索引和非聚集索引可通过对比汉语字典的索引。汉语字典提供了两类检索汉字的方式，第一类是拼音检索（前提是知道该汉字读音），比如拼音为cheng的汉字排在拼音chang的汉字后面，根据拼音找到对应汉字的页码（因为按拼音排序，二分查找很快就能定位），这就是我们通常所说的字典序；第二类是部首笔画检索，根据笔画找到对应汉字，查到汉字对应的页码。拼音检索就是聚集索引，因为存储的记录（数据库中是行数据、字典中是汉字的详情记录）是按照该索引排序的；笔画索引，虽然笔画相同的字在笔画索引中相邻，但是实际存储页码却不相邻，这是非聚集索引。
聚集索引
索引中键值的逻辑顺序决定了表中相应行的物理顺序。
聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。 聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行 的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此 类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节 省成本。
以上是innodb的b+tree索引结构
我们知道b+tree是从b-tree演变而来，一棵m阶的B-Tree有如下特性：
1、每个结点最多m个子结点。
2、除了根结点和叶子结点外，每个结点最少有m/2（向上取整）个子结点。
3、如果根结点不是叶子结点，那根结点至少包含两个子结点。
4、所有的叶子结点都位于同一层。
5、每个结点都包含k个元素（关键字），这里m/2≤k<m，这里m/2向下取整。
6、每个节点中的元素（关键字）从小到大排列。
7、每个元素（关键字）字左结点的值，都小于或等于该元素（关键字）。右结点的值都大于或等于该元素（关键字）。
b+tree的特点是：
1、所有的非叶子节点只存储关键字信息。
2、所有卫星数据（具体数据）都存在叶子结点中。
3、所有的叶子结点中包含了全部元素的信息。
4、所有叶子节点之间都有一个链指针。
我们发现，b+trre有以下特性：
- 对一个范围内的查询特别有效快速（通过叶子的链指针）；
- 对具体的key值查询仅仅比b-tree低效一点（因为要到叶子一级），但也可以忽略；
非聚集索引
索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。
非聚集索引的存储结构与前面是一样的，不同的是在叶子结点的数据部分存的不再是具体的数据，而数据的聚集索引的key。所以通过非聚集索引查找的过程是先找到该索引key对应的聚集索引的key，然后再拿聚集索引的key到主键索引树上查找对应的数据，这个过程称为回表！
举个例子说明下：
create table student ( `id` INT UNSIGNED AUTO_INCREMENT,
`username` VARCHAR(255),
`score` INT,
PRIMARY KEY(`id`), KEY(`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
聚集索引clustered index(id), 非聚集索引index(username)。
使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。
select id, username from t1 where username = '小明' select username from t1 where username = '小明'
但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score：
select username, score from t1 where username = '小明'
如何解决非聚集索引的二次查询问题
复合索引（覆盖索引）
建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询，如index(col1, col2)，执行下面的语句
select col1, col2 from t1 where col1 = '213';
要注意使用复合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。
何时使用聚集索引或非聚集索引：
| 动作描述 | 使用聚集索引 | 使用非聚集索引 |
| 列经常被分组排序 | 应 | 应 |
| 返回某范围内的数据 | 应 | 不应 |
| 一个或极少不同值 | 不应 | 不应 |
| 小数目的不同值 | 应 | 不应 |
| 大数目的不同值 | 不应 | 应 |
| 频繁更新的列 | 不应 | 应 |
| 外键列 | 应 | 应 |
| 主键列 | 应 | 应 |
| 频繁修改索引列 | 不应 | 应 |
目前mysql中就是将自增Id强制设定为主键索引，这是为了b+tree和分页。
mysql中每次新增数据，都是将一个页写满，然后新创建一个页继续写，这里其实是有个隐含条件的，那就是主键自增！主键自增写入时新插入的数据不会影响到原有页，插入效率高！且页的利用率高！但是如果主键是无序的或者随机的，那每次的插入可能会导致原有页频繁的分裂，影响插入效率！降低页的利用率！这也是为什么在innodb中建议设置主键自增的原因！
这棵树的非叶子结点上存的都是主键，那如果一个表没有主键会怎么样？在innodb中，如果一个表没有主键，那默认会找建了唯一索引的列，如果也没有，则会生成一个隐形的字段作为主键！
实例
#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into s1 values(i,concat('egon',i),'male',concat('egon',i,'@oldboy'));
set i=i+1;
end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
#3. 查看存储过程
show create procedure auto_insert1\G
#4. 调用存储过程
call auto_insert1();
#5、修改删除索引
1、在创建表后创建索引
create index name on s1(name); #添加普通索引
create unique username on s1(username);添加唯一索引
alter table s1 add primary key(id); #添加住建索引，也就是给id字段增加一个主键约束
create index idandname on s1(id,name); #添加普通联合索引
2.删除索引
drop index id on s1;
drop index name on s1; #删除普通索引
drop index age on s1; #删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了
alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)
先只使用主键索引。
name未使用索引
索引覆盖
select * from s1 where id=123; 该sql命中了索引，但未覆盖索引。 利用id=123到索引的数据结构中定位到该id在硬盘中的位置，或者说再数据表中的位置。 但是我们select的字段为*，除了id以外还需要其他字段，这就意味着，我们通过索引结构取到id还不够， 还需要利用该id再去找到该id所在行的其他字段值，这是需要时间的，很明显，如果我们只select id， 就减去了这份苦恼，如下 select id from s1 where id=123; 这条就是覆盖索引了，命中索引，且从索引的数据结构直接就取到了id在硬盘的地址，速度很快
设计原则
#1.最左前缀匹配原则，非常重要的原则， create index ix_name_email on s1(name,email,) - 最左前缀匹配：必须按照从左到右的顺序匹配 select * from s1 where name='egon'; #可以 select * from s1 where name='egon' and email='asdf'; #可以 select * from s1 where email='alex@oldboy.com'; #不可以 mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配， 比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引， d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。 #2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器 会帮你优化成索引可以识别的形式 #3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)， 表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、 性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同， 这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录 #4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’ 就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值， 但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。 所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
#5.其他
- 索引字段尽量使用数字型（简单的数据类型）
- 尽量不要让字段的默认值为NULL
- 使用组合索引代替多个列索引
- 使用唯一索引，考虑某列中值的分布。索引的列的基数越大，索引的效果越好。 例如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“ M” 和“F”，则对此列进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行。
-
- 如果对大的文本进行搜索，使用全文索引而不要用使用 like ‘%…%’
- like语句不要以通配符开头
对于LIKE：在以通配符%和_开头作查询时，MySQL不会使用索引。like操作一般在全文索引中会用到（InnoDB数据表不支持全文索引）。
例如下句会使用索引：
SELECT * FROM mytable WHERE username like'admin%'
而下句就不会使用：
SELECT * FROM mytable WHEREt Name like'%admin'
- 不要在列上进行运算
- 尽量不要使用NOT IN、<>、!=
- 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。当索引列有大量数据重复时,查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用
1541
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
