MySQL学习笔记

1.MySQL

和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上。插件式的存储引擎架构将查询出来和其他的系统任务以及数据存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
三大范式

• 确保每列保持原子性
• 确保表中的每列都和主键相关
• 确保每列都和主键列直接相关,而不是间接相关

1.1连接层

最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp\ip的通信。主要完成一些类似于连接处理、授权认证及相关的安全方案链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

1.2服务层

主要完成大多数的核心服务功能，如SQL接口。并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在本层实习，如存储过程、函数等。服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

1.3引擎层

存储引擎真正的负责MySQL中数据的存储和提取，服务器通过api与存储引擎进行通信，不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选座。（后面我们将介绍myisam和innoDB）

1.4存储层

数据存储层，主要是将数据存储在运行于设备的文件系统之上，并完成与存储引擎的交互。

1.5常用引擎对比InnoDB与MyISAM

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其他行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引，还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性影响
关注点	性能	事务
表空间	小	大
默认安装	是	是

2.性能下降SQL慢原因分析

1）执行时间长
①查询语句写的烂
②索引失效
③关联查询太多join（设计缺陷或不得已的需求）
④服务器调优及各个参数设置（缓存、线程数等）
2）等待时间长
索引分类
单值：create index idx_user_name on user(name)
复合：create index idx_user_nameAge on user(name,age)

3.SQL执行加载顺序

1手写：
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>
LIMIT <limit_number>

2机读：
FROM <left_table>
ON <join_condition>
<Join_type> JION <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMLT <limit_number>

3总结

4.JOIN查询

学生表	成绩表

4.1图1 ~内连接AB两表共有（交集）

4.2图2~左连接左边全部

4.3图3~右连接右边全部

4.4图4~A表独有

4.5图5 ~B表独有

4.6图6~AB全部数据（并集）

4.7图7A表独有并B表独有

5.索引

5.1什么是索引？

MySQL官方对索引的定义：索引（index）是帮助MySQL高效获取数据的数据结构。
索引的目的在于提高查询效率，类比字典(没加索引前顺序是乱的，加后先排序，好比同学站队没加索引前全是乱站，加索引后按照从低到高排队，你要找170的同学就比较好找)_{如果要查询“MySQL”这个单词，没有索引就全表扫描，加索引后，我们要定位到“M”在到“y”->到“sql”}
在数据本身之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

col1、col2是数据表，一共有两列七条记录，最左边的是数据记录的物理地址，为了加快col2的查找，可以维护一个右边所示的二叉树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上
我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都使用B+树索引，统称索引。除了B+树这种类型的索引之外，还有哈希（hash index）索引等

5.2索引优劣势

优势：

• 类似大学图书馆建书目录索引，提高数据检索的效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低了cpu的消耗

劣势：

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
• 虽然索引大大提高了查询速度，同时却降低更新表的速度，如对表进行insert、update和delete都会调整因为更新所带来的键值变化后的索引信息
• 索引只是提高效率的一个因素，如果你的MySQL有大量的表，就需要花时间研究建立最优秀的索引，或优化查询。

5.3索引分类与基本语法

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引；
唯一索引：索引列的值必须唯一，但可以为空值；
复合索引：一个索引包含多列
基本语法
创建
CREATE [UNIQUE] INDEX table_nameON tableName (cokumnname(length));
ALTER TABLE table_nameADD [UNIQUE] INDEX [indexName] (cokumnname(length))
删除
Drop index [indexName] ON table_name
查看
SHOW INDEX FROM table_name

5.4需要/不需要创建索引的场景

• 那些情况需要创建索引：
  1 主键 自动建立唯一索引
  2 频繁作为查询条件的字段 创建索引
  3 查询中与其它表关联的字段，外键关系建立索引
  4 单键、组合索引的选择问题（在高并发下倾向创建组合索引）
  5 查询中排序的字段，排序字段若通过索引去访问将提高排序效率
  6 查询中统计或者分组字段
• 那些情况不用创建索引：
  1 表记录太少
  2 经常增删改的字段
  3 注意，如果某个数据列包含许多重复的内容，为它创建索引没有太大实际效果
  4 频繁更新的字段不适合建立索引~每次更新时需要更新索引，加重了IO负担
  5 where条件里用不到的字段不创建索引

5.5MySQL常见的瓶颈

1CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
2IO磁盘IO瓶颈发生在装入数据远大于内存容量的时候
3服务器硬件的性能瓶颈：top,free,iostat和vmstat来看查看系统的性能状态

6.EXPLAIN 查看执行计划

6.1基本信息

作用：
1表的读取顺序
2数据读取操作的操作类型
3那些索引可以使用
4那些索引被实际使用
5表之间的引用
6每张表有多少行被优化器查询
语法： explain + SQL语句

