mysql 查询查询组合出来的变量长度_MySQL总结

最新推荐文章于 2022-09-29 14:25:30 发布
最新推荐文章于 2022-09-29 14:25:30 发布
阅读量545 收藏
点赞数
文章标签： mysql 查询查询组合出来的变量长度
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_33931886/article/details/113628092
版权

MySQL基础回顾

1、group by 查询

select 字段名，分组名
from 表名
where 筛选条件  // 分组前筛选
group by 分组名
having 分组后筛选条件  //分组后筛选
order by 排序字段

2、sq99连接查询

select 字段名
from 表名 as 别名
【连接类型】join 表名 as 别名
on 连接条件
where 筛选条件
order by 排序字段

其中【连接类型包括】

  • inner //内连接，等值连接
  • left //外连接，主表有、从表没有；左表是主表
  • right //右边是主表
  • full
  • cross

3、子查询与联合查询

select * 
from 表名
where id in/any/all (子查询)

union [all]：联合查询把不同的查询结果进行并集。

4、delete与truncate区别

delete可以删除部分行，通过where进行筛选。

truncate则不可以，是清楚整张表，相当于格式化。

5、表的复制

create table my_copy like 目标表   //复制结构
create table my_copy 
select * from 目标表               //复制结构和

6、约束

  • 非空
  • 主键 //主键默认非空且唯一，自动会创建索引，可以组合
  • 唯一 //可以为空，可以组合
  • 检查
  • 默认
  • 外键 //作用于表上，而不是列上。会自动创建索引。
【CONSTRAINT 约束名】  约束类型（字段名）

7、视图

  • 多个地方用到同样的查询且SQL相对复杂。
  • 不保存结果，只保存SQL。

优点：重用SQL，保护和封装数据。

可以进行插入或者修改，不建议这样，视图最好只有‘只读’权限。

create view 视图名
as
查询语句；

8、级联

  • 级联删除
  • 级联置空

9、变量

  • 系统变量：全局变量、会话变量
  • 自定义变量：用户变量、局部变量
//查看系统变量
SHOW [GLOBAL|SESSION] VARIABLES;  //不填写默认是会话变量

//查看指定系统变量
SELECT @@GOLOBAL|SESSION.系统变量名；   //不填写默认是会话变量

//设置指定系统变量
SET @@GOLOBAL|SESSION.系统变量名 = 值；   //不填写默认是会话变量

变量修改后重启服务则恢复默认初始值，除非修改配置文件。

10、存储过程和函数

可以理解为一组方法

delimeter $
create procedure 存储过程名（IN|OUT 变量名 变量类型）

begin
    SQL语句
end $

//调用
CALL 存储过程名（参数列表）

函数：有且只有一个返回

create function 函数名（参数类型） returns 返回类型

begin
    函数体
end

第一章 MySQL架构与历史

1、MySQL逻辑架构图

4d78fe5b53fe50c1396fe04bba068a0b.png
  • 最上层：比较通用的一个设计。
  • 第二层：核心功能处在这里，包括查询解析、分析、优化、缓存和所有内置函数。跨存储引擎的功能也在这一层实现，包括存储过程、视图、触发器等。
  • 最下层：存储引擎，负责数据的存出和提取。不同存储引擎各有自己的优劣势。通过提供存储引擎API，来屏蔽不同存储引擎的差异。

连接与鉴权

每个客户端都会在服务器进程中拥有一个线程，较高版本的MySQL版本提供了线程池插件，实现用较少的线程来支持大量的连接。

连接后，执行某操作还会进行鉴权。

优化与执行

第二层会解析并分析查询语句、优化查询语句、看是否命中缓存等。

2、并发控制

主要讲服务器层面 与 存储引擎层面 的并发控制。

读写锁

  • 共享锁，又称作 读锁。
  • 排它锁，又称作 写锁。

锁粒度

  • 行级锁：开销大、但是并发量高。大部分数据库采用这种形式。在存储引擎层面实现。
  • 表锁：开销小、但是并发量低。在服务器层面实现，需要手动加锁来锁定整张表。

