MySQL元数据获取基础笔记day06

一、辅助索引细分

1.1单列辅助索引

select * from t1 where name=""

1.2联合索引

select * from t1 where a and b and c

唯一索引

二、索引树高度（越低越好）

2.1表的数据量级大

分区表
分库分表（分布式架构）

2.2索引键值的长度

1.尽可能选择列值短的列创建索引。
2.采用前缀索引。

2.3数据类型选择（选择合适）

varchar和char
enum

三、索引管理

3.1压力测试准备

3.2索引命令操作

---查询索引

use school;
desc student;

Key这一列显示的就是索引

Key:PRI（主键）,UNI(唯一索引)，MUL(辅助索引)

show index from student\G;

---创建索引??

alrer table student add index idx_name(sname);
desc student;

---创建联合索引???

alter table student add index idx_sname_sage_ssex(sname,sage,ssex);

---创建前缀索引??

alter table student add index  idx(sname(5));

---创建唯一索引

 #先添加一列微信号创建个环境
 alter table student   add WeChat char(11)   not null;

alter table student add   unique index idx_tel(Wechat);

---删除索引

alter table student drop index idx;
后面不需要跟列名，只需要索引名即可。

3.3

压力测试准备（mysql自带功能）

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='VWtu'" engine=innodb --number-of-queries=2000 -uroot -p123456 -verbose

建个索引之后会发现速度明显提升。

4 explain(desc) ???

explain select  *  from t100w where k2='VWtu';
or
desc select * from text.t100w where k2='VWtu';

作用：抓取优化器优化过的执行计划。

4.1执行计划的分析

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

重点关注:
table:以上SQL语句涉及到的表???
type：查询的类型（全表扫描（ALL），索引扫描，查不到数据（NULL））?????
possible_keys:可能会用到的索引 ????
key:使用到的索引
key_len：索引的覆盖长度 ?????
Extra:额外的信息 ????

4.2 type详细说明?????

ALL:全表扫描,不会走任何索引

（1）查询条件，没建索引
（2）建了索引不走 （where条件中使用了!=）

desc select * from t100w where k2!='asdf';
desc select * from t100w where k2 like '%aa%'
desc select * from t100w where k2 not in ('asda','asas');
desc select * from t100w;

index全索引扫描

desc select k2 from t100w;顺序扫描

range 索引范围查询

==========从range开始，我们才认为索引是有价值的。==========
辅助索引

in () or
聚集索引：
!=not in

desc select * from city where id<10;
desc select * from city where countrycode like 'CH';

B+tree 索引能额外优化到。> < >= <= like between and
in 和or享受不到b+tree额外的优化效果，所以一般情况会将in,or进行改写

desc select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

ref:辅助索引等值查询

desc select * from city where countrycode='CHN' 

eq_ref:多表连接查询中，非驱动表on的条件是主键或者唯一键

多表才会出现

const(system)：主键或唯一键的等值查询 ( 效果最好)

desc select * from city where id=10;

NULL:获取不到数据

possible_keys:可能会用到的索引 ???

NULL:没有和查询条件匹配的索引条目。
有值：有和查询条件匹配的索引条目，但是没走，大部分原因是语句查询方式不符合索引应用的条件

key:使用到的索引?????

最终使用的索引，可以帮助我们判断是否走了合适的索引。

key_len:索引的覆盖长度????

在联合索引应用的判断时，会经常去看。

对于单列索引：

字符集
utf8 :3个字节
not null
int:4个字节 4 4+1
tinyint:1个字节 1
char（2）： 24 没有not null就是24+1
vachar（2） 24+2 没有not null24+2+1 需要2个字节去存储整个字符的长度
utf8mb4:一个字符最大是4个字节
int
tinyint
char

说明：
1.有非空约束时，key_length就是最大字节长度。 也就是有not nll约束时key_lengh就是最大长度。
2.在没有非空约束时，字符最大长度+1。
3.varchar类型，需要额外在最大字符长度+2（存储字符长度的最长值。）

联合索引优化细节：

image.png

alter table t1 add index idx(id,num,k1,k2,k3,k4);
select 5+4+9+8+11+10

(1) 最理想的

desc select * from t1 where a=1 and b='a' and c='a';
desc select * from t1 where b='1' and a=1 and c='a';
desc select * from t1 where c='1' and a=1 and b='a';
desc select * from t1 where c='1' and b='a' and a=1;
desc select * from t1 where a=1 and c='a' and b='a';
desc select * from t1 where b='1' and c='a' and a=1;

结论：
当我们的查询条件当中，包含了索引列中的所有的列条件时，并且都是等值的，那么无关他的顺序，都可以走全联合索引优化。原因是优化器会自动调整顺序来达到最佳的优化效果。
所以，我们重点需要关注的是联合索引建立的顺序，从左到右，唯一值多的列放在最左边。

(2)查询条件中有哪些因素会影响key_len长度
---按照索引的建立顺序，在查询条件中，少了任意一个中间列，后续列都无法走索引。
---在条件查询中间，出现不等值查询时。（后续只能卡在这个不等值上，无法使用联合索引。）

(3)如果有多子句的条件查询（必须是联合索引）
按照子句的执行顺序，建立联合索引。

Extra:额外的信息
Using file sort：原因是在group by ,order by, distinct等注意。
一般优化的方法是和where条件的列进行联合索引。