mysql数据库总结

1、为什么要使用数据库？

实际项目离不开数据；数据放在内存中，存取速度快，但是无法得到永久保存；数据放在文件中，能给永久保存，但是存取速度慢；
所以需要一个专门存放数据的仓库，也就是数据库；将数据保存到数据库中，既能永久保存数据，也能使用sql语句快速的读取数据；总之，数据库是为了能够永久的保存数据，高效的存取数据，更加方便的管理数据；
sql：结构化查询语言，操作数据库的语言；
mysql：关系型数据库的一个产品；

2、mysql数据库的基本架构

MySQL主要分为Server层和存储引擎层；
（1）连接器：用户的身份认证（账号密码）和权限管理（读写数据库），用户连接数据库后只要不断开，权限被修改也不受影响；
（2）查询缓存：将查询条件和结果以key-value的形式放在缓存中，用户查询数据如果能在key中匹配，直接返回给用户结果，否则交给分析器处理，得到的结果返回给用户并放在缓存中，方便用户下次查询；但是表更新后，缓存就得清空，所以经常发生数据变化的表不建议使用查询缓存；
（3）分析器：主要包括词法分析和语法分析；词法分析就是提取出关键字，比如查询的表，字段名，查询条件等；语法分析就是判断SQL语句是否符合MySQL的语法；
（4）优化器：优化器会选择它认为的最优的查询方案执行查询，比如使用如何选择索引，多表关联时如何选择关联顺序；
（5）执行器：执行SQL语句，校验是否具有执行这条SQL语句的权限；
存储引擎：数据的存储和读取；

3、数据库的三大范式

范式就是一种标准规范，三大范式对于三种约束，约束条件不断严格；
（1）第一范式：每一列都是原子项，不能被拆分

上图中的系，包括系名和系主任，可以被拆分，因此不符合第一范式；
如果将上图中的系拆分，这个表就符合第一范式的要求，但是存在很多问题：
首先是数据冗余，每个系只有一个系主任，但是在表中的每个系都要写一遍系主任，比如经济系，只要表明经济系的主任是张三丰即可，没必要出现一次经济系，就得在后面写一次张三丰，也就是数据冗余较多；
其次，在表中增删数据会造成很多错误，如果新开设了一个系，没有招收学生，在表中只写系的信息，就可能会导致数据不合法，因为这是一个关于学生成绩的表格，只出现系的信息会很突兀，但是不在这张表中填写又没有别的表可以写；如果张无忌毕业，要求将他的信息删除，但是删除后发现经济系的信息都没有了，显然这是不合理的；
综上，需要另一张表保存学生的信息，所有有了第二范式的要求；
（2）第二范式：在第一范式的基础上，非主属性要完全依赖于码；
如果通过A属性（A属性组）的值可以唯一确定B属性的值，那么B依赖于A，写作A–>B；
比如姓名依赖于学号；分数依赖于属性组（学号，课程）；
完全函数依赖：
B必须依赖属性组中的每一个属性；分数依赖于属性组（学号，课程），不也就是必须被所有的属性共同确定；
部分函数依赖：
属性组中的部分属性的值就可以确定B，比如姓名部分依赖于属性组（学号，课程）；
传递函数依赖：
A确定B，B确定C，C传递函数依赖于A；
比如学号确定姓名，姓名确定系主任，系主任传递函数依赖于学号；
码：
一个表中的属性（属性组）被表中所有的属性完全依赖，这个属性（属性组）就是码；
比如（学号，课程名称）是这张表的码；
码中的属性是主属性，其他属性是非主属性；
了解了以上概念，再分析上面的表格，学号和课程名称是这张表的码，这两个属性是主属性，其他属性是非主属性，此时，姓名这个非主属性只需要码中的学号就可以确定，也就是对码部分依赖，所以这不符合第二范式；

将表一分为二，选课表和学生表，此时每个表中不再有非主属性对码的部分依赖，符合第二范式；
此时还存在着数据冗余，数据增删造成错误的问题；因此需要第三范式的规范；
（3）第三范式：第二范式的基础上，消除传递依赖，任何非主属性不能依赖于其他非主属性；
上面学生表中的学号是主属性，但是系名和系主任之间存在着传递依赖；因此需要再创建一个系的信息的表；

此时任何非主属性不再依赖于其他非主属性；

4、mysql数据类型

int ：整数类型
float：浮点型，4字节
double：含字节数为8，64bit数值范围
decimal:16字节，128bit，不存在精度损失，常用于银行帐目计算
decimal(a,b)
a指定指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38；
b指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0
char：char(10)，长度固定，存储3个字节的数据也占10个字节；高效但占用空间，适合存储长度固定的数据，比如md5加密的密码；
varchar：varchar(10)，最多存放10个字节，长度不固定，数据长度加上记录数据长度的字节；占用空间少；
date 日期类型（只包含年月日yyyy-MM-dd）
datetime 日期 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
timestamp 时间戳类型 不给初始值，默认使用系统时间

5、索引

