Day06 - MySQL索引优化

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# 进行测试数据恢复操作：
mysql> source ~/t100w_oldboy.sql
# 进行数据库程序服务压测：
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --quer y="select * from oldboy.t100w where k2='VWlm'"
engine=innodb --number-of-queries=2000 -uroot -p123456 -h192.168.30.101 -verbose
-- concurrency=100  模拟同时100会话连接；
-- iterations=1 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次
-- create-schema='test'  指定操作的数据库信息；
-- quer y="select * from test.100w where k2='780P'"  指定压测过程具体执行了什么语句操作
-- number-of-queries=2000 指定一共做了多少次查询，总的测试查询次数(并发客户数×每客户查询次数)

1

mysql> alter table oldboy.t100w add index idx_k2(k2);

1
2
3

mysql> use world;
mysql> desc city;
-- 查询表结构信息，获取索引配置

1
2
3

mysql> use world;
mysql> show index from city;
-- 查询表索引信息，获取索引详细信息

1
2
3
4
5
6

mysql> ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
-- 创建主键索引
mysql> alter table city add index idx_name(name);
-- 创建辅助索引
mysql> ALTER TABLE `table_name` ADD UNIQUE (`column`)
-- 创建唯一键索引

1

mysql> alter table city add index ix_na_po(name,population);

1

mysql> alter table city add index ix_n(name(10));

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# 删除索引信息（一般索引）
alter table 表名 drop index 索引名;
mysql> alter table t100w drop index idx_name;
-- 删除辅助索引
mysql> alter table t100w drop index oldboy;
-- 删除联合索引
mysql> alter table t100w drop index id;
-- 删除唯一索引
# 删除索引信息（聚簇索引）
alter table 表名 drop primar y key;
mysql> alter table t100w drop primar y key;
-- 删除聚簇索引

1
2
3

explain select * from oldboy.t100w where k2='VWlm';
或者
desc select * from oldboy.t100w where k2='VWlm';

1
2
3
4
5
6
7

# 联合索引设置
alter table t1 add index id_a_b_c(a列,b列,c列);
# 联合索引应用
select * from t1 where a=xx and b=xx and c=xx
100行 -- 回表100
50行  -- 回表50
10行  -- 回表10

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 常见测试数据表
use test;
create table keylen (
id int not null primar y key auto_increment,
k1 int not null,
k2 char(20),
k3 varchar(30) not null,
k4 varchar(10)
) charset=utf8mb4;
# 设置表中列索引信息
alter table keylen add index idx(k1,k2,k3,k4);

1
2
3
4
5
6
7
8

# key_len计算思路
k1 = 4
k2 = 4 * 20 +1 = 81
k3 = 4 * 30 +2 = 122
k4 = 4 * 10 +2 + 1 = 43
sum = 4 + 81 + 122 + 43 = 250
# 进行校验结果
desc select * from keylen where k1=1 and k2='a' and k3='a' and k4='a';

1
2
3
4
5
6

mysql> use oldboy;
mysql> show index from t100w;
mysql> alter table t100w drop index idx_k2;
mysql> show index from t100w;
mysql> desc t100w;
-- 删除原有表中所有索引信息；

1
2
3

# 在不满足最左原则创建联合索引
mysql> alter table t100w add index idx(num,k1,k2);
-- 此时key_len的最大预留长度：4+1 + 2*4+1 + 4*4+1 = 31

1

desc select * from t100w where num=913759 and k1='ej' and k2='EFfg';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

