mysql 索引及优化简单总结
首页 计算机相关 数据库 mysql 索引及优化简单总结
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.缓存语句和失效都会带来额外的性能开销