mysql的数据库优化方案_MySql数据库优化方案

1.选择InnoDB引擎

理由：

(1)支持事务

InnoDB与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务(Transaction)功能，类似于PostgreSQL。

(2)支持行锁

MyISAM只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

2.选择自增ID作为主键

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

InnoDB使用聚簇索引，数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子(InnoDB默认为15/16)，则开辟一个新的页(节点)。如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键(如果身份证号或学号等)，由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。因此不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

3.单表字段总的Size不宜过大

主键(即聚簇索引)的B+树上叶子节点与数据存放在一起。每一个节点在数据库中都是一个页，页是innodb磁盘管理最小的单位，innodb每个页的大小是16K。单行数据越小，每个页存储的行数就越多。

如何计算当前表的理论上分表的最大值

在开始这一块之前，我们需要了解以下几个信息：

① 每一个节点在数据库中都是一个页，页是innodb磁盘管理最小的单位，innodb每个页的大小是16K，且不可更改。常见的类型有：数据页 B-tree Node；undo页 Undo Log Page；系统页 System Page；事务数据页 Transaction system Page；插入缓冲位图页 Insert Buffer Bitmap；插入缓冲空闲列表页 Insert Buffer freeBitmap；未压缩的二进制大对象页Uncompressed BLOB Page；压缩的二进制大对象页 Compressed BLOB Page。

② 每个key后有个页号4B，还有6B的其他数据(参考《MySQL技术内幕：InnoDB存储引擎》P193的页面数据)

③ 装载因子(InnoDB默认为15/16)

以下是针对单行数据在300bit大小的数据计算步骤：

�①索引节点：(16000/(8+4+6))15/16=833.33个

�②叶子节点：假设单行数据300b,计算为(16000/(300+8+4+6))15/16=47.17

�③总共三层B+树可存储数据量为：833.33833.3347.17=32756682条数据

根据以上计算步骤，300b数据量情况下理论单表三度索引数据大概是3275w数据

3.联合索引的最左匹配原则

与b+树检索是比较key相关

4.union、in、or 都能够命中索引，建议使用 in

or：新版的 MySQL 能够命中索引

5.负向条件查询不能使用索引，可以优化为 in 查询

负向条件有：!=、<>、not in、not exists、not like 等。

6.把计算放到业务层而不是数据库层

7.强制类型转换会全表扫描

where语句中字段与匹配值类型不同时会触发强制类型转换。

8.更新十分频繁、数据区分度不高的字段上不宜建立索引

更新索引字段值会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。

“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。

9.如果有 order by、group by 的场景，请注意利用索引的有序性

order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。

如果索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b;，索引(a,b)无法排序。

10.对于列的值比较长的索引，使用短索引(又叫前缀索引)来优化索引

11.建立索引的列，不允许为 null

12.单表索引建议控制在5个以内,单索引字段数不允许超过5个

13.如果明确知道只有一条结果返回，limit 1 能够提高效率