mysql 索引及优化简单总结

2018-08-05 22:32:14

1.索引大大减少了扫描数据量。
2.索引可以过滤掉不需要的行,减少行级锁。
3.帮助服务器避免排序和临时表。
4.可以将随机i/o变为顺序i/o。
5.数据量少的表简单的全表扫描就足够了,中到大型表用索引很不错。
6.但是特大型表(建立索引代价很大)最好用一种技术来区分要查询哪一组数据,比如分区技术,水平分表技术。
7.使用联合索引一般法则为数量小的在前。

注意:索引会降低 insert、update、delete 速度,而且索引会产生碎片,不过可以通过删除索引,再重建索引来解决或者optimize table。

独立索引

这是最常见的索引,很多新手也只会用这类索引。

#key(a_id)
#利用不到索引
select name from actor where a_id + 1 = 5;

#利用到索引
select name from actor where a_id = 4;

联合索引

假设我们经常要使用如下操作。

select name from actor where a=1 and b='111' and c='222'
select name from actor where a=1
select name from actor where a=1 and b='111'

用么利用a为条件,要么利用a、b,要么利用 a、b、c(后面两个必须保持顺序)。

那么我们可以建立联合索引。

key(a,b,c)

联合索引就算里面有几个字段,但也只是一个索引,维护成本只有一个。如果像下面这样建立3个独立索引。

key(a),key(b),key(c)

那么就是3个索引。

当然要不要这样做,也要看实际情况,如果第一个条件 a=xxx 已经能过滤掉大部分了,那么只建立 key(a) 索引也就够了。

聚簇索引---主键

1.严重依赖顺序,所以一般自增
2.更新索引代价很高,所以一般很少更新主键
3.如果没有定义主键,innodb会选择唯一非空索引代替,如果没有,会隐式定义主键作为聚簇索引
4.定义了主键id,那么建立索引key(aa,bb)其实相当于key(aa,bb,id)
5. where aa=3 order by bb,id 能利用到索引排序,排序效率很高

覆盖索引

就是查询的数据本身的字段就是索引,所以就无需再去查询数据行,速度会快很多。

利用索引排序例子

unique key re(rental_date, inventory_id, customer_id)

#成功,因为rental_date为常量
where rental_date = '2005-05-25' order by inventory_id,customer_id

#成功,因为order by下的2列为索引的最左前缀
where rental_date > '2005-05-05' order by rental_date,inventory_id

#失败,
where rental_date >'2005-05-25' order by inventory_id,customer_id

#失败,不能使用两种不同的排序方向
where rental_date='2005-05--25' order by inventory_id desc,customer_id asc

#失败,order by子句中使用了一个不在索引中的列staff_id
where rental_date='2005-05--25' order by inventory_id,staff_id

#失败,where和order by中的列无法组合成索引的最左前缀,中间缺失了inventory_id
where rental_date='2005-05-25' order by customer_id

#失败,多个条件
rental_date=2005-05-25 and inventory_id in (1,2) order by customer_id

#失败,由于优化器在优化时将film_actor表当作关联的第二个表,所以实际上无法使用索引
select actor_id,title from sakila.film_actor join sakila.film using(film_id) order by actor_id

索引冗余

#下面的key(a)多余
key(a,b), key(a)

#下面的索引就不是多余
key(a,b), key(b,a)

#key(state_id)--q1
select count(*) from userinfo where state_id = 5;       #速度很快

#q2
select state_id, city, address from userinfo where state_id=5;      #速度还行
#如果加上3星索引,就能成为覆盖索引速度提升很多key(state_id, city, address)
#但是q1就变慢了,所以如果需要更快的查询速度,可以建立2个索引,但是索引多了会影响insert,updat,delete

其他

#key(sex,country)
where country='cn' #利用不到索引

#当不需要过滤性别就可以利用诀窍如
where sex in(1,2) and country='cn'
#当然如果sex有很多值就不适合

#不加索引速度很慢
select col from profiles where sex='m' order by rating limit 3,10;
#加索引 key(sex,rating) 加速查询和排序。


#像这种mysql要大量的时间来扫描需要丢弃的行
#只能通过反范式,缓存或预先计算来优化
select col from profiles where sex='m' order by rating limit 30000,10;

当然也可以用覆盖索引来优化,比如通过覆盖索引找到10个id(很快),然后再通过id来超找需要的列。

如果要使用 like 模糊匹配,模糊匹配项只能在后面。

like '111%'


垂直分表

1.把常用的几个字段放一张表,不常用的放到另一个表,通过一个整形字段关联,因为一个表的字段越少,查询速度越快。

水平分表

根据特定值的hash或取余技术,分配到几十张不同表中,比如用户发布的文章,可以建立10张article表,然后通过用户id%10来确定存入哪张表。

事范式,反范式

1.范式就是避免数据冗余
2.反范式就是允许数据一定的冗余来增加查询速度。

范式优点:更新操作比反范式快,表通常更小,可以更好的放在内存,所以执行操作很快。一般无需distinct 或 group by。
范式缺点:一般需要关联查询。

一般实际应用肯定是两者结合。

计数器优化例子

#每次更新都会上锁,也就是只能串行执行,效率一般
update hit_counter set cnt = cnt + 1;

#设计两个字段,通过sum来获取总数,这样一次只锁定一条
update hit_counter set cnt = cnt + 1 where slot = rand() + 100;
select sum(cnt) from hit_counter;

查询过程

1.服务器检查缓存,命中缓存直接返回,否则进入下一阶段
2.sql解析,预处理,再由优化器生成对应的执行计划并调用api执行

切分查询

1.将一个大查询切分成几个小查询,比如删除时候别一次性删除很多,可以分时删除一部分避免锁住大多资源。

分解关联查询

1.把一句搞定但是需要联表的查询分解成3个独立的查询。

优点:

1.缓存的效率更高
2.减少锁的竞争
3.在应用层做关联更容易拆分
4.减少冗余数据

连接查询

原理是先生成临时表,然后通过嵌套循环,注意,确保 on 或 using 上有索引,确保order by,group by只涉及一个表的列。

如果连接或子查询第二个查询条件很少,可以考虑拆分成多个独立查询。

连接查询排序

如果 order by 子句的所有列都在第1个表中,那么在查询第1个表时就会先排序然后放到临时表,再跟第2个表进行联查。

如果order by列多表都有,那么只能先查询完所有放入临时表,再在临时表进行排序。

#性能极差
select * from film where file_id in (select film_id from film_actor where actor_id=1)

#先全表查询comments,再逐个跟子查询比较
explain select name from comments where EXISTS (select * from tags where tagid=1 and comments.star=tags.aaa)

#关联子查询用的好性能也还好,最好自己试试
select count(*) from city where id > 5;

视图

虚拟表

外键

要来保证多表键数据一致性

查询缓存

1.sql语句尽可能的写死而不是调用里面的函数
2.查询缓存会对写入操作带来影响,当写入数据,mysql会把所有跟她有关的缓存设置为失效
3.所以查询缓存的内存大小应该设置为合理的值,而不是越大越好
4.缓存语句和失效都会带来额外的性能开销

©著作权归作者所有
收藏
推荐阅读
简介
天降大任于斯人也,必先苦其心志。