架构视界
主页
企业架构
  • 方案总览
  • 业财一体
  • 企业中台
  • 组织与绩效
  • 流程治理
数据收集与分析
  • 转型总览
  • 信息化阶段
  • 数字化解决方案
  • 智数化解决方案
  • 智能化解决方案
  • 产品设计
  • 项目管理
全栈软件工程
AI 原生软件工程
方法论
博客
  • 资源导航
  • 效率工具
  • 展示专区
主页
企业架构
  • 方案总览
  • 业财一体
  • 企业中台
  • 组织与绩效
  • 流程治理
数据收集与分析
  • 转型总览
  • 信息化阶段
  • 数字化解决方案
  • 智数化解决方案
  • 智能化解决方案
  • 产品设计
  • 项目管理
全栈软件工程
AI 原生软件工程
方法论
博客
  • 资源导航
  • 效率工具
  • 展示专区
  • 标签索引
  • 2024年

    • 勇闯大A:美债与美元周期
    • 勇闯大A:交易纪律与自我管理
    • 勇闯大A:交易系统分层
    • 勇闯大A:买卖点与止损规则
    • 勇闯大A:债券与资金流向
    • 勇闯大A:微观、宏观与周期
    • Mac 开发环境:新机配置清单
    • 勇闯大A:内外盘与换手率
    • 会计管理:财报阅读与管理报表
    • 会计基础:记账原理与分录
    • 财务入门:财务体系全景
  • 2023年

    • 境外投资:泰国 BOI 申请实务
  • 2022年

    • 企业架构:故障管理与分级响应
    • 企业架构:技术债务的识别、度量与偿还
  • 2021年

    • Python 数据分析:员工分布可视化
    • Python 数据分析:IP 访问折线图
    • Python 数据分析:IP 词云图
    • Redis:RediSearch 全文检索
    • Rust 基础:第一个 CLI 程序
    • Rust 基础:所有权机制
    • Rust 基础:函数与控制流
    • Rust 基础:变量与数据类型
    • Rust 基础:入门与开发环境
    • 企业架构:分布式系统设计
    • 企业架构:编程式权限设计
    • JVM 调优:启动参数与 GC 日志
    • MyBatis 批量插入:三种写法对比
    • 测试:Spring Boot 分层 Mock
    • Redis:布隆过滤器判断元素存在
    • Jenkins:安装配置与应用部署
    • WebClient:非阻塞 HTTP 客户端
    • Drone:Docker 部署轻量 CI
    • Spring AOP:操作审计日志落库
    • Redisson:分布式锁与 RedLock
    • MyBatis 拦截器:雪花算法主键
    • Netty:线程模型与网络编程
    • Redis:SCAN 游标遍历大键空间
    • Jenkins:单机部署与升级脚本
    • Mac:Neo4j 与 Jupyter 安装
    • JavaFX:OpenJDK 11 环境配置
    • Mac:用 jenv 管理 JDK 版本
    • Redis:有序集合实现延时队列
    • Spring Boot 日志:MDC 串联链路
    • 定时任务:Spring 调度与 Cron
    • GoAccess:Nginx 日志分析
    • MyBatis 拦截器:慢 SQL 分析
    • Lucene:倒排索引与中文分词
    • 分布式锁:三种实现方案对比
    • 学习技巧:减少学习排斥心理
    • 动态数据源:多租户数据路由
    • Compose:Spring Boot 三件套
    • Java 阻塞队列:源码与用法
    • Redis:Docker Compose 哨兵
    • Redis:Docker Compose 主从
    • 网络通信:TCP/IP 与 HTTP 基础
  • 2020年

    • 延时队列:DelayQueue 与 Redis 实现
    • MySQL 基础:员工库导入实践
    • 雪花算法:分布式 ID 生成
    • Redis:地理位置查询
    • 抓包:Wireshark 与 Tcpdump
    • SPI:JDK 与 Spring 扩展机制
    • ZooKeeper:概念与集群搭建
    • Spring Boot:集成 JMH 基准测试
    • Wireshark:抓包工具入门
    • Compose:Redis 与 MySQL
    • CentOS 7:Docker 安装部署
    • Netty:源码阅读环境搭建
    • MySQL 基础:SQL 常见用法
    • CentOS 7:Selenium 运行环境
    • Nginx:HTTPS 证书配置
    • JMH:基准测试与 Spring Boot 集成
    • Spring Boot:指定 Tomcat 版本
    • Eureka:实例上下线告警
    • 钉钉告警:应用异常与实例上下线
    • JVM:内存布局、类加载与调优
    • Git:rebase 合并多次提交
    • CentOS 7:OpenResty 部署
  • 2019年

    • Redis CLI:macOS 安装与使用
    • Nginx:访问日志格式
    • 工程规范:可维护代码的写法
    • IntelliJ IDEA:常用插件
    • SkyWalking:源码阅读环境搭建
    • Spring Boot:Redis 超时配置
    • gRPC:ProtoBuf 定义与代码生成
    • Arthas:Java 线上问题诊断
    • Redis:缓存穿透与击穿防护
    • Docker 部署:Elasticsearch
    • Docker 部署:MySQL 5.7 单机
    • Spring Redis:字符串命令封装
    • Docker 部署:ZooKeeper 运行
    • Docker 部署:Redis 单机运行
    • Dubbo:Spring Boot 微服务集成
    • CentOS 7:CMake 源码编译安装
    • 性能度量:吞吐量与 QPS 指标
    • Java 递归:从笔试题到经典问题
    • CentOS 7:ELK 日志平台部署
    • CentOS 7:SonarQube 部署
    • Selenium:Chrome 浏览器自动化
    • JJWT:令牌生成与解析测试
    • Spring 源码:阅读环境搭建
    • JUnit 4:注解、断言与参数化
    • JWT:令牌结构与签名机制
    • FastDFS:架构与文件存取机制
    • XPath:XML 文档节点定位
    • Redis 基础:锁、队列与事务
    • Redis 基础:数据类型与命令
    • MyBatis:Slf4j 日志接入
    • MyBatis:H2 单元测试与 SQL 日志
    • 源码阅读:开源项目环境搭建
    • Git 配置:命令清单与作用域
    • Java 核心:面向对象与基础类型
    • Java 核心:关键字、异常与反射
    • Java 核心:多线程基础
    • Java 核心:同步关键字与线程池
    • Java 核心:锁与并发工具
    • Java 核心:语言基础与并发
    • Dubbo:远程调用与服务治理
    • Java 集合:框架总览与实现对比
    • Maven:生命周期与常用命令
    • MyBatis:执行流程与插件机制
    • Spring:IoC 容器与依赖注入
    • Spring:AOP 与自动装配
    • Spring:事务与启动流程
    • Spring:IoC、AOP 与事务
    • Spring MVC:请求分发与注解
    • MySQL:架构与索引
    • MySQL:事务、锁与 MVCC
    • MySQL:日志与 SQL 优化
    • MySQL:架构、事务与优化
    • Redis 基础:持久化与线程模型
  • 2018年

    • Java HashMap:扩容机制与哈希冲突
    • Java 集合:HashSet 去重原理
    • Java 基础:交换两个变量的值
    • Spring MVC:零 web.xml 改造
    • Mac 开发环境:新机配置清单
    • Log4j:日志分级与按业务拆分
    • Modbus:工业通信协议与 RTU 帧
    • MySQL 基础:子查询与连接查询
    • MySQL 基础:库与表操作
    • MySQL 基础:表操作与查询
    • MySQL 基础:约束、引擎与数据类型
    • MySQL 基础:库表、约束与类型
    • Java 集合:Vector 与 Stack
    • Java 集合:Vector 与 Stack
    • RabbitMQ:CentOS 7 单机部署
    • Redis:CentOS 7 单机部署
    • MongoDB:CentOS 7 单机部署
    • Linux:常用系统与运维命令
    • 注册中心:Eureka 与 ZooKeeper
    • MySQL:CentOS 7 单机部署
    • Git 分支:主分支与临时分支
    • CentOS 7:Java 开发环境部署
    • Java 集合:LinkedList 双向链表
    • Java 集合:ArrayList 动态数组
    • Spring AOP:注解方式实现切面