mysql> select num,count(*) from t100w group by num having count(*)>1 order by count(*) desc limit 3;
+--------+----------+
| num    | count(*) |
+--------+----------+
| 339934 |       14    |
| 614847 |       12    |
|  65003 |       12     |
+--------+----------+
3 rows in set (0.54 sec)
mysql> select * from t100w where num='339934';
+---------+--------+------+------+---------------------+
| id      | num    | k1   | k2   | dt                  |
+---------+--------+------+------+---------------------+
|  959036 | 339934 | 7X   | jkwx | 2019-08-12 11:52:47 |
|    4277 | 339934 | Ba   | NOpq | 2019-08-12 11:41:21 |
|  185265 | 339934 | BO   | 78Z0 | 2019-08-12 11:43:21 |
|  965745 | 339934 | eL   | Z0wx | 2019-08-12 11:52:52 |
|  987825 | 339934 | fs   | nomn | 2019-08-12 11:53:07 |
|  308385 | 339934 | g1   | deRS | 2019-08-12 11:44:44 |
|  223157 | 339934 | ku   | mn89 | 2019-08-12 11:43:46 |
|  138236 | 339934 | or   | UV45 | 2019-08-12 11:42:51 |
|  765105 | 339934 | rJ   | 89qr | 2019-08-12 11:50:26 |
|  478517 | 339934 | t8   | abef | 2019-08-12 11:46:49 |
|  107745 | 339934 | tZ   | noKL | 2019-08-12 11:42:31 |
|  503036 | 339934 | v3   | BCGH | 2019-08-12 11:47:07 |
|  596385 | 339934 | Yb   | PQqr | 2019-08-12 11:48:17 |
| 1000001 | 339934 | yb   | pqqs | 2022-11-12 12:41:59 |
+---------+--------+------+------+---------------------+

1
2

mysql> insert into t100w values(1000001,339934,'yb','pqqs',now());
mysql> select * from t100w where num='339934';

1
2

mysql> select * from t100w where num=339934 and k1='yb' and k2 > 'PQqr';
mysql> desc  select * from t100w where num=339934 and k1='yb' and k2 > 'PQqr';

1

mysql> desc select * from t100w where num=339934;

1
2
3

mysql> show variables like '%switch%';
mysql> set global optimizer_switch='index_condition_pushdown=off ';
-- 实现测试练习完，需要恢复开启（操作可以省略）

1
2

mysql > select * from t100w where num=339934 and k1<'yb' and k2='nokl';
mysql > desc  select * from t100w where num=339934 and k1<'yb' and k2='nokl';

1

mysql> desc select * from t100w where num=339934  and k2='ej';

1

mysql> desc select * from t100w;

1

mysql> desc select * from t100w where num<339934 ;

1

