面试-MySQL

来源:http://www.sh-fengwen.com 作者:气血双补 人气:104 发布时间:2019-11-12
摘要:三范式 三范式定义(范式和反范式) 1NF :每个数据项都是最小单元,不可分割,确定行列之后只能对应一个数据。 2NF :每一个非主属性完全依赖于 候选码 (属性组的值能唯一的标识

三范式

三范式定义(范式和反范式)

1NF:每个数据项都是最小单元,不可分割,确定行列之后只能对应一个数据。

2NF:每一个非主属性完全依赖于候选码(属性组的值能唯一的标识一个元组,但是其子集不可以)。

3NF:每一个非主属性既不传递依赖于,也不部分依赖于(主码=候选码为多个市,从中选出一个作为主码)。

BCNF主属性(候选码中的某一个属性)内部也不能部分或传递依赖于码。

4NF :没有多值依赖。

面试-MySQL

 

数据类型

MySQL数据类型-菜鸟教程 

MYSQL中数据类型介绍

整数: int(m)里的m是表示数据显示宽度,浮点数,定点数。

字符串:char(n)4.0 n 代表字节,5.0 n 代表字符 (UTF-8=3zj,GBK=2zj)

 char 固定的字符数,空格补上;检索速度快。

 varchar 字符数+1个字节(n<=255)或2个字节(n>255)

 text 字符数+2个字节;不能有默认值;索引要指定前多少个字符;文本方式存储

 blob 二进制方式存储

1 事务的特性

 

事务具有四个特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持续性(Durability)。这四个特性也简称ACID性。

(1)原子性:事务是应用中最小的执行单位,就如原子是自然界最小颗粒,具有不可再分的特征一样。事务是应用中不可再分的最小逻辑执行体。

(2)一致性:事务执行的结果,必须使数据库从一个一致性状态,变到另一个一致性状态。当数据库中只包含事务成功提交的结果时,数据库处于一致性状态。一致性是通过原子性来保证的。

(3)隔离性:各个事务的执行互不干扰,任意一个事务的内部操作对其他并发的事务,都是隔离的。也就是说:并发执行的事务之间不能看到对方的中间状态,并发执行的事务之间不能相互影响。

(4)持久性:指事务一旦提交,对数据所做的任何改变,都要记录到永久存储器中,通常是保存进物理数据库。

存储引擎

各种存储引擎的区别与联系     (存储数据技术和策略,存储机制、索引技巧、锁定水平等)

数据库存储引擎     show table status 显示表的相关信息

InnoDB与MyISAM的比较(从5.7开始innodb存储引擎成为默认的存储引擎。)

 锁机制:行级锁,表级锁

 事务操作:事务安全,不支持

InnoDB (1)可靠性要求比较高,要求事务;(2)表更新和查询都相当的频繁,并且行锁定的机会比较大的情况。

 MySQL4.1之后每个表的数据和索引存储在一个文件里。

 InnoDB 采用了MVCC来支持高并发,并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ(可重复读) ,行级锁。

 自动灾难恢复。与其它存储引擎不同,InnoDB表能够自动从灾难中恢复。

 外键约束。MySQL支持外键的存储引擎只有InnoDB。

 支持自动增加列AUTO_INCREMENT属性。

MyIsam  (1)做很多count 的计算;(2)插入不频繁,查询非常频繁;(3)没有事务。

 表存储在两个文件中,数据文件(MYD)和索引文件(MYI)

 表级锁,读=共享锁,写=排它锁。

 适合选择密集型的表,插入密集型的表。

2 MySQL存储引擎

 

特点

Myisam

BDB

Memory

InnoDB

Archive

存储限制

没有

没有

64TB

没有

事务安全

 

支持

 

支持

 

锁机制

表锁

页锁

表锁

行锁

行锁

B树索引

支持

支持

支持

支持

 

哈希索引

   

支持

支持

 

全文索引

支持

       

集群索引

     

支持

 

数据缓存

   

支持

支持

 

索引缓存

支持

 

支持

支持

 

数据可压缩

支持

     

支持

空间使用

N/A

非常低

内存使用

中等

批量插入的速度

非常高

支持外键

     

支持

 

Myisam:每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同,扩展名分别是.frm(存储表定义)、.MYD(MYData,存储数据)、.MYI(MYIndex,存储索引)。数据文件和索引文件可以放置在不同的目录,平均分布io,获得更快的速度。最常使用的2种存储引擎:

 

InnoDB:是Mysql的默认存储引擎。当create创建新表时,未指定新表的存储引擎时,默认使用InnoDB。存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

数据库ACID

数据库的ACID

数据库事务介绍

原子性(Atomicity)一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作。

一致性(Consistency)数据库总是从一个一致性的状态转换到另一个一致性的状态。

隔离性(Isolation)一个事务所做的修改在最终提交以前,对其他事务是不可见的。

持久性(Durability)一旦事务提交,则其所做的修改不会永久保存到数据库。

4 种隔离级别

MVVC的简单介绍

READ UNCOMMITTED(未提交读)脏读:事务中的修改,即使没有提交,对其他事务也都是可见的。

READ COMMITTED(提交读)不可重复读:事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。

REPEATABLE READ(可重复读):幻读:一个事务按相同的查询条件读取以前检索过的数据,其他事务插入了满足其查询条件的新数据。产生幻行。

SERIALIZABLE(可串行化) 强制事务串行执行

MVVC是个行级锁的变种,它在普通读情况下避免了加锁操作,自特定情况下加锁

3 选择合适的MySQL存储引擎

选择标准:根据应用特点选择合适的存储引擎,对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合。下面是常用存储引擎的适用环境:

MyISAM:在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

InnoDB:默认的MySQL插件式存储引擎,用于事务处理应用程序,具有众多特性,包括ACID事务支持。

Memory:将所有数据保存在RAM中,在需要快速查找引用和其他类似数据的环境下,可提供极快的访问。

Merge:允许MySQLDBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起,并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

Mysql死锁问题

Mysql悲观锁总结和实践

Mysql乐观锁总结和实践

SELECT ... LOCK IN SHARE MODE SELECT ... FOR UPDATE:(LOCK IN SHARE MODE 在有一方事务要Update 同一个表单时很容易造成死锁)

乐观锁:取锁失败,产生回溯时影响效率。

 取数据时认为其他线程不会对数据进行修改。

 更新时判断是否对数据进行修改,版本号机制或CAS操作。

悲观锁:每次取数据都会加锁。

innodb_lock_wait_timeout 等待锁超时回滚事务:  【超时法】

直观方法是在两个事务相互等待时,当一个等待时间超过设置的某一阀值时,对其中一个事务进行回滚,另一个事务就能继续执行。在innodb中,参数innodb_lock_wait_timeout用来设置超时时间。

wait-for graph算法来主动进行死锁检测:  【等待图法】

innodb还提供了wait-for graph算法来主动进行死锁检测,每当加锁请求无法立即满足需要并进入等待时,wait-for graph算法都会被触发。

4 MySQL特点

与其他数据库Oracle、DB2、SQL Server等相比功能稍弱一些

1、可以处理拥有上千万条记录的大型数据;

2、支持常见的SQL语句规范 ;

3、可移植行高,安装简单小巧 ;

4、良好的运行效率,有丰富信息的网络支持;

5、调试、管理,优化简单(相对其他大型数据库)。

索引

索引(存储引擎 快速找到记录的一种数据结构,索引的基本功能)

什么是B-Tree

MySQL索引背后的数据结构及算法原理

MySQL性能优化-慢查询分析、优化索引和配置

5 MySQL支持的数据类型

数值型:

图片 1

 

日期时间类型:

图片 2

 

字符串类型:

图片 3

 

空间数据类型:

图片 4

 

索引类型:

 B-Tree索引 索引列的顺序影响者是否使用索引。

 哈希索引

 无法用于排序。

 只支持全部匹配。

 只支持等值比较。

 有很多哈希冲突时,效率不太高。

 空间数据索引(R-Tree)无需前缀查询,从所有维度查询数据。

 全文检索 查找文本中的关键词,类似于搜索引擎做的事情。

6 MySQL内置函数

数学函数

BIN(x):返回x的二进制(OCT返回八进制,HEX返回十六进制)

FLOOR(x):返回小于x的最大整数值

ROUND(x,y):返回参数x的四舍五入的有y位小数的值

LOG(x,y):返回x的以y为底的对数

MOD(x,y):返回x/y的模(余数)

聚合函数:

COUNT(col):返回指定列中非NULL值的个数

MIN(col):返回指定列的最小值

MAX(col):返回指定列的最大值

SUM(col):返回指定列的所有值之和

字符串函数:

LENGTH(s):返回字符串str中的字符数

CONCAT(s1,s2...,sn):将s1,s2...,sn连接成字符串

TRIM(str):去除字符串首部和尾部的所有空格

LEFT(str,x):返回字符串str中最左边的x个字符

RIGHT(str,x):返回字符串str中最右边的x个字符

日期时间函数:

NOW():返回当前的日期和时间

WEEK(date):返回日期date为一年中第几周(0~53)

YEAR(date):返回日期date的年份(1000~9999)

DAYOFWEEK(date):返回date所代表的一星期中的第几天(1~7)

DAYOFMONTH(date):返回date是一个月的第几天(1~31)

DAYOFYEAR(date):返回date是一年的第几天(1~366)

加密函数:

MD5():计算字符串str的MD5校验和

ENCRYPT(str,salt):使用UNIXcrypt()函数,用关键词salt(一个可以惟一确定口令的字符串,就像钥匙一样)加密字符串str

控制流函数:MySQL有4个函数是用来进行条件操作的,这些函数可以实现SQL的条件逻辑,允许开发者将一些应用程序业务逻辑转换到数据库后台。

SELECT CASE 'green'

WHEN 'red' THEN 'stop'

WHEN 'green' THEN 'go' END;

格式化函数:

DATE_FORMAT(date,fmt):依照字符串fmt格式化日期date值

TIME_FORMAT(time,fmt):依照字符串fmt格式化时间time值

类型转化函数:

为了进行数据类型转化,MySQL提供了CAST()函数,它可以把一个值转化为指定的数据类型。类型有:BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED

示例:

SELECT CAST(NOW() AS SIGNED INTEGER), CURDATE()+0;

SELECT 'f'=BINARY 'F', 'f'=CAST('F' AS BINARY);

系统信息函数:

CONNECTION_ID():返回当前客户的连接ID

VERSION():返回MySQL服务器的版本

USER()或SYSTEM_USER():返回当前登陆用户名

FOUND_ROWS():返回最后一个SELECT查询进行检索的总行数

 具体类型介绍:

单列索引:不允许为空

 普通索引 不允许有空值

 唯一索引

 主键索引 在 InnoDB 引擎中很重要

组合引擎:多个字段上创建的索引,复合索引时遵循最左前缀原则。

 查询中某个列有范围查询,则其右边的所有列都无法使用查询

全文索引:

空间索引:

参考:细说mysql索引、我的MYSQL学习心得(九) 索引

7 MySQL解决乱码问题

在数据库安的时候指定字符集,如果在安完了以后可以更改以下文件:C:ProgramFilesMySQLMySQL Server 5.0my.ini 里的所有的 default-character-set=gbk C:Program FilesMySQLMySQL Server 5.0datadepot_developmentdb.optdefault-character-set=gbk default-collation=gbk_chinese_ci 建立数据库时候:指定字符集类型

1 事务的特性 事务具有四个特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持续性(Durability)。这四个特性...

本文由美高梅游戏平台网站发布于气血双补,转载请注明出处:面试-MySQL

关键词:

最火资讯