MySQL 总结

mysql 知识书籍

高性能 mysql
MySQL实战

Mysql 基础架构是怎样的？

客户端首先连接连接器
连接器去数据库缓存文件查询，如果命中，返回结果
如果没有，连接分析器，会分析sql 语句是否有问题，
分析器连接优化器，优化这个sql ,
连接执行器
连接搜索引擎查询数据

mysql 连接器是啥？

连接器负责跟客户端建立连接管理连接
连接的时候会经过TCP握手
然后验证身份

Mysql 的查询缓存是什么？

mysql 拿到sql 语句会先去缓存文件查询是否执行过这个语句
缓存失效很容易，只要对表做修改，缓存会清空
mysql 8 取消了缓存

事务四个特性

1.原子性
2.隔离性
3.持久性
4.一致性

原子性、隔离性、持久性是保证一致性的手段，一致性是目的.

2.mysql 是如何保证原子性操作？

利用 mysql 的 undo log .
undo log 名为回滚日志，是实现原子性的关键。
当事务需要回滚时，能够撤销所有已经执行成功的 sql 语句。

mysql 事务隔离级别有哪些？

msyql 事务的隔离级别有四种：
1：读未提交
一个事务还没有提交，它做的变更，会被其他事务看到
解决脏读问题
2：读已提交
一个事务只有在提交后，他做的变更，才会被其他事务看到
3： 可重复读
一个事务启动时看到的结果，跟执行过程中看到的时一样的
4： 串行化
对于读会加读锁，写会加写锁，出现读与写冲突时，后面事务会等待当前事务执行完毕

mysql 隔离级别解决哪些问题？

1： 脏读
2：不可重复读
当前事务还没提交，读取了其他事务对该数据的修改结果
3：幻读
相同的查询条件查询到其他事务新增的结果

如何设置事务隔离级别？

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
//等级就是上⾯的⼏种

如何启动事务？

显式启动
begin 或者 start transaction
配合 commit 或者rollback

怎么查看事务隔离级别？

show variables

事务的隔离级别和 MVCC 的关系是怎样的？

MVCC 是多版本并发控制
是 MySQL innodb 中实现的提高读写并发性能的，用更好的方式去处理读写冲突，即时又并发读写，也能做到不加锁，非阻塞读写。

这里的读是快照读。

msyql 索引 有哪些数据结构

hash 和 B +TREE

如何理解 hash 索引？

hash 表的特点是可以快速查询，但是不支持范围查询
如果使用hash 索引，那么就会在查找范围时，全表扫描

hash 索引在哪些场景可以使用？

比如 redis memcache

二叉树索引为什么不推荐？

索引不只是存在与内存中，最后还是要持久化到磁盘上，二叉树，如果树的高度高，查询成本会很大

B tree 做索引为什么不推荐选用B+ Tree？

B TREE 是对二叉树做了节点在横向上的扩容，这样树的高度降低了
原因是 B tree 一个节点可以存储多个元素

同样的元素存储，B + Tree 要比B Tree 矮一点，原因在于B+ Tree 中非叶子节点会冗余一份在叶子节点中，而且叶子节点之间用指针相连

B+ Tree 的优点是什么？

支持范围查找，树的高度降低，查询效率更高，就是叶子结点通过指针链接的

主键索引和普通索引的查询过程？

主键索引是直接查询叶子节点，普通索引先查询非叶子结点，找到主键，然后再用主键找到那条数据，这个过程叫回表

什么是覆盖索引？

覆盖索引可以减少树的搜索次数，提升性能
很多联合索引的建立就是为了支持覆盖索引
使用select 时候，直接从索引树查询，不用通过数据库查找，也就是说查询的列被索引覆盖。

索引的最左匹配原则是什么？

全值匹配，优化器会进行优化，走索引
包含最左边的列并且是连续的，不管又没有顺序，会走索引
包含最左列，不连续的，走索引但是是回表
不包含最左边的列，不走索引

索引只能用来查询key 是否存在或者相等，对于范围查找，比如：
betwee > < like 就不能进一步的匹配了。遇到范围就不再匹配
如果有：
a b c d 为联合索引
a= 1 and b = 2 and c >3 and d = 1
abc会走索引

索引使用要注意的有哪些？

索引列不能参与计算
在经常搜索的列创建索引

唯一索引和普通索引的选择？

唯一索引就是这张表只有一个这样的索引
普通索引可以存在多个
innodb 在更新数据页的时候，如果这个数据页在内存中有，就直接更新，如果没有，在不影响数据一致性的前提下，将这些数据页缓存在 change buffer 中，change buffer 在内存中有拷贝，也会被写入磁盘中，mysql 定期会有线程进行mergae 操作，以保证数据一致性。断电或者重启，也会执行mreage
普通索引支持 change buffer, 唯一索引不支持
对于写多读少的业务，最好使用普通索引，比如账单类，日志类
对于读多写少的业务，采用唯一索引

如何对很长的字段类型做索引？

可以对该字段hash 保存在一个字段中，每次校验hash 就行了
SELECT rank FROM tableName WHERE url_hash=hash(“www.baidu.com/1/2”) AND url=“www.baidu.com/1/2”
或者做前缀索引

mysql 调优如何做？

首先排除数据库缓存的干扰，在 mysql 8 之前，mysql 支持缓存
，因为存在缓存，执行sql 无论如何都是执行很快的
当然第一次执行慢，使用 noCache 排除缓存干扰
其次，借助 explain 查看执行计划去分析
或者 force index 强制
或者使用覆盖索引减少回表查询

