学习数据库相关知识

最新推荐文章于 2024-10-13 21:08:42 发布

weixin_45389821

最新推荐文章于 2024-10-13 21:08:42 发布

阅读量451

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45389821/article/details/113541511

版权

索引

什么是索引？

索引是一种特殊的文件（INNODB数据表上的索引是表空间的一个组成成分），它包含着对数据表里所有记录的引用指针。

为何要使用索引？

一般应用系统对比数据库的读写比例在10：1左右，当数据库中的数据量比较大时，由于MySQL必须从第一行开始，然后读取整个表以查找相关行，查询数据会变得很慢。索引相当于字典的目录，通过它可以很快查询到结果，不需要进行全表扫描，从而提高查询速度；但是会降低更新表的速度。

索引的分类

普通索引：仅加速查询；
唯一索引：加速查询 + 列值唯一（可以有null）
主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索（只有MyISAM引擎支持）

索引的使用

创建索引

#直接创建索引
语法：
create index 索引名 on 表名(字段名称（长度）)
#如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
#字段类型如果不是字符串，可以不填写长度部分
举例：
#创建普通索引
create index name on students (sname(20));
#创建唯一索引
create unique index name on students(sname(20));
#创建组合索引
create index combination on students (name(20),brith(10));

# 通过修改表结构创建索引
语法：
alter table 表名 add index 索引名(字段（长度）);

删除索引

#直接删除索引
语法：
drop index 索引名 on 表名;
# 修改表结构删除索引
语法:
alter table 表名 drop index 索引名;

查看索引

语法：
show index from 表名;
或者
show key from 表名;

索引机制

1.为什么添加完索引后查询速度会变快？
- 传统的查询方法，是按照表的顺序遍历的，无论多少条数据，mysql都要将表数据从头到尾遍历一遍，添加索引后，mysql一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历，找到相应的键从而获取数据。
2.索引的代价
- 创建索引是为了产生索引文件，占用磁盘；另外索引文件是一个二叉树类型的文件，我们的增删改操作也会对索引文件进行修改，性能会下降。
3.在哪些情况下使用索引？
- 主键自动建立索引；
- 频繁作为查询条件的字段应该建立索引；
- 查询中与其他表关联的字段，外键关系建立索引；
- 在高并发的情况下创建复合索引；
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度（建立索引的顺序和排序的顺序保持一致）
1. 哪些情况不使用索引？
- 频繁更新的字段不适合建立索引；
- where条件里面用不到的字段不创建索引；
- 表记录太少，当表中数据超过三百万条数据，可以考虑建立索引；
- 数据重复且平均的表字段不适合建立索引

账户管理

在生产环境下操作数据库时，绝对不可以使用root账号连接，而是创建特定的账户，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的增删改查。

MySQL账户体系

根据账户所具有的权限的不同，MySQL的账户可以分为以下几种：

服务实例级账户
- 启动一个mysqld,即为一个数据库实例；如果某个用户如root，拥有服务实例级分配的权限，那么该账户就可以删除所有的数据库、连同这些库中的表。
数据库级别账户
- 对特定数据库执行增删改查的所有操作。
数据表级别账户
- 对特定表执行增删改查等所有操作。
字段级别的权限
- 对某些表的特定字段进行操作。
储存程序级别的账户
- 对储存程序进行增删改查的操作。

创建账户

语法：
create user '用户名'@'主机'  identified by '密码'; 
主机是指可以登录的主机，可选参数有：
localhost表示本机；
% 表示所有主机。

举例：
create user 'test'@'localhost' identified by 'test123';

删除账户

语法：
drop user [if exists] user [,user]...;

举例：
drop user 'test'@'localhost';

修改密码

修改当前账户密码：
alter user user() identified by '新密码';
举例：
alter user user() identified by 'a123456';

修改指定用户密码:
set password for 用户账号 = password('新密码');
举例：
set password for 'test'@'localhost'  = password('a123456');

授权权限

grant 权限列表 on 数据库 to '用户名'@'访问主机';
若用户不存在可以则可以这样写：
grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';

#举例：给test账号，增加只能对数据库test所有表进行读操作。
grant select on test.* to 'test'@'localhost' ;

常用权限主要包括：create、alter、drop、insert、update、delete、select
如果分配所有权限可以使用all privileges;
刷新数据库权限 flush privileges;

MySQL三层体系

MySQL是单进程多线程数据库。
MySQL可分为四层：网络连接层、SQL层、引擎层、存储层。
在这里插入图片描述

