数据库进阶 の认识索引

最新推荐文章于 2024-07-10 18:39:57 发布
最新推荐文章于 2024-07-10 18:39:57 发布
阅读量191 收藏
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhaozhao236/article/details/116920763
版权

文章目录


一. 配置文件


1.查询日志

  • log-bin ：二进制日志，主要用于主从复制，二进制日志文件
  • log-err ：默认是关闭的，用来记录严重的警告和错误提示，每次启动和关闭的详细信息等
  • log： 查询日记。比如记录一下，查询时间超过 5s 的记录

windows 下的配置文件叫做 my.int 文件，linux 下的配置文件是/etc/my.cnf文件。


2.数据文件

  • 两系统
  • frm 文件： 存的是表结构
  • myd 文件： 存放的是表数据
  • myi 文件： 存放的是表索引

myd 和 myi 连个就像是指针。两个互相指


二.Mysql 的逻辑架构

mysql本身就是一个分层的，可拔插的程序结构。
插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和时间需要合理的选择合适的存储引擎。

在这里插入图片描述


2.1 连接层

最上面，是一些客户端和连接服务，包含本地的sock 通信和大多数的 服务器和客户端额链接，类似于TCP/IP 协议，主要用于类似一些安全处理，还有授权认证等相关的方案，该层还引入了 线程池的 概念，为通过授权认证的客户端提供线程，同样在该层上可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端，验证它所具有的操作权限。


2.3服务层

服务层，第二层的框架完成大多数的服务功能，如SQL 接口，必须完成缓存的查询，SQL的分析和优化，以及部分内置函数的执行，所有跨存储引擎的功能也在这一层实现，如 过程 ,函数 等，在该层中，服务器会解析查询，并创建相应的内部解析树，并对其完成相应的优化，如确定查询表的顺序，是否利用索引，最后生成相应的执行操作。如果是 select 语句，服务器会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的时候，就能很好提升系统的性能。


2.4 引擎层

储存引擎层，引擎层真正的负责了 MySQL 中数据的存储和提取，服务器通过API 和存储引擎进行通信，不同的存储引擎具有的功能不同，这样我们能根据自己的实际需求进行选取，后面介绍mylsam 和 innoDB ，


2.5 存储层

真正的进行文件存储的地方。主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。


三.存储引擎

3.1查看存储引擎

SHOW ENGINES

在这里插入图片描述


对比项MyISAMInnoDB
主外键不支持支持
事务不支持支持
行表锁表锁，即使操作一条数据，也会锁住整个表，不适合高并发操作行锁，操作时候只能锁定某一行，不对其它行有影响，适合高并发的操作
缓存只缓存索引，不缓存真实数据不仅缓存索引还要缓存真实数据，对内存要求比较高，而且内存的大小对性能有决定性的影响
表空间小大
关注点性能 (偏读)事务
默认安装YY

3.2 索引优化分析

1.性能下降 SQL 慢，执行时间长，等待时间长
  • 查询语句写的烂，没有建立索引或者索引失效。
  • 索引失败，单值- - -复合
  • 关联查询太多 join （设计缺陷或不得已的需求）
  • 服务器调优及各个参数设置（缓冲，线程数）

3.3 建立索引

  • 单值索引
  • 复合索引
CREATE TABLE; #建表
CREATE INDEX; #建索引

单值索引

比如我们要查student 表的 name 字段，可以用如下命令建立索引。默认的索引名就是 idx_表名_字段名 。

CREATE INDEX `idx_student_name` on studentes(name);

复合索引

一般比如在淘宝买东西的时候，会有按照多个条件进行查询，这种情况下，如果使用 sql 语句，就相当于用 AND 关键字进行连接。

SELECT * FROM `studentes` WHERE `name`='小王' AND `classid`='2';

CREATE INDEX idx_students_nameClassid on studentes(`name`,`classid`);
查看索引表
show INDEX from `student`

mysql 的书写顺序和执行顺序

书写顺序

select 
    <要返回的数据列>
from
    <表名>
<join, left join, right join...> join
    <join表>
on
    <join条件>
where
    <where条件>
group by
    <分组条件>
