4 索引及执行计划

本文详细介绍了MySQL索引的作用、种类、B树结构及其分类,重点讨论了辅助索引和聚集索引的构建,并分析了索引树高度的影响因素。此外,还探讨了索引管理、执行计划获取与分析、压力测试以及索引应用规范,提供了SQL优化和避免全表扫描的建议。
摘要由CSDN通过智能技术生成

一. 索引作用

提供了类似于书中目录的作用,目的是为了优化查询

二. 索引的种类

B树索引
Hash索引
R树
Full text
GIS

三. B树基于不同的查找算法分类介绍

B-tree
在范围查询方面提供了更好的性能(> < >= <=)
B+Tree
B*Tree

四. 在功能上的分类

辅助索引(S)怎么构建B树结构的?

(1). 索引是基于表中,列(索引键)的值生成的B树结构
(2). 首先提取此列所有的值,进行自动排序
(3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4). 然后生成此索引键值所对应得后端数据页的指针
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度
id name age gender
select * from t1 where id=10;
问题: 基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO.

聚集索引©

前提
(1)表中设置了主键,主键列就会自动被作为聚集索引.
(2)如果没有主键,会选择唯一键作为聚集索引.
(3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)
辅助索引(S)怎么构建B树结构的?
(1) 在建表时,设置了主键列(ID)
(2) 在将来录入数据时,就会按照ID列的顺序存储到磁盘上.(我们又称之为聚集索引组织表)
(3) 将排好序的整行数据,生成叶子节点.可以理解为,磁盘的数据页就是叶子节点

五.辅助索引细分

1.普通的单列辅助索引
2.覆盖索引(联合索引)
多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询
3.唯一索引
索引列的值都是唯一的.

六. 关于索引树的高度受什么影响

  1. 数据量级, 解决方法:分表,分库,分布式
  2. 索引列值过长 , 解决方法:前缀索引
  3. 数据类型:
    变长长度字符串,使用了char,解决方案:变长字符串使用varchar
    enum类型的使用enum (‘山东’,‘河北’,‘黑龙江’,‘吉林’,‘辽宁’,‘陕西’…)
    1 2 3

七. 索引的基本管理

索引建立前

db01 [world]>desc 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      |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Field :列名字
key :有没有索引,索引类型
PRI: 主键索引
UNI: 唯一索引
MUL: 辅助索引(单列,联和,前缀)

单列普通辅助索引

创建索引:

db01 [world]>alter table city add index idx_name(name);
                                       表                    索引名(列名)
db01 [world]>create index idx_name1 on city(name);
db01 [world]>show index from city;

在这里插入图片描述
注意:
以上操作不代表生产操作,我们不建议在一个列上建多个索引
同一个表中,索引名不能同名。
删除索引:

db01 [world]>alter table city drop index idx_name1;
                                        表名                 索引名

覆盖索引(联合索引)

Master [world]>alter table city add index idx_co_po(countrycode,population);

前缀索引

db01 [world]>alter table city add index idx_di(district(5));

注意:数字列不能用作前缀索引。

唯一索引

db01 [world]>alter table city add unique index idx_uni1(name);
ERROR 1062 (23000): Duplicate entry 'San Jose' for key 'idx_uni1'

统计city表中,以省的名字为分组,统计组的个数

select district,count(id) from city group by district;

需求: 找到world下,city表中 name列有重复值的行,最后删掉重复的行

db01 [world]>select name,count(id) as cid from city group by name  having cid>1 order by cid desc;
db01 [world]>select * from city where name='suzhou';

八. 执行计划获取及分析

介绍

(1)
获取到的是优化器选择完成的,他认为代价最小的执行计划.
作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.
如果业务中出现了慢语句,我们也需要借助此命令进行语句的评估,分析优化方案。
(2) select 获取数据的方法

  1. 全表扫描(应当尽量避免,因为性能低)
  2. 索引扫描
  3. 获取不到数据

执行计划获取

获取优化器选择后的执行计划
在这里插入图片描述
在这里插入图片描述
更正: 上图 rows:997261 的含义为 查询结果集的行数

执行计划分析

重点关注的信息

table: city                        
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值