mysql如何优化

转载▼

 

1.数据库的对象：

      1）优化表的类型：

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

      3) 所有字段都得有默认值

      4) 选择合适表类型（InnoDB或者myisam）

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 数据库架构

本文出自 “ghost” 博客，请务必保留此出处http://caizi.blog.51cto.com/5234706/1531411