MySql数据库索引优化（百万级数据表的⽣成脚本）
⼀、概述
MySQL官⽅对索引的定义为：索引(Index)是帮助MySQL⾼效获取数据的数据结构。
索引的本质：索引是数据结构，索引可以提⾼查询效率（类⽐英语新华字典，如果我们要查询MySQL这个单词，⾸先我们需要在⽬录（索引）定位到M，然后在定位到y，以此类推到SQL）。
如果没有索引，就需要从A到Z，去遍历的查⼀遍，直到到我们需要的，⼀个⼀个和直接根据⽬录定位到数据，效率将会相差很⼤。这就是索引的妙⽤。
索引的优势：insert语句字段顺序
类似⼤学图书馆书⽬索引，提⾼数据检索效率，降低数据库IO成本
通过索引列对数据进⾏排序，降低数据排序成本，降低了CPU消耗
索引的劣势：
实际上索引也是⼀张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占⽤空
间的。
虽然索引⼤⼤提⾼了询速度，同时却会降低更新表的速度，如果对表INSERT,UPDATE和DELETE。因为更新表时，MySQL不仅要不存数据，还要保存⼀下索引⽂件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
索引只是提⾼效率的⼀个因素，如果你的MySQL有⼤数据量的表，就需要花时间研究建⽴优秀的索引，或优化查询语句。
索引分类：
单值索引：即⼀个索引只包含单个列，⼀个表可以有多个单列索引
唯⼀索引：索引列的值必须唯⼀，但允许有空值
复合索引：即⼀个索引包含多个列
索引创建⽅式：
创建⼀：create[unique]index indexName on tableName (columnName (length));
创建⼆：alter tableName add[unique]index[indexName]on(columnName (length))
查看：SHOW INDEX FROM table_name;
删除：DROP INDEX[indexName]ON mytable;
哪些情况需要建索引
主键⾃动建⽴唯⼀索引
频繁作为查询的条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建⽴索引
频繁更新的字段不适合创建索引：因为每次更新不单单是更新了记录还会更新索引，加重IO负担
Where条件⾥⽤不到的字段不创建索引
单间/组合索引的选择问题（在⾼并发下倾向创建组合索引）
查询中排序的字段，若通过索引去访问将⼤⼤提⾼排序的速度
查询中统计或者分组字段
哪些不适合建索引
表记录太少
经常增删改的表
数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建⽴索引。注意，如果某个数据列包含许多重复的内容，为它建⽴索引就没有太⼤的实际效果。
⼆、数据的准备（MySQL⽣成百万条测试数据）
整体思路:
1、准备内存表和普通表
2、准备⽣成随机数的函数以及插⼊语句的存储过程
3、执⾏
1、创建表结构（本⽂基于Navicat Premium）
-- 新建⼀个查询，复制以下语句，执⾏即可。
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;
2、创建⽣成n个随机数字的函数
如果报错：
1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in
可以先执⾏
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 DEFAULT0;
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 DEFAULT0;
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;
3、创建插⼊内存表数据的存储过程
# 创建插⼊内存表数据存储过程⼊参n是多少就插⼊多少条数据
DELIMITER $$
CREATE PROCEDURE`add_test_user_memory`(IN n int)
BEGIN
DECLARE i INT DEFAULT1;
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(R AND()*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 DEFAULT1;
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;
执⾏以上语句后， 准备⼯作即完成。⽣成的结构如图：
4、执⾏存储过程即可快速⽣成数据：
#循环100次，每次⽣成10000条数据总共⽣成⼀百万条数据
CALL add_test_user_memory_to_outside(100,10000);
以上100万条数据⼤概需要四五分钟（有时更久）的时间，请耐⼼等待。如果需要更多的数据量，只需要调整⼊参即可。
三、索引基本学习
1、上述表结构创建，并插⼊数据后，由于创表时新增了⼀个索引KEY idx_user_id (user_id)，所以加
上主键id会⾃动创建的⼀个索引，⼀共有两个。
-- 查看索引情况
SHOW INDEX FROM test_user
2、现在通过没有索引的字段phone、lan_id、region_id进⾏查询，结果查询效率如图：
SELECT count(1)from test_user a;
结果：2020000
--执⾏没有索引情况下的查询语句，结果耗时时间: 0.905ms  SQL_NO_CACHE是指不适⽤缓存
SELECT SQL_NO_CACHE *from test_user a where a.phone='136********'and a.lan_id=ion_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='136********'and a.lan_id=ion_id=47;
四、索引特点及优化思路
1、索引的⼀个特点：最左前缀法则
如果建⽴的是复合索引，索引的顺序要按照建⽴时的顺序。如a,b,c三个字段构成符合索引，那么where条件⼀定要按照abc的顺序编写，才能最⼤效率⽤到此索引。（桥头-桥⾝-桥尾 ： 前⾯中断，即后⾯⽆效，⽆法通⾏）

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。