← 返回博客

MySQL:日志与 SQL 优化

2019年1月1日 #数据与存储 #MySQL

系列文章:MySQL:架构、事务与优化 的第 3 篇。

SQL语句的执行顺序

我们在编写一个查询语句的时候

SELECT DISTINCT
    < select_list >
FROM
    < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
    < where_condition >
GROUP BY
    < group_by_list >
HAVING
    < having_condition >
ORDER BY
    < order_by_condition >
LIMIT < limit_number >

FROM

  • 首先,对 SELECT 语句执行查询时,对FROM 关键字两边的表执行连接,会形成笛卡尔积,这时候会产生一个虚表VT1(virtual table)

首先先来解释一下什么是笛卡尔积

现在我们有两个集合 A = {0,1} , B = {2,3,4}

那么,集合 A * B 得到的结果就是

A * B = {(0,2)、(1,2)、(0,3)、(1,3)、(0,4)、(1,4)};

B * A = {(2,0)、{2,1}、{3,0}、{3,1}、{4,0}、(4,1)};

上面 A * B 和 B * A 的结果就可以称为两个集合相乘的 笛卡尔积

我们可以得出结论,A 集合和 B 集合相乘,包含了集合 A 中的元素和集合 B 中元素之和,也就是 A 元素的个数 * B 元素的个数

