看了这篇文章，发现自己不懂数据库

前言

索引： 单个索引、联合索引、主键索引

事务： 四个粒度, 分别是数据库级、表级、记录级 (行级) 和属性级(字段级)

锁：行锁，表锁；乐观锁，悲观锁

mysql优化： 分表，sql优化

生产数据库都要求数据库引擎设置为innodb，因为考虑到并发的优势！

正文

0：数据库引擎介绍

myisam是mysql数据库的默认引擎，不支持事务，使用的锁是表级别锁，一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁，由于锁的粒度比较大，所以并发能力一般！

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

在InnoDB的事务管理和锁定机制中，容易发生死锁，所以有一套专门检测死锁存在的机制，当两个事务发生死锁后，会终断小的事务，让大的事务运行（大小，依照事务涉及数据量来评估）

1： 索引

分类：

普通索引(单列索引)：最基本的索引，没有任何限制@hxx 常加在where列 和join 列上

Select people.age, ##不使用索引

town.name##不使用索引

FROM peopleLEFTJOINtown ON people.townid=town.townid ##考虑使用索引

Where firstname='Mike'##考虑使用索引

AND lastname='Sullivan'##考虑使用索引

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

主键索引：它 是一种特殊的唯一索引，不允许有空值。@hxx就是一种唯一性索引

全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。

组合索引：为了更多的提高 mysql 效率可建立组合索引，遵循” 最左前缀 “原则。

最左侧原则：例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .

建立多个单列索引和组合索引的区别?

比如：Select peopleid

FROM people

Where firstname='Mike'

ANDlastname='Sullivan'ANDage=17;

将三个列都建立独立的单列索引，和建立 ADD INDEX fname_lname_age (firstname,lastname,age);

区别在哪？

三个单索引，相当于查三次索引，每次范围都缩小一次，就是row的结果集依次减少，最后得出结果，但是row的减少并不是最优的！

而建立组合索引，相当于只有一个索引块了，利用b+树的特点，只需要查一个索引就能快速跳转得到结果！

创建索引的sql：

create （unique，）index on Tablename(列的列表)

alter table TableName add index (列的列表)

原理：

b+树：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级，这就是b+树的优势。

哪些sql语句会用到索引？

列做比较的表达式，如=, >, >=,

like语句的条件是不以通配符开头的常量串@hxx LIKE 'Mich%' 这个会使用索引

col_name的列建了索引，则形如"col_name is null"的SQL会用到索引

where条件不只1个条件，则MySQL会进行Index Merge优化来缩小候选集范围

优化：

a：列的区分度比较高的，加上索引会比较好！@hxx 无脑的做法是为常用的where 语句的字段都加上索引，如果此字段识别度比较低（比如sex字段），那么查询效率不会有太大的提升，并且还会影响更新和插入的性能

b：

@hxx 参见美团这篇文章https://tech.meituan.com/mysql-index.html

2：事务

捋一下思路

事务的隔离性 造成的影响： 脏读，不可重复读，幻影读

事务一致性造成的影响： 不要用并发来回答这个问题，一致性指的是从一个状态符合预期的变成另一个状态，acid其余三个属性都是为了一致性服务的！ 并发只的是多线程引起的，而一个事务就是一个线程，所以并发应该算到隔离性中。

事务是如何实现的？@hxx 总觉得redo log 和undo log只要一个即可！

redo log：保存执行的每一条sql log，当客户端执行每条SQL（更新语句）时，redo log会被首先写入log buffer；当客户端执行COMMIT命令时，log buffer中的内容会被视情况刷新到磁盘@hxx 1s刷一次

undo log：undo log是为回滚而用，具体内容就是copy事务前的数据库内容（行）到undo buffer，在适合的时间把undo buffer中的内容刷新到磁盘。 @hxx 回退，注意是拷贝数据

rollback segment：可以认为undo log和回滚段是同一意思，@hxx 是undo log的物理存储形式

锁：行锁，表锁，下段介绍

隔离级别：四种级别

@hxx redo log 和undo log 的区别？ redolog 和 binlog 的区别？

