Mysql调优——通过索引优化

通过索引进行优化

优化小细节；

当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层。

#如

select actor_id from actor where actor_id = 4;

select actor_id from actor where actor_id - 1 = 5;

尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表操作。

使用前缀索引——详情见“前缀索引实例说明.md”

前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by 和 group by。

使用索引扫描来排序——详情见“使用索引扫描来做排序.md”

union all、in、or都能使用索引，但推荐使用in，如果使用union的话建议改为union all，因为union中有一个distinct去重，而去重是很麻烦的过程。

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

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

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

使用exists(sub select)查询，and的优先级比or要高，用or时要用括号包起来

select * from actor where exists(select 1 from actor where actor_id = 1 or actor_id = 2);

#exists的效率比较高，子查询里面返回的时候一个布尔类型数据true/false，一旦查询到了有值(true)则就返回数据。

范围列可以用到索引

范围条件是：< 、>

范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列。

强制类型转换会导致全表扫描

create table user(id int,name varchar(10),phone varchar(11));

alter table user add index idx_1(phone);

explain select * from user where phone=13479315954;#由于phone是varchar类型，这里进行了强制转换，不会报错，但索引也失效了

explain select * from user were phone='13479315954';#触发索引

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

更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能

类型于性别这类区分不大的属性，建立索引是没有意义的，不能有效过滤数据

一般区分度在80%以上的时候就可以建立索引，区分度可以使用count(distinct(列名))/count(1)来计算。

创建索引的列，不允许为null，可能会得到不符合预期的结果。

当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致。

能使用limit的时候尽量使用limit。

单表索引建议控制在5个以内

单索引字段数不允许超过5个(组合索引）

创建索引的时候应该避免以下错误概念：

索引越多越好。

过早优化，在不了解系统的情况下优化。

join on和left join on的区别：

join on只有符合条件的数据才查询出来，即数据有交集才查询出结果

left join on把左边表的数据全部查出，右边表符合条件的才查询出结果，不符合时用null填充

索引监控：

输入指令：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:从数据节点读取下一条数据的次数