Mysql 优化数据库对象及锁问题

优化数据库对象:
1.优化表的数据类型
表需要使用何种数据类型，是需要根据应用来判断的。虽然应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样即浪费存储也浪费内存。
我们可以使用PROCEDURE ANALYSE()对当前已有应用的表类型的判断，该函数可以对数据表中的列的数据类型提出优化建议，可以根据应用的实际情况酌情考虑是否实施优化。
语法：
SELECT * FROM tbl_name PROCEDURE ANALYSE();
SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);
输出的每一列信息都会对数据表中的列的数据类型提出优化建议。第二个例子告诉PROCEDUREANALYSE()不要为那些包含的值多于16个或者256字节的ENUM类型提出建议。
如果没有这样的限制，输出信息可能很长；ENUM定义通常很难阅读。
在对字段类型进行优化时，可以根据统计信息并结合应用的实际情况对其进行优化。

2. 通过拆分，提高表的访问效率
这里我们所说的拆分，主要是针对Myisam类型的表，拆分的方法可以分成两种情况：
1>. 纵向拆分：
纵向拆分是只按照应用访问的频度，将表中经常访问的字段和不经常访问的字段拆分成两个表，经常访问的字段尽量是定长的，这样可以有效的提高表的查询和更新的效率。
2>. 横向拆分：
横向拆分是指按照应用的情况，有目的的将数据横向拆分成几个表或者通过分区分到多个分区中，这样可以有效的避免Myisam表的读取和更新导致的锁问题。

3>. 逆规范化
数据库德规范化设计强调数据的独立性，数据应该尽可能少地冗余，因为存在过多的冗余数据，这就意味着要占用了更多的物理空间，同时也对数据的维护和一致性检查带来了问题。
但是对于查询操作很多的应用，一次查询可能需要访问多表进行，如果通过冗余纪录在相同表中，更新的代价增加不多，但是查询操作效率可以有明显提高，这种情况就可以考虑通过冗余数据来提高效率。

4>. 使用冗余统计表
使用create temporary table语法，它是基于session的表，表的数据保存在内存里面，当session断掉后，表自然消除。
对于大表的统计分析，如果统计的数据量不大，利用insert...select将数据移到临时表中比直接在大表上做统计要效率更高。

5>. 选择更合适的表类型
a.如果应用出现比较严重的锁冲突，请考虑是否更改存储引擎到innodb，行锁机制可以有效的减少锁冲突的出现。
b.如果应用查询操作很多，且对事务完整性要求不严格，则可以考虑使用Myisam存储引擎。

锁问题:
一、获取锁等待情况
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：
mysql> show status like 'Table%';

可以通过检查Innodb_row_lock状态变量来分析系统上的行锁的争夺情况：
mysql> show status like 'innodb_row_lock%';

另外，针对Innodb类型的表，如果需要察看当前的锁等待情况，可以设置InnoDB Monitors，然后通过Show innodb status察看，设置的方式是：
CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
监视器可以通过发出下列语句来被停止：
DROP TABLE innodb_monitor;
设置监视器后，在show innodb status的显示内容中，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等等，便于进行进一步的分析和问题的确定。打开监视器以后，默认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常的巨大，所以我们在确认问题原因之后，
要记得删除监控表以关闭监视器。或者通过使用--console选项来启动服务器以关闭写日志文件。
tail -f /usr/local/mysql/data/`hostname`.err

二、什么情况下使用表锁
表级锁在下列几种情况下比行级锁更优越：
1.很多操作都是读表。
2.在严格条件的索引上读取和更新，当更新或者删除可以用单独的索引来读取得到时：
3.UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
4.DELETE FROM tbl_name WHERE unique_key_col=key_value;
5.SELECT和INSERT语句并发的执行，但是只有很少的UPDATE和DELETE语句。
6.很多的扫描表和对全表的GROUPBY操作，但是没有任何写表。

三、什么情况下使用行锁
行级锁定的优点：
1.当在许多线程中访问不同的行时只存在少量锁定冲突。
2. 回滚时只有少量的更改。
3. 可以长时间锁定单一的行。
行级锁定的缺点：
1.比页级或表级锁定占用更多的内存。
2.当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。
3.如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。
4.用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

四、insert ... select ...带来的问题
当使用insert...select...进行记录的插入时，如果select的表是innodb类型的，不论insert的表是什么类型的表，都会对select的表的纪录进行锁定。

