简介

AUTOBEAR的关系型数据库的个人学习笔记，本笔记随时增添，用来巩固知识，查缺补漏。

基础概念

1.DB: DATABASE数据库，数据仓库
2.DBMS：数据库管理系统（MySQL、Oracle等）
3.SQL:结构化查询语言（通用性高）
4.DBA:数据库管理员
数据库的特点:数据-->表-->数据库

数据库引擎

1.InnoDB数据引擎（聚集索引）
2.MylSAM(非聚集索引)
所谓聚集就是索引字段和数据是否用地址代替。

基础语法知识

DISTINCT去重复数据的查询：SELECT DISTINCT col1,col2 FROM table;
删除去重：DELECT t1 FROM table t1,table t2 WHERE t1.email=t2.email and t1.id>2.id;
+号的作用仅仅作为运算符：Ⅰ 'abc'+20=20; Ⅱ null+20=null; Ⅲ abc'+'efg'=0;
查询拼接：SELECT CONCAT('a','b','c')=abc;
平均值：SELECT AVG(salary),department_id,job_id FROM employees
分组筛选：GROUP BY job_id,department_id
排序： ORDER BY AVG(salary) DESC;
右外连接查询哪个部门没员工：

SELECT d.*,e.*
FROM departments d
LEFT OUTER JOIN employees e
ON d.'department_id'=e.'department_id'
WHERE e.'employee_id' IS NULL

菜鸟教程的几种连接图：

sql-join.png

约束

约束（一种限制，用于限制表中数据），六大约束：
1.NOT NULL;
2.DEFAULT;
3.PRIMARY KEY;
4.UNIQUE(唯一约束，可以为空，比如座位号)；
5.CHECK(检查约束)；
6.FOREIGN KEY(外键约束)
创建语法：

CREATE TABLE 表明(
  字段名 字段类型 约束
)
CREATE TABLE 表明(
  字段名 字段类型 
  
  CONSTRAINT 约束名 PRIMARY KEY(id)
)

CREATE TABLE 表名(
    id INT PRIMARY KEY ,
    name VARCHAR(20) NOT NULL,
    gender CHAR(1) CHECK(gender='男' or gender='女'),
    seat INT UNIQUE,
    age INT DEFAULT 18,
    majorId INT FOREIGN KEY REFERENCES major(id)
)

查看表结构、修改索引语句：

DESC 表名
SHOW INDEX FROM 表名
ALTER TABLE student ADD CONSTRAINT uk_name unique(name);

外键：主从表关联列类型要求一致或者兼容、主表的关联列必须是一个Key(主键或者唯一键)、插入数据时先插主再插从，先删从再删主。

标识列

标识列又叫自增长列，一个表至多一个标识列必须在创建时就定义，相关语句：

CREATE TABLE table_ii(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
)
SHOW VARIABLES LIKE '%auto_increment%';
SET auto_increment_increment(步长)=3;
SET auto_increment_offset(起始值)=20

事务

transaction control language事务控制语言（需要支持事务的引擎配合），是一个或一组sql语句组成一个执行单元。
事务的（ACID）属性：
1.原子性
2.一致性
3.隔离性（隔离级别）
4.持久性（一旦提交就是永久性）
隐式事务：没有明显的开启和结束的标志，普通的增删改查就是这种隐式事务。
显式事务：1.禁用自动提交（SET autocommit=0）只对当前会话有效。2.start transaction（可以省略）3.编写sql语句。4.commit/rollback。

事务隔离机制

数据库使用中常出现的问题现象：
1脏读（T1/T2两个事务，T1读取了T2更新但还没被提交的字段，若T2回滚，T1读取的内容就是临时无效的）
2不可重复读（T2读取后T1修改并提交，T2再次读取，两次数据冲突）
3幻读（针对插入、删除数据行，操作前后进行范围查找得出的结果不同）
为了解决以上问题MYSQL提供4种事务隔离级别:
1.READ UNCOMMITTED 读取未提交，会出现所有问题。
2.READ COMMITTED 读取已提交，只解决脏读。
3.REPEATABLE READ 可重复读，默认。解决脏读和不可重复读问题。
4.SERIALIZABLE 串行化(会造成严重的事务阻塞)，解决全部问题。
Oracle有两种事务隔离级别(READ COMMITTED /SERIALIZABLE)

事务语法

相关设置语句：

SELECT @@tx_isolation; 或者@@transaction_isolation;
set seesion transaction isolation level 隔离级别;

