MySQL:关系型数据库管理系统_索引_索引优化_视图

最新推荐文章于 2024-11-14 18:36:14 发布

AoA.

最新推荐文章于 2024-11-14 18:36:14 发布

阅读量93

点赞数 1

文章标签：数据库

本文链接：https://blog.csdn.net/weixin_64022848/article/details/132776805

版权

一、索引

1.索引介绍

索引类似图书的目录，一种数据结构，通过索引可以快速的找到需要查询的内容。

InnoDB引擎：索引和数据都是存储在表名.idb文件中。

MyISAM引擎：索引和数据存储在不同的文件中，表名.MYD 存储数据，表名.MYI存储索引。

2.索引的结构

索引在数据库底层有两种结构：BTREE和HASH。默认使用的是BTREE。

1.HASH结构

Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。

缺点

哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表

适合于精确的查找，也不适合范围查询

2.BTREE结构

BTree分为B-Tree和B+Tree，MySQL数据库索引采用的B+Tree，B+Tree是在B-Tree上做了优化改造。

1.B-Tree

索引值和data(数据)分布在整棵树结构中
每个节点可以存放多个索引值以及对应的data(数据)
树节点中的多个索引值从左到右升序排列

缺点：所有的节点都存放数据，数据会占用空间，导致存放的索引变少。

2.B+Tree

非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
叶子节点包含了所有的索引值和data数据
叶子节点用指针连接，提高区间的访问性能

相比B-树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B-树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

3.索引的优点

创建索引可以大大提高系统的查询能力

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

可以加速表和表之间的连接，特别是在实现数据的完整性方面特别有意义。

在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

通过使用索引，可以在查询的过程中，使用查询优化器，提高系统的性能。

4.索引的缺点

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

5.创建索引的字段选择

在经常需要搜索的列上，可以加快搜索的速度；

在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

建立索引，一般按照select的where条件来建立。

6.索引分类

1.介绍

索引分为单列索引，组合索引，全文索引。

单列索引就是只是给某个列加索引；组合索引是给表中大于等于两个列添加索引。

2.单列索引：

单列索引又分为：主键索引、普通索引和唯一索引。

主键索引

它是一种特殊的唯一索引，不允许有空值
在创建或修改表时添加主键约束即可，添加了主键约束就会自动创建主键索引
每个表只能有一个主键约束，所以一张表只能有一个主键索引

-- 1. 创建表时指定主键约束
create table 表名(
   字段1 类型 primary key,
   ...
);

-- 2. 为创建好表，但是并没有指定主键约束的表添加主键约束
alter table 表名 add primary key(字段名);

-- 3. 查看某张表中的所有索引
show index from 表名;

普通索引

特点:

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

普通字段: 非主键约束，非外键约束，非唯一约束

-- 1. 创建表时创建普通索引
create table 表名(
   字段1 类型,
   ...,
   index [索引名称](字段名) -- 不指定索引名称，自动生成
);

-- 2. 为创建好的表添加普通索引
alter table 表名 add index [索引名称](字段名); -- 不指定索引名称，自动生成

-- 3. 为创建好的表添加普通索引
create index <索引名称> on 表 (字段名); -- 必须指定索引名称

唯一索引

-- 创建表时指定唯一约束，会自动创建唯一索引
create table 表名(
   字段1 类型 unique,
   ...
);

-- 为创建好的表添加唯一索引
alter table 表名 add unique index [索引名称](字段名);

-- 为创建好的表添加唯一索引
create unique index <索引名称> on 表名(字段名);

3.组合索引

给表中大于等于两个列添加索引。

但是需要满足最左前缀，创建组合索引相当于创建了多个索引，一般把最常用的放在最左边。

-- 语法格式：
create index 索引名 on 表名(列1，列2...)

create index index3 on demo(col1,col2,col3)

create index index3 on demo(col1,col2)

4.全文索引

创建

-- 创建表
create table tb_fulltext(
	id int(11) primary key auto_increment,
	name varchar(100),
    address varchar(200),
    FULLTEXT index_name (name)
);
-- 方式2
ALTER TABLE table_name ADD FULLTEXT index_name(column);

