mysql笔记

索引

• 检索效率提高，降低io
• 便于排序，降低cpu
• 占内存——>磁盘便宜
• 增删改时对索引需要维护——>多是查询，增删改少

索引数据结构

索引在存储引擎层实现，不同存储引擎结构不同

• B+树
  二叉树：顺序插入成链表，查询性能极差
  严格平衡二叉树：为平衡需要不断调节，性能消耗
  红黑树（平衡二叉树）：差不多平衡。大数据量层级太深
  B树：多路平衡查找树。一个节点4个key，层级少，data和key在一个node，node太大
  B+树：数据在叶子节点，叶子节点之间有指针形成链表。
• HASH：精确匹配，不能范围查询。innodb有自适应hash，根据B+树自动构建
• R-树：地理位置
• FULL-text：倒排索引，快速匹配文档
  

索引分类

1. 按结构分
   
2. 按作用范围分
   
3. 单列/联合索引：多个查询条件时使用联合索引
4. 前缀索引：字符串的一定长度前缀做索引

# 生成前缀索引
create index idx_xx on tablename(column(n))

前缀长度

查找过程：
根据前缀索引，查找到对应主键
根据主键，获取当前行数据，判断是否是需要的数据
如果不是，遍历下一个前缀索引

5. 覆盖索引： 查询使用了索引，并且需要返回的列在该索引中能全部找到。不适用select *
   回表查询：从二级索引找到主键，从主键找到数据
   

使用规则

1. 最左前缀

   联合索引（a,b,c）

• 如果要使用联合索引，则最左边的列必须存在，即a必须在
  eg：select * from table where a=1;
• 不可跳过某一列
  eg：select * from table where a=1 and c=2; 跳过了b，所以a走索引，c不走，索引部分失效
• 最左边的列存在即可，不要求顺序
  eg：select * from table where b=1 and a=2;
• 范围查询，右边的列失效，使用大于等于，小于等于避免该种情况
  eg：select * from table where a=1 and b>2 and c=3; ab走索引，c不走索引
  select * from table where a=1 and b>=2 and c=3; abc走索引

原理：
将联合索引生成B+Tree排序，以第一列排序，第一列相同，以第二列排序，以此类推
∴a是有序的。在a相同的情况下b是有序的，在b相同的情况下c是有序的，但是在a相同的情况下c不是有序的。
所以不能跳过一列

2. 索引失效

• 索引列上运算，索引失效
• 字符串不加引号，失效
• 模糊查询，尾部不失效，头部失效
• or，两侧列都有索引，索引才生效，一侧有一侧没有，全部失效
• mysql 评估使用索引比全表扫描更慢，则失效

3. 索引提示

• use index(XX) 建议使用xx索引
• ignore index(XX) 忽略xx索引
• force index(XX) 强制使用xx索引

select * from table use index(xx) where id = 2

设计原则

事务

-- 查看自动提交 1为自动 0为手动
select @@autocommit;
# 挂起自动提交
set @@autocommit = 0;
# 提交事务，恢复自动提交
commit
#回滚
rollback
#开启事务，挂起自动提交
start transaction
begin

特性

1. 原子性：出错时返回到事务开始前的状态
2. 一致性：事务执行前后处于一致状态
3. 隔离性：多个事务一起执行（即使使用同一数据），各做各的，不混淆，串行化/序列化请求，好像是系统给定时间内的唯一操作。加锁实现/mvcc（多版本并发控制）
4. 持久性：事务执行完成，对数据的更改是永久性的（写入磁盘），引入日志，记录修改后的数据，避免数据在事务提交前必须写入磁盘的需求，减少I/O

隔离级别

隔离级别	数据丢失	脏读	不可重复读	幻读	锁
读未提交	×	√	√	√	不采用锁
读已提交	×	×	√	√	读不加锁
重复读	×	×	×	√	读写加锁
序列化	×	×	×	×	串行

