索引优化百万数据量

-- 新建一个查询，复制以下语句，执行即可。通过存储过程创建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%'; -- 走索引

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• SQL Server 检测是不是数字型数据的两种方法
• 用于PostgreSQL索引维护的有用查询
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 和利时MACS6通过dat文件实现任意时间范围与测点的历史数据提取与科研分析
• 深入理解spring框架：剖析多线程模式下数据库连接
• 索引的一些总结
• 数据可视化是如何帮助大型企业提高效率的？
• redis 数据库
• openpyxl模块--------------------------------重复数据

索引 数据索引 数据库 数据 数据结构 索引 结构 数据 数据 字段 索引 类型 函数oceanbase索引 数据库 索引 概念 数据库 数据 kingbasees索引 建议 数据库 索引 大会 数据mysql 索引 数据库 数据mongo 索引 不够 数据库 数据 索引 教程 数据 全文