索引相当于书的目录，有了索引之后，能够提高查询数据的速度，但是创建和维护索引需要消耗时间，索引本身也会占用空间，索引是数据之外的额外消耗，所以频繁使用的数据字段可以建立索引，经常改变的表和不经常使用的字段不适合建索引；
mysql使用的是InnoDB存储引擎，该引擎使用B+树作为索引的结构；
B+树：
（1） B+ 树是基于 B 树和叶子节点顺序访问指针实现，它具有 B 树的平衡性，并且通过顺序访问指针来提高区间查询的性能；
（2）在 B+ 树中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是 key i 和 key i+1，且不为 null，则该指针指向节点的所有 key 大于等于 key i 且小于等于 key i+1；
（3）进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找；直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data；
（4） 插入、删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性；
为什么使用B+树而不使用哈希算法，红黑树，B树？
（1）哈希算法查找速度快，但是计算hashcode不支持范围查找，因为hashcode原本就不是按数字的顺序排列的；而且遇到hashcode相同的索引，还需要扫描表进行查找；
（2）平衡二叉树：左右子树高度差不能超过1，且左子树小于根，右子树大于根；

平衡二叉树的优点就是高度有一定限制，因为左右子树高度差不能为1；但如果用平衡二叉树作为索引的结构，再范围查询时可能需要回旋查找，比如图中找到大于5的数据，8的左子树和右子树都需要查找；
（3）B树
B树相对于平衡二叉树高度更低，因为结点可以有不止两个孩子；

但是使用B树作为索引的结构也会遇到回旋查找的问题，原因就是非叶子结点也存储了需要的数据；
（4）B+树

在B树的基础上，B+树的非叶子结点不存储实际的数据，只存储找到实际数据的索引信息；B+树的叶子是双向链表，因此很适合范围查找；
综上，为什么使用B+树而不使用B树？
（1）B树的分支节点也存储实际的数据，占用内存空间，指针能够使用的内存空间减少，结点的增加很可能导致树的层数的增加；而B+树的分支节点只存储索引信息，占用内存少，完全可以加载到内存中，更加快速的使用；而且分支节点能够给指针使用的空间大，可以减少树的层数，更加快速的读取数据；
（2）B+树存储数据的叶子结点是双向链表，很适合进行范围查询；而B树进行范围查询时只能使用中序遍历，可能会导致回旋查找；
为什么官方建议使用自增长主键作为索引？
增加数据时B+树会根据索引的值进行调整，自增主键是连续的，在插入过程中能够减小调整的幅度；并且能减少数据的移动，每次插入都是插入到最后；总之就是减小树调整的幅度和移动的频率。
聚簇索引
聚簇索引是按照每张表的主键构建一颗B+树，叶子结点中存放表中的所有信息；每张表只有一个聚集索引；


InnoDB默认使用主键作为索引，没有主键，就使用非空唯一的字段；否则隐式定义一个主键作为索引；
优点：速度快，索引和数据在一个树中；特别是范围查找；
缺点：插入顺序影响插入速度，如果不使用自增主键会严重影响性能；

辅助索引

辅助索引：辅助索引总是需要二次查找，叶子结点中存放的是主键（聚簇索引）的值，根据辅助索引找到主键值，根据主键值查找数据；
比如select * from user where age = 30;
id是聚簇索引，age是辅助索引；
如果使用这个辅助索引执行上面的sql语句，需要回表查询，扫描两次B+树，效率较低；

覆盖索引
覆盖索引指的是只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快；
例如：select id,age from user where age = 10;
查询的数据在索引中就能得到，不需要查询数据行；
（1）索引通常远小于数据行的大小，只读取索引能大大减少数据访问量；
（2）一些存储引擎（例如：MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存；因此，只访问索引可以不使用系统调用（通常比较费时）；
（3）对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引；
实现索引覆盖：将需要查询的字段建立联合索引，比如age,name；
适合使用覆盖索引的场景：
（1）全表的count查询，如果查询count（name），建立一个name的索引，就实现了覆盖索引，只在这个索引树上就能完成查询；
（2）列查询回表优化，查询某几列（上面的例子），将这些列做一个联合索引，就实现了覆盖索引；
最左匹配原则
使用索引 (name, age, sex) 的时候，B+ 树是按照从左到右的顺序来建立搜索树的；
比如：(小明, 22, 男)，先比较 name，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据。但当 (22, 男) 这样没有 name 的数据来的时候，B+ 树就不知道第一步该查哪个节点；
当 (小明, 男) 这样的数据来检索时，B+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于小明的数据都找到，然后再匹配性别是男的数据；
最左前缀匹配原则会一直向右匹配直到遇到范围查询（>、<、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 的顺序可以任意调整；
怎么知道创建的索引有没有被使用到？或者说怎么才可以知道这条语句运行很慢的原因?
使用 Explain 命令来查看语句的执行计划，MySQL 在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划；可以通过其中和索引有关的信息来分析是否命中了索引，例如：possilbe_key、key、key_len 等字段，分别说明了此语句可能会使用的索引、实际使用的索引以及使用的索引长度；

