【SQL学习历程】1.MYSQL的一些基础知识

本文内容包括了MySQL比较系统全面的基础知识总结和学习的时候遇到的一些问题，算是对学习的回顾。
为什么是MySQL？内部原因是本人用的最多的数据库就是MySQL，但是一直没有进行系统的学习，导致对于它的知识一直是零碎的、不成系统的，从MySQL开始学习对以后其他数据库的学习都有一定的帮助。外部而言，对于数据分析师而言，数据库是至关重要的，而MySQL的优势显而易见——开源、灵活，对于不必对数据库的开发投入太多精力的数据分析师职业是一个很好的选择。

一.MySQL架构

MySQL架构与其他数据库最大的区别在于，它的储存引擎架构是插件式的，将查询处理和其他系统任务和数据的存储提取分离开，给不同的业务需求和实际需要提供了多样的存储引擎。

架构（从上往下）：

1.连接层：用于连接处理、授权认证、安全方案。
2.服务层：大部分核心功能在这里完成，包括了解析、分析、优化、缓存、所有的内置函数、触发器、存储过程、视图
3.引擎层：存储引擎负责MySQL数据的存储和提取，不同的引擎功能不同，可以自定义选取。
4.存储层：将数据储存在运行于该设备的文件系统上，同时要完成与储存引擎的交互。
架构图：

二.存储引擎

存储引擎是MySQL的组件，不同引擎提供不同的存储机制、索引技巧、锁定水平等功能。一个数据库中的多个表可以使用不同引擎满足各种性能的实际需求。
查看和设置引擎的SQL语句

-- 设置存储引擎
-- 在建表的时候指定引擎（默认的是INNODB）
CREATE TABLE T1(i INT) ENGINE=INNODB;
CREATE TABLE T2(i INT) ENGINE=CSV;
CREATE TABLE T3(i INT) ENGINE=MEMORY;

--修改存储引擎
ALTER TABLE T ENGINE=INNODB;

--修改默认存储引擎
SET DEFAULT_STORAGE_ENGINE=NDBCLUSTER;

文件存储结构

查看SQL文件保存位置：

show variables like 'data%'

INNODB的物理文件结构为：
.frm：与表相关的源文件数据信息
.ibd或.ibdate：存放INNODB数据的文件，独享表空间使用.idb,共享表空间使用.ibdate

INNODB和MylSAM的对比

INNODB	MylSAM
支持事务	不支持事务
支持外键	不支持外键
聚蔟索引	非聚簇索引
不保存表具体行数	保存整个表行数
最小锁粒度：行锁	最小锁粒度：表锁

三.数据结构

主要有五个类型：

• 整数类型：BIT、BOOT、TINY INT、SMALL INT、MEDIUM INT、INT、BIG INT
• 浮点数类型：FLOAT、DOUBLE、DECIMAL
• 字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
• 日期类型：DATE、DATETIME、TIMESTAMP、TIME、YEAR
• 其他类型：BINARY、SET、POINT、POLYGON、GEOMETRY等
  
  
  

*CHAR和VARCHAR的对比

1.char的长度固定，varchar的长度可变，char（）和varchar（）括号中的数字代表字符的个数。
2.存储时，char按照char的长度分配空间，varchar按照实际的数据分配存储空间。存储时，char占用n个字符的空间，而varchar占用字符实际字节空间加1（用来储存长度，如果长度>255，则多占用1）
3.char适合存储很短、固定长度的字符串。

*BLOB和TEXT的区别

BLOB是二进制对象，容纳可变数量的数据，而TEXT是一个不区分大小写的BLOB，一般保存字符数据。

*列的字符串类型是：SET、BLOB、ENUM、CHAR、TEXT、VARCHAR

四.索引

索引的本质就是数据结构，目的在于提高查询效率。索引以数据结构的方式指向数据，从而在这些数据结构上实现高级查找算法。一般说的索引就是B+树结构组织的索引。
基本语法