6.2包含的信息：

id	Select_type	table	type	Passible_keys	key	Key_len	ref	rows	extra

Id : select查询的序列号；包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况：
①Id相同，执行顺序由上往下

②Id不同，如果是子查询，id的序号会递增，id值越大优先级越高，优先被执行

③Id相同不相同，同时存在

Select_type:
有6种情况：

• SIMPLE：简单的select查询，查询不包含子查询或者union
• PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为primary
• SUBQUERY：在select或者where列表中包含了子查询
• DERIVED：在from列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
• UNION: 若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为DERIVED
• UNION RESULT：从UNION表获取结果的select

Table：显示这行的数据是关于哪张表的
Type：

• System：表只有一行记录（等于系统表，这是const）类型的特列，平时不会出现
• Const:表示通过索引一次就找到了，const用于比较primary key或者unique 索引。因为只匹配一行数据，所以很快如将主表置于where列表中，MySQL就能将该查询转换为一个常量
• Eq_ref:唯一索引扫描，对于每个索引键，表中只有一条记录匹配。常见于主键或者唯一索引扫描
• Ref: 非唯一索引扫描，返回匹配某个单独值的所有行。
  本质上也是一种索引访问，它返回所有匹配某个单独值的行然而他可能会找到多个符合条件的行，所以他应该属于找到和扫描的混合体
• Range： 只检索给定的范围行，使用一个索引来选择行。Key列显示使用了那个索引
  1 一般就是在where语句中出现between、>、<、in等查询
  2 这种范围扫描索引扫描比全表索引要好，因为他只需要开始于索引某一点结束另一点不用扫描全部索引
• Index：full index scan,index与all区别为index类型只遍历索引树。这通常比all快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但是index是从索引中读取，而all是从硬盘中读取的）
• All: full table scan将遍历全表以找到匹配的行
  一般来说，得保证查询至少到达range级别，最好能达到ref

Passible_keys：显示可能应用在这张表的索引，一个或多个。
涉及到字段上若存在索引，则该索引被列出，但是不一定实际使用

Key:实际使用的索引，如果为null则没有使用索引
查询中若使用了覆盖索引，则该索引出现在key列表中

Key_len:表示索引中使用的字节数，可以通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
Key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即Key_len是根据表定义计算而得，不是通过表内检索出的

Ref: 显示索引的那一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值
Rows: 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra:

• Using filesort:说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取；MySQL中无法利用索引完成的排序操作称为“文件排序”（需要调整）

• Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by（需要调整）

• Using index:表示相应的select操作中使用了覆盖索引（covering index），避免访问了表的数据行，效率不错！

  如果同时出现using where，表明索引被用来执行索引键值的查找；
  如果没有同时出现using where，表明索引被用来读取数据而非执行查询动作
  覆盖索引（covering index）：
  就是select的数据列只从索引列中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，
  而不必根据索引再次去读取文件即 **查询列要被所建的索引覆盖**
  

• Using where：用where过滤

• Using join buffer：使用了连接缓存

• Impossible where：where子句的值总是false，不能使用来获取任何元祖

• Select table optimized away:

• Distinct:

注意：

• 当多表联查时left join 索引加在右边表上；right join 索引加在左边表上；
• 尽量可能减少join语句中的nestedLoop的循环总次数；“永远用小结果集驱动大的结果集”
• 优先优化nestedLoop的内层循环：保证join语句中被驱动表上join条件的字段已经被索引
• 无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，可以把joinbuffer设置大点

6.3创建索引的原则

1.索引失效的原因：
2.全职匹配我最爱；
3.最佳左前缀法则，中间兄弟不能断（查询从索引最左前列开始 并且不跳过索引中的列）；
4.不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描；
5.存储引擎不能使用索引中范围条件右边的列；
6.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*
7.MySQL在使用不等（！=或者<>）的时候无法使用索引导致全表扫描
8.Is null， is not null 也无法使用索引
9.Like以通配符开头(‘%abc…’)MySQL索引失效会变成全表扫描的操作（like%加右边，两边加%不失效，可以用覆盖索引解决）
10.字符串不加单引号索引失效
11.少用or，用它来连接时会索引失效

定值、范围还是排序，一般order by 是给个范围
Group by 基本上都需要进行排序，会有临时表产生

6.4一般性建议：

• 对于单键索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
• 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

6.5优化总结口诀

优化总结口诀：
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or, 索引失效要少用。
Group by
Group by实质是先排序后进行分组，遵照索引的最佳左前缀
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort+buffer_size参数的设置
Where高于having，能写在where限定的条件就不写在having限定里

7.慢SQL

