Mysql

面试中有哪些经典的数据库问题

一、为什么用自增列作为主键

1、如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增,这个ROWID不像ORACLE的ROWID那样可引用,是隐含的)。

2、数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)

3、如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页

4、如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置,此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。…

Centos7安装MySQL8.0 – 操作手册

MySQL 8 正式版 8.0.11 已发布,官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍,还带来了大量的改进和更快的性能!

一.  Mysql8.0版本相比之前版本的一些特性

1) 默认编码utf8
默认编码使用utf8mb4, utf8mb4编码是utf8编码的超集,兼容utf8,并且能存储4字节的表情字符历史,MySQL数据库的 "utf8"并不是真正概念里的 UTF-8。MySQL中的"utf8"编码只支持最大3字节每字符。真正的大家正在使用的UTF-8编码是应该能支持4字节每个字符。MySQL的开发者没有修复这个bug。他们在2010年增加了一个变通的方法:一个新的字符集"utf8mb4"另外,utf-32编码固定使用4字节,32bit存储相比utf8浪费空间。

2) 降序索引
在之前的版本中可以创建,但是实际创建的还是升序索引

MySQL索引失效的几种情况

1.索引不存储null值

更准确的说,单列索引不存储null值,复合索引不存储全为null的值。索引不能存储Null,所以对这列采用is null条件时,因为索引上根本

没Null值,不能利用到索引,只能全表扫描。

为什么索引列不能存Null值?

将索引列值进行建树,其中必然涉及到诸多的比较操作。Null值的特殊性就在于参与的运算大多取值为null。

这样的话,null值实际上是不能参与进建索引的过程。也就是说,null值不会像其他取值一样出现在索引树的叶子节点上。…

    

互联网项目中mysql应该选什么事务隔离级别

摘要

企业千万家,靠谱没几家。
社招选错家,亲人两行泪。

祝大家金三银四跳槽顺利!

引言

开始我们的内容,相信大家一定遇到过下面的一个面试场景

面试官:“讲讲mysql有几个事务隔离级别?”
你:“读未提交,读已提交,可重复读,串行化四个!默认是可重复读”
面试官:“为什么mysql选可重复读作为默认的隔离级别?”
(你面露苦色,不知如何回答!)
面试官:"你们项目中选了哪个隔离级别?为什么?"
你:“当然是默认的可重复读,至于原因。。呃。。。”
(然后你就可以回去等通知了!)

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

说起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()会因为不同的查询时间,返回不同的查询结果,再比如包含

        

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

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

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

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

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

6af9578033fb49f255da47c0ead5e0304a61cc81

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

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

5edc40f9af608084c0586291328ad5df92ec050a

数据库概述

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

f207a157e6033c50966f0e989db45db33fd2d1d7

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

221c122f7a7435684fe50c104aa43bd2e88636ff

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

19f8d1462ef50d271d95801c9ef06618e0cf1d24

MongoDB 特点介绍:

da23683d068c48f16dbae00f49a772be4e4cd638

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

        

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

file

SQL 数据库正趋向于严格。

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

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

JeremyKuang 翻译于 1周前
 由 Summer 审阅
    

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

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这样的值. …