MySql总结

MySQL是什么

MySQL是数据库管理系统，能够帮助你管理关系型数据库，并且是开源免费的，如果必要，你可以修改源代码

数据库引擎

MySQL默认有多款数据库引擎,默认使用的是InnoDB.介绍一下InnoDB和MyISAM

innoDB：

1.支持事务

2.支持表锁、行锁，支持死锁检测

3.将表和索引在一个逻辑表空间中

4.支持外键

5.支持聚餐索引（将数据存入索引叶子页面上）

MyISAM：

1.较高的插入、查询速度、但不支持事务

2.仅支持表锁

3.不支持聚簇索引

4.把数文件和索引文件放在不同目录

5.不支持外键

索引

InnoDB和MyISAM索引数据结构均为B+树

在Innodb中，Mysql中的数据是按照主键的顺序来存放的，聚簇索引默认就是主键索引，如果表没有主键InnoDB会隐式定义一个主键来作为聚簇索引。

索引类型：

普通索引: 可插入重复值和空值

唯一索引: 列值必须唯一,但可有空值

组合索引: 列值的组合必须唯一

主键索引: 特殊的唯一索引,不允许空值

B+Tree：

为磁盘等外存储设备设计的一种平衡查找，是一种平衡的多路查找树，在B+Tree中，所有数据都是按照键值大小顺序存放在的叶子节点上，而非叶子节点上只存储key值信息

B+Tree特点：若根节点不是叶子节点，则至少有2个孩子 ，每个节点最多有4个孩子，所有叶子节点都在同一层，叶子节点持有下一个叶子节点的指针

聚簇索引查找：

非聚簇索引查找：

非聚簇索引仅包含索引的数据，先从非聚簇索引树开始查找，然后找到聚簇索引后。根据聚簇索引，在聚簇索引的B+树上，找到完整的数据

每增加一个索引就会创建一颗新的树，索引过多时表的插入、删除和修改操作会变慢

事务

并发问题：

脏读：A事务中对数据进行了更新，但事务还没有提交，B事务可以“看到”该事务没有提交的更新结果

不可重复读：一个事务在整个事务过程中对同一条数据读取，读取的结果不同

幻读：查询在整个事务过程中多次执行，查询所得的结果集不一样

ACID：

原子性(A)：要么都成功,要么都失败

一致性(C)：保证没有数据更新会执行到一半而导致和其他数据不统一

隔离性(I)：事务A和事务B之间具有隔离性

持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

隔离级别：

读未提交(read uncommitted)：事务A和事务B，事务A未提交的数据，事务B可以读取到

读已提交(read committed)：事务A和事务B，事务A提交的数据，事务B才能读取到

可重复读(repeatable read)（默认级别）：A,B事务在执行期间前后看到的数据是一致的

串行化(serializable)：事务A和事务B，事务A在操作数据库时，事务B只能排队等待

锁

乐观锁：

用数据版本记录机制实现，即为数据增加一个版本标识（version）

当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据

悲观锁：

在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对数据操作

行锁:

基于索引实现，仅锁定数据行，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁

表锁:

锁定全表

死锁：

两个或两个以上的线程在执行过程中，因资源互斥而造成的一种互相等待的现象（如：A等待B释放锁，B等待A释放锁），导致都将无法执行下去

存储过程与存储函数

一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，当用户通过指定存储程序的名字并给定参数（如果该存储程序带有参数）来调用才会执行

存储函数与存储过程有如下区别：

（1）存储函数的限制比较多，例如不能用临时表，只能用表变量；而存储过程的限制较少，存储过程的实现功能要复杂些，而函数的实现功能针对性比较强

　　（2）返回值不同。存储函数必须有返回值，且仅返回一个结果值；存储过程可以没有返回值，但是能返回结果集

　　（3）调用时的不同。存储函数嵌入在SQL中使用,可以在select 存储函数名(变量值)；存储过程通过call语句调用 call 存储过程名

视图

视图是一个逻辑表，是sql的查询结果，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据，在使用视图时动态生成

触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。

主从复制

将主数据库中的数据操作保存到二进制日志（Binary log），从库将日志复制到自己的中继日志（Relay log）中，然后将这些日志执行，从而使得从数据库的数据与主数据库保持一致

MySQL主从复制工作原理：

1.在主库上把数据更高记录到二进制日志

2.从库将主库的日志复制到自己的中继日志

3.从库读取中继日志的事件，将其重放到从库数据中

MySQL主从复制解决的问题

1.数据分布：随意开始或停止复制，并在不同地理位置分布数据备份

2.负载均衡：降低单个服务器的压力

3.高可用和故障切换：帮助应用程序避免单点失败

4.升级测试：可以用更高版本的MySQL作为从库

左连接、右连接和内连接

left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。

right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。

inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。

连接查询条件判断用ON，如: select a.name,b.job from A a inner join B b on a.id = b.A_id

优化

1.使用合理的数据类型，避免表的默认值为null

2.合理创建索引

3.开启数据库缓存，提升缓存命中率

4.表分区

5.开启慢查询日志，优化sql