一步一步学 Mysql

关系型数据一般学习什么？

一、如何设计一个关系型数据库

1. 存储管理 ：需要对数据的格式以及文件的分割进行统一管理 便设计到了存储管理
2. 缓存模块 ： 为了更快更好优化程序 使用缓存机制 把下一次需要的数据直接存储到缓存中
3. SQL解析模块： 对SQL进行编译解析 供机器可读
4. 日志管理模块 ： 对操作做记录
5. 权限划分模块 ： 每个职位对应权限
6. 容灾机制 ：处理异常情况 需要异常机制 出现异常恢复方式
7. 索引模块
8. 锁模块
   

二、为什么要使用索引

避免全表扫描，提高查询效率
索引的介绍：

• 索引：将无序的数据变成相对有序的一种数据结构
• 索引目的：高效的查找得到我们所需要的数据，减少分组和排序时间，提高我们的mysql的性能

三、索引相关的数据结构

二叉查找树：
效率： log(n) 复杂度
缺点：
1.如果按照1、2、3、4、5等按顺序的插入 查询复杂度为 O(n)，大大增加了查找小路
2.树的深度越深，IO次数越多

B Tree 树

B + Tree 树
B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构。B+Tree与B-Tree的结构很像，但是也有几个自己的特性：
1、所有的非叶子节点只存储关键字信息。
2、所有卫星数据（具体数据）都存在叶子结点中。
3、所有的叶子结点中包含了全部元素的信息。
4、所有叶子节点之间都有一个链指针。

索引的性质
1.索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，
磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，
即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，
一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

2.索引的 最左匹配 特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，
最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，
必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，
所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

优点：

• B+ 树的 IO 次数少，磁盘读写的代价更低
• B+树的查询效率更加稳定
• B+树更加有利于对数据库的扫描

Hash 索引
缺点：

1. 仅仅能满足 = in 不能使用范围查询
2. 无法用来进行排序
3. 无法进行组合查询

密集索引（聚集索引）和稀疏索引的区别

**聚集索引：**有且只有一个聚集索引，一般都是主键
innodb存储引擎：有且只有一个密集索引。密集索引的选取规则如下：

1. 若主键被定义，则主键作为密集索引
2. 如果没有主键被定义，该表的第一个唯一非空索引则作为密集索引
3. 若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引）
4. 非主键索引存储相关键位和其对应的主键值，包含两次查找
   

如何定位并且优化慢sql

1. 慢日志定位慢查询 sql
   慢日志记录 会保存 慢 sql 和查询时间

show variables like '%query_log';
-- 开启慢查询日志
set GLOBAL slow_query_log = on;
-- 设置慢查询时间
set GLOBAL long_query_time = 1;

2. 使用 explain 等工具分析 sql
详细请看 EXPLAIN用法和结果分析

-- 如果表中的数据很多
explain select count(id) from person_info_large;
-- 注意：这里它不是走id（主键索引）聚集索引，因为数据量大 查询优化器会选择其他索引，不需要获取数据所以走稀疏索引 
select count(id) from person_info_large force index(primary)

3. 修改 sql 尽量走索引

联合索引的最左匹配原则的原因

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而 最左前缀原则 指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下

select * from user where name=xx and city=xx ; // 可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引  

总结：

1.最左匹配原则，非常重要的原则。mysql会一直向右匹配直到遇到范围查询( > < between like )就停止匹配。比如 a =3 and b = 4 and c > 5 and d = 6 如果建立（a,b,c,d）顺序的索引，d 是无法使用索引的。把顺序改为 a =3 and b = 4 and d = 6 and c > 5
2.= 和 in 可以乱序， a =3 and b = 4 and d = 6 and c > 5 其中 abc 可以随意顺序，mysql的查询优化器会帮助优化成索引可以识别的形式。

Mysql的那些锁

MySQL锁概述

MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
表级锁： 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁： 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

