mysql简单索引_mysql 索引及优化简单总结

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.缓存语句和失效都会带来额外的性能开销