一、RDBMS和非RDBMS：
1. RDBMS(关系型数据库)：
- 把复杂的数据结构归结为简单的二元关系，建立在关系模型基础上的数据库，表与表之间的数据记录有关系。
2. 非RDBMS(非关系型数据库)：
- 基于键值对(不只有键值对)存储数据，性能非常高。
二、存储引擎：
1. InnoDB：
-
InnoDB崩溃后可安全恢复
-
InnoDB是mysql的默认事物引擎，被设计用来处理大量的短期事务
-
InnoDB是为处理巨大数据量的最大性能设计
-
.frm：表的定义文件， .ibd：表的数据文件
2. MyISAM：
-
MyISAM崩溃后无法安全恢复
-
优势是访问速度快，对事物完整性没有要求或者以SELECT、INSERT为主的应用
-
.frm：存储表结构, .MYD：存储数据, MYI：存储索引
| 对比项 | MyISAM | InnoDB |
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 | 行锁，操作时只锁某一行，不对其他行有影响，适合高并发的操作 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性影响 |
| 自带系统使用表 | Y | N |
| 关注点 | 性能：节约资源、消耗少、业务简单 | 事务：并发写、事务、更大资源 |
| 默认安装 | Y | Y |
| 默认使用 | N | Y |
3. Archive：
-
用于数据存档，仅支持插入(性能很高)和查询(性能较差)操作。拥有很好的压缩机制，使用zlib压缩库
4. Blackhole：
-
丢弃写操作，读操作会返回空内容。服务器会记录Blackhole表的日志，可用于复制数据到备库或者简单的记录到日志
5. CSV：
-
存储数据时，以逗号分隔各个数据项
6. Memory：
-
置于内存的表，同时支持哈希索引(默认)和B+索引
三、索引设计原则：
1. 适合创建索引：
- 字段的数值有唯一性的限制
- 频繁作为WHERE查询条件的字段
-
经常GROUP BY 和 ORDER BY的列
- UPDATE、DELETE 的 WHERE 条件列
- DISTINCT 字段
- 多表 JOIN 连接操作， WHERE 条件 或是 用于连接的字段
- 列的类型小的创建索引
- 使用字符串前缀创建索引
- 区分度高(散列性高)的列
- 使用最频繁的列放在联合索引的左侧
- 多个字段都要创建索引的情况下，联合索引优于单值索引
2. 不适合创建索引：
- 在 WHERE等条件中使用不到的字段
-
数据量小的表不要使用索引
- 有大量重复数据的列
- 避免对经常更新的表创建过多的索引
- 不建议用无序的值作为索引
- 删除不再使用或者很少使用的索引
- 不要定义冗余或重复索引
四、EXPLAIN分析语句：
| 列名 | 描述 |
| id | 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id |
| select_type | SELECT关键字对应的那个查询类型 |
| table | 表名 |
| partitions | 匹配的分区信息 |
| type | 针对单表的访问方式 |
| possible_keys | 可能用到的索引 |
| key | 实际用到的索引 |
| key_len | 实际用到的索引长度 |
| ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 |
| rows | 预估的需要读取的记录条数 |
| filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
| extra | 一些额外的信息 |
1. type：
-
当表中只有一条记录，并且该表使用的存储引擎的统计数据是精确的，对该表的访 问方法是system
- 当根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法是count
- 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方法进行 访问，则对该被驱动表的访问方法是eq_ref
- 当通过普通二级索引列与常量进行等值匹配来查询某个表，对该表的访问方法可能 是ref
- 当对普通的二级索引进行等值匹配查询，该索引列的值可能为NULL，那么对该表的 访问方法是ref_or_null
- 单表访问方法时，在某些场景下可以使用’Intersection’、’Union’、’Sort-Union’这三种 索引合并的方式进行查询，该表的访问方法为index_merge
-
针对一些包含’IN’子查询的查询语句中，如果查询优化器决定将’IN’子查询转换 为’EXISTS’子查询，而且子查询可以使用到主键进行等值匹配的话，该子查询的访问方 法为unique_subquery
- 使用索引获取某些范围区间的记录，该表的访问方法可能是range
-
当可以使用覆盖索引，但需要扫描全部的索引记录时，该表的访问方法为index
- 全表扫描为all
效率依次递减
2. extra：
-
当查询语句没有FROM子句时，会显示No table used
- 当WHERE子句永远为FALSE时，会显示Impossible WHERE
-
全表扫描来执行对某个表的查询，并且WHERE子句中有针对该表的搜索条件时，会 显示Using where
-
使用索引访问来查询表(select *)，若WHERE子句仅包含索引列的查询条件，会显示 NULL
- 当查询语句有MIN或MAX聚合函数，但是没有符合WHERE字句中的搜索条件的记 录时，会显示 No matching min/max row；存在记录会显示Select tables optimized away
- 当查询列表(select id)及搜索条件中只包含属于某个索引的列，会显示Using index
- 索引条件下推会显示Using index condition
- 在连接查询中，被驱动表不能有效的利用索引加快访问速度，mysql一般会为其分 配一块’join buffer’的内存块来加快查询速度(基于块的嵌套循环算法)，会显示Using join buffer(hash join)
- 在未使用索引的排序中，只能在内存中(记录较少)或者磁盘中(记录较多)进行排序， 此时会显示Using filesort
- 在许多查询的执行过程中，mysql可能会借助临时表来完成一些功能，例 如’DISTINCT’、’GROUP BY’、’UNION’等子句，如果不能有效利用索引来完成查询，mysql 可能寻求通过建立内部的临时表来执行查询，此时会显示Using temporary
五、优化案例：
1. 深翻页问题：
-
查询过程中，若不是对主键进行查询，mysql会先走二级索引，获取结果后回表查询。而深翻页过程中，前面页码也会回表但之后数据会被丢弃，回表IO代价大，因此可以通过覆盖前面索引，只取需要的索引进行回表，随着数据量的增多，该优化效果愈发明显。其中，二级索引获取到的值只需取表中存在的(大部分情况下都存在id)，也可以避免一次回表。
-
例： select * from (select id from order_info where period = 202207 order by modified desc limit 99000, 1000) as temp join order_info where temp.id = order_info.id;
2. 未走最优索引问题：
-
查询过程中，存在二级索引，但mysql不一定会选择最优方式(视情况而定，可能走其他条件索引，或是走全表扫描)，此时可以通过force index去强制索引。
-
例：select * from order_info force index (idx_period) where period between 202205 and 202208 order by modified;
3. 多条件查询问题：
-
查询过程中，多个条件会一次走索引，期间会回表，此时可以增加联合索引，mysql会执行索引下推，减少不必要IO。由于联合索引需要存储更详细的数据，因此会消耗更多存储空间。
-
例：alter table order_info add index idx_period_phone(period,phone);
- Select * from order_info force index(idx_period_phone) where period > 202207 and phone > ‘997576’;
六、锁：
1. 表锁：
1）S、X锁：
| 锁类型 | 自己可读 | 自己可写 | 自己可操作其他表 | 他人可读 | 他人可写 |
| 读锁 | 是 | 否 | 否 | 是 | 否，等 |
| 写锁 | 是 | 是 | 否 | 否，等 | 否，等 |
2）意向锁(intention lock)：
-
意向锁可理解为在进行行锁之后，对表锁做一个标记，并不是真锁
| 意向共享锁(IS) | 意向排它锁(IX) | |
| 意向共享锁(IS) | 兼容 | 兼容 |
| 意向排它锁(IX) | 兼容 | 兼容 |
| 意向共享锁(IS) | 意向排他锁(IX) | |
| 共享锁(S) | 兼容 | 互斥 |
| 排它锁(X) | 互斥 | 互斥 |
3）自增锁(AUTO-INC)：用于自增字段
4）元数据锁(MDL)：每执行一条DML、DDL语句时都会申请metadata锁，DML操作需要metadata读锁，DDL操作需要metadata写锁，metadata加锁过程是系统自动控制
2. 行锁：
1）记录锁(Record Locks)：仅对该条记录上S或X锁
2）间隙锁(Gap Locks)：对查询未命中的记录时，会对该记录id的上下界开区间进行加锁(不区分S、X锁)，不允许添加数据以解决幻读问题，但易出现死锁
3）临键锁(Next-Key Locks)：本质上是对记录锁与间隙锁的合体，既保护了该条记录，又能阻止别的事务将新纪录插入被保护记录的前边
4）插入意向锁：执行插入操作时，总会检查当前插入操作的下一条记录（已存在的主索引节点）上是否存在锁对象，判断是否锁住了gap，如果锁住了，则判定和插入意向锁冲突，当前插入操作就需要等待，也就是配合上面的间隙锁或者临键锁一起防止了幻读操作
3. 页锁：开销介于表锁和行锁之间，会出现死锁
4. 乐观锁：适合读操作多的场景，相对来说写的操作比较少。优点在于程序实现，不存在死锁问题，但是阻止不了除了程序以外的数据库操作
5. 悲观锁：适合写操作多的场景，因为写操作具有排他性。可以从数据库层面阻止其他事物对该数据的操作权限，防止”读-写”和”写-写”冲突，但是并发性差
1203
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
