MySQL基础知识笔记
基础
查询：SELECT DISTINCT <Top Num> <select list> FROM <table> <join_type> JOIN <right_table> ON <join_condition> WHERE <condition> GROUP BY <list> HAVING <condition> ORDER BY <list> LIMIT <list>
插入：INSERT INTO <table> VALUES(list)
更新：UPDATE <table> SET a=b WHERE <condition>
删除：DELETE FROM <table> WHERE <condition>
进阶
LIMIT #取第N到第M条记录
IN #用于子查询
BETWEEN AND #设置区间
LIKE/REGEXP #匹配通配符/正则表达式
GROUP BY HAVING #按组查询，设置条件语句
ALIAS(AS) #可以为表或列取别名
LEFT/RIGHT/FULL JOIN #左连接/右连接/全连接
OUT/INNER JOIN #内连接/外连接
UNION/UNION ALL #并集，后者不去重
INTERSECT #交集
EXCEPT #差集
SELECT INTO #查询结果赋给变量或表
CREATE TABLE #创建表
CREATE VIEW AS #创建视图
CREATE INDEX #创建索引
CREATE PROCEDURE BEGIN END #创建存储过程
CREATE TRIGGER T_name BEFORE/AFTER INSERT/UPDATE/DELETE ON MyTable FOR #创建触发器
ALTER TABLE ADD/MODIFY COLUMN/DROP #修改表:增加字段/修改字段属性/删除字段
UNIQUE #字段、索引的唯一性约束
CHECK #限制字段值的范围
TRUNCATE TABLE #删除表数据，不删表结构
函数
#日期时间函数
Date() #返回日期部分
DateDiff() #计算两个日期之差
Date_Add() #高度灵活的日期运算函数
Date_Format() #返回一个格式化的日期或时间串
CurDate() #返回当前日期
CurTime() #返回当前时间
AddDate() #增加一个日期，天、周等
AddTime() #增加一个时间，天、周等
Day() #返回一个日期的天数部分
DayOfWeek() #返回一个日期对应的星期几
Hour() #返回一个时间的小时部分
Minute() #返回一个时间的分钟部分
Month() #返回一个日期的月份部分
Now() #返回当前日期和时间
Second() #返回一个时间的秒部分
Time() #返回一个日期时间的时间部分
Year() #返回一个日期的年份部分
#数值函数
Max() Min() Avg() Sum() Count() #求均值
Round(n,m) #以m位小数来对n四舍五入
Mid(ColumnName,Start,[,length]) #得到字符串的一部分
Convert(xxx,TYPE/Cast(xxx AS TYPE)) #把xxx转为TYPE类型的数据
Abs() #求绝对值
Mod() #求余，同'%'
Sqrt() #求开方
Exp(n) #求e^n，同'**'
Pi() #求圆周率
Rand() #返回一个随机数
Sin() #求一个角度的正弦值
Cos() #求一个角度的余弦值
Tan() #求一个角度的正切值
Format() #用来格式化数值
First(ColumnName) #返回指定字段中第一条记录
Last(ColumnName) #返回指定字段中最后一条记录
#文本函数
Length(str) #返回字符串str长度
Locate(substr,str) #返回子串substr在字符串str第一次出现的位置
LTrim(str) #移除字符串str左边的空格
RTrim(str) #移除字符串str右边的空格
Trim(str) #移除字符串str左右两边的空格
Left(str,n) #返回字符串str最左边的n个字符
Right(str,n) #返回字符串str最右边的n个字符
Soundex() #读音类似
SubString(str,pos,len)/Substr() #从pos位置开始截取str字符串中长度为的字符串
Upper(str)/Ucase(str) #小写转化为大写
Lower(str)/Lcase(str) #大写转化为小写
一、MySQL架构
连接层：客户端和连接服务
服务层：核心服务功能， 包括查询解析、分析、优化、缓存、内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等。
引擎层：数据的存储和提取
存储层：将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互
客户端请求 —> 连接器（验证用户身份，给予权限） —> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） —>
分析器（对SQL进行词法分析和语法分析操作） —> 优化器（主要对执行的sql优化选择最优的执行方案方法） —>
执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） —> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）
第一范式(1NF)
数据表的每个字段(属性)必须是唯一的、不可分割的。
第二范式(2NF)
数据表的每条记录必须是唯一的(主键约束)，且非主键字段只依赖于主键。
第三范式(3NF)
数据表中不应该存在多余的字段，也就是说每个字段都不能由其他字段推理得到。
逆范式：不按照标准的范式去设计数据库。
二、存储引擎
常用存储引擎： InnoDB、MyISAM、Memory、NDB
InnoDB引擎的4大特性：插入缓冲、二次写、自适应哈希索引、预读。
三、数据类型
整数、浮点数、字符串、日期、其他
整数：tinyint（8位二进制）、smallint（16位二进制）、mediumint（24位二进制）、int（32位二进制）
浮点数：float（单精度）double（双精度）decimal（压缩严格定点数）
日期：year (yyyy) date(yyyy-mm-dd) time(hh:mm:ss) datetime timestamp
文本：char(m) 0-255 varchar(m) 0-65535的整数
枚举：ENUM
四、索引
帮助MySQL高效获取数据的 数据结构
优势：提高数据检索效率，降低数据库IO成本；降低数据排序的成本，降低CPU的消耗。
劣势：索引也是一张表，需要占用内存；降低更新表的速度，如对表进行INSERT、UPDATE和DELETE
数据结构角度：
B+树索引（常用）
Hash索引
Full-Text全文索引
R-Tree索引
物理存储角度:
聚集索引（clustered index）
非聚集索引（non-clustered index），也叫辅助索引（secondary index） 聚集索引和非聚集索引都是B+树结构
逻辑角度
主键索引：主键索引是一种特殊的唯一索引，不允许有空值 普通索引或者单列索引：每个索引只包含单个列，一个表可以有多个单列索引
多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。
创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建。
索引结构
index是在存储引擎（storage engine）层面实现的，而不是server层面
InnoDB 存储引擎就是用 B+Tree 索引结构
主键索引与辅助索引的结构：
MyISAM的索引文件和数据文件是分离的。叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址。
InnoDB引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录。InnoDB的数据文件本身就是主键索引文件，这样的索引被称为"“聚簇索引”，一个表只能有一个聚簇索引。
Hash索引:目前有Memory引擎和NDB引擎支持Hash索引
full-text全文索引: MyISAM\ InnoDB
哪些情况需要创建索引
主键自动建立唯一索引
频繁作为查询条件的字段
查询中与其他表关联的字段，外键关系建立索引
单键/组合索引的选择问题，高并发下倾向创建组合索引
查询中排序的字段，排序字段通过索引访问大幅提高排序速度
查询中统计或分组字段
哪些情况不要创建索引
表记录太少
经常增删改的表
数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
频繁更新的字段不适合创建索引（会加重IO负担）
where条件里用不到的字段不创建索引
MySQL高效索引
覆盖索引(不需要回表操作)
五、MySQL查询
count() 和 count(1)和count(列名)区别
count()包括了所有的列，相当于行数，不会忽略列值为NULL
count(1)包括了所有列，用1代表代码行，不会忽略列值为NULL
count(列名)只包括列名那一列，会忽略列值为NULL
MySQL中 in和 exists 的区别？* 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
UNION和UNION ALL的区别? 将两个结果集合并为一个，两个要联合的SQL语句
字段个数必须一样，而且字段类型要“相容”（一致）； UNION在进行表连接后会筛选掉重复的数据记录（效率较低），而UNION
ALL则不会去掉重复的数据记录 UNION会按照字段的顺序进行排序
三大范式
第一范式：每个列都不可以再拆分。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。
六、MySQL 事务（存储引擎层实现）
事务的隔离级别有哪些？MySQL的默认隔离级别是什么？
什么是幻读，脏读，不可重复读呢？MySQL事务的四大特性以及实现原理。 MVCC熟悉吗，它的底层原理？
1.Read Uncommited （未提交读）
事务可以读取未提交的数据，也称脏读。非常可怕。
2.Read Commited （提交读）
一个事务从开始到提交之前，所做的修改对其他事务是不可见的。但是重复执行同样的查询可能会导致不同的结果。
3.Repeatable Read （可重复读）
这是mysql的默认隔离级别，重复执行同样的查询结果相同。
4.Serializable （可串行化）
最高的隔离级别，它强制事务串行执行。会导致大量的超时和锁争用问题。虽然数据一致性好，但是并发能力很弱，一般也很少使用。
注： 在隔离级别是repeatable read下，select是快照读。但是如果使用的是 select for update, 就是不可重复读的，也就是说可以读取到其他事务的修改。
ACID属性
A (Atomicity) 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。
C (Consistency) 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
I(Isolation)隔离性：一个事务的执行不能其它事务干扰。
D (Durability) 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚
支持事务，能崩溃恢复，行级锁，非锁定读，聚簇索引，外键
InnoDB：用于MySQL的事务安全（ACID支持）存储引擎，具有提交，回滚和崩溃恢复的功能以保护用户数据。
InnoDB行级锁定（不升级到更粗的粒度锁）和Oracle风格的非锁定读取增加了多用户并发性和性能。
InnoDB将用户数据存储在聚簇索引中，以减少基于主键的常见查询的I / O。 为了保持数据完整性，InnoDB还支持FOREIGN KEY参照完整性约束。
存储过程（属于事务）
1.创建：
create procedure <name>()
begin
declare var_name type *declare num int*
set num=num+3;
SQL语句---
end;
delimitor $：更改分隔符
2.调用： call <name>;
3.查看：
select name from mysql.proc where db=<database>;
#查看proc 表记录所有存储过程；
show procedure status;
show create procedure px;
4.删除：drop procedure [if exists] <name>;
传递参数
create procedure px4(in height int,out description varchar(10) )
begin
if height >= 180 then
set description='身材高挑';
elseif height >= 170 and height < 180 then
set description='标准身材';
else
set description='一般身材';
end if;
end$
call px4(176)
case 语法
create procedure px6(mon int)
begin
declare result varchar(10);
case
when mon>=1 and mon<=3 then
set result= '第一季度';
when mon>=4 and mon<=6 then
set result= '第二季度';
when mon>=7 and mon<=9 then
set result= '第三季度';
else set result= '第四季度';
end;
select concat('结果为',result);
end$
循环结构while
#从1到n累加
create procedure px7 (n int)
begin
declare total int default 0;
declare num int default 1;
while num<=n do
set total = total + num;
set num=num+1;
end while;
select total;
end$
repeat
begin
declare total int default 0;
repeat
set total = total + n;
set n=n-1;
until n=0
end repeat;
select total;
end$
loop leave…
游标
游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理。
声明、open、fetch、clase
create procedure px8 ()
begin
declare
declare stu_result cursor for select * from student;
open stu_result into ;
fetch stu_result;
close stu_result;;
end$
存储函数（有返回值）
create function fun()
select fun(*);
七、MySQL锁机制
数据库的乐观锁和悲观锁？MySQL 中有哪几种锁，列举一下？MySQL中InnoDB引擎的行锁是怎么实现的？MySQL 间隙锁有没有了解，死锁有没有了解，写一段会造成死锁的 sql 语句，死锁发生了如何解决，MySQL 有没有提供什么机制去解决死锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。
锁：完全在存储引擎层实现，分：锁策略：表锁和行锁、锁粒度
读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响
写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁
八、MySQL调优
日常工作中你是怎么优化SQL的？SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义？如何写sql能够有效的使用到复合索引？一条sql执行过长的时间，你如何优化，从哪些方面入手？什么是最左前缀原则？什么是最左匹配原则？
慢查询日志
记录在 MySQL 中响应时间超过阈值的语句，具体指运行时间超过 long_query_time（默认10） 值的 SQL，则会被记录到慢查询日志中。
性能优化
索引优化【全值匹配、最佳左前缀法则、不在索引列上做任何操作、存储引擎不能使用索引中范围条件右边的列、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select is null ,is not null 也无法使用索引、字符串不加单引号索引失效、少用or，用它来连接时会索引失效、<，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行，会导致全表扫描
触发器
触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。old new
SQL执行频次查询
show global status like ‘Com_______’;
show status like ‘innodb_rows_%’\G;
定位低效的查询语句：
慢查询日志和show processlist；查看所有客户端正在操作的信息；
±—±-----±----------------±-----±--------±-----±---------±-----------------+
| Id | User | Host | db | Command | Time | State | Info |
±—±-----±----------------±-----±--------±-----±---------±-----------------+
| 14 | root | localhost:61212 | tons | Query | 0 | starting | show processlist |
±—±-----±----------------±-----±--------±-----±---------±-----------------+
安全管理
第一层网络：有保护的局域网或跳板机做端口转发的公网
第二层主机：1.系统账号都改成基于ssh key认证，不允许远程密码登入，且ssh key的算法、长度有要求以确保相对安全。2.禁止root账号远程登录主机。3.正确设置MySQL及其他数据库服务相关目录权限，不要全是755，一般750就够了。
第三层数据库：1.严格限制数据库账号权限级别。业务帐号，权限最小化，坚决不允许DROP、TRUNCATE权限。2.设置MySQL账号的密码安全策略，包括长度、复杂性。
未完待续
基础：sql语句、表结构设计
调优：索引、慢查询优化、配置参数调优
核心原理：
InnoDb存储引擎 （包括隔离级别、事务、锁、缓存池、回滚日志等等）
Mysqld (包括连接管理、进程管理、查询缓存、查询优化、日志等等)
架构与运维：用户与权限、安全备份与恢复、日志、分布式与高可用
3148
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
