普通索引
create index 索引名称 on 表名(列名)
drop index 索引名称 on 表名
唯一索引
create unique index 索引名称 on 表名(列名)
组合索引
create unique index 索引名称 on 表名(列名1,列名2)
create index ix_name on userinfo(name,email)
最左前缀匹配
select * from userinfo where name='wunan' 生效,name是最左,可以索引
select * from userinfo where name = 'wunan' and email = 'wunan' 符合 name是最左,可以索引
select * from userinfo where email = 'wunan' 不符合,email不是最左,不能索引
名词
索引覆盖
在索引文件中直接获取数据
索引合并
把多个单列索引合并使用,即将两个索引条件and起来
效率
组合索引 > 索引合并
无法命中索引的情况
like ‘%xx’ 运用like语句无法命中索引
select * from tb1 where email like '%cn'
使用函数
select * from tb1 where reverse(email) = 'wunan'
因为反转了,所以无法查询索引表
or
虽然nid是索引,但name不是,所以无法命中
select * from tb1 where nid = 1 or name = 'wunan'
特别的:nid和email是索引列
select * from tb1 where nid = 1 or name = 'wunan' and email = 'wunan@123.com'则可以命中索引
类型不一致
如果列是字符串型,传入的条件必须用引号引起来,否则会进行类型转换,因此无法命中索引
select * from tb1 where email = 999
!=
不能走索引
select * from tb1 where email != "wunan"
特别的:如果where后面的条件是主键也可以走索引
“>”
大于某个字符串不会命中
select name from tb1 where email > '123'
特别的:如果主键或者索引类型是数字,则可以进行索引
order by
select name from order by email desc;
如果根据索引排序,但是最后取的数据不是索引列的,则不会走索引,如果是对主键排序则不会影响
最左前缀(上文已经提到了)
其它
避免使用select *
count(1)或count(列)代替count(*)
创建表尽量使用char代替varchar
表的字段顺序固定长度的字段优先
组合索引代替多个单列索引(经常使用多个条件查询时)
尽量使用短索引(局部索引) create index ixxx on userinfo(name(5))即对name的前几个字符取索引
使用join来代替子查询
连表注意类型一致
索引散列值少的不适合建立索引
执行计划
定义:让mysql预估执行操作的时间。
explain select * from userinfo;
其中显示的结果id代表第几个sql语句,type代表搜索表的类型
type类型查询速度比较:
all < index <range < index_merge < ref_or_null < ref < eq_ref < system/const
type类型解释:
all 全表扫描,对于数据表从头到尾找
index 全索引扫描,对索引表扫描一遍
range 对索引列进行范围查找
index_merge 索引合并使用
ref 根据索引查找一个或多个值
eq_ref 唯一索引或者主键索引
const/system 根据主键找 并只找到一个数据
分页性能分析
进行分页,从第20条数据开始,显示十条
select * from userinfo limit 20,10