索引优化百万数据量

-- 新建一个查询，复制以下语句，执行即可。通过存储过程创建100万数据
CREATE TABLE `test_user_memory` (
`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=MEMORY DEFAULT CHARSET=utf8mb4;

 

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 DEFAULT CHARSET=utf8mb4;

 

set global log_bin_trust_function_creators=1;

#生成n个随机数字
DELIMITER $$
CREATE FUNCTION randNum(n int) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str varchar(20) DEFAULT '0123456789';
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()10 ),1));
SET i = i +1;
END WHILE;
RETURN return_str;
END $$
DELIMITER;

 

 

#生成随机手机号码
# 定义常用的手机头 130 131 132 133 134 135 136 137 138 139 186 187 189 151 157
#SET starts = 1+floor(rand()15)4; 截取字符串的开始是从 1、5、9、13 ...开始的。floor(rand()15)的取值范围是0~14
#SET head = substring(bodys,starts,3);在字符串bodys中从starts位置截取三位

DELIMITER $$
CREATE FUNCTION generatePhone() RETURNS varchar(20)
BEGIN
DECLARE head char(3);
DECLARE phone varchar(20);
DECLARE bodys varchar(100) default 130 131 132 133 134 135 136 137 138 139 186 187 189 151 157;
DECLARE starts int;
SET starts = 1+floor(rand()15)4;
SET head = trim(substring(bodys,starts,3));
SET phone = trim(concat(head,randNum(8)));
RETURN phone;
END $$
DELIMITER ;

 

 

#创建随机字符串和随机时间的函数
DELIMITER $$
CREATE FUNCTION `randStr`(n INT) RETURNS varchar(255) CHARSET utf8mb4
DETERMINISTIC
BEGIN
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
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() 62), 1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END$$
DELIMITER;

 

# 创建插入内存表数据存储过程 入参n是多少就插入多少条数据
DELIMITER $$
CREATE PROCEDURE `add_test_user_memory`(IN n int)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i = n) DO
INSERT INTO test_user_memory (user_id, user_name, phone, lan_id,region_id, create_time) VALUES (uuid(), randStr(20), generatePhone(), FLOOR(RAND() 1000), FLOOR(RAND() 100), NOW());
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;

 

#循环从内存表获取数据插入普通表
#参数描述 n表示循环调用几次；count表示每次插入内存表和普通表的数据量
DELIMITER $$
CREATE PROCEDURE `add_test_user_memory_to_outside`(IN n int, IN count int)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i = n) DO
CALL add_test_user_memory(count);
INSERT INTO test_user SELECT FROM test_user_memory;
delete from test_user_memory;
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;

CALL add_test_user_memory_to_outside(100,10000);

执行完毕，test_user表插入了100万数据！

如果建立的是复合索引，索引的顺序要按照建立时的顺序。如a,b,c三个字段构成符合索引，那么where条件一定要按照abc的顺序编写，才能最大效率用到此索引。（桥头-桥身-桥尾 ： 前面中断，即后面无效，无法通行）
alter table test_user add index index_phone_landId_regionId(phone,lan_id,region_id);
-- 再次执行上述的查询语句 结果 耗时: 0.003ms远远小于之前的0.905
SELECT SQL_NO_CACHE * from test_user a where a.phone='13647715321' and a.lan_id=756 and a.region_id= 47;

-- 执行没有索引情况下的查询语句，结果耗时 时间: 0.905ms SQL_NO_CACHE是指不适用缓存
SELECT SQL_NO_CACHE * from test_user a where a.phone='13647715321' and a.lan_id=756 and a.region_id= 47;

-- 现在对phone、lan_id、region_id三个字段添加一个复合索引
alter table test_user add index index_phone_landId_regionId(phone,lan_id,region_id);
-- 再次执行上述的查询语句 结果 耗时: 0.003ms远远小于之前的0.905
SELECT SQL_NO_CACHE * from test_user a where a.phone='13647715321' and a.lan_id=756 and a.region_id= 47;

-- 可以用EXPLAIN来对语句进行查看。
-- key_len 是在不损失精度的基础上，越小越好 （粗略值）
-- ref 是指用了三个常量去查找
-- rows 是指查找了多少行，也是越小越好
EXPLAIN SELECT SQL_NO_CACHE * from test_user a where a.phone='13647715321' and a.lan_id=756 and a.region_id= 47;

-- 也可以直接查看sql执行的时间来对索引进行辨别。
-- 正常的a、b、c顺序， 时间: 0.001ms
EXPLAIN SELECT SQL_NO_CACHE * from test_user a where a.phone='13647715321' and a.lan_id=756 and a.region_id= 47;

-- b、c 没有用a,破坏了索引，用EXPLAIN可以看到没有用到索引 时间: 1.389ms
EXPLAIN SELECT SQL_NO_CACHE * from test_user a where a.lan_id=756 and a.region_id= 47;

-- a、c 用到了索引，只是效率没有最大化，可以用EXPLAIN看到，只有a,即phone字段用到了。 另外两个字段索引无效。 时间: 0.001ms
EXPLAIN SELECT SQL_NO_CACHE * from test_user a where a.phone='13647715321' and a.region_id= 47;

-- a、b 的话一样，用到了2/3 ，没有最大化使用 时间: 0.001ms
EXPLAIN SELECT SQL_NO_CACHE * from test_user a where a.phone='13647715321' and a.lan_id=756;

-- b、c 由于没有'桥头'，所以没有索引的使用。 时间: 0.850ms
EXPLAIN SELECT SQL_NO_CACHE * from test_user a where a.lan_id=756 and a.region_id= 47;

-- 索引的使用与where语句中字段出现的顺序无关，只与是否使用到索引相关字段有关。如下的两个语句都会使用到索引。
EXPLAIN SELECT SQL_NO_CACHE * from test_user a where a.phone='13923169519' and a.lan_id=262;
EXPLAIN SELECT SQL_NO_CACHE * from test_user a where a.lan_id=262 and a.phone='13923169519';

-- 2、以下用法会导致索引失效
-- 计算，如：+、-、*、/、!=、<>、is null、is not null、or
-- 函数，如：sum()、round()等等
-- 手动/自动类型转换，如：id = “1”，本来是数字，给写成字符串了
EXPLAIN SELECT SQL_NO_CACHE a.* from test_user a where a.phone='13647715321' and a.lan_id=756 and a.region_id= 47;
-- 改一个！= 直接没有索引 时间: 1.538ms
EXPLAIN SELECT SQL_NO_CACHE a.* from test_user a where a.phone!='13647715321' and a.lan_id=756 and a.region_id= 47;

3、索引不要放在范围查询右边
比如复合索引：a->b->c，当 where a=“xxx” and b>10 and c=“xxx”，这时候只能用到 a 和 b，c 用不到索引，
因为在范围之后索引都失效（和 B+树结构有关）

4、减少 select * 的使用

5、-- like 模糊搜索
-- 失效情况
-- like “%张三%”
-- like “%张三”
-- 解决方案
-- 使用覆盖索引，即 like 字段是 select 的查询字段。 type=index
-- 如：select name from table where name like “%张三%”;
-- 使用 like “张三%”
EXPLAIN SELECT SQL_NO_CACHE a.* from test_user a where a.phone like '133%'; -- 走索引
EXPLAIN SELECT SQL_NO_CACHE a.* from test_user a where a.phone like '1%'; -- 不走索引
EXPLAIN SELECT SQL_NO_CACHE a.* from test_user a where a.phone like '%133%'; -- 不走索引
EXPLAIN SELECT SQL_NO_CACHE a.* from test_user a where a.phone like '%133'; -- 不走索引
EXPLAIN SELECT SQL_NO_CACHE a.phone from test_user a where a.phone like '%133%'; -- 走索引