MySQL笔记

1、文件类型
.frm 表结构
.myd 表数据
.myi 表索引
.logbin 主从复制
.errlog 错误日志

 

2、架构
A、连接层（连接驱动，常用JDBC）
B、服务层（拦截器、分析器、优化器、过滤器）
C、存储引擎（MyIsAm、Innodb）
D、文件服务（磁盘IO操作）

3、数据库查询的执行顺序
from table -> inner/left/right/outer join on -> where -> group by -> having -> select -> order by

 

4、索引
A、索引类型
主键索引、唯一索引、复合索引、单值索引、覆盖索引
B、索引失效原因
最左原则
不在索引列进行函数、运算、类型转换（自动和手动）操作
范围查询后后字段会索引失效，所以避免在复合索引列前面进行or、in、大小比较，会导致后面索引失效
like的%号开始的模糊查询，我们一般采用覆盖索引来解决like的%号开始的模糊查询索引失效问题
order by 索引字段顺序，以及多字段排序asc、desc混用，要看索引创建的顺序是否一致
group by分组
C、慢查询优化
explain
id 查询的执行顺序（越大越先执行）
select_type 查询类型（普通类型、主查询、子查询）
ref 查询级别 system（系统级）->const（常量）->ref_unique（唯一索引）->ref（非唯一索引）->index（索引）->range（范围）->ALL（全表扫描）
possible_keys 可能用到的索引名称
keys 实际用到的索引名称
rows 读取数据扫描的数据行数
ref 用到的索引的列或常量 const,columnName
Extra 查询用到的条件 Using Temporary（使用临时表）、Using Filesort（使用内排序）、Using Where（使用条件查询）、Using Index（使用索引）
D、慢查询日志
设置配置文件，开启慢查询日志，慢查询日志设置扫描慢查询的时间（默认10s）
使用命令查询慢查询时间或者慢查询次数最多的前多少条数据显示

 

5、数据库
特点：ACID
原子性：事务里面的操作要么全部执行要么全部回滚。
一致性：事务前后，状态一致。
隔离性：事务的执行环境是独立的，不受事务外的操作所影响。
持久性：事务提交以后，数据永久存储到磁盘，不受系统错误或崩溃的影响。

事务的隔离级别：
读未提交
读已提交
不可重复读
串行化

脏读：A读到B未提交的数据，读到未提交的数据
不可重复读：B在A重复读之间更改了A读取的数据，数据内容不一致
幻读：A读到B操作前后的数据，数据量不一致

 

6、锁
MyIsAm（偏读的存储引擎、不支持事务）
读锁（共享锁），不阻塞其他进程的读操作，会阻塞写操作
写锁（独享锁），会阻塞其他进程的读写操作，所以该存储引擎适用于偏读的服务
Innodb（适合高并发、支持事务、行级锁）
行锁，不阻塞其他进程的读操作，会阻塞写操作，颗粒度只锁定处理的行（如果where条件没有使用索引可能导致全表扫描，锁住整张表）
表锁
间隙锁：处理数据使用范围，则范围的所有间隙都会上锁，例如select ... where id > 0 and id < 10 for update;就算数据没有id=6的，也无法进行插入操作，或者将其他id更新至该范围中
如何锁定一行
select ... from ...where id = 1 for update;
直到事务提交才解除

 

7、主从复制
从机slave 从主机master拉取主机的binary log二进制日志文件
slave的IO线程会自动拉取master的binary log文件到slave从机中
slave的SQL线程会自动复制从master拉取的binary log文件数据到中继日志中，然后解析执行sql文件，完成复制

A、配置主机master,server_id = 1,配置从机白名单ip和账号配置，开启主从复制
B、配置从机slave,server_id = 2,配置开启主从复制，主线问题可以关闭同步