start transaction; insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry'); insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry'); commit;
-- 客户端连接服务端时，加上参数 -–local-infile mysql –-local-infile -u root -p -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 set global local_infile = 1; -- 执行load指令将准备好的数据，加载到表结构中 load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;
示例演示: A. 创建表结构
1 2 3 4 5 6 7 8 9 10
CREATE TABLE `tb_user` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `username` VARCHAR(50) NOT NULL, `password` VARCHAR(50) NOT NULL, `name` VARCHAR(20) NOT NULL, `birthday` DATE DEFAULT NULL, `sex` CHAR(1) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_user_username` (`username`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
B. 设置参数
1 2 3 4
-- 客户端连接服务端时，加上参数 -–local-infile mysql –-local-infile -u root -p -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 set global local_infile = 1;
C. load加载数据
1
load data local infile 'D:/sql/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;
-- 创建索引 mysql> create index idx_user_age_phone on tb_user(age, phone);
D. 创建索引后，根据age, phone进行升序排序
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
mysql> explain select id, age, phone from tb_user order by age; +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tb_user | NULL | index | NULL | idx_user_age_phone | 48 | NULL | 24 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
mysql> explain select id, age, phone from tb_user order by age, phone; +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tb_user | NULL | index | NULL | idx_user_age_phone | 48 | NULL | 24 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
建立索引之后，再次进行排序查询，就由原来的Using filesort， 变为了 Using index，性能就是比较高的了。
E. 创建索引后，根据age, phone进行降序排序
1 2 3 4 5 6 7
mysql> explain select id, age, phone from tb_user order by age desc, phone desc; +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+----------------------------------+ | 1 | SIMPLE | tb_user | NULL | index | NULL | idx_user_age_phone | 48 | NULL | 24 | 100.00 | Backward index scan; Using index | +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec)
也出现 Using index， 但是此时Extra中出现了 Backward index scan，这个代表反向扫描索引，因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。 在MySQL8版本中，支持降序索引，我们也可以创建降序索引。
F. 根据phone，age进行升序排序，phone在前，age在后。
1 2 3 4 5 6 7
mysql> explain select id, age, phone from tb_user order by phone, age; +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | tb_user | NULL | index | NULL | idx_user_age_phone | 48 | NULL | 24 | 100.00 | Using index; Using filesort | +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)
排序时,也需要满足最左前缀法则,否则也会出现 filesort。因为在创建索引的时候， age是第一个字段，phone是第二个字段，所以排序时，也就该按照这个顺序来，否则就会出现 Using filesort。
F. 根据age, phone进行降序一个升序，一个降序
1 2 3 4 5 6 7
mysql> explain select id, age, phone from tb_user order by age, phone desc; +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | tb_user | NULL | index | NULL | idx_user_age_phone | 48 | NULL | 24 | 100.00 | Using index; Using filesort | +----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)
mysql> explain select profession , count(*) from tb_user group by profession ; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 24 | 100.00 | Using temporary | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec)
然后，我们在针对于 profession ， age， status 创建一个联合索引。
1
mysql> create index idx_user_pro_age_sta on tb_user(profession , age , status);
紧接着，再执行前面相同的SQL查看执行计划。
1 2 3 4 5 6 7
mysql> explain select profession , count(*) from tb_user group by profession ; +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tb_user | NULL | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 54 | NULL | 24 | 100.00 | Using index | +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
再执行如下的分组查询SQL，查看执行计划：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
mysql> explain select profession , count(*) from tb_user group by profession, age ; +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tb_user | NULL | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 54 | NULL | 24 | 100.00 | Using index | +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
mysql> explain select age , count(*) from tb_user group by age ; +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | tb_user | NULL | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 54 | NULL | 24 | 100.00 | Using index; Using temporary | +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec)
我们发现，如果仅仅根据age分组，就会出现 Using temporary ；而如果是 根据profession,age两个字段同时分组，则不会出现 Using temporary。原因是因为对于分组操作，在联合索引中，也是符合最左前缀法则的。 所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：
-- 我们先查询sn为100000003145009000000的主键id，这里刚好为9000000，真巧 mysql> select id, sn from tb_sku where sn="100000003145009000000"; +---------+-----------------------+ | id | sn | +---------+-----------------------+ | 9000000 | 100000003145009000000 | +---------+-----------------------+ 1 row in set (0.00 sec)
-- 接着我们用拿到的主键查询接下来的10条数据 mysql> select * from tb_sku where id>9000000 order by id limit 10; 10 rows in set (0.00 sec)
这样来看，我们的分页查询效率比之前高了多少倍大家都有目共睹吧。
count优化
概述
1 2 3 4 5 6 7
mysql> select count(*) from tb_sku ; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (19.86 sec)
