索引设计和实例

neptunepunk
162 阅读5分钟

设计

一、先代码，再索引

一般在业务功能开发完成后，再把sql语句拿出来建索引

二、联合索引尽可能的涵盖条件

多建几组联合索引，尽量让联合索引包含在where、order by、group by中，满足最左前缀原则

三、小基数字段上不要建立索引

比如gender字段，代表男-1，女-2，基数就为2，基数太小，发挥不出B+树快速二分查找的优势。

四、长字符使用前缀索引

比如name varchar(255),优化->key index(name(20),age,city)。但是这种情况下，order by就不能用了。group by也是一样。

五、where优先于order by

先用where找出数据，再排序

六、慢sql查询

set global slow_query_log = 1;在slow_query_log_file指定路径找到文件，查看慢sql语句

实战

一、按条件搜索优化（京东搜索空气炸锅）

image.png

  • 按品牌和容量：where brand = xx and volume = xx
  • 建立了索引(brand,volume)
  • 加入价格，去掉容量后筛选 where brand = xx and price > xx and price < xx，索引中加入price->(brand,volume,price)，但是由于没有了volume，是不走索引的，可以优化为： where brand = xx and volume in('3L以下','3-5L','5L以上') and price > xx and price < xx
  • 继续，筛选一周内上架(brand,volume,price,create_time)。where brand = xx and volume in('3L以下','3-5L','5L以上') and price > xx and price < xx and create_time < xx,由于price是范围，所以create_time不走索引
  • 优化方案：create_time改为create_in_week，索引顺序改为(brand,volume,create_in_week,price)。where brand = xx and volume in('3L以下','3-5L','5L以上') and create_in_week = 1 and price > xx and price < xx
  • 根据实际情况，还可以再建另一个辅助联合索引(volume,price)。where volume = '3-5L' order by price asc

二、分页查询优化

select * from table limit 10000,10，mysql在执行该语句时，是取出10010条数据，然后返回最后10条，所以在分页时，查询效率会随着页码的增加而变小。
优化：

  • 主键自增且连续的情况下，sql语句可以改成select * from table where id>10000 limit 10
  • 如果主键不连续时，select * from table t inner join (select id from table limit 10000,10) td on t.id = td.id，虽然也会找10010条数据，由于找的是主键，所以速度很快，找出之后，再和原来的语句合并匹配出结果，就很快了。

三、join关联查询算法

驱动表/被驱动表

也可以理解成sql执行顺序，先驱动表，再被驱动表。

  • inner join时，优化器会优先选择小表做驱动表，和顺序无关
  • left join时，左表驱动表；right join时，右表驱动表；join时，数据量小的是驱动表

1、嵌套循环连接（NLJ）算法

一次从第一张表(驱动表)中取一行数据，根据关联字段在另一张表(被驱动表)中取出相关数据，最后取两张表的结果合集。

  • 例如a1表，字段(id,x,y)，10000条数据，a2表字段(id,x,y)，100条数据，a1和a2关联字段为x。
  • 语句：select * from a1 inner join a2 on a1.x=a2.x；a2是驱动表，a1是被驱动表
  • 用explain分析时，如果extra中没有出现using join buffer，就表示join使用了NLJ算法
  • 上面👆语句查了200行。
  • 如果没有使用关联字段，select * from a1 inner join a2 on a1.y=a2.y；mysql会使用基于块的嵌套循环连接（BNL）算法

2、基于块的嵌套循环连接（BNL）算法

把驱动表的数据放入到join buffer中，然后扫描被驱动表，把被驱动表中的每一条数据取出来和join buffer做对比。

  • select * from a1 inner join a2 on a1.y=a2.y
  • 把a2中的100条数据放入到join buffer中，把a1中的每一条数据取出来和join buffer中的a2做对比，整个过程在内存中判断了100 * 10000 = 100万次。如果a2的100条在join buffer中放不下，那么会先放80条进去，判断完后，再放剩下的20条，再判断。
  • 这种情况如果用NLJ算法，也是100万次，但是NLJ是磁盘扫描，没有BNL快。

3、join建议

  • 关联查询时，把关联字段加索引，尽量走NLJ算法。（前面200行扫描->100万次）
  • 如果明确知道哪个是小表，可以用straight_join固定连接方式，省去mysql判断(有时候优化器判断错误)，用小表驱动大表。
  • 小表的确定：并不是总数据量小的为小表，要看实际参与join的数据，例如：select * from a1 inner join a2 on a1.x=a2.x where a1.id<10；那么，a1就是小表。 例如select * from a1 straight_join a2 on a1.y=a2.y代表a1固定为驱动表。
  • straight_join只适用于inner join，因为left join和right join已经指定了驱动表。

四、in和exists优化，小表驱动大表的原则

1、当B小于A时，用in

select * from A where id in (select id from B)

等价于：

for(select id from B){
    select * from A where A.id = B.id
}

2、当A小于B时，用exists

select * from A where exists (select 1 from B where B.id = A.id)

等价于：

for(select id from A){
    select * from B where A.id = B.id
}

A和B的id字段建立索引

五、count(*)

select count(*) from A
select count(id) from A
select count(name) from A
select count(1) from A

上面四条语句执行效率差不多
字段有索引：count( * )>count(1)>count(字段)>count(主键id)
字段无索引：count( * )>count(1)>count(主键id)>count(字段) count(*)会统计值为null的行，count(列)不会

avatar
文章
阅读
粉丝