MySql数据库及调优

2020-04-10 浏览次数:

1.数据库的三范式

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型
构成，包括整型、实数、字符型、逻辑型、日期型等。
第二范式（2NF）：满足第一范式，另外包含两部分内容，一是表必须有一个主键(多个关键字)；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
第三范式（3NF）：在1NF基础上，任何非主属性不依赖于其它非主属性[在2NF基础上消除传递依赖]，非主键属性只依赖于主键。首先要明确的是：满足着第三范式，那么就一定满足第二范式、满足着第二范式就一定满足第一范式
第一范式：字段是最小的的单元不可再分
第二范式：满足第一范式,表中的字段必须完全依赖于全部主键而非部分主键，也有可能，其它键有相互依赖。
第三范式：满足第二范式，非主键外的所有字段必须互不依赖，就是数据只在一个地方存储，不重复出现在多张表中，可以认为就是消除传递依赖

2.ACID
a.事务：事务是指逻辑上的一组操作，组成事务操作的各个单元必须全部成功。
b.事务的特性(ACID)

原子性：代表事务不可分割

一致性：代表事务执行前后，数据的完整性保持一致

隔离性：代表一个事务在执行的过程中，不应该受到其他事务的干扰

持久性：代表事务执行完成后，数据就持久化到数据库中

c.不考虑隔离性可能引发的问题

读问题

脏读：一个事物读取到了另一个事务未提交的数据

幻读：一个事务读到另一个事务已经提交的Insert数据，导致在前一个事务多次查询结果不一致

不可重复读：一个事务读到另一个事务已提交的update数据，导致在前一个事务多次查询结果不一致
写问题

引发两类丢失更新

d.设置隔离级别

Read uncommitted：不可提交读，是可能读取到其他会话中未提交事务修改的数据，以上读问题都会发生

Read committed: 可提交读，只能读取到已经提交的数据。解决脏读，但是幻读和不可重复读可能发生(Orcale默认)

Repeatable read: 重复读，在同一个事务内的查询都是事务开始时刻一致的，事务开始时查到的值，不会应为在事务执行过程中，有修改，就会读到和第一次不一样的值，解决脏读和不可重复读，幻读有可能发生(MySql默认)

Serializable：序列化，每次读都需要获得表级共享锁，读写相互都会阻塞，解决所有读的问题

3.mysql 的内连接、左连接、右连接有什么区别？

a.内连接只显示两表中有关联的数据，交集
select from table1 inner join table2 on table1.字段1 = table2.字段2;
b.左连接显示左表所有数据，右表没有对应的数据用NULL补齐，多了的数据删除
select from table1 left join table2 on table1.字段1 = table2.字段2;
c.右连接显示右表所有数据，左表没有对应的数据用NULL对齐，多了的数据删除
select * from table1 right join table2 on table1.字段1 = table2.字段2;

4.Mysql索引
索引是一种快速查询表中内容的机制，类似于新华字典的目录，是一种数据结构
a.分类

1.普通索引：普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。
2.唯一索引：普通索引允许被索引的数据列包含重复的值，唯一索引不允许两行具有相同的索引值，表中不能有重复数据。
3.主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。
4.复合索引：多个列组成的索引，需要符合最左匹配的原则(从组合索引的最左端开始匹配，所以要将含有范围的放到最右边，防止不检测后面的索引)。
5.聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个，值可以重复
6.非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个，值可以重复
7.空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。空间索引只能在存储引擎为MYISAM的表中创建。

b.索引的优缺点

数据库索引的优点：
（1）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
（2）可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
（3）可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
（4）在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
（5）通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
数据库索引的缺点：
（1）创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
（2）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
（3）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

c.索引创建的时机

什么时候要创建索引
（1）表经常进行 SELECT 操作
（2）表很大(记录超多)，记录内容分布范围很广
（3）列名经常在 WHERE 子句或连接条件中出现
什么时候不要创建索引
（1）表经常进行 INSERT/UPDATE/DELETE 操作
（2）表很小(记录超少)
（3）列名不经常作为连接条件或出现在 WHERE 子句中

5.MySql引擎
mysql索引采用的是B+tree，InnoDB的数据文件本身就是索引文件(聚集索引)，MyISAM索引文件和数据文件是分离的(非聚集索引)，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶结点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
Innodb和MyIASM