索引失效
（1）索引列参与表达式运算、函数运算：where age + 10 > 30；
（2）索引列模糊查询：name like ‘%大%’；
（3）使用or，即使部分条件有索引也不走索引；
（4）优化器认为全表扫描比使用索引效率高（比如数据量很少时）；
查询性能的优化
减少请求的数据量
（1）只返回必要的列：最好不要使用 SELECT * 语句；
（2） 只返回必要的行：使用 LIMIT 语句来限制返回的数据；
（3）缓存重复查询的数据：缓存经常被查询的数据；
减少服务器端扫描的行数
（1） 最有效的方式是使用索引来覆盖查询，因为索引在内存中，数据很多都存在服务器中；

6、InnoDB 和 MyISAM 的比较？

（1）事务：MyISAM不支持事务，InnoDB支持事务；
（2）关于 count()：MyISAM会直接存储总行数，InnoDB 则不会，需要按行扫描；对于 select count() from table; 如果数据量大，MyISAM 会瞬间返回，而 InnoDB 则会一行行扫描，所以对于全表count可以建立覆盖索引；
（3）外键：MyISAM 不支持外键，InnoDB 支持外键；
（4）MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。
表级锁：开销小，加锁快，不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发量最低；
行级锁：开销大，加锁慢，会出现死锁；锁力度小，发生锁冲突的概率小，并发度最高；

7、谈谈你对水平切分和垂直切分的理解？

（1）水平切分是将同一个表中的记录拆分到多个结构相同的表中；当一个表的数据不断增多时，水平切分是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力；
（2）垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。例如：将原来的电商数据库垂直切分成商品数据库、用户数据库等；

8、主从复制/同步

指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。
一句话表示就是，主数据库做什么，从数据库就跟着做什么；
为什么要主从复制？
负载均衡；备份数据库，提高数据库系统的可用性；
主从复制主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。
binlog 线程 ：将主服务器上的数据更改写入二进制日志（Binary log）中；
I/O 线程 ：从主服务器上读取二进制日志，并写入从服务器的重放日志（Relay log）中；
SQL 线程 ：读取重放日志并重放其中的 SQL 语句；
主从复制原理：
（1）主数据库只要发生变化，立马记录到Binary log 日志文件中
（2）从数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志
（3）从数据库I/O获取到的二进制日志，保存到自己的Relay log 日志文件中
（4）从数据库有一个 SQL thread定时检查Realy log是否变化，变化那么就更新数据
主从同步的延迟的原因：来不及读取主服务器中的SQL
假如一个服务器开放 Ｎ 个连接给客户端，这样有会有大并发的更新操作, 但是从服务器的里面读取 binlog 的线程仅有一个， 当某个 SQL 在从服务器上执行的时间稍长或者由于某个 SQL 要进行锁表就会导致主服务器的 SQL 大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。
措施：增多从服务器的数量；
谈谈你对数据库读写分离的理解？
读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。
读写分离能提高性能的原因在于：
主从服务器负责各自的读和写，极大程度缓解了锁的争用；

9、事务

如果一个包含多个步骤的业务操作被事务管理，那么这些操作要么同时成功，要么同时失败；
事务的操作：
开启事务：start transaction；
回滚：rollback；
提交：commit；
mysql执行一次增删改操作默认提交一次到数据库；
如果开启事务，不再默认提交，必须手动提交事务；
事务的四大特性：
原子性：不可分割，事务中的步骤要么同时成功，要么同时失败；
持久性：事务提交或回滚，数据库都会持久化的更新；
隔离性：多个事务之间相互独立；
一致性：事务操作前后总量不变，一个减少了多少，另一个就增加了多少；
事务隔离级别：
多个事务之间相互独立，但是如果多个事务操作同一批数据，就会引发一些问题，设置不同的隔离级别可以解决这些问题；
脏读：一个事务读取到另一个事务没有提交的数据；
不可重复读（虚读）：在同一个事务中两次读取到的事务不一样；
幻读：一个事务增删改数据表中的所有记录，另一个事务添加了一条数据，第一个事务查询不到自己的修改；
（1）read uncommitted 读未提交
产生脏读 不可重复读 幻读
（2）read committed 读已提交
产生不可重读读 幻读
（3）repeatable read 可重复读
产生幻读
（4）serializable 串行化
不会产生任何问题
隔离级别从小到大，效率越来越低

可重复读取(Repeatable Read)：禁止不可重复读取和脏读取，但是有时可能出现幻读数据。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务;
B读取数据的时候不允许其他人修改，其他人可以读；
B修改数据的时候别人不能修改也不能读；

10、谈谈你对 MVCC 的了解？

数据库并发场景：

1. 读-读：不存在任何问题，也不需要并发控制；
2. 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读；
3. 写-写：有线程安全问题，可能会存在更新丢失问题。
   多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联；
   读操作只读该事务开始前的数据库的快照；
   MVCC 可以为数据库解决以下问题：
4. 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能；
5. 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题。