MySQL数据库的概念 mysql数据库的基本原理

文章目录

• 了解SQL语句在Mysql内部是怎么流转的？

• MySQL的体系结构

• MySQL存储方式
• ==两种存储引擎的区别==
• InnoDB存储引擎锁机制常见问题
• SQL语句执行过程
• 1 服务层
• 2 核心层（mysqld） 指的是CPU和内存部分
• 3 存储引擎层 硬盘
• SQL语句执行步骤
• MySQL的逻辑架构

• mysql可分为Server层和存储引擎层
• 1 连接器
• 2 查询缓存
• 3 分析器
• 4 优化器
• 5 查询执行引擎
• 数据库处理一个查询的步骤

• InnoDB buffer pool原理

• 索引设计

• MySQL索引
• 常见索引模型
• 索引的优缺点

• ==索引的优点==
• ==索引的缺点==
• 索引的分类

• 表连接

• 表连接的执行计划

• 不走索引的
• 常用的表连接方式
• 辅助索引范围扫描的优化方式

• MySQL函数

• 
  
• 三种函数

• string函数
• Date and Time函数
• numeric函数

• select语句中的聚合函数

• union
• group by
• 使用到临时表temporary buffer的操作

• 执行计划的阅读
• MySQL中的锁

• 锁的粒度
• mysql的存储引擎和锁机制
• 全局锁
• 表级锁

• 表锁
• 元数据锁 MDL（metadata lock）
• 排序

• 行级锁：共享锁S、排它锁X
• 两锁协议

• 行锁

• 死锁
• 重点总结

• 主从复制用途

• 补充知识

• 在MySQL的innodb引擎中的information_schema
• 查看mysql当前活跃的并发连接数
• mysql的执行计划是在什么地方生成的？
• 如何压缩并且重建表的索引
• mysql数据类型
• SQL循环语句
• key_len表示被使用索引列的长度
• lenngth()表示字节数；char_length()表示字符数
• sum()和avg()函数的限制是
• 加在表上的锁
• 能观察到锁的信息
• 关键字INTERVAL

• 基础语法

• 数据库操作
• 表的操作
• 数据的操作
• 常用的列属性
• 索引示例
• select
• where
• ==group by==
• order by
• limit
• union联合查询
• join连接
• 子查询（嵌套查询）

• 视图
• 事务

• 事务的定义及作用
• ==事务的四大特性==
• 并发下事务会产生的问题3个
• 事务的隔离级别5种

• 事务的操作：
• 直接用 SET 来改变 MySQL 的自动提交模式:

• 权限
• ==delete、truncate、drop==
• 常见的函数
• information_schema

• information_schema定义
• information_schema组成对象
• 常见的信息查看
• information_schema中包含的信息，一部分和show语句的信息相同

• performance_schema
• MySQL 存储过程
• MySQL 复制表
• 获取服务器元数据信息
• MySQL 及 SQL 注入
• bin log、undo log、redo log的区别
• 
  

了解SQL语句在Mysql内部是怎么流转的？

MySQL的体系结构

redis缓存？抗住数据的高并发

MySQL主从架构：主要数据库接收用户传过来的数据变化，从数据库接收主数据库中的变化

MySQL是一个千万级的数据结构，一张表的数据量控制到几千万
要存储大数据量——采用分布式存储，一张表分散到几个数据库中去存储

要学习分析数据库内部的性能

• MySQL语句是单进程多线程的，所有的解析优化都是一个进程完成的
• 主键是非空且唯一的
• ENGINE：表中的主键引擎方式。MySQL5.5之后默认创建的数据库引擎为ENGINE=InnoDB。若没有指定主键，则MySQL会找第一个非空且唯一的数据作为主键。若都没有，则会默认建立一个隐藏列，这个隐藏列就会作为非空且唯一的6字节主键索引。因此不要求必须要有非空且唯一列。

MySQL存储方式

• InnoDB（聚簇索引cluster table）：内部是按照主键的形式存在的，主键所在的叶子节点位置就是这条数据的位置。要是非主键索引来查数据，会先找到非主键指向的数据行，然后由这行数据中的主键索引再次查询找到这条数据的索引位置 。
  考虑索引的性能需要考虑树的高度，当需要取出的数据量比较小的时候才适合用索引查找
• MylSAM（非聚簇索引）：是一个典型的堆表，来一个数据堆存储，来一个数据堆存储——逐渐淘汰 。 由主键索引的树结构进行折半查找，找到相应的员工号所在叶子节点，里边存员工号的值与具体所在的行号指针，指针指向这条数据所在的行

两种存储引擎的区别

• 区别

特性	InnoDB	MylSAM
事务支持	支持事务	不支持事务
外键支持	支持外键	不支持外键
聚集索引	聚簇索引	非聚簇索引
表的具体行数	不保存表的具体行数	保存表的具体行数
行级锁	支持行级锁（并发优势）	不支持行级锁
主键	必须有主键	非必须

1. MyISAM的性能更高一些
2. InnoDB的B+树主键索引的叶子结点节点就是数据文件， 辅助索引的叶子节点是主键的值（需要回表）；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针
3. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描；而MyISAM用一个变量保存了整个标的行数，执行上述语句时只需要独处该变量即可
4. InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。因此如果没有命中索引，也就无法使用行锁，将要退化为表锁。行级锁可以最大程度的支持并发处理，同时带来了最大的锁开销

• 如何选择
  （1）看是否要支持事务，支持的话选InnoDB
  （2）若表中绝大多是都只是读查询，可以考虑MySAM；若既有读也有写，使用InnoDB
  （3）系统崩溃后，MySAM恢复更困难，能否接受

InnoDB存储引擎锁机制常见问题

参考文献

1. 事务锁等待超时怎么处理？
   参数 innodb_lock_wait_timeout 控制事务等待锁的超时时间，默认是 50s ，如果事务发生超时错误，是不会自动回滚数据库，而是隐式的提交了，需要程序捕获异常，人工回滚事务。

SQL语句执行过程

从SQL语句执行过程开始：
Commander Sispatcher：命令分发器 Query Cache Module：缓存，方便重复取值——一般不用，8.0之后版本就没有了
|
Parser：解析SQL语句——下边有多个优化的分支
|
Access Control Module：有没有访问权限
|
Table Manager
|
Abstracted Storage Engine Module：抽象存储引擎——InnoDB、MylSAM

1 服务层

2 核心层（mysqld） 指的是CPU和内存部分

包括SQL接口、解析器、优化

3 存储引擎层 硬盘

SQL语句执行步骤

语句执行之前需要先进性管理连接和权限验证，通过之后再进行SQL语句执行