ON

  • 然后对 FROM 连接的结果进行 ON 筛选,创建 VT2,把符合记录的条件存在 VT2 中。

JOIN

第三步,如果是 OUTER JOIN(left join、right join) ,那么这一步就将添加外部行,如果是 left join 就把 ON 过滤条件的左表添加进来,如果是 right join ,就把右表添加进来,从而生成新的虚拟表 VT3。

WHERE

  • 第四步,是执行 WHERE 过滤器,对上一步生产的虚拟表引用 WHERE 筛选,生成虚拟表 VT4。

  • WHERE 和 ON 的区别

    • 如果有外部列,ON 针对过滤的是关联表,主表(保留表)会返回所有的列;
    • 如果没有添加外部列,两者的效果是一样的;
  • 应用

    • 对主表的过滤应该使用 WHERE;
    • 对于关联表,先条件查询后连接则用 ON,先连接后条件查询则用 WHERE;

GROUP BY

  • 根据 group by 字句中的列,会对 VT4 中的记录进行分组操作,产生虚拟机表 VT5。果应用了group by,那么后面的所有步骤都只能得到的 VT5 的列或者是聚合函数(count、sum、avg等)。

HAVING

  • 紧跟着 GROUP BY 字句后面的是 HAVING,使用 HAVING 过滤,会把符合条件的放在 VT6

SELECT

  • 第七步才会执行 SELECT 语句,将 VT6 中的结果按照 SELECT 进行刷选,生成 VT7

DISTINCT

  • 在第八步中,会对 TV7 生成的记录进行去重操作,生成 VT8。事实上如果应用了 group by 子句那么 distinct 是多余的,原因同样在于,分组的时候是将列中唯一的值分成一组,同时只为每一组返回一行记录,那么所以的记录都将是不相同的。

ORDER BY

  • 应用 order by 子句。按照 order_by_condition 排序 VT8,此时返回的一个游标,而不是虚拟表。sql 是基于集合的理论的,集合不会预先对他的行排序,它只是成员的逻辑集合,成员的顺序是无关紧要的。

ORDER BY注意事项

  • 排序的列名:ORDER BY 后面可以有一个或多个列名,如果是多个列名进行排序,会按照后面第一个列先进行排序,当第一列的值相同的时候,再按照第二列进行排序,以此类推。

  • 排序的顺序:ORDER BY 后面可以注明排序规则,ASC 代表递增排序,DESC 代表递减排序。如果没有注明排序规则,默认情况下是按照 ASC 递增排序。我们很容易理解 ORDER BY 对数值类型字段的排序规则,但如果排序字段类型为文本数据,就需要参考数据库的设置方式了,这样才能判断 A 是在 B 之前,还是在 B 之后。比如使用 MySQL 在创建字段的时候设置为 BINARY 属性,就代表区分大小写。

  • 非选择列排序:ORDER BY 可以使用非选择列进行排序,所以即使在 SELECT 后面没有这个列名,你同样可以放到 ORDER BY 后面进行排序。

  • ORDER BY 的位置:ORDER BY 通常位于 SELECT 语句的最后一条子句,否则会报错。