3、事务

  • 原子性：一起成功 or 一起失败。
  • 一致性：数据库会从一个一致性状态 到达 另外一个一致性状态。
  • 隔离性：在一个事务结束之前，其他事务对其修改是看不到的。
  • 持久性：一旦事务提交，其数据会持久的存在数据库中。

事务需要额外的很大开销，所以说如果不需要事务，可以选择不支持事务的存储引擎。当前不支持事物的存储引擎，也可以通过LOCK TABLES为程序提供一定的保护（服务器层面）。

隔离级别

SQL中定义了四种隔离级别，隔离级别越低，开销越低，并发越高。

c8cfc040841a3386642f44b08a6d0944.png

大多数数据库的隔离级别是 读已提交，但是MySQL采用 可重复读。可重复读 会带来幻读这一问题，MySQL采用多版本并发（MVCC）来解决这一问题。

死锁与日志

两个事务互相等待对方释放资源。InnoDB处理方式，持有最少行级排它锁的事务进行回滚。

事务日志可以提高事务性能，修改数据只需要修改内存中数据，并且把修改记录行为记录在日志中，持久化在硬盘中即可。

4、多版本并发控制MVCC

通过增加两个隐藏列用于解决幻读情况。两列都是全局自增系统版本号。

5、存储引擎

InnoDB: 支持事务。聚簇索引。

MyISAM: 不支持事务（行级锁），可以在服务器层面锁定整张表。

存储引擎选择：

  • 1、是否支持事务。不需要的情况下可以选择MyISAM,比如日志系统。
  • 2、备份
  • 3、崩溃恢复
  • 4、特有特性

常用SQL语句：

ALTER TABLE mytable ENGINE = InnoDB;  //修改表的存储引擎

mysqldump //备份数据库

参考文章：

数据库事务隔离级别 - 分析脏读 & 不可重复读 & 幻读​www.cnblogs.com
edc7c99361ce1afb44618694ff793f16.png

第二章 MySQL基准测试

1、测试目标

  • 吞吐量 ：单位时间内数据库处理的事务量。
  • 响应时间或延迟 : 可以拿95%的相应时间来做参考。
  • 可扩展性：

2、设计基准测试

首先是提出问题并且明确目标

3、基准测试工具

集成测试工具：测试整个Web服务接口

  • ab
  • http_load
  • JMeter

单组键测试工具：直接测试MySQL

  • mysqlslap ：
  • super smack ：一款用于MySQL和PostgreSQL的基准测试工具。
  • sysbench : 一款多线程系统压测工具。

第三章 服务器性能分析

1、问题：

  • 如何确认服务器达到了最佳性能状态。
  • 找出某条语句为什么执行的不够快。
  • 如何解决这些疑难杂症。

常用指令：

SHOW VARIABLES LIKE 'PROFILING';

SET PROFILING = 1;

SHOW PROFILES;

SHOW PROFILE FOR QUERY 1;

SHOW STATUS;

SHOW PROCESSLIST;

第四章 Schema与数据类型优化

1、Schema涉及原则

  • 更小的通常更好。
  • 简单就好： 整形比字符串的性能好，所以日期、时间和IP等，最好用整形存储。
  • 尽量避免NULL： 对建立索引不利，索引列最好不要有NULL。

类型：

整形：tinyint、smallint、mediumint、int、bigint
实数：decimal(精度高、需要存储空间大)、float、double
字符串：varchar(变长、字符串越短，占用空间越小；适合用在修改少、最大长度远远大于平均长度场景下)
       char（定长，适用于经常修改场景，密码这种定长场景、效率高）
BLOB与TEXT: 尽量少使用。
ENUM：效率比较高。
时间与日期类型：DateTime-效率低点，存储时间跨度长；TimeStamp:1970-01-01以来的，不同时区看到值不同
Bit：

2、范式与反范式涉及

  • 平衡存储与查询性能的杠杆
  • 是否引入缓存表与汇总表
  • ALTER TABLE是非常耗时的，要注意

第五章 创建高性能索引

1、索引基础

  • BTree索引
  • Hash索引
  • full-text全文索引
  • R-Tree索引

