MySQL++：MySQL 锁表原因 及 核心问题（性能优化）
MySQL 锁概述：
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。
如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
本章我们着重讨论MySQL锁机制的特点，常见的锁问题，以及解决MySQL锁问题的一些方法或建议：↓
- - -
相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。
比如：
MyISAM：和 MEMORY存储引擎采用的是表级锁（table-level locking）；
BDB：存储引擎采用的是页面锁（page-level locking），但也支持表级锁；
InnoDB：存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
MySQL 这 3 种锁的特性可大致归纳如下：↓ ↓ ↓
开销、加锁速度、死锁、粒度、并发性能
从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！
仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；
而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
一切取决于应用程序，应用程序的不同部分可能需要不同的锁类型。
为了确定是否想要使用行级锁定的存储引擎，应看看应用程序做什么并且混合使用什么样的选择和更新语句。
例如，大多数Web应用程序执行许多选择，而很少进行删除，只对关键字的值进行更新，并且只插入少量具体的表。
基本MySQL MyISAM 设置已经调节得很好。
在 MySQL 中对于使用表级锁定的存储引擎，表锁定时不会死锁的。
InnoDB：
对于普通SELECT语句，InnoDB不会加任何锁；
InnoDB 行锁是通过给索引项加锁实现的：这就意味着只有通过索引条件检索数据时，InnoDB才使用行锁，否则使用表锁。
在mysql中，如果查询条件带有主键，会锁行数据，如果没有，会锁表。
当where条件中的字段没有加索引时，会锁住整张表。
在有索引的情况下，更新不同的行，innodb 默认的行锁是不会阻塞的。
where 后面的索引失效时，行锁变表锁，其他事物操作会有阻塞。
注意：在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。
行锁建议：↓
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小索引的范围
尽可能较少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能低级别事务隔离
varchar 不加单引号会引发行锁。
导致锁表原因：↓
1、锁表发生在insert update 、delete 中
2、锁表的原理是 数据库使用独占式封锁机制，当执行上面的语句时，对表进行锁住，直到发生commite 或者 回滚 或者退出数据库用户
第一、 A程序执行了对 tableA 的 insert ，并还未 commite时，B程序也对tableA 进行insert 则此时会发生资源正忙的异常 就是锁表
第二、锁表常发生于并发而不是并行（并行时，一个线程操作数据库时，另一个线程是不能操作数据库的，cpu 和i/o 分配原则）
3、减少锁表的概率：
减少insert 、update 、delete 语句执行 到 commite 之间的时间。
具体点批量执行改为单个执行、优化sql自身的非执行速度 如果异常对事物进行回滚
小栗子：↓ ↓ ↓
1）：使用 update
假设kid 是表table 的 一个索引字段 且值不唯一：
1）：如果kid 有多个值为12的记录那么：
update table set name=’feie’ where kid=12; #会锁表
2）：如果kid有唯一的值为1的记录那么：
update table set name=’feie’ where kid=1; #不会锁
总结：用索引字段做为条件进行修改时， 是否表锁的取决于这个索引字段能否确定记录唯一，当索引值对应记录不唯一，会进行锁表，相反则行锁。
2）：使用 delete
如果有两个delete ： kid1 与 kid2 是索引字段
1）：语句1 delete from table where kid1=1 and kid2=2;
2）：语句2 delete from table where kid1=1 and kid2=3;
# 这样的两个delete 是不会锁表的
1）：语句1 delete from table where kid1=1 and kid2=2;
2）：语句2 delete from table where kid1=1 ;
# 这样的两个delete 会锁表
总结：同一个表，如果进行删除操作时，尽量让删除条件统一，否则会相互影响造成锁表
相关指令：↓
1）：显示那些线程正在运行（查询到相对应的进程===然后 kill id）
show processlist ：只列出前100条
show full processlist：查看全部进程
2）：显示那些表是打开的
show open tables 、 show open tables from database;
这条命令能够查看当前有那些表是打开的。
In_use 列表示有多少线程正在使用某张表，Name_locked 表示表名是否被锁，这一般发生在Drop或Rename命令操作这张表时。
所以这条命令不能帮助解答我们常见的问题：当前某张表是否有死锁，谁拥有表上的这个锁等。
3）：查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
4）：查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
5）：查看服务器状态
show status like '%lock%';
6）：查看超时时间：
show variables like '%timeout%';
7）：查询表级锁争用情况
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：
show status like 'table%';
show global status like "table_locks%";
如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。
8）：获取 InnoDB 行锁争用情况
可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：
show status like 'innodb_row_lock%';
如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高
补充：
1、show processlist; 进程状态（status）解释
Checking table
正在检查数据表（这是自动的）。
Closing tables
正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。
这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
复制从服务器正在连接主服务器。
Copying to tmp table on disk
由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table
正在创建临时表以存放部分查询结果。
deleting from main table
服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables
服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables
正在执行FLUSH TABLES，等待其他线程关闭数据表。
Killed
发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。
MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。
如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked
被其他查询锁住了。
Sending data
正在处理SELECT查询的记录，同时正在把结果发送给客户端。
Sorting for group
正在为GROUP BY做排序。
Sorting for order
正在为ORDER BY做排序。
Opening tables
这个过程应该会很快，除非受到其他因素的干扰。
例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates
正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。
因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table
获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting
修复指令正在排序以创建索引。
Repair with keycache
修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update
正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
Sleeping
正在等待客户端发送新请求。
System lock
正在等待取得一个外部的系统锁。
如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
Upgrading lock
INSERT DELAYED正在尝试取得一个锁表以插入新记录。
Updating
正在搜索匹配的记录，并且修改它们。
User Lock
正在等待GET_LOCK()。
Waiting for tables
该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。
然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。
以下几种情况下会产生这个通知：↓
FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert
INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。
大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。
还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。
=====
更多资料汇总：↓ ↓ ↓
行为不规范-BUG满屏飞 @Coding++