事务种设置回滚点：

SAVEPOINT A;
ROLLBACK TO A;
TRUNCATE不支持回滚DROP和DELETE都支持

视图

视图是一种虚拟表。

CREAT VIEW v1 AS
SELECT name,major FROM studenttable s INNER JOIN major m ON s.majorid=m.id 
--之后就可以把V1当做表来操作。
ALTER VIEW v1 AS ....
DORP VIEW v1;
SHOW v1;

一般不直接对视图内的数据进行增删改操作。

变量

分为全局变量、会话变量、自定义变量、局部变量。
会话变量：只对当前会话session有效。
查看变量值：SHOW (GLOBAL/SEESION) VARIABLES;
自定义变量：SET @自定义变量名:=值也可以直接用=号
局部变量：只在BEGIN/END内有效。DECLARE 变量名 类型 DEFAULT 值； SET 局部变量名:=值；

存储过程

存储过程是一种预先编译好的SQL语句的集合，批处理语句，减少了和数据库服务器连接的次数。
一些语法：

DELIMITER $;设置结束标记。

CREAT PROCEDURE function1()
BEGIN
     INSERT INTO admintable(username,password) VALUES('mike','123456');
END $
CALL function1()$

CREAT PROCEDURE function2(IN Myname VARCHAR(20))
BEGIN
     SELECT * FROM table t WHERE t.name=Myname
END $
CALL function2('张飞')$

