4.mysql调优--mysql索引优化实现细节

mysql优化细节

• 触发回表查询
• 使用前缀索引
• 使用索引扫描来排序
• union all、or、in三种索引查询方式

select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;

select * from actor where actor_id = 1 or actor_id = 2;

select * from actor where actor_id in (1,2);

union all、or、in三种查询方式，都能使用到索引，但推荐使用in。

• 范围列索引

范围条件是：<、<=、>、>=、between，范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多可以用到一个范围列。

• 强制类型转换会全表扫描

create table user(
	id int,
	name varchar(10) not null default '' comment '姓名',
	phone varchar(11) not null default '' comment '电话'
)charset utf8 comment '用户表';

alter table user add index idx_1(phone); 

没有用到索引：explain select * from user where phone = 90;

有用到索引：explain select * from user where phone = '90';

• 更新十分频繁，数据区分度不高的字段不宜建立索引

1. 更新会变更B+树，更新频繁的字段建立索引会降低数据库性能；
2. 类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据；
3. 一般区分度在80%以上就可以建立索引，区分度可以使用count(distinct(列名))/count(*)来计算；​​​​​​​

• 创建索引的列不允许为空，可能会得到不符合预期的结果
• 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致

• show variables like '%join_buffer%';

        show status like 'Handler_read%'；

• 如果明知道只有一条结果返回，limit能够提高效率
• 单表索引建议控制在5个以内
• 单索引字段数不允许超过5个
• 创建索引的时候应该避免以下错误概念，索引越多越好，过早优化，在不了解系统的情况下进行优化