mysql> desc select * from t100w where k2='ej';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# 创建索引情况
mysql> alter table t100w add index idx(num,k1,k2);
# 执行压力测试命令
[root@xiaoQ-01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --quer y="select * from t100w where
num=339934 and k1='yb' and k2='PQqr';" engine=innodb --number-of-queries=200000 -uroot -p123456 -h192.168.30.101 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 20.197 seconds
Minimum number of seconds to run all queries: 20.197 seconds
Maximum number of seconds to run all queries: 20.197 seconds
Number of clients running queries: 100
Average number of queries per client: 2000

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 调整索引情况
mysql> alter table t100w drop index idx;
mysql> alter table t100w add index idx(k1,k2,num);
# 执行压力测试命令
[root@xiaoQ-01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --quer y="select * from t100w where
num=339934 and k1='yb' and k2='PQqr';" engine=innodb --number-of-queries=200000 -uroot -p123456 -h192.168.30.101 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 20.494 seconds
Minimum number of seconds to run all queries: 20.494 seconds
Maximum number of seconds to run all queries: 20.494 seconds
Number of clients running queries: 100
Average number of queries per client: 2000

1
2
3
4
5
6
7

# 查看指定表索引信息
mysql> use world;
mysql> show index from city;
# 删除无用索引信息
mysql> alter table city drop index ix_na_po;
mysql> alter table city drop index ix_n;
mysql> alter table city drop index idx;

1
2
3

mysql> select * from city where countr ycode='CHN';
mysql> desc select * from city where countr ycode='CHN';
-- 获取默认查询执行计划情况

1
2

# 模拟情况一：利用order by实现排序
mysql> desc select * from city where countr ycode='CHN' order by population;

1
2

# 错误设想创建索引：因为本身索引构建过程就存在自动排序问题
alter table city add index idx(population)

1
2

# 正确优化处理方式：创建联合索引
mysql> alter table city add index idx1(countr ycode,population);

1
2
3

# 模拟情况二：利用group by实现排序
mysql> desc select district,count(*) from city where countr ycode='CHN' group by district;
mysql> desc select district,count(*) from city where countr ycode='CHN' group by district order by sum(population);

1
2

select * from t1;
select * from t1 where id=1001 or 1=1;   -- 执行SQL注入语句问题

1
2
3

innodb_index_stats
innodb_table_stats
-- mysql库中的相应表

1
2

mysql > analyze table world.city;
-- 表示立即更新过久的统计信息（也可以将索引删除重建）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 错误举例：
select * from test where id-1=9;
# 正确举例：
select * from test where id=10;
-- 总之尽量避免条件信息出现 算数运算  函数运算  子查询
# 子查询补充：
# 子查询指一个查询语句嵌套在另一个查询语句内部的查询
# SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据
# 查询中国城市人口大于北京人口数量的城市信息
查询01：查询中国 北京的人口数量
select population from city where countr ycode='CHN' and name='Peking';
查询02：查询中国 基于北京人口数量 大于北京人口数量城市
select * from city where countr ycode='CHN'  and population > 'xxx'
mysql> select * from city where Countr yCode='CHN' and population > (select population from city where Countr yCode='CHN' and name='Peking');

1
2
3
4
5
6
7
8
9

# 创建测试数据表
mysql> create table test (id int,name varchar(20),telno char(11));
mysql> insert into test values (1,'a','110'),(2,'b','123'),(3,'c','120'),(4,'d','119'),(5,'e','130');
# 创建索引信息
mysql> alter table test add index idx(telno);
# 查询数据信息
mysql> select * from test where telno='110';
mysql> select * from test where telno=110;
-- 上面两条语句都能查看到结果信息，但是有一条语句是没有走索引的

1
2

<> , not in , like "%_"
-- 应用以上特殊符号信息，也会导致辅助索引失效

1
2
3
4

# 在创建索引或修改索引时，可以设置不可见或可见索引（默认）
mysql> alter table test alter index idx invisible;
mysql> alter table test add index idx1(name) invisible;
-- 在做批量数据导入时，辅助索引信息可以设置为不可见，优化器就不会加载识别索引信息

1
2
3
4
5
6
7
8
9

# 官方解释说明
idx(a,b,c)
-- 创建a b c 索引列 并按照从小到大排序
desc select * from where xx order by a,b desc,c   索引全覆盖
order by a,b desc,c
-- 由于排序中出现了逆向排序，所以只有a列会走索引，查询b和c还是会再进行排序处理，不会利用索引排序
# 最新版数据库索引创建
idx(a,b desc,c)
-- 可以灵活调整索引排序方式，应对不同的查询条件，从而避免排序问题对CPU资源的消耗

1
2
3
4
5
6
7

mysql> show variables like 'innodb_adaptive_hash_index';
+--------------------------------------+-------+
| Variable_name                           | Value |
+--------------------------------------+-------+
| innodb_adaptive_hash_index  | ON    |
+--------------------------------------+-------+
1 row in set (0.00 sec)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

mysql> show variables like '%change_buffer%';
+-----------------------------------------+-------+
| Variable_name                                | Value |
+-----------------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
| innodb_change_buffering              | all   |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)
--all：    默认值。开启buffer inserts、delete-marking operations、purges
--none： 不开启change buffer

1
2

idx(a,b,c)
where a=10 and b like '%x%' and c=z

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

mysql> show variables like '%switch%';
mysql> set global optimizer_switch='index_condition_pushdown=off ';
-- 实现测试练习完，需要恢复开启（操作可以省略）
# 测试练习
mysql> select * from t100w where k1='qj' and k2 like '%v%';
mysql> desc select * from t100w where k1='qj' and k2 like '%v%';
-- extra列显示using index condition信息，表示应用了索引下推
# 进行压测
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --quer y="select * from t100w where k1='qj' and k2 like '%v%"
engine=innodb --number-of-queries=20000 -uroot -p123456 -h192.168.30.101 -verbose

1
2
3

mysql > set optimizer_switch='mrr=on';
mysql > set global optimizer_switch='mrr_cost_based=off ';
Quer y OK, 0 rows affected (0.06 sec)