a.Innodb引擎，Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它设计的目标就是处理大数据容量的数据库系统。它本身实际上是基于Mysql后台的完整的系统。Mysql运行的时候，Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是，该引擎是不支持全文搜索的。同时，启动也比较的慢，它是不会保存表的行数的。当进行Select count(*) from table指令的时候，需要进行扫描全表。所以当需要使用数据库的事务时，该引擎就是首选。由于锁的粒度小，写操作是不会锁定全表的。所以在并发度较高的场景下使用会提升效率的。

大容量的数据集时趋向于选择Innodb。因为它支持事务处理和故障的恢复。Innodb可以利用数据日志来进行数据的恢复。主键的查询在Innodb也是比较快的

b.MyIASM引擎，它是MySql的默认引擎，但不提供事务的支持，也不支持行级锁和外键。因此当执行Insert插入和Update更新语句时，即执行写操作的时候需要锁整个表。所以会导致效率会降低。不过和Innodb不同的是，MyIASM引擎是保存了表的行数，于是当进行Select count(*) from table语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的。可以将MyIASM作为数据库引擎的首选。
大批量的插入语句时（这里是INSERT语句）在MyIASM引擎中执行的比较的快，但是UPDATE语句在Innodb下执行的会比较的快，尤其是在并发量大的时候。

6.MySQL索引的数据结构
MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。
Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

7.平衡二叉树
平衡二叉树是基于二分法的策略提高数据的查找速度的二叉树的数据结构
a.特点

（1）非叶子节点最多拥有两个子节点；
（2）非叶子节值大于左边子节点、小于右边子节点；
（3）树的左右两边的层级数相差不会大于1;
（4）没有值相等重复的节点;

8.B-Tree
B树即平衡多路查找树
a.特点

（1）排序方式：所有节点关键字是按递增次序排列，并遵循左小右大原则；
（2）子节点数：非叶节点的子节点数>1，且<=M ，且M>=2，空树除外（注：M阶代表一个树节点最多有多少个查找路径，M=M路,当M=2则是2叉树,M=3则是3叉）；
（3）关键字数：枝节点的关键字数量大于等于ceil(m/2)-1个且小于等于M-1个（注：ceil()是个朝正无穷方向取整的函数如ceil(1.1)结果为2);
（4）所有叶子节点均在同一层、叶子节点除了包含了关键字和关键字记录的指针外也有指向其子节点的指针只不过其指针地址都为null对应下图最后一层节点的空格子;
B树相对于平衡二叉树的不同是，每个节点包含的关键字增多了，特别是在B树应用到数据库中的时候，数据库充分利用了磁盘块的原理（磁盘数据存储是采用块的形式存储的，每个块的大小为4K，每次IO进行数据读取时，同一个磁盘块的数据可以一次性读取出来）把节点大小限制和充分使用在磁盘快大小范围；把树的节点关键字增多后树的层级比原来的二叉树少了，减少数据查找的次数和复杂度

9.B+Tree
B树和B+树的区别：

B树一个节点中的叶子数为其关键字数+1，B+树一个子节点中的关键字数等于其叶子节点数。
B+树叶子节点间有横向指针，方便顺序搜索，B树就没有。

数据库为什么采用B+树作为底层来存储数据
B+树适合作为数据库的基础结构，完全是因为计算机的内存-机械硬盘两层存储结构。内存可以完成快速的随机访问（随机访问即给出任意一个地址，要求返回这个地址存储的数据）但是容量较小。而硬盘的随机访问要经过机械动作（1磁头移动 2盘片转动），访问效率比内存低几个数量级，但是硬盘容量较大。典型的数据库容量大大超过可用内存大小，这就决定了在B+树中检索一条数据很可能要借助几次磁盘IO操作来完成。要尽可能的减少磁盘的IO操作来提高效率。B+树可以存储很大量的数据，而且遍历的速度很快，对于一个容量为22.1G的表，也只需要三层的B+树就可以存储，这一般就可以应对很多应用了。三层B+树最多也只需要三次IO的磁盘操作就可以找到数据，所以说速度还是很快的。对于四层则可以存放25.9T的数据，也只需要四次磁盘IO就可以找到数据。

聚集索引与非聚集索引：
对于聚集索引存储来说，行数据和主键B+树存储在一起，辅助键B+树只存储辅助键和主键，主键和非主键B+树几乎是两种类型的树。对于非聚集索引存储来说，主键B+树在叶子节点存储指向真正数据行的指针，而非主键。

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id = 14″这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树中再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。(用id和name都只访问一次就可以了)

聚集索引的优势：

由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
辅助索引使用主键作为”指针” 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个”指针”。也就是说行的位置（实现中通过16K的Page来定位，后面会涉及）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。

