MySQL设计指导

mysql数据库加索引（提高效率）：

适合加索引的条件
1）占用存储空间比较少的字段适合选作索引
2）存储空间固定的字段更适合选为索引的关键字 与text类型的字段相比 char类型更适合选为索引
3）where字句中经常使用的字段应该创建索引，两个表连接字段应该创建索引
4）更新频繁的字段不适合当索引，不会出现在where子句的也不适合当索
5）最左前缀原则
6）尽量使用前缀索引

普通索引 添加index
给表 nc_user 的name加索引
alert table nc_user add index index_name(name)

主键索引 添加 primary key
alert table nc_user add primary key (id)

唯一索引 添加unique
alert table nc_user add unique(create_time)

全文索引 添加fulltext
alert table nc_user add fulltext(content)

1.尽量避免过度设计
2.小而简单的合适数据类型
3.避免使用NULL值
4.尽量使用相同的数据类型存储相似或者相关的值(如goods表的id，在订单表中对应的goods_id的值应该都是int(n))
5.注意可变长度字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
6.尽量使用整型定义的标识列(id)
7.避免使用Mysql已经遗弃的特性
8.小心使用ENUM和SET
9.范式是好的，但是反范式有时候也是必须的。
10.根据表不同的功能选择性地使用InnoDB和MyISAM。

mysql的MyISAM 和 InnoDB 的区别
1.InnoDB不支持FULLTEXT类型的索引。

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

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

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

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

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

6.InnoDB 支持事物

选择存储引擎 根据实际情况选择。

一般情况下如果查询多建议使用myIsam 。

如果你需要事务处理或是外键，那么InnoDB 可能是比较好的方式。

优化MYSQL数据库的方法:

1,选取最适用的字段属性,尽可能减少定义字段长度,尽量把字段设置NOT NULL,例如’省份,性别’,最好设置为ENUM

2，使用join代替子查询

3，使用联合(UNION)来代替手动创建的临时表

4，事务处理（保证数据完整性,例如添加和修改同时,两者成立则都执行,一者失败都失败）

5，适当建立索引（如何建立索引？索引的利与弊？）

6，优化sql语句

7，explain可以看到mysql执行计划

8，分表（垂直分表，水平分表？）

9，数据库主从

//时间转成年月日
select date_format(now(),’%Y%m%d’)
//去年此时
select DATE_ADD(now(), Interval -1 year)
//上月此时
select DATE_ADD(now(), Interval -1 month)
//昨天此时
select DATE_ADD(now(), Interval -1 day)
//一小时前
select DATE_ADD(now(), Interval -1 hour)
//一分钟前
select DATE_ADD(now(), Interval -1 minute)
//一秒钟前
select DATE_ADD(now(), Interval -1 second)
//昨天（年月日）
select date_format(DATE_ADD(now(), Interval 1 day),’%Y%m%d’)

//上个月第一天和最后一天
select date_sub(date_sub(date_format(now(),’%Y%m%d’),interval extract( day from now())-1 day),interval 1 month);
select date_sub(date_sub(date_format(now(),’%Y%m%d’),interval extract(day from now()) day),interval 0 month);

//第几周
select weekofyear( now() )
select weekofyear(‘20090606’)