查询优化

  • 避免使用 select * 而是查询需要的字段,这样可以提升速度,以及减少网络传输的带宽压力。

  • 尽量使用 Join 语句来替代子查询,因为子查询是嵌套查询,而嵌套查询会新创建一张临时表,而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间,但 Join 语句并不会创建临时表,因此性能会更高。

  • 尽量使用覆盖查询。通过创建联合主键的方式减少回表操作。

  • 不要在列上进行运算操作

  • 适当增加冗余字段,防止多度连表查询,通常不建议join超过三张表。

  • 最小数据长度,一般说来数据库的表越小,那么它的查询速度就越快,因此为了提高表的效率

  • 使用最简单数据类型,能使用 int 类型就不要使用 varchar 类型,因为 int 类型比 varchar 类型的查询效率更高。

  • 尽量少定义 text 类型,text 类型的查询效率很低,如果必须要使用 text 定义字段,可以把此字段分离成子表,需要查询此字段时使用联合查询,这样可以提高主表的查询效率。

  • 适当分表、分库策略,分表和分库方案也是我们经常说的垂直分隔(分表)和水平分隔(分库)。

    分表是指当一张表中的字段更多时,可以尝试将一张大表拆分为多张子表,把使用比较高频的主信息放入主表中,其他的放入子表,这样我们大部分查询只需要查询字段更少的主表就可以完成了,从而有效的提高了查询的效率。

    分库是指将一个数据库分为多个数据库。比如我们把一个数据库拆分为了多个数据库,一个主数据库用于写入和修改数据,其他的用于同步主数据并提供给客户端查询,这样就把一个库的读和写的压力,分摊给了多个库,从而提高了数据库整体的运行效率。

SQL优化explain

  • explain出来的各种item的意义;
select_type
  • 表示查询中每个select子句的类型,有几种值:simple(表示简单的select,没有union和子查询),primary(有子查询,最外面的select查询就是primary),union(union中的第二个或随后的select查询,不依赖外部查询结果),dependent union(union中的第二个或随后的select查询,依赖外部查询结果)
type
  • 表示MySQL在表中找到所需行的方式,又称“访问类型”,有几种值:system(表仅有一行(=系统表),这是const连接类型的一个特例),const(常量查询), ref(非唯一索引访问,只有普通索引),eq_ref(使用唯一索引或组件查询),all(全表查询),index(根据索引查询全表),range(范围查询)
possible_keys
  • 指出MySQL能使用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
key
  • 显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL
key_len
  • 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
ref
  • 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
Extra
  • 包含不适合在其他列中显示但十分重要的额外信息

  • profile的意义以及使用场景;

查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等。

innodb的读写参数优化

  • 读取参数
global buffer pool以及 local buffer;
  • 写入参数;
innodb_flush_log_at_trx_commit

innodb_buffer_pool_size
  • 与IO相关的参数;
innodb_write_io_threads = 8

innodb_read_io_threads = 8

innodb_thread_concurrency = 0

视图

  • 视图是一种虚拟的表,具有和物理表相同的功能。可以对视图进行增,改,查,操作,试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易,相比多表查询。
  • 只暴露部分字段给访问者,所以就建一个虚表,就是视图。
  • 查询的数据来源于不同的表,而查询者希望以统一的方式查询,这样也可以建立一个视图,把多个表查询结果联合起来,查询者只需要直接从视图中获取数据,不必考虑数据来源于不同表所带来的差异。

存储过程