redo log 可以防止掉电数据恢复，只要重新执行sql语句即可恢复（从Checkpoint开始执行），undo log 主要用于出错rollback，它没有一个单独的log文件，而是以数据库内部一个特殊的数据段存在的，undo段位于共享表空间内！

一个事务执行的操作：

begin->用排他锁锁定该行->记录redo log->记录undo log->修改当前行的值，写事务编号，回滚指针指向undo log中的修改前的行@hxx 事务编号，是为了mvcc

@hxx MyISAM不支持事务，但是不是不支持锁哈

3：锁@hxx 表锁和行锁 像HashTable 和CocurrentHashMap，粒度不一样

@hxx 记得之前在解释事务的时候，“事务不是加锁的吗”笔记，拿过锁做比较！！

事务： 就是acid，也可以说四个特性是acid@hxx 数据库的事务就是acid，我一直以为是“要么全成功要么都失败”，这是原子性

锁： 实现acid中，consistent 一致性的方式！

锁的分类： 有两种分类方式，一种是按对象（粒度），一种按模式，注意是组合的哈

按对象@hxx 下方有个表格，根据不同的引擎划分

行级：开销大，加锁慢，因为粒度最小，锁冲突的概率小，并发度高， 会出现死锁@hxx 其他引擎实现，InnoDB存储引擎, 表锁是默认引擎myisam实现的

页级（mysql特有）： 中等@hxxBerkeleyDB

表级： 开销小，加锁快，并发度低， 死锁概率最低@hxx 也有说是不会死锁的，MyISAM表锁是deadlock free的

按模式

排它锁（悲观锁，x锁）：阻塞一切事务读写， 事务获取排它锁后能读写，其他事务不能读写

共享锁（乐观锁，s锁）：只阻塞写， 事务获得共享锁后能读不能写，只有获得排它锁才能写（@hxx当然如果当前行没有加排它锁，当然可以写啦）

~~~innodb独有的锁

意向锁，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。@hxx 意向锁是innodb的表锁定的叫法

间隙锁（Next-Key锁）：对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。@hxx 因为幻影读是增删，对预期增加的项加锁了，你就插不进来了，哈哈

假如emp表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL：

mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

@hxx 锁按对象和按模式，是组合的哈，不是对立的，比如 innodb 引擎 默认使用行级锁，其行级锁又有x锁或s锁，具体什么时候用哪种，则

锁与锁之间的共存和互斥关系如下：

如何加锁@hxx 按照引擎来

MyISAM：在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。@hxx myisam 是读读共享锁，写+其他 阻塞，并且默认是表锁

@hxx 当一个读事务过来，然后再来一个写事务，myisam先执行哪个？

读事务先进锁等待队列，写事务插队（优先级高），会插到队列前面，所以先执行写 （非公平锁）

InnoDB：行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁@hxx 两次query涉及到同一行，不一定会触碰到行锁，必须要涉及到同一个索引键！

@hxx ，可否理解为，只要query中使用了索引，就一定触碰到行锁

答案不一定，当某个query对应的表的数据量非常小，mysql不一定会使用索引，因为全表扫描的代价可能更低，这个时候使用到的是表锁

该如何选择

当事务所涉及的数据比较大，并且希望小概率的出现死锁，则选择表锁，表锁主要是MyISAM@hxx 所以需要知道并发的情况，show status like 'table%';查看mysql内部表被锁的情况

大量按索引条件并发，选择行锁，主要是InnoDB存储引擎

如何避免死锁@hxx 下面所列的几点都是针对innodb的，因为myisam不支持事务哈

通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。

具体的方式有：

a：控制事务执行的顺序，比如控制表的串行访问减少并行，

b： 在最开始就申请最严格的锁，比如更新数据时，申请排它锁，不要先申请共享锁等到执行update再申请排它锁@hxx 牺牲性能，换取避免死锁

