Mysql

我必须得告诉大家的MySQL优化原理

admin Mysql 暂无评论 1,162 浏览

说起MySQL的查询优化，相信大家收藏了一堆奇技淫巧：不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧？是否理解其背后的工作原理？在实际场景下性能真有提升吗？我想未必。因而理解这些优化建议背后的原理就尤为重要，希望本文能让你重新审视这些优化建议，并在实际业务场景下合理的运用。

MySQL逻辑架构

如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图，有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。

MySQL逻辑架构，来自：高性能MySQL

MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。

MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。

最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。

MySQL查询过程

我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。

当向MySQL发送一个请求的时候，MySQL到底做了些什么呢？

MySQL查询过程

客户端/服务端通信协议

MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。

与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果
都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含

…
        
Read more

原启用mysqli报告,捕获PHP程序中未使用索引的查询语句.

admin PHP 暂无评论 1,373 浏览

启用mysqli报告,捕获PHP程序中未使用索引的查询语句.

使用mysqli_report($flags)函数

在代码开发和测试期间有助于改进查询的函数。根据标志，它报告来自mysqli函数调用或不使用索引的查询（或使用错误索引）的错误。

代码如下

<?php
/* 激活报告 */
mysqli_report(MYSQLI_REPORT_ALL);

$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* 检查连接 */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* 此查询会报告错误 */
$result = mysqli_query("SELECT Name FROM Nonexistingtable WHERE population
…
        
Read more

没有宫廷内斗，数据库界的延禧攻略

admin Mysql 暂无评论 901 浏览

摘要： 各位老铁们，你们有没有想老张，最近老张的才华被工作的繁忙所限制了，所以一直没时间更博，今儿个时隔数日我们终于再次见面啦（很开心）！最近有部特别火的宫廷戏，不知道大家有没有看，剧名叫做《延禧攻略》，讲述得是一个宫女，一路过关斩将，最后成为皇上最宠爱的令贵妃的故事。

各位老铁们，你们有没有想老张，最近老张的才华被工作的繁忙所限制了，所以一直没时间更博，今儿个时隔数日我们终于再次见面啦（很开心）！最近有部特别火的宫廷戏，不知道大家有没有看，剧名叫做《延禧攻略》，讲述得是一个宫女，一路过关斩将，最后成为皇上最宠爱的令贵妃的故事。

加上我本人巨爱这类题材，所以痴迷得不得了。（好像暴露了自己没有更博的真正原因哈哈）。宫廷类的剧，都是后宫嫔妃之间的尔虞吾诈，勾心斗角，有你没我，有我没你的残酷事实。胜者为王，败者为寇这种思想好像从古代就一直延续到今日。非要分出个胜负，分出个谁好，谁坏才罢休。

在数据库领域也会有此类问题，老张我混迹开源数据库圈多年。MySQL 数据库占领着开源数据库的头把交椅，MongoDB 占领着 NoSQL 数据库的第一位。我们来看下数据库的整体排名情况；

6af9578033fb49f255da47c0ead5e0304a61cc81

两者都是第一，所有总会拿来比较。也会经常被人问及到诸如此类的问题MongoDB4.0 已经问世了，而且支持事务了，是不是将来可以取代 MySQL了。MySQL 和 MongoDB 哪个数据库好用啊。今天老张想通过这篇文章，带着大家全方位解读 MySQL 与 MongoDB 的区别。让有困惑的老铁们明白，没有谁替代谁，只有哪个场景更适合谁。

我们从下面四个方向依次阐明两者的区别。只有更了解彼此，让能更好地利用它们的功能性。

5edc40f9af608084c0586291328ad5df92ec050a

数据库概述

我们先来了解一下 MySQL 这个数据库；

f207a157e6033c50966f0e989db45db33fd2d1d7

再来学习一下 MySQL 数据库的特点；

221c122f7a7435684fe50c104aa43bd2e88636ff

MySQL了解完，同理我们来了解 MongoDB 及其特点的介绍；

19f8d1462ef50d271d95801c9ef06618e0cf1d24

MongoDB 特点介绍：

da23683d068c48f16dbae00f49a772be4e4cd638

学习完第一部分之后，我们对两者数据库都有了一定的认识；接下来去从运维的角度来检验两者的不同；

…
        
Read more

深入了解 MySQL 的 JSON 数据类型（关系型数据库里的 NoSQL 初探）

admin Mysql 暂无评论 1,187 浏览

file

SQL 数据库正趋向于严格。

如果使用过它们，你会同意数据库设计在实践中有很多坑这个说法，尽管它们看起来很容易。SQL 数据库的精髓是结构，因此被称为结构化查询语言。

另外一种视角看，我们拥有了 NoSQL 数据库，它灵活性好也被称为无模式数据库。在无模式数据库中，没有强迫的结构化限制，仅仅只要存储数据。

JeremyKuang 翻译于 1周前
 0  重译
 由 Summer 审阅
…
    
Read more

原MySQL 5.7数据库新增字段报 Incorrect date value: ‘0000-00-00’ for column ‘endtime’ at row 2的问题

admin Mysql 暂无评论 1,912 浏览

MySQL 5.7数据库新增字段报 Incorrect date value: '0000-00-00' for column 'endtime' at row 2的问题

在数据库执行 ALTER TABLE `smt_announce` ADD `ischildarea` tinyint(1) DEFAULT '0' COMMENT '是否下发到子区域站点0:否；1:是';报以上错误

是因为数据库里的记录的 endtime字段,存在0000-00-00这样的值. …

    
Read more

性能之王，MySQL 8.0 GA版本发布！

admin Mysql 暂无评论 2,483 浏览

MySQL 5.7可以被称为近10年最为经典的版本，正如同96年NBA的芝加哥公牛队，2011年的巴塞罗那队。一代王朝建立，远远甩开原本的竞争对手们。Percona、MariaDB、PostgreSQL们足够努力，只是好学生永远无法追赶闪着光芒的天才。回望过去的3年，现在拿着望远镜，MySQL也已找不到对手。

芝加哥公牛队三冠王，巴萨十年王朝告诉我们，王朝一旦建立，将会势不可挡。今天MySQL 8.0版本GA，至此MySQL这艘之前的巡洋舰，摇身变为一搜巨型航母，未来必将开拓更多的领域，影响更多行业对于开源数据库的应用。

MySQL 5.7版本解决了很多企业级数据库应用的痛点，诸多企业从老版本（例如5.5、5.6）升级到了5.7。甚至在传统领域，MySQL也已经撬动和影响了很多行业。5.7.17发布的MySQL Group Replication必将在未来3年内成为金融行业数据库解决方案的事实标准。

MySQL 8.0是新一代的性能之王。200W QPS不再是瓶颈，这狠狠打脸了Redis、MongoDB、MariaDB、PostgreSQL、TiDB这样的竞争对手。当对手们还在对性能遮遮掩掩时，MySQL已然无可挑剔。

云时代，MySQL 8.0带来了包括但不限于以下的诸多新特性：

· InnoDB各模块重构，性能可有30% ~ 100%的大幅提升（具体见：MySQL 8.0 200W QPS！！！InnoDB大重构 #M1005#）；

· 更好的从机多线程复制机制（writeset-based MTS）机制，至此彻底解决困扰MySQL多年的从机复制延迟问题（具体见：滚蛋吧，MySQL主从复制延迟 #M1002#）；

· 支持基于角色的用户管理，更好地对用户进行更细粒度的管理；

· 基于InnoDB的New Data Diction（新元数据字典），Atomic

…
Read more

58到家MySQL军规升级版

admin Mysql 暂无评论 1,184 浏览

一、基础规范

  • 表存储引擎必须使用InnoDB

 

  • 表字符集默认使用utf8，必要时候使用utf8mb4

解读：

（1）通用，无乱码风险，汉字3字节，英文1字节

（2）utf8mb4是utf8的超集，有存储4字节例如表情符号时，使用它

 

  • 禁止使用存储过程，视图，触发器，Event

解读：

（1）对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，不要交到数据库层

（2）调试，排错，迁移都比较困难，扩展性较差

 

  • 禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统，数据库中存储路径
  • 禁止在线上环境做数据库压力测试
  • 测试，开发，线上数据库环境必须隔离

 

二、命名规范

  • 库名，表名，列名必须用小写，采用下划线分隔

解读：abc

…
Read more

赶集mysql军规

admin Mysql 暂无评论 900 浏览

总是在灾难发生后，才想起容灾的重要性。
总是在吃过亏后，才记得曾经有人提醒过。

 

一，核心军规

  • 不在数据库做计算，cpu计算务必移至业务层
  • 控制单表数据量，单表记录控制在千万级
  • 控制列数量，字段数控制在20以内
  • 平衡范式与冗余，为提高效率可以牺牲范式设计，冗余数据
  • 拒绝3B(big)，大sql，大事务，大批量

 

二，字段类军规

  • 用好数值类型
    tinyint(1Byte)
    smallint(2Byte)
    mediumint(3Byte)
    int(4Byte)
    bigint(8Byte)
    bad case：int(1)/int(11)
  • 有些字符转化为数字
    用int而不是char(15)存储ip
  • 优先使用enum或set
    例如：`sex` enum (‘F’, ‘M’)
  • 避免使用NULL字段
    NULL字段很难查询优化
    NULL字段的索引需要额外空间
    NULL字段的复合索引无效
    bad case：
    `name` char(32) default null
    `age` int
…
Read more

PHP API中，MYSQL与MYSQLI的持久连接区别

admin Mysql, PHP 暂无评论 1,675 浏览

很久很久以前，我也是因为工作上的bug，研究了php mysql client的连接驱动mysqlnd 与libmysql之间的区别php与mysql通讯那点事，这次又遇到一件跟他们有联系的事情，mysqli与mysql持久链接的区别。写出这篇文章，用了好一个多月，其一是我太懒了，其二是工作也比较忙。最近才能腾出时间，来做这些事情。每次做总结，都要认真阅读源码，理解含义，测试验证，来确认这些细节。而每一个步骤都需要花费很长的时间，而且，还不能被打断。一旦被打断了，都需要很长时间去温习上下文。也故意强迫自己写这篇总结，改改自己的惰性。…

    
Read more