1. 分析器——解析： 根据SQL语句第一个单词把SQL语句路由到相应的位置 耗费CPU

• 词法解析：看你要查的这个表的这个列存在不存在
• 语法解析：看SQL语句语法对不对

2. 优化器： 生成真正的执行计划，并进行索引的选择

• SELECT——Optimizer
• UPDATE,etc.——Table Modification Module (Update etc.)
  MySQL不缓存执行计划，用的是硬解析，需要CPU去解析
  MySQL8.0之后query cache缓存被去掉了

3. 执行器： 到不同的存储引擎中把数据查取出来 耗费的硬盘资源
   MySQL数据文件：一张表一个文件 比如emp表会有文件emp.ibd存数据， 文件emp.frm存表结构文件
   InnoDB buffer pool是一个大的存储空间
4. 存储引擎： 存储数据提供读写接口
5. 获取： 若为取数据则直接取，若有排序则还需进行排序再获取数据

• MySQL数据访问状态：
  idle空闲
  active
  session会话

MySQL的逻辑架构

mysql可分为Server层和存储引擎层

• Server层：包括连接器、查询缓存、分析器、优化器、执行器等，覆盖了大多核心服务功能；
• 存储引擎层：负责数据的存取和提取。其架构模式是插件式的，支持InnodB、MyISAM、Memory等多个存储引擎。最常用的是InnoDB，从mysql5.5.5版本开始就是默认的存储引擎

1 连接器

• 第一步就会连接到这个数据库上，这时候接待你的就是连接器
• 连接器：负责跟客户建立链接、获取权限、维持和管理连接
  mysql -h -p -u

2 查询缓存

• mysql拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句会以key-value的键值对形式存储在缓存中，key是查询的语句，value是查询的结果，若能直接找到相同的查询语句key就直接从缓存里取出数据返回给客户端即可
• 若语句不在查询缓存中，就会继续执行后面的执行阶段

3 分析器

• 词法分析：你输入的字符串里都有啥
• 语法分析：根据你输入的这些东西，对其进行语法规则分析判断是否符合mysql语法
  （这两个分析解析的时候若有误，系统会提示相应的错误，类似于java中的catch）

4 优化器

• 经过分析器，mysql就知道你要弄啥了
• 但是要先对你要执行的操作进行优化，比如要用哪个索引或者多表关联时要决定各个表的连接顺序

5 查询执行引擎

• 根据优化器生成的执行计划，调用存储引擎的API来执行分布distribution查询；
• 判断权限：执行的时候先判断你对这个表有没有执行查询的权限，若没有权限会返回没有权限的错误
• 若有权限：打开表继续执行，调用所使用的引擎
• 调用InnoDB引擎接口取这个表的第一行，做判断看是不是你要的数据，不是则跳过，是则将结果集中；继续调用存储引擎接口取“下一行”，重复相同的逻辑判断，直到取到这个表的最后一行
• 执行器将所有满足条件的行组成的记录作为结果返回给客户端。至此结束。

数据库处理一个查询的步骤

1. 客户端发送一条查询给服务器；
2. 服务器先会检查查询缓存query ***，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
3. 服务器端进行SQL解析parsing、预处理transition，再由优化器optimization生成对应的执行计划；
4. 根据优化器生成的执行计划，调用存储引擎的API来执行分布distribution查询；
5. 将结果返回给客户端。

InnoDB buffer pool原理

• InnoDB buffer pool即InnoDB存储引擎内存模型——缓冲池
  InnoDB buffer pool存储的是索引和数据
• InnoDB buffer pool以LRU（Latest Recent Use，最近最少使用）链表的方式存储数据
• 作用：缓存索引和数据
• warm up预热，让表缓存到内存池InnoDB buffer pool，防止系统崩溃
• Instance参数：设置CPU数量
  %：通配符代表任意字符串
• 每个用户创建之后就会生成一些独有的内存缓存：sort buffer、join buffer、mult-range read buffer(MRR)
• 内存中会产生脏数据dirty date
  写脏数据和是否写commit没有关系，取决于当前磁盘的IO压力
  commit提交事务：将日志永久的保存到磁盘里
  rollback取消事务事务
  事务：就是对数据做修改，最后以确认commit或者回滚rollback为结束。
  事务的四大特性：原子性（commit 和 rollback，要么都成功要么都失败）、一致性（UNDO保证）、隔离性（锁）、持久性（redo 日志）
• 了解InnoDB buffer pool的两个进程：
  IO write thread *n：写数据线程 线程的个数不能超过CPU的核数
  IO read thread *n：读数据线程
• ibdata1文件：存放undo日志，里边存数据被修改之前的值【前镜像】，UNDO里边的值在commit之后就失效了
  undo日志作用：
  （1）没有提交或者回滚的时候，做一致性读取【前镜像】
  （2）回滚rollback之后，就支持事务的回滚
• log buffer日志缓存：当缓存内容达1M就会存到磁盘
  ib_logfile0文件、ib_logfile1文件：保存REDO日志，redo日志太小则会导致数据库hang住
  redo日志: 当数据库对数据做修改的时候，需要把数据页从磁盘读到buffer pool中，然后在buffer pool中进行修改，那么这个时候buffer pool中的数据页就与磁盘上的数据页内容不一致，称buffer pool的数据页为dirty page 脏数据，redo日志记录相应的数据修改内容（记录日志是顺序IO），因此当DB发生crush的情况，回复DB的时候，可以根据redor日志将这个文件的记录内容重新应用到磁盘文件，使得数据保持一致。
  数据库服务器宕机，如果发生其他故障导致尚有脏页未写入磁盘的场景，也是可以通过redo log恢复的。
  redo 日志不可以归档！
  log buffer 进程：log thread
  数据库软件设计第二大原则：日志先行Write ahead log（WAL）,日志记录先一切操作
  断电后内存中的数据丢失，磁盘中的数据还在
  commit操作就是将log buffer中的日志刷到redo log里，这叫落盘，redo log主要节省的是随机写磁盘的IO消耗（转成顺序写）
  要尽量避免随机读，物理损耗太大，减少commit提交量
  参数：inndb_flush_log_at_trx_commit：为1则严格执行commit一次刷一次盘； 为0则每秒钟刷一次盘（有可能丢失1秒钟的数据），可以提高事务的执行速度，因为每攒够1秒钟就刷一次数据； 为2则每commit一次则把其存到操作系统的一个缓存区里，每1秒钟再把缓存的commit数据统一刷到磁盘里。
• redo log执行流程图：



• 关系型数据库瓶颈：并发。 多个生产者应对一个消费者

mysqld 内存+CPU	log buffer
engine 存储引擎	redo log