mysql 默认为重复读，默认将每条语句作为事务处理，每条被自动提交

select @@TRANSACTION_ISOLATION

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [隔离级别]

并发事务的问题

数据丢失：两个写操作，后一个覆盖前一个，前一个数据丢失

脏读：读到别人未提交的数据。A事务修改数据未提交，B事务读取数据，A撤销修改数据，则B读到的数据无效。

不可重复读：A的两次读数据之间，B修改了数据，致使A两次读出的数据不同

解决：行锁和可重复读

幻读：A查找数据，不存在，插入数据，报错，显示已存在，再次查找仍然找不到。因为B插入了数据

解决：MVCC+间隙锁

• mvcc：在读已提交和可重复读这两种隔离级别下的事务，对每一行数据增加版本号，SELECT时访问版本链中的记录。

• 间隙锁：锁的是索引叶子节点的next指针。使用行锁+间隙锁锁住记录避免修改，如果没有索引结构能存储行锁或者间隙锁，则表锁

  • 快照读：select，不加锁。可能读到数据的历史版本，也有可能读到数据的当前版本。通过mvcc来避免幻读。
  • 当前读：插入/更新/删除，需要加锁。通过next-key来避免幻读。锁住某个条件下的数据不能更改。

sql优化

sql性能分析

1. 查看执行频次

# 查看sql执行频次
# 七个下划线，七个字符
show [SESSION | GLOBAL] status like 'Com_______'

2. 慢查询日志

记录在MySQL中响应时间超过long_query_time值的语句

# 开启
slow_query_log=1
# sql执行时间超过，则认为是慢查询，记录
long_query_time=2

3. show profiles

分析当前会话中sql语句执行的资源消耗情况

# 查看是否支持
select @@have_profiling
# 开启
set profiling = 1
# 查看sql耗时等
show profiles
# 查看指定query_id的sql耗时等
show profile for query query_id
# 查看指定query_id的sql的cpu使用等
show profile cpu for query query_id

show profiles

4. explain

①表的读取顺序。（对应id,id的值表示select子句或表的执行顺序，id相同，执行顺序从上到下，id不同，值越大的执行优先级越高）
②数据读取操作的操作类型。（对应select_type,普通查询、联合查询、子查询）
③查询的sql语句好坏。type(从最好到最差依次为：system>const>eq_ref（唯一索引扫描）>ref（非唯一性索引扫描）>range>index>ALL。)
全索引扫描，index和ALL的区别：index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描，但是index是从索引中读取，ALL是从磁盘中读取
④哪些索引可以使用。（对应possible_keys，查询涉及到的字段若存在索引，则该索引将被列出）
⑤哪些索引被实际使用。（对应key）
⑥索引中所使用的字节数。（key_len，索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的）
⑦表直接的引用。（对应ref）
⑧rows
根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数。
⑩Extra（Using index：表明相应的select操作中使用了覆盖索引，避免访问表的额外数据行，索引用来读取数据而非执行查找动作，Using where，表明索引被用来执行索引键值的查找）

sql优化

insert

1. 批量插入
   一条语句插入多条数据（千条）

insert into tablename values (...),(...),(...)

2. 手动事务提交
   一次insert 自动提交一次，事务频繁开启关闭
   改为手动提交，多次insert提交一次
3. 主键顺序插入
4. 大批量插入 load
   

主键

索引组织表：数据根据主键顺序组织存放的表

页分裂

主键乱序插入：在已经满了的第一页需要插入一个数据
此时新申请一页，将第一页的50％数据移动到新的页上，然后添加数据。再改变指针，将新一页插入到原来的第一页和第二页之间

页合并

数据删除时并不会在物理磁盘上直接删除，而是标记其为已删除数据，当一页的数据的50％都被标记则查看该页和前后页是否可以合并

设计原则

order by

