1、什么是索引？都有哪些优缺点？
概念：让mysql快速获取到数据的排好序的数据结构。
优点 ：
（1）使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
（2）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
缺点 ：
（1）创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
（2）索引需要使用物理文件存储，也会耗费一定空间。
2、索引都有哪些数据结构？
（1）二叉树：左子树的键值小于根的键值，右子树的键值大于根的键值。为什么不用二叉树，当索引是自增整数的时候，退化成链表，就会生成很深的树，这个时候查找数据进行遍历的时候，就会效率很慢，和没有索引效率没差别。
（2）mysql很早以前版本索引用的红黑树。为什么不用红黑树（二叉平衡树），还是因为层级比较多，这样检索数据比较慢。
（3）B tree：平衡多路查找树。
1> 每个节点都存储key和data。任何关键字出现且只出现在一个节点中
2> 叶子节点指针为null。
3> 搜索有可能在非叶子节点结束，在关键字全集内做一次查找，性能逼近二分查找算法。
（4）B+ tree：变种的B tree。
1> 所有的叶子结点中包含了全部关键字的信息，非叶子节点只存储键值信息，及指向含有这些关键字记录的指针。
2> 为所有叶子节点增加了一个链指针，且叶子结点本身依关键字的大小自小而大的顺序链接。
3> 提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要 I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。非叶子结点直接在内存里面折半查找，只有叶子节点才会进行一次磁盘IO。
3、什么是聚集索引？都有哪些优缺点？
聚集索引（聚簇索引）：数据和索引设计到一个文件中。叶节点包含了所有数据记录。
优点：聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。
缺点：依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。
4、什么是非聚集索引？都有哪些优缺点？
非聚集索引（非聚簇索引）：数据和索引设计到不同文件中。
优点：更新代价比聚集索引要小 。因为非聚集索引的叶子节点是不存放数据的。
缺点：跟聚集索引一样，非聚集索引也依赖于有序的数据，可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。当查到索 引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。
5、聚集索引和非聚集索引哪个检索数据快？为什么？
聚集索引检索数据会比非聚集索引快。
原因：聚集索引只要找到索引就能一次性把索引所在行的数据一次性加载出来，因为之前已经加载到内存中了，而非聚集索引需要先在索引文件（MYI）中把磁盘文件地址找到，然后再去数据文件（MYD）中再做一次磁盘io，所以需要两次磁盘io，而聚集索引只需要一次磁盘io，所以聚集索引会快。
6、什么是回表？
需要扫描两遍索引树，先通过普通索引定位到主键值，再通过聚集索引定位到行记录。
7、什么是hash索引？
对索引的key进行一个hash运算，就可以定位出数据存储的位置。很多时候，hash索引会比b+tree索引更高效。仅能满足“=” 或者 “IN”，不支持范围查找（工作中很少用hash索引的原因）。会发生hash碰撞，但概率很低。
8、hash索引如何定位索引数据的？
例如查询条件是：where col3 = “tom”，只要把tom的hash值算出来，然后定位到数据存储的位置，逐个在hash链表中进行比对，找到和Tom相同的元素，拿出他的磁盘文件地址，去磁盘里面定位这行的所有元素。
9、什么是覆盖索引？
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称之为“覆盖索引”。在 InnoDB 存储引擎中，如果不是主键索引（叶子节点存储的是主键+列值）。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢，覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！
覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。
如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。
再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。
10、什么是主键索引(Primary Key)？
数据表的主键列使用的就是主键索引。
一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
11、什么是二级索引(辅助索引)？
二级索引又称为辅助索引，属于非聚集索引。是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。
唯一索引，普通索引，前缀索引等索引属于二级索引。
12、创建索引需要注意哪些？
（1）选择合适的字段创建索引：
不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。
（2）被频繁更新的字段应该慎重建立索引。
虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。
（3）尽可能的考虑建立联合索引而不是单列索引。
因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。
（4）注意避免冗余索引 。
冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。
（5）考虑在字符串类型的字段上使用前缀索引代替普通索引。
前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。
13、使用索引的一些建议
对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
避免 where 子句中对字段施加函数，这会造成无法命中索引。
在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用
在使用 limit offset 查询缓慢时，可以借助索引来提高性能
14、MySQL 如何为表字段添加索引？
（1）添加 PRIMARY KEY（主键索引）
ALTER TABLE table_name ADD PRIMARY KEY ( column )
（2）添加 UNIQUE(唯一索引)
ALTER TABLE table_name ADD UNIQUE ( column )
（3）添加 INDEX(普通索引)
ALTER TABLE table_name ADD INDEX index_name ( column )
（4）添加 FULLTEXT(全文索引)
ALTER TABLE table_name ADD FULLTEXT ( column)
（5）添加多列索引
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )
15、为什么建议innodb表必须建主键，并且推荐使用整型的自增主键？
（1）为什么必须建主键？innodb整个表的结构，他的设计就是必须有一个b+tree来组织整张表的数据。如果设置了主键，主键自带索引，他就会默认用主键来组织整张表的数据，如果没有建索引，他会帮我们找一个主键，他会在整张表逐列去找，找到一列所有数据都不重复，就是那种可以添加唯一索引的列，他找到这个列之后，会用这列数据来建一个b+tree的结构来组织整张表的所有数据；如果找不到这样不重复的列，mysql会维护一个隐藏列，类似rowid，他会用这个隐藏列来组织维护整张表的所有数据。
（2）为什么用整型？和uuid对比，找索引元素的时候，会有很多次元素大小的比对，对于比较大小肯定是整型比uuid那种字符串比较要快，从存储空间来看，整型也比uuid占用的少。
（3）为什么要用自增？自增的话，永远是往后面插入，如果不是自增，插入的时候会插入到中间，插入到中间的话，如果节点满了，树就会进行分裂，分裂后还会做一下平衡。这样效率就比较低，所以自增往后面插入效率会高。
16、B+tree 与 B-tree区别有哪些？
（1）B-tree：叶子节点没有维护双向指针，没有指针就没有办法更好支持范围查找。
（2）B+tree：非叶子结点只放了索引元素，没有放数据，整个数据都放到叶子节点，这样可以降低树的高度，查找效率会更高。
17、索引最左前缀原理？
联合索引的底层存储结构长什么样？
image.png
逐个字段去比较，如果第一个字段不相等，那就已经比出来大小了，不用比较后面的字段了，如果第一个字段相同，就去比较第二个字段。如果第二个字段也相等，就比较第三个字段。如果三个字段都相等，那就不是主键索引了，那就是非主键索引，那么就存储主键。
例：索引myindex:(a,b,c)，只会走a、a,b、a,b,c 三种类型的查询，其实这里说的有一点问题，a,c也走，但是只走a字段索引，不会走c字段，另外还有一个特殊情况说明下，select * from table where a = '1' and b > ‘2’ and c='3' 这种类型的也只会有a与b走索引，c不会走。原因如下：索引是有序的，myindex索引在索引文件中的排列是有序的，首先根据a来排序，然后才是根据b来排序，最后是根据c来排序，像select * from table where a = '1' and b > ‘2’ and c='3' 这种类型的sql语句，在a、b走完索引后，c肯定是无序了，所以c就没法走索引，数据库会觉得还不如全表扫描c字段来的快。
18、mysql常用的存储引擎？如何查看使用的哪种存储引擎？有什么区别？
查看方式：
（1）查看MySQL提供的所有存储引擎命令：mysql> show engines;
（2）查看MySQL当前默认的存储引擎命令：mysql> show variables like '%storage_engine%';
（3）查看表的存储引擎命令：show table status like "table_name" ;
存储引擎是用来修饰表的，常用的存储引擎有两种InnoDB与MyISAM。
InnoDB与MyISAM对比：
（1）InnoDB支持事务，MyISAM不支持
（2）InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
（3）InnoDB支持外键，而MyISAM不支持
（4）InnoDB是聚集索引：使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组
织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询
到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
MyISAM是非聚集索引：也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索
引和 辅助索引是独立的。
也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索
引和辅助索引的叶子节点都是数据文件的地址指针。
（5）InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，
执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）。
（6）Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后
的InnoDB支持全文索引了。
（7）MyISAM表格可以被压缩后进行查询操作。
（8）InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），
而Myisam可以没有。
（9）InnoDB存储文件有frm、ibd，而Myisam是frm、MYD、MYI
InnoDB：frm是表定义文件，ibd是数据文件
MyISAM：frm是表定义文件，myd是数据文件，myi是索引文件
19、mysql中局部性原理
我们代码可能就只需要取一个字节的数据，但是我们计算机拥有局部性原理，返回的并不是这一个字节的数据，而是这个字节所在页的一页数据。原因：虽然你现在就只需要用一个字节的数据，但是可能等一下你又立马需要用到这个字节相邻的其他数据，但是你本次并没有把相邻的其他数据取出来，接下来的请求你可能又需要从磁盘取数据。这样效率就比较差。因此这个理论就是在取一个字节数据的时候会把相邻的数据也同时从磁盘取出来，放到内存里面，下一次如果正好符合这个理论，就不需要从磁盘里面取，直接从内存里面取就可以了。取一个数据的时候，会多取一部分数据，放到内存里面去，接下来用到这部分数据的时候就直接从内存里面拿，不用从磁盘里面取了，这样就会快一些。那么一次取多少相邻的数据呢？操作系统里面有一个页的单位，他就是取一页的数据，4KB大小的数据。innodb中也有一个页的单位，他的大小是16KB。
20、mysql中锁你需要知道的
（1）InnoDB锁模式：
InnoDB 实现了以下两种类型的行锁：
共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（IntentionLocks），这两种意向锁都是表锁：
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
锁模式的兼容情况：
image.png
（如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务； 反之， 如果两者不兼容，该事务就要等待锁释放。）
（2）InnoDB加锁方法：
意向锁是 InnoDB 自动加的， 不需用户干预。
对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)；
对于普通 SELECT 语句，InnoDB 不会加任何锁；事务可以通过以下语句显式给记录集加共享锁或排他锁：
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 其他session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁。
隐式锁定：
InnoDB在事务执行过程中，使用两阶段锁协议：随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；
锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。
显式锁定 ：
select for update：
在执行这个 select 查询语句的时候，会将对应的索引访问条目进行上排他锁（X 锁），也就是说这个语句对应的锁就相当于update带来的效果。
select *** for update 的使用场景：为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。
select lock in share mode ：in share mode 子句的作用就是将查找到的数据加上一个 share 锁，这个就是表示其他的事务只能对这些数据进行简单的select 操作，并不能够进行 DML 操作。select ***lock in share mode 使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。
性能影响：
select for update 语句，相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。
select lock in share mode 语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。
for update 和 lock in share mode 的区别：
前一个上的是排他锁（X 锁），一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 forupdate ；
后一个是共享锁，多个事务可以同时的对相同数据执行 lock in share mode。
（3）InnoDB 行锁实现方式：
InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。
由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。 应用设计的时候要注意这一点。
（4）InnoDB的间隙锁：
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
select ... lock in share mode //共享锁
select ... for update //排他锁
很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
（5）InnoDB使用间隙锁的目的：
1> 防止幻读，以满足相关隔离级别的要求；
2> 满足恢复和复制的需要：
MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制（复制其实就是在 Slave Mysql 不断做基于BINLOG 的恢复）有以下特点：
一是 MySQL 的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。
二是 MySQL 的 Binlog 是按照事务提交的先后顺序记录的， 恢复也是按这个顺序进行的。
由此可见，MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。
InnoDB 在不同隔离级别下的一致性读及锁的差异：
锁和多版本数据（MVCC）是 InnoDB 实现一致性读和 ISO/ANSI SQL92 隔离级别的手段。
因此，在不同的隔离级别下，InnoDB 处理 SQL 时采用的一致性读策略和需要的锁是不同的：
对于许多 SQL，隔离级别越高，InnoDB 给记录集加的锁就越严格（尤其是使用范围条件的时候），产生锁冲突的可能性也就越高，从而对并发性事务处理性能的 影响也就越大。因此， 我们在应用中， 应该尽量使用较低的隔离级别， 以减少锁争用的机率。实际上，通过优化事务逻辑，大部分应用使用 Read Commited 隔离级别就足够了。对于一些确实需要更高隔离级别的事务，
可以通过在程序中执行 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 或 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 动态改变隔离级别的方式满足需求。
（6）死锁（Deadlock Free）
死锁产生：死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会
——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。
检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。
外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。
MyISAM避免死锁：
在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。
InnoDB避免死锁：
为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
改变事务隔离级别
如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。
（7）一些优化锁性能的建议
尽量使用较低的隔离级别
精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会
选择合理的事务大小，小事务发生锁冲突的几率也更小
给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改>时再请求排他锁，这样容易产生死锁
不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
不要申请超过实际需要的锁级别
除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能
（8）乐观锁、悲观锁
乐观锁(Optimistic Lock)：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
乐观锁不能解决脏读的问题。
乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。
悲观锁(Pessimistic Lock)：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
悲观锁，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
网友评论