MySQL基础篇

数据库基础知识

以MySQL为基础

---

1. 数据库事务 ：数据库中一组原子性的SQL操作，彼此状态一致。具有ACID特性。

2. 事务 ACID 特性：

   • 原子性：数据库事务是一个整体，其中的SQL操作要么全部提交成功commit要么全部失败回滚rollback，不可分割；
   • 一致性：与原子性有联系。事务总是从一个一致状态转换到另一个一致状态；
   • 隔离性：事务之间彼此互不影响，一个事务在提交之前，对其他事务是不可见的。
   • 持久性：一个事务一旦提交成功，他所做的修改就会永久性的存储在数据库中。

3. MySQL 4 种隔离级别

   • 未提交读READ UNCOMMITTED：一个事务在提交之前，对其他事务是可见的，即事务可以读取未提交的数据。存在“脏读”（读到了脏数据）问题；
   • 提交读READ COMMITTED：事务在提交之前，对其它事务是不可见的。存在“不可重复读”（两次查询的得到的结果可能不同，即可能在查询的间隙，有事务提交了修改）问题。解决了“脏读”问题。
   • 可重复读REPEATABLE READ：在同一事务中多次读取的数据是一致的。解决了脏读和不可重复读问题，存在“幻读”（在事务两次查询间隙，有其他事务又插入或删除了新的记录）。--- MySQL默认隔离级别。
   • 可串行化SERIALIZABLE：强制事务串行化执行。即一个事物一个事物挨个来执行，可以解决上述所有问题。

4. 锁及粒度：

   • 共享锁/读锁：互不阻塞，优先级低
   • 排他锁/写锁：阻塞其他锁，优先级高，即确保在一个事务写入时不受其他事务的影响。
   • 锁粒度：锁定的数据量越少（粒度越小），并发程度越高，但相应的加锁、检测锁、释放锁用的系统开销也随之增大。
   • 锁策略：锁开销与数据安全性之间的平衡
     • 表锁：锁住整张表，读锁互不阻塞，写锁阻塞其他所有读写锁（同一张表）。开销最小。
     • 行级锁：对每一行数据（记录）加锁，开销大，并发程度高。

5. InnoDB对死锁的处理：此处死锁与OS死锁类似，多个事务互相持有对方所有要申请资源的锁不释放，造成环路死锁。MySQL InnoDB引擎检测到死锁循环依赖后，回滚持有最少行级锁的事务。

6. 索引及其作用和实现方法：

   • 概念：对数据库表列进行增加恰当索引，可以快速的找到匹配的记录行数，相比于默认的全表扫描，可以大大加快查找的速度。
   • 作用：加快查找速度；
   • 实现方法：一般分为B+树索引和哈希索引。
     • B+树索引：在B-tree上改进得到，其非叶子节点均为key值，叶子节点是key-data键值对。叶子节点前后相连且有序。
     • 哈希索引：通过对key进行hash(crc/MD5/sha1/sha256...)而将记录存储在不同的bucket种，可以做到常数时间的查找，但要注意哈希冲突的避免（链表法、线性探测、二次探测、公共溢出区的方法）。其中MD5 128位，和sha1/256码都较长不太适合作为hash函数。默认无序。
     • 为什么有了B+树索引还要hash索引？
       1. B+树默认有序，hash默认无序，所以哈希索引无法用于排序；
       2. 哈希索引O(1)在速度上毋庸置疑要快于B+树近似O(logn);
       3. 哈希索引只能进行等值查询（因为他要计算hash(key)再去匹配）而B+树索引可以进行等值、部分前缀、范围查询；
       4. 底层实现结构不同：B+树是非线性结构，hash桶是线性结构。
       5. 对于某些场景如热点页/活跃查询页，需要借助哈希索引来实现快速查询。
   • 索引越多越快？
     此言差矣，索引并非是虚无缥缈的，是实实在在的一种数据结构（B+树/hash桶）要占内存、维护它要系统开销，一般的插入删除都要进行结构的调整，这要消耗时间，所以索引太多反而拖慢查找时间。有时候，见数据量不多时，建立索引还不如全表查询。索引加快了检索的速度，但是插入删除修改都需要DBMS动态更新内部索引结构，要耗费开销。

7. InnoDB MVCC
   多版本并发控制，是为了避免加锁而实现的。一般的实现方法是存储快照来实现的。InnoDB实现方式是在记录后添加两个隐藏列（表项），分别是事务创建时间、过期时间，存储的实际上是系统版本号（系统版本号随着事务的创建而递增）。
   这样一来，INSERT 时加上开始版本号，UPDATE/DELETE时加上过期版本号，这样一来在SELETE时，就只访问开始系统版本号小于当前的事务的版本号、过期时间要么未定义要么在当前版本号之后的记录，这样就可以保证：访问的记录是在本事务开始前就存在而且在本事务期间没有过期（被删除或被修改过的）。可以避免脏读、不可重复读、幻读的问题。（个人觉得）

8. MySQL存储引擎简介

   • InnoDB,最为通用/推荐的一种引擎，支持事务、行级锁、甚至间隙锁（避免幻读）、支持热备份，MVCC，在并发上占优势，系统资源占用多。
   • MyISAM,默认的存储引擎，不支持事务和行级锁，只支持表锁，某些场景性能很好：占用存储上优，查询速度上完胜（大概是InnoDB的3倍）系统资源占用少。
   • InnoDB支持事务, MyISAM不支持；
   • InnoDB支持行级锁、表锁；MyISAM只支持表锁；
   • InnoDB支持MVCC，MyISAM不支持；
   • InnoDB不支持全文索引，MyISAM支持；
   • InnoDB支持外键，MyISAM不支持外键；
   • InnoDB和MyISAM都支持B+树索引，InnoDB还支持自适应哈希索引
   • MyISAM实现了前缀压缩技术，占用存储空间更小（但会影响查找），InnoDB是原始数据存储，占用存储更大。

   PS：大部分情况下，InnoDB都是正确的选择。---《高性能MySQL》

9. SQL优化

   • 在经常性的检索列上，建立必要索引，以加快搜索速率，避免全表扫描（索引覆盖扫描）；
   • 多次查询同样的数据，可以考虑缓存该组数据；
   • 审视select * form tables, 你需要所有列数据吗？
   • 切分查询（大查询切分成为小查询，避免一次性锁住大量数据）
   • 分解关联查询（单表查询，结果在应用程序中进行关联，可以减少处理过程中的锁争用）
   • 尽量先做单表查询；
   • ...

10. profile 的作用和用法
    用于保存SQL语句执行状态，需要手动开启，才可以查看。

set profiling = 1; 开启
show profiles; 显示SQL查询的profiles概况
show profile all for query X; 查看第X条语句的所有执行情况。
show profile cpu, block io, memory for query X; 查看部分profile信息。

11. MySQL查询的步骤
    • 客户端发送查询到服务器；
    • 服务器检查查询缓存query cache（大小写敏感的哈希查找，常数时间）。如果命中，返回缓存中的结果，否则下一步；
    • 解析语句，生成执行计划；（SQL解析，预处理，优化器生成执行计划）；
    • 根据执行计划，根据存储引擎的不同调用API，执行查询（一棵指令树）；
    • 结果返回客户端。