having
    <分组后的筛选条件>
order by
    <排序条件>
limit
    <行数限制>

执行顺序
机读的时候，电脑会先从 from 开始执行，而不是从 select 开始执行，因为电脑更在乎的是在哪个表里查找。

from 
    <表名>          # 笛卡尔积
on 
    <筛选条件>          #对笛卡尔积的虚表进行筛选
<join, left join, right join...> join 
    <join表>  #指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
where 
    <where条件>     #对上述虚表进行筛选
group by
    <分组条件>      #分组
<sum()等聚合函数>   #用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
having 
    <分组筛选>      #对分组后的结果进行聚合筛选
select 
    <返回数据列表>       #返回的单列必须在group by子句中，聚合函数除外
distinct
order by 
    <排序条件>      #排序
limit 
    <行数限制>

在这里插入图片描述


四.索引

索引是帮助 MySQL 高效获取数据的数据结构，可以得到索引的本质，索引是数据结构。

索引是一种数据结构

索引的目的在于提高查询效率，可以类比字典

索引就是排好序的快速查找数据结构

数本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构，以某种方式指向数据，这样可以在这些数据结构的基础上，实现高级查找算法，这种数据结构就是索引。也就是 B 树索引。

索引简单点来理解，就是排好序的快速查找数据结构。

  • 索引对查找和排序都会有明显的影响

4.1为什么增删慢，查找快
  • 查找就是直接查
  • 而增删其实就是要建立索引树，所以频繁增删的数据不适合做索引
4.2 索引的结构
  • BTree 索引
  • Hash 索引
  • full-text 全文索引
  • R-Tress 索引

真实的情况是，3层的 B+ 树可以表示上百万的数据，如果上百万的数据，查找只需要 三次IO ，性能的提升将会是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共几百次的IO ，显然成本非常的高。


如果我们平时所说的索引，如果没有特殊指名，指的都是 B 树索引（多路搜索树，并不一定是二叉的）结构组织索引，除了 B 树索引以外，还是 hash 索引。


三百万以上的数据，就需要做数据优化了。


索引的劣势
  • 索引实际上也是一张表，他也要占空间
  • 虽然索引大大提高了查询的速度，但是也降低了更新表的速度
  • 更新表的时候，不仅要更新数据，还要更新索引
  • mysql 目前一个表里最多只能建立 16个索引
  • 但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快
单值索引
  • 即一个索引只包含一个列，一个表可以有多个单例索引
唯一索引
  • 索引列的值必须是唯一的，但是允许为空值，比如，银行卡号
复合索引
  • 即一个索引包含了多个列， 比如在银行系统中，可以根据名字和地址做索引。

在这里插入图片描述

查找的过程：
在这里插入图片描述
这样相当于只做三次 IO 就可以了。



什么时候应该建立索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 外键应该建立索引
  4. 频繁更新的字段不适合建立索引
  5. where 用不到的数据不适合建立索引
  6. 单键索引和组合索引在一起的时候，我们应该建立 组合索引
  7. 查询中的排序字段，若排序字段通过索引去访问，将大大的提高排序速度
  8. 分组时候要用到索引

什么时候不建议使用索引

  • 表的记录低于 300w 条的时候
  • 经常增删的表
  • 数据重复且平均的表，建立索引的意义不大。(比如，一个表中有2000条数据，有 1980条不同，那么索引的选择性就是1980/2000=0.99)，一个索引的选择性越接近于1，这个索引的效率就越高。

性能分析

  • mysql query optimizer，自带的优化器
  • mysql 常见的瓶颈。
  • CPU：cpu 饱和，一般发生在，数据装入内存，或者从磁盘中读取数据的时候，

  • 磁盘IO 一般发生在装入的数据，远大于内存容量的时候，

  • 服务器硬件瓶颈
  • Explain：

Explain 关键字

explain 是干什么的？

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

直接在SQL语句的前边加上 EXPLAIN 即可。

EXPLAIN SELECT * FROM class;

在这里插入图片描述

explan主要能干的几件事

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以被使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每个表有多少行被优化器查询
explain关键字 - id

