百万数据的curd测试
经常使用mysql的我们都知道，一旦数据量大起来，还是用以前的老方法查询，卡是必然的。那么怎么优化和合理的使用索引，写出高效的sql语句。下面俺来测试一波~
准备工作
这里有位大佬的导入一百万条数据的方法：点我打开
导入1w条数据约16s，导入100w条数据约1600s,不想等那么长时间导入数据的，可以直接看本人的测试结果
表结构和测试数据
表结构
CREATE TABLE `test_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`user_id` varchar(36) NOT NULL COMMENT '用户id',
`user_name` varchar(30) NOT NULL COMMENT '用户名称',
`phone` varchar(20) NOT NULL COMMENT '手机号码',
`lan_id` int(9) NOT NULL COMMENT '本地网',
`region_id` int(9) NOT NULL COMMENT '区域',
`create_time` datetime NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1020001 DEFAULT CHARSET=utf8mb4 COMMENT='普通表';
数据demo
mysql> select * from `test_user` where `id` = 1\G;
*************************** 1. row ***************************
id: 1
user_id: 8488b3da-eabd-11eb-8437-0242ac120005
user_name: mrXhmNKdEuhN0t9cpi5p
phone: 13636237617
lan_id: 255
region_id: 6
create_time: 2021-07-22 15:22:05
1 row in set (0.00 sec)
查询简单和复杂字段对比
ps:
id = 123123 等的整型，数据相对是最简单的了，全是位数不大的数字
phone = '13636237617' 等的字符串，数据比id复杂一点
user_id = '0000170a-eac2-11eb-8666-0242ac120005' 等的字符串,数据比phone又复杂一点
select id from test_user; -- 查询简单字段(全盘扫描)： 1.8s
select phone from test_user; -- 查询复杂字段(全盘扫描)： 3.6s
select user_id from test_user; -- 查询复杂字段(全盘扫描)： 5s
查询少量和大量字段
select id from test_user; -- 查询少量字段：2.43s
select * from test_user; -- 查询大量字段：9s
curd索引有效和失效的对比
-- 查询少量数据（结果集较少的）：
select * from test_user where user_id like '%8488b3da-eabd-11eb-8437-0242ac12%' -- like查询(索引失效) : 1.3s
select * from test_user where user_id like '8488b3da-eabd-11eb-8437-0242ac12%'; -- like查询(索引有效) : 0.003s
select * from test_user where id = 1000; -- 等值查询(索引有效) : 0.003s
select * from test_user where abs(id) = 5; -- 运算符号绝对值(索引失效) ： 0.9s
select * from test_user where id=5 or id = -5; -- 运算符号替代方案(索引有效) ：0.007s
-- 查询大量数据（结果集较多的）：
select * from test_user where id > 100000; -- 查询90w数据出来(索引有效)：9s
select * from test_user where id < 100000; -- 查询10w数据出来(索引有效)：0.9s
select * from test_user where id > 100000 limit 100000; -- 查询10w数据出来(索引有效)：0.9s
-- 查询的数据类型出现隐式转化：
select * from test_user where user_id = 8437; -- 出现隐式字符转整型(索引无效)：6s
select * from test_user where user_id = '8437'; -- 正确的数据格式查询(索引有效)：0.004s
-- 增
INSERT INTO `test_data`.`test_user`(`id`, `user_id`, `user_name`, `phone`, `lan_id`,`region_id`, `create_time`)
VALUES (11, '843712', 'qsQFFaFGid2myxQszlS8', '18792592447', 683, 2, '2021-07-22 15:22:05'); -- 0.013s
-- 删
delete from test_user where user_id = '843712'; -- 使用索引：0.012s
delete from test_user where phone = '18792592447'; -- 未使用索引：3.69s
-- 改
update test_user set user_id='84371' where user_id = '8437' -- 使用索引：0.05s
update test_user set user_id='843712' where phone = '18792592447' -- 未使用索引：5.12s
结论
1 百万级别的单条数据查询，有无索引速度差别在50-100倍；
2 更新：只要用到了where条件的都受到索引的影响；
3 新增：速度最快,不存在查询所以百万数据的表中新增速度不受影响；
4 删除：无论有无索引，都比修改数据要快；
（ps:有文章说or查询条件的左右都必须是索引字段，否则索引会失效，本人亲测暂时没有看出差别)
索引失效分析
1、like 以%开头，索引无效。（仅后缀有%时，索引还是有效的）
2、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引失效，产生全表扫描。
3、对索引字段使用函数或者计算，如abs或+-运算等，将导致索引失效。
4、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
另外还有以下几个有兴趣可以去测试下，用例本文没有一一列举了：
1、组合索引，查询条件中没有在第一个字段使用组合的第一个字段，索引失效。 （//todo 还没有测试）
2、在索引字段上使用not，<>，!=等操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 （测试后感觉差别不大）
3、or语句前后没有同时使用索引。只有当or左右查询字段均为索引时，才会生效,否则索引失效。 （测试后感觉差别不大）
优化建议
1 尽量控制查询的字段个数
2 尽量控制查询的条数
3 尽量查询简单的字段
4 即使建立了索引，也必须注意合理使用，尽量避免出现导致索引失效的语句
