数据库复习

SQL语句

查询语句的执行

1. 连接器

根据用户名和密码，连接数据库（进行身份认证与权限验证）

连接命令：

mysql -h$ip -P$port -u$user -p

-p 后回车输入密码，不要直接在命令中写密码，可能会导致泄露！

mysql是客户端工具，完成TCP三次握手，和数据库建立连接。

• 成功建立连接后，即使管理员账号对该用户权限修改，不会影响已经存在的连接权限。新建的连接才会使用新的权限。 Why? 认证通过后，连接器会在权限表中查出所拥有的权限。

2. 查询缓存

MySQL8.0以后去除：查询缓存失效频繁，每次更新表，都会清空所有查询缓存

​ 系统配置表（静态表，长时间不需要更新）==> 可以使用

3. 分析器

• 词法分析：提取关键词，表名，字段名，查询条件等
• 语法分析：判断是否符合MySQL语法

4. 优化器

• 一个表中多个索引，决定使用哪个
• 一个语句中有join(多表关联)，决定各个表的连接顺序

select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

既可以先从t1中取出c = 10的值的记录的ID值，再通过ID值关联t2；

也可以先从t2取。

两种执行方式逻辑相同，但效率不同

5. 执行器

一条SQL语句执行很慢的原因：

• 大多数情况下正常，偶尔很慢

  • 数据库在刷新脏页，redo log写满了，需要同步到磁盘，不能再等待空闲同步

  • 执行时遇到锁。查看当前状态：

    show processlist
    

• 一直都很慢

  • 没用到索引

    • 字段没有索引

    • 字段左边进行了运算，从上面修改成下面语句

      select * from t where c - 1 = 1000;
      select * from t where c = 1000 + 1;
      

    • 字段左边函数操作

  • 数据库不走索引，选择全表扫描

    一个索引上不同的值越多，区分度越高，区分度也叫基数，基数越大，符合查询条件的记录数越少，走索引查询效率越高。系统通过采样预测索引的基数。

    ==> 可以通过强制索引方式查询

    select * from t force index(a) where c > 100 and c < 1000;
    

1. delete, truncate & drop

• drop: DDL
  • can be used to delete databases, tables, views, etc
• truncate: DDL
  • ==> DDL cannot be rolled back
• delete: DML
  • used to delete existing records from an existing table
  • returns the number of records that were deleted by its execution

2. 内连接 vs 外连接

左连接：返回包括左表所有记录和右表中连接字段相等的记录

右连接：返回包括右表所有记录和左表中连接字段相等的记录

全外连接：返回左右两表所有记录

内连接：仅返回值相等的记录

PrepareStatement

索引

Hash表 & 有序数组 & B+树

1. hash表：适用于只有等值查询的场景（hashcode）（NoSQL引擎如Memcached）

2. 有序数组：适用于等值查询 & 范围查询，只适用于静态存储引擎，如 保存2020年某市所有人口信息（查询效率高，但更新数据成本高）

3. B+树：从B-树改进。层级少，速度快，天然有序，全节点遍历快。非叶节点只保存关键字key，不存记录的数据地址；所有数据地址都在叶节点 --> 可以存更多的关键字。

   索引不止存在内存中，还需要写入磁盘。100万个节点的平衡二叉树，树高20，一次查询访问20个数据块。为了让一次查询尽量少读盘，必须让查询过程访问尽量少的数据块。 ==> 使用N叉树。InnoDB的一个整数字段索引，N差不多为1200

   读写性能优，访问模式适配磁盘

InnoDB 索引模型

1. 索引组织表

表根据主键顺序以索引形式存放 ==> 推荐设置一个自增主键（插入新数据=追加操作=>不需要挪动其他记录，也不触发分裂）

​ 每个索引在InnoDB中对应一棵B+树

2. 索引类型

• 主键索引（聚簇索引clustered index）： 主键查询方式， 则只需要搜索主键这棵B+树；
• 非主键索引（二级索引secondary index）：普通索引查询方式， 则需要先搜索普通索引的索引树， 得到主键的值， 再到主键索引树搜索一次。 这个过程称为回表。

3. 索引维护

增/删：分裂/合并

​ ==> 自增主键

注意事项

1. 尽量使用主键查询

2. 主键长度越小， 普通索引的叶子节点就越小， 普通索引占用的空间也就越小。

覆盖索引

索引中包含需要查询的字段的值

select ID from T where k between 3 and 5;

id的值已经在k的索引树上，所以可以直接提供查询结果，不需要回表

查询优化

1. 减少请求的数据量

• 只返回必要的列：最好不使用select *
• 只返回必要的行：使用limit限制返回的数据
• 缓存重复查询的数据？

2. 减少服务器端扫描的行数

• 使用索引

主从复制 & 读写分离

1. 主从复制

三个线程：

• binlog：将主服务器的数据更改，写入二进制日志中
• I/O：从主服务器上读取二进制日志，写入从服务器的中继日志中
• SQL：读取中继日志，并重放sql语句

2. 读写分离

主服务器处理写操作 & 实时性要求较高的读操作；

从服务器处理读操作（从服务器可以使用MyISAM存储引擎，提升查询性能，节约系统开销）。

RR级别下如何防止幻读

通过多版本并发控制（MVCC）+间隙锁（Next-Key Locking）