MySQL索引

索引的种类

• 普通索引：加速查找
• 主键索引：加速查找 + 不能为空 + 不能重复
• 唯一索引：加速查找 + 不能重复
• 联合索引(联合唯一):
  • 联合主键索引
  • 联合唯一索引
  • 联合普通索引
    -无索引：从前到后依次查找
    -有索引：创建额外的文件(某种格式存储)

原理

• hash索引:索引表
  • 特点：索引表因为哈希值变成了无序的
  • 优点：找单值快
  • 缺点：找范围慢
• btree索引
  • 二叉树
• 特点
  • 额外的文件保存特殊的数据结构，需要额外空间
  • 查询快，插入更新删除慢
  • 命中索引

创建索引

普通索引
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

• 问题：limit是全局扫描，也就意味着limit后面的数字超级大之后会查询的非常慢
• 解决方案
  • 记录当前页的最大id和最小id，然后翻页的时候用当前页的最大或最小id进行limit