CREAT PROCEDURE function3(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
     DECLARE result INT DEFAULT 0;
     SELECT COUNT(*) INTO result FROM table t WHERE t.username=username AND t.password=password;
     SELECT IF(result>0,'成功','失败');
END $
CALL function3('admin','123456')$

CREAT PROCEDURE function4(IN username VARCHAR(20),OUT password VARCHAR(20))
BEGIN
    SELECT t.password INTO password FROM table t WHERE t.username=username;

END $
SET @mypassword
CALL function4('admin',@mypassword)$
SELECT @mypassword$
DROP PROCEDURE function1;

函数：

存储过程类似，但是函数有且仅有一个返回，适合查值。存储过程主要增删改。
语法块：

CREAT FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    DECLARE c INT DEFAULT 0;
    SELECT COUNT(*) INTO c FROM table ;
    RETURNS c;
END
SELECT function('sb')$
SHOW CREAT FUNCTION function;#查看函数
DROP FUNCTION function
表proc内有所有的函数和存储过程

IF(FLAG,1,0);

IF score>=90 AND score<=100 THEN RETURNS 'A';
ELSEIF score>=80 THEN RETURNS 'B';
ELSE RETURNS 'C';
END IF 

CASE 变量|表达式|字段 
WHEN 1 THEN 100;
WHEN 2 THEN 200;
WHEN 3 THEN 300;
ELSE 400;
END CASE

CASE
WHEN score>=90 AND score<=100 THEN SELECT 'A';
WHEN score>=80 AND score<=90 THEN SELECT 'B';
ELSE SELECT 'C';
END CASE;

循环：iterate=continue ,结束本次循环继续下一次
      leave  =break    ,跳出循环。
DECLARE i INT DEFAULT 1;
aaaname:WHILE i<=count DO
    INSERT INTO table VALUES (CONCAT('USER',i),'666');
    SET i=i+1;(IF i>=20 THEN LEAVE aaaname);
END WHILE

杂记几个函数：

SELECT DATE_FORMAT(date,'%Y年%M月%D日');  
NOW()  ;获得当前时间，不同的数据库还不太一样。
CONCAT(str1,str2,…)  字符串拼接，不要再用+了，容易出事情啊。

索引

索引是一种帮助数据库高效获数据的一种排好序的数据结构，也就是是一种（高效获取数据的）数据结构。帮助查找降低数据库的IO成本同时降低排序的CPU消耗。解决IO时间比较高的问题。
但是索引需要占用磁盘空间，降低了增删改速度，需要花时间优化索引查询。
索引类型：
1.单值索引：一个表可以有多个单列索引（银行卡号、电话号码）最多不超5个；
2.唯一索引：唯一但允许空。
3.联合索引：一个索引包含多个列
4.全文索引
索引语法：

    CREATE [UNIQUE] INDEX indexNAME on mytable(col(length));
    ALTER mytable ADD [UNIQUE] INDEX [indexNAME] ON (col(length));
    ALTER TABLE mytable ADD FULLTEXT  indexNAMEON(col); 全文索引
    ALTER TABLE table ADD unique index(aa,bb);联合唯一索引
    SHOW INDEX FROM table_name; \G
    SHOW GLOBAL STATUS like 'Innodb_page_size';  叶子节点大小默认16kb

    CREATE INDEX INDEX_NAME ON TABLE(ID,COL1,COL2);
    CREATE INDEX INDEX_NAME1 ON tb_product('id','price','caption');

 CREATE INDEX INDEX_TABLE1_UNITED ON TABLE_NAME(COL1,COL2,COL3);

索引的数据结构：
1.B+Tree结构
2.HASH结构
需要创建索引的情况：
1.主键自动创建唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.频繁更新的字段不适合创建索引
5.WHERE条件里用不到的字段不创建索引
6.查询中排序的字段
7.查询中统计或者分组的字段
不必要建的情况：
1.表记录太少（百万以内）
2.经常增删改的表
3.数据重复且分布平均的表字段（性别、国籍）

索引数据结构的底层分析

常见数据结构：二叉树、红黑树、HASH表、B-Tree.
数据结构演示网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
二叉搜索树：根节点-->左小，右大。
红黑树（二叉平衡树）：自动平衡。
B-Tree与B+Tree,区别：
1.非叶子节点只存储键值信息。
2.所有叶子节点之间都有一个链指针。
3.数据记录都存放在叶子节点中。

B+tree链接：https://www.cnblogs.com/vianzhang/p/7922426.html.png

执行计划

性能分析中常见瓶颈
1.CPU饱和瓶颈，一般发生在数据装入内存或者从磁盘上读取数据的时候。
2.磁盘I/O瓶颈，发生在装入数据远大于内存容量的时候。
3.服务器硬件的性能瓶颈，top/free/iostat/vmstat查看性能
EXPLAIN：查看执行计划。explain select * from table； --explain +SQL语句；
执行计划表的阅读理解：
1.ID加载顺序,select查询的序列号三种情况
①ID相同，执行顺序由上到下。
②ID不同，如果是（子查询id序列号递增，id值越大越先执行）。
③ID相同不同同时存在，数字大优先级最高，平级时顺序执行。
2.select_type:SIMPLE简单查询、PRIMARY主查询、SUBQUERY子查询、DERIVERD衍生虚拟表查询、UNION联合查询、UNION RESULT联合查询结果集。
3.type:访问类型，最好到最坏system>const>eq_ref>ref>range>index>ALL。ALL全表扫描最差劲，const常出现主键和唯一键内，eq_ref唯一性索引扫描，ref非唯一性索引扫描，range范围搜索，index全索引扫描。
4.possible_keys：可能应用在这张表中的索引，但不一定实际使用。
5.key:实际使用的索引。
6.key_len：表示索引中使用的字节数的最大可能长度，并非实际使用长度。
7.ref:显示索引的哪一列被使用了。
8.rows：每张表有多少行被优化器查询。
9.Extra：额外扩展信息
①Using filesort 使用“文件排序“，性能低。
②Using temporary 使用临时表保存中间结果，常见于：order by和group by中，性能更低了。
③Using index 使用“覆盖索引”，性能较高,如果出现using where表明索引被用来执行索引键值的查找，没有出现using where表明索引用来读数据而非执行查找动作。
④Using where 使用WHERE语句过滤
⑤Using join buffer使用连接缓存
⑥Impossible where 过滤语句错误

索引优化分析

先看执行计划，再建索引，调优。索引是按顺序的，中间出现范围查询容易翻车。最好把中间字段删了。
JOIN语句优化：尽可能减少JOIN中的NestedLoop的循环总次数，小的结果集驱动大的结果集。优先优化内层循环。保证被驱动表上join字段被索引。同时 joinbuffer适当调大一点。
如何避免索引失效：
1.联合索引有顺序要求遵循最佳左前缀法则。
2.不能在索引列上做任何操作，否则会导致索引失效而转向全表扫描。
3.范围条件右边的列索引失效。尽量取什么用什么，少用select *。
4.使用不等于（!=）时，索引失效。is null ;is not null;也无法使用索引。
5.like以通配符开头时候查找索引之外的数据索引会失效，最好查找id和其他索引字段就好（覆盖索引）。
6.百分号最好在右边写，比如july%，不要%july%或者%july。
7.字符串不加单引号会导致索引失效。
8.order by也要按顺序,group by容易出现临时表。
永远小表驱动大表：
比如A比B大，就先查B，再用B的数据去匹配A的数据。SELECT * FROM A WHERE id IN(select id from B); 反过来用就是SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE A.id=B.id);
主外键表，左连接的特性：左表驱动，左表全有，所以右表要加索引。