• 在MySQL的innodb引擎中如何查看未完成的事务：通过information_schema.innodb_trx视图
• length和char_length的区别：
  select char_length(“xxx”)：返回存储这个字符串所占的字符数，字母、数字、汉字都当成一个字符处理。
  select length(“xxx”)：返回存储这个字符串所占的字节数
  utf8编码：一个汉字三个字节，一个数字或字母一个字节。
  gbk编码：一个汉字两个字节，一个数字或字母一个字节。
  按照参数值character_set_client=xxx来计算，若=utf8则是一个字符占3个字节，若=latin1则一个字符占一个字节
  数据库的变量合集：show variables like '%xxx%'

varchar是变长类型

索引设计

• 索引目的：提高查询效率，就像书的目录一样

MySQL索引

• 索引分单列索引和组合索引。
• 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。
• 组合索引：一个索引包含多个列
• 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录

常见索引模型

主键索引也叫聚簇索引

• 哈希表索引
  插入速度快；没有办法做范围性查询
• 有序数组
  插入更新速度慢；支持范围查询；
  只适合一些历史表
• 二叉树
  把折半查找形象化了；
  查找速率最快；
  只能挂两个分支，数据越多树层数越多，效率越低，因此受限制，数据库不使用
• N叉搜索树 也叫 B+树 也叫 平衡搜索树
  Balance平衡
  N叉树中的N取决于数据块的大小
  树高是多少每次取这个数据就需要经过多少次IO
• InnoDB索引模型：
  主键ID索引：聚簇索引
  K值索引：二级索引、辅助索引

1. 若语句是主键查询方式，则只需要搜索ID这颗B+树；
2. 若语句是普通索引查询方式，则需要先搜索K索引树，得到ID值，再到ID索引树搜索一次，这个过程称为回表
   若select的内容只包含k和ID，则不需要回表

• InnoDB索引维护：插入新值得时候需要做必要的维护。比如在建表的时候加上自增主键。
  不涉及到挪动其他记录，也不会触发叶子结点的分裂
  若用整型（int）做主键，则只要4个字节，若长整型（bigint）则是8个字节
  主键长度越小，普通索引的叶子结点越小，普通索引占用的空间也就越小。
• select * from T where k between 3 and 5需要执行6次树的搜索操作。每次搜索都需要从根节点开始搜索
  图：
  3 7
  1 2 3 5 6
  100 200 300 500 600
• 覆盖索引 —— 常用的索引优化手段
  目的：减少回表
  设计表的时候索引有(name, age)的时候是一个联合索引，实则有三列，因为主键id也暗自包括在里边了，(name, age)就是把年龄和姓名拼接起来存储。
  联合索引必须第一列出现的时候才会有效，最左列的分支将数据先定义到某一个分支，然后才去匹配后边的列。——最左前缀原则。当通配符%出现在最前边的时候，索引失效。
• select concat与select ws_concat的区别
  concat(str1,str2,...)：concat是直接将字符串进行拼接
  concat_ws(separator,str1,str2,...)：ws_concat会把第一个字符当做分隔符，然后再用该分隔符将字符串连接起来
• user()和current_user()
  user()返回当前登陆的用户user
  current_user()返回这个用户user在数据库mysql.user表里对应的账号
• 获得当前日期：curdate()
  date是数据类型不是函数
• 收集统计信息：analyze table test1;（test1表示表名）
  查看统计信息：show index from test1;
• 唯一索引与普通索引
  唯一索引：列中没有重复的值，保证了数据的唯一性。主键一定是唯一性索引。一张表只能有一个主键，但可以有多个唯一索引
  普通索引：可以有重复的值。
  （1）若创建的是唯一索引，则每次插入值的时候都会走一次索引，查询是否有重复的数据。唯一索引的每一列都是不一样的。由于索引定义了唯一性，在查找到第一个满足条件的记录后，就会停止检索，返回找到的数据
  （2）普通索引：在查找到满足条件的第一个记录（5,500）之后，还需要查找下一个记录，直到碰到第一个不满足k=5条件的记录
  唯一索引，修改时要判断唯一性，change buffer失效
  普通索引，修改时不需要读取记录
  （3）性能：普通索引比唯一索引多做了一次内存搜素和判断，性能相差不大
  （4）区别：数据不在内存时候，唯一索引要加载数据判断唯一性，普通直接存入change buffer，返回
• Innodb引擎的change buffer
  change buffer 是对数据进行修改的时候(insert、update、delete)的时候将修改优先写入change buffer，减少磁盘的随机IO消耗
  change buffer是Innodb buffer pool特有的
  把修改先缓存到change buffer，当数据缓存到一定程度之后，再将修改进行分类刷到磁盘，减少物理移动的次数， changebuffer因为减少了随机读磁盘的IO消耗，所以提升了更新数据的性能
  若要修改的列有唯一索引，则change buffer失效
  change buffer劣势： 不适合于马上写就要马上读的操作；不适合写多读少的业务；不适合账单类、日志类业务

delimiter指令：重新定义分号;，若没有重新定义则每句的结尾就是以分号结束，重新定义可以自定义结束符号

call指令：调用存储过程

索引的优缺点

索引的优点

• 加快数据检索（这也是我们创建索引的主要原因）
• 创建唯一性索引，保证数据库表中每一行数据的唯一性
• 加速表于表之间的链接
• 使用分组和排序子句对数据检索时，减少检索时间
• 使用索引在查询过程中，使用优化隐藏器，提高系统的性能

索引的缺点

• 创建与维护费时间；
• 索引占用物理空间和数据空间
• 表中的数据插入、删除、修改和维护速度下降（因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件。并且建立索引也会占用磁盘空间的索引文件）

索引的分类

• 普通索引：加速查询 ——是最基本的索引，没有任何的限制
• 唯一索引：加速查询+列值唯一（可以有null） ——索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
• 主键索引：加速查询+列值唯一（非空，且不能有null）+表中只能有一个
• 组合索引：多个列值组成的一个索引，专门用于组合搜索（效率大于索引合并）
• 全文索引：对文本内容进行分词搜索（只有MyISAM引擎支持）

表连接

表连接的执行计划

-- 假设t1有100条数据；t2有1000条数据
-- 表连接查询语句：
select * from t1 join t2 on(t1.a=t2.a)
-- 查看表连接这个操作的执行计划指令：`explain`
explain select * from t1 join t2 on(t1.a=t2.a)

force index：强制让表查询走索引

不走索引的

• 任何带 函数、表达式、含不等于！或<>的列、包含null 的查询条件不走索引
  ！、 <> 、 is null、 is not null不走索引
  in可以走索引，not in不可以走索引
  like 'h%'可以走索引，like '%h'不能走索引，最左原则
  and走索引，OR不走索引
  SQL里null与任何值做计算都是null