explain 主要有哪些字段？

• type ： 表明sql 效率的级别是否用到了索引
  一般有 null ，all 是不走索引的，system 和const 是走索引的
• extra : 额外的信息说明
  extra 一般会有 using where 说明使用了where 条件过滤 ，一般在where 后面的字段加索引
  如果是 using index , 说明sql 所需要的字段都在一棵索引树上，无序访问实际的行记录，这类语句性能比较好
  如果是 using index condition ,说明确实命中了索引，但不是所有的数据列都在索引树上，还要访问实际的行记录，这类语句性能也比较高，但是不如 using index，支持覆盖索引，不用回表。
  如果是 using filesort ,说明得到结果集，需要对所需记录进行文件排序
  这类sql 性能较差，需要优化
  比如： 在 order by 后面的列没有加索引，就会出现 using fildsort , 索引在order by 后面的字段加索引，避免每次都走全量排序
  如果是using trmporary ，说明查询过程mysql 创建了临时表，可能是做了子查询，修改为join 查询，或者group by 和order by 同时使用，但是作用在不同字段上。
  如果是 using join buffeer, 说明进行了嵌套循环查询，比如两个表做了 join ，关联字段没有建立索引。

数据库设计的三范式

设计数据库时遵守不同的规范要求，这些规范被称为范式。各种规范是递次规范。一般来说数据库只需要满足第三范式就行了。

三范式

• 1NF: 列的原子性，保证字段不可分
• 2NF: 有主键，非主键字段依赖主键
  （例如：订单详情表中有： 订单Id,产品Id,折扣,数量, 产品单价，产品名称等字段，订单ID和产品Id是主键，折扣和数量都是跟主键依赖的字段，产品单价和产品名称只是跟产品Id 依赖，依赖主键的一部分，所以不符合2NF）
• 3NF: 非主键字段必须直接依赖与主键，不能间接依赖主键，就是不能存在传递依赖
  （例如：订单表有：订单id, 订单日期，联系人id，联系人名称，联系人地址。订单id是主键，联系人信息依赖依赖联系人id，不依赖主键订单id，这样就不符合3NF）

mysql 存储引擎

存储引擎是指是数据库底层在增删改查时使用哪些引擎技术。不同的存储引擎提供不同的功能。
mysql 5.5 版本之前默认存储引擎是 myisam , 5.5 之后默认是 Innodb.
myisam 不支持事务和行级锁，只支持表级锁。应用场景：对于修改操作较少的业务和对数据一致性要求不高的业务，推荐使用 myisam,比如：资讯类业务。
innodb 支持事务和行级锁、表级锁，默认支持行级锁

创建表时选取最适用的字段属性

mysql 可以很好的支持大数据量的存取，但是一般来说，数据库中的表越小，执行的查询也越快。因此在创建表的时候，为了更好的性能，尽可能的将字段长度设置的小一点。例如：在定义邮政编码这个字段时，如果使用char(255),显然浪费了数据库空间，因为char(M)是定常字段类型，不够长度的会在右边补齐，但是使用varchar也是不够好的，varchar(M) 是变长字段，总长度是M+1, 多出来的是记录长度的字节。最好使用char(6)就可以了。

使用join 来代替子查询效率高

例如：
select * from
customerInfo
where customerId not in (select customerId from salesInfo )

使用 join 优化 ：
select * from
customerInfo a
left join salesInfo b on a.customerId = b.customerId
where b.customerId is null

join 之所以效率高一些，mysql 不需要再内存中创建临时表来完整这个逻辑上需要两步的操作。

索引分类

主键索引 ：不允许有空值
唯一索引 ：列唯一，允许有空值
普通索引
全文索引
复合索引
聚簇索引
非聚簇索引
对于经常变更的字段，不适合加索引
索引会提高查询效率，但是会降低增删改的效率
create index 索引名 on 表 （字段）

模糊查询

a like “x%” // 走索引
a like “%x%” // 全表扫描
a like “%x” // 全表扫描

索引失效的情况

1. 只要列中包含了 null , 或者复合索引只要有一列为 null,索引都会失效，所以在设计数据库时不要让字段默认值为null，普通索引，查询条件为is null、is not null 索引会失效
2. like 前面有% 会失效，后面有% 不会失效
3. or 前后只要一个使用索引，索引会失效
4. 复合索引没有遵守最左匹配原则
5. 在索引字段上使用 not、<>、！= 索引会失效
6. 索引字段的函数计算
7. 索引字段是字符串，查询时不加单引号，索引会失效

聚簇索引和非聚簇索引

聚簇索引：不是一种索引类型是一种物理索引，是一种数据存储方式，
一个表中只能有一个聚簇索引，在innnodb 就是一张表，innoba 通过主键来聚集数据，如果没有索引，会隐式的创建一个rowid作为聚簇索引。 聚簇索引存储主键和具体数据。
非聚簇索引：二级索引或辅助索引，非聚簇索引引用存储行的主键，通过主键查询聚簇索引相关的数据，这个过程叫回表。

mysql 行级锁、表级锁、锁表

innnodb 支持行级锁、表级锁，默认是行级锁。
行级锁的粒度小，会产生死锁。
表级锁粒度最大，不会产生死锁
死锁原因？
两个或两个以上进程，因争夺资源，而造成的一种互相等待的状态。

having 和 where 区别？

where 可以使用索引，不能使用聚集函数
having 不能使用索引 ，可以使用聚集函数

join 语句区别是什么？

left join : 交集并加上左表的剩余部分
right join ： 交集并加上右表的剩余部分
inner join : 交集