慢查询日志

可以直接my.cnf修改slow_query_log为ON然后重启
或者使用语句

SHOW VARIABLES LIKE '%slow_query_log';
SET slow_query_log =1;
设置阀值   ：SHOW VARIABLES LIKE 'long_query_time%';
查看慢sql数：SHOW GLOBAL STATUS LIKE '%slow_queries%';

慢日志分析工具 ：mysqldumpslow
Show Profile:查看资源消耗情况

SHOW VARIABLES LIKE 'profiling';SET profiling=on;Show Profiles;
Show profile cpu,block io for query 4(编号);

四种情况需要注意：
1.converting HEAP to MyISAM查询结果太大，内存不够用。
2.Creating tmp table 创建临时表
3.Copying to temp table on disk 把内存中临时表复制到磁盘，危险
4.locked 出现锁
数据库优化步骤：
1.观察至少跑一天，看看生产的慢SQL情况。
2.开启慢查询日记，设置阀值，超过5秒的慢SQL并抓出来。
3.explain+慢SQL分析
4.SHOW PROFILE
5.运维进行SQL服务器的参数调优。

锁

锁是计算机调协多个进程或线程并发访问某一资源的机制（防止争抢）。
分为表锁（偏读MyISAM）、行锁（偏写Innodb）、页锁 会话关闭会自动解锁。
语法：

lock table table_name read(write),table_name2 read(write);
Show open tables; 查看表锁
unlock tables; 全局多表解锁；
行锁，配合事务隔离级别使用：
共享锁（S）：SELECT * FROM table_name WHERE ID=8 LOCK IN SHARE MODE
排他锁（X）：SELECT * FROM table_name WHERE ID=8 FOR UPDATE
事务隔离级别为RR的行锁是悲观锁。
SHOW STATUS LIKE 'innodb_row_lock%';
current_wait当前锁数量、lock_wait等待总次数、lock_time_avg等待平均时长、lock_time等待总时长

索引失效会导致行锁变表锁。
间隙锁，使用范围检索INNODB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做"间隙GAP",NEXT-KEY锁。
关于锁的建议：
1.尽可能让所有数据检索通过索引完成，避免无索引行锁升级为表锁
2.合理设计索引，尽量缩小锁范围
3.尽量可能较少检索条件避免间隙锁
4.尽量控制事务大小，减少锁定资源量和时间长度
5.尽可能低级别事务隔离。

关于死锁

死锁是一个程序概念，不仅限于在JAVA编程时会出现，在数据库操作时也会出现。
是由于多个并发进程因争夺系统资源而产生相互等待导致的现象。
死锁四要素：
1.资源互斥： 某种资源一次只允许一个进程访问，即该资源一旦分配给某个进程，其他进程就不能再访问，直到该进程访问结束。
2.占有且等待： 一个进程本身占有资源（一种或多种），同时还有资源未得到满足，正在等待其他进程释放该资源。
3.不可抢占： 别人已经占有了某项资源，你不能因为自己也需要该资源，就去把别人的资源抢过来。
4.循环等待： 存在一个进程链，使得每个进程都占有下一个进程所需的至少一种资源。

在数据库中，由于存在事务的隔离等级，“锁"便被利用起来，第一个条件资源互斥满足。
由于事务开启后，如果不提交事务，"锁"不会主动释放，第二个条件占有且等待满足。
一个事务无法抢占其他事务的锁，第三个条件不可抢占满足。
那么如果这时候循环等待满足了，就会产生死锁，所以数据库的死锁很大部分是和循环等待有关的。
比如：
A事务拿到a数据，判断后还需要拿b数据，最后提交数据。
B事务拿到b数据，判断后还需要拿c数据，最后提交数据。
C事务拿到c数据，判断后还需要拿a数据，最后提交数据。
假设他们同时开始，那么三个事务都无法提交。

主从复制（备份）。

原理：
1.master将改变记录到二进制文件（binary log），这个记录过程叫做二进制日志事件--binary log events
2.slave将matera的binary log events拷贝到它的中继日志relay log
3.slave重做中继日志中的事件，将改变应用到自己的数据库中，MYSQL的复制是异步、串行化的。
要求版本一致

致谢

尚硅谷MySql的网课
李玉婷老师的课很好听，推荐大家有空可以看看自己需要的那部分。