存储过程是一些预编译的SQL语句。

  • 更加直白的理解:存储过程可以说是一个记录集,它是由一些T-SQL语句组成的代码块,这些T-SQL语句代码像一个方法一样实现一些功能(对单表或多表的增删改查),然后再给这个代码块取一个名字,在用到这个功能的时候调用他就行了。
  • 存储过程是一个预编译的代码块,执行效率比较高,一个存储过程替代大量T_SQL语句 ,可以降低网络通信量,提高通信速率,可以一定程度上确保数据安全。

Mysql Master slave 的原理

  • 主:binlog线程——记录下所有改变了数据库数据的语句,放进master上的binlog中;

  • 从:io线程——在使用start slave 之后,负责从master上拉取 binlog 内容,放进 自己的relay log中;

  • 从:sql执行线程——执行relay log中的语句;

备份

备份计划,mysqldump以及xtranbackup的实现原理

  • 备份计划

    • 这里每个公司都不一样
  • 备份恢复时间,这里跟机器,尤其是硬盘的速率有关系,以下列举几个仅供参考

    • 20G的2分钟(mysqldump)

    • 80G的30分钟(mysqldump)

    • 111G的30分钟(mysqldump)

    • 288G的3小时(xtra)

    • 3T的4小时(xtra)

    • 逻辑导入时间一般是备份时间的5倍以上

  • xtrabackup实现原理

    • 在InnoDB内部会维护一个redo日志文件,我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时,InnoDB会检查数据文件和事务日志,并执行两个步骤:它应用(前滚)已经提交的事务日志到数据文件,并将修改过但没有提交的数据进行回滚操作。

mysqldump中备份出来的sql,如果我想sql文件中,一行只有一个insert….value()的话,怎么办?如果备份需要带上master的复制点信息怎么办

--skip-extended-insert

[root@helei-zhuanshu ~]# mysqldump -uroot -p helei --skip-extended-insert

Enter password:

KEY `idx_c1` (`c1`),

KEY `idx_c2` (`c2`)

) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `helei`

--

LOCK TABLES `helei` WRITE;

/*!40000 ALTER TABLE `helei` DISABLE KEYS */;

INSERT INTO `helei` VALUES (1,32,37,38,'2016-10-18 06:19:24','susususususususususususu');

INSERT INTO `helei` VALUES (2,37,46,21,'2016-10-18 06:19:24','susususususu');

INSERT INTO `helei` VALUES (3,21,5,14,'2016-10-18 06:19:24','susu');

扩展

MYSQL优化

  • SQL语句、索引优化和数据配置优化比较常见,硬件优化不是很常见。

  • 配置优化

    • show variables like '%max_connections%'; 这个参数表示 MySQL 可以接收到的最大连接数,
    • show status like 'Threads%'; MySQL 的实际连接数
    • innodb_buffer_pool_size InnoDB 存储引擎下 MySQL 的内存缓冲区大小。
      • 从磁盘读取数据效率是很低的,为了避免这个问题,MySQL 开辟了基于内存的缓冲池,核心做法就是把经常请求的热数据放入池中,如果请求交互的数据都在缓冲池中则会很高效,所以一般数据库缓冲池设置得会比较大,占到操作系统内存值的 70%~80%。
      • show status like 'Innodb_buffer_pool_read_%'

慢SQL

如果发现系统慢了,又说不清楚是哪里慢,那么就该用这个工具了。只需要为mysql配置参数,mysql会自己记录下来慢的sql语句。

  • long_query_time SQL执行时间必须超过,不包括锁等待时间。
#查看慢查询日志是否开启 OFF关闭 ON开启
show variables like 'slow_query_log'

#开启慢查询日志
set global slow_query_log = on

#慢查询日志所存储的文件位置
show variables like 'slow_query_log_file'

#设置慢查询日志位置
set global slow_query_log_file = '/home/slow.txt'

#是否吧没有使用索引的sql记录到慢查询日志当中 PFF关闭 ON开启
show variables like 'log_queries_not_using_indexes'

#设置吧没有使用索引的sql记录添加到慢查询日志当中
set global log_queries_not_using_indexes = on;

#查看long_query_time设置的时间
show variables like 'long_query_time';

#超过多少秒之后的查询记录到慢查询日志当中
set [session|global] long_query_time = 1
  • 修改my.cnf
