文章目录
前言
mysql索引
一、mysql索引
1.索引的创建
代码如下:
create unique/fulltext index index_name
on table_name(name,age,col);
//name,age,col索需要创建索引的字段,多个字段指创建联合索引
//unique表示创建唯一索引,创建索引的列不能重复
//fulltext表示创建全文素印
//不加参数表示创建的是常规索引
事例如下(linux中):
2.查看索引
show index from tb_user;
3.删除索引
drop index index_phone on tb_user;
4.查看sql的执行性能
explain select * from tb_user;
//查看select语句的具体执行
各列所表示的含义
(1)id:表示select的执行顺序,id越大,越先执行(多表连接时可用)
(2)possible_key:可能用到的索引
(3)key:实际用到的索引
(4)key_len:索引的长度
(5)type:索引的性能(性能由好到坏:null、system、const、eq_ref、range、index、all)
二、索引的使用规则
1.最左前缀法则(联合索引)
从索引最左边的列开始,不跳过中间的某一列,当跳过某一列时,后续的索引将会失效,跳过最左边的索引,则全部失效。
代码如下:
create index index_pho_age_gen on tb_user(phone,age,gender);
//创建联合索引
当对三个条件进行使用时,索引生效
当删除最左边的phone后,索引失效
当删除后两个条件时,索引依旧生效
(注:删除最右一列时,并未影响到索引的长度????)
当用到<>时后面的索引会失效。
2.索引失效情况
(1)当在索引上进行运算操作时索引会失效
(2)字符串类型的字段不加引号索引会失效,查询会成功
(3)头部进行模糊匹配,索引失效,例如like"%mm",后面不会
(4)使用or连接时,当一侧有索引而另一侧没有索引时,则索引失效
(5)数据分布影响,当使用索引反而更慢时,mysql也不会使用索引
3.sql提示
当一个字段既有单列索引又有联合索引时,mysql会选择一个执行,sql提示是用户自己指定所使用的索引。
select * from tb_user use index(index_name) where condition;
//指定需要使用的索引
select * from tb_user ignore index(index_name)where condition;
//不要使用的索引
4.覆盖索引与回表查询
1.覆盖索引:select * 的效率较低,尽量避免,可以使用select (使用的索引的列) 代替。
1.当执行上面的语句时,mysql会查询聚集索引最终返回一列
2.当执行下面的语句时,mysql会查询辅助索引,最终返回id,姓名,不涉及回表查询,但当执行的select语句中包含其他列(profession,age等)时,则需要回表查询聚集索引,根据id查询所需要的信息,此时查询效率降低。
3.select * 很容易出现回表查询的情况
5.前缀索引
字符串的一部分前缀截取出来建立索引。
create index index_name on table_name(column(n))
//n代表的是前几个字符来构建索引
总结
单列索引与联合索引的使用,mysql会自动根据那个性能更高而选择使用哪个索引,一般来讲,联合索引的性能要高于单列索引。
首先按phone排序,一样时则会按照name字段排序