MySQL数据库设计需要注意的点

1.为什么字段尽可能用NOT 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.”

-----------------------------------------------

Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引

--------这也是《高性能mysql第二版》介绍的

解读：

“可空列需要更多的存储空间”：需要一个额外字节作为判断是否为NULL的标志位

“需要mysql内部进行特殊处理”：http://www.phpben.com/?post=69 这是mysql索引统计，里面有介绍mysql怎么处理NULL。

注意：但把NULL列改为NOT NULL带来的性能提示很小,除非确定它带来了问题,否则不要把它当成优先的优化措施,最重要的是使用的列的类型的适当性.

引自：http://www.phpben.com/?post=71

2. sequence db (因为partition的原因，not auto_increment)

为每一个id创建sequence表。使用LAST_INSERT_ID()函数和mysql_insertid属性。

3. innodb vs myisam

MyISAM:这个是默认类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法.与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。如果执行大量 的SELECT，MyISAM是更好的选择。

InnoDB:这种类型是事务安全的.它与BDB类型具有相同的特性,它们还支持外键.InnoDB表格速度很快.具有比BDB还丰富的特性,因此如果需 要一个事务安全的存储引擎,建议使用它.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑，应该使用InnoDB表,对于支持事物的InnoDB类型的表，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动Commit，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打 开也可以），将大大提高性能。

1).InnoDB不支持FULLTEXT类型的索引。

2).InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。

3).对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

4).DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

5).LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”

参考：http://www.phpchina.com/archives/view-37224-1.html

4.主键，索引，唯一索引, 外键

普通索引

普通索引（由关键字key或index定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（wherecolumn=）或排序条件（orderbycolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

唯一索引

普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字unique把它定义为一个唯一索引。这么做的好处：一是简化了mysql对这个索引的管理工作，这个索引也因此而变得更有效率；二是mysql会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，mysql将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为 256 字节。大多数存储引擎有更高的限制。在 MySQL 5.1 中，对于 MyISAM 和 InnoDB 表，前缀可以达到 1000 字节长。请注意前缀的限制应以字节为单位进行测量，而 CREATE TABLE 语句中的前缀长度解释为字符数。当为使用多字节字符集的列指定前缀长度时一定要加以考虑。

还可以创建 FULLTEXT 索引。该索引可以用于全文搜索。只有 MyISAM 存储引擎支持FULLTEXT 索引，并且只为 CHAR 、 VARCHAR 和 TEXT 列。索引总是对整个列进行，不支持局 部(前缀) 索引。也可以为空间列类型创建索引。只有 MyISAM 存储引擎支持空间类型。

设计索引的原则
1）. 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的 列 。
2）. 使用惟一索引。考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。例如，存放年龄的列具有不同值，很容易区分各行。而用来记录性别的列，只含有 “ M ” 和 “ F ” ，则对此列进行索引没有多大用处（不管搜索哪个值，都会得出大约一半的行）
3）. 使用短索引。如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做 。例如，如果有一个 CHAR(200) 列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘 I/O 较少，较短的值比较起来更快 。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此， MySQL也可以在内存中容纳更多的值。这增加 了找到行而不用读取索引中较多块的可能性。（当然，应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的 ，因为这个索引中不会有许多不 同的值。）
4）. 利用最左前缀。在创建 一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。（这与索引一个列的前缀不同，索引一个列的前缀是利用该的前 n 个 字符作为索引值。）
5.） 不要过度索引。不要以为 索引 “ 越多越好 ” ，什么东西都用索引是错的。每个额外的 索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍 过。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表 的修改速度。此外， MySQL 在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的 索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左 索引。如果是，则就不要费力去增加这个索引了，因为已经有了。
6）. 考虑在列上进行的比较类型。索引可用于 “ < ” 、 “ < = ” 、 “ = ” 、 “ > = ” 、 “ >” 和 BETWEEN 运算。在模式具有一个直接量前缀时，索引也用于 LIKE 运算。如果只将某个列用于其他类型的运算时（如 STRCMP( ) ），对其进行索引没有价值。

补充：

1.建立索引的时机：若表中的某字段出现在select、过滤、排序条件中，为该字段建立索引是值得的。
2.对于like '%xxx'的模糊查询，普通的索引是无法满足的，需要建立全文索引。
3.对于有多个条件的，比如: "...where a=xxx and b=yyy","...where a=xxx order by b","...where a=xxx group by b"。需要使用组合索引。但是组合索引只能在SQL语句中满足"最左前缀"的条件下使用。且组合索引有一些副作用，如索引尺寸可能比数据本身大，因为组合索引的组合条目多。所以在实际应用中，要量身定做，使用慢查询分析工具分析。
4.开启索引缓存，直接在内存中查找索引，不用再磁盘中。
5.建立索引是有代价的，当update、delete语句执行时，会使得索引更新，将耗掉更多的时间。可以使用mysqlreport报告，了解select、update、delete、insert、replace各语句所占的百分比。

MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。

参考：http://www.phpben.com/?post=74

  http://www.dewen.org/q/397/mysql%E5%BB%BA%E7%AB%8B%E7%B4%A2%E5%BC%95%E6%9C%89%E5%93%AA%E4%BA%9B%E9%9C%80%E8%A6%81%E6%B3%A8%E6%84%8F%E7%9A%84

5、MySQL数据类型及选择

参考《高性能MYSQL》数据类型描述部分。