这两天在看大家都挺推荐的《Mysql技术内幕InnoDB存储引擎》以及其他几本相关的书中对锁，事务，sql调优的章节，对mysql有了更多的理解，零零散散的记一些。

索引

innodb按主键的顺序组织表，即按b+树组织。聚焦索引（可以简单理解为主键）；辅助索引，其叶子节点并不包含行记录的全部数据，叶子结点除了包含键值以外，每个叶子结点中的索引行还包含了一个书签，该书签用来告诉存储引擎可以在哪找到相应的数据行。一般情况下一条sql只能使用一个索引，多个优化器也只会选择最优的那个。

• 主键：非空的唯一索引，innodb里不指定也会自动生成一个6字节大小的指针。
• 联合索引:会按索引字段的前后进行排序和索引，因此注意索引字段的顺序，如索引（A，B，C）where A=? and B=? and C=?可以利用索引，而where B=? and C=?无法利用，并且联合索引对于排序也有好处：where A=? and B=? order by C一样可以利用这个索引。group by的优化也类似。
• 索引覆盖，简单理解就是你的select字段就是索引，那么就用到了索引覆盖。如select id from T where name='twogoods'若name字段上有索引，我们的查询会走索引，在这个索引的叶子节点并不包含所有节点，但包含了找到具体数据的主键信息，这里我们只需要得到主键，因此这个索引的叶子节点上就已经提供了，无需去查找具体的数据行。对于索引覆盖形象点得说法是：我要在书里找一个内容，但是书的目录写的很详细，我通过书就找到了，那就无需去翻到具体的页查看了。

锁

首先关于乐观锁，悲观锁，名字本身很形象了。乐观锁很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。悲观锁很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。我们不显示指定事务时一条sql也是一个事务，一般来说锁是在整个事务执行完被释放，当然也可能因为不同的隔离级别而有所不同。
Innodb是行级锁，S Lock(共享锁)：允许事务读一行数据；X Lock(排他锁)：允许删除更新记录；S与X互斥，S与S兼容，X与X互斥。

• 一致性非锁定读：通过多版本的方式来读取当前执行时间数据库中的数据。正在读取的行被执行update时不等行上锁的释放依然可以操作。
• 一致性锁定读：innodb默认的隔离级别是可重复读，select 时使用一致性非锁定读。如果读时不允许写需要显试加锁：select ... for update 这样对该行加X锁，其他事务对该行的读或写都阻塞；select lock in share mode加S锁，可以同时读，无法写。

个人理解这两种方式就是乐观锁、悲观锁的具体实现；乐观锁在写时不加锁多个线程都可以写，有点像java数据结构里copyonwrite的机制。

字段

• tingyint 1个字节，-128~127；int 4字节 ；bigint8字节；注意取值范围合理选用。
• datatime 8字节，timestamp 4字节，两者都精确到秒，考虑优先使用timestamp。
• varchar 根据需要制定合适的长度，若在此字段上加索引，也注意要索引的长度，长20的索引区分度就能达到90%。

其他

• 类型转换：字段是int的sql中可以使用String来查，只不过mysql帮我们做类型转换而已。
• like：由于b+树索引的结构，like "%a%"是无法通过索引查找的即不能左模糊，like "a%"是可以利用上索引的。不推荐使用like关键字，mysql也提供了全文检索的功能，对中文检索支持并不好，这里有一个中文检索插件，不过感觉要上全文索引，好像都直接上搜索引擎了吧…
• limit：mysql offset并不会真正跳过这么多行，可以先快速定位获取id：select * from T a,(select id from T limit 10000,20)b where a.id=b.id
• count：注意count(*)会统计该行为null的，count(列名)不会统计null；有时候也可以考虑count(辅助索引列)来加快查询。
• in：in里不要有太多的数据，如id in (select id form T)，我当初还犯过很二的错误，in里需要的数据在另一个数据库里来，没办法写到一个sql里，我查了几十万的id如果在sql里使用in，没多久程序就OOM了-_-……