mysql可以在线加索引吗 mysql在线加索引锁表

Mysql索引

索引是帮助MySQL高效获取数据的排好序的数据结构（牢牢把握“排好序”）

(可能会需要你用手能画出大概的图)

MyISAM存储引擎非聚集索引/稀疏索引

• 主索引：MyISAM索引文件和数据文件是分离的,叶子节点存放的是数据文件对应的磁盘文件地址指针，从存储文件也可以看出。因此多了一个间接查询的过程（只能有一个）
• 辅助索引/普通索引：MyISAM的主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。（辅助索引并不是指联合索引）

InnoDB存储引擎聚集索引/密集索引

• 主索引的区别：InnoDB索引文件和数据文件是在一起的，表数据文件本身就是按照B+Tree组织的一个索引结构。因此InnoDB的表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。（只能有一个）
• 辅助索引/普通索引：InnoDB的辅助索引同样是一颗B+Tree,只是data域为主键

InnoDB索引相关问题

为什么不建议使用过长的字段作为主键

因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大

为什么InnoDB必须有主键，并且推荐使用整型的自增主键？

• 因为InnoDB表数据文件是按照主键组织的一个索引结构
• 索引查询的过程会有无数比较过程，整数比UUID要快，而且整型的储存空间更小，每个节点可以存放的数据更多
• 如果非自增，插入的过程为无序插入，那么很可能插入已满叶子节点触发树的向上分裂过程，非常耗时

什么时候创建索引？（高频面试题）

一般来说，应该在这些列上创建索引:

• 在经常需要搜索、排序、WHERE的列上，可以加快对应操作的速度。
• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
• 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。

同样，对于有些列不应该创建索引（因为索引是用空间换时间的一种方式）。一般来说，不应该创建索引的的这些列具有下列特点：

• 对于那些在查询中很少使用或者参考的列不应该创建索引。
• 对于那些只有很少数据值的列也不应该增加索引。当数据量不大时，全表扫描也是可以接受的。
• 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
• 当修改操作比大于检索操作更频繁时，不应该创建索引。因为需要频繁的维护索引结构。

Explain

这里后续可能还要收集一些面试题才可以。要理解场景地去背，别人不会直接问你type列作用，而是问你XXX功能怎么实现。

Explain详解与索引最佳实践

1. 如何查看索引是否生效

explain type列

2.阿里面试被问过的一个问题：explain你记得哪些字段，分别有什么含义？

事务

主要背概念了

事务的特性【ACID】

• 原子性（Atomicity）:原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
• 一致性（Consistency）:事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。
• 隔离性（Isolation）:隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
• 持久性（Durability）:持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

事务并发操作会带来的问题

脏读-》事务隔离级别-》锁

1.脏读又称无效数据读出

一个事务读取另外一个事务还没有提交的数据叫脏读。

2. 不可重复读

事务A：读取数据行X

事务B：修改数据行X为Y，提交事务

事务A：读取数据行X，观察为Y，那么在同一个事务A，读到了两个不同的数据。

在一个事务中前后两次读取的结果并不致，导致了不可重复读。

（事务读取之间，有其他事务提交操作，所以不能重复读到相同的数据）

3. 幻读

事务 T1 读取一条指定的 Where 子句所返回的结果集，然后 T2 事务新插入一行记录，这行记录恰好可以满足T1 所使用的查询条件。然后 T1 再次对表进行检索，但又看到了 T2 插入的数据。 （和可重复读类似，但是事务 T2 的数据操作仅仅是插入和删除，不是修改数据，读取的记录数量前后不一致）

幻读和不可重复读有什么区别

幻读主要是针对插入和删除操作，不可重复读主要是针对修改操作。

脏读和不可重复读有什么区别

脏读主要是针对没有提交的事务的修改，不可重复读主要是针对事务提交之后的修改。

事务的隔离级别

首先是了解了数据库在并发操作下会引发的问题，然后再来了解隔离级别，针对哪些问题

1. 读未提交（Read Uncommited）

最低的隔离等级，允许其他事务看到没有提交的数据，会导致脏读。

2. 读已提交（Read Commited）

被读取的数据可以被其他事务修改，这样可能导致不可重复读。也就是说，事务读取的时候获取读锁，但是在读完之后立即释放(不需要等事务结束)，而写锁则是事务提交之后才释放，释放读锁之后，就可能被其他事务修改数据。该等级也是 SQL Server 默认的隔离等级。

3. 可重复读（Repeated Read）

MySQL的默认事务隔离级别

所有被 Select 获取的数据都不能被修改，这样就可以避免一个事务前后读取不一致的情况。但是没有办法控制幻读，因为这个时候其他事务不能更改所选的数据，但是可以增加数据，因为强恶意事务没有范围锁。

