MySQL索引的测试 (千万级数据) 以及特点总结｜周末学习

本文已参与 周末学习计划，点击查看详情

这是我参与更文挑战的第6天，活动详情查看： 更文挑战

通过存储过程插入百万，千万数据，来对比使用索引和没使用索引的区别（普通索引）

创建表

可以看到这里创建的索引类型都是 BTREE

-- ----------------------------
-- Table structure for mall
-- ----------------------------
DROP TABLE IF EXISTS `mall`;
CREATE TABLE `mall`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `categoryId` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `price` decimal(10, 2) NOT NULL,
  `type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `desc` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `img` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

百万级数据

在这里我们使用存储过程直接往表里插入一百万条数据

-- ------------ MYSQL8.0.17 插入百万数据
-- 获取数据库版本
SELECT VERSION();

-- ROUND( ) 四舍五入 第二个参数表示保留两位小数 ; RAND() 返回 0-1的小数
SELECT  ROUND(RAND()*1000,2) as 'test_name';

-- ---------------------------------创建生成随机字符串函数【START】------------------------------------------------------------------
-- 修改分隔符 避免被MySQL 解析
DELIMITER $$
-- 如果存在就删除
DROP FUNCTION IF EXISTS rand_str;
-- 创建函数名 rand_str  参数为返回的长度
create FUNCTION rand_str(strlen SMALLINT ) 
-- 返回值
RETURNS VARCHAR(255)

BEGIN
--  声明的字符串
    DECLARE randStr VARCHAR(255) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';
-- 声明 i 循环变量
    DECLARE i SMALLINT DEFAULT 0;
-- 声明返回变量
    DECLARE resultStr VARCHAR(255) DEFAULT '';
    WHILE i<strlen DO
        SET resultStr=CONCAT(SUBSTR(randStr,FLOOR(RAND()*LENGTH(randStr))+1,1),resultStr);
        SET i=i+1;
    END WHILE;
    RETURN resultStr;
END $$
DELIMITER ;

-- ------------------------------------创建生成随机字符串函数【END】---------------------------------------------------------------


-- 创建函数报错，可参考 # https://www.cnblogs.com/kerrycode/p/7641835.html
show variables like 'log_bin';
show variables like '%log_bin_trust_function_creators%';
set global log_bin_trust_function_creators=1;

-- 调用随机字符串函数
select rand_str(FLOOR(RAND()*20));

-- 创建存储过程  插入1 000 000 数据
DROP PROCEDURE IF EXISTS `add_mall`;

DELIMITER $$
CREATE PROCEDURE `add_mall` ( IN n INT )
BEGIN
	DECLARE	i INT UNSIGNED DEFAULT 0;
	WHILE
            i < n DO
            INSERT INTO mall ( categoryId, `name`, price, type, `desc`, `img` )
                VALUES
                ( 1, 'test', ROUND( RAND()* 1000, 2 ), rand_str ( FLOOR( RAND()* 20 )), 'test', 'test.jpg' ),
                ( 1, 'test', ROUND( RAND()* 1000, 2 ), rand_str ( FLOOR( RAND()* 20 )), 'test', 'test.jpg' ),
                ( 1, 'test', ROUND( RAND()* 1000, 2 ), rand_str ( FLOOR( RAND()* 20 )), 'test', 'test.jpg' ),
                ( 1, 'test', ROUND( RAND()* 1000, 2 ), rand_str ( FLOOR( RAND()* 20 )), 'test', 'test.jpg' ),
                ( 1, 'test', ROUND( RAND()* 1000, 2 ), rand_str ( FLOOR( RAND()* 20 )), 'test', 'test.jpg' ),
                ( 1, 'test', ROUND( RAND()* 1000, 2 ), rand_str ( FLOOR( RAND()* 20 )), 'test', 'test.jpg' ),
                ( 1, 'test', ROUND( RAND()* 1000, 2 ), rand_str ( FLOOR( RAND()* 20 )), 'test', 'test.jpg' ),
                ( 1, 'test', ROUND( RAND()* 1000, 2 ), rand_str ( FLOOR( RAND()* 20 )), 'test', 'test.jpg' ),
                ( 1, 'test', ROUND( RAND()* 1000, 2 ), rand_str ( FLOOR( RAND()* 20 )), 'test', 'test.jpg' ),
                ( 1, 'test', ROUND( RAND()* 1000, 2 ), rand_str ( FLOOR( RAND()* 20 )), 'test', 'test.jpg' );
            SET i = i + 1;
	END WHILE;
END $$
DELIMITER; 

-- 调用存储过程 100w 829.876s
CALL add_mall(100000);

-- 如果插入数据报错，可能需要调整该值大小
show VARIABLES LIKE '%max_allowd_packet%';

索引

先看看表里现在有多少条数据

不使用索引

-- 查询时不使用缓存
SELECT SQL_NO_CACHE * FROM mall WHERE type ='book';

使用索引

-- 添加索引
ALTER TABLE mall ADD INDEX idx_book(type);
-- 删除索引
DROP INDEX idx_book ON mall;

SELECT SQL_NO_CACHE * FROM mall WHERE type ='book';

可以看到在使用索引之后 这个查询简直是飞快，直接变成 1ms ，对比之前 656ms 的速度 👀

千万级数据

想要更快地插入可以修改引擎为MyISAM,使用jdbc等去批量插入，比如一次插入 5000 甚至更多就可以了。 在使用 innodb 时，可以将 autocommit 关闭，插入完数据再去建立索引（后知后觉🙃）。 下图是改用 MYISAM 后插入 100万 数据使用的时间。

-- 调用上面的存储过程再插入900w条数据。  这里用了两个多小时 。。  
CALL add_mall(900000); 

通过SELECT count(*) FROM mall;看到现在表里有1200万条数据

先简单介绍下 MySQL8 新特性的隐藏索引，一般创建索引比较耗时的（在数据量大的情况下），现在有了这个隐藏索引，我们测试起来就更方便了，实际应用中还可以避免误删索引。

-- mysql8新特性之隐藏索引
alter TABLE mall ALTER INDEX idx_book invisible;
-- 显示索引 
alter TABLE mall ALTER INDEX idx_book visible;
-- 简单测试SQL
SELECT SQL_NO_CACHE name,type,price,`desc`,img FROM mall WHERE type = 'book'

接下来我们试试这个MYISAM引擎下的查询耗时情况:

MYISAM

隐藏索引：

显示索引：

🛫

起飞！✔

Innodb下:

隐藏索引：

显示索引：

================ 简单测试结束 😄===================

可以看到使用索引和不使用索引的速度区别是非常大的！

索引的类型

• 主键索引
• 普通索引
• 唯一索引
• 组合索引
• 全文索引
• 空间索引

可以发现索引的类型是很多的，而且和这个存储引擎有关

下面介绍几个常见的存储引擎的索引特点😄

InnoDB 存储引擎的索引特点

Index Class	Index Type	Stores NULL VALUES	Permits Multiple NULL Values	IS NULL Scan Type	IS NOT NULL Scan Type
Primary key	BTREE	No	No	N/A	N/A
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
FULLTEXT	N/A	Yes	Yes	Table	Table

MyISAM 存储引擎的索引特点

Index Class	Index Type	Stores NULL VALUES	Permits Multiple NULL Values	IS NULL Scan Type	IS NOT NULL Scan Type
Primary key	BTREE	No	No	N/A	N/A
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
FULLTEXT	N/A	Yes	Yes	Table	Table
SPATIAL	N/A	No	No	N/A	N/A

Memory 存储引擎的索引特点

Index Class	Index Type	Stores NULL VALUES	Permits Multiple NULL Values	IS NULL Scan Type	IS NOT NULL Scan Type
Primary key	BTREE	No	No	N/A	N/A
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
Primary key	HASH	No	No	N/A	N/A
Unique	HASH	Yes	Yes	Index	Index
Key	HASH	Yes	Yes	Index	Index

欢迎关注，交个朋友呀！！ ( •̀ ω •́ )y

嘿嘿，我是4ye 咱们下期…… 很快再见！😄

如果你觉得本篇文章对你有所帮助的话，那拜托再点点赞支持一下呀😝

让我们开始这一场意外的相遇吧！~

欢迎留言！谢谢支持！ヾ(≧▽≦*)o 冲冲冲！！