-- 创建索引
CREATE [UNIQUE] INDEX indexNAME ON MYTABLE(username(LENGTH));
-- 添加索引（修改表结构）
ALTER TABLE TABLENAME ADD [UNIQUE] INDEX indexNAME(columnNAME)
-- 删除索引
DROP INDEX[indexNAME] ON MYTABLE;
-- 查看
SHOW INDEX FROM TABLE_NAME\G --通过\G来格式化输出信息
-- 使用ALTRT命令
  --添加一个主键，索引值必须唯一且不为NULL
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
  --创建唯一的索引值（可能包含多个NULL）
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
  --索引值可出现多次
ALTER TABLE tbl_name ADD INDEX index_name (column_list)
  --索引为FULLTEXT，用于全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

优缺点

1.降低数据库IO成本，提高数据检索效率，降低数据排序成本，降低CPU的消耗
2.索引是需要占用内存的表，保存了主键和索引字段。
3.降低更新表的速度。使用INSERT、UPDATE和DELETE时需要多保存索引文件更新的索引列的字段。

创建索引的情况

1.需要创建索引：

• 主键自动建立唯一索引
• 频繁作为查询条件的字段
• 查询中与其他表关联的字段、外部关系建立索引
• 查询中排序的字段
• 查询中统计和分组字段
  2.不需要创建索引
• 表记录太少
• 经常增删改的表
• 数据重复且分布均匀的表字段
• where条件里用不到的字段

五.MySQL查询

count的使用

1.count（*）包含了所有的列，在统计时不会忽略列值为NULL的计数
2.count（1）包括了所有的列，用1代表代码行，在统计时不会忽略列名为NULL的计数
3.count（列名）只包括列名一列，在统计时会忽略列值为空的计数，但是当字段为NULL时不统计。
4.执行效率：
当列名为主键，count（列名）快于count（1）。列名不为主键，count（1）更快。
表有多个列，且没有主键，count（1）快于count（ *）
有主键时最快的方法是select count（主键）
表只有一个字段时最快方法是select count（ *）

UNION 和 UNION ALL 的区别

两者都是将两个结果合并为一个，两个要合并的语句字段个数必须一样，字段类型一致。
1.UNION会筛选掉重复的数据记录，降低效率，而UNION ALL不会去掉重复的记录。
2.UNION按照字段的顺序排序后返回，UNION ALL将两个结果合并之后就返回。

IN 和 EXISTS 的区别

1.exists 对外表用loop逐条查询，当exist里的条件语句能够返回记录行时，条件为True，返回当前loop的记录。不能返回时，当前loop的记录就会被丢弃。
2.exists适用于大表，in更适用于小表。

SELECT * FROM A WHERE A.ID IN(SELECT ID FROM B);
SELECT * FROM A WHERE A.ID EXISTS(SELECT * FROM B WHERE B.ID=A.ID)

SQL的执行顺序

SELECT DISTINCT <select_list> 
FROM <left_table><join_type> 
JOIN <right_table> ON <join_condition> 
WHERE <where_condition> 
GROUP BY<group_by_list> 
HAVING<having_condition> 
ORDER BY<order_by_condition> 
LIMIT<limit_number>

各种join解析

A为左表，B为右表，设C为两表交集，ON条件为A.key=B.key
1.LEFT JOIN：以左表为准
无附加条件：结果为A
WHERE B.KEY IS NULL:结果为A-C
2.RIGHT JOIN：以右表为准
无附加条件：结果为B
WHERE A.KEY IS NULL:结果为B-C
3.INNER JOIN：结果为C
4.FULL OUTER JOIN：两个表所有的结果
无附加条件：结果为A+B-C
WHERE A.KEY IS NULL
OR B.KEY IS NULL结果为A+B-2C
5.JOIN：左右都可匹配

六.MySQL事务

MYSQL事务主要用于处理操作量大、复杂度高的数据。

事务基本要素

