索引的优点:
1.索引大大减少了服务器需要扫描的数据量
2.索引可以帮助服务器避免排序和临时表
3.索引可以将随机I/O变为顺序I/O
MySQL数据库中InnoDB存储引擎,B+树索引可以分为聚簇索引(也称聚集索引,clustered index)和辅助索引(有时也称非聚簇索引或二级索引,secondary index,non-clustered index)。这两种索引内部都是B+树,聚集索引的叶子节点存放着一整行的数据。
InnoDB使用的是聚簇索引,MyISAM使用的是非聚簇索引
InnoDB中的主键索引是一种聚簇索引,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引。
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。具体细节依赖于其实现方式,但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
聚簇索引(聚集索引)
聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分,每张表只能拥有一个聚簇索引。
InnoDB通过主键聚集数据,如果没有定义主键,InnoDB会选择非空的唯一索引代替。如果没有这样的索引,InnoDB会隐式的定义一个主键来作为聚簇索引。
MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
InnoDB如下图:
辅助索引(非聚簇索引)
在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置,而是主键值。通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的Page Directory找到数据行。
InnoDB辅助索引的叶子节点并不包含行记录的全部数据,叶子节点除了包含键值外,还包含了相应行数据的聚簇索引键。
辅助索引的存在不影响数据在聚簇索引中的组织,所以一张表可以有多个辅助索引。在InnoDB中有时也称辅助索引为二级索引。
在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。
同样也是一颗B+Tree,data域保存数据记录的地址。因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
MyISM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。
聚簇索引和非聚簇索引访问区别,如下图所示,一个表存储了4行数据。其中Id作为主索引,Name作为辅助索引。
聚簇索引的优缺点
优点:
1.数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快
2.聚簇索引对于主键的排序查找和范围查找速度非常快
3.使用覆盖索引扫描的查询可以直接使用页节点中的主键值
缺点:
1.插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键
2.更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新。
3.二级索引可能比想象的更大,因为在二级索引的叶子节点包含了应用行的主键列,且访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。
4.聚簇数据最大限度的提高了I/O密集型应用的性能,但如果数据全部都放到内存中,则访问的顺序就没那么重要了,聚簇索引也就没什么优势了
5.聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏的,或者由于页分裂导致数据存储不连续的时候
索引覆盖
mysql> create table user(
-> id int(10) auto_increment,
-> name varchar(30),
-> age tinyint(4),
-> primary key (id),
-> index idx_age (age)
-> )engine=innodb charset=utf8mb4;
id 字段是聚簇索引,age 字段是普通索引(二级索引)
插入数据
insert into user(name,age) values('张三',30);
insert into user(name,age) values('李四',20);
insert into user(name,age) values('王五',40);
insert into user(name,age) values('刘八',10);
mysql> select * from user;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | 30 |
| 2 | 李四 | 20 |
| 3 | 王五 | 40 |
| 4 | 刘八 | 10 |
+----+--------+------+
只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。
例如:select id,age from user where age = 10;
如何实现覆盖索引
将被查询的字段,建立到联合索引里去。
1、如实现:select id,age from user where age = 10;
explain分析:因为age是普通索引,使用到了age索引,通过一次扫描B+树即可查询到相应的结果,这样就实现了覆盖索引
2、实现:select id,age,name from user where age = 10;
explain分析:age是普通索引,但name列不在索引树上,所以通过age索引在查询到id和age的值后,需要进行回表再查询name的值。此时的Extra列的NULL表示进行了回表查询
为了实现索引覆盖,需要建组合索引idx_age_name(age,name)
drop index idx_age on user;
create index idx_age_name on user(age
,name
);
explain分析:此时字段age和name是组合索引idx_age_name,查询的字段id、age、name的值刚刚都在索引树上,只需扫描一次组合索引B+树即可,这就是实现了索引覆盖,此时的Extra字段为Using index表示使用了索引覆盖。
在InnoDB表钟按逐渐顺序插入行
创建表
mysql> create table user(
-> id int unsigned NOT NULL auto_increment,
-> age tinyint(4),
-> name varchar(30),
-> city varchar(30),
-> adress varchar(30),
-> email varchar(30),
-> gender varchar(30),
-> password varchar(30),
-> career varchar(30),
-> hobby varchar(30),
-> primary key (id),
-> index idx_age (age)
-> )engine=innodb charset=utf8mb4;
此处使用自增的证书ID做为主键
第二张表user_uuid,除了主键改为uuid,其余的和前面的表一样
对两张表插入相同的数据
表名 行数 时间(s) 索引大小(MB)
user 1 000 000 137 342
user_uuid 1 000 000 180 544
user 3 000 000 1233 1036
user_uuid 3 000 000 4525 1770
注意到向uuid主键插入行不仅花费时间更长,而且索引占用的空间也更大。这一方面是由于主键字段更长;另一方面毫无疑问是由于页分裂和碎片导致的
主键自增的表,因为主键值是顺序的,所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时(InnoDB默认的最大填充因子时页大小的15/16,留出部分空间用于以后修改,下一条记录机会写入新的页钟。一旦数据按照这种顺序的方式加载,主键页就会近似的被顺序记录填满,这也是正是所期望的结果(然而,二级索引页可能是不一样的)
而uuid,因为新行的主键值不一定比之前插入的大,所以InnoDB无法简单的总是把新行插入到索引的最后,而是需要为新的行寻找合适的位置——通常是已有数据的中间位置——并且分配空间。这会增加很多额外的工作,并导致数据分布不够优化。因为写入时乱序的,InnoDB不得不频繁的做页分裂操作,以便为新的行分配空间。页分裂会导致移动大量的数据,一次插入最少需要修改三个页而不是一个页,且可能会产生数据碎片。写入的目标页可能已经刷到磁盘上,并从缓存中移除,或者是还没有被加载到缓存钟,InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O。
另外关于索引优化,避免索引失效可遵循如下小口诀:
*全职匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
LIKE百分写最右,覆盖索引不写 * ;
不等空值还有OR,索引影响要注意;
VAR引号不可丢, SQL优化有诀窍。
可以简单的对照下图理解
另外,一个值只有0和1的字段,到底要不要建索引?
不要!
但是如果表里面这个字段的值分布极度不均匀的情况下,而且你需要查询分布较少的记录的话,索引就非常有用了
举个例子,假设表中有一千万条记录,某个状态为0的记录总数大概会有100条,那么你想查询状态为0的记录时,有没有索引影响非常大,而查询状态为1的记录,则索引基本无用。如果两种状态的记录数相差无几的话,索引也基本无用。
所有的关于索引的文章,建议你不要为这种字段建索引的依据,都是以值分布是均匀为前提的。但如果值分布不均匀的时候,这个建议就不一定是正确的了。当我们需要查询的记录恰好是分布较少的记录的时候,值分布越是不均匀,索引就越有价值!
索引的本质就是排序,因为大家都排好了队,所以一样的数据都依次排在一块了。这个时候,就像奥运会开幕式入场的运动员们一样,中国队、美国队、德国队。。。队长都举着自己国家的小牌子,让人远远地就能看到那个瑞典队在什么地方(有索引)。这个时候,你要采访瑞典队,直接跑过去就行了。当散场后你再想去餐厅找瑞典队采访(没有索引),估计就得按个问过去:兄弟,你是不是瑞典队的?等你找到采访对象,还是算了吧,人家都吃完饭走人了(查询超时)。。。
二值的情况下,形象点说,就是你在上海人民广场找人,看你找的是中国人还是外国人。找10个中国人很简单,基本上是逮着一个算一个。但要找10个外国人,就比较费时间了。。。这个时候如果大家都排队,中国人排前面,外国人排后面,你就可以直奔队伍的末尾去喊上10个外国人了。