mysql优化总结

1.数据库的对象：

    1）选择表合适存储引擎：

            MyISAM:  应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的.

            Innodb： 事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。（Innodb有效地降低删除和更新导致的锁定）

            Memory：数据保存在RAM,快速访问数据。要求表不能太大或者对mysql异常终止后不用恢复数据的

            Merge:      
    2）优化表的数据类型,选择合适的数据类型：

           原则：更小通常更好，简单就好，所有字段都得有默认值,尽量避免null：

           例如：

          数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型.(mediumint就比int更合适)

         比如时间字段：datetime和timestamp
         datetime占用8个字节，而timestamp占用4个字节，只用了一半，而timestamp表示的范围是1970—2037适合做更新时间

2.优化索引：

  

    1）. 普通索引

普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。

   2）. 唯一索引

普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个”员工个人资料”数据表里可能出现两次或更多次。
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

   3）. 主索引

在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引就是所谓的”主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。

  4）. 外键索引

如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

  5）. 复合索引

索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使用复合索引INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用。

 

2,优化sql语句：
  1）通过show status了解各种sql的执行频率
        show status like 'Com_%'
         了解 Com_select,Com_insert 的执行次数
   2)  通过Explain分析低效的sql语句
   3)   建立合适的索引
   4)   通过show status like 'Handler_%'查看索引的使用情况
         handler_read_key表明索引效率的很高
         Handler_read_rnd_key的值很高，表明查询运行效率很低
   5)   定期分析表和检查表
         analyze table test_table和check table test_table
         然后查看Msg_text字段的值是否是ok
   6）定期优化表 optimize table test_table
         如果对表的字段varchar blob，text等进行了很多更改， 则撒花用
    7)  优化 order by orgroup by等

3，锁的问题
   1) MyISAM为表级锁
        由于MyISAM写进程优先获得锁，使得读锁请求靠后等待队列
        如果在大量更新操作的情况下，使得很难获得读锁。从而造成阻塞。
        所以MyIsam不适合做大量更新操作的原因
   2 )INNODB的行锁是基于索引实现，如果不通过索引访问数据，Innodb会使用表锁

4，MySQL server服务器配置优化：
   1）使用show variables 了解服务器参数
   2）show status 了解服务器运行状态，如锁等待情况，当前连接数等
   3）影响mysql性能的重要参数：
         key_buffer_size设置索引块的缓存大小
         table_cache数据库打开表的缓存数量 ，每个连接进来，都会至少打开一个表缓存。因此
         table_cache和max_connections有关， 例如 对于200个并行运行的连接，应该让表的缓存至少是200 *N
         N 是可以执行查询的一个连接中的表的最大数
    4)  还有innodb_buffer_pool_size等innodb参数的设置


5，磁盘io优化
   1）使用磁盘阵列  RAID (廉价磁盘冗余阵列)
         RAID就是按照一定的策略将数据分布到若干物理磁盘上，这样不仅增强了数据存储的可靠性，而且提高数       据读写的性能 （RAID有不能的级别）
         1) 读写很频繁的，可靠性要求也很高的，最好RAID 10
         2) 数据读很频繁，写相对较少的，对可靠性一定要求的，选择RAID 5
         3) 数据读写都很频繁，但是可靠性要求不高的可以选择RAID 0
   
   2) 使用符号链接 分布I/O

       MYSQL在默认的情况下，数据库和数据表都存放在参数datadir定义的目录下，这样如果不使用RAID或者逻辑卷，所有的数据都存放在一个磁盘设备上，无法发挥多磁盘并 行读写的优势。例如：

      ln -s /var/mysql/data/test /tmp/data/test)

   3) 禁止操作系统更新文件的atime属性

6，应有的优化
   1)减少对mysql的访问，使用mem缓存等
   2)负载均衡，复制分流查询操作
           利用mysql的主从复制，分流更新操作和查询操作
          1), 创建复制账号：Gran replication slave on *.* to 'rel'@'10.0.1.2' identified by '123456'
          2), 修改主服务器的配置my.conf 开启binlog和设置server-id
          3), 将主服务器的数据一致性恢复到从服务器，保证将要复制的数据时一只的，否则出问题
          4), 在从服务器上修改配置my.conf
               server-id=2
               master-host=10.0.1.3
               master-user='rel'
               master-password='123456'
               master-port='3306'

          5), 从服务器启动slave线程： start slave
          show processlist 查看。

   3)分布式cluster 数据库架构

 

7、对表进行拆分

   1）水平划分
    如果某个表的数据太多，预期有上千条甚至上亿以上，我们可以化整为0：拆表。
    这里就涉及到拆表的算法：
    记录日志的表，也可以按周或者按月来拆。
    记录用户信息的表，按用户id的hash算法来拆。

    2）垂直拆分
　　如果表记录数并不多，可能也就2、3万条，但是字段却很长，表占用空间很大，检索表时需要执行大量I/O，严重降低了性能。这个时候需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。