A（Atomicity）原子性：事务中的所有操作需要在一次流程中全部完成，如果中间出现错误，则会被回滚到事务开始前的状态。
C（Consistency）一致性：事务开始之前和结束之后，数据库的完整性约束没有被破坏。
I（Isolation）隔离性：一个事务的执行不能被其他事务干扰，并发执行的各个事务不能互相干扰。
D（Durability）持久性：事务完成之后，该事务对数据库的更改就会持久保存，不会被回滚。

并发事务的问题和解决方法

1.更新丢失：两个事务选择同一行，根据最初选定的值更行改行时，会发生更新丢失的问题。
解决方法：事务控制器+应用程序对要更新的数据加上必要的锁
2.脏读：事务A读取了事务B更新后的数据，B回滚操作，A读到的数据就是脏数据
解决方法：两种事务隔离机制，一种是加锁，一种是数据多版本并发控制。
3.幻读：事务A读取了几行数据之后，事务B插入了一些数据，随后A会发现多出了一些原本不存在的记录。
解决方法：同脏读。

事务隔离

事务隔离有以下四个级别（由低到高）：

• READ-UNCOMMITED（读未提交）：允许读取尚未提交的数据变更，可能导致脏读、幻读、不可重复读。
• READ-COMMITTED（读已提交）：允许读取并发事务已提交的数据，可能发生幻读、不可重复读。
• REPEATABLE-READ（可重复读）：A对同一字段的多次读取结果都是一致的，除非该字段是被A自己修改。可能发生幻读。
• SERIALIZABLE（可串行化）：所有事务依次逐个执行，事务之间不可能产生干扰。

选择隔离级别，一要看应用对“不可重复读”和“幻读”的敏感程度，二要看数据并发访问的能力

-- 查看数据库事务隔离级别
show variables like 'tx_isolation'

事务的实现

事务基于数据库的存储引擎，MySQL支持事物的引擎有INNODB和NDB
事物的隔离性通过锁来实现，而原子性、一致性和持久性通过事务日志实现。

事务日志

事务日志包括了重做日志redo和回滚日志undo

• redo log（重做）实现持久性和原子性。
  在INNODB中，事务日志通过redo log和引擎的日志缓冲实现。事务中的操作会先写入引擎的日志缓冲中，事务提交之前，缓冲的日志都要提前刷新到磁盘上持久化。若数据库崩溃，已完成的事物可以根据日志恢复，未完成的可以选择提交或者回滚。
• undo log（回滚）实现一致性
  undo log记录数据在操作前的状态，如果需要回滚，则根据undo log进行回滚操作。

七.MySQL锁机制

锁是计算机协调多个进程并发访问某一资源的机制。数据库锁就是数据库为了保持一致性，使各种共享资源在被并发访问中变得有序的一种规则。

锁的分类

从对数据操作的类型分类：

• 读锁：针对同一份数据，可以同时进行多个读操作。
• 写锁：当前写操作没完成前，阻断其他写锁和读锁。

从对数据操作的粒度分类：

• 表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。适用于以查询为主，只有少量按索引条件更新数据的应用。
• 行级锁：开销大，加锁满，会出现死锁，锁定力度最小，发生锁冲突的改率最低，并发度最高。适用于有大量按索引条件并发更新少量不同数据，又有并发查询的应用。
• 页面锁：介于两种之间，会出现死锁。

INNODB行锁

INNODB有以下两种类型的行锁：

• 共享锁：允许一个事务读一行，阻止其他事物获得相同数据集的排他锁。
• 排他锁：允许获得排他锁的十五更新数据，组织其他事务取得相同数据集的共享读锁和排他写锁。

加锁机制

1.乐观锁假定大概率不会发生并发更新冲突，在访问和数据处理的过程中不加锁，在更新数据时根据版本号和时间戳判断冲突，有冲突则处理，无则提交。
2.悲观锁假定大概率会发生更新冲突，访问、处理数据时加排他锁，整个数据处理过程锁定数据，事务提交或回滚后才释放锁。悲观锁是数据库默认实现了的，使用时调用相关语句就可以了。
*select for update的含义