MySIAM 存储引擎

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock），表独占写锁（排它锁）（Table Write Lock）

• 对于MyISAM表的读操作，不会堵塞其他用户对同一表的读请求，但会阻塞对同一表的写请求，
• 对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；

当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

-- 获得读写的锁
lock tables table_name write|read
--释放锁
unlock tables table_name write|read
在这里插入代码片

注意：MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行更新操作 （update、delete、insert等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给MyISAM表显式加锁。

--排它锁 select
select * from user where id = '1024' for update'

--读锁
select * from user where id = '1024' lock in share mode;

InnoDB

支持行级锁 =======> 与索引有关，用到索引
当时 where 后面不走 非索引 的时候 ==========> 是表级锁
InnoDB还支持共享读锁（IS），排它写锁（IX）

数据库事务的四大特性

ACID

1. 原子性 => 要不全部执行，要不全部失败回滚。
2. 一致性 => A 1000元 转账给 B 1000元，AB总钱数应该一直为2000元。
3. 隔离性 => 多个事务并发执行的时候，一个事务不应该影响其他事务的执行。
4. 永久性 => 一个事务被提交，永久不可回滚的修改。

事务并发访问引起的问题以及如何避免

• 脏读 - 一个事务可以读取另一个事务未提交的数据 - READ-UNCOMMITTED 读未提交

sessioin1：

start transaction;
-- 原始账户 1000 消费 100 元
udpate account_innodb set balance = 1000 - 100 where id =1;
-- 还未提交

sessioin2：

start transaction;
select * from account_innodb  where id =1;
-- 此时读取未提交的数据 900元 
update account_innodb set balance = 900 + 100 where id =1
-- 但是 1 还未提交 若session1 回滚到 1000 就造成脏读了

• 不可重复读 - READ-COMMITTED 读已提交
  这里的读已提交的 只是如果 sesson1 还没有没有提交 session2就读原始表的数据。

sessioin1：

start transaction;
-- 开始有 1000 余额
-- 存入 300
udpate account_innodb set balance = 1000 + 300 where id =1;
-- 还未提交

sessioin2：

start transaction;
-- 读已提交 但是 1还未提交 2查找的时候 账户余额还是 1000 元
select * from account_innodb  where id =1;
-- 1 提交后 再次查找 变成 1300 

• 幻读 - READ-COMMITTED 可重复读

幻读主要是 session 1 更新
session2 此时 新增 或者 删除 一条数据
最终 session 1 发现 更新的少了 或者多了

RR级别通过 gep锁保证了幻读

相关的语法 书写

以学生 课程 成绩表为例

Group By

常见的聚合函数
1、求个数/记录数/项目数等：count()
2、求某一列平均数 :avg()
3、求总和，总分等：sum() --必须为数字列
4、求最大值，最高分，最高工资等：max()
5、求最小值，最低分，最低工资等：min()
6、 count_big()返回指定组中的项目数量。

查询所有同学的学号，选课数，总成绩

select student_id,count(course_id),sum(score)
from score
group by student_id

查询所有同学的学号，学生名，选课数，总成绩

select s.student_id,stu.name,count(course_id),sum(score)
from score s, student stu
where s.student_id = stu.student_id
group by student_id

注意：

1. where 一定要写在 group by 前面
2. group by 的 select 要不就是本表的 group by 中的列，要不就是带有函数的列 其他表的列不影响

Having

• 通常与 Group by 子句使用
• where 是用来过滤行 而 having 使用来过滤列的
• 出现统一 sql 时，顺序： where > group by > having

查询平均成绩大于60分的学生学号和平均成绩

select  student_id , avg(score)
from score
group by student_id
having avg(score) > 60

查询没有学全课程的学生的学号 和 姓名

select stu.student_id,stu.name
from student stu, score s
where stu.student_id = s.student_id
group by s.student_id
having count(*) < ( select count(*) from course )