常用的表连接方式

Nested-Loop join：嵌套循环连接

1. Index Nested-Loop Join： 嵌套循环，被驱动表上有索引
   Index：有索引
   （1）先选一个数据量比较小的表作为驱动表，对表t1做一次全表扫描，前提是被驱动表里边有索引；
   （2）每从t1里拿出一行，就在被驱动表t2中进行1次索引查找，一共100次；
   （3）返回结果；
2. Simple Nested-Loop join： 即被驱动表t2没有索引，需要做M*N次扫描
   Simple：没索引
3. Simple NLJ的优化手段：Block-Nested-Loop join： Block：分块
   优化方法：先把驱动表缓存到join buffer里，这个区域属于内存区域这样做全表扫描的时候就非常快，然后再用被驱动表去扫描驱动表 扫描的数量个数没有变，优化的点在于驱动表放在了内存区域中的join buffer里，这样查询速度快了。
   Block NLJ问题：用join buffer将其中的一个较小的表缓存到join buffer中，若两个表的数据都比较大，则需要将join buffer的大小调大一些，方便使用。
   join buffer不适合高并发的场景。
4. Batched Key Access（BKA）：Index NLJ的基础上做MRR优化，解决IO过多问题

• 四种表连接的优化方法：
  Batched Key Access（BKA） > Index NLJ > Block NLJ> Simple NLJ
  怎么使用：设置优化方法参数set optimizer_switch = 【要启动的优化方法】 要启动的优化方法中：
  block_nested_loop默认是on的；1
  bached_key_access默认是off的，要打开的话还需要把MRR的设置也启动，即 mrr=on, mrr_cost_based=off;

辅助索引范围扫描的优化方式

• MRR（multi range read）：优化器对二级辅助索引扫描的优化方法
  MRR也是每次用户一登入都会给用户分配的一个内存的buffer
  MRR(Multi-Range Read )：将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段
  MRR的使用过程：先通过二级索引取出满足条件的二级索引和主键放到缓冲区（大小由参数read_rnd_buffer_size控制）中，当该缓冲区满了之后，再通过主键进行排序，最后按照排序后的结果集取表中的数据。
• show variables like '%buffer%'：查看参数中有buffer字段的变量都有哪些
  read_rnd_buffer_size：存储辅助索引范围扫描后的结果，对其进行分类优化
  optimizer_switch：控制优化器能够使用什么算法，用参数指定
• 当二级索引范围扫描比较多的时候，就可以启动MRR扫描：set optimizer_switch = 'mrr=on, mrr_cost_based=off'
• 表连接的Index NLJ就可以用到这个MRR优化方法——Batched Key Access（BKA）
  将辅助索引主键缓存到join buffer里
• 用到join buffer的表连接优化方式有： BNL、BKA

MySQL函数

三种函数

• String：处理字符串
• Data and Time：处理时间类型
• Numeric：处理数字

string函数

• 获取字符串的长度：
  select length('xxx');返回字节数（byte）
  select char_length('xxx');返回字符个数
• instr(string, subdtring)：返回substring在string中的位置，从1开始数
• strcmp(string1, string2)：返回两个字符串的比较。返回值0：相同；-1：string1比string2要小；1：string1比string2大
• 字符串拼接：
  concat('str1', 'str2', 'str3')：将字符串拼接在一起
  concat_ws('sep', 'str1', 'str2', 'str3')：用分隔符sep将字符串拼接起来
• reverse('xxx')：把字符串取反
• 截取字符串：
  left('xxx', num)：从左到右截取num个字符
  right('xxx', num)：从右到左截取num个字符
• 变大小写：
  lower()：全部转换成小写
  upper()：全部转换呈大写
• 位数不足则补位
  lpad('MySQL', 9, '.')：’…MySQL’
  lpad('MySQL', 9, '.')：‘MySQL…’
• 从固定位置截取指定位数的字符串
  substring('xxxxxx', 位置，要截取的位数) .
• 用字符s分割字符串，取出其中的前n个字符串，n为正数则从左往右数的前n个字符串，n为负数则为从右往左数的前n个字符串
  substring_index('xxx1sxxx2', 's', 1)：xxx1
• 去除空格或者指定的字符
  trim( MySQL )：‘MySQL’
  trim('x' from 'xxxMySQLxxx')：‘MySQL’去除字符串中指定的字符
  leading去掉头部的空格

Date and Time函数

时间字段尽量使用时间类型，对时间类型数据的加减操作也是
data

函数	语法
获取当前的日期以及时间	select now()
获取当前的日期	curdate()
获取当前的时间	select curtime()
获取年	year(data_expression日期类型的参数)
获取月	monthname(data_expression)
获取日	dayname(data_expression)
获取时、分、秒	hour(data_expression)、minute(data_expression)、second(data_expression)

数据类型	默认的格式
DATE	YYYY-MM-DD
TIME	HH:MM:SS
DATETIME	YYYY-MM-DD HH:MM:SS
TIMESTAMP	YYYY-MM-DD HH:MM:SS
YEAR	YYYY

numeric函数

• 四舍五入
  round()：四舍五入到整数
  round(num, n)：四舍五入到小数点后的第n位
  特殊：round(23.298, -1) = 20
• 截取：
  truncate(num, n)：不考虑四舍五入的截取小数点后的n位
  特殊：truncate(122.999, -2) = 100
• 无论大小均进数：ceiling()、ceil()
• 查看当前用户：
  user()：查看当前登录的用户
  current_user()：查看当前用户在mysql.user数据表里的位置，即数据字典里对应的内容

create user 'jack'@'%' identified by 'jack'; -- 创建用户jack可以从任意主机登录
mysql -h localhost -p3306 -ujack -p jack; -- 登录创建的用户

select语句中的聚合函数

• count()：计数 select count(*) from tble1; – 统计数据的条数
• sum()：求和
• group by：分组
• having：对group by之后的行再进行条件筛选，即对分组再进行过滤
• group_concat()：分组后把每组的内容放在group_concat()这个列里
  语法：group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] ) 例如：以id分组，把price字段的值在一行打印出来，分号分隔select id,group_concat(price separator ';') from goods group by id;

union

union：并集去重， 需要先缓存在temparary buffer临时buffer里进行去重， 有主键
union all：合并不去重

group by

• group by原理：用到了temporary buffer，在这里新建一个临时表用来存储两列数据，一个是要聚合的字段名，一个是计数器count()的结果
  执行计划的extra里的字段是：using temporary