4. 串行化（Serializable）

所有事务一个接着一个的执行，这样可以避免幻读 (phantom read)，对于基于锁来实现并发控制的数据库来说，串行化要求在执行范围查询的时候，需要获取范围锁，如果不是基于锁实现并发控制的数据库，则检查到有违反串行操作的事务时，需回滚该事务。

innodb下，幻读是如何被解决？

MySQL通过MVCC(multi version concurrent control)来实现默认的"Repeatable Read"事务隔离级别

1. MVCC定义

MVCC，Multi-Version Concurrency Control，多版本并发控制。MVCC 使用了一种不同的手段，每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照，写者写操作造成的变化在写操作完成之前（或者数据库事务提交之前）对于其他的读者来说是不可见的。**当一个 MVCC 数据库需要更一个一条数据记录的时候，它不会直接用新数据覆盖旧数据，而是将旧数据标记为过时（obsolete）并在别处增加新版本的数据。这样就会有存储多个版本的数据，但是只有一个是最新的。**这种方式允许读者读取在他读之前已经存在的数据，即使这些在读的过程中半路被别人修改、删除了，也对先前正在读的用户没有影响。

2. mysql底层实现

InnoDB在每行记录后面保存两个隐藏的列来，分别保存了这个行的创建时间和行的删除时间。这里存储的并不是实际的时间值,而是系统版本号，当数据被修改时，版本号加1。在读取事务开始时，系统会给当前读事务一个版本号，事务会读取版本号<=当前版本号的数据。此时如果其他写事务修改了这条数据，那么这条数据的版本号就会加1，从而比当前读事务的版本号高，读事务自然而然的就读不到更新后的数据了。

举个栗子，假设初始版本号为1：

INSERT

insert into user (id,name) values (1,'Tom');

idnamecreate_versiondelete_version1Tom1

下面模拟一下文章开头的场景：

SELECT (事务A)

select * from user where id = 1;

此时读到的版本号为1，值为"Tom"

UPDATE(事务B)

update user set name = 'Jerry' where id = 1;

在更新操作的时候，该事务的版本号在原来的基础上加1，所以版本号为2。 先将要更新的这条数据标记为已删除，并且删除的版本号是当前事务的版本号，然后插入一行新的记录

idnamecreate_versiondelete_version1Tom121Jerry2

SELECT (事务A)

此时事务A再重新读数据：

select * from user where id = 1;

由于事务A一直没提交，所以此时读到的版本号还是为1，所以读到的还是Tom这条数据，也就是可重复读。

Mysql锁

向面试官解释清楚什么是表锁，什么是行锁

面试管可能会为你行锁有哪几种，表锁有哪几种，实际上它是在问你排他锁和共享锁

排他锁

排他锁又称为写锁，顾名思义，排他锁就是不能与其他锁所并存，如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁。

加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制

共享锁

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改

行锁

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；

• 共享锁：select * from tableName where ... + lock in share more
• 排他锁：select * from tableName where ... + for update
• 行锁的劣势：开销大；加锁慢；会出现死锁
• 行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但不存在的记录，叫作“间隙（GAP）”。InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定值范围内的任何数据。若执行的条件是范围过大，则InnoDB会将整个范围内所有的索引键值全部锁定，很容易对性能造成影响。

行锁优化

1 尽可能让所有数据检索都通过索引来完成，避免无索引行或索引失效导致行锁升级为表锁。
2 尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围。
3 尽可能减少事务的粒度，比如控制事务大小，而从减少锁定资源量和时间长度，从而减少锁的竞争等，提供性能。
4 尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低。

表锁

顾名思义，表锁就是一锁锁一整张表，在表被锁定期间，其他事务不能对该表进行操作，必须等当前表的锁被释放后才能进行操作。

有待补充

• 共享读锁：lock table tableName read;
• 排他写锁：lock table tableName write;
• 批量解锁：unlock tables;
• 表锁的优势：开销小；加锁快；无死锁
• 表锁的劣势：锁粒度大，发生锁冲突的概率高，并发处理能力低

什么场景下用表锁？/什么时候走表锁（很常问）

InnoDB默认采用行锁，在未使用索引字段查询时升级为表锁。MySQL这样设计并不是给你挖坑。它有自己的设计目的。即便你在条件中使用了索引字段，MySQL会根据自身的执行计划，考虑是否使用索引(所以explain命令中会有possible_key 和 key)。如果MySQL认为全表扫描效率更高，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

第一种情况：全表更新。事务需要更新大部分或全部数据，且表又比较大。若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。

第二种情况：多表查询。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。

MyISAM与InnoDB的区别，以及使用场景