MySQL学习笔记2

最新推荐文章于 2024-08-19 22:23:29 发布
最新推荐文章于 2024-08-19 22:23:29 发布
阅读量1.7k 收藏 27
点赞数 48
文章标签： mysql 学习 笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_47175003/article/details/137534456
版权

文章目录

一、存储引擎

1.MySQL体系结构

  • 连接层
    最上层是一些客户端和链接服务，主要完成一些类似干连接处理，授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
  • 服务层
    第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。
  • 引擎层
    存储引擎真正的负责了MVSOL中数据的存储和提取，服务器通过AP!和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
  • 存储层
    主要是将数据存储在文件系统之上，并完成与存储引擎的交互。
    在这里插入图片描述

注意:索引是位于存储引擎层的，所以不同的引擎对应着不同的索引结构

2.存储引擎简介

  • 概念:存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
  • 查看所有引擎的SQL语句
show engines ;
  • 自主选定引擎
create table myISAM(
    id int,
    name varchar(50)
)engine =MyISAM;
  • 查看表的引擎相关信息
show create table test;

InnoDB引擎为默认引擎

3.存储引擎特点

InnoDB引擎
  • 1.介绍

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MVSOL5.5之后，InnoDB是默认的 MySQL 存储引擎,

  • 2.特点

DML操作遵循ACID模型，支持事务
行级锁，提高并发访问性能;
支持 外键 FOREIGN KEY约束，保证数据的完整性和正确性:

    1. 文件

xxx.ibd:xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构(frm、sdi)、数据和索引。参数:innodb file per table

  • 4.逻辑存储结构
    TableSpece:表空间
    segment:段
    Extent:区
    Page:页
    ROW:行
  • 文件存储位置(以我的MySQL版本为参考)

C:\ProgramData\MySQL\MySQL Server 8.0\Data

MyISAM引擎
  • 1.介绍

MyISAM是MySQL早期的默认存储引擎。

  • 2.特点

不支持事务，不支持外键，支持表锁，不支持行锁，访问速度快

  • 3.文件

xxx.sdi:存储表结构信息
xxx.MYD:存储数据
xxx.MYI:存储索引

Memory引擎
  • 1.介绍

Memorv引擎的表数据存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用

  • 2.特点

内存存放
hash索引(默认)

  • 3.文件

xxx.sdi:存储表结构信息

引擎特点区分

引擎特点区分

4.存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

InnoDB:

是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致》性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

MyISAM:

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

MEMORY:

将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表>无法缓存在内存中，而且无法保障数据的安全性。

二、索引

1、索引概述

  • 索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

没有索引的数据库查找 （全表扫描） 效率是很低,有索引则能更高速的查找数据。
在这里插入图片描述

  • 优缺点
    在这里插入图片描述

2、索引结构

在这里插入图片描述

在这里插入图片描述

我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引

为了解决大数据量情况下，层级较深，检索速度慢-----------------》B+Tree

1.B-Tree(多路平衡查找树)

在这里插入图片描述

2.B+Tree

在这里插入图片描述

3.Hash索引

在这里插入图片描述

  • Hash索引特点
    1.Hash索引只能用于对等比较(=，in)，不支持范围查询(between，>，
    2.无法利用索引完成排序操作
    3.查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

  • 思考

为什么InnoDB存储引擎选择使用B+tree索引结构?

1.相对于二叉树，层级更少，搜索效率高
2.对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低;
3.相对Hash索引，B+tree支持范围匹配及排序操作;

3、索引分类

在这里插入图片描述
在这里插入图片描述

  • 聚集索引选取规则:
    如果存在主键，主键索引就是聚集索引。
    如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。
    如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

  • 如图所示，聚集索引与行数据对应，二级索引与主键对应。
    在这里插入图片描述

  • 回表查询:通过二级索引拿到对应数据的主键，再通过聚集索引由主键找到对应的行数据。
    在这里插入图片描述

  • 思考题 ————InnoDB主键索引的B+tree高度为多高呢?
    在这里插入图片描述

4、索引语法

  • 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,.. );

后面括号里面有多个参数的时候,表示联合索引,即:一个索引，多个字段使用。

  • 查看索引
