mysql
基本数据类型
可以分为6类
1.整型 tinyint smallint int bigint
不同的int类型能够存储的数字范围是不一样的
1.要注意是否存负数(正负号需要占一个比特位)
2.针对手机号码只能用bigint
2.浮点型 float,double,decimal
float只能存小数点后7位,double能存15位,而decimal 能存完这个数字.三者的精确度不一样
float(255,30) # 总共255位,小数位占30位
3.字符串 char(4) varchar(4)
定长类型:最多只能存4个字符,多了报错少了则自动空格填充至四个
变长类型:最多只能存4个字符,多了报错少了有几个则存几个
4.枚举 enum() 多选一
enum('male','female','others')
5.集合 set() 多选多
hobby set('basketball','football','doublecolorball')
6.日期
date 年月日
datetime 年月日时分秒
time 时分秒
year 年份
存储引擎相关
# 存储引擎索引结构
Innodb:B+树
myisam:B+树
memory:hash表
blackhole:不知道
# 总共有几种?
大概十几种吧,5.7我记得支持9种吧.好像,记不太清了.
# 需要掌握的四个存储引擎
MYISAM (读音:卖艾山姆)
MySQL5.5 之前默认的存储引擎
不支持事务,行级锁,和外键.针对数据的操作较于InnoDB不够安全
但是数据的存取速度较于InnoDB更快
InnoDB
MySQL5.5 之后默认的存储引擎
因为InnoDB支持事务,行级锁和外键.针对数据的操作更加的安全
memory
基于内存存取数据
速度最快,但是断电立刻丢失
blackhole
写入其中的数据都会立刻消失,类似于垃圾处理站
"""
在mysql文件夹例data对应的库中查看上述创建的4个表,你会发现:
NyISAM会创建三个文件
.frm 表结构文件
.MYD 表数据文件
.MYI 表索引文件(索引是用来加快数据查询的)
InnoDB会创建两个文件
.frm 表结构文件
.ibd 表数据和表索引文件
memory
.frm 表结构文件
blackhole
.frm 表结构文件
"""
Mysql高可用方案有哪些?
1.基于主从复制；(常用)
2.基于Galera协议；
3.基于NDB引擎；
4.基于中间件/proxy；
5.基于共享存储；
6.基于主机高可用；
简述触发器,函数,视图,存储过程
1.触发器: 对某个表进行(增/删/改)操作的前后触发一些操作即为触发器,(触发器用于自定义用户对表的行进行(增/删/改)前后的行为) 触发器必须定义在特定的表上
触发器分为六种情况:
增加前、增加后 before insert 、 after insert
删除前、删除后 before delete 、after delete
修改前、修改后 before update 、after updata
2.函数: 内置函数
自定义函数
3.视图
视图是查询命令结果构成的一个虚拟表(非真实存在)，其本质是【根据SQL语句获取动态的数据集，并为其命 名】，用户使用时只需使用【名称】即可获取结果集合，并可以当作表来查询使用。
4.存储过程:存储过程(procedure)，概念类似于函数，就是把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现。在封装的语句体里面，可以同if/else ,case,while等控制结构，可以进行sql编程，查看现有的存储过程。
mysql常见的函数
ABS()
BIN()
CEILING向上取整 FLOOR()
EXP(x)
e的x次方
0-1的随机数 圆周率
RAND()
PI()
LEAST(X1, X2 ...) GREATEST(X1, X2)
LN() 返回x的自然对数
LOG(x, y) 返回x的以y为底的对数
取模(余数) 四舍五入
返回集合中最小的值
返回集合中最大的值
MOD()
round()
SIGN(x)
SQRT(X)
TRUNCATE(X,Y) 返回数字x截短为y位小数的结果
聚合函数
AVG
COUNT
MIN
MAX
SUM
GROUP_CONCATE 返回集合中最小的值
日期和时间函数
curdate() current_date() 当前日期 curtime() current_time() 当前时间
1.DDL, DML, DCL
# DDL DML DCL
DDL 指数据库定义语言,用户定义和管理SQL数据库中所有对象的语言,是一种有助于创建数据库模式的SQL命令。
主要命令:create,alter,drop,truncate,rename等
DML 数据库操作语言,只能操作表或者表中的一列数据,是一种有助于检索和管理关系数据库中数据的SQL命令。
主要命令:select,update,insert,delete等
DCL 数据库控制功能,用来设置或者更改数据库用户角色权限等的语句
主要命令:grant(古乱特),deny(得奶),revoke(律vok),commit,savepoint(save破硬特),rollback等
# DCL一般只有sysadmin,dbcreator,db_ownew,db_securityadmin等人员有权限操作
2.事务机制
# 什么是事务?
事务是一系列的操作,只要他们符合ACID特性,我的理解是开启事务以后,事务里的操作要么全部成功,要么全部失败.没有执行一半的情况.
# 什么是ACID?
A:原子性,就是要么全部成功要么全部失败,不可能只执行一部分
C:一致性,系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态
I:隔离性,通常来讲,一个事务在完全提交之前,对其他事务是不可见的, 注意前面的通常来说加了红色,意味着有意外情况
D:持久性,一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果
# 事务隔离级别?
Mysql有四种隔离级别:
未提交读: 允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
(读取到了其他事务未提交的部分,而之后该事务进行了回滚)
这个级别的性能没有足够大的优势,但是又有很多问题,因此很少使用
提交读: 只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
可重复读: 可重复读,在同一个事务内的查询都是事务开始时刻一致的,innoDB默认级别,在SQL标准中,该隔离级别消除了
不可重复读,但是还存在幻读,但是innodb解决了幻读.
串行读: 这是最高的隔离级别,每次读都需要获得表级的共享锁,读写相互都会堵塞
(因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用.)
3.常用约束
常用约束有:主键约束,默认值,唯一约束,外键约束,非空
# 详细说说:
主键约束: 就是常见的ID,用于约束表中的一行,作为这一行的标识符,在一个表中通过外键就可以找到这一行,要求主键不能重复,不能为空
默认值: 插入数据为空的时候使用默认值.
唯一约束: 指定表中某个字段的值不能重复,即这个字段的每个值都是唯一的
外键约束: 该字段的值引用了另外表的字段,一个表可以有多个外键,但是每个外键必须参考另一个表的主键,被外键约束的列取值必须在它参考的列中有对应值
非空: 就是插入值的时候不能为空
4.连表查询操作
1.外连接分为
左外连接,无论左表在右表有没有匹配,都返回左表的数据,缺失的右表数据为null
右外连接,无论右表在左表有没有匹配,都返回右表的数据,缺失的左表数据为null
完全外连接,无论左右表有没有匹配,都返回两个表的数据,缺失的数据为Null
2.内连接 求两个表的交集,只要匹配到的数据
3.交叉连接 求两个表的笛卡尔积
# 不过跨表查询的思路有2个,一是连接,二是子查询,更多时候我喜欢用子查询.
# 那什么是子查询?
将SQL语句查询的结果括号括起来当做另外一条SQL语句的条件.也就是分步操作
语法:select 要查询的字段 from 表名 where id=(另外一个表的一条SQL语句);
5.乐观锁和悲观锁
# 悲观锁：
就是对数据的处理持悲观态度，总认为会发生并发冲突，比如在获取和修改的时候别人回来修改数据。所以在整个数据的处理过程中，需要将数据锁定，悲观锁的实现，一般用数据库提供的锁机制来实现。比如mysql的排他锁,select ... for update
来实现悲观锁,悲观锁在并发的控制上,是先上锁再处理数据的保守策略.虽然保证了数据的安全,但是缺降低了效率
# 乐观锁
就是对数据的处理持乐观态度.乐观的认为数据一般情况下不会发生冲突,只有提交数据去更新的时候,才会对数据是否冲突进行检测.如果发现冲突了.就返回错误信息给用户.让用户自己决定怎么操作.
乐观锁需要我们自己去实现,一般实现乐观锁的方式是记录数据的版本,或者加时间戳.
数据版本,为数据增加的一个版本标识.当读取数据时将版本标识的值取出来,数据每更新一次,同时对这个版本标识去进行更新,比如+1,当我们更新的数据的时候,判断数据库表对应的版本信息和第一次取出来的版本标识对比,如果一样就更新,如果不一样就认为是过期数据,返回错误信息给用户
# 适用场景?
乐观锁是基于程序实现的，所以不存在死锁的情况，适用于读多的应用场景。如果经常发生冲突，上层应用不断的让用户进行重新操作,这反而降低了性能，这种情况下悲观锁就比较适用
6.中间件:什么情况下需要分库分表?
mysql的中间件一般都是用来解决分库分表的.
# 什么情况下需要分库分表
1.单台服务器无法承载已有压力
2.数据库单表容量越来越大
3.大量的读写需求无法平衡
4.资源如果扩容,应用改动较大
5.资源的负载没法拆分,或者不容易拆分
了解的是一个Mycat中间件方案
https://blog.csdn.net/qq_22075913/article/details/119062209
MyCat 是一个彻底开源的，面向企业应用数据库中间件 , 支持事务， 可以视为MySQL集群的企业级数据库，
用来替代昂贵的Oracle集群, 在MyCat 中融合内存缓存技术、NoSQL技术、HDFS大数据的 新型SQL Server ,
并结合传统数据库和新型分布式数据仓库的新一代企业级数据库中间件产品
7.脏读,幻读,不可重复读,重复读
脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到是数据就是脏数据
幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE四个等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读
不可重复读:事务A多次读取同一条数据,事务B在事务A多次读取的过程中,对数据做了更新并提交,导致事务A多次读取同一数据时结果不一样,因为只能读到提交后的数据,原数据不可重复的读取.
重复读:原来的数据就算被其他的事务修改了,还是能读取到原来没有修改的数据
总结:不可重复读和幻读很容易混淆,不可重复读侧重于修改,幻读则侧重于新增或者删除.解决不可重复读的问题只需要锁住满足条件的行,解决幻读需要锁表
8.读写分离
# 什么是读写分离?
其实就是数据库分为了主从库,一个主库用于写数据,多个从库完成读数据的操作,主从库之间通过某种机制进行数据的同步,是一种常见的数据库架构.
# 一个组从同步集群，通常被称为是一个“分组”。
# mysql主从搭建总结
搭建mysql主从的目的是让一台mysql作为主数据库,一台或多台mysql作为从数据库,主数据库只负责数据的写入,从数据库只负责数据的查询(就是做一个读写分离),且主从数据库是实时同步的,这样就可以减轻单个数据库压力,从而提高项目的并发量。
一.mysql主从数据库搭建的前提
1.mysql版本必须一致
2.改变防火墙和selinux的策略，防止测试失败
二.修改主服务器配置文件, 文件位置/etc/my.cnf
三.修改从服务器配置文件，文件位置/etc/my.cnf
四．配置完成以后所有服务器重新启动
五．在主服务器上建立授权账号
六．登陆主服务器，查询master状态
七．配置从服务器
八．测试
测试主从数据库是否能同步数据
测试方法：
1． 在主服务器上面创建一个库test,如有该库，去其他名字的库也可以；
2． 在从库上检查有没有该库；
有该库说明主从数据能同步，达到我们的要求，实验成功。
9.聚簇索引,辅助索引
聚簇索引就是按照每张表的主键构建一个B+树,同时叶子节点存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页.这个特性决定了索引组织表中数据也是索引的一部分,每张表只能拥有一个聚簇索引.
"聚簇索引就是按照每张表的主键构建一个B+树,叶子节点存放表数据，每张表只能拥有一个聚簇索引"
Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。
聚簇索引的优缺点
优点：
数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的排序查找和范围查找速度非常快
缺点：
插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
# 非聚簇索引
辅助索引（非聚簇索引）
在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。
Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。
辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引
11.如何保证和redis双写一致性的问题?
# 场景:
双写一致性指的是当我们更新了数据库的数据之后redis中的数据也要同步去更新。使用redis读取数据的流程，当用户访问数据的时候，会先从缓存中读取数据，如果命中缓存的话，那么直接把缓存中的数据返回给用户，如果缓存中没有数据的话，先查询数据库把查询到的数据保存到缓存中，然后返回给用户。
# 保证双写一致性的策略
1、先更新缓存，再更新数据库
2、先更新数据库，再更新缓存
3、先删除缓存，再更新数据库
4、先更新数据库，再删除缓存
# 四种策略的优缺点
1、先更新缓存，再更新数据库
问题很明显如果更新缓存成功，更新数据库失败，就会造成缓存的脏数据
2、先更新数据库，再更新缓存
如果再高并发的情况下，可能会存在如下的情况，线程A更新了数据库，如果由于网络或者其他的原因，线程A还没来得及更新缓存，这时候有一个进程B更新了数据库，更新了缓存，这时候进程A才更新缓存，这时候就会导致线程B对缓存的更新丢失了，像事务丢失的情况
3、先删除缓存，再更新数据库
这种策略可能已经避免掉了，策略2中缓存丢失的情况，但是再高并发的情况下，也会有不一致的情况，比如线程A做写操作，首先删除缓存然后准备更新数据库，这时候，线程B执行了写操作，没有命中缓存，然后查询数据库，这时候读取的是旧值，并把查询到的旧值保存到缓存中，接着线程A完成了数据库的更新，这时候数据库和缓存又出现了不一致的情况，解决方案：我们只要再线程A，完成数据库的更新之后，稍作延迟再删除一次缓存，也叫作延迟双删。这里的延迟时间一定要大于业务的一次读操作的时间。
4、先更新数据库，再删除缓存
再高并发的情况下，也会有不一致的情况，比如线程A做读取数据的操作，正准备写入缓存的时候，线程B更新了数据库，然后执行了删除缓存的操作，这时候线程A才把旧值写入到缓存中，虽然这种情况出现的概率比较低，因为写操作的时候要大于一次读操作的时间的。解决方案：延迟双删，延时双删还是有问题的，如果删除缓存失败怎么办，当然是再次删除，不断的循环删除。删除失败后我们可以将要删除的key放入到队列中，然后尝试重复删除，直到删除成功。
12.B树和B+树
# B树特点:
1.所有键值分布在整棵树中
2.搜索有可能在非叶子节点结束,效率接近二分查找
3.每个节点最多拥有m(无穷)颗子树
4.根节点最少有两棵子树
5.分支节点至少有m/2颗子树(除根节点和叶子节点外都是分支节点)
# 缺点:
每个磁盘空间只有4k,如果数据data太大,那么导致树的深度加深,查询变慢
# B+树特点:
1.B+树每个节点包含更多的节点,原因1:降低树的高度,原因2:将数据的范围变为多个区间,区间越多数据检索越快
2.非叶子节点存储key,叶子节点存储key和数据
3.叶子节点俩俩指针相互连接(符合磁盘的预读特性),顺序查询时效率更高
# Innodb每次预读16k，4k的整数倍
# mysql为什么选择B+树？
B树在提⾼了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应⽤⽽⽣。
B+树只需要去遍历叶⼦节点就可以实现整棵树的遍历。⽽且在数据库中基于范围的查询是⾮常频繁的，⽽B树不⽀持这样的操作或者说效率太低
13.如何优化慢查询
让不走索引的sql走索引
联合索引，避免回表
特殊优化，深度分页
14.如何排查数据库慢的原因
排查网络原因
通过慢查询日志,找出相关语句,优化查询语句调整索引策略。
还可以将复杂存储过程、触发器、自定义函数交由应用代码实现;
15.mysql锁的机制
MyISAM和InnoDB存储引擎使⽤的锁：
MyISAM采⽤用表级锁(table-level locking)。
InnoDB⽀支持行级锁(row-level locking)和表级锁，默认为行级锁
⾏级锁表级锁和⻚级锁对⽐
# ⾏级锁:
⾏级锁是Mysql中锁定粒度最细的⼀种锁，表示只针对当前操作的⾏进行加锁。⾏级锁能⼤⼤减少数据库操
作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发⽣锁冲突的概率最低，并发度也最高。
# 表级锁
表级锁是MySQL中锁定粒度最⼤的⼀种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，
被⼤部分MySQL引擎⽀持。最常使⽤的MYISAM与INNODB都⽀持表级锁定。表级锁定分为表共享读锁（共享锁）
与表独占写锁（排他锁）。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最⾼，并发度最低。
# ⻚级锁
页级锁是MySQL中锁定粒度介于⾏级锁和表级锁中间的⼀种锁。表级锁速度快，但冲突多，行级冲突少，
但速度慢。所以取了折衷的⻚级锁，一次锁定相邻的⼀组记录。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
# 从锁的类别上分MySQL都有哪些锁呢？像上⾯面那样⼦进行锁定岂不不是有点阻碍并发效率了
从锁的类别上来讲，有共享锁和排他锁。
共享锁: ⼜叫做读锁。 当⽤户要进⾏数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
排他锁: ⼜叫做写锁。 当⽤户要进行行数据的写⼊时，对数据加上排他锁。排他锁只可以加⼀个，他和其他的排他
锁，共享锁都相斥。
⽤上⾯的例⼦来说就是用户的为有两种，⼀种是来看房，多个⽤用户⼀起看房是可以接受的。一种是真正的⼊入住一晚，在这期间，⽆论是想入住的还是想看房的都不可以。
锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁，⻚级锁，表级锁。
他们的加锁开销从⼤到小，并发能力也是⼤到小。
16.在普通索引中，什么情况下会引发表锁
常用的索引有三类:主键、唯一索引、普通索引。
主键:自带最高效的索引属性;
唯一索引:指的是该属性值重复率为0，一般可作为业务主键，例如学号;
普通索引:与前者不同的是，属性值的重复率大于0，不能作为唯一指定条件，例如学生姓名。"只是加速查询"
1.结论:
当 Where 查询条件中的字段没有索引时，更新操作会锁住全表! 可以看到，在有索引的情况下，更新不同的行，InnoDB 默认的行锁不会阻塞。
当“值重复率”低时，甚至接近主键或者唯一索引的效果，“普通索引”依然是行锁;当“值重复率”高时，MySQL 不会把这个“普通索引”当做索引，即造成了一个没有索引的 SQL，此时引发表锁。
所有explain查看sql执行计划
17.mysql约束命令
约束类型与关键字：
主键 PRIMARY KEY
默认值 DEFAULT
唯一 UNIQUE
外键约束 FOREIGN KEY
非空 NOT NULL
<1> 一对多关系 （常见）
例如： 班级和学生， 部门和员工， 客户和订单， 分类和商品
建表原则： 在从表（多方）创建一个字段， 字段作为外键指向主表（一方）的主键
<2>多对多关系 （常见）
例如：老师和学生， 学生和课程， 用户和角色
建表原则： 需要创建第三张表，中间表中至少有两个字段，这两个字段分别作为外键，指向各自一方的主键
<3> 一对一关系（了解）
在实际开发中应用不多，因为一对一可以创建成一张表
建表原则： 外键唯一， 主表的主键和从表的唯一外键，形成主外键关系， 外键唯一用UNIQUE修饰
