数据库优化

1.数据表设计
    1)单实例表数目必须小于500
    2)单表列数目必须小于30
    3)表必须有主键，例如自增主键
    4)符合三范式
    5)垂直拆分（解决数据表过宽的问题）
    6)水平拆分（解决数据量过大的问题）
2.字段数据类型
    1)不要将数字存储为字符串类型(原因：字符集和校队规则)
    2)尽可能使用较小的整数类型使表更小。如：mediumint/int，(原因：占用更少磁盘，内存，cup)
    3)针对“性别”，“国家”，“民族”，“状态”应该使用enum而不是varchar
    4)尽可能把字段定义为NOT NULL并且提供默认值
    5)禁止使用ENUM，可使用TINYINT代替
    6)使用可以存下你的数据的最小的数据类型
    7)datetime类型（范围1001-9999）适合用来记录数据的原始的创建时间
      timestamp（范围1970-2038）类型适合用来记录数据的最后修改时间(原因待完善)
    8)存储IPv4使用整型而不是varchar(15)，因为它实际就是32位无符号整数，加小数点只是方便阅读。
    9)尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
    10)尽量使用TIMESTAMP而非DATETIME
3.索引
    1)遵守最左前缀原则
    2)避免重复索引(单个索引和联合索引中索引重复)
    3)避免浪费索引(在性别列添加索引)
    4)在where/group by/order by等字段上面添加索引
    5)单标索引最多6个，不宜过多
    6)禁止在更新十分频繁、区分度不高的属性上建立索引
    7)建立组合索引时，必须把区分度高的字段放在前面(可以缩小查询范围的字段条件)
4.查询语句优化
    1)使用explain,profile进行sql分析
    2)尽量命中索引，避免权标扫描
    3)当确定只有一行数据的时候使用limit 1
    4)禁止使用SELECT *，只获取必要的字段
    5)禁止在WHERE条件的属性上使用函数或者表达式
    6)禁止负向查询(NOT、!=、<>、!<、!>、NOT IN、NOT LIKE、or)，以及%开头的模糊查询,会导致全表扫描
    7)尽量使用TIMESTAMP而非DATETIME
    8)where 子句中对字段进行null值判断将导致引擎放弃使用索引而进行全表扫描
    9)where 子句中对字段进行表达式或函数操作将导致引擎放弃使用索引而进行全表扫描
    10)like 'abc%'不会引起全表扫描,而like '%abc%'会
    11)拆分查询:一条复杂拆分成几条简单的查询
    12)分解关联查询:多表关联，可以对每一个表单查，将结果在应用程序中关联
    13)尽量用union all代替union
    14)有些情况下，可以使用连接来替代子查询
5.配置文件优化
    1)my.cnf配置文件优化
    2)/etc/sysctl.conf系统文件优化
        # 增加tcp支持的队列数
        net.ipv4.tcp_max_syn_backlog=65535
        # 减少断开连接时，资源回收
        net.ipv4.tcp_max_tw_buckets=8000
        net.ipv4.tcp_tw_reuse=1
        net.ipv4.tcp_tw_recycle=1
        net.ipv4.tcp_fin_timeout=10
    3)/etc/security/limits.conf文件数量优化
        soft nofile 65535
        hard nofile 65535
6.服务器磁盘优化
    raid0+raid1
7.其他
    1)分表，分库，分区，分片
    2)读写分离
8.数据库引擎
    MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是
        1)不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁
        2)不支持事务；
        3)不支持外键；
        4)不支持崩溃后的安全恢复；
        5)在表有读取查询的同时，支持往表中插入新纪录；
        6)支持BLOB和TEXT的前500个字符索引，支持全文索引；
        7)支持延迟更新索引，极大提升写入性能；
        8)对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用
    InnoDB在MySQL 5.5后成为默认索引，它的特点是
        1)支持行锁，采用MVCC来支持高并发；
        1)支持事务；
        1)支持外键；
        1)支持崩溃后的安全恢复；
        1)不支持全文索引。
    小结：MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表
9.mysql调优参数
    thread_concurrency：并发线程数，设为CPU核数的两倍。
    max_user_connection：最大连接数，默认为0无上限，最好设一个合理上限。
    wait_timeout：数据库连接闲置时间，闲置连接会占用内存资源。可以从默认的8小时减到半小时。
    innodb_log_buffer_size：InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB。
    skip_name_resolve：禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问。
    table_cache：类似于thread_cache _size，但用来缓存表文件，对InnoDB效果不大，主要用于MyISAM。
    thread_cache_size：保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的。
    record_buffer：每进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果做很多顺序扫描，可能想要增加该值。
10.常用语句优化demo
    1)in优化
      原句：select count(1) from table1 where id in (select id from table2)
      优化：select count(distinct u1.id) from table1 u1 join table2 u2 on u1.id = u2.id
    2)limit优化
      原句：select * from table u order by u.id desc limit 1000,20
      优化：select * from table u where u.id <2020 and u.id >2000 order by u.id limit 2000,20
    3)or优化
      原句：select id from t where num=10 or num=20
      优化：select id from t where num=10  union all  select id from t where num=20
    4)in优化(2)
      原句：select num from a where num in(select num from b)
      优化：select num from a where exists(select 1 from b where num=a.num)
    5)and优化(延迟关联，覆盖查询)
      原句：select * from ta1  where a='**' and b like '**%';
      优化：select * from tal join( select id from tal where a='**' b like '**%' ) as t2 on (t2.id=tal.id);
    6)count优化
      原句：select count（*）from city where id>5;
      优化：select (select count(*) from city) - count(*) from city where id<=5
    7)limit优化
      原句：select id ,name from user order by phone limit 50,5
      优化1(延迟关联)：select a.id,name from user INNER JOIN(select id from user order by phone limit 50,5)as a using(id)
      优化2(转换为已知位置查询)：select id,name from user where position between 50 and 54 order by position;
      优化3(向前翻页)：select id,name from user where id<10040 order by id desc limit 5
11.优秀优化博客
    http://www.cnblogs.com/clsn/p/8214048.html