MySQL创建索引详解(3种创建方式及5大原则)

MySQL索引的介绍

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询，更新数据库中表的数据。

建立索引就像创建目录一样，索引(Index)是帮助mysql高效获取数据的数据结构，索引可以提高查询效率。

 

MySQL创建索引语法

MySQL创建索引主要分为以下3种：

1.使用CREATE INDEX创建

create index index_name on table_name(column_name)

 

2.使用ALTER语句创建

修改表结构添加索引，如下所示：

alter table table_name add index index_name(column_name)

 

3.建表的时候创建索引

create table table_name(
  id int not null,
  username varchar(64) not null,
  index [index_name] (username)  
);

 

MySQL创建索引原则

1.最左前缀匹配原则

非常重要的原则，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(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少。

 

4.索引列不能参与计算，保持列“干净”

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引。

原因很简单：b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。

所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

 

5.尽量的扩展索引，不要新建索引

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

可以多用查询优化神器 ： explain命令。

陈睿mikechen

10年+大厂架构经验，资深技术专家，就职于阿里巴巴、淘宝、百度等一线互联网大厂。

关注「mikechen」公众号，获取更多技术干货！

后台回复【面试】即可获取《史上最全阿里Java面试题总结》，后台回复【架构】，即可获取《阿里架构师进阶专题全部合集》