• group by耗费的是CPU，每次拿到值都需要在临时表里进行扫描，并且默认的会对group by的结果进行排序，若不需要默认排序则order by null即可
• 一张表最多4~5个索引

使用到临时表temporary buffer的操作

• 使用到临时表temporary buffer的操作：group by 、union 、 distinct

执行计划的阅读

• 在SQL语句前加explain，调查最终查询的执行计划，这条SQL语句是不执行的，只是打印出执行计划
• 执行计划的内容：
  id：SQL语句里简单标识符
  select_type：描述正在执行的select类型，一般是simple
  table：表名
  partitions：查询将要匹配记录的分区
  type：查询方式（全表扫描、索引） all：全表扫描 ref：等值索引扫描 range：索引范围扫描 index：索引扫描 符合最左前缀原则
  possible：可能用到的索引都有啥
  key：最终用到的索引
  key_len：用到的索引所占的字节数 int(x)表示整数，永远占4字节，因为x是这个整数的位数
  ref：
  rows：估计要取出select的数据大概要扫描多少行
  filtered：过滤率是多少，即最终得到的数据行数与扫描的行数的比值
  extra：附加信息 using filesort：用了用户自身的sort buffer去排序

快速创建一张和已存在表结构数据一样的表：create table cityex as select *from city; 项目中创建一张类似的表可用该方法，然后再将不需要的字段删除，将需要的字段插入即可

analyze table 表名：手动收集统计信息（不加分号）
查看统计信息：show index from 表名

创建复合索引：alter table cityex add index idx_name_id(name, id);

MySQL中的锁

锁保证了事务的隔离性

锁的粒度

按锁的粒度划分，锁可分为表级锁、行级锁、页级锁。

• A、行级锁
  开销大，加锁慢，会出现死锁，锁定力度最小，发生锁冲突的概率最低，并发度高。
• B、表级锁
  开销小，加锁快，不会出现死锁，锁定力度大，发生冲突所的概率高，并发度低。
• C、页面锁
  开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定力度介于表和行行级锁之间，并发度一般。

mysql的存储引擎和锁机制

mysql不同的存储引擎支持不同的锁机制。

• InnoDB支持行级锁、表级锁，默认情况采用行级锁。
• MyISAM和MEMORY存储引擎采用表级锁。

全局锁

使用场景：做备份
加锁命令：Flush tables with read lock 备份时用，所有更新语句,数据定义语言,commit/rollback 都会被阻塞
解锁命令：unlock tables

表级锁

• 表锁：lock table：有读锁和写锁（排它锁）
• 元数据锁：MDL锁：保护锁的结构不受修改

表锁

每登录一个用户就是进入一个session会话

• 读锁：

-- 读锁
lock table emp read;
-- 解锁emp表
unlock table emp;

• 写锁：

-- 写锁，也叫排他，其他人不能做任何操作
lock table emp write;
-- 解锁所有表
unlock tables;

• 隐士上锁：
  select读锁
  insert、update、delete写锁

元数据锁 MDL（metadata lock）

• 目的：保证读写的正确性
• 特点：不需要显式的使用在访问一个表的时候会被自动加上
• 增删改查时会自动加上MDL读锁；对表结构变更时会自动加上MDL写锁
• 
  
• 参数tx_isolation：可以设置MySQL事务的隔离级别
  begin;：开启一个事务
• 要注意线上加索引可能会导致整个数据库锁住
• select后边最好不要加 * ，需要什么则查询什么，这样便于索引优化

排序

• 排序的原理：
  用了排序则在extra：附加信息里有 using filesort，即使用了sort buffer，不能证明使用了磁盘排序

原因：
小文件：直接在sort buffer 里排序
大文件用了磁盘排序：先把要排序的内容从磁盘里加载到Innodb buffer pool，然后将其分成多个小部分再sort buffer里进行排序，每次排好序的临时文件数据就放到磁盘里，然后再将每一个排好序的临时文件中的最小数据再拿出来进行排序

再设计上要尽量避免大数据量的排序，可以在业务上进行区分等等

select * from cityex order by population desc; --使用filesort
delect population from cityex order by population desc; --使用了索引排序
delect name, population from cityex order by population desc;  --使用filesort

行级锁：共享锁S、排它锁X

• 行级锁分为：共享/读锁（S）、排他/写锁（X）
• 共享/读锁（S）：一个事务获得了共享锁S，其他事物可以获取共享锁S，不能获取排它锁X。获取共享锁S的事务和其他事务可以进行读操作，不能进行写操作
  加共享锁：select * from table lock in share mode
• 排他/写锁（X）：如果事务T对数据A加上排它锁后，其他事物不能再对A加任何类型的封锁。获取排它锁X的事务和其它事务既能读数据，又能修改数据
  加排它锁：select * from table for update

两锁协议

• 两段锁协议是指每个事务的执行可以分为两个阶段：生长阶段（加锁阶段）和衰退阶段（解锁阶段）
• 加锁阶段：在该阶段可以进行加锁操作。
• 解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作
• 两段锁协议要求每个事务中，所有的封锁请求先于所有的解锁请求。

行锁

• 隐士上锁：insert、update、delete自动加排它锁
• 显示上锁：加共享锁S：select * from table lock in share mode；加排它锁X：select * from table for update；
• 解锁：事务提交commit、事务回归rollback、kill阻塞进程

死锁

• 并发中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，成为死锁
• 死锁发生时会撤销其中的一个事务，并恢复到初态
• 不同存储引擎锁：myisam是表锁、Innodb是行锁和表锁

重点总结

• change buffer 和 MRR都是Innodb特有的
• Innodb索引的特点：
  （1）最左原则
  （2）二级索引要回表（即到主键索引中再查一遍）
  （3）支持索引扫描
• Innodb的索引：
  聚簇索引（整张表的主键，放在叶子上面的）
  唯一索引
  普通索引
  前缀索引（取索引字段的前几位作为索引，需要考虑前缀的区分度）
• 唯一索引和普通索引的区别：
  只有普通索引能用change buffer
  普通索引适合于频繁写入，但不常读取的操作
  对于写完即读的情况，二者没有区别
• select * from cityex where upper(name)='BEIJING';这种操作不走索引MySQL里不走函数索引
• 复合索引扫描时符合最左前缀原则。不符合最左前缀原则时，不走索引
  要定位到上面的分支才能走下面的分支，没有索引跳跃扫描
• explain中的type：数据扫描的方式，为ref或者range即走了索引扫描
  =ref时为等值索引扫描
  =range为走索引扫描