使用

insert into tb_fulltext values(1,'tony','beijing yizhuang');
insert into tb_fulltext values(2,'kevin','beijing yizhuang jingkaiqu');
insert into tb_fulltext values(3,'jordan','beijing daxing');

explain select * from tb_fulltext where match(name,address) against('yizhuang');
select * from tb_fulltext where match(name) against('yizhuang');// 执行报错

against中内容有三种模式:

自然语言模式：IN NATURAL LANGUAGE MODE
布尔模式：IN BOOLEAN MODE
查询扩展模式：WITH QUERY EXPANSION

自然语言模式：拆分出来的关键字必须严格匹配。例如beijing只能通过beijing搜索，不能通过bei搜索。

布尔模式：支持特殊符号。即使对没有全文索引的列也可以进行搜索，但是非常慢。查询时必须从最左开始查询

查询扩展：查询时进行扩展查询，发现和条件有关系的内容都查询出来

5.中文拆词器

由于中文是没有空格的，MySQL 从5.7.6开始内置ngram中文分词插件。可以设置把整个中文按照指定大小进行拆词。

设置拆词长度（根据自己的需求进行完成即可）

ngram_token_size=2

给address创建全文索引。

create fulltext index index3 on ft(address)  with parser ngram;

7.聚集索引和非聚集索引

Innodb存储引擎：（索引和数据在同一个文件中）

       聚集索引 | 聚簇索引：并不是索引的分类，索引值和行数据存储在一起，数据会按照索引的顺序进行存储。
         主键索引为聚集索引的一种，也可以自定义聚集索引（很少自定义）。
表中没有主键索引，自动找一个唯一非空的索引作为聚集索引，自动创建一个隐藏的字段作为聚集索引。

       非聚集索引 | 非聚簇索引 | 二级索引 | 辅助索引：并不是索引的分类，索引值和主键值存储在一起，根据索引值找到主键，根据主键找到行数据（回表查询）。
MyISAN存储引擎：（索引和数据在不同的文件中）
       非聚集索引：索引值和行数据的地址存储在一起。

二、索引优化

1.使用短索引（前缀索引）

对串列进行索引，如果可能应该指定一个前缀长度。

CREATE  INDEX  index_name  ON  table_name (column(length));

2.索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

3.like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”（非前导模糊查询）可以使用索引。使用后，优化到range级别。

explain select * from teacher where address like '%oracle%';

4.不要在列上运算

会导致索引失效而进行全表扫描

5.范围列可以使用索引

范围条件有：<、<=、>、>=、between等。

范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。所以where中把最主要的查询条件放在第一个。

6.类型转换会导致索引无效

当列是文本类型时，把数值类型当作列的条件会弃用索引。

explain select * from teacher where name = 20;

7.总结

索引的级别：const(主键查询) > ref > range > index(扫描全部索引) > all(全表扫描)

不要在where后的条件中进行列的运算和函数操作
模糊查询时，like‘%xxx%’，放弃使用索引二进行全表扫描。like‘xxx%’，可以使用索引索引级别为range。
类型转换会导致索引无效

理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的。

建议：一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

三、视图

1.什么是视图

视图是一种虚拟表

视图建立在已有表的基础上，视图建立依赖的这些表称为基表

向视图提供数据内容的语句为 SELECT 语句，可以将视图理解为存储起来的 SELECT 语句

视图向用户提供基表数据的另一种表现形式

2.视图的作用

简化复杂的查询

视图本身就是一条查询SQL，我们可以将一次复杂的查询构建成一张视图，用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)，可以理解为查询视图就相当于将创建视图的SQL再次执行一次

视图主要就是为了简化复杂查询

3.视图的使用

-- 创建视图
create view 视图名称 as select语句; 
-- view: 表示视图
-- as: 表示视图要执行的操作
-- select: 向视图提供数据内容
-- 查询视图和查询表一样
select * from 视图名称;
-- 删除视图
drop view 视图名称;

4.视图与表的区别

视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示

通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列经过计算得到的结果,不允许更新）

删除视图，表不受影响，而删除表，视图不再起作用

AoA.

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