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

-- ----------------------------
-- 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';
复制代码

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

-- 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'
复制代码