(二)mysql 优化相关

mysql查询速度慢的一些可能原因

   1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的的缺陷)

   2、I/O吞吐量小，形成了瓶颈效应。

   3、没有创建计算列导致查询不优化。（计算列创建，例如http://blog.csdn.net/vvhesj/article/details/22749957）

   4、内存不足

   5、网络速度慢

   6、查询出的数据量过大(可以采用多次查询，其他的方式降低数据量)

   7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)

   8、读写竞争资源。（读写分离）

   9、返回了不必要的行和列

   10、查询语句不好，没有优化

mysql优化

   1. 优化数据库对象

   2. 优化SQL

   3. 优化索引

   4. 表锁的问题

   5. MySQL server服务器配置优化

   6. 磁盘IO优化

   7. 应用优化

   8. 对表进行分拆

1 sql优化

   1) 通过show status了解各种sql的执行频率 show status like 'Com_%' 了解 Com_select,Com_insert 可以查看select 语句执行次数。

   2) 通过Explain分析低效的sql语句

   3) 定期分析表和检查表analyze table test_table和check table test_table然后查看Msg_text字段的值是否是ok 

   4）定期优化表 optimize table test_table 如果对表的可变字段varchar blob，text等进行了很多更改， 则应用OPTIMIZE优化。

    在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。        

    OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。  

    对于MyISAM表，OPTIMIZE TABLE按如下方式操作：如果表已经删除或分解了行，则修复表。如果未对索引页进行分类，则进行分类。          如果表的统计数据没有更新（并且通过对索引进行分类不能实现修复），则进行更新。

   5)  优化 order by orgroup by等  

2. 优化数据库对象  

   1）优化表的数据类型,选择合适的数据类型：

 

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

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

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

   2) 字符串数据类型：char，varchar，text选择区别

   (1)长度的区别，char范围是0～255，varchar最长是64k，但是注意 这里的64k是整个row的长度，要考虑到其它的column，还有如果存在not null的时候也会占用一位，对不同的字符集，有效长度还不一样，比如utf8的， 最多21845，还要除去别的column，但是varchar在一般情况下存储都够用了。 如果遇到了大文本，考虑使用longtext，最大能到4G。  

   (2) 效率来说基本是char>varchar>text，但是如果使用的是Innodb引擎的话，推荐使用varchar代替

   (3)默认值  charchar和varchar可以有默认值，text不能指定默认值

   3）MySQL中float数据类型的问题

   (1) FLOAT或DOUBLE列与具有数值类型的数值进行比较，不能使用等式(=) 比较.这个是因为浮点数精度的问题,会产生误差。

   (2)对货币等对精度敏感的数据，应该用定点数表示或存储

   4）在InnoDB数据表设计中，我们需要注意几点：

   1. 显式的定义一个 INT 类型自增字段的主键，这个字段可以仅用于做主键，不做其他用途    

   2. 如果不显式定义主键的话，可能会导致InnoDB每次都需要对新数据行进行排序，严重损害性能     

   3. 尽量保证不对主键字段进行更新修改，防止主键字段发生变化，引发数据存储碎片，降低IO性能     

   4. 如果需要对主键字段进行更新，请将该字段转变成一个唯一索引约束字段，另外创建一个没有其他业务意义的自增字段做主键     

   5. 主键字段类型尽可能小，能用SMALLINT就不用INT，能用INT就不用BIGINT     

   6. 主键字段放在数据表的第一顺序

3. 表锁的问题

   跟性能相关的最重要的区别就是 MyISAM 和 InnoDB 实现的锁机制不 一样！ MyISAM 使用的是表锁， 而 InnoDB实现的是行锁。

   1) MyISAM为表级锁

   由于MyISAM写进程优先获得锁，使得读锁请求靠后等待队列。 不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。 如果在大量更新操作的情况下，使得很难获得读锁。从而造成阻塞。所以MyIsam不适合做大量更新操作的原因

   2 )INNODB的行锁是基于索引实现，如果不通过索引访问数据，Innodb会使用表锁表级锁更适合以查询为主，只有少量按索引条件更新数据的应用。

   行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又并发查询。因为只锁定要操作的行， 所以可以多个线程同时操作不同的行 （只要不操作其他线程已经锁定的行）。