SHOW INDEX FROM table_name;
  • 删除索引
DROP INDEX index_name ON table_name ;

5、索引—性能分析

通过查询数据库的主要功能(增、删、改、查)，进行针对性优化。

5-1 访问频次查询

MySQL客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:

SHOW GLOBAL STATUS LIKE 'Com_______';

在这里插入图片描述

5-2 慢查询日志

作用:收集相关功能较慢的语句,方便进行针对性优化。

慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(C:\ProgramData\MySQL\MySQL Server 8.0\my.ini)中配置如下信息:

#开启MySOL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为2秒，SOL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志long
long_query_time=2

在这里插入图片描述
在这里插入图片描述

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，就可以根据慢日志文件中记录的信息，查询处需要优化的语句。

5-3 profile详情

show profiles 能够在做SOL优化时帮助我们了解时间都耗费到哪里去了。通过select @@have_profiling参数，能够看到当前MySQL是否支持profile操作:

-- 当前MySQL是否支持profile操作
select @@have_profiling;

-- 查看profiling是否开启。
select @@profiling;

-- 开启profiling
set @@profiling=1;

#查看每一条SOL的耗时基本情况

show profiles;

#查看指定query id的SQL语句各个阶段的耗时情况

show profile for query query_id;

#查看指定query id的SQL语句CPU的使用情况

show profile cpu for query query_id;
5-4 explain执行计划

EXPLAIN 或者 DESC命令获取 MySQL如何执行 SELECT语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

语法:

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件
  • EXPLAIN 执行计划各字段含义:
    id
    select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下:id不同，值越大，越先执行)。
-- 查询选择MySQL课程的学生
select id from course where name='MySQL'; -->id=3
select studentId from student_course where courseId='3';
explain select * from student s where s.id in(select studentId from student_course where courseId=(select id from course where name='MySQL'));

在这里插入图片描述
select type
表示 SELECT的类型，常见的取值有 SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等
type
表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all 。
语句优化的时候，尽量往前优化，当然NULL是优化不到的。
在这里插入图片描述
possible_key
显示可能应用在这张表上的索引，一个或多个

Key
实际使用的索引，如果为NULL，则没有使用索引。

Key_len
表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

rows
MVSOL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的

filtered
表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好

6、索引的使用

6-1最左前缀法则

如果索引关联了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列 (Column name) 开始，并且不跳过索引中的列。（where 之后 所有与索引关联的列都要存在）
如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

举例
1.创建联合索引

create index key_num_name_age on employee(workaddres,name,age);

2.查询该表的索引

show index from employee;

在这里插入图片描述

  1. 查看执行效率--------正常情况------------从索引的最左列开始，并且不跳过索引中的列。
    走索引搜索
explain select * from employee where workaddres='00314'&&name='lee'&&age=18;

在这里插入图片描述
4. 查看执行效率--------不正常情况------------不从索引的最左列开始。
走的是全表搜索，效率低下！

explain select * from employee where  name='lee' and age=18;

在这里插入图片描述

6-2 范围查询

联合索引中，出现范围查询(>，<)，范围查询右侧的列索引失效
因此建议使用（>=,<=）而不是（>,<）

6-3 索引列运算

不要在索引列上进行运算操作，索引将失效，

6-4 字符串不加引号

字符串类型字段使用时，不加引号，索引将失效

