大型网站建设千万级数据量MYSQL数据库优化方法

在我们做网站建设的时候，有些网站的数据量非常大，达到千万级以上，如果数据库设计时不进行优化，会导致网站交互时速度变慢，而且是慢到几乎无法忍受的地步，甚至不能正常访问。那要怎么样优化呢？

配置规范

1、保证字符集设置统一，数据库、表的字符集使都用UTF8，应用程序连接、展示等可以设置字符集的地方也都统一设置为UTF8字符集。

2、数据库、表数量尽可能少；数据库一般不超过50个，每个数据库下，数据表数量一般不超过500个（包括分区表）。

建表规范

1、存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。

2、整型定义中无需定义显示宽度，比如：使用INT，而不是INT(11)。

3、禁止用数据库存储图片或文件或超大字符串，尽可能不使用TEXT、BLOB类型，如果必须使用，建议将过大字段或是不常用的描述型较大字段拆分到其他表中。

4、字段尽量不用NULL，可以设置默认值为0。

命名规范

1、库、表、字段全部采用小写。

2、库名、表名、字段名、索引名称均使用小写字母，并以“_”分割。

3、库名、表名、字段名建议不超过12个字符。

4、库名、表名、字段名见名知意，不需要添加注释。

索引规范

1、索引建议命名规则：idx_col、uniq_col，字段过长采用缩写。

2、建立复合索引时，优先将选择性高的字段放在前面。

3、UPDATE、DELETE语句需要根据WHERE条件添加索引。

4、对较小的数据列使用索引，这样会使索引文件更小，同时内存中也可以装载更多的索引键。

5、避免在索引字段上使用函数，否则会导致查询时索引失效。。

6、单张表的索引个数控制在5个以内。

7、使用组合索引，可以减少文件索引大小，在使用时速度要优于多个单列索引。

8、InnoDB表一般都建议有主键列，尤其在高可用集群方案中是作为必须项的。

应用优化

1、避免使用存储过程、触发器、自定义函数等，容易将业务逻辑和DB耦合在一起，后期做分布式方案时会成为瓶颈。

2、考虑使用UNION ALL，减少使用UNION，因为UNION ALL不去重，而少了排序操作，速度相对比UNION要快，如果没有去重的需求，优先使用UNION ALL。

3、考虑使用limit N，少用limit M，N，特别是大表或M比较大的时候。

4、减少或避免排序，如：group by语句中如果不需要排序，可以增加order by null。

5、统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)；InnoDB表避免使用COUNT(*)操作，计数统计实时要求较强可以使用Memcache或者Redis，非实时统计可以使用单独统计表，定时更新。

6、做字段变更操作（modify column/change column）的时候必须加上原有的注释属性，否则修改后，注释会丢失。

7、使用prepared statement可以提高性能并且避免SQL注入。

8、SQL语句中IN包含的值不应过多。

9、UPDATE、DELETE语句一定要有明确的WHERE条件。

10、WHERE条件中的字段值需要符合该字段的数据类型，避免MySQL进行隐式类型转化。

11、SELECT、INSERT语句必须显式的指明字段名称，禁止使用SELECT * 或是INSERT INTO table_name values()。

12、INSERT语句使用batch提交（INSERT INTO table_name VALUES(),(),()……），values的个数不应过多。