【翻译整理】mysql innodb&nb…

---资料整理线---------------------------------

************常规通用知识***************
innodb不支持全文索引
count（*）会扫表，变慢了
auto increament必须只有一个作为主键
innodb delete from table很慢，因为是一条条删除
innodb行锁是基与索引进行锁定的，如果要搞行锁的一定要注意
不要在innodb内做主键值更新
innodb 偏 insert update ，myiasm偏查询
innodb  多条update建议启用事务，否则容易卡慢超时
如果将自动提交关闭（SETAUTOCOMMIT= 0），那么innodb的性能会有显著提高，
但是开发过程中需要底层驱动自动执行提交事务操作否则对数据的更改添加的数据就会丢失
事务会超时，底层一定要做好捕获

 
********表的设计*******
innodb的主键是和数据一起存储的，所以当设计主键的时候可以考虑使用组合主键可以使得相同前缀的数据在一起
主键的无序随机存储会导致系统开销特别大，尽量通过主键排序，读取数据的时候尽量按照主键顺序读取会更快
，如果主键是auto incresement
不要更新主键开销特别大
主键要尽量的短因为其他索引是使用这个主键做索引的，这个会关系到索引的大小，如果索引过大，会导致查询缓慢

没有主键的表查询会慢很多，但是这种情况他内部会创建 一个key ，不过查询的时候不好使用
如果一个表中有主键，同时所有查询都是通过主键进行，即使主键是比较长的，那通过主键进行查询也是更快的。
你可以给主键设置autoincreament并且加入唯一索引


一个库内不要有太多的表
InnoDB自己的表定义（字典）缓存依赖于MySQL的table_cache变量值。只要打开一次，InnoDB就不会从缓存中移除这个表。
每张表大概要消耗4KB以上的空间。MySQL 5.1 的 InnoDB已经将这个空间减少了50% 到 75%，
当重启时，每个表的统计将会被重新计算。所以启动会是非常耗资源的。使用MySQL的插件table_cache将会串行执行这些操作。


*********索引**********
使用唯一索引作为索引，查询速度会快很多
innodb的索引会比myiasm大很多，所以创建索引的时候要注意索引的大小，如果过大可能效果反倒不如没有索引
避免过多的索引
对所有需要更新时查询的列进行索引，如 DELETE FROM users WHERE name=’peter’如果name没有索引会锁定整个表
不要给自动增长字段设置值，否则在insert的时候会锁表，导致并发为1


********关于锁*********
当insert执行的时候拥有autoincrement（不在事务内）有几率锁表，会导致并发插入数据的时候的性能下降（这个问题据说在修5.5以后应该修了）
普通select不会锁表
只对需要的行进行锁定才会获得更好的性能，如果不能规划好解决事务间锁定冲突问题尽量不要使用锁，否则会导致卡住直到超时
共享锁的时候当产生更新到锁定数据的时候会禁止其他读锁访问
缓慢的select查询，不会影响表的update和查询操作
慢查询尽量避免，他会影响到数据一致性，如果使用 事务隔离级别read committed效果会好很多


在read commited模式下select会锁定。因为不能lock一个不存在的行。这个查询结果会跟普通的select是不同的。
SELECT…FOR UPDATE总是不得不访问行数据进行lock，所以这种情况下索引是不会生效的，这样事务会很慢

select没有锁的字段是不会执行锁定的
务必控制好锁定的时间，如果事务执行时间很长，会对系统有很大问题
锁定需要的数据，无关的不要锁定
缩减一个事务的处理粒度对整个表的影响（更改添加）越小越好
使用SELECT…FOR UPDATE如果你想更新大部分你所选择的行
外部锁sELECT GET_LOCK(‘mylock’)可以避免死锁

*********隔离级别********
nnoDB支持很多种的隔离级别。这些隔离级别可以设置为全局有效 也可以针对每个连接和每个事务。

READ UMCOMMITED（不提交读）— 这个是很少使用。其他事务未提交的数据也可以读取到，会影响性能。

READ COMMITED（获取提交的数据）在执行过程中，可以读取到其他事务刚提交（commit）完毕的数据。在mysql5.1，InnoDB会有一些间歇锁在这个级别上。
使用行级复制 和binlog可以避免这个问题。

REPEATABLE READ（可重复读）— 默认的隔离级别。事务内的读都是完全可重复的，即事务只读取事务开始时候的数据状态，正在事务执行的时产生的数据不会出现。

SERIALIZABLE（串行化）– 让所有select都锁住select，尽可能避免使用这个隔离级别。他会导致整个表只有一个select在执行，其他排队

***********外键***********
行更新的时候会检查外键，对性能有影响，现在很少人使用它了
外键的更新会行锁定自己相关数据及其他表相关数据
外键会锁住子表，当父表在更新的时候。（select … for update在父表上这样执行就不会锁住子表


***********事务**********************
在一定数量内的运行中的事务和执行查询，InnoDB性能表现良好
多个运行中查询如果出现相互的性能干扰。通过配置Innodb_thread_concurrency能够控制同事在InnoDB内核中执行查询的线程数量。
过多的事务运行会导致表内大量的锁，建议尽量减少锁在事务中的使用
如果有可能，在同一时间内限制一定数量的查询，在应用程序端做好队列，这样能缓解服务器压力。
处理量过多事务会超时回滚，在你调用的时候驱动要能够捕获这个错误

**********insert by select *****
执行这个操作会导致select查询排队锁定直到他执行完毕
强烈建议使用select into outfile或者load data infile不要直接这么查询


***********间隙锁*************
行锁的时候不仅仅锁定相关数据行，还会锁定两个行锁之间的数据，因为使用REPEATABLE READ隔离模式的时候会出现重复数据。
如果没有使用binlog，那么可以禁止这个间隙锁

*********关于count（*）*****
innodb和myiasm如果count的时候有where条件的时候速度是一样的，如果没有条件那么myiasm速度最快
innodb引擎变量内有一个表内数据总个数，但是这个数值不准
innodb尽量避免使用大量数据的count*查询或者全表



*********推荐开发参考必看的经典***************
http://7567567.blog.51cto.com/706378/586925  这个是下面的翻译，有很多不准确的地方，上面的介绍是我根据下面的的ppt再修正的
http://www.mysqlperformanceblog.com/files/presentations/UC2007-Innodb-Performance-Optimization.pdf


**********系统配置方面优化好文********************
推荐文章地址：http://imysql.cn/node/609
这个介绍的不错，讲了很多配置细节可以参考这个做数据库配置优化,并且有很多优化细节系列文章介绍