select 查询的序列号，包含一组数字，表示查询中执行 select 子句或者操作表的顺序。

  • id 相同，执行顺序，有上至下。

比如下边的表，id 都相同，那么执行顺序就应该由 table 至上到下执行。

在这里插入图片描述

  • 如果是子查询，id 的序号，会递增，id 越大优先级别越高，越先被执行。

在这里插入图片描述

在这里插入图片描述

所以由上表也可以看出来，SQL的执行顺序和你写的顺序，不一定相同。


explain-select_type
  • simple :简单的select 查询，查询中不包含子查询或者 union
  • primary：查询中若包含任何复杂的子查询，最外层查询则被标记为
  • subquery：在 select1 或者 where 列表中包含子查询
  • derived：在 from 表中包含的子查询被标记为 derived（衍生），mysql 会递归执行这些子查询，把结果放在临时表里。
  • union：若第二个 select 出现在 union之后，这被标记为 union：若union包含在 from 子表的查询中，外层的 select ，就会被标记为 derived 。

explain-type

从最好到最差，依次如下

  1. system
  2. const
  3. eq_ref
  4. ref
  5. range
  6. index
  7. ALL

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


explain-possible_keys- keys
  • possible_keys ：可能用到的索引。
  • key：实际用到的索引。
  • key_len: 计算查询中使用的索引的长度，在不损失精度的情况下，长度越短越好，key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len 是根据表的定义计算出来的，而不是通过表内检索出来的。

若使用了覆盖索引，那么所以索引只出现在 key 列表中，这种情况的出现，就是因为，你要查询的索引，正好和我建立的索引一模一样。
在这里插入图片描述
比如下面的情况，你的查询条件越细，查询就越准确，相对应的，key_len 也就越长。

在这里插入图片描述


explain - ref

显示索引的哪一列被引用，如果可以的话，是个常数，哪些列或常量被用于查找索引列上的值

在这里插入图片描述

比如下面，就是看到的哪个索引字段被充分引用了。
其实可以看到哪些索引可以使用，哪些索引被实际使用。

在这里插入图片描述


explain-rows

根据表统计信息以及索引选用情况，大致估算出找到所需要读取的行数。

row这个东西当然是用的越少越好

在这里插入图片描述

explain-extra

该选项包含不适合在其他列显示，但是十分重要的额外信息。

  • using filesort 说明mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作，称为文件排序
  • using temporary 使用了临时表保存了中间结果，mysql 对查询结果排序时使用了临时表。常见排序 order by 和 分组查询 group by 。
  • using index 覆盖索引，要建立的索引和你要查的正好重合了。比如你建的是 name 和age 两个字段，你查的也是 name 和 age 两个字段。

热身小例子

在这里插入图片描述



Join 关系

内连接

在这里插入图片描述

 select <select_list>
 From TableA A 
 inner join TableB B 
 ON A.key=B.key

左连接

select <select_list>
from tableA A
left Join TableB B
on A.key=B.key

在这里插入图片描述



右连接

select <select_list>
from TableA A
right join TableB B
on A.key=B.key

在这里插入图片描述


A的独有

select <select_list>
from Table A A
left join Table B B
On A.key=B.key
Where B.ke IS NULL

在这里插入图片描述

B独有

在这里插入图片描述

B独占

select <select_list>
from TableA A
right join TableB B
On A.key=B.key
Where A.key IS NULL

在这里插入图片描述


select <select_list>
from TableA A
fullouter JOIN Table B B
ON A.key = B.key

在这里插入图片描述

select <select_list>
from TableA A
fullouter JOIN TableB B
ON A.key=B.key
Where A.key is null
or B.key is null

在这里插入图片描述


四.索引

索引是帮助 MySQL 高效获取数据的数据结构，可以得到索引的本质，索引是数据结构。

索引的目的在于提高查询效率，可以类比字典

索引就是排好序的快速查找数据结构

数本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构，以某种方式指向数据，这样可以在这些数据结构的基础上，实现高级查找算法，这种数据结构就是索引。也就是 B 树索引。


4.1为什么增删慢，查找快
  • 查找就是直接查
  • 而增删其实就是要建立索引树