• possible key：可能用到的索引
• key_len：用到的索引长度。若为复合索引则会只显示用到了那几个索引的长度
  int(20) 长度4 id int 长度为5（空值位占1位）
  cahr(20) 长度20
  varchar(20) 长度22 -utf8 占20*3+2（变长占两位）=62位
  datetime 长度8
  date 长度3
  如果字段可为 null，则需要额外再加1
• 表连接的优化方法： Batched Key Access（BKA） >
  Index NLJ（最常用） > Block NLJ（是Simpe NLJ的优化，用的join buffer）> Simple NLJ（Index NLJ中没有被驱动表则会退化为这个）
• 二级索引的范围查询：用MRR（Multi Range Read）
  MRR也是每次用户一登入都会给用户分配的一个内存的buffer
• Batched Key Access（BKA）与Block NLJ会用到join buffer
• 用了order by的查询会在extra：附加信息中显示 using filesort，只说明用了sort buffer，不能证明用了排序
• 为了优化sort buffer，会给order by后面加上索引，这个索引要求和select后面的内容相对应

主从复制用途

• 如果要求写入即读，只能采用单实例，即主库写主库读
  主库接收变化，从库接收主库发生变化的log，然后再自己花时间去与主库同步
  半同步复制可以防止主从复制的时候数据丢失
• Innodb存储引擎的两个日志：
  binlog 日志：记录完整地事务；以commit的顺序来进行记录；不能rollback，是主库与从库之间的桥梁；
  和redo 日志一样也在Innodb里
• sync_binlog：=1（commit一次写一次盘），=0（每秒钟落一次盘），=n（每n次提交刷一次盘）
  参数sync_binlog与innodb_flush_log_tx_commit：
  都设为0，双0模式性能最高； 但会丢失数据
  都设为1，双1模式最安全的；
• PXC主从架构业务使用场景：
  多点写入
  数据同步
  并发复制
  快速维护
  自动克隆
• PXC分布式数据库的障碍：
  无法实现即时读写，只是把日志先传递到了其他的库
  乐观锁定

基于中间件的分布式

分布式：
查询和修改尽量在一个分片上

补充知识

在MySQL的innodb引擎中的information_schema

• 通过information_schema.innodb_trx：查看未完成的事务
• information_schema.innodb_lock_lock：查看锁
• information_schema.innodb_lock_waits：查看锁等待
• information_schema包含tables、triggers、views等等server运行过程中的元数据信息
• 
  

查看mysql当前活跃的并发连接数

• Threads_running ：这个数值指的是激活的连接数，这个数值一般远低于connected数值。Threads_running是代表当前并发数
• Threads_connected ：跟show processlist结果相同，表示当前连接数

mysql的执行计划是在什么地方生成的？

• 优化器

如何压缩并且重建表的索引

• optimze table city;

mysql数据类型

整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
浮点数类型包括 FLOAT 和 DOUBLE
定点数类型为 DECIMAL。

SQL循环语句

declare @i int 
set @i=1 
while @i<30 
begin 
insert into test (userid) values(@i) 
set @i=@i+1 
end

• loop…end loop
• repeat…end repeat
• while…end while

key_len表示被使用索引列的长度

lenngth()表示字节数；char_length()表示字符数

• lenngth()一个汉字占3个字节；一个字母或数字占1个字节
  length(“你好”)=6
  length(“he”)=2
• char_length()中无论汉字/字符还是数字都占1个字符
  char_length(“你好”)=2
  char_length(“he”)=2

sum()和avg()函数的限制是

• 不支持字符类型
• 不支持临时数据
• 不能用其他的聚合函数作为参数

加在表上的锁

• 元数据锁：MDL锁（metadata lock）
• lock table

能观察到锁的信息

• information_schema.innodb_lock
• show engine innodb status;

关键字INTERVAL

• 直接计算时间间隔
  INTERVAL关键字可以用于计算时间间隔
  例1：查询当前时间之前2个小时的日期：SELECT NOW()-INTERVAL '2' HOUR; 注：INTERVAL后面的数字可以用数字格式或者字符格式，当时间单位是YEAR_MONTH这种时，必须用字符格式。
• 用在时间函数中
  INTERVAL关键字可以用在DATE_SUB(),SUBDATE(),ADDDATE()等函数中。
  例1：查询两天前的时间：SELECT NOW(),SUBDATE(NOW(),INTERVAL 2 DAY);

基础语法

数据库操作

数据库操作

-- 数据库选项：字符集charset （一般默认utf8）、校对规则collate（bin结尾的是二进制码进行比较；ci结尾不区分大小写；cs结尾区分大小写）
create database test default [数据库选项];
create database test default charset utf8 collate utf8_bin;

-- show查看当前有哪些数据库
show databases;

-- 查看数据库的创建语句
show create database [数据库名];

-- alter修改数据库的选项信息
alter database [数据库名] [新的数据库选项];

-- drop删除数据库
drop database [数据库名];

表的操作

表的操作
-- create 创建表
create table user(
id int(10) unsigned not null auto_increment comment 'id',
name varchar(4) not null comment '姓名',
age int(10) not null default 0 comment '年龄',
primary key (id)
)engine=InnoDB default charset=utf8 comment='学生表';

-- show查询当前数据库下都有那些表
show tables;
show tables like '%表';
show create table '表名';

-- desc查看表的结构：显示表结构、字段类型、主键、是否为空等属性，但不现实外键 
desc '表名'; 

-- drop删除表
drop table '表名';

-- alter修改表
alter table 旧表名 rename to 新表名;

-- add增加一列 、drop删除一列 、modify修改字段类型 、first把某个字段排在最前面 、after列放在某列后面
alter table 表名 add 新列名 字段类型[字段选项];
alter table 表名 drop 字段名;
alter table 表名 modify 字段名 新的字段类型[新的字段选项];

-- 关键字：first 修改字段的排序，把某个字段放在最前面；
alter table test modify name varchar(100) not null default 'admin' comment '修改后名字' first;
-- 关键字：after 修改字段排序，字段名1放在字段名2的后面
alter table 表名 modify 字段1 字段类型[字段选项] after 字段名2;
alter table test modify name varchar(100) not null default 'admin' comment '将字段name移动到time字段后' after time;

-- change重命名字段
-- 例如：把表name字段重命名为username
alter table test change name username varchar(50) not null default comment '用户名字';

-- 修改表选项
alter table 表名 表选项信息;
alter table test engine Myisam default charset gbk;

数据的操作

数据的操作
-- insert 插入数据
insert into user(id,name,age) values(1, 'rose', 18);

-- select 查询语句

-- delete删除
delete from user where age<1;

-- update更新数据
update user set age=100 where name like '%se';
update user set age=100 where name like '%se' order by id asc limit 3;

常用的列属性