创建索引时默认按照索引升序排列
联合索引：多个字段按升序排列
查找时如果都是升序或都是降序则Using index；如果一个升序一个降序则using filesort。可以在建立索引时指定每个字段按升序/降序

group by

索引提高group by效率，索引满足最左前缀法则

limit

limit 200000,10

offset = 200000
count = 10

需要对数据排序，找到offset = 200000的位置，效率低。

优化：覆盖索引+子查询
先对有索引的列进行排序，找到offset = 200000的位置，再查找数据
in里面不能用limit

count

update

行锁针对索引加的而非记录
因此如果没有索引或索引失效则行锁升级为表锁

锁

全局锁

数据备份，所有表锁定，获得一致性视图，保证数据完整性。后续的DML,DDL，更新操作的事务提交的语句均被阻塞

表级锁

1. 表锁

lock tables 表名 read/write
unlock tables/客户端断开连接

• 元数据锁 MDL

元数据：表结构

• 系统自动控制
• 若表存在未提交的事务，则不允许修改表结构
• 对表数据增删改查加MDL读锁。对表结构变更加MDL写锁

3. 意向锁

给表加行锁后，再加意向锁
另一个线程要给表加表锁时，查看意向锁和表锁的兼容情况，如果能兼容则加表锁，如果不能则阻塞
省略了加表锁时对全表的遍历

行级锁

1. 行锁

粒度小，冲突少，并发度高
对索引项加锁

2. 间隙锁/临键锁

间隙锁：锁的是索引叶子节点的next指针。锁住记录避免修改, RC、RR下支持
临键锁（next key lock）：锁的是当前行前面的间隙+当前行。RR下支持

间隙锁可以共存

存储引擎

mysql体系结构

存储引擎

存储引擎：存储数据，查询/更新数据，创建索引等技术的实现方式。基于表，又称表结构，一个库内不同表可以使用不同的存储引擎。
mysql默认innodb

innodb

高可靠、高性能（高效使用内存和cpu）

特点

• 支持事务，四个特性
• 支持外键，保证数据完整性
• 行级锁，表级锁，排它锁，共享锁，意向锁，MVCC。
  锁粒度更小，效率更高

不保存表的行数，查全表，可以查主键以外的索引列（因为index和rowdata在一起）索引单独存放，且有一指针指向主键
主键范围更大
不支持全文索引

逻辑存储结构

• 每张表对应一个表空间文件xxx.ibd，存储表结构，数据，索引
• 缓存数据块， 映射到块再到行
  

架构

内存结构

磁盘结构

事务

redo log

• 记录事务提交时数据页的物理修改，刷新脏页（缓冲池中页数据被修改还未落到磁盘）到磁盘时发生错误，进行数据恢复。实现事务持久性。
  redo log buffer（内存），redo log file（磁盘）。
  每一次事务提交将信息存在redo log buffer，持久化到redo log file（日志追加，顺序io）。定时将脏页刷新到磁盘（随机io），如果出错，则使用redo log file恢复。如果成功，则定期清理redo log file

undo log

记录数据被修改前的信息，用于回滚和mvcc
逻辑日志
insert时产生的日志，只在回滚时使用，事务提交后可立即删除
删/改产生的日志，不会立即删除

mvcc

mvcc：在已提交读和可重复读这两种隔离级别下的事务，对每一行数据增加版本号，SELECT时访问版本链中的记录。

当前读：读取记录最新操作，读取时保证其他并发事务不能修改当前记录
快照读：select，生成快照，不加锁，非阻塞读
读已提交：每次select 生成一个快照，生成readview
可重复读：事务的第一个select 增加快照，生成readview，后续复用readview
序列化：快照读退化为当前读

实现：字段（事务id，回滚指针(记录的上一个版本)+undo log，隐式主键(没有主键则生成)），undo log， readview
readview：读视图，快照读sql执行mvcc提取数据的依据，记录并维护系统当前活跃的事务id

存储过程

其他

主从复制

读写分离

分库分表