9.mysql 的行锁和表锁
下面的都基于innodb引擎下。
表级锁：每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高.
一般来说，如果where条件中只用到索引项，则加的是行锁；否则加的是表锁。比如说主键索引，唯一索引和聚簇索引等。如果sql的where是全表扫描的，想加行锁也爱莫能助。
共享锁（S）：SELECT FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X）：SELECT FROM table_name WHERE … FOR UPDATE
mysql 还提供了LOCK TABLES，UNLOCK TABLES，用于加表锁

10.mysql性能优化
数据库结构优化
1）范式优化：比如消除冗余（节省空间。。）
2）反范式优化：比如适当加冗余等（减少join）
3）拆分表：垂直拆分和水平拆分
垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表，通常我们按以下原则进行垂直拆分:

把不常用的字段单独放在一张表;
把text，blob等大字段拆分出来放在附表中;
经常组合查询的列放在一张表中;

垂直拆分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用jion关联起来即可;

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。
1.拆分原则
通常情况下，我们使用取模的方式来进行表的拆分;比如一张有400W的用户表users，为提高其查询效率我们把其分成4张表users1，users2，users3，users4，通过用ID取模的方法把数据分散到四张表内Id%4+1 = [1,2,3,4]，然后查询,更新,删除也是通过取模的方法来查询
在insert时还需要一张临时表uid_temp来提供自增的ID,该表的唯一用处就是提供自增的ID;得到自增的ID后,又通过取模法进行分表插入; 注意,进行水平拆分后的表,字段的列和类型和原表应该是相同的,但是要记得去掉auto_increment自增长

SQL语句的优化

①选择最有效率的表名顺序
数据库的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表将被最先处理
在FROM子句中包含多个表的情况下：
如果三个表是完全无关系的话，将记录和列名最少的表，写在最后，然后依次类推
也就是说：选择记录条数最少的表放在最后
如果有3个以上的表连接查询：
如果三个表是有关系的话，将引用最多的表，放在最后，然后依次类推。
也就是说：被其他表所引用的表放在最后
例如：查询员工的编号，姓名，工资，工资等级，部门名emp表被引用得最多，记录数也是最多，因此放在form字句的最后面
select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname
from salgrade,dept,emp where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)

②WHERE子句中的连接顺序
数据库采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。
emp.sal可以过滤多条记录，写在WHERE字句的最右边
select emp.empno,emp.ename,emp.sal,dept.dname from dept,emp where (emp.deptno = dept.deptno) and (emp.sal > 1500)

③SELECT子句中避免使用号
我们当时学习的时候，“”号是可以获取表中全部的字段数据的。但是它要通过查询数据字典完成的，这意味着将耗费更多的时间使用*号写出来的SQL语句也不够直观。

④多使用内部函数提高SQL效率
例如使用mysql的concat()函数会比使用||来进行拼接快，因为concat()函数已经被mysql优化过了。

⑤使用表或列的别名
如果表或列的名称太长了，使用一些简短的别名也能稍微提高一些SQL的性能。毕竟要扫描的字符长度就变少了

⑥善用索引
索引就是为了提高我们的查询数据的，当表的记录量非常大的时候，我们就可以使用索引了。

⑦避免在索引列上使用计算
WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描，这样会变得变慢

⑧用 >= 替代 >
低效：
SELECT FROM EMP WHERE DEPTNO > 3
首先定位到DEPTNO=3的记录并且扫描到第一个DEPT大于3的记录
高效：
SELECT FROM EMP WHERE DEPTNO >= 4
直接跳到第一个DEPT等于4的记录

⑨索引的最左前缀匹配原则，非常重要的原则，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的顺序可以任意调整。所以建立索引顺序的时候要注意将范围放到最后面，而在优化sql语句的时候，范围最好写在最右边，利用SQL的执行，提高效率。

11.Mysql中的聚合函数
1：max()求某字段中最大值：求t_book表中按书籍类型中每种书的最大数量:
select bookName, max(bookNum) from t_book group by bookType;
2：sum()求和函数：求t_book中按书籍类型中每种类型书的总和：
select sum(bookNum) booktype from t_book group by bookType;
3：min()求某字段中最小值：求t_book表中按书籍类型中每种书的最小数量:
select bookName, min(bookNum) from t_book group by bookType;
4：avg()求平均值：求t_book表中每种类型书价格的最后平均值:
select avg(bookprice) ,booktype from t_book group by bookType;
5：count()用来统计数据库中记录的条数: 求t_book表中每种类型书中有多少条记录:
select count(bookName), booktype from t_book group by bookType