• 常用列的属性：null、 not null、default、priamry key、auto_increment、comment
• 空属性：null和not null。默认属性值为null空，空数据没有意义。实际开发中应尽量保证所有数据都不应该为null
• default：自定义默认属性，通常配合not null一起使用
  例如：

create table test1(
a int not null default 10,
b int
)

insert into tst1(a,b) values(default, 20);

• priamry key主键： 被强制定义成not null和unique，即非空且唯一。
  创建主键两种方式：定义字段后直接加primary key；定义完字段后定义已有的字段为主键primary key(字段)
  一张表中只能有一个字段可以使用对应的主键，用来唯一的约束该字段里面的数据。
• unique key解决了多个字段需要唯一性约束的问题
  例如：创建表的时候在最后定义unique key(name, goods)
• auto_increment自增长规则： 任何一个字段要做自增长前提必须是一个索引；自增长字段必须是整型int数字
  每次插入记录的时候，自动的为某个字段的值加上1，通常跟主键搭配

索引示例

索引示例
-- 创建主键索引
create table student(
id int(10) unsigned not null auto_increment comment 'id主键索引',
name varchar(4) not null default '' comment '名字',
class varchar(50) not null default comment '班级',
seat_number smallint(5) not null default 0 comment '座位编号'
)engine=InnoDB DEFAULT CHARSET=UTF8 COMMENT='学生表';

-- 创建唯一索引或者聚集索引
create [unique唯一索引][clustered聚集索引] index <索引名> on <表名>(<列名称>[<排序>],<列名称>[<排序>])
create unique index number on student(seat_number desc);

-- 创建普通索引
create index name_class on student(name asc, class desc);

-- 删除索引
drop index <索引名> on表名;
drop index number on stuednt;

select

-- select
select * from user;
select distinct * from user;
select math as M from score; -- 对某一列使用别名用as，as可省略

-- 关键字as：可以为每个列使用别名，适用于简化列标识，避免多个列标识重复，as也可省略

-- 虚拟表的名称：dual：
-- mysql执行select的时候会自动创建一个虚拟表
select now() from dual;

where

• 对从from获得的数据进行条件查询；表达式由运算符和运算数组成
• 常用的运算符：
  大于小于
  != 或 <>
  is null、is not null
  between A and B、 not between A and B
  in、not in
  like 通配符：_表示任意单个字符；%表示任意的字符
  逻辑运算符：&&(and) 、 ||(or)、!(not)

group by

• group by分组
• asc默认升序，desc降序
• 统计函数：注意统计函数需要配合group by使用
• count返回统计值
  conunt(*)不忽略null值，统计行的总数目；
  count(字段)会忽略null值，对特定的列具有的数据行数进行计算
  count（1）跟count（主键）一样，只扫描主键
  count（*）和count（主键）使用方式一样，但是在性能上有略微的区别，mysql对前者做了优化
• 其他统计函数：
  sum求和
  max求最大值
  min最小值
  avg平均值
• 注意：在group by子句中，select之后的字段除聚合函数外都必须出现在group by中。最终结果集中的每一个组只用一行数据来表示
• having子句（条件查询）与where条件查询的区别：
  （1）having与where的本质区别：where子句是把磁盘上的数据筛选到内存上；having子句是把内存中的数据再次进行筛选
  （2）where不能使用统计函数；统计函数一般需要配合group by才会用到having
  having与where的功能、用法相同，但执行时机不同

order by

• order by子句：对某一字段排序
• 查询score>80的数据，查询后的数据对score进行升序，name降序排序：select * from student where score>80 order by score, name desc;

limit

• limit子句：限制查询结果的数量
• limit语法：limit [offset偏移量即从哪个数据开始取，默认从0开始]，[length需要现实的记录数]

union联合查询

• union：就是将多个查询结果进行纵向上的拼接，union是去重查询。用union上下连接的各个select都不可以带有自己的order by子句
  union可以替代平常用的or
• union all：全部连接

join连接

• 内连接inner join
• 外连接：
  左外连接left join，元数据个数可能大于左边表的数据个数；
  右外连接right join，如果数据不存在，右表记录会出现，而左表为null填充

子查询（嵌套查询）

• 子查询就是：嵌入到另一个查询语句之中的查询语句
• 子查询（内查询）在主查询（外查询）之前一次执行完成，子查询的结果被主查询使用
• 使用子查询需要用括号包裹

视图

• 视图：是由数据库中的一个表或者多个表导出的虚拟表，是一种虚拟存在的表，方便用户对数据的操作。视图是存储在数据库中的查询的SQL语句。数据库中置存放了视图的定义，并没有存放视图中的数据，这些数据还放在原来的表中。
• 视图的作用：简单性；安全性；逻辑数据独立性
• 创建视图：

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(属性清单)]
AS SELECT语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

• 参数语法：
  （1）ALGORITHM：可选项，表示视图选择的算法。指定视图执行的算法，通过algorithm指定：
  merge（合并算法）：将视图的语句和外层的语句合并后再执行
  template（临时表算法）：将视图执行的结果生成一张临时表，再执行外层语句
  undefined（未定义型）：这也是默认方式
  （2）视图名：表示要创建的视图名称。视图名称必须唯一，同时不能与表重名；
  （3）**属性清单（就是字段列表）：**可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
  （4）SELECT语句：表示一个完整的查询语句，将查询记录导入视图中。
  （5）WITH CHECK OPTION：可选项，表示更新视图时要保证在该视图所定义的权限范围之内。
  字段列表若存在，数目必须等于select语句检索的列数
• 视图可以查询，但不可以新增、删除、修改。
• 视图上可以定义新的视图，不可以建立基本表！

CREATE OR REPLACE VIEW view_user
AS
	SELECT id,name FROM tb_user;

--查看视图结构
show create view 视图名称

-- 删除视图
drop view [if exists] 视图名称

-- 修改视图结构
alter view 视图名称 [字段列表] as sql语句

事务

事务的定义及作用

• 事务：是访问数据库的一个操作写序列，数据库应用系统通过事务集来完成对数据库的存取。
• 事物的作用：保证用户每一次操作都是可靠的，即便出现了异常的访问情况，也不至于破坏后台数据的完整性。

事务的四大特性

• 原子性： 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性： 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性： 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性： 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

并发下事务会产生的问题3个

• （1）脏读：就是事务A读到了事务B还没有提交的数据
• （2）不可重复读：不可重复读是指在对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，另一个事务修改了数据并提交了。
• （3）幻读：幻读是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行，就像产生幻觉一样，这就是发生了幻读。

事务的隔离级别5种