slow_query_log=1 # 设置为1打开慢日志。
slow_query_log_file=/data/mysql-slow.log # 指定慢sql文件存储位置。
  • SQL 问题中索引相关的问题也是最突出的。

  • show full processlist 通过 show full processlist 实时获取交互的 SQL。

  • SQL语句查询计划

    • explain ${SQL}

索引优化

对于数据量达到一定的表,尽量不要走扫描全表。在使用过滤条件的时候where 及 order by 涉及的列上建立索引。

  • 查询语句无论是使用哪种判断条件 等于、小于、大于, WHERE 左侧的条件查询字段不要使用函数或者表达式

  • 使用 EXPLAIN 命令优化你的 SELECT 查询,对于复杂、效率低的 sql 语句,我们通常是使用 explain sql 来分析这条 sql 语句,这样方便我们分析,进行优化。

  • 当你的 SELECT 查询语句只需要使用一条记录时,要使用 LIMIT 1

  • 不要直接使用 SELECT *,而应该使用具体需要查询的表字段,因为使用 EXPLAIN 进行分析时,SELECT * 使用的是全表扫描,也就是 type = all。

  • 为每一张表设置一个 ID 属性

  • 避免在 WHERE 字句中对字段进行 NULL 判断

  • 避免在 WHERE 中使用 != 或 <> 操作符。否则将执行全表扫描。

  • 使用 BETWEEN AND 替代 IN。对于连续的范围查询使用BETWEEN 。

  • 为搜索字段创建索引,大字段类型不要建立索引。

  • 选择正确的存储引擎,InnoDB 、MyISAM 、MEMORY 等

  • 使用 LIKE %abc% 不会走索引,而使用 LIKE abc% 会走索引。

    • SELECT * FROM tab_name WHERE index_column LIKE 'abc%' #索引是有效的
      SELECT * FROM tab_name WHERE index_column LIKE '%abc' #索引是无效的
      SELECT * FROM tab_name WHERE index_column LIKE '%cba' #索引是无效的
      SELECT * FROM tab_name WHERE index_column LIKE '%abc%' #索引是无效的
      
    • 如果要提高效率,可以考虑使用全文检索。

    • select id from t with(index(索引名)) where num = @num
      
  • 对于枚举类型的字段(即有固定罗列值的字段),建议使用ENUM而不是VARCHAR,如性别、星期、类型、类别等

  • 拆分大的 DELETE 或 INSERT 语句

  • 选择合适的字段类型,选择标准是 尽可能小、尽可能定长、尽可能使用整数。

  • 字段设计尽可能使用 NOT NULL

  • 避免在WHERE子句中使用表达式。

    • select id from t where num/2 = 100 # 错误的案例
      select id from t where num = 100*2 # 正确的案例
      
  • 进行水平切割或者垂直分割。

表连接优化

Nested Loop Join(NLJ)算法:

基础算法:NLJ,嵌套循环算法。循环外层是驱动表,循坏内层是被驱动表。

  • 驱动表会驱动被驱动表进行连接操作。首先驱动表找到第一条记录,然后从头扫描被驱动表,逐一查找与驱动表第一条记录匹配的记录然后连接起来形成结果表中的一条记。
  • 被驱动表查找完后,再从驱动表中取出第二个记录,然后从头扫描被驱动表,逐一查找与驱动表第二条记录匹配的记录,连接起来形成结果表中的一条记录。
 foreach row1 from t1
        foreach row2 from t2
            if row2 match row1 #row2与row1匹配,满足连接条件
                join row1 and row2 into result #连接row1和row2加入结果集
# 首先加载t1,然后从t1中取出第一条记录,之后加载t2表,与t2表中的记录逐个匹配,连接匹配的记录。

Block Nested Loop Join(BNLJ)算法:

高级算法:BNLJ,块嵌套循环算法,可以看作对NLJ的优化。

  • 建立一个缓存区joinbuffer,一次从驱动表中取多条记录,然后扫描被驱动表,被驱动表的每一条记录都尝试与缓冲区中的多条记录匹配,如果匹配则连接并加入结果集。
  • 缓冲区越大,驱动表一次取出的记录就越多。这个算法的优化思路就是减少内循环的次数从而提高表连接效率。
  • 其实这就是 hash join 了,MySQL 8.0 已支持。

