库表设计
- 库名、表名、字段名使用小写字母，”_”分割，不超过18 个字符，使用名词且见名知意. 不使用temp、old、new等带有误导性的关键词作为表名的一部分; 新建表必须有用途的注释说明，便于日后维护
- 默认使用innodb 存储引擎，使用其他引擎必须注明缘由【FAQ】
- 存储精确浮点数使用DECIMAL,替代FLOAT 和DOUBLE
- 使用int unsigned 存储IP 地址【FAQ】
- 根据字段长度选择合适的字段类型，如数字类型有tinyint，smallint，mediuint，int，
bigint 五种类型，分别占用1byte，2byte，3byte，4byte，8byte。需要特别注意，
int（10）和int（2）无区别，应该采用tinyint（2）替代int（2）. - 尽量使用tinyint 代替enum 和set 类型，减少后台类型转换
- 尽量避免使用text、blob 字段类型
- Varchar(N)中，N 表示的是字符数不是字节数，如varchar（255），可以最大存储
255 个汉字。N 值应尽可能小，单表varchar 字段最大长度为65536 个字节，在排序和创建临时表等内存操作时，会使用N 值来申请内存，而非存储值的实际长度 - 表字符集统一使用utf8，客户端一致（--default-character-set=utf8）,
可以规避很多中文引起的业务问题 - 存储年使用year 类型，存储日期使用date 类型，存储时间（精确到秒）使用timestamp 类型，而非datetime 类型。因为timestamp 使用4 个字节，而datetime 使用8 个字节【FAQ】.
- 字段全部定义为NOT NULL【FAQ】
- 将过大字段拆分到其他表中，不在数据库中存储图片、文件等内容
- 固定长度的表会更快【FAQ】
- 尽量避免使用外键【FAQ】
索引设计：
1、索引命名规范：
1.1、 索引名称全部使用小写；
1.2、非唯一索引按照“ix_字段名称字段名称[字段名称]”进行命名；
1.3、唯一索引按照“uq_字段名称字段名称[字段名称]”进行命名。
2、 唯一索引由3 个以下字段组成并且字段都是整形时，使用唯一索引作为主键。没有唯一索引或唯一索引不符合上述条件时，使用自增id 作为主键。注意唯一索引不和主键重复
【FAQ】
3、单张表的索引数量控制在字段数的20%以内，至多5 个，索引数量过多会导致写入性能的显著下降
4、合理创建复合索引。首先要避免冗余，ix_a_b_c 相当于同时创建了ix-
_a,ix_a_b,ix_a_b_c 三个索引；其次要避免索引过大，建议最大4 列复合，列数过多很难提升索引的区分度，反而降低索引的性能
5、合理使用覆盖索引
6、对于长度大于100 的varchar 字段建立索引时，使用其他方法【FAQ】
7、使用EXPLAIN 判断SQL 语句是否合理使用索引，尽量避免extra 列出现FILE SORT,USING
TEMPORARY【FAQ】
8、索引不只用于select 查询，update 和delete 语句也需要根据where 条件合理设计索引
9、where 条件中的非等值条件（IN,BETWEEN,<,<=,>,>=）会导致后面的条件无法使用索引
SQL 语句设计
1、使用prepared statement，可以提升性能并且避免SQL 注入【FAQ】
2、降低SQL 的复杂度，把MySQL 尽量当做存储使用：
2.1、避免在SQL 语句中进行数学运算、函数计算、逻辑判断等操作
2.2、避免多表join，尽量拆分成多条查询。如无法避免，在join 表时应使用相同类型的列，并且在列上有索引【FAQ】
2.3、避免使用存储过程、触发器、函数等
3、Insert 语句使用batch 提交（insert into table values （），（），（），……）,values 的个数不超过500；sql 语句中in 包含的值不超过500
4、Update，delete 语句避免使用limit，如果确实需要分配处理大量数据，可以增加其他字段来限制每次处理的记录数，比如主键id
5、避免使用select 【FAQ】
6、避免使用order by rand（），使用其他方式替换【FAQ】
7、使用合理的分页方式以提高分页的效率
8、统计表中记录数时使用count(),而不是count（pk）或count（1）
9、数据库默认开启查询缓存，合理利用查询缓存提升sql 效率【FAQ】
10、当只需要1 行数据时使用 limit 1【FAQ】
11、拆分大的delete 和insert【FAQ】
12、Where 条件中使用合适的类型，数值不加引号，字符加引号，避免MySQL 进行隐式类型转换，从而无法使用索引【FAQ】
13、避免使用or，对同一个字段将or 改为in，对不同字段将or 改为union【FAQ】
14、尽量避免负向查询，如NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE 等
15、针对同一张表的alter table 操作，应该用逗号分隔，一次完成
16、注意MySQL 中insert ignore into；insert on duplicate key update；replace into 的区别，在应用中合理使用【FAQ】
17、从避免死锁的角度考虑：避免极短时间内重复提交相同的删除sql；避免执行空删除语句（指执行delete语句实际删除行数为0的情况）。
18、商业平台数据库目前依赖触发器来生成报文。但有些sql会生成row格式binlog，从而造成触发器无法执行。为避免这种情况，主要是避免如下几种sql写法：
18.1.避免一些不确定函数的使用：
LOAD_FILE()； UUID()； USER()； current_user()；FOUND_ROWS()；SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
18.2. 避免用a表数据更新b表的场景：
A．语句INSERT ...SELECT
B．如下格式的UPDATE：
UPDATE a , b SET a. date=b. date, a.ctype=b.ctype WHERE a.id = b.id AND a.ddate <> b.ddate ;
分表设计
1、单表数据量控制在500w 以下，如果字段全部为int 类型，控制在500w 以下
2、 避免使用MySQL 自带的分区表功能，单表数据量时通过程序来分表，使用hash 分表时，
表名后缀使用16 进制表示，如user_ff;使用日期分表时，表明后缀使用日期，如
user_20130707 或user_201307
其他
1、 尽量减小事务：事务使用原则是即开即用，用完即关 ；事务无关操作放到事务外面, 减少锁资源的占用；在不破坏一致性前提下，使用多个短事务代替长事务
2、可重复读（repeatable read）是MySQL 的默认事务隔离级别，解决了脏读（dirty read）
和幻读（phantom read）的问题，原则上禁止修改事务的隔离级别。【FAQ】
3、尽量使用短连接，完成查询后要主动释放连接，避免MySQL 中出现大量sleep 线程
4、不管使用连接池还是直连MySQL，执行查询前都需要考虑获取的MySQL 连接可能已经断开，如断开可以重连。执行查询后都要检查查询是否成功，如不成功，考虑是否需要重新执行。
5、如果触发器建立在从库，则binlog_format不能设置为row模式，否则无法触发。
FAQ
FAQ1.2
从安全性和性能两个角度都建议使用InnoDB 引擎。首先数据量较大的时候，系统崩溃
后如何快速恢复是一个重要问题。相对而言，MyISAM 崩溃后发生损坏的概率比InnoDB 高
得多，而且恢复速度慢，可能丢失数据。因此，即使不需要支持事务，也推荐使用innoDB.
另外为改善InnoDB 的性能，Oracle 投入了大量资源，对Innodb 内部做了大量优化，使得
其性能在绝大部分场景远高于MyISAM。
FAQ1.4
使用INT UNSIGNED 而不是char(15)来存储ipv4 地址，通过MySQL 函数inet_ntoa 和
inet_aton 来进行转化。Ipv6 地址目前没有转化函数，需要使用DECIMAL 或者两个bigINT 来
存储。例如：
SELECT INET_ATON('209.207.224.40');
3520061480
SELECT INET_NTOA(3520061480);
209.207.224.40
FAQ1.10
INT[M]，M值代表什么含义？
注意数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如INT(3)默认显示3位，空格补齐，超出时正常显示，python、java客户端等不具备这个功能。
为什么建议使用TIMESTAMP来存储时间而不是DATETIME？
DATETIME和TIMESTAMP都是精确到秒，优先选择TIMESTAMP，因为TIMESTAMP只有4个字节，而DATETIME8个字节。同时TIMESTAMP具有自动赋值以及自动更新的特性。
如何使用TIMESTAMP的自动赋值属性？
a) 将当前时间作为ts的默认值：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP。
b) 当行更新时，更新ts的值：ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP。
c) 可以将1和2结合起来：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
FAQ1.11
除非有很特别的原因去使用 NULL 值，最好总是让字段保持 NOT NULL。
首先，确定“Empty”和“NULL”有多大的区别（如果是INT，那就是0 和NULL）？如果
觉得它们没有什么区别，那么就不要使用NULL。（在 Oracle 里，NULL 和 Empty 的字符串
是一样的！)。NULL 的存储需要额外的空间，并且，在进行比较的时候，会增加程序的复
杂性。 当然，这里并不是说就不能使用NULL 了，现实情况是很复杂的，依然会有些情况
下，你需要使用NULL 值。
下面摘自MySQL 官方文档：
“NULL columns require additional space in the row to record whether their values are NULL. For
MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”
FAQ1.13
如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixedlength”
。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。固定长度的表会提高性能，因为MySQL 搜寻得会更快一些，因为这些固定的长度是很容易
计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。
FAQ1.14
外键能够简化开发，但是外键在高并发下带来严重的锁问题，对性能影响极大，可以考虑
通过程序来保证约束条件。
FAQ2.2
建议使用自增id 作为主键，以提升写入性能。以写入10 万行数据为例，对比如下：
主键字段写入时间
Int（10）auto_increment 133 秒
Varchar（36）： 147 秒
小结：InnoDB 是聚集索引，写入速度严重依赖于写入顺序，按照主键的顺序写入是最快的方式。
FAQ2.6
可以考虑使用前缀索引或模拟hash 索引
下面的表增加一列url_crc32，然后对url_crc32建立索引，减少索引字段的长度，提高效率。
CREATE TABLE url(
url VARCHAR(255) NOT NULL DEFAULT ‘’,
url_crc32 INT UNSIGNED NOT NULL DEFAULT 0,
……
index idx_url(url_crc32) )
FAQ2.7
EXPLAIN 语句（在MySQL 客户端中执行）可以获得MySQL 如何执行SELECT 语句的信息。
通过对SELECT 语句执行EXPLAIN，可以知晓MySQL 执行该SELECT 语句时是否使用了索引、全表扫描、临时表、排序等信息。尽量避免MySQL 进行全扫描、使用临时表、排序等。
详见官方文档。
FAQ3.1
Prepared Statements 很像存储过程，是一种运行在后台的SQL 语句集合，我们可以从使用prepared statements 获得很多好处，无论是性能问题还是安全问题。比如可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL 注入式”攻击。当然，你也可以手动地检查你的这些变量，然而，手动的检查容易出问题，而且很经常会被程序员忘了。当我们使用framework 或是ORM 的时候，这样的问题会好一些。在性能方面，当一个相同的查询被使用多次的时候，这会为你带来可观的性能优势。你可以给这些Prepared Statements 定义一些参数，而MySQL 只会解析一次。虽然最新版MySQL 在传输Prepared Statements 是使用二进制形势，所以这会使得网络传输非常有效率。
FAQ3.2.2
如果应用程序有很多 JOIN 查询，应该确认两个表中Join 的字段是被建过索引的。这样，MySQL 内部会启动为你优化Join 的SQL 语句的机制。而且，这些被用来Join 的字段，应该是相同的类型的。例如：如果要把 DECIMAL 字段和一个 INT 字段Join 在一起，MySQL 就无法使用它们的索引。对于那些STRING 类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）
// 在state 中查找company
$r = mysql_query("SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $user_id");
// 两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集。
FAQ3.5
从数据库里读出越多的数据，那么查询就会变得越慢。并且还会增加网络传输的负载。减少使用覆盖索引完成查询的可能性。所以，应该养成需要什么就取什么的好习惯。
// 不推荐
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// 推荐
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
FAQ3.6
想打乱返回的数据行？随机挑一个数据？方便用法的后面有非常可怕的性能问题。如果真的想把返回的数据行打乱，有N 种方法可以达到这个目。这样使用会让你的数据库性能呈指数级的下降，具体问题是：MySQL 会不得不去执行RAND()函数（很耗CPU 时间），而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1 也无济于事（因为要排序，很耗IO）
下面的示例是随机挑一条记录
// 千万不要这样做：
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// 这要会更好：
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
FAQ3.9
某些查询语句会让MySQL 不使用缓存。请看下面的示例：
// 不能够使用缓存
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// 能够使用查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
上面两条SQL 语句的差别就是 CURDATE() ，MySQL 的查询缓存对这个函数不起作用。所以，
像 NOW() 和 RAND() 或是其它的诸如此类的SQL 函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL 的函数，从而使用缓存。
FAQ3.10
当你查询表的有些时候，你已经知道结果只会有一条结果， 在这种情况下，加上 LIMIT 1可以增加性能。此时，MySQL 数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。
下面的示例，只是为了找一下是否有“中国”的用户，很明显，后面的会比前面的更有效
率。
// 没有效率的：
$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
if (mysql_num_rows($r) > 0) {
// ...
}
// 有效率的：
$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
if (mysql_num_rows($r) > 0) {
// ...
}
FAQ3.11
如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。
如果你把你的表锁上一段时间，比如30 秒钟，那么对于一个有很高访问量的站点来说，这30 秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让你WEB 服务Crash，还可能会让你的整台服务器马上掛了。
所以，如果你有一个大的处理，你定你一定把其拆分，使用 LIMIT 条件是一个好的方法。
下面是一个示例：
while (1) {
//每次只做1000 条
mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
if (mysql_affected_rows() == 0) {
// 没得可删了，退出！
break;
}
// 每次都要休息一会儿
usleep(50000);
}
FAQ3.12
因为MySQL 进行隐式类型转化之后，可能会将索引字段类型转化成”=号”右边值的类型，导致使用不到索引.
FAQ3.13
OR 的时间复杂度为0(n),in 的时间复杂度为0(log n),也就是说用in 效率更高.
例子：
使用in 提升性能
Select * from opp WHERE phone=‘12347856' or phone=‘42242233';修改为
Select * from opp WHERE phone in ('12347856' , '42242233')
Merge index 效果不好，使用union all 提升性能。
例子:
Select * from opp WHERE phone='010-88886666' or cellPhone='13800138000'; 修改为
Select * from opp WHERE phone='010-88886666' union all Select * from opp WHERE
cellPhone='13800138000';
FAQ3.16
1.insert ignore into
当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。所以使用ignore 请确保语句本身没有问题，否则也会被忽略掉。
2.on duplicate key update
当primary key 或者unique key 重复时，则执行update 语句, 注意如果多个行匹配，只更新1 行，所以应避免对带有多个unique key 的表使用ON DUPLICATE KEY 子句。
3.replace into
如果存在primary key or unique key 相同的记录，则先全部删除掉，再插入新记录。
FAQ5.2
大多数数据库系统的默认隔离级别是提交读（READ COMMITTED），但MySQL 是可重复读（repeatable read）。提交读这个事务隔离级别也叫做不可重复读。如果业务场景确实因为大量的并发插入导致锁问题严重，理论上可以通过降低隔离级别到提交读，减少锁争用，但是降级一方面会产生幻读的问题，另一方面必须工作在MySQL 的binlog 格式是row 的情况下，否则会造成主从同步中断。所以，从数据安全性角度出发，原则上禁止修改事务的隔离级别，性能问题尽量通过优化业务逻辑来解决。
