mysql 500万数据库_MySQL 500万的单表性能

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

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

通过主键查询，基本上是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_num

end 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

1SIMPLEtbl_user_no_partrefix_no_part_nameix_no_part_name768const2Using 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

1SIMPLEtbl_user_no_partALL4981948Using where

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

1SIMPLEtbl_user_no_partALL4981948Using where

模糊查询 通过索引3.6千秒

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

1SIMPLEtbl_user_no_partrangeix_no_part_nameix_no_part_name768373842Using 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,1388*6*0.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';

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

| database_name | table_name       | last_update         | n_rows  | clustered_index_size | sum_of_other_index_sizes |

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

| 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)

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

| id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |

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

|  1 | SIMPLE      | tbl_user_no_part | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4980250 |    33.33 | Using where |

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

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

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

| id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |

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

|  1 | SIMPLE      | tbl_user_no_part | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4980250 |    10.00 | Using where |

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

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

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

| id | select_type | table            | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |

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

|  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单表确实有个阀值，超过该阀值就无法兼顾了。