Mysql思维导图

Calcite

CBO基于成本优化
RBO基于规则优化

存储位置

存储格式

支持事务、行级锁、主键

存储位置

存储格式

不支持事务

存储位置

逻辑日志，每行操作会记录一条日志，存在于数据库的undo log segment（段）中，
undo log也会产生redo log，因为undo log也要实现持久性保护
分为：
insert undo log（insert只对本身事务可见，对其他事务无影响）
update undo log（update/delete）

回滚

MVCC

脏读、不可重复读、幻读

不可重复读、幻读

幻读

物理格式的日志，记录数据库中每个页的修改
包含两部分：
1、内存中的日志缓冲（redo log buffer）
2、磁盘上的重做日志文件(redo log file)
在一个事务中每一次sql操作都会写一个redo log至内存的buffer中
事务提交时必须先将该事务所有的redo log调用fsync至redo log file中

当系统崩溃时，虽然数据没有持久化，但是redo log持久化了，
系统可以根据redo log的内容，将所有数据恢复至最新的状态

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引
Innodb默认将创建表定义的主键设置为主键索引，如果没有主键会选择一个唯一的非空索引代替
如果没有这样的索引，InnoDB 会隐式定义一个主键6个字节row_id来作为聚簇索引

1、ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2、创建表的时候指定主键即可

1、ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
2、CREATE INDEX index_name ON table_name (column_name)

辅助索引的叶子节点中不是存放的实际数据，而是存放的主键，
再通过主键索引查询对应的记录称为回表
例：表tbl中字段id、name，id为主键、name设置了辅助索引
select * from tbl where name='zhangsan';

根据辅助索引查询的时候，如果查询字段刚好是辅助索引叶子节点的数据
例：表tbl中字段id、name，id为主键、name设置了辅助索引
select id from tbl where name='zhangsan';

当where查询存在多个条件查询的时候，我们需要对查询的列创建组合索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

创建了一个组合索引（a，b，c），相当于创建了三个索引（a），（a，b），（a，b，c）
select * from tbl where a=1 and b=2 and c=2; 使用索引（a，b，c）
select * from tbl where a=1 and b=2; 使用索引（a，b）
select * from tbl where a=1 and c=2; 使用索引（a）
select * from tbl where b=2 and c=2; 不使用索引
select * from tbl where c=2; 不使用索引
like关键词：like '%aaa%' 不使用索引，like 'aaa%'使用索引

当查询字段不是或不全是组合索引字段，查询条件是多条件且where/order by字段全是组合索引字段
index_condition_pushdown（ISP）（mysql 5.6+包括5.6 提供该功能）

默认开启的，通过下面配置控制索引下推开关
SET optimizer_switch = 'index_condition_pushdown=off';

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须一

1、ALTER TABLE `table_name` ADD UNIQUE [indexName] (`column`)
2、CREATE UNIQUE INDEX index_name ON table_name (column_name)

FULLTEXT索引用于全文搜索。
只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列
5.6以前只有MyISAM支持，5.6及5.6+两者都支持

ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

select * from table_name where match(col1,col2) against('xxx xxx');
col1、col2是创建全文索引基于的列，这里match必须和创建的全文索引保持一致

表共享读锁（Table Read Lock）

表独占写锁（Table Write Lock）

在查询时，会对查询语句中所有的表自动加上读锁；
在更新时，会对更新语句中所有的表自动加上写锁

show status like 'table%';
如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况

通过在索引上加锁实现的，与Oracle不同，Oracle是通过在数据块中对相应数据行加锁来实现的
所以在Innodb中，只有通过索引检索数据时，才会用到行锁，否则只会用到表锁

show status like 'innodb_row_lock%';
如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

共享锁（读锁）

排他锁（写锁）

update,delete,insert都会自动给涉及到的数据加上排他锁
select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句
所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，
但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制