-- 明确指定主键，有此条记录，row rock
SELECT * FROM A WHERE ID='3' FOR UPDATE;
SELECT * FROM A WHERE ID='3' AND TYPE=1 FOR UPDATE

--明确指定主键，无此记录，无rock
SELECT * FROM A WHERE ID='-1' FOR UPDATE;

--无主键，table rock
SELECT * FROM A WHERE NAME='MOUSE' FOR UPDATE

--主键不明确，table rock
SELECT * FROM A WHERE ID<>'3' FOR UPDATE;
SELECT * FROM A WHERE ID LIKE '3' FOR UPDATE

死锁

1.产生原因：

• 两个或多个事务在同一资源上相互占用，请求锁定对方占用的资源，产生恶性循环。
• 事务试图以不同的顺序锁定资源，或者多个事务同时锁定一个资源。
• 存储引擎实现方式原因。

2.如何检测：INNODB能检测到死锁的循环依赖并返回一个错误。
3.如何恢复：只有部分或完全回滚其中一个事务才能打破死锁。一般是将最少行级排他锁的事务进行回滚。
4.INNODB的死锁避免：

• 在事务开始时通过为要修改的每个行使用SELECT FOR UPDATE来获取必要的锁。
• 事务中，要更新记录，应该直接申请足够级别的排他锁。
• 若事务要修改多个表，则在每个事务中以相同的顺序使用加锁语句。
• 改变事务隔离级别。

5.死锁处理：如果产生死锁，可以使用
SHOW ENGINE INNODB STATUS
来确定最后一个死锁产生的原因，据此分析改进措施。

八.MySQL优化

影响MySQL性能的因素

• 业务需求对MySQL的影响
• 存储定位对MySQL的影响
• 不适用放进MySQL的数据
• 超大文本数据
• 流水队列数据
• 二进制多媒体数据
• 需要放进缓存的数据
• 系统各种配置及规则数据
• 活跃用户的基本信息数据
• 活跃用户的个性化定制信息数据
• 准实时的统计信息数据
• 其他一些访问频繁但变更较少的数据
• 硬件环境对性能的影响

性能瓶颈定位

MYSQL>SHOW STATUS --显示状态信息
MYSQL>SHOW VARIABLES --显示系统变量
MYSQL>SHOW INNODB STATUS --显示INNODB存储引擎状态
MYSQL>SHOW PROCESSLIST --查看当前SQL执行
SHELL>MYSQLADMIN VARIABLES -U USERNAME -P PASSWORD --显示系统变量
SHELL>MYSQLADMIN EXTENDED-STATUS -U USERNAME -P PASSWORD -- 显示状态信息

索引优化

• 对于单键索引，尽量选择针对当前query过滤性更好的索引。
• 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合索引的时候，尽量选择可以包含当前Query中的where字段中更多字段的索引。

查询优化

小表驱动大表

SELECT * FROM A WHERE ID IN
(SELECT ID FROM B) --等价于
SELECT ID FROM B
SELECT * FROM A WHERE A.ID=B.ID

当B表的数据集小于A的数据集时，用in优于exists

SELECT * FROM A WHERE EXISTS (SELECT 1FROM B WHERE B.ID=A.ID
--等价于
SELECT * FROM A
SELECT * FROM B WHERE B.ID=A.ID

*ORDER BY 关键字优化

• order by子句尽量用index的方式排序，避免使用filesort
• 尽可能在索引列上完成排序操作，遵照索引键的最佳最前缀

*GROUP BY关键字优化

• 遵照索引键的最佳左前缀
• 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
• 能用在where条件限定就不要用having

*数据类型优化

• 尽量用能正确存储数据的最小数据类型
• 尽量避免NULL

九.其他问题

如何删除量极大的数据

1. 删除索引
2. 删除无用数据
3. 重新创建索引
4. 删除数据

三个范式

1. 第一范式，数据库表中的字段都是单一属性，不可再分。
2. 第二范式，数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖，及非关键字段完全依赖于任意椅子候选关键字。
3. 都三范式，在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。