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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 创建表可以看到这里创建的索引类型都是 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_gen

创建表


可以看到这里创建的索引类型都是 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




相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
30天前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
157 9
|
2月前
|
安全 关系型数据库 测试技术
学习Python Web开发的安全测试需要具备哪些知识?
学习Python Web开发的安全测试需要具备哪些知识?
41 4
|
13天前
|
SQL 存储 关系型数据库
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
74 22
 MySQL秘籍之索引与查询优化实战指南
|
14天前
|
存储 关系型数据库 MySQL
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
64 10
|
1月前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
78 18
|
27天前
|
存储 Oracle 关系型数据库
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
56 8
|
1月前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
62 7
|
1月前
|
缓存 关系型数据库 MySQL
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
92 5
|
1月前
|
存储 关系型数据库 MySQL
mysql怎么查询longblob类型数据的大小
通过本文的介绍,希望您能深入理解如何查询MySQL中 `LONG BLOB`类型数据的大小,并结合优化技术提升查询性能,以满足实际业务需求。
112 6
|
1月前
|
存储 关系型数据库 MySQL
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
138 7