500万的单表性能

上次谈了单表千万会变慢的问题，从理论来说找不到变慢的利用。

MYSQL 单表千万变慢

全表查询，当然会越大越慢啊！

通过主键查询，基本上是3次索引IO，外加1次行IO

MYISAM 是3+1次 INNODB是3次

通过二级索引

MYISAM是3+1次，innodb是3+3次（二级索引+主键索引）

插入和更新 MYISAM 堆表结构，插入时间不会随数据量增长而增长。INNODB 就会，因为它是有序的，必须进行索引分裂。

下面建表测试500万的数据

CREATE TABLE tbl_user_no_part (

id int(11) NOT NULL AUTO_INCREMENT,

username varchar(255) DEFAULT NULL,

email varchar(20) DEFAULT NULL,

age tinyint(4) DEFAULT NULL,

type int(11) DEFAULT NULL,

create_time datetime DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (id),

KEY ix_no_part_name (username) USING BTREE,

KEY ix_no_part_createtime (create_time) USING BTREE,

KEY ix_no_part_email (email) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8；

set global log_bin_trust_function_creators = true ;

delimiter $$

-- 随机生成一个指定长度的字符串

create function rand_string(n int) returns varchar(255)

begin

declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

declare return_str varchar(255) default '';

declare i int default 0;

while i < n do

set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));

set i = i + 1;

end while; return return_str;

end $$

-- 创建插入的存储过程

create procedure insert_user(in start int(10), in max_num int(10))

begin

declare i int default 0; set autocommit = 0;  repeat    set i = i + 1;    insert into tbl_user_no_part values ((start+i) ,rand_string(8), concat(rand_string(6), '@random.com'), 1+FLOOR(RAND()*100), 3, now());    until i = max_numend repeat;

commit;

end $$

-- 将命令结束符修改回来

delimiter ;

拿MYSQL开发库 --虚拟机上6GB 2核2.4GHZ FOR WIN2008

call insert_user(100001,5000000);--4183.074

生成数据花费4千秒

通过全表方式统计行数花费63秒

select count(*) from tbl_user_no_part; --63.910

通过索引花费5.9秒

select count(*) from tbl_user_no_part; --5.967s ix_no_part_createtime

通过索引等值查询姓名

select * from tbl_user_no_part where username ='lJMJiOtD'; --0.832

1 SIMPLE tbl_user_no_part ref ix_no_part_name ix_no_part_name 768 const 2 Using index condition

范围查询基本都没用上索引返回数据5万-100万之间

select * from tbl_user_no_part where age <=20 --78.076秒 100,0013条

select * from tbl_user_no_part where age >=80 --47.384 105,0969

1 SIMPLE tbl_user_no_part ALL 4981948 Using where

select * from tbl_user_no_part where age =80 --62.743 4，9928

1 SIMPLE tbl_user_no_part ALL 4981948 Using where

模糊查询通过索引3.6千秒

select * from tbl_user_no_part where username like 'a%'; --3639.332 19,1388

1 SIMPLE tbl_user_no_part range ix_no_part_name ix_no_part_name 768 373842 Using index condition

很显然 MYSQL优化器有点傻，据说返回数据占表数据10%以下，通过索引去访问。

从上面AGE来看 100万占500万为20% 当5万占500万为1%，为啥不走索引呢？

因为它没有直方图，从EXPLAIN 返回信息是4981万。

而模糊查询 LIKE 'a%' 花费近4千秒返回近20万的数据执行计划评估是37万数据。

这里AGE 和 NAME 索引都是有序的 a% 开头的姓名应该在一起，就是通过二级范围扫描就可以了。为什么模糊查询那么慢？接近插入500万的数据时间？

按理论来说二级索引需要6次IO 得到一个数据

select * from tbl_user_no_part where username ='lJMJiOtD'; 返回2行数据它也是通过二级索引 3+3+3=9次IO 每次IO 大约0.1秒。

19,138860.1=11,4832秒 11万秒。而上面4千秒MYSQL优化不少！

这里可以得到个结论，在大表下，返回大量数据，并且通过二级索引的话，会很慢，很慢的。不过ORACLE通过索引返回大量数据，同样得慢，这不是MYSQL的个性，是数据库的通性。

据互联网应用限制每个SQL不能超过5秒，一般维持在1-2秒，最好是毫秒级别。

显然上面除了等值查询username ='lJMJiOtD'; 符号互联网的要求，其他4个都不行！

要是换上好的硬件呢？

##物理机 15G E5-2630 2.2GHZ 1

mysql> call insert_user(100001,5000000);

Query OK, 0 rows affected (19 min 43.00 sec)

19分钟比4183秒 69分钟快多了！

表已分析

mysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name like 'tbl_user_no_part';

+---------------+------------------+---------------------+---------+----------------------+--------------------------+

+---------------+------------------+---------------------+---------+----------------------+--------------------------+

| test | tbl_user_no_part | 2019-06-20 09:35:35 | 4980250 | 19956 | 30041 |

+---------------+------------------+---------------------+---------+----------------------+--------------------------+

获得总数通过索引

select count(*) from tbl_user_no_part; --1.16 ix_no_part_createtime

比5.967s 快乐不少！

其他查询，走的同样得执行计划，都大幅提升

select * from tbl_user_no_part where age <=20;

999009 rows in set (3.22 sec)

+----+-------------+------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

+----+-------------+------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

+----+-------------+------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

SELECT * FROM tbl_user_no_part WHERE age =80 49946 rows in set (2.46 sec)

+----+-------------+------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

+----+-------------+------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

+----+-------------+------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

select * from tbl_user_no_part where username ='TFwrvdem'; 1 row in set (0.005 sec)

+----+-------------+------------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+

+----+-------------+------------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+

| 1 | SIMPLE | tbl_user_no_part | NULL | ref | ix_no_part_name | ix_no_part_name | 768 | const | 1 | 100.00 | NULL |

+----+-------------+------------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+

牛叉吧都达到了互联网应用的需求！

这下好了！下次回答面试官多大的单表会变慢？

回答如下

假设是INNODEB引程下

1 插入更新的性能会随数量增大而变慢

2 通过二级索引返回大量数据时会越来越慢

3 MYSQL单表没有限定多少万就要考虑分表分库，而是指目前硬件支持条件下，和业务并发量下，客户响应时间要求限制下。MYSQL单表确实有个阀值，超过该阀值就无法兼顾了。

推荐资讯

推荐站点