MySQL性能优化

数据库优化的目的

        避免出现页面访问错误

              由于数据库连接timeout产生页面5xx错误

              由于慢查询造成页面无法加载

              由于阻塞造成数据无法提交

        增加数据库的稳定性

               很多数据库问题都是由于低效的查询引起的

        优化用户体验

                流畅页面的访问速度

                良好的网站功能体验

可以从以下几个方面进行数据库优化

SQL及索引优化

        子查询的优化

               通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否有一对多的关系，要注意重复数据。

        优化Limit查询

               优化步骤1：使用有索引的列或主键进行Order by操作

               优化步骤2：记录上次返回的主键，在下次查询时使用主键过滤；避免了数据量大时扫描过多的记录

        如何选择合适的列建立索引？

               1.在where从句，group by从句，order by从句，on从句中出现的列

               2.索引字段越小越好

               3.离散度大的列放到联合索引的前面  

        索引的维护及优化——重复及冗余索引

               重复索引是指相同的列以相同的顺序建立的同类型的索引。

               冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引。

        注：索引是指把你设置为索引的字段A的内容储存在一个独立区间S里，里面只有这个字段的内容。在找查这个与这个字段A的内容时会直接从这个独立区间里查找，而不是去到数据表里查找。找到的这些符合条件的字段后再读取字段A所指向真实的数据记录的物理地址，再把对应的数据内容输出。 如果你查找的不是索引的字段那么他会从数据表里面查找。因为数据表有很多不相关的字段，数据库程序是不会省略不查找。要判断那些不相关的字段以及多次在记录中跳转是花费一定的资源的。 当然不是设置越多索引就越好。因为索引都放到这个独立区间S，独立区间S越大搜索所占的资源就越大。如果你只有一个字段为索引，那么你搜索这个字段是非常快速的。

数据库结构优化

         选择合适的数据类型

         数据类型的选择，重点在于合适二字，如何确定选择的数据类型是否合适？

                1.使用可以存下你的数据的最小的数据类型。

                2.使用简单的数据类型。int要比varchar类型在mysql处理上简单。

                3.尽可能的使用not null定义字段。

                4.尽量少用text类型，非用不可时最好考虑分表。

                使用int来存储日期时间，利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数来进行转换

                使用bigint来存储IP地址，利用INET_ATON(),INET_NTOA()两个函数来进行转换。

         表的垂直拆分

                所谓的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。通常垂直拆分可以按以下原则进行：

                     1.把不常用的字段单独存放到一个表中。

                     2.把大字段独立存放到一个表中。

                     3.把经常一起使用的字段放到一起。

         表的水平拆分

                表的水平拆分是为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的。

操作系统配置优化

      数据库是基于操作系统的，目前大多数MySQL都是安装在Linux系统之上，所以对于操作系统的一些参数配置也会影响到MySQL的性能。下面列出一些常用的系统配置。

       网络方面的配置，要修改/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

       打开文件数的限制，可以使用ulimit -a查看目录的各种限制，可以修改/etc/security/limits.conf文件，增加以下内容以修改打开文件数量的限制。

       * soft nofile 65535

       * hard nofile 65535

       除此之外最好在MySQL服务器上关闭iptables,selinux等防火墙软件。