shard tech decision

We recommend HiveDB plus Dual Master for most installations
Hibernate shard.

local cache with ehcache.
distributed cache with memcached.

　　15、使用索引的缺点
　　1)减慢增删改数据的速度；
　　2）占用磁盘空间；
　　3）增加查询优化器的负担；
　　当查询优化器生成执行计划时，会考虑索引，太多的索引会给查询优化器增加工作量，导致无法选择最优的查询方案；
　　
　　16、分析索引效率
　　方法：在一般的SQL语句前加上explain；
　　分析结果的含义：
　　1）table：表名；
　　2）type：连接的类型，(ALL/Range/Ref)。其中ref是最理想的；
　　3）possible_keys：查询可以利用的索引名；
　　4）key：实际使用的索引；
　　5）key_len：索引中被使用部分的长度（字节）；
　　6）ref：显示列名字或者"const"（不明白什么意思）；
　　7）rows：显示MySQL认为在找到正确结果之前必须扫描的行数；
　　8）extra：MySQL的建议；
　　
　　17、使用较短的定长列
　　1）尽可能使用较短的数据类型；
　　2）尽可能使用定长数据类型；
　　a）用char代替varchar，固定长度的数据处理比变长的快些；
　　b）对于频繁修改的表，磁盘容易形成碎片，从而影响数据库的整体性能；
　　c）万一出现数据表崩溃，使用固定长度数据行的表更容易重新构造。使用固定长度的数据行，每个记录的开始位置都是固定记录长度的倍数，可以很容易被检测到，但是使用可变长度的数据行就不一定了；
　　d）对于MyISAM类型的数据表，虽然转换成固定长度的数据列可以提高性能，但是占据的空间也大；
　　
　　18、使用not null和enum
　　尽量将列定义为not null，这样可使数据的出来更快，所需的空间更少，而且在查询时，MySQL不需要检查是否存在特例，即null值，从而优化查询；
　　如果一列只含有有限数目的特定值，如性别，是否有效或者入学年份等，在这种情况下应该考虑将其转换为enum列的值，MySQL处理的更快，因为所有的en

um值在系统内都是以标识数值来表示的；



If you do store UUID values, you should remove the dashes or, even better, convert
the UUID values to 16-byte numbers with UNHEX( ) and store them in a
BINARY(16) column. You can retrieve the values in hexadecimal format with the
HEX( ) function.
Values generated by UUID( ) have different characteristics from those generated
by a cryptographic hash function such ash SHA1( ): the UUID values are unevenly
distributed and are somewhat sequential. They’re still not as good as a monotonically
increasing integer, though.

use Hash indexes when possible

Isolate the Column

If you’re using InnoDB and don’t need any particular clustering, it can be a good idea
to define a surrogate key, which is a primary key whose value is not derived from
your application’s data. The easiest way to do this is usually with an AUTO_INCREMENT
column. This will ensure that rows are inserted in sequential order and will offer better
performance for joins using primary keys.
It is best to avoid random (nonsequential) clustered keys. For example, using UUID
values is a poor choice from a performance standpoint: it makes clustered index
insertion random, which is a worst-case scenario, and does not give you any helpful
data clustering.
To demonstrate,

Where possible, try to extend existing indexes rather than adding new ones. It is usually
more efficient to maintain one multicolumn index than several single-column
indexes. If you don’t yet know your query distribution, strive to make your indexes as
selective as you can, because highly selective indexes are usually more beneficial

multicolumn index than several single-column
As a summary: Use multi column indexes is typically best idea if you use AND between such columns in where clause. Index merge does helps performance but it is far from performance of combined index in this case. In case you're using OR between columns - single column indexes are required for index merge to work and combined indexes can't be used for such queries

high performance

You may have noticed that we’re keeping the age column at the end of the index.

Avoiding Multiple Range Conditions

We can add special indexes for sorting these low-selectivity cases. For example, an
index on (sex,rating) can be used for the following query:
mysql> SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 10;

Clustering by primary key
All InnoDB tables are clustered by the primary key, which you can use to your
advantage in schema design.
All indexes contain the primary key columns
so if you don’t keep your primary
key short, the indexes will grow very large.

Slow data load
As of MySQL 5.0, InnoDB does not specially optimize data load operations. It
builds indexes a row at a time, instead of building them by sorting. This may
result in significantly slower data loads.

Foreign keys aren’t free.
Instead of using foreign keys as constraints, it’s often a good idea to constrain the
values in the application.