mysql dba课程_MySQL-DBA课程-Day06

第二部分 MySQL 核心技术课程介绍

索引

存储引擎

日志

备份

主从

===========================

1. 索引及执行计划

1.1 介绍

相当于一本书中的目录,优化查询.

1.2 MySQL索引的类型(算法)

BTREE (Banlance Tree) ★★★★★★

HASH

FULLTEXT

RTREE

GIS

1.3 索引算法的演变

1.3.1 BTREE算法 由来

BTREE讲究的是查找数据的平衡,让我们的查询可以快速锁定范围

1.3.2 BTREE 的增强之路

B-TREE ------> 叶子节点双向指针 ------> 非叶子结点双向指针 -----> BTREE

1.3.3 BTREE 数据结构构建过程 *****

(1) 数据排序(默认是从小到大)

(2) 将数据有序的存储到16KB数据页,生成叶子(leaf node)节点.

(3) 通过叶子节点范围(最小值到下个叶子节点最小值)+每个叶子节点指针生成non-leaf.

(4) 通过non-leaf节点的范围(最小值到下个non-leaf节点最小值)+每个 non-leaf指针生成root.

(5) BTREE中,为了进一步优化范围查询,加入了leaf双向指针,non-leaf双向指针.

减少索引IO次数,有效的较少IOPS

减少了随机IO的数量

减少IO量级

1.4 MySQL的 索引组织表(InnoDB)★★★★★★

(1) Clusterd Index: 聚簇(聚集,集群)索引

前提:

1. MySQL默认选择主键(PK)列构建聚簇索引BTREE.

2. 如果没有主键,自动选择第一个唯一键的列构建聚簇索引BTREE.

3. 如果以上都没有,会自动按照rowid生成聚簇索引.

说明:

聚簇索引,叶子节点,就是原始的数据页,保存的是表整行数据.

为了保证我们的索引是"矮胖"结构,枝节点和根节点都是只保存ID列值范围+下层指针.

(2) Secondary Index: 辅助(二级)索引

构建过程: alter table t1 add index idx(name)

1.提取name+id列的所有值

2.按照name自动排序,有序的存储到连续的数据页中,生成叶子节点

3. 只提取叶子节点name范围+指针,生成枝节点和根节点

(3) 针对 name列的查询,是如何优化?

select * from t1 where name='bgx';

1. 按照查询条件bgx,来带基于Name列构建的辅助索引进行遍历

理论上读取page为3次,找到主键值

2. 根据ID值,回到聚簇索引树,继续遍历,进而找到所需数据行.

理论读取的数据页为3次.

1.5 辅助索引细分

1.5.1 单列

1.5.2 联合索引 *****

例如:

idx(a,b,c)

理论上可以有效的避免回表的次数.

1.5.3 唯一索引

手机号,身份证号类似的列.

理论上通过唯一索引作为遍历条件的话,读取6个page即可获取数据行.

1.6 索引树高度问题,影响的原因?

(1) 数据行数多.

分区表(现在用的少).

归档表.

分库分表

(2) 选取的索引列值过长

前缀索引.

test(10)

(3) varchar(64) char(64) enum()等数据类型的影响

1.7 索引管理操作

1.7.1 查询索引

desc city;

key:

PRI : 主键

UNI : 唯一键

MUL : 普通

mysql> show index from city\G

select

table_schema,table_name,

column_name ,

data_type,Column_key ,

COLUMN_COMMENT from information_schema.columns

WHERE table_schema NOT IN ('sys','informatiion_schema','performance_schema','mysql');

1.7.2 创建索引

例子:

-- 1. 单列索引例子

select * from city where population>10000000

索引设计:

mysql> alter table city add index idx_popu(population);

说明:

1. 作为 where 查询条件的列.

2. 经常作为 group by ,order by,distint,union的列创建索引.

-- 2. 联合索引例子

select * from city where district='shandong' and name='jinan';

索引设计:

mysql> alter table city add index idx_dis_name(district,name);

说明:

联合索引排列顺序,从左到右.重复值少的列,优先放在最左边.

-- 3. 前缀索引应用(字符串)

mysql> alter table city add index idx_name(name(5));

-- 4. 唯一索引

mysql> alter table student add unique index idx_tel(xtel);

mysql> desc student;

1.7.3 删除索引

mysql> alter table city drop index idx_dis_name;

执行计划(explain)分析

2.0 命令

explain select

desc select

2.1 使用场景

(1) 语句执行之前 : 防患未然

(2) 出现慢语句时 : 亡羊补牢

2.2 执行计划结果查看(优化器选择后的执行计划)

mysql> 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 |

+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

2.3 重点关注指标说明

table: 发生在哪张表的执行计划.

type : 查询的类型

全表扫描 : ALL

索引扫描 : index < range < ref < eq_ref < connst(system)< NULL ★★★★★

possible_keys : 可能用到的索引

