mysql数据库优化总结

一，对mysql优化是一个综合性的技术，主要包括

• 表的设计合理化(符合3NF)
• 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
• 分表技术(水平分割、垂直分割)
• 读写[写: update/delete/add]分离
• 存储过程 [模块化编程，可以提高速度]
• 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
• mysql服务器硬件升级
• 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

口诀：
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上无计算，范围之后全失效；
like百分加右边，覆盖索引不写星
不等空值还有or，索引失效要少用；
字符串里有引号，SQL高级也不难

下面我们对一些问题做详细解释

• 什么样的表结构才符合3NF范式
  表的范式，是首先符合1NF, 才能满足2NF , 进一步满足3NF

1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF

数据库的分类 关系型数据库: mysql/oracle/db2/informix/sysbase/sql server 非关系型数据库:
(特点: 面向对象或者集合) NoSql数据库: MongoDB(特点是面向文档)

2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现
3NF: 即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放.
反3NF : 但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

索引的代价：
1.占用磁盘空间
2.对dml操作有影响，变慢
索引合适哪些列：
a: 肯定在where条经常使用
b: 该字段的内容不是唯一的几个值(sex)
c: 字段内容不是频繁变化.

• 在开发中，我们经常使用的存储引擎 myisam / innodb/ memory
  myisam 存储: 如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表，回复表.
  INNODB 存储: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.
  问 MyISAM 和 INNODB的区别

1. 事务安全
2. 查询和添加速度
3. 支持全文索引
4. 锁机制
5. 外键 MyISAM 不支持外键， INNODB支持外键. (在PHP开发中，通常不设置外键，通常是在程序中保证数据的一致)
   Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快.
   如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理。并且在mysql5.5之前默认引擎是myisam，之后默认引擎是innodb。

三，索引概念和优化

1.索引的分类

索引一般可分为 主键索引，复合索引（最左前缀原则），普通索引，唯一索引。

2.索引的数据结构

根据数据结构来讲，又分为聚簇索引（主键索引），和辅助索引。

每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。

除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值。在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录，所以会产生回表。

3.覆盖索引

覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但如果我们建立了一个abc的组合索引，如果我查询的时候只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。