不推荐通过设置这个参数来避免insert...select...导致的锁，如果需要进行可能会扫描大量数据的insert...select操作，我们推荐使用
select...into outfile和load data infile的组合来实现，这样是不会对记录进行锁定的。

五、next-key锁对并发插入的影响
在行级锁定中，InnoDB使用一个名为next-key locking的算法。InnoDB以这样一种方式执行行级锁定：当它搜索或扫描表的索引之时，它对遇到的索引记录设置
共享或独占锁定。因此，行级锁定事实上是索引记录锁定。
可以用next-key锁定在你的应用程序上实现一个唯一性检查：如果你以共享模式读数据，并且没有看到你将要插入的行的重复，则你可以安全地插入你的行，并且
知道在读过程中对你的行的继承者设置的next-key锁定与此同时阻止任何人对你的行插入一个重复。因此，thenext-key锁定允许你锁住在你的表中并不存在的一些
东西。

六、隔离级别对并发插入的影响
    REPEATABLE READ是InnoDB的默认隔离级别。带唯一搜索条件使用唯一索引的SELECT...FOR UPDATE,SELECT...LOCK IN SHARE MODE,UPDATE和DELETE语句只锁定找到的索引记录，而不锁定记录前的间隙。用其它搜索条件，这些操作采用next-key锁定，用next-key锁定或者间隙锁定锁住搜索的索引范围，并且阻止其它用户的新插入。
    在持续读中，有一个与READ COMMITTED隔离级别重要的差别：在这个级别，在同一事务内所有持续读读取由第一次读所确定的同一快照。这个惯例意味着如果你在同一事务内发出数个无格式SELECT语句，这些SELECT语句对相互之间也是持续的。
    READ COMMITTED隔离级别是一个有些象Oracle的隔离级别。所有SELECT...FOR UPDATE和SELECT...LOCK IN SHARE MOD语句仅锁定索引记录，而不锁定记录前的间隙，因而允许随意紧挨着已锁定的记录插入新记录。UPDATE和DELETE语句使用一个带唯一搜索条件的唯一的索引仅锁定找到的索引记录，而不包括记录前的间隙。
    在范围类型UPDATE和DELETE语句，InnoDB必须对范围覆盖的间隙设置next-key锁定或间隙锁定以及其它用户做的块插入。这是很必要的，因为要让MySQL复制和恢复起作用，“幽灵行”必须被阻止掉。

七、如何减少锁冲突
1. 对Myisam类型的表：
1) Myisam类型的表可以考虑通过改成Innodb类型的表来减少锁冲突。
2) 根据应用的情况，尝试横向拆分成多个表或者改成Myisam分区对减少锁冲突也会有一定的帮助。

2. 对Innodb类型的表：
1) 首先要确认，在对表获取行锁的时候，要尽量的使用索引检索纪录，如果没有使用索引访问，那么即便你只是要更新其中的一行纪录，也是全表锁定的。要确保sql
是使用索引来访问纪录的，必要的时候，请使用explain检查sql的执行计划，判断是否按照预期使用了索引。
2) 由于mysql的行锁是针对索引加的锁，不是针对纪录加的锁，所以虽然是访问不同行的纪录，但是如果是相同的索引键，是会被加锁的。应用设计的时候也要注意，这里和Oracle有比较大的不同。
3) 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，当表有主键或者唯一索引的时候，不是必须使用主键或者唯一索引锁定纪录，其他普通索引同样可以用来检索纪录，并只锁定符合条件的行。
4) 用SHOW INNODB STATUS来确定最后一个死锁的原因。查询的结果中，包括死锁的事务的详细信息，包括执行的SQL语句的内容，每个线程已经获得了什么锁，在等待什么锁，以及最后是哪个线程被回滚。详细的分析死锁产生的原因，可以通过改进程序有效的避免死锁的产生。
5) 如果应用并不介意死锁的出现，那么可以在应用中对发现的死锁进行处理。
6) 确定更合理的事务大小，小事务更少地倾向于冲突。
7) 如果你正使用锁定读，（SELECT...FOR UPDATE或...LOCK IN SHARE MODE），试着用更低的隔离级别，比如READ COMMITTED。
8) 以固定的顺序访问你的表和行。则事务形成良好定义的查询并且没有死锁。