MySQL-索引和索引优化分析

最新推荐文章于 2024-07-22 17:39:53 发布

月弦YueXian

最新推荐文章于 2024-07-22 17:39:53 发布

阅读量111

点赞数

分类专栏： MySQL 文章标签： mysql b树数据结构

本文链接：https://blog.csdn.net/zZZ251682553/article/details/125171274

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

前言

本文介绍了索引、索引结构和索引分类，索引创建的条件，以及使用Explain分析索引应该怎么创建和避免索引失效。

索引简介

介绍

索引(index)是帮助MySQL高效获取数据的数据结构。
可以理解为：索引是数据结构；或者排好序的快速查找数据结构。
索引本身很大，不可能全部存在内存中，是以索引文件的形式存储在磁盘上。

索引优缺点

优点

提高数据检索效率，降低数据库的io成本。
通过索引对数据排序，降低排序成本，降低cpu消耗。

缺点

降低了更新表的速度，如insert、update和delete。
索引也是一张表，该表保存了主键和索引字段，并指向实体表的纪录，所以占用了更多空间。

索引结构

B-Tree

B-Tree的特点

所有键值分布在整个树中。
任何关键字出现且只出现在一个节点中。
搜索有可能在非叶子节点结束。
在关键字全集内做一次查找，性能逼近二分查找算法。

B-Tree存在的问题

每个节点中有key，也有data，而每一个页的存储空间是有限的，如果data数据较大时就会导致每个节点（即一个页）能存储的key的数量很小
当存储的数据量很大时，同样1会导致B-Tree的深度较大，增加查询时的磁盘I/O次数，进而影响查询效率

B+Tree

B+Tree与BTree的不同在于：

所有关键字存储在叶子节点，非叶子节点不存储真正的data。
为所有叶子节点增加了一个链指针。

总结

因为计算机内存问题和查询效率问题，MySQL选择B+Tree

索引分类

基本语法

# 创建索引
CREATE  [UNIQUE ]  INDEX [indexName] ON table_name(column)) 

# 删除索引
DROP INDEX [indexName] ON mytable; 

# 查看索引
SHOW INDEX FROM table_name\G

单一索引

# 随表一起建索引
create table `t_emp`(
    `id` int(11) not null auto_increment,
    `name` varchar(20) default null,
    `age` int(3) default null,
    `deptId` int(11) default null,
    `empno` int not null,
    primary key (`id`),
    key 'idx_dept_id' ('deptId')    --创建索引
)engine=innodb auto_increment=1 default charset = utf8;
  
# 单独建单值索引
create index idx_name on t_emp(name);
 
# 删除索引
drop index idx_name on t_emp;

唯一索引

create unique index idx_empno on t_emp(empno);

主键索引

注意：新建的表可以创建主键索引，已有数据的表不能新建索引。

# 随表一起建索引
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
   
CREATE TABLE customer2 (id INT(10) UNSIGNED   ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
 
# 单独建主键索引
ALTER TABLE customer add PRIMARY KEY customer(customer_no);  
 
# 删除建主键索引
ALTER TABLE customer drop PRIMARY KEY ;  
 
# 修改建主键索引
必须先删除掉(drop)原索引, 再新建(add)索引

复合索引

create index idx_age_deptid_name on t_emp(age,deptId,name);

索引创建条件

需要创建索引的条件

主键自动建立唯一索引。
频繁作为查询条件的字段应该创建索引(where 后面的语句)。
查询中与其它表关联的字段，外键关系建立索引。
单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)。
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

不需要创建索引的条件

表记录太少
经常增删改的表或者字段
Where条件里用不到的字段不创建索引
过滤性不好的不适合建索引

Explain（索引创建的标尺）

介绍

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

使用方式

# Explain + SQL语句
explain select c.name, ab.name ceoname from t_emp c left join (
    select a.name,b.id from t_emp a inner join t_dept b on a.id = b.CEO
) ab on c.deptId = ab.id;

可以得到以下字段

字段解释

id字段

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序。
每个id号，表示一趟独立的查询。一个sql 的查询趟数越少越好，即id号越少越好。

select_type字段

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

table字段

显示这一行的数据是关于哪张表的。

type字段

字段类型

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。