临时表优化

MySQL在执行SQL查询时可能会用到临时表,一般情况下,用到临时表就意味着性能较低。

  • 一般情况下,MySQL会先创建内存临时表,但内存临时表超过配置指定的值后,MySQL会将内存临时表导出到磁盘临时表。
ORDERY BY price GROUP BY name
# ORDER BY子句和GROUP BY子句不同,且都字段没有加入索引。
SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name
# 在JOIN查询中,ORDER BY或者GROUP BY使用了不是第一个表的列

大表连表查询

一个6亿的表a,一个3亿的表b,通过外间tid关联,你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

  • 1、如果A表TID是自增长,并且是连续的,B表的ID为索引
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
  • 2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。
select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

数据库的三范式是什么

  • 第一范式:列不可再分,字段具备单一职责
  • 第二范式:行可以唯一区分,主键约束
  • 第三范式:表的非主属性不能依赖与其他表的非主属性 外键约束
  • 第一范式(1NF):数据库表中的字段都是单一属性的,不可再分。这个单一属性由基本类型构成,包括整型、实数、字符型、逻辑型、日期型等。
  • 第二范式(2NF):数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖(部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况),也即所有非关键字段都完全依赖于任意一组候选关键字。
  • 第三范式(3NF):在第二范式的基础上,数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖,指的是如 果存在"A → B → C"的决定关系,则C传递函数依赖于A。因此,满足第三范式的数据库表应该不存在如下依赖关系: 关键字段 → 非关键字段 x → 非关键字段y
  • 且三大范式是一级一级依赖的,第二范式建立在第一范式上,第三范式建立第一第二范式上。

Mysql 大小写问题

  • 表名、表别名、字段名、字段别名都小写。SQL保留字段、函数名、绑定变量名都大写。

DDL设计数据表的原则

  1. 数据表越少越好
  2. 数据表中的字段越少越好
  3. 数据表中联合主键的字段越少越好
  4. 使用主键和外键越多越好

varchar与char的区别以及varchar(50)中的50代表的涵义

  • varchar与char的区别
    • CHAR和VARCHAR类型在存储和检索方面有所不同,CHAR是一种固定长度的类型,VARCHAR则是一种可变长度的类型。
    • CHAR列长度固定为创建表时声明的长度,长度值范围是1到255当CHAR值被存储时,它们被用空格填充到特定长度,检索CHAR值时需删除尾随空格。
  • varchar(50)中50的涵义
    • 最多存放50个字符,varchar(50)和(200)存储hello所占空间一样,但后者在排序时会消耗更多内存,因为order by col采用fixed_length计算col长度(memory引擎也一样)
  • int(20)中20的涵义
    • 是指显示字符的长度但要加参数的,最大为255,比如它是记录行数的id,插入10笔资料,它就显示00000000001 ~~~00000000010,当字符的位数超过11,它也只显示11位,如果你没有加那个让它未满11位就前面加0的参数,它不会在前面加0
    • 20表示最大显示宽度为20,但仍占4字节存储,存储范围不变。
  • mysql为什么这么设计
    • 对大多数应用没有意义,只是规定一些工具用来显示字符的个数;int(1)和int(20)存储和计算均一样。

表中有大字段X(例如:text类型),且字段X不会经常更新,以读为为主。如何拆分

拆带来的问题:连接消耗 + 存储拆分空间;不拆可能带来的问题:查询性能;

  • 1、如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序IO,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗
  • 2、如果能容忍不拆分带来的查询性能损失的话:上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择。

什么是临时表,何时删除临时表

