mysql

mysql

SQL的生命周期

应用服务器与数据库服务器建立一个连接

数据库进程拿到请求sql

解析并生成执行计划，执行

读取数据到内存并进行逻辑处理

通过步骤一的连接，发送结果到客户端

关掉连接，释放资源

SQL 约束

NOT NULL: 用于控制字段的内容一定不能为空（NULL）。

UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。

PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。

FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

CHECK: 用于控制字段的值范围。

触发器

Before Insert

After Insert

Before Update

After Update

Before Delete

After Delete

视图

所谓视图，本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。

特点

• 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
• 视图是由基本表(实表)产生的表(虚表)。
• 视图的建立和删除不影响基本表。
• 对视图内容的更新(添加，删除和修改)直接影响基本表。
• 当视图来自多个基本表时，不允许添加和删除数据。

优点

• 查询简单化。视图能简化用户的操作
• 数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护
• 逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

缺点

• 性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。
• 修改限制。当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的

事物的四大特性(ACID)

一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；

隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

为什么要使用数据库

1. 数据保存在内存中

• 优点 ：存取速度快
• 缺点 ： 数据不能永久保存

2. 数据保存在文件中

• 优点 ： 存取数据快

• 缺点

  • 1. 速度比内存操作慢，频繁的IO操作
  • 2. 查询数据不方便

3.数据存储在数据库

• 1.数据永久保存
• 2. 使用sql语句，查询方便且效率高
• 3. 管理数据方便

什么是SQL？

结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言

什么是MySQL?

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。

• MySQL是一个关系型数据库管理系统

作用：用于存取数据、查询、更新和管理关系数据库系统。

数据库三大范式

第一范式(确保每列保持原子性)

第二范式(确保表中的每列都和主键相关)

第三范式(确保每列都和主键列直接相关,而不是间接相关)

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

mysql有关权限的表都有哪几个

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。

db权限表：记录各个帐号在各个数据库上的操作权限。

table_priv权限表：记录数据表级的操作权限。

columns_priv权限表：记录数据列级的操作权限。

host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

MySQL的binlog有有几种录入格式？分别有什么区别？

statement

• statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。

row

• row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。

  • 新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录

mixed

• mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

数据类型

整数类型

，包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。
长度：整数类型可以被指定长度，例如：INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。
例子，假定类型设定为INT(5)，属性为UNSIGNED ZEROFILL，如果用户插入的数据为12的话，那么数据库实际存储数据为00012。
可看博主
：https://blog.csdn.net/ThinkWon/article/details/104778621

• tinyInt

  • 很小的整数(8位二进制)子主题 1

• smallint

  • 小的整数(16位二进制)

• mediumint

  • 中等大小的整数(24位二进制)

• int(integer)

  • 普通大小的整数(32位二进制)

小数类型

• float

  • 单精度浮点数

• double

  • 双精度浮点数

• decimal(m,d)

  • 压缩严格的定点数

日期类型

尽量使用timestamp，空间效率高于datetime，
用整数保存时间戳通常不方便处理。
如果需要存储微妙，可以使用bigint存储。

• year

  • YYYY 1901~2155

• time

  • HH:MM:SS -838:59:59~838:59:59

• date

  • YYYY-MM-DD 1000-01-01~9999-12-3

• datetime

  • YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59

• timestamp

  • YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC

文本、二进制类型

• CHAR(M)

  • M为0~255之间的整数

• VARCHAR(M)

  • M为0~65535之间的整数

• TINYBLOB

  • 允许长度0~255字节

• BLOB

  • 允许长度0~65535字节

• MEDIUMBLOB

  • 允许长度0~167772150字节

• LONGBLOB

  • 允许长度0~4294967295字节

• TINYTEXT

  • 允许长度0~255字节

• TEXT

  • 允许长度0~65535字节

• MEDIUMTEXT

  • 允许长度0~167772150字节

• LONGTEXT

  • 允许长度0~4294967295字节

• VARBINARY(M)

  • 允许长度0~M个字节的变长字节字符串

• BINARY(M)

  • 允许长度0~M个字节的定长字节字符串

枚举类型

• 把不重复的数据存储为一个预定义的集合。
• 有时可以使用ENUM代替常用的字符串类型。
• ENUM存储非常紧凑，会把列表值压缩到一个或两个字节。
• ENUM在内部存储时，其实存的是整数。
• 尽量避免使用数字作为ENUM枚举的常量，因为容易混乱。
• 排序是按照内部存储的整数

使用策略

• 对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
• 对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。
• 使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。
• 尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。（b树的内节点和叶节点存储键和键值，而B+树内部节点都是键，叶子节点同时存放键和值）
更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

优点

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点

• 时间方面

  • 创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；

• 空间方面

  • 索引需要占物理空间。

索引使用场景（重点）

• where语句

  增加一个没有建立索引的字段

  alter table table1 add sex char(1);

  按sex检索时可选的索引为null

  建立索引后查询的效率提升，数据量越大越明显

  • 如果字段仅建立了主键索引，因此此SQL执行可选的索引只有主键索引，如果有多个，最终会选一个较优的作为检索的依据。

• order by

  当我们使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序了。

  但是如果我们对该字段建立索引alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）

• join

  对join语句匹配关系（on）涉及的字段建立索引能够提高效率

索引覆盖

如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据
（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在 select后只写必要的查询字段，以增加索引覆盖的几率。

• 查询语句的执行只用从索引中就能够取得

索引的类型

• 主键索引

  • 数据列不允许重复，不允许为NULL，一个表只能有一个主键

• 唯一索引

  可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
  可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

  • 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引

• 普通索引

  可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

  可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

  • 基本的索引类型，没有唯一性的限制，允许为NULL值。

• 全文索引

  • 是目前搜索引擎使用的一种关键技术。

索引的数据结构

• b树
• hash

原理

• 就是把无序的数据变成有序的查询

  • 把创建了索引的列的内容进行排序
  • 对排序结果生成倒排表
  • 在倒排表内容上拼上数据地址链
  • 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

创建索引的原则

最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

关联查询

内连接（INNER JOIN）

• 等值连接：ON A.id=B.id
• 不等值连接：ON A.id > B.id
• 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

外连接（LEFT JOIN/RIGHT JOIN）

• 左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
• 右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

联合查询（UNION与UNION ALL）

如果使用UNION ALL，不会合并重复的记录行
效率 UNION 高于 UNION AL

• 就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
• 如果使用UNION ALL，不会合并重复的记录行
• 效率 UNION 高于 UNION ALL

交叉连接（CROSS JOIN）

• 笛卡尔积

子查询

条件：一条SQL语句的查询结果做为另一条查询语句的条件或查询结果

嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

varchar与char的区别

int(10) 10位的数据长度 9999999999，占32个字节，int型4位
char(10) 10位固定字符串，不足补空格 最多10个字符
varchar(10) 10位可变字符串，不足补空格 最多10个字符

char的特点

• char表示定长字符串，长度是固定的；
• 如果插入数据的长度小于char的固定长度时，则用空格填充；
• 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
• 对于char来说，最多能存放的字符个数为255，和编码无关

varchar的特点

• varchar表示可变长字符串，长度是可变的；
• 插入的数据是多长，就按照多长来存储；
• varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
• 对于varchar来说，最多能存放的字符个数为65532

其他

字段为什么要求定义为not null？

• null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

如果要存储用户的密码散列，应该使用什么字段进行存储？

• 密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。