一、细节优化
1、当使用索引列进行查询的时候尽量不要使用表达式,把计算放到业务层而不是数据库层,比如:
select * from actor where actor_id + 4 = 5; 应该改为:
select * from actor where actor_id = 1;
2、尽量使用主键查询,而不是其他索引,避免触发回表查询;
3、使用前缀索引:
如果索引很长,需要占用更多的存储空间,以及执行时间;通常情况下可以使用其开始的部分字符串,从而提高索引 的效率,但这会降低索引的选择性,索引的选择性是指不重复的索引值和数据表记录总数的比值,范围从1/#T到1之间。
案例:
--查找最常见的名称列表,
select count(*) as cname, name from student group by name order by cname desc limit 10;
--查找最频繁出现的名称前缀,先从3个前缀字母开始,发现比原来出现的次数更多,可以分别截取多个字符查看名 称出现的次数
select count(*) as cname,left(name ,3) as pref from student group by pref order by cname desc limit 10;
select count(*) as cname,left(name ,7) as pref from student group by pref order by cnamedesc limit 10;
--计算完成之后可以创建前缀索引
alter table student add key(name (7));
--注意:前缀索引是一种能使索引更小更快的有效方法,但是也包含缺点:mysql无法使用前缀索引做order by 和 group by。
4、使用索引扫描来排序
5、union all,in,or都能够使用索引,但是推荐使用in
6、范围列可以用到索引
--注意:范围列可以用到索引,但是范围列后面的列无法用到索引,索引最多用于一个范围列(组合索引)
7、强制类型转换会全表扫描
-- 比较 explain select * from user where phone=13800001234 与 explain select * from user where phone=‘13800001234’
8、更新十分频繁,数据区分度不高的字段上不宜建立索引
(1)、更新频繁的字段,也会使B+树频繁更新,降低数据库性能
(2)、区分不大的属性,无法过滤无关数据
(3)、一般区分度在80%以上的时候就可以建立索引,区分度可以使用 count(distinct(列名))/count(*) 来计算
9、创建索引的列,不允许为null,可能会得到不符合预期的结果
10、当需要进行表连接的时候,最好不要超过三张表,因为需要join的字段,数据类型必须一致
11、能使用limit的时候尽量使用limit
12、单表索引建议控制在5个以内
13、单索引字段数不允许超过5个(组合索引)
14、创建索引的时候应该避免以下错误概念
(1)、越多越好
(2)、过早优化,在不了解系统的情况下进行优化
二、索引监控
show status like 'Handler_read%';
Handler_read_first:读取索引第一个条目的次数
Handler_read_key:通过index获取数据的次数
Handler_read_last:读取索引最后一个条目的次数
Handler_read_next:通过索引读取下一条数据的次数
Handler_read_prev:通过索引读取上一条数据的次数
Handler_read_rnd:从固定位置读取数据的次数
Handler_read_rnd_next:从数据节点读取下一条数据的次数