（八）MySQL索引测试
MySQL索引测试，优化
1、准备
建表app_user：
CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
批量插入数据：100w
DROP FUNCTION IF EXISTS mock_data;
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE count INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < count DO
INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`) VALUES(CONCAT('用户', i), CONCAT(RANDOM_STR(10), '@163.com'), CONCAT('18', RANDOM_NUMBER(100000000, 999999999)), RANDOM_NUMBER(0,2), UUID(), RANDOM_NUMBER(0,100));
SET i = i + 1;
END WHILE;
RETURN i;
END
2、索引效率测试
测试字段等值（name）：
- 无索引
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.36 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.35 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.39 sec)
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 992759
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
- 创建索引
CREATE INDEX idx_app_user_name ON app_user(name);
- 测试普通索引
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ref
possible_keys: idx_app_user_name
key: idx_app_user_name
key_len: 203
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
测试范围查找（age）：
- 无索引
SELECT * FROM app_user WHERE age > 20;
790389 rows in set (1.55 sec)
790389 rows in set (1.42 sec)
790389 rows in set (1.34 sec)
- 创建索引
CREATE INDEX idx_app_user_name ON app_user(age);
3、索引失效
随着表的增长，where条件出来的数据太多，大于15%，使得索引失效（会导致CBO计算走索引花费大于走全表）
mysql> SHOW INDEX FROM app_user\G
*************************** 1. row ***************************
Table: app_user
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 991939
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: app_user
Non_unique: 1
Key_name: idx_app_user_age
Seq_in_index: 1
Column_name: age
Collation: A
Cardinality: 101
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM app_user WHERE age BETWEEN 50 AND 58\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: idx_app_user_age
key: NULL
key_len: NULL
ref: NULL
rows: 991939
filtered: 18.54
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM app_user WHERE age BETWEEN 50 AND 57\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: range
possible_keys: idx_app_user_age
key: idx_app_user_age
key_len: 5
ref: NULL
rows: 156852
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
单独引用复合索引里非第一位置的索引列
创建索引：
CREATE INDEX idx_app_user_age_phone_create_name ON app_user(`age`, `phone`, `name`);
-- 单独使用phone查询 索引无效
mysql> EXPLAIN SELECT * FROM app_user WHERE phone = '18CwxZaHS7H'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 991939
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
-- 按索引字段顺序排列。索引有效
mysql> EXPLAIN SELECT * FROM app_user WHERE age = 15 AND phone = '18CwxZaHS7H'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ref
possible_keys: idx_app_user_age,idx_app_user_age_phone_create_name
key: idx_app_user_age_phone_create_name
key_len: 88
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
对索引列运算，运算包括（+、-、*、/、！、<>、%），导致索引失效。
mysql> EXPLAIN SELECT * FROM app_user WHERE age = 15\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ref
possible_keys: idx_app_user_age,idx_app_user_age_phone_create_name
key: idx_app_user_age
key_len: 5
ref: const
rows: 18304
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM app_user WHERE (age - 5) = 15\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 991939
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
对索引应用内部函数，这种情况下应该建立基于函数的索引。
类型错误，如字段类型为varchar，where条件用number。
like的模糊查询以%开头，索引失效
mysql> CREATE INDEX idx_app_user_password ON app_user(`password`);
Query OK, 0 rows affected (2.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT * FROM app_user WHERE password LIKE 'aaa%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: range
possible_keys: idx_app_user_password
key: idx_app_user_password
key_len: 402
ref: NULL
rows: 236
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.01 sec)
mysql> EXPLAIN SELECT * FROM app_user WHERE password LIKE '%aaa%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 991939
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
覆盖索引
查询字段包含于索引字段
mysql> EXPLAIN SELECT password FROM app_user WHERE password LIKE '%aaa%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: index
possible_keys: NULL
key: idx_app_user_password
key_len: 402
ref: NULL
rows: 991939
filtered: 11.11
Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)
Like查询
使用索引覆盖嵌套查询似乎比直接查出来快。
SELECT * FROM app_user WHERE id IN (SELECT id FROM app_user WHERE password LIKE '%aaa%');
1403 rows in set (0.47 sec)
SELECT * FROM app_user WHERE password LIKE '%aaa%';
1403 rows in set (0.63 sec)
mysql> EXPLAIN SELECT * FROM app_user WHERE password LIKE '%aaa%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 991939
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM app_user WHERE id IN (SELECT id FROM app_user WHERE password LIKE '%aaa%')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: index
possible_keys: PRIMARY
key: idx_app_user_password
key_len: 402
ref: NULL
rows: 991939
filtered: 11.11
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: example_db.app_user.id
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
4、索引排序
-- 无索引
mysql> SELECT * FROM app_user WHERE email = 'QhGs5PK8bo@163.com' ORDER BY age;
105 rows in set (0.54 sec)
-- CREATE INDEX idx_app_user_email_age ON app_user(`email`, `age`);
mysql> SELECT * FROM app_user WHERE email = 'QhGs5PK8bo@163.com' ORDER BY age;
105 rows in set (0.00 sec)
- EXPLAIN
mysql> EXPLAIN SELECT * FROM app_user WHERE email = 'QhGs5PK8bo@163.com' ORDER BY age\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 991939
filtered: 10.00
Extra: Using where; Using filesort
1 row in set, 1 warning (0.01 sec)
mysql> EXPLAIN SELECT * FROM app_user WHERE email = 'QhGs5PK8bo@163.com' ORDER BY age\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ref
possible_keys: idx_app_user_email_age
key: idx_app_user_email_age
key_len: 202
ref: const
rows: 105
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
索引中排序字段之前的字段出现范围查询时，后面的字段失效：
- Using filesort：尽量优化掉
mysql> EXPLAIN SELECT * FROM app_user WHERE email LIKE 'QhGs5PK8bo@163.com%' ORDER BY age\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: range
possible_keys: idx_app_user_email_age
key: idx_app_user_email_age
key_len: 202
ref: NULL
rows: 105
filtered: 100.00
Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)
5、GROUP BY：
Using temporary;尽量优化掉
mysql> EXPLAIN SELECT COUNT(1), age FROM app_user WHERE email = 'QhGs5PK8bo@163.com' GROUP BY age\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 991939
filtered: 10.00
Extra: Using where; Using temporary; Using filesort
mysql> CREATE INDEX idx_app_user_email_age ON app_user(`email`, `age`);
Query OK, 0 rows affected (2.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT COUNT(1), age FROM app_user WHERE email = 'QhGs5PK8bo@163.com' GROUP BY age\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ref
possible_keys: idx_app_user_email_age
key: idx_app_user_email_age
key_len: 202
ref: const
rows: 105
filtered: 100.00
Extra: Using where; Using index
-- 索引前
mysql> SELECT COUNT(1), age FROM app_user WHERE email = 'QhGs5PK8bo@163.com' GROUP BY age;
64 rows in set (0.37 sec)
-- 索引后
mysql> SELECT COUNT(1), age FROM app_user WHERE email = 'QhGs5PK8bo@163.com' GROUP BY age;
64 rows in set (0.00 sec)
5、小表驱动大表测试
- IN 和 EXISTS ：
- IN：内存为小表
- EXISTS：外层为小表
