CREATE TABLE `user_info_varchar20` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(20)   NOT NULL COMMENT '用戶名',
  `age` tinyint(4)  NOT NULL DEFAULT 0 COMMENT '年齡',
  `sex` tinyint(2) NOT NULL DEFAULT 0 COMMENT '性別 0：男 1： 女',
  `create_time` datetime NOT NULL DEFAULT NOW() COMMENT '創(chuàng)建時間',
  `udpate_time` datetime NOT NULL DEFAULT NOW() COMMENT '更新時間',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) COMMENT 'name索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶賬戶';

CREATE TABLE `user_info_varchar200` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(200)   NOT NULL COMMENT '用戶名',
  `age` tinyint(4)  NOT NULL DEFAULT 0 COMMENT '年齡',
  `sex` tinyint(2) NOT NULL DEFAULT 0 COMMENT '性別 0：男 1： 女',
  `create_time` datetime NOT NULL DEFAULT NOW() COMMENT '創(chuàng)建時間',
  `udpate_time` datetime NOT NULL DEFAULT NOW() COMMENT '更新時間',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) COMMENT 'name索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶賬戶';

CREATE DEFINER=`root`@`localhost` PROCEDURE `insertTestData2`(IN total INT)
BEGIN
    DECLARE i INT DEFAULT 1;
		DECLARE batch_size INT DEFAULT 500;
		DECLARE var_offset INT DEFAULT 1;
		DECLARE var_limit INT;
		DECLARE var_batch_values TEXT;
		DECLARE var_age TINYINT;
		#DECLARE var_sex TINYINT;
		
		WHILE i < total+1 DO
		
			SET var_limit=LEAST(var_offset+batch_size, total+1);
			
			SET var_batch_values='';
		  WHILE i < var_limit  DO
			
				set var_age=FLOOR(RAND() * (100 - 18) + 18);
				#set var_sex=IF(RAND() < 0.5, 0, 1);
				
				IF i = (var_limit-1) THEN
						#SET var_batch_values = CONCAT(var_batch_values, CONCAT('(\'', CONCAT('測試用戶', i), '\', ', var_age, ', ', var_sex ,', NOW(), NOW())'));
						SET var_batch_values = CONCAT(var_batch_values, CONCAT('(\'', CONCAT('測試用戶', i), '\', ', var_age,', 0, NOW(), NOW())'));
				ELSE
				    #SET var_batch_values = CONCAT(var_batch_values, CONCAT('(\'', CONCAT('測試用戶', i), '\', ', var_age, ', ', var_sex ,', NOW(), NOW()),'));
						SET var_batch_values = CONCAT(var_batch_values, CONCAT('(\'', CONCAT('測試用戶', i), '\', ', var_age,', 0, NOW(), NOW()),'));
				END IF;
				SET i=i+1;
			END WHILE;
			
			SET @sql = CONCAT('INSERT INTO user_info_varchar20(name, age, sex, create_time, udpate_time) VALUES ', var_batch_values);
			PREPARE stmt FROM @sql;
      EXECUTE stmt;
			
			SET @sql = CONCAT('INSERT INTO user_info_varchar200(name, age, sex, create_time, udpate_time) VALUES ', var_batch_values);
			PREPARE stmt FROM @sql;
      EXECUTE stmt;
			
			SET var_offset=var_limit;
		END WHILE;
END

SELECT
    table_schema AS "數(shù)據(jù)庫",
    table_name AS "表名",
    table_rows AS "記錄數(shù)",
    TRUNCATE ( data_length / 1024 / 1024, 4)  AS "數(shù)據(jù)容量（MB）",
    TRUNCATE ( index_length / 1024 / 1024, 4)  AS "索引容量（MB）" 
FROM
    information_schema.TABLES 
WHERE
    table_schema = 'custom_db' 
and (TABLE_NAME = 'user_info_varchar20' or TABLE_NAME = 'user_info_varchar200')
ORDER BY
    data_length DESC,
    index_length DESC;

ANALYZE TABLE user_info_varchar20;
ANALYZE TABLE user_info_varchar200;

SELECT
    table_schema AS "數(shù)據(jù)庫",
    table_name AS "表名",
    table_rows AS "記錄數(shù)",
    TRUNCATE ( data_length / 1024 / 1024, 4)  AS "數(shù)據(jù)容量（MB）",
    TRUNCATE ( index_length / 1024 / 1024, 4)  AS "索引容量（MB）" 
FROM
    information_schema.TABLES 
WHERE
    table_schema = 'custom_db' 
and (TABLE_NAME = 'user_info_varchar20' or TABLE_NAME = 'user_info_varchar200')
ORDER BY
    data_length DESC,
    index_length DESC;

 show variables like '%profiling%';

 set profiling = 1;  開啟

-- 條件為索引字段查詢		
select * from user_info_varchar20 where name = '測試用戶800000';
select * from user_info_varchar200 where name = '測試用戶800000';

-- 排序字段為索引同時搜索字段為索引覆蓋(深分頁)
select name from user_info_varchar20 order by name LIMIT 999995, 5;
select name from user_info_varchar200 order by name LIMIT 999995, 5;


-- 排序字段為索引同時搜索字段為普通字段（深分頁）
select * from user_info_varchar20 order by name LIMIT 999995, 5;
select * from user_info_varchar200 order by name LIMIT 999995, 5;

-- 索引排序同時深分頁
select * from user_info_varchar20 order by name LIMIT 999995, 5;
select * from user_info_varchar200 order by name LIMIT 999995, 5;

/* 打開optimizer_trace，只對本線程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
 
/* 執(zhí)行語句 */
select name,age from user_info_varchar200 order by name LIMIT 999995, 5;
 
/* 查看 OPTIMIZER_TRACE 輸出 */
SELECT * from information_schema.OPTIMIZER_TRACE;
 
/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 計(jì)算Innodb_rows_read差值 */
SELECT @b-@a;