4. MySQL server服务器配置优化

   1）使用show variables 了解服务器参数    

   2）show status 了解服务器运行状态，如锁等待情况，当前连接数 等    

   3）影响mysql性能的重要参数： key_buffer_size 设置索引块的缓存大小：key_buffer_size是对 MyISAM表性能影响最大的一个参数

        通过：

  mysql> show global status like 'key_read%'; +-------------------+------------     + | Variable_name        | Value            | +-------------------+------------+ | Key_read_requests  | 3465117712 | | Key_reads               | 624                | +-------------------+------------      +

   Key_read_requests：从缓存读取索引的请求次数。   

   Key_reads：从磁盘读取索引的请求次数。

   通常人们认为Key_read_requests / Key_reads越大越好

   需要适当加大key_buffer_size  

   table_cache数据库打开表的缓存数量 ，每个连接进来，都会至少打开一个表缓存。因此 table_cache和max_connections有关， 例如 对于200个并行运行的连接，应该让表的缓存至少是200 *N  N 是可以执行查询的一个连接中的表的最大数    

   4)还有innodb_buffer_pool_size等innodb参数的设置

5. 磁盘IO优化

   对于我们数据库调优来说，磁盘I/O优化是首屈一指的调优重点，我们都知道木桶原理，短板绝对整体的好坏，而数据库系统中这个短板正是由于我们 使用的硬件设备里最弱的磁盘所导致。很多时候，我们会发现系统中I/O累得要死，而CPU却在那里空闲等待，主要是由于I/O执行响应时间太长，处 理读写的速度远远赶落后于CPU的处理速度，这时我们会尽可能的让操作 放到内存中进行，由磁盘与CPU的关系，转变成内存与CPU的关系。但是， 我们始终不能回 避磁盘I/O的弱点，优化是必须的。

   磁盘搜索是巨大的性能瓶颈。当数据量变得非常大以致于缓存性能变 得不可能有效时，该问题变得更加明显。对于大数据库，其中你或多或少地 随机访问数据，你可以确 信对读取操作需要至少一次硬盘搜索，写操作需要 多次硬盘搜索。要想使该问题最小化， 应使用搜索次数较少的磁盘。

 

   1）使用磁盘阵列  RAID (廉价磁盘冗余阵列)

         RAID就是按照一定的策略将数据分布到若干物理磁盘上，这样不仅增强 了数据存储的可靠性，而且提高数 据读写的性能 （RAID有不能的级别）          

   1) 读写很频繁的，可靠性要求也很高的，最好RAID 10          

   2) 数据读很频繁，写相对较少的，对可靠性一定要求的，选择RAID 5          

   3) 数据读写都很频繁，但是可靠性要求不高的可以选择RAID 0

   2) 使用符号链接 分布I/O 

         

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

   可以将表和数据库从数据库目录移动到其它的位置并且用指向新位置的符 号链接进行替换。推荐的方法只需要将数据库通过符号链接指到不同的磁盘。符 号链接表仅作为是 最后的办法。

   符号链接一个数据库的方法是，首先在一些有空闲空间的硬盘上创建一个 目录，然后从 MySQL 数据目录中创建它的一个符号链接。

6. 应用优化

   1 )使用连接池对于访问数据库来说，建立连接的代价比较昂贵，因此，我们有 必要建立 " 连接池 " 以提高访问的性能。我们可以把连接当作对象或者设备，池中又有许多已经建立的连接，访问本来需要与数据库的连接的地方，都改为和池相连，池临时分配连接供访问使用，结果返回后，访问将连接交还。

   2)减少对mysql的访问，使用mem或redis缓存等

   3)负载均衡，复制分流查询操作。利用mysql的主从复制，分流更新操作和查询操作          

   3.1), 创建复制账号：Gran replication slave on *.* to 'rel'@'10.0.1.2' identified by '123456'         

   3.2), 修改主服务器的配置my.conf 开启binlog和设置 server-id  

   3.3), 将主服务器的数据一致性恢复到从服务器，保证将要复制的数据时一只的，否则出问题           

   3.4), 在从服务器上修改配置my.conf                server-id=2                master-host=10.0.1.3                master-user='rel'                master-password='123456'                master-port='3306'           

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

   4) 分布式cluster 数据库架构

7. 对表进行分拆

   1）水平划分

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

   2）垂直拆分   

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