2、是否创建索引情况

  • 常用查询和排序的字段需要创建。
  • 数据量小，区分度低，常修改的则不要建索引。

3、执行报告的字段类型

  • id 相同则按序加载，不同则id越大，优先级越高。
  • select_type 查询类型，主要用于区别普通查询，联合查询，子查询等复杂查询。
    • SIMPLE
    • PRIMARY //带有子查询的最外层
    • SUBQUERY
    • DERIVED //派生出来的
    • UNION
    • UNION RESULT
  • table 哪张表
  • type 访问类型
    • system // 一张表且只有一条数据
    • const // 唯一索引，查询一条数据
    • eq_ref // 外键
    • ref // 非唯一性索引扫描，返回匹配某个单独值的所有行。
    • range // 一个索引的范围 30-60之间等
    • index // 索引读全部
    • all //直接全表，没有走索引
  • possible_keys //显示可能应用在这张表上的索引，一个或多个。
  • key // 实际用到的索引。覆盖索引（查询的col1,col2...与联合索引的大小和顺序相同）
  • key_len // 索引字段的最大可能长度。越短越好。
  • ref // 索引中用于筛选的值
  • rows // 每张表有多少行被优化器查询
  • extra // 额外重要的信息
    • using filesort // 排序时，索引没用上，需要内存内单独排序。
    • using temporary // 使用了临时表保存中间结果，对查询结果排序时常用到和group by。
    • using index // 表明使用到了覆盖索引。若同时出现using where,代表索引被用来执行值的查找；若没有出现，代表索引用来读取数据而非执行查找动作（即不去数据文件中）。
    • using where //表示使用到了where筛选
    • using join buffer //使用了连接缓存
    • impossible where // 筛选条件总是false
    • select tables optimized away //
    • distinct // 匹配一个值后，不再查找同样的值

4、索引案例分析

最左匹配原则
索引列不要设计计算、类型转换（手动或自动）等操作
范围之后的索引全部失效
尽量使用覆盖索引进行查询
如果左边是通配符，最好采用覆盖索引查，type级别是index。
字符串不加单引号索引失效（相当于在索引字段上进行类型转换）
少用or，用它链接时，索引失效。
不等于、IS NULL、IS NOT NULL会使索引失效，尽量避免。

左连接给右表加索引
小结果集驱动大结果集
优先优化嵌套循环的内层循环


小表驱动大表，使用in
大表驱动小表，使用exist     //要深刻理解in与exist的区别

5、Order By的优化

排序方式：

  • 文件排序 using filesort
  • 索引排序 using index

文件排序两种算法：

  • 双路算法、两次I/O
  • 单路算法，一次I/O
单路算法，一次I/O，但是耗内存，是把所有查询的字段加载到buffer，按照排序列进行排序。
所以要把握好buffer的大小，避免多次I/O加载。

调优步骤

  • select * 是大忌讳，需要哪些字段就查哪些字段。
    • Query的字段大小总和小于max_length_for_sort_data时，且不是TEXT|BLOB类型时，会采用优化后的排序方法，即单路排序算法。
    • 两种算法都可能超过sort_buffer的值，导致产生合并排序。
  • 尝试提高sort_buffer_size的值。
  • 尝试提高max_length_for_sort_data的值。但是设置的太高，又可能触发sort_buffer。。。

Group By的优化与Order By基本相同。不同在于

  • 先排序再分组
  • 用不上索引时，适当调大系统参数
  • where是分组前筛选，having是分组后筛选

慢查询日志

  • 开启慢查询日志
  • 设置阈值、慢查询日志文件位置
  • 查看相关信息
  • 工具：mysqldumpslow

6、show profile

show profiles;

show profile cpu, block io for query id;

7、读写锁

MyISAM（偏读）：给表加读锁后； show open tables ：查看表的锁状态。

  • 当前session可以读。
  • 当前session写自己表出错。
  • 当前session读其他表出错。
  • 其他session可以读。
  • 其他session若修改，则会一直阻塞。

给表加写锁

  • 当前session可以写。
  • 当前session可以读。
  • 当前session读其他表出错。
  • 其他session读则阻塞。
  • 其他session写则阻塞。