6-3 模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
(但是我测试为什么’%'放前面，放后面，都没有使用索引啊)

6-4 or连接的条件

条件为or时，左右两边的操作数都应该有索引，并且不是联合索引。才能够走索引查询。

6-5 数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引

6-6 SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的,
就比如说一个字段，它有两个索引，包括一个联合索引，这是系统会默认选择联合索引进行搜索，如果我需要改变选择的话，就需要用到SQL提示。

  • use index() —use 只是建议性使用指定索引
 explain select * from employee  use index (index_num) where worknumber='00314';

在这里插入图片描述

  • ignore index( )
 explain select * from employee  ignore index (index_num) where worknumber='00314';
 -- 忽略index_num之后，走联合索引。

在这里插入图片描述

  • force index () -----强制使用指定索引。
 explain select * from employee  force index (index_num) where worknumber='00314';
6-7 查询覆盖索引

  • 尽量减少 * 的使用进行查询，而应做到查询覆盖索引，
    因为:在使用索引查询时，当需要查询的字段没有索引时，那么需要通过其他字段的索引去查找他，就必须用到回表查询，回表查询势必就会减低查找效率。
    而 * 查询的是一列的数据，一列数据中总有一些是没有索引的，就必须要回表，因此尽量减少select *的查询
    在这里插入图片描述

  • 在explain语句中字段extra含义有如下
    在这里插入图片描述

6-8 前缀索引

当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

  • 前缀长度的选择
    可以根据索引的选择性(不重复性)来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
    简单点说就是用最少的字符串长度把所有该字段的数据都能够概括进来

步骤如下

  1. 选择性值查询如下
select count(distinct 字段名)/count(*) from tablename ;
  1. 确定前缀长度最小的最高选择性。
select count(distinct substring(字段名,1,选择性最高的前缀长度))/count(*) from employee ;
  1. 前缀索引使用
create index 字段名_选择性最高的前缀长度 on tablename(字段名(前缀长度));

7 索引设计原则

  • 针对于数据量较大，且查询比较频繁的表建立索引。
  • 针对于常作为查询条件(where)、排序(orderby)、分组(groupby)操作的字段建立索引。
  • 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  • 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  • 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  • 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  • 如果索引列不能存储NULL值，请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
点名啦
关注
  • 48
    点赞
  • 踩
  • 27
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
Mysql学习笔记（全）
m0_60264772的博客
12-07 2万+
目录 MyISAM和InnoDB 性能下降SQL慢的原因： Mysql执行顺序 SQLJoin 索引 索引的优劣 索引分类 mysql索引结构 那些情况建索引 哪些情况不要建索引 性能分析 Extra 索引优化 索引口诀 索引案例 Order By 索引优化 exsites Mysql慢查询日志命令 函数操作批量插入数据 show Profile分析sql 全局查询日志 Mysql锁 锁命令 表锁：读锁 表锁：写锁 行锁 事务 行锁测试 间隙锁 行
MySQL学习笔记.md
12-07
观看b站：《老杜带你学_mysql入门基础》，所做的随堂笔记，杜老师讲的十分通俗易懂，respect！
MySql学习笔记2
m0_73839343的博客
05-15 764
Mysql学习笔记
MySQL学习笔记2：如何避免数据库乱码
honvin的博客
10-27 3833
客户端发送的sql语句编码字符集。它的作用就是告诉mysql服务器，本地客户端采用了什么编码环境。insert into t values(‘吴’);服务器知道这条sql是以character_set_client指定的字符集所编码的,'吴’将被转化为指定的编码格式，比如它的utf8编码为E590B4，最终以0101…发送到server。：服务器将接收到的character_set_client指定编码的sql语句翻译成character_set_connection指定编码的sql。
MySQL学习笔记8——游标
qq_44778824的博客
04-24 1825
本节学习了游标的使用方法，包括在存储过程中使用游标的4个步骤，分别是定义游标、打开游标、读取游标数据和关闭游标。除此之外，还介绍了经常与游标结合使用的流程控制语句，包括循环语句LOOP、WHILE和REPEAT；条件判断语句IF和CASE；还有跳转语句LEAVE和ITERATE。
尚硅谷MySQL学习笔记
IAMLSL的博客
02-11 1万+
MySQL笔记目录一、MySQL数据库基础篇大纲1. 数据库概述与MySQL安装篇第01章：数据库概述第02章：MySQL环境搭建2. SQL之SELECT使用篇第03章：基本的SELECT语句第04章：运算符第05章：排序与分页第06章：多表查询第07章：单行函数第08章：聚合函数第09章：子查询3. SQL之DDL、DML、DCL使用篇第10章：创建和管理表第11章：数据处理之增删改第12章：MySQL数据类型精讲第13章：约束4. 其它数据库对象篇第14章：视图第15章：存储过程与函数第16章：变量、
MYSQL学习笔记-SQL
Hhg66hh的博客
01-11 476
MySQL学习笔记5——函数和索引
qq_44778824的博客
04-22 2372
- 删除索引DROP INDEX 索引名 ON 表名；--删除主键索引ALTER TABLE 表名 DROP PRIMARY KEY;索引的成本存储空间的开销，是指索引|需要单独占用存储空间。数据操作上的开销，是指一旦数据表有变动，无论是插入一条新数据，还是删除一条旧的数据，甚至是修改数据，如果涉及索引字段，都需要对索引本身进行修改，以确保索弓|能够指向正确的记录。因此，索引也不是越多越好，创建索引有存储开销和操作开销，需要综合考虑。
MySQL学习笔记整理（上部）
霁晨晨晨的博客
05-01 3万+
以下是我整理了关于MySQL数据库的常用命令和使用场景，一些基本的概念，比如索引、约束和事务，以及常见的问题和解决方案，欢迎大家浏览并留言，若有错误的地方请大家指正。
MySQL学习笔记整理（下部）
热门推荐
霁晨晨晨的博客
05-01 3万+
以下是我整理了关于MySQL数据库的常用命令和使用场景，一些基本的概念，比如索引、约束和事务，以及常见的问题和解决方案，欢迎大家浏览并留言，若有错误的地方请大家指正。
MySQL学习笔记
m0_70415287的博客
06-06 3085
MySQL初级知识学习
MySql学习笔记（详细）
05-18
### MySQL学习笔记（详细） #### 一、数据库操作 MySQL是一种关系型数据库管理系统，通过一系列命令来进行数据库的管理和数据的处理。以下是一些常见的数据库操作命令： - **连接MySQL服务器**： - `mysql -u ...
MySQL学习笔记 MySQL学习笔记
09-28
MySQL是世界上最受欢迎的关系型数据库管理系统之一，尤其在Web应用...学习MySQL不仅涉及理论知识，还需要大量的实践操作来熟悉其语法和功能。掌握MySQL能让你在数据管理、Web开发和系统集成等领域具备更强的竞争力。
MySQL进阶篇学习笔记
05-14
黑马MySQL课程总结的学习笔记
MySQL学习笔记2-高级查询与存储.md
08-22
总结来说，通过对 MySQL 中的高级查询技术和存储过程的学习与实践，我们不仅能够更高效地管理和查询数据库，还能够编写出更加复杂且功能强大的应用程序。无论是对于数据库管理员还是开发人员来说，掌握这些技能都是...
MySQL中处理JSON数据:大数据分析的新方向
Chujun123528的博客
08-18 1153
灵活性：JSON数据类型允许你在单个列中存储复杂的数据结构，如数组和嵌套对象，这减少了需要创建多个表来存储相关信息的必要性。查询能力：MySQL为JSON数据类型提供了一系列内置的函数，如, , , , , 等，使得查询和修改JSON数据变得非常便捷。索引支持：MySQL支持为JSON文档中的特定路径创建虚拟列（Generated Columns），然后可以在这些虚拟列上创建索引，从而加速基于JSON内容的查询。性能优化：随着MySQL的不断改进，对JSON数据的处理性能也在不断提高。虽然与专门的NoSQL
Java学习Day28：Mysql 第二章：斗气大陆
weixin_51721783的博客
08-16 989
设计数据库时，某一个字段属于一张表，但它同时出现另一个或多个表中，且完全等同于它在其本来所属表的意义表示尽量遵循范式理论的规约，尽可能少的冗余字段，让数据库设计看起来精致合理加入冗余字段，减少join，让数据库执行性能更好添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。索引的优点大大的提高查询速度可以显著的减少查询中分组和排序的时间。索引的缺点创建索引和维护索引需要时间，而且数据量越大时间越长。
MySQL单表条件查询语句
最新发布
weixin_72431427的博客
08-19 322
MySQL中的一些常见的单表条件查询语句。
SpringBoot MySQL BinLog 监听数据变化(多库多表)
掐指一算乀缺钱
08-19 485
SpringBoot MySQL BinLog 监听数据变化(多库多表) 库.表,库1.表1,库1.表2
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值