MySQL中索引应用浅析

本文为作者原创内容,未经许可,禁止转载。如您发现侵权行为,请联系我们


在了解了索引实现原理之后,本文将来讨论索引在MySQL中的种类与应用。

在MySQL中,在创建表的时候要指定表的存储引擎(关于MySQL的存储引擎的介绍见文章《MySQL存储引擎浅析》),MySQL中广泛使用的存储引擎有两种:MyISAM和InnoDB,这两种存储引擎均支持B树索引。

那么在B树索引下又细分了几种索引:

1、聚集索引(又称聚簇索引)

2、非聚集索引(非聚簇索引) 

  • 唯一索引
  • 普通索引
  • 复合索引


聚集索引(聚簇索引):

那什么是聚集索引呢?

数据行的物理顺序与列值(一般就是主键的那一列)的逻辑顺序相同,且一个表中只能有一个聚集索引。


数据表在存储的时候,每一行数据都会对应一个物理地址,如果一个表中没有指定主键,那么表在存储的时候不会按照表中的某一列进行参照存储,从表的逻辑上来看是乱序的。而如果指定了主键,存储的时候会参照主键的顺序进行顺序存储,且MySQL会默认给主键建立一个聚集索引,且主键是自动递增的。总之一句话:数据表中每一行存储时会按照聚集索引去进行物理存储。


聚集索引的创建与删除:  

  • 建表的时候建立:
CREATE TABLE `student` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (id) --声明主键,即给ID添加了一个聚集索引
) ENGINE=MyISAM DEFAULT CHARSET=utf8
  • 表外创建:
alter table 表名 add primary key(列名);
tip:在创建聚集索引的时候,会检测该列上是否有重复的值,如果有则创建失败
  • 删除聚集索引

alter table 表名 drop primary key;

tip:最好是在建表的时候声明主键,否则在表中已经有数据的情况下去建立主键,会根据索引列的排序去移动数据行的存储顺序,消耗性能。    


非聚集索引:

如果了解了聚集索引,那么非聚集索引就比较容易理解,例如,我们在ID列上建立聚集索引,而表中的name列在存储形式上,就不会按照一定的逻辑顺序存储,此时在该列上建立的索引即为非聚集索引,那么符合这个特性的都可以叫做非聚集索引一个表中可以有多个非聚集索引。

唯一索引:

唯一索引,即保证某一列上的所有的值不会重复。但是,索引的功能不是优化我们的查询吗,这里为什么会用到索引,是因为DBMS为了保证每次插入的值不会重复,会根据插入的值去列中进行检索对比,而检索的过程为了提高性能就会使用到索引,所以唯一索引就是:检索,如果有重复的值拒绝插入。

笔者在文章《数据库的完整性》中列举过“完成性约束”,而用户自定义的“列值上的唯一性约束”,与唯一索引有什么区别呢?

在网上看到一篇文章(链接附在文末),大意总结如下:

在MySQL中唯一约束与唯一索引只是概念上的不同(约束是为了保证数据的完整性,索引是为了辅助查询),除了概念上的不同,两者没有其他的差别了,因为在创建唯一约束时,为自动创建唯一索引。因为唯一约束的实现逻辑为:要保证插入时没有充重复的值,会全部检索一遍数据,为了检索的效率会同步创建唯一索引。

可以这样理解:两者的概念不同,但是都是同样的实现方式。

亲自动手实验下: 
create table zds_student(
id int(10) unsigned not null auto_increment comment '表ID',
name VARCHAR(32) unique not null default '',
age int not null,
primary key(id)
)engine=myisam default charset=utf8;
标红处即为唯一性约束。
   

再执行如下SQL:    

show index from zds_student;


如我们所料,在声明完整性约束时就创建了唯一索引。   

 

唯一索引的创建与删除:    

  • 建表的时候建立:    
create table zds_student(
id int(10) unsigned not null auto_increment comment '表ID',
name VARCHAR(32) not null default '',
age int not null,
primary key(id),
unique index index_unique(name)
)engine=myisam default charset=utf8;

tip:其中index_unique是索引的名称,也可以这样写:unique key(name),此时索引名默认为列名,即name

  • 表外创建:

alter table 表名 add unique index(列名);//默认唯一索引名为列名
alter table 表名 add unique index 自定义索引名(列名);//可以自定义索引名称

  • 删除    

alter table 表名 drop index 索引名称;


相比较上面几种有特定功能的索引,普通索引则没有专门定制的功能,其存在就是为了增加检索性能。

其创建删除与唯一索引类似,只是将unique修饰符去掉即可,这里不再赘述。


复合索引:    

在具体说明复合索引之前,我们需要知道的是,构建聚集索引和非聚集索引的B+树的叶子节点上存储的数据不同:
1、在聚集索引的叶子节点上,包含了一条记录的所有数据。即一个节点携带表中的一行数据。
2、在非聚集索引的叶子节点上,如果表中包含了聚集索引,那么叶子节点上则包含了非聚集索引字段的值与聚集索引字段的值。

假设表中有三个字段,ID,username,score,其中在id上建立了聚集索引,在username上建立了非聚集索引

所以,当我们进行如下的查询的时候:
select id,username from table where username ='毛羽';
select username from table username = '毛羽';

不需要进行二次查询,因为我们可以直接在叶子节点上得到想要的值。但,如果进行如下的查询: 

select username,score from table where username='毛羽';

就会进行二次查询,因为从也在节点上得到了username,但是没有score,会根据叶子节点聚集索引的值进行二次查询。  

   tip:在数据量不大的情况下,DBMS有可能不会使用非聚集索引进行查询,而是使用聚集索引进行查询,即便是扫描整个聚集索引,效率也会比使用非聚集索引高。


如何解决非聚集索引的二次查询,可以使用复合索引(联合索引),即建立两列以上的索引,如index(clo1,clo2);

复合索引遵循“最左匹配原则”,假如有字段A,B,C三个字段,建立复合索引index(A,B,C),如果执行
select A,B,C from table  where B='TEST'

复合索引不会使用, 因为不符合“最左匹配原则”,条件中没有出现最左边的A元素。 

select A,B,C from table  where A='TEST' and C='TEST'
select A,B,C from table where A='TEST' and B='TEST' and C='TEST';
select A,B,C from table where A='TEST' and B='TEST';

复合索引会被使用 


动手实验见如下表:(zds_area表共有四个字段,area_id,parent_id,area_name,sort)   



那为什么会最左原则呢?我们看下复合索引创建的原理:    

   

注:图片来自互联网,侵权删。

如上图所示,如果复合索引有三列,会根据三列中的第一列,建立B+树,即图中的绿色部分,而叶子节点中存放所有的数据。假如复合索引的三列为(age,name,flag),进行检索的时候,会根据age的值检索出叶子节点中的内容,将所有相同age的值加载到内存中,然后再根据name的值和flag值进行筛选得出最终结果。如果,使用 (张三,1234)这样的条件进行检索,b+树就不知道下一步该查哪个节点,因为建立搜索树的时候age就是第一个比较因子,必须要先根据age来搜索才能知道下一步去哪里查询。这就是“最左匹配原则”。所以想要使用复合索引,一定把最左列包含进条件中。


补充:

创建索引的原则:
1.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式
2.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录
3.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
4.尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可


参考文献:
1.《高性能MySQL》

2.《数据结构与算法分析》 


唯一性约束与唯一索引的区别观点摘自:
非聚集索引叶子节点数据组成摘自:

感谢。