千万级表索引优化_2018-12-21

创建货物品类表

CREATE TABLE classify (
    id serial,
    name VARCHAR (10)
) ENGINE = MYISAM DEFAULT CHARACTER
SET = utf8 COLLATE = utf8_general_ci AUTO_INCREMENT = 1 ROW_FORMAT = COMPACT;

创建货品表

CREATE TABLE goods (
    id serial,
    classify_id INT (11),
    name VARCHAR (10),
    price DOUBLE,
    stock INT (11),
    update_time datetime 
) ENGINE = MYISAM DEFAULT CHARACTER
SET = utf8 COLLATE = utf8_general_ci AUTO_INCREMENT = 1 ROW_FORMAT = COMPACT;

填充货品类别

insert  into classify values
(null,'水果生鲜'),
(null,'日常百货'),
(null,'电子耗材'),
(null,'回收二手'),
(null,'奢侈品');

填充货品数据（1000万条）

begin
declare i int default 0 ;
dd:loop 
insert  into goods values
(null,1,'商品1',20,3232,now()),
(null,2,'商品2',1699,3434,now()),
(null,3,'商品3',4000,6578,now()),
(null,5,'商品5',139,43,now()),
(null,1,'商品6',10000,783,now()),
(null,2,'商品7',110,23,now()),
(null,3,'商品8',12,5555,now()),
(null,4,'商品9',130,458,now()),
(null,1,'商品0',12,893,now());
  commit;
set i = i+10 ;
   if  i = 100000 then leave dd;
  end if;
 end loop dd ;
end

将标的引擎修改为InnoDB（使用MyISAM插入数据迅速）

ALTER TABLE goods ENGINE=InnoDB;
ALTER TABLE classify ENGINE=InnoDB;

或者创建InnoDB引擎插前禁用检查，插入后再回复回来

# 禁用唯一性检查   
set unique_check=0;
# 禁用外键检查    
set foreign_key_checks=0; 
# 禁止自动提交    
set autocommit=0;
# 引用索引      
alter table goods disable keys

创建索引

INDEX `id_index` (`id`) USING BTREE ,
INDEX `name_index` (`name`) USING BTREE ,
INDEX `price_index` (`price`) USING BTREE ,
INDEX `name_id_index` (`name`, `id`) USING BTREE 
INDEX `name_price_stoce_index` (`name`, `price`, `stock`) USING BTREE 

# 货品表（主键id，金额price，产品名称name，产品库存量num）
# price字段单独索引查询时where条件使用一个确切的值时索引有效
EXPLAIN SELECT * FROM goods g WHERE g.price = 15;
# price字段单独索引查询时where条件使用一个范围的值时索引无效
EXPLAIN SELECT * FROM goods g WHERE g.price >= 15;
# price字段单独索引查询时where条件使用一个范围的值，给主键加索引，只检索主键id，索引生效
EXPLAIN SELECT g.id FROM goods g WHERE g.price >= 15;
# 给name，price，stock创建联合索引，对于多列索引，只有在查询条件中使用了这些字段中的第一个字段时，索引才会被使用
EXPLAIN SELECT * FROM goods g WHERE g.name LIKE '1%' AND g.price >= 15  ;
# 试一下1=1对查询的影响,事实证明，跟不加1=1无任何差别！！！
EXPLAIN SELECT * FROM goods g WHERE 1=1 AND g.name LIKE '1%' AND g.price >= 15  ;
在子查询中，主句连接的索引无效，子句连接的索引有效
EXPLAIN SELECT * FROM classify c  WHERE c.id in (select g.classify_id from goods g where g.price>=2000) ;
# 使用EXISTS关键字替换IN优化
EXPLAIN SELECT * FROM classify c  WHERE  EXISTS (select g.classify_id from goods g where g.classify_id = c.id AND g.price>2000) ;

# 不加order by 默认返回主键的顺序，千万级1.5秒
EXPLAIN SELECT * FROM goods g LIMIT 2000000,10;
# 加order by 没有走索引，千万级11秒
EXPLAIN SELECT * FROM goods g  order by g.id  LIMIT 2000000,10;
# 加order by 走id主键索引，千万级1.2秒
EXPLAIN SELECT g.id FROM goods g order by g.id LIMIT 2000000,10;
# 加order by 走id主键索引，千万级0.6秒
EXPLAIN SELECT * FROM goods g WHERE g.id >= (SELECT sg.id FROM goods sg ORDER BY sg.id LIMIT 2000000,1) LIMIT 10;
# 加order by 走id主键索引，千万级0.001秒
EXPLAIN select * from goods g where g.id  > 8000000 ORDER BY g.id limit 10;
# 千万级耗时7.4秒
EXPLAIN SELECT * FROM goods g order by g.update_time ,g.id LIMIT 2000000,10;
# 千万级耗时0.04秒
EXPLAIN  select * from goods where id between 7000000 and 7000010 order by id desc
# 千万级耗时7.2秒
EXPLAIN SELECT g.update_time,g.id FROM goods g order by g.update_time ,g.id LIMIT 2000000,10;
# 用上了time索引，但是由于根据update_time有大量重复数据，不如id唯一性，导致>结果和上面不一致，千万级1秒
EXPLAIN SELECT * FROM goods g WHERE g.update_time >= (SELECT sg.update_time FROM goods sg ORDER BY sg.update_time LIMIT 2000000,1) LIMIT 10;
# 如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！
# 无索引，千万级2.7秒，有(name,id)索引0.8秒
EXPLAIN select g.id from goods g where g.`name`='商品1' limit 700000,10;