1、压测
/data/app/mysql5728/bin/mysqlslap --defaults-file=/data/3307/conf/my.cnf --concurrency=100 --iterations=1 --create-schema='test' \--query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose#未添加索引压测结果BenchmarkRunning for engine rboseAverage number of seconds to run all queries: 873.674 secondsMinimum number of seconds to run all queries: 873.674 secondsMaximum number of seconds to run all queries: 873.674 secondsNumber of clients running queries: 100Average number of queries per client: 20#给k2字段添加索引alter table t100w add index idx_k2(k2);#添加索引压测结果BenchmarkRunning for engine rboseAverage number of seconds to run all queries: 0.288 secondsMinimum number of seconds to run all queries: 0.288 secondsMaximum number of seconds to run all queries: 0.288 secondsNumber of clients running queries: 100Average number of queries per client: 20
2、查询表的索引
desc test.t100w;
Key
PRI :主键 (聚簇索引)
MUL :辅助索引
UNK :唯一索引
show index from world.city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city | 0 | PRIMARY | 1 | ID | A | 4188 | NULL | NULL | | BTREE | | |
| city | 1 | CountryCode | 1 | CountryCode | A | 4188 | NULL | NULL | | BTREE | | |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3、创建索引
3.1、单列辅助索引
select * from test.t100w where k2='780P'
#优化方式: 给k2字段添加索引
语法:
alter table 表名 add index 索引名(列名);
alter table t100w add index idx_k2(k2);
3.2、创建联合索引
语法:
alter table 表名 add index 索引名(字段1,字段2,...);
alter table t100w add index idx_k1_num(k1,num);
3.3、创建前缀索引
desc world.city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
#需求:
需要给 city表 name列创建索引, 但是 name字段的数据类型时 char(35), 数据类型占用空间大, 枝节点就会增多, 从而索引的优化效果不会很好
#解决办法:
取 name的值前5个字符创建索引, 较少枝节点的数量, 提升优化效果
#生产中如何判断, 前缀索引的取值长度:
(1). 取 name字段去重复的行数
select count(distinct name) from city;
(2). 取 name字段5个前缀去重复的行数
select count(distinct(left(name,5))) from city;
(3). 用取前缀的行数 / 总行数, 不大于20%为合理范围
语法:
alter table city add 索引名(字段(前缀字符个数));
alter table city add index idx_n(name(5));
4、删除索引
语法:
alter table 表名 drop 索引名;
alter table city drop index idx_n;
注意:
创建索引, 删除索引, 属于DDL操作, 避开业务繁忙期间进行, 如果一定要再业务繁忙进行, 一定要使用 pt工具进行, 减少锁表时间
5、执行计划获取和分析
5.1、介绍
功能:
用于查看, 分析创建的索引, 编写的SQL语句合理性的分析
命令: 任选一个, 两个命令执行结果一样
explain
desc
原理:
在MySQL体系结构中, SQL层执行SQL之前会进行分析,选择合理的优化算法, 预估执行结果
5.2、使用方法
语法:
desc SQL语句;
desc select * from city where countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 3 | const | 363 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
5.3、执行计划信息介绍
table : 此次查询访问的表 ★
type : 索引查询的类型（ALL、index、range、ref、eq_ref、const(system)、NULL）
possible_keys : 可能会应用的索引 ★
key : 最终选择的索引
key_len : 索引覆盖长度, 主要是用来判断联合索引应用长度
rows : 需要扫描的行数
Extra : 额外信息 ★
5.4、type列信息详解
5.4.1、ALL: 全表扫描-没有创建索引
1. 查询条件没建立索引
#district字段没有创建索引
desc select * from world.city where district='shandong';
2. 有索引不走, SQL语句问题
#例如 like '%%', '%xxx', !=, not in
desc select * from city where countrycode!='CHN';
desc select * from city where countrycode not in ('CHN','USA');
desc select * from city where countrycode like '%CH%';
5.4.2、index 全索引扫描
#index: 走索引条件查询, 但是需要扫描整个B树结构才能拿到结果, 一般是由SQL语句查询条件造成的
desc select countrycode from city;
5.4.3、range 索引范围扫描
会受到: B树额外优化, 叶子节点双向指针
mysql> desc select * from city where id<10;
mysql> desc select * from city where countrycode like 'CH%';
以下两种查询, 大几率受不到叶子节点双向指针优化
mysql> desc select * from city where countrycode in ('CHN','USA');
mysql> desc select * from city where countrycode='CHN' or countrycode='USA';
建议: 如果查询列重复值少的话, 我们建议改写为 union all
desc
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';
5.4.4、ref 辅助索引等值查询
desc select * from city where countrycode='CHN';
5.4.5、eq_ref : 多表连接查询中、非驱动表的连接条件是主键或唯一键时
#多表连接查询时, 最好的结果
desc select city.name,country.name
from city
left join country
on city.countrycode=country.code where city.population<100;
5.4.6、const(system): 主键或唯一键等值查询
select * from city where id=1;
5.4.7、NULL、结果不存在
desc select * from city where id=1000000000000000;
5.5、possible_keys列说明
代表可能会用哪些索引
5.6、key_len
5.6.1、作用
用来判断联合索引应用的部分
例如:
alter table db01.tb01 add index idx(a,b,c);
我们希望应用联合索引的部分越多越好
5.6.2、如何计算
#列的key_len长度, 按照每列的最大预留长度来做的计算
key_len=a+b+c
create table t1 (
id int,
a int ,
b char(10),
c varchar(10))
#最大存储预留长度 (字节):
-------------------------------------------------------------------------------
数据类型 : 占用字节量 有not null 没有Not Null
-------------------------------------------------------------------------------
数字类型 :
tinyint : 1字节 1 1+1
int : 4字节 4 4+1
bigint : 8字节 8 8+1
-------------------------------------------------------------------------------
字符串类型:
utf8:
char(10) : 10*3字节 =30 30 30+1
varchar(10) : 10*3+2字节=32 32 32+1
-------------------------------------------------------------------------------
utf8mb4:
char(10) : 10*4字节 =40 40 40+1
varchar(10) : 10*4字节+2 =42 42 42+1
-------------------------------------------------------------------------------
#创建测试表
use test;
create table test (
id int not null primary key auto_increment,
a int not null , # 4
b int , # 5
c char(10) not null , # 40
d varchar(10), # 43
e varchar(10) not null # 42
)engine=innodb charset=utf8mb4;
#添加联合索引
alter table test add index idx(a,b,c,d,e);
#可能出现的情况
应用5个列: a,b,c,d,e
4+5+40+43+42=134
应用4个列:a,b,c,d
4+5+40+43=92
应用3个列: a,b,c
4+5+40=49
应用2个列: a,b
4+5=9
应用1个列: a
4
5.6.3、验证联合索引-key_len
mysql> desc select * from test where a=10 and b=10 and c='a' and d='a' and e='a';
mysql> desc select * from test where a=10 and b=10 and c='a' and d='a';
mysql> desc select * from test where a=10 and b=10 and c='a';
mysql> desc select * from test where a=10 and b=10;
5.7、联合索引细节
5.7.1、条件
联合索引应用要满足最左原则
1. 建立联合索引时, 选择重复值最少的列作为最左列, 因为索引排序会按照从左到右, 然后创建叶子节点
2. 使用联合索引时, 查询条件中, 必须包含最左列, 才有可能应用到联合索引, 否则是全索引扫描 (type: index)
5.7.2、联合索引不同覆盖场景
ALTER TABLE test.t100w ADD INDEX idx(num,k1,k2);
SHOW INDEX FROM test.t100w;
Column_name
num : 5
k1 : 9
k2 : 17
5.7.2.1、完全覆盖
#最左选择:
创建联合索引时, 靠左边的选择唯一值多的字段
desc select * from t100w where num=913759 and k1='ej' and k2='EFfg';
desc select * from t100w where k1='ej' and k2='EFfg' and num=913759;
#优化器会自动按照创建索引的顺寻, 对where条件的字段进行排序, 根据最左原则排序, 所以where条件的字段不用完全跟索引顺序完全一致
说明:
index idx (a,b,c);
where a = and b = and c =;
where b = and c = and a =;
#非等值的条件, 创建索引时放在最后一位, 也会走全列索引, 回表次数少
desc select * from t100w where num=913759 and k1='ej' and k2 in('EFfg','abcd');
desc select * from t100w where num=913759 and k1='ej' and k2 like 'EF%'
5.7.2.2、部分覆盖
index idx (a,b,c)
#只查询部分内容
where a = and b =
where b = and a =
where a =
#创建索引时, 非等值查询的字段没有在最后
where a = and b> < >= <= in like between and and c=
如何优化? 使它走全列索引 , 调整索引最左原则的顺序
index idx (num,k1,k2)
顺序改为:
index idx (num,k2,k1)
desc select * from t100w where num=913759 and k2='EFfg' and k1>'zz' ;
5.7.2.3、完全不覆盖
index idx (a,b,c)
where b c
where b
where c
#没有最左条件
5.8、extra 额外的信息
xtra 列如果结果显示: using filesort
#代表以下针对语句, 排序规则没有运用到索引, 因为创建索引时, 生成叶子之前是进行排序操作的
group by \ order by \ distinct \ union all
desc select * from world.city where countrycode='CHN' order by population;
ExtraUsing
index condition; Using file
#优化方案: 针对where 条件 + 排序子句时, 一定要创建联合索引, 给2列创建单列索引, 依然不会生效
底层原理: 根据where字段进行和排序字段一起排序, 而不是单独排序
#验证
1.分别给2列创建单例索引
desc select * from world.city where countrycode='CHN' order by population;
#显示排序没有走索引
ExtraUsing
index condition; Using file
2.创建联合索引
alter table world.city add index idx_cp(countrycode,population);
desc select * from world.city where countrycode='CHN' order by population;
#显示走索引
ExtraUsing
Using index condition
6、应用场景
数据库慢:
1. 应急性慢, 以前一致稳定, 突然一下很慢
排查方法:
show full processlist; 找出慢语句, 使用 explain分析SQL语句, 优化索引、改写语句
2. 间歇性慢
slowlog 找出慢语句, 使用 explain 分析SQL语句, 优化索引、改写语句
若有收获，就点个赞吧
0 人点赞