连接层：
- 最上层的连接池是一些连接服务，包含本地sock通信和大多数基于C/S工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全连接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
- 作用：连接与线程处理，比如连接处理、授权认证、安全等。（通信协议，线程，验证）
服务层：
- 第二层架构主要完成大多数的核心服务功能，如SQL接口、缓存的查询、SQL的分析和优化、内置函数等。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在频繁读操作的环境中能够很好的提升系统的性能。
- 作用：解析器,授权,优化器,查询执行,查询高速缓存,查询日志记录，跨存储引擎功能。
- sql层处理数据流程：
  用户传入sql-----查询缓存（命中缓存可直接返回结果）----解析器（生成sql解析树）----预处理器（可能sql等价改写）-----查询优化器（生成sql执行计划）----查询执行引擎----结果返回给用户。
- SQL接口：接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface。
- 解析器：SQL命令传递到解析器的时候会被解析器验证和解析（进行语义和语法的分析，分解成数据结构，如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的），生成sql解析树。解析器是由Lex和YACC实现的，是一个很长的脚本。
- 查询优化器：SQL语句在查询之前会使用查询优化器对查询进行优化，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果，即执行计划。查询优化器使用选取-投影-联接策略生成执行计划。
- 查询缓存功能（Cache和Buffer）:
  用一个例子就可以理解： select uid,name from user where gender = 1;
  这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤。
  这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。
  将这两个查询条件联接起来生成最终查询结果。
- 选取-投影-联接：
  当执行sql的时候,sql第一次被执行,然后再次执行的时候如果相同的sql,可以不进行解析，直接返回结果,提高查询效率.
  关闭查询缓存：query_cache_type = 0 query_cache_size = 0
  局限性比较大，任何查询结果有变更，都需要进行更新，对于mysql性能影响比较严重,整个更新过程的锁颗粒度的比较高,还持有全局锁，效率很低.在mysql8.0里没了查询缓存功能。
引擎层：
- 存储引擎真正的负责MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有的特性不同，我们可以根据实际需进行选取。
存储层：
- 数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

在这里插入图片描述

SQL的执行过程：

数据库通常不会被单独使用，而是由其它编程语言通过SQL支持接口调用MySQL，由MySQL处理并返回执行结果。首先，其它编程语言通过SQL支持接口调用MySQL，MySQL收到请求后，会将该请求暂时放在连接池，并由管理服务与工具进行管理。当该请求从等待队列进入到处理队列时，管理器会将该请求传给SQL接口，SQL接口接收到请求后，它会将请求进行hash处理并与缓存中的数据进行对比，如果匹配则通过缓存直接返回处理结果；否则，去文件系统查询：由SQL接口传给后面的解析器，解析器会判断SQL语句是否正确，若正确则将其转化为数据结构。解析器处理完毕后，便将处理后的请求传给优化器控制器，它会产生多种执行计划，最终数据库会选择最优的方案去执行。确定最优执行计划后，SQL语句交由存储引擎处理，存储引擎将会到文件系统中取得相应的数据，并原路返回。

MySQL引擎之MyISAM

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。

MyISAM主要特性有：
- 数据存储方式简单，使用- B± Tree- 进行索引
- 使用三个文件定义一个表：.MYI索引文件拓展名 .MYD数据文件拓展名 .frm文件存储表定义
- 少碎片、支持大文件、能够进行索引压缩
- 二进制层次的文件可以移植- (Linux -> Windows)
- 访问速度快，是所有MySQL文件引擎中速度最快的
- 不支持事务、外键约束等
- Table- level- lock，性能稍差，更适合读取多的操作
- 表数据容量有限，一般建议单表数据量介于- - 50w–200w
适用场景：
- 非事务应用
- 只读类应用

MySQL引擎之InnoDB

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键.
InnoDB主要特性有：
- 使用- Table- Space- 的方式来进行数据存储- (ibdata1,- ib_logfile0)
- 支持- 事务、外键约束等数据库特性
- Rows- level- lock- ,- 读写性能好
- 能够承载大数据量的存储和访问
- 拥有自己独立的缓冲池，能够缓存数据和索引
- 在关闭自动提交的情况下，与MyISAM引擎速度差异不大
InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

InnoDB与MyISAM对比

InnoDB	MyISAM
支持主外键	不支持主外键
支持事务	不支持事务
行锁，操作时只锁某一行，不对其他行有影响—适合高并发操作	表锁，操作一条记录会锁住整个表，不适合高并发
缓存索引同时还缓存整数数据，对内存要求高，内存大小对性能有决定性影响	只缓存索引，不缓存真实数据
表空间大	表空间小
关注点为事务	关注点位性能