InnoDB：支持事务、行级锁。

间隙锁：修改字段的条件是一个范围，那么会对这个范围的所有行加锁，包括不存在的行。若这时有其他session进行增加数据，则会阻塞。

// 锁定一行
select * from table where xxx  for update;

8、主从复制

杭亮
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql function select 赋值_MySQL变量（二十五）
weixin_39593354的博客
12-05 1653
在本教程中，您将学习如何在SQL语句中使用MySQL用户定义的变量。MySQL用户定义变量简介有时候，您希望将值从SQL语句传递给另一个SQL语句。为此，您可将该值存储在第一个语句中的MySQL用户定义的变量中，并在随后的语句中引用它。要创建用户定义的变量，请使用格式@variable_name，其中variable_name由字母数字字符组成。 用户自定义变量的最大长度为64个字符(MySQL ...
mysql查询的长度限制_SQL查询的长度的实际限制(特别是MySQL)
weixin_31111107的博客
01-26 2642
阅读你的查询让我想玩RPG.这绝对不会太长.只要格式很好,我会说实际的限制是大约100行.之后,你最好不要将子查询分解成意见,以防止双眼过渡.我已经处理了一些1000行的查询,这很难调试.顺便提一句,我可以建议一个重新格式化的版本吗？这主要是为了证明格式的重要性;我相信这将会更容易理解.select *from4e_magic_items mi,4e_magic_item_levels mil,4...
MySQL 变量的详细讲解
最新发布
weixin_61635597的博客
09-29 3584
MySQL 有关变量的详细讲解
MySQL 变量
非著名程序猿
05-15 4123
创建用户定义的变量，请使用格式@variable_name，其中variable_name包含字母数字字符。从MySQL 5.7.5开始，用户定义变量的最大长度为64个字符 用户定义的变量不区分大小写。这意味着@id和@ID是一样的 赋值 方式1 SET SET @variable_name := value; # or SET @variable_name = value; SET @num = 2; SELECT @num as test; 方式2 SELECT语句 在这种情况下，必须使用
mysql中varchar的最大长度
weixin_33695082的博客
10-29 123
1、限制规则 字段的限制在字段定义的时候有以下规则：a)存储限制 varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字节），因此最大长度不能超过65535。b)编码长度限制 字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766; 字符类型若为utf8，每个字符最多占3个字节，最大长度不...
MySQL查询优化--调整内部变量的详解
09-10
MySQL查询优化是一个重要的主题，尤其是对于那些处理大量数据和高并发访问的应用来说。调整内部变量是优化策略的一个关键部分，因为它可以直接影响到数据库的性能和稳定性。以下将详细讲解几个关键的MySQL内部变量...
MySQL查询优化–调整内部变量的详解
01-19
MySQL是如此的开放，所以可轻松地进一步调整其缺省设置以获得更优的性能及稳定性。需要优化的一些关键变量如下： 　改变索引缓冲区长度(key_...　当一个查询不断地扫描某一个表，MySQL会为它分配一段内存缓冲区。read_b
如何修改Mysql中group_concat的长度限制
09-09
总结来说，`GROUP_CONCAT`是MySQL中的一个强大功能，但默认长度限制可能在处理大数据时成为瓶颈。通过调整`group_concat_max_len`系统变量，你可以根据需要扩展其返回值的长度。同时，利用其提供的选项，如自定义...
关于MySQL中的查询开销查看方法详解
09-09
你可以通过查询`last_query_cost`状态变量来获取最近查询的预估成本，如示例代码所示： ```sql mysql> select * from t_message limit 10; mysql> show status like 'last_query_cost'; ``` `Last_query_cost`的值...
mysql检测变量长度知识小记
qq_25175063的博客
09-13 748
一、今天在写东西时使用了mysl中的length（）方法来检测数据长度，发现一个问题：当变量为纯英文字符时没有问题，但是中间夹杂着汉字时，这个结果就不一样了。这是因为在mysql中一个汉字的length为3导致的。 二、我们可以采用下面的方式来进行处理。      引入char_length（）方法。与length（）不同的是char_length（）是把汉字也当做是一个长度的。 也欢迎登录...
mysql中数据类型的长度
qimo601的专栏
08-06 1093
  因为最近要登记一些长度较大的数值，今天才仔细的查看了bigint的范围。 以前都忽略了mysql中数据长度的上限问题。   在mysql中创建表时，varchar类型必须指定长度，int类型可以不指定长度。当然，在mysql中使用界面创建表（不使用sql语句创建表），若不指定长度，它会为你指定默认长度，下面对mysql中常见的几种数据类型的默认长度和最大长度进行了总结。  ...
MySQL--字符串长度
热门推荐
while(1) { smile(); }
11-24 10万+
常用函数有char_length、length、bit_length，它们针对不同的字符编码处理方式不一样。 总的来说： char_length字符显示的个数； length 字符在当前编码下存储，所占的字节数。 bit_length 字符在当前编码下存储，所占的bit，也就是length*8GBK 编码select char_length('中国'); -- 2 select lengt
mysql 用户变量（@）和系统变量（@@ show variables 和 show global variables 区别）
码农崛起
07-02 9068
http://www.uuboku.com/392.html用户变量用户变量是针对当前登录MYSQL的用户的私有变量，可以先在用户变量中保存值然后在以后引用它；这样可以将值从一个语句传递到另一个语句。用户变量与连接有关。也就是说，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。用户变量的形式为@var_name，其中变量名var_name可以由当前...
MySQL之定义变量
编程学习者的博客
09-10 3万+
1.定义用户变量(或者赋值) set @变量名=值 -- 创建自定义用户变量，当当前的用断开连接的时候就会消失 set @value=1; 2.查询用户变量值 select @变量 -- 查询定义的数据 select @value; 3.在当前的存储过程和函数中使用变量 定义变量： declare 变量名[,变量名2...] 变量类型 [default 默认值] 赋值变量： set 变量名1...
(超实用、简单明了)Mysql变量定义与赋值
FAIRYTAIL的博客
03-25 1万+
最近在使用存储过程定时创建分区的时候，运行时经常报错，原因都是因为变量的定义或者赋值问题导致的，在此记录一下MySQL的变量定义及赋值，方便自己和大家查阅，各位看到此博客的小伙伴，如有不对的地方请及时通过私信我或者评论此博客的方式指出，以免误人子弟。多谢！ 另外，这是一个系列文章，最终我们将实用MySQL定时器和存储过程，实现定时自动创建分区功能，这是在此之前需要掌握的知识点，感兴趣的敬请关注。 目录 局部变量 声明变量 变量赋值 用户变量 变量赋值 系统变量 查看变量 修改变量
MYSQL变量定义+组合变量
bugua3542的博客
04-21 620
变量作用域 变量作用域：变量能够使用的区域范围 局部作用域 使用declare关键字声明（在结构体内：函数/存储过程/触发器），而且只能在结构体内部使用。 declare关键字声明的变量没有任何符号修饰，就是普通字符串，如果在外部能够访问该变量，系统会自动认为是字段。 会话作用域 用户定义的：使用@符号定义的变量，使用set关键字。 会话作用域：在当前用户当次连接有效，只要在本连接之中，任何地方都可以使用（可以在结构内部，也可以跨库） 会话变量可以在函数内部使用 mysql中变量不用事前申
mysql 求数据的长度_mysql 如何求数据的长度
weixin_39643255的博客
02-01 569
mysql求数据长度的方法：1、使用“LENGTH”函数来获取字符串长度，语法如“SELECT LENGTH('字符')”；2、通过“CHAR_LENGTH”函数查看字符串长度，语法如“SELECT CHAR_LENGTH('字符')”。推荐：《mysql视频教程》MySql计算字段的长度用户账号有用中文字符，查找所有含有中文字符的账号SELECT member_name FROM table_m...
mysql性能优化-慢查询分析、优化索引和配置.doc
11-22
总结来说，MySQL性能优化是一个综合性的任务，需要结合硬件条件、查询优化和配置调整多方面进行。理解并掌握这些方法，能够有效地提升数据库系统的稳定性和效率，满足高并发、大数据量的业务需求。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值