c：在REPEATABLE-READ隔离级别下：查询的时候不准修改！ 即查询会在当前行修改上加排它锁，，这个时候如下case会出现死锁，如果两个事务，同时查询过来，都加排它锁，发现没有记录，则插入，这个时候互相等待对方的锁，造成死锁 ， 解决方案是，修改隔离级别为 read-commited， 即防止脏读，即修改的时候对读加排他锁，而读的时候并不会加锁（会出现不可重复读）@hxx 非常重要的点！

d：当隔离级别为READ COMMITTED时，还是上面的case，两个事务同时过来，同时查询记录（不会加排它锁），如果没有记录，则插入，加排它锁的，只有一个能插入成功，另一个没获得锁等待，一个插入成功另一个继续插入，但是记录存在了，报主键存在异常，如果异常后不释放排它锁，则第三个事务过来申请锁的时候就会死锁

，所以需要finally中释放锁！

4：MVCC@hxx innodb中的概念，Multiversion Concurrency Control， 只针对read repeatable

锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销。@hxx 注意是替代行锁的！

mysql 如何实现mvcc？

下图展示了db一条记录，需要记录的内容，与mvcc 相关的是 ，三个隐藏字段 （标蓝的）

6字节的DATA_TRX_ID 标记了最新更新这条行记录的transaction id，每处理一个事务，其值自动+1，@hxx 就是系统版本号的值，是自动增加的

DATA_ROLL_PTR是记录undo日志的位置指针，用于找到之前版本的数据回滚

DELETE BIT位用于标识该记录是否被删除，这里的不是真正的删除数据，而是标志出来的删除。真正意义的删除是在commit的时候

@hxx 实现原理，在每一行数据中，多加了3列，事务版本号 和 删除标记位，按照下面的操作写入这两个列的数据：

begin->用排他锁锁定该行->记录redo log->记录undo log->修改当前行的值，写事务编号，回滚指针指向undo log中的修改前的行

mvcc 的具体过程：

SELECT： 比如具备两个要求的数据才会被返回：

a： 查询事务的版本号，大于或等于 row 中记录版本号@hxx 意思就是只会查询已存在的数据或者是本事务操作的数据

b： 行的删除操作的版本一定是未定义的或者大于当前事务的版本号@hxx 保证没有被删除

insert：InnoDB为每个新增行记录当前系统版本号作为创建ID@hxx 此时 删除版本号为null

update：InnoDB复制了一行，有新老两行数据啦。新行的事务id为系统版本号，新行的删除id为null；老行的 事务id不变， 删除id为当前系统版本号

delete：InnoDB为每个删除行的记录当前系统版本号作为行的删除ID。

为何适用于REPEATABLE READ？

REPEATABLE READ，可重复读，完全适用MVCC，只能读取在它开始之前已经提交的事务对数据库的修改,在它开始以后,所有其他事务对数据库的修改对它来说均不可见

原因：当有事务a读取row a， 事务b 修改了row a （commit后版本号change），事务a再读取，不会得到a的最新数据； 如果事务b修改没有commit，row a被a事务再次读取的时候是不受影响的！ 这个过程中都是并发的，没有锁的影响，所以说mvcc 能提高性能！

5：常见优化手段

表设计优化：略

sql优化：略

sql查询的过程：略

6： 常见设计思想

分表之后想让一个 id 多个表是自增的@hxx 并不要求多个表是顺序的递增的

a：新建一个id表来管理id，做法是来一条数据，在插入对应表前，先从id表插入一个自增id，然后获取最大的id，这个id连同数据即可插入了

劣势： mysql 的auto increment 有锁，新能可能比较低

b：redis 自增id功能来实现

c：队列服务，在队列中预分配一批id，实时监控队列的数据长度，及时往队列中put id保证充足@hxx 能控制队列中id的规则， 比如qq的靓号放在一个另一个队列中，实现盈利

d：mysql id 区间隔离 : 不同分库设置不同的起始值和步长，比如 2 台 mysql，就可以设置一台只生成奇数，另一台生成偶数. 或者 1 台用 0~10 亿，另一台用 10~20 亿. 优势：利用 mysql 自增 id 缺点：运维成本比较高，数据扩容时需要重新设置步长