CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`mobile` char(11) DEFAULT NULL,
`age` tinyint(3) DEFAULT NULL,
`sex` tinyint(1) DEFAULT NULL,
`countey` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_mobile` (`mobile`),
KEY `idx_age_name_mobile` (`age`,`name`,`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
create index idx_age_name_mobile on user (age,name,mobile)
三星索引
- 索引将相关的记录放到一起获得一星
- 如果索引中的数据顺序和查找中的排列顺序一致则获得二星
- 如果索引中的列包含了查询中需要的全部列则获得三星
索引的选择性
索引的选择性是指,不重复的索引值(也称为基数,cardinality)和数据表的记录总数(#T)的比值,范围从1/#T到1之间。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行
索引的好处
- 减少扫描数据量
- 避免排序和临时表
- 随机I/O变为顺序I/O
聚簇索引
聚簇索引,InnoDB中索引文件的B+树的叶子节点中,存储的为主键及整个行的数据。
即将索引与数据本身存放在一起,找到索引时也就找到了数据。
非聚簇索引
索引内存储的为数据行的主键,使用非聚簇索引需要回表查询,也就是通过非聚簇索引查找到主键,在根据主键在聚簇索引中查找数据行
覆盖索引
在查询语句中,一个索引包含的列可以覆盖查询的列与条件列,该索引就是一个覆盖索引。覆盖索引可以提高查询效率,是因为查询语句通过索引数据就可以直接获得结果 ,不需要进行回表操作。
主键索引
一张表只有一个主键索引,不可为空不可重复
唯一索引
一张表可以有多个唯一索引,数据列的值不可重复,可为空
普通索引
一张表可以有多个普通索引 ,允许数据 重复允许 数据为空
组合索引
包含两个或 两个以上列的索引,查询时需匹配“最左前缀”原则才可生效,即语句中的查询索引需匹配创建索引时的顺序。
在执行查询时,引擎会对SQL进行优化,对条件语句的顺序会进行调整以匹配索引。
即where name="hello" and mobile="12345678909" and age=10;
在执行时会以where age=10 and name="hkm" and mobile="12345678909" ;
执行,来匹配索引idx_age_name_mobile
。
最左匹配
最左匹配原则,即条件语句 与创建索引时的顺序需要匹配,直至遇到范围查询
create index idx_age_name_mobile on user (age,name,mobile)
该索引下:
age=1 and name='abc' and mobile='123'
✅
age=1 and name='abc' and mobile>'123'
✅
age>1 and name='abc' and mobile='123'
❌
age=1 and name='abc'
✅
name='abc' and mobile='123'
❌
age=1
✅
索引的优化
- 组合索引中的顺序,根据最左匹配原则来创建索引
- 尽可能使用覆盖索引
- 选择区分度高的列作为索引
- 扩展现有索引 而不是 总是新建索引