• 事务的隔离级别就是为了解决上面几种问题而诞生的。事务的隔离级别越高，并罚下会产生的问题就越少，但同时付出的性能消耗也将越大。
• （1）DEAFAULT： 默认的隔离级别。可使用select @@isolation来进行查看当前的事务隔离级别
• （2）READ_UNCOMMITTED： 读未提交，即能够读取到没有被提交的数据。无法解决上述的3个问题
• （3）READ_COMMITTED： 读已提交，即能够读到那些已经提交的数据。可以防止脏读
• （4）REPEATABLE_READ： 重复读，即在数据读出来之后加锁，类似于select * from XXX for update，防止别人修改它，这条事务不结束别人就不能修改这条记录。解决了脏读、不可重复读 mysql默认的事务隔离级别是可重复读(Repeatable Read)
• （5）SERLALIZABLE： 串行化，是最高的事务隔离级别。不论多少事务，挨个运行完一个事务的所有操作后才能执行另一个事务里边的操作。解决了脏读、不可重复读、幻读问题
  在这个级别，可能导致大量的超时现象和锁竞争。这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。
• 注意：并非事务隔离界别设置的越高越好，事务隔离级别设置的越高，则效率会降低，因为要花手段去加锁以保证事务的正确性。一般情况下设置为READ_COMMITTED即可
• 在同样的运行环境下，不同隔离级别的性能从高到低的顺序是 Read uncommitted > Read committed > Repeatable read > Serializable，与隔离级别相反！

事务的操作：

-- 开启事务
start transaction;
或者begin transaction;

-- 提交事务
commit;

-- 回滚事务
rollback;

直接用 SET 来改变 MySQL 的自动提交模式:

• 注意：修改事务自动提交 set autocommit = 0 | 1 （0取消自动提交；1自动提交）。
  mysql默认是自动提交的
• 事务的特性：
  原子性：事务中的操作要么都执行，要么都不执行
  一致性：事务发生前后数据的完整性必须保持一致
  隔离性：多个并发的事务之间是相互隔离的
  持久性：事务一旦被提交，对数据库中的数据改变就是永久性的
• 注意：
  （1）数据定义语言（DDL）语句不能回滚。DDL（create、drop、alter 数据库/表/索引/视图）
  （2）事务不能被嵌套

权限

-- 创建用户
create user 用户名[@主机地址] identified by '密码';

-- 删除用户
drop user 用户名[@主机地址];

-- 分配权限给用户
grant 权限列表 on *|库名.*|表名 to 用户名[@主机地址] [identified by '用户密码'] [with grant option]

• 分配权限给用户：grant 权限列表 on *|库名.*|表名 to 用户名[@主机地址] [identified by '用户密码'] [with grant option]
  权限列表：
  all [privileges]：表示所有权限
  delete：允许使用delete
  select：允许使用select
  update：允许使用update
  insert：允许使用insert
  *.*：表示所有库的所有表
  库名.表名：表示某个库下的某表
• 撤销权限：revoke 权限列表 on *|库名.*|表名 from 用户名[@主机地址];
  撤销所有权限：revoke all privileges, grant option from 用户名[@主机地址]; 例如：revoke update on *.* from 'user_one'@'localhost';

delete、truncate、drop

• 1.drop删除表中的数据以及定义(出手最狠) —— 就是把整个表给去掉
  drop table Student；
• 2.truncate删除表中数据，定义还在(比较温柔) —— 只是清空表数据
  truncate table Student；
• 3.delete删除表中某些数据，定义还在，但是删的比较低效(温柔型) —— 删除某些数据
  delete table Student；(系统一行一行删，保留日志，可以rollback)
  参考文献

常见的函数

char_length() -- 计算字符个数

instr() --某字符串首次出现的位置

strcmp()

information_schema

information_schema定义

• information_schema提供了对数据库元数据、统计信息、以及有关MySQL Server的信息访问（如：数据库名或标明，字段的数据类型和访问权限等）
• information_schema也可称为MySQL的数据字典或系统目录
• information_schema下的所有表是只读的，不能执行INSERT、UPDATE、DELETE等数据变更操作

information_schema组成对象

information_schema下的所有表都是使用的Memory和InnoDB存储引擎，且都是临时表，不是持久表，在数据库重启之后这些数据会丢失，在MySQL 的4个系统库中，也是 唯一一个在文件系统上没有对应库表的目录和文件的系统库 。

常见的信息查看

• information_schema.INNODB_TRX：查看当前在InnoDB引擎中执行的每个事务的信息

information_schema中包含的信息，一部分和show语句的信息相同

performance_schema

• performance_schema中setup_instruments用于记录哪些事件被支持收集
• performance_schema中存储事件信息，可以通过SQL语句控制那些事件被收集

MySQL 存储过程

• mysql从5.0版本开始支持存储过程
• 存储过程是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象
• 存储过程是为了完成特定功能的SQL语句集，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
• 存储过程就是数据库SQL语言层面的代码封装与重用。存储过程就是具有名字的一段代码，用来完成一个特定的功能。
• 存储过程保存在数据库的数据字典中
• 优点：
  存储过程可封装，并隐藏复杂的商业逻辑。
  存储过程可以回传值，并可以接受参数。
  存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
  存储过程可以用在数据检验，强制实行商业逻辑等。

MySQL 复制表

• 复制一个表的步骤：
  （1）使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
  （2）复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
• 复制一个现有表的内容到新表：
  INSERT INTO ... SELECT 语句来实现。

获取服务器元数据信息

• SELECT VERSION( ) 服务器版本信息
• SELECT DATABASE( ) 当前数据库名 (或者返回空)
• SELECT USER( ) 当前用户名
• SHOW STATUS 服务器状态
• SHOW VARIABLES 服务器配置变量

MySQL 及 SQL 注入

• 如果您通过网页获取用户输入的数据并将其插入一个MySQL数据库，那么就有可能发生SQL注入安全的问题。
• 所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。

bin log、undo log、redo log的区别

• 区别：bin log是MySQL的Service层记录的日志；undo log、redo log是InnoDB引擎记录的日志，用来支持事物。
• bin log中记录的是数据库所有增删改操作（sql语句），逻辑日志；
• redo log记录的是数据库事务操作中产生的变化，记录修改后的值；
• undo log记录的是事务操作前的数据值
• 事务的修改：
  bin log只会在事务提交后进行记录。binlog日志不会循环使用，当bin log写满时，会另写一个bin log文件；
  redo log文件是循环使用的；
  bin log适合用来做备份，主从复制就是根据bin log来实现数据同步的；
  undo log日志是MySQL用来实现事务原子性的，在InnoDB引擎中，undo log还可以用来实现多版本并发控制

---