在 MySQL 中,有三种类型的表
一种是`永久表`,永久表就是创建以后用来长期保存数据的表
一种是`临时表`,临时表也有两类,一种是和永久表一样,只保存临时数据,但是能够长久存在的;还有一种是临时创建的,SQL 语句执行完成就会删除。
一种是`虚表`,虚表其实就是`视图`,数据可能会来自多张表的执行结果。
  • MySQL 会在下面这几种情况产生临时表
    • 使用 UNION 查询:UNION 有两种,一种是UNION ,一种是 UNION ALL ,它们都用于联合查询;区别是 使用 UNION 会去掉两个表中的重复数据,相当于对结果集做了一下去重(distinct)。使用 UNION ALL,则不会排重,返回所有的行。使用 UNION 查询会产生临时表。
    • 使用 TEMPTABLE 算法或者是 UNION 查询中的视图。TEMPTABLE 算法是一种创建临时表的算法,它是将结果放置到临时表中,意味这要 MySQL 要先创建好一个临时表,然后将结果放到临时表中去,然后再使用这个临时表进行相应的查询。
    • ORDER BY 和 GROUP BY 的子句不一样时也会产生临时表。
    • DISTINCT 查询并且加上 ORDER BY 时;
    • SQL 用到 SQL_SMALL_RESULT 选项时;如果查询结果比较小的时候,可以加上 SQL_SMALL_RESULT 来优化,产生临时表
    • FROM 中的子查询;
    • EXPLAIN 查看执行计划结果的 Extra 列中,如果使用 Using Temporary 就表示会用到临时表。

Exists与in

  • 将设A、B两表,走索引的情况下。根据A、B表的大小比较,如果A表大于B表那么IN的查询效率比EXISTS高。
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)

SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)

测试环境打开profiling

  • 首先我们需要看下 profiling 是否开启,开启它可以让 MySQL 收集在 SQL 执行时所使用的资源情况,命令如下:
mysql> select @@profiling;
# profiling=0 代表关闭,我们需要把 profiling 打开,即设置为 1:
mysql> set profiling=1;
# 然后我们执行一个 SQL 查询(你可以执行任何一个 SQL 查询):
mysql> select * from wucai.heros;

去除重复行

  1. DISTINCT 需要放到所有列名的前面
# 这么写会报错
SELECT name, DISTINCT age FROM us
  1. DISTINCT 其实是对后面所有列名的组合进行去重。

drop、delete与truncate

SQL中的drop、delete、truncate都表示删除,但是三者有一些差别

  • delete和truncate只删除表的数据不删除表的结构

  • 速度,一般来说: drop> truncate >delete

  • delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;

  • 如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger。

  • 不再需要一张表的时候,用drop

  • 想删除部分数据行时候,用delete,并且带上where子句

  • 保留表而删除所有数据的时候用truncate

in, not in , exists , not exists 它们有什么区别,效率如何

  • 参考文献

你是否做过主从一致性校验,如果有,怎么做的,如果没有,你打算怎么做

  • 主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等。

你们数据库是否支持emoji表情,如果不支持,如何操作

  • 如果是utf8字符集的话,需要升级至utf8_mb4方可支持。

Too many connections

连接数超出 Mysql 的最大连接限制

  • 解决方法:

    • 1、在 my.cnf 配置文件里面增加连接数,然后重启 MySQL 服务。max_connections = 10000
    • 2、临时修改最大连接数,重启后不生效。需要在 my.cnf 里面修改配置文件,下次重启生效。

MySQL数据库cpu飙升到100%的话怎么处理

  • 列出所有进程 show processlist,观察所有进程 ,多秒没有状态变化的(干掉)
  • 查看超时日志或者错误日志 (一般会是查询以及大批量的插入会导致cpu与i/o上涨,当然不排除网络状态突然断了,导致一个请求服务器只接受到一半,比如where子句或分页子句没有发送,,当然的一次被坑经历)。

参考文献

https://mp.weixin.qq.com/s/lnLSOPQkjTkr957ds_4ZPA

小结

binlog 记录逻辑变更、redo 保证崩溃恢复、undo 支撑回滚与 MVCC,两阶段提交协调两者一致;执行顺序固定为 FROM 到 LIMIT,explain 与慢日志是优化入口。

最近更新: 2026/11/20 22:35
Contributors: 庆峰
Prev
MySQL:事务、锁与 MVCC
Next
MySQL:架构、事务与优化