key : 此次查询走的索引名.

key_len : 索引覆盖长度.评估联合索引应用长度的. ★★★★★

rows : 扫描了表中的多少行

Extra : 额外的信息 ★★★★

2.4 type

(1) ALL : 全表扫描

mysql> desc select * from city;

mysql> desc select * from city where 1=1 ;

mysql> desc select * from city where population=42;

mysql> desc select * from city where countrycode !='CHN';

mysql> desc select * from city where countrycode not in ('CHN','USA');

mysql> desc select * from city where countrycode like '%CH%';

(2) index : 全索引扫描

mysql> desc select countrycode from city;

(3) range : 索引范围扫描(最常见)

< >= <= like

in or

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');

改写:

desc

select * from city where countrycode='CHN'

union all

select * from city where countrycode='USA'

(4) ref 辅助索引等值查询

desc

select * from city where countrycode='CHN';

(5) eq_ref 多表关联查询中,非驱动表的连接条件是主键或唯一键

desc

select

city.name,

country.name ,

city.population

from city

join country

on city.countrycode=country.code

where city.population<100;

(6) const(system) :主键或者唯一键等值查询

mysql> desc select * from city where id=10;

(7) NULL 索引中获取不到数据

mysql> desc select * from city where id=100000;

2.5 key_len详细说明

2.5.1 作用

判断联合索引覆盖长度

2.5.2 最大覆盖长度的计算方法

idx(a,b,c) ====> a(10)+b(20)+c(30)

(1) 影响计算的条件

字符集 : utf8mb4

数字类型

tinyint 1 Bytes

int 4 Bytes

bigint 8 Bytes

字符串类型

char(5) 54 Bytes

varchar(5) 54 Bytes + 2 Bytes

没有 not null : 多一个字节存储是否为空

测试表:

create table keyt (

id int not null primary key auto_increment,

num int not null,

num1 int ,

k1 char(10) not null ,

k2 char(10) ,

k3 varchar(10) not null ,

k4 varchar(10)

)charset=utf8mb4;

num : 4

num1: 5

k1 : 40

k2 : 41

k3 : 42

k4 : 43

2.5.3 联合索引应用"道道" *****

-- 建立联合索引时,最左侧列,选择重复值最少的列.

alter table keyt add index idx(a,b,c);

-- 例子:

-- 哪些情况可以完美应用以上索引.

desc select *from student where xname='张三' and xage=11 and xgender='m';

desc select *from student where xage=11 and xgender='m' and xname='张三' ;

desc select *from student where xgender='m' and xname='张三' and xage=11 ;

-- 影响到联合索引应用长度的.

-- 缺失 联合索引最左列,不走任何索引

mysql> desc select *from student where xage=11 and xgender='m' ;

-- 缺失中间部分,只能走丢失部分之前的索引部分

mysql> desc select *from student where xname ='张三' and xgender='m' ;

-- 查询条件中,出现不等值查询(> ,< ...like )

mysql> desc select *from student where xname ='张三' xage<18 and xgender='m' ;

联合索引应用长度到不等值列截断了.

-- 多子句

按照 select 子句顺序创建联合索引.

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
该视频包含以下文件目录 均为高清不加密录制 此资源来源于某宝购买 觉得有学习价值 特此分享 共勉 -------------------Mysql DBA高级运维系列课程(老男孩19部全)-------------------------------- 01-第一部-MySQL基础入门(21节) 02-第二部-老男孩MySQL多实例安装与企业应用场景(10节) 03-第三部-老男孩MySQL应用管理及进阶实战操作(29节) 04-第四部-老男孩MySQL乱码问题及字符集实战(14节) 05-第五部-老男孩MySQL备份-增量备份及数据恢复基础实战(12节) 06-第六部-老男孩MySQL主从复制原理及实战部署(10节) 07-第七部-老男孩MySQL主从复制企业级深入高级应用实战(20节) 08-第八部-老男孩MySQL备份与恢复实战案例及生产方案(10节) 09-第九部-老男孩MySQL服务日志详细介绍及增量恢复命令实践(7节) 10-第十部-老男孩MySQL常用引擎及优缺点-应用场景-调优详解(14节) 11-第十一部 MySQL读写分离实现(2节) 12-第十二部-老男孩MySQL高可用工具heartbeat实战(33节) 13-第十三部-老男孩MySQL高可用工具drbd实战(18节) 14-第十四部-老男孩MySQL高可用综合实现实战(22节) 15-第十五部-老男孩MySQL数据库优化思想与优化实战(9节) 16-第十六部-老男孩MySQL业务变更流程与安全管理思想(7节) 17-第十七部-老男孩MySQL数据库索引优化及数据丢失案例-3节 18-第十八部-老男孩MySQL数据库生产场景核心优化精讲-05-节 19-第十九部-老男孩MySQL读写分离开发实现及软件实现-物理备份-高可用分享-5节
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值