MySQL：千万级数据下的优化过程探索

一、MySQL 测试数据准备

1、表结构

CREATE TABLE `goods` (
`id`  bigint(20) ,
`goods_name`  varchar(10) ,
`price`  double ,
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1
ROW_FORMAT=DYNAMIC

2、存储过程插入数据

begin
declare i int default 0 ;
dd:loop 
insert  into goods values
(null,'商品1',20),
(null,'商品2',18),
(null,'商品3',16),
(null,'商品4',4),
(null,'商品5',13),
(null,'商品6',1),
(null,'商品7',11),
(null,'商品8',12),
(null,'商品9',13),
(null,'商品0',12);
  commit;
set i = i+10 ;
   if  i = 10000000 then leave dd;
  end if;
 end loop dd ;
end

二、性能优化

1、分页查询性能优化

select * from goods limit 8000000,1000;

执行时间： 2.160 ms.

select * from goods where id > 8000000 limit 1000;

执行时间： 0.096 ms.
结论： 大的分页偏移量会增加使用的数据，MySQL会将大量最终不会使用的数据加载到内存中。就算我们假设大部分网站的用户只访问前几页数据，但少量的大的分页偏移量的请求也会对整个系统造成危害；对应于自增id的表，如果数据量非常大的分页查找，可以观察id的分布规律计算出其id的范围通过范围查找来实现分页效果。

2、索引优化

索引优化主要用于区分度高的数据表中，而我的测试数据中每天记录之间的区分度很低，故而建立索引后对于查询的提升也很小，区分度大的数据中建立索引，优化效果更明显。

count(distinct col)/count(*)；

索引失效的情况应该尽量避免：

• like查询以 % 开头。
• where条件中带有or，即使其中条件带索引也不会触发。
• !=，not in ,not exist不会使用索引。
• where字句的查询条件里使用了函数或计算。
• 复合索引如果单独使用，只有复合索引里第一个字段有效。

3、VARCHAR探讨

• VARCHAR在硬盘占用上确实是按实际大小占用
• 但如果涉及到临时表，是按后面的数字分配内存
• 在VARCHAR列建立索引，key_len也是按照后面数字分配
  结论： VARCHAR按需取长 ，避免临时表占满内存溢出至磁盘从而导致查询速度下降。