默认情况下，MySQL的数据库没有开启慢查询日志，需要我们手动来设置参数；如果不是调优的话，一般不建议开启；因为开启慢查询日志会给性能带来一定影响。慢查询日志支持记录写入文件
默认关闭
Show variables like ‘%slow_query_log%’

7.1开启慢日志配置

使用 set global slow_query_log = 1
开启了慢查询日志只对当前数据库生效，如果MySQL重启则会失效

如果要永久生效，就必须修改配置文件my.cnf,[mysqld]下增加或修改参数
Slow_query_log和Slow_query_log_file后，然后重启MySQL服务器；
Slow_query_log = 1
Slow_query_log_file = /var/lib/mysql/atguigu-slow.log

查看阙值命令：show variables like ‘long_query_time%’;
Set global long_query_time = 3；
也可以在my.cnf参数中修改
假如运行时间刚好等于long_query_time 的情况并不会记录下来。

函数：有返回值
存储过程：没有返回值

8.MySQL锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制
Show open tables—查看是否有锁
Lock table tableName read;–读锁
Unlock tables—解锁

8.1表锁（偏读）~MyISAM

特定：偏向MyISAM存储引擎，开销小，枷锁快；无死锁；锁定粒度大，发生锁冲突的概率更高，并发度最低

如果某个表A加read锁；本会话以及其他会话 查询数据 没有任何问题，本会话修改本表A记录会报没有解锁；其他会话修改会进入阻塞；本会话修改其他表也显示没有解锁

如果某个表A加write锁；本会话以查询及修改数据没有任务问题，本会话查询其他表显示没有解锁；其他会话查询进入组塞；

MyISM在执行查询语句Select 前，会自动给涉及的所有表加读锁，在执增删改操作前，会自动给涉及的表加锁。

MySQL的表级锁有两种模式：
表共享读锁（table read lock）
表独占写锁（table write lock）

锁类型	可否兼容	读锁	写锁
读锁	是	是	否
写锁	是	否	否

结论：
对myisam表进行操作，会有以下情况：
1对myisam表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。
2对myisam表的写操作（加写锁），会阻塞其他进程对同一表的读和写，只有当写锁释放后，才会执行其他进程的读写操作。
简而言之：就是读锁会阻塞写，但是不会阻塞读，而写锁会把读和写都阻塞；

SQL：show status like ‘table%’；
Table_locks_immediate:产生表级锁定的次数，表示可以立即获取锁查询次数，每立即获取锁值加1；
Table_locks_waited:出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在较严重的表级锁争用情况；

此外myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

8.2行锁（偏写）~innoDB

偏向innoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发也最高
InnoDB与myisam的最大不同有两点：1支持事务（transaction）；2采用了行级锁

8.2.1事务

是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性（ACID）
原子性（Atomicity）:事务是一个原子操作，对数据修改，要么全部执行，要么全都不执行
一致性（Consistent）:事务开始和完成时，数据必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行；这意味着事务处理过程中的中间状态对外是不可见的，反之亦然。
持久性（Durable）：事务完成之后，他对数据的修改是永久的，即使出现系统故障也能够保持。

脏读（已修改未提交）：事务A读到事务B 已修改未提交的数据 ，并且B事务回滚 ，A读的数据是无效的
页锁

例如：用户A向用户B转账100元，对应SQL命令如下
update account set money=money+100 where name=’B’;  (此时A通知B)
update account set money=money - 100 where name=’A’;

当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。

不可重复读（修改数据并提交）：事务A读到事务B已经提交的修改数据 ，不符隔离性

例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。

幻读（新增数据）：事务A读到事务B已经提交的新增数据，不符隔离性

例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，
这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。
而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，
其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

8.2.2事务的隔离级别

读数据一致性及允许的并发副作用隔离级别	读数据的一致性	脏读	不可重复读	幻读
读未提交（read uncommitted）	最低级别，只保证不读取物理上损坏的数据 一个事务可以读取另一个事务未提交的数据	是	是	是
读已提交（read committed）	语句级 一个事务只能读取已经提交的数据，但是在同一事务中，多次读取同一数据可能会得到不同的结果	否	是	是
可重复读（repeatable read）	事务级 一个事务在同一数据上进行多次读取时，可以得到相同的结果，但是在同一事务中，其他事务插入的数据对该事务不可见	否	否	是
串行化（serializable）	最高级，事务级 所有事务串行执行，保证数据的一致性和完整性，但是并发性较差	否	否	否

数据库的事务隔离越远高，并发副作用越小，但是付出的代价也就越大，以为你事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

在MySQL数据库中，支持上面四种隔离级别，默认的为Repeatable read (可重复读)；
而在Oracle数据库中，只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别，其中默认的为Read committed级别

查看当前数据库的事务隔离级别：show variables like ‘tx_isolation’;
关闭自动提交Set autocommit = 0;