4.2 索引的结构
  • BTree 索引
  • Hash 索引
  • full-text 全文索引
  • R-Tress 索引

真实的情况是，3层的 B+ 树可以表示上百万的数据，如果上百万的数据，查找只需要 三次IO ，性能的提升将会是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共几百次的IO ，显然成本非常的高。


三百万以上的数据，就需要做数据优化了。



参考文献

https://www.bilibili.com/video/BV12b411K7Zu?p=188&share_source=copy_web

沧海一声笑·rush
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
专栏目录
索引覆盖与回表
AF_0228的博客
09-09 5532
懂的越多，不懂的越多 上回书说到：什么情况下，索引下推没办法提高sql查询效率？ 表 info 主键 id 名称 name 值 value 别名 realname 对于info表，我们现在有（name,value）联合索引
数据库——SQL语言进阶
qq_45945307的博客
06-24 161
在本次实验中，我们利用搭建好的环境进行了SQL语言的进阶练习，如了解了子查询的原理并使用子查询与常见函数简化了查询过程和查询步骤；认识了伪列以及伪列的使用、注意事项；对存储过程、块、触发器也有了进一步的理解和认识。我们也通过思考题的方式了解到了伪列使用的特殊方法，掌握了不从头开始进行伪列查询的代码。收获良多。
MySQL(进阶)--索引
m0_65078452的博客
05-28 1163
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式,存储引擎是基于表的,而不是库的,所以存储引擎也被称为表类型但是我们之前写的表好像并没有给其设置存储引擎,那么到底是怎么一回事,我们可以查看某表的建表语句我们可以看到即使我们创建表的时候,没有设置存储引擎,那么MySQL也会提供一个默认的存储引擎(MySQL5.5之后默认为InnoDB)我们也可以根据一条sql语句查看当前数据库支持哪些存储引擎,从而之后可以设置。
MYSQL 四、mysql进阶 8（索引优化与查询优化）
最新发布
kkkkatoq的博客
07-10 429
关于数据库调优的知识非常分散。不同的DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。虽然SQL查询优化的技术有很多，但是大方向上完全可以分成。
Android 进阶 1、sqlite数据库
aiwanchengxu的博客
10-03 3688
在这个位置我们可以打开cmd，然后输入sqlite3,就可以执行一个命令行的数据库操作器，要注意的是他的一些命令是不一样的，跟其他的数据库比较起来，他创建或者打开数据库使用的是.open，在每一个关于数据库命令下面，都需要在前面加上一个点号，而在每一个sql命令下面都需要在后面加上一个分号；这个子类需要创建数据库和继承父类的构造方法，首先需要写一个构造方法，将上下文传进去，super一下父类的构造方法，然后就是接口方法oncreate，在里面执行创建数据表的sql命令；
数据库MySQL详解
热门推荐
卓越无关环境，保持空杯心态——靡不有初，鲜克有终
07-24 18万+
全网最详细MySQL教程，2023持续更新中
【MySQL】数据库索引入门介绍
qq_61862008的博客
11-05 354
数据库索引的初级介绍
【MySQL进阶篇】学习笔记
热爱编程、热爱生活、热爱探索
07-04 1000
本篇文章是对MySQL进阶篇学习所写的笔记，通过进阶篇的学习，我了解了InnoDB底层的大致结构，常见索引结构以及各种索引结构的优缺点，还有很多常见的SQL优化方法，以及如何进行SQL调优，还有一些MySQL高级知识比如触发器、存储函数，MySQL中的各种锁的特点，事务的实现原理等等，很多东西都是在学校课堂中学不到的东西，在此也十分感谢B站黑马
SQL Server数据库高级进阶之锁实战演练
跟着阿笨一起玩NET
07-12 381
一、SQL Server锁的本质 锁的定义：锁主要用于多用户环境下，保证数据库完整性和一致性的技术。 锁的解释：当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的完整性和一致性。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制。 二、SQL Server锁的分类 锁的分类，在教材上，网络上好多都是按两个维度来描述的。一种维度是按锁的功能来划分，一种维度是
索引初探，MySQL数据库查询
zsah2011的博客
04-12 173
数据库，简而言之，就是存储数据的，提供数据读写的系统。我们最常见的就是关系型数据库（MySQL）和非关系型数据库（Redis），今天我们就来简单的认识下MySQL中数据中的一个关键点——索引！ 数据的读写无处不在，java代码中，我们会将一些临时数据存入栈中，进行入栈和出栈，或者是存在堆中、方法区中，在需要的时候去获取。当然，这些都只是少量数据的存取，稍微多一点的数据，我们可能会再加一层封装，将数据放到数组或者List中（最终也是存在堆中），在需要的时候去获取数据。
SQL语句视频课程(进阶版)
06-13
本课程系SQL语句从入门到精通迅速提升篇视频课程（oracle零基础版）的进阶版，主要讲解索引、视图、物化视图、触发器、存储过程等常见数据库对象进行讲解。 ? ? 使大家对常见数据库对象有较为清晰的认识，同时能够...
数据库系统基础教程第二版
03-16
《数据库系统基础教程第二版》是一本深入浅出地介绍数据库理论与实践的教材，针对初学者和进阶者提供了全面的知识覆盖。本教程通过英文版答案和课后习题解答，帮助读者巩固和深化对数据库概念、设计及管理的理解。 ...
Python面试宝典（Python基础，Python高级，数据库，前端，Web，爬虫，Shell与自动化运维，测试，人工智能）
11-21
此外，数据库优化、索引原理、ACID特性以及CAP理论是数据库面试中常出现的问题。 **前端** 前端开发涉及到HTML、CSS和JavaScript。理解DOM模型、CSS布局（如流式布局、网格布局、Flexbox和Grid）以及JavaScript的...
关于Oracle数据库的一些基础知识
10-15
Oracle数据库是全球广泛使用的大型关系型数据库管理系统，其在企业级数据存储和管理中扮演着重要角色。...通过阅读"Oracle数据库傻瓜一千问"和深入学习相关文档，初学者可以逐步建立起对Oracle数据库的全面认识。
E-R图
rush_peng的博客
10-26 9158
文章目录E-R 图概述使用 visio 画E-R图Chen数据库的其他相关概念参考文献： ER图用来画数据库之间的关系 E-R 图概述 E-R图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。 它是描述现实世界关系概念模型的有效方法。是表示概念关系模型的一种方式。用 矩形框 表示 实体型 ，矩形框内写明实体名称；用 椭圆图框或圆角矩形 表示实体的属性，并用“实心线段”将其与相应关系的“实体型”连接起来； 用 菱
数据库の概念模型
rush_peng的博客
10-26 1339
文章目录1.基本概念2.联系 1.基本概念 信息世界的一些基本的概念 实体：一个人，一个部门，一个学生，一门课，学生的一次选课。 属性：一个实体可以用若干属性来刻画。例如学生可以用学号、姓名。 实体型：例如：学生 实体集：例如：全体学生 实体内部的联系，通常指组成实体的各属性之间的联系 实体之间的联系，通常指实体集之间的联系 2.联系 实体之间的联系，有一对一，一对多，多对多等多种类型 如果实体集 A 中的每一个实体，实体集 B 至多有一个实体（也可以没有）与之联系，反之亦然，则实体集 A
数据库 の 联表查询
rush_peng的博客
05-20 935
文章目录一. 概念 一. 概念 联表查询使用关键字 join 坐标查询 右表查询 中间查询 操作 描述 Inner jion 如果表中有一个匹配，就返回行 left join 会从左表中返回所有的值，即使右表中没有匹配的值 right join 会从右表中返回所有的值 ...
.net core 中使用 EFcore做ORM
rush_peng的博客
10-22 779
文章目录1.准备2.安装相应的 nuge 包3.软件包管理控制台3.1迁移的过程3.2其他的 EF Core 命令行工具4.一个简单的应用实例4.1建表4.2 增删改查操作5.指定表之间的关系6.一个复杂点的例子参考文献： 1.准备 安装mysql数据库 2 .在msql数据库中使用 efcore，可以查阅msql官方文档https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/ 如果使用EF core，查阅efcore 官方文档https
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值