mysql优化

MySQL与优化技术
MySQL历史
   1979年， 报表工具，数据引擎
   1996年，MySQL 1.0 (3.11.1) 发布，支持SQL
   2000年，成立 MySQL AB 公司
   2008年1月，Sun公司以10亿美元收购MySQL AB公司
   2009年4月，Oracle公司以74亿美元收购Sun公司
MySQL里程碑
   3.11.1 First public release
   3.23 集成Berkeley DB, 支持事务，抽象出Storage Engine
   4.0 集成InnoDB
   4.1 重大改进，子查询、unicode、c/s通信协议
   5.0 stored procedure、view、triggers、query optimizer
   5.1 File NDB、record replication
MySQL架构
MySQL存储引擎比较
MyISAM vs InnoDB
MyISAM 特点
   数据存储方式简单，使用 B+ Tree 进行索引
   使用三个文件定义一个表：.MYI   .MYD   .frm
   少碎片、支持大文件、能够进行索引压缩
   二进制层次的文件可以移植 (Linux  Windows)
   访问速度飞快，是所有MySQL文件引擎中速度最快的
   不支持一些数据库特性，比如 事务、外键约束等
   Table level lock，性能稍差，更适合读取多的操作
   表数据容量有限，一般建议单表数据量介于 50w–200w
   增删查改以后要使用 myisamchk 检查优化表
InnoDB 特点
   使用 Table Space 的方式来进行数据存储 (ibdata1, ib_logfile0)
   支持 事务、外键约束等数据库特性
   Rows level lock , 读写性能都非常优秀
   能够承载大数据量的存储和访问
   拥有自己独立的缓冲池，能够缓存数据和索引
   在关闭自动提交的情况下，与MyISAM引擎速度差异不大

MyISAM vs InnoDB 性能测试     
   数据量/单位:万 MyISAM InnoDB 备注: my.cnf 特殊选项
   插入：1w 3秒 219秒 innodb_flush_log_at_trx_commit=1
   插入：10w 29 秒 2092秒 innodb_flush_log_at_trx_commit=1
   插入：100w 287秒 N/A innodb_flush_log_at_trx_commit=1
   插入：1w 3秒 3秒 innodb_flush_log_at_trx_commit=0
   插入：10w 30秒 29秒 innodb_flush_log_at_trx_commit=0
   插入：100w 273秒 423秒 innodb_flush_log_at_trx_commit=0
   插入：1w N/A 3秒 innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size=256M
   插入：10W N/A 26秒 innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size=256M
   插入：100W N/A 379秒 innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size=256M

MyISAM vs InnoDB 性能测试
   测试结果
   可以看出在MySQL 5.0里面，MyISAM和InnoDB存储引擎性能差别并不是很大，针对InnoDB来说，影响性能的主要是
innodb_flush_log_at_trx_commit 这个选项，如果设置为1的话，那么每次插入数据的时候都会自动提交，导致性能急剧下降，应该是跟刷新日志有关系，设置为0效率能够看到明显提升，当然，同样你可以SQL中提交“SET AUTOCOMMIT = 0”来设置达到好的性能。
同时也可以看出值得使用 InnoDB 来替代 MyISAM 引擎来进行开发，毕竟InnoDB 有多数据库特性、更良好的数据存储性能和查询性能


MySQL优化方式
    系统优化：硬件、架构
    服务优化
    应用优化

系统优化
     使用好的硬件，更快的硬盘、大内存、多核CPU，专业的存储服务器（NAS、SAN）

     设计合理架构，如果 MySQL 访问频繁，考虑 Master/Slave 读写分离；数据库分表、数据库切片（分布式），也考虑使用相应缓存服务帮助 MySQL 缓解访问压力

服务优化
     MySQL配置原则
     配置合理的MySQL服务器，尽量在应用本身达到一个MySQL最合理的使用
    针对 MyISAM 或 InnoDB 不同引擎进行不同定制性配置
    针对不同的应用情况进行合理配置
    针对 my.cnf 进行配置，后面设置是针对内存为2G的服务器进行的合理设置
   
服务优化公共选项
     选项 缺省值 推荐值 说明
    max_connections 100 1024 MySQL服务器同时处理的数据库连接的最大数量
    query_cache_size 0 (不打开） 16M 查询缓存区的最大长度，按照当前需求，一倍一倍增加，本选项比较重要
    sort_buffer_size 512K 16M 每个线程的排序缓存大小，一般按照内存可以设置为2M以上，推荐是16M，该选项对排序order by，group by起作用
    record_buffer 128K 16M 每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区，可以设置为2M以上
    table_cache 64 512 为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符。
   
MyISAM 选项
     选项 缺省值 推荐值 说明
    key_buffer_size 8M 256M 用来存放索引区块的缓存值, 建议128M以上，不要大于内存的30%
    read_buffer_size 128K 16M 用来做MyISAM表全表扫描的缓冲大小. 为从数据表顺序读取数据的读操作保留的缓存区的长度
    myisam_sort_buffer_size 16M 128M 设置,恢复,修改表的时候使用的缓冲大小，值不要设的太大

应用优化
   应用优化方式
    数据库存储优化
    设计合理的数据表结构
    建立合适有效的数据库索引
    数据查询优化
    关于存储过程的优化

数据库存储优化
    数据库对象的放置策略是均匀地把数据分布在系统的磁盘中，平衡I/O
       访问，避免I/O瓶颈。
    访问分散到不同的磁盘，即使用户数据尽可能跨越多个设备，多个I/O
       运转，避免I/O竞争，克服访问瓶颈；分别放置随机访问和连续访问据。
    分离系统数据库I/O和应用数据库I/O。把系统审计表和临时库表放在不
       忙的磁盘上。
    把事务日志放在单独的磁盘上，减少磁盘I/O开销，这还有利于在障碍后
       恢复，提高了系统的安全性。
    把频繁访问的“活性”表放在不同的磁盘上；把频繁用的表、频繁做
      Join*作的表分别放在单独的磁盘上，甚至把把频繁访问的表的字段放在
      不同的磁盘上，把访问分散到不同的磁盘上，避免I/O争夺；
    利用段分离频繁访问的表及其索引（非聚族的）、分离文本和图像数
     据。段的目的是平衡I/O，避免瓶颈，增加吞吐量，实现并行扫描，提高 并发度，最大化磁盘的吞吐量。利用逻辑段功能，分别放置“活性”表及其非聚族索引以平衡I/O。当然最好利用系统的默认段。另外，利用段可以使备份和恢复数据更加灵活，使系统授权更加灵活。

表结构设计原则
    选择合适的数据类型：如果能够定长尽量定长
   不要使用无法加索引的类型作为关键字段，比如 text类型
   为了避免联表查询，有时候可以适当的数据冗余，比如     邮箱、姓名这些不容易更改的数据
   选择合适的表引擎，有时候 MyISAM 适合，有时候     InnoDB适合
   为保证查询性能，最好每个表都建立有 auto_increment     字段， 建立合适的数据库索引
   最好给每个字段都设定 default 值

表结构设计原则

     选择合适的数据类型：如果能够定长尽量定长
   不要使用无法加索引的类型作为关键字段，比如 text类型
   为了避免联表查询，有时候可以适当的数据冗余，比如     邮箱、姓名这些不容易更改的数据
   选择合适的表引擎，有时候 MyISAM 适合，有时候     InnoDB适合
   为保证查询性能，最好每个表都建立有 auto_increment     字段， 建立合适的数据库索引
   最好给每个字段都设定 default 值

索引建立原则
     一般针对数据分散的关键字进行建立索引
   尽量使用短索引，一般对int、char/varchar、date/time 等      类型的字段建立索引
   需要的时候建立联合索引，但是要注意查询SQL语句的编写
   谨慎建立 unique 类型的索引（唯一索引）
   一般建议每条记录最好有一个能快速定位的独一无二定位的      唯一标示（索引）
   不要过度索引，单表建立的索引不要超过5个，否则更新索      引将很耗时
   在使用索引字段作为条件时，如果该索引是联合索引，那么必须
      使用到该索引中的第一个字段作为条件时才能保证系统使用该索
      引，否则该索引将不会被使用
   要注意索引的维护，周期性重建索引
  
编写高效的 SQL(一）
    尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。
   尽量避免反复访问同一张或几张表，尤其是数据量较大的表,
      可以考虑先根据条件提取数据到临时表中，然后再做连接。
   能够快速缩小结果集的 WHERE 条件写在前面，如果有恒量条件，      也尽量放在前面
   尽量避免使用 GROUP BY、DISTINCT 、OR、IN 等语句的使用，      避免使用联表查询和子查询，因为将使执行效率大大下降
   能够使用索引的字段尽量进行有效的合理排列，如果使用了      联合索引，请注意提取字段的前后顺序
   针对索引字段使用 >, >=, =, <, <=, IF NULL和BETWEEN 将会使用      索引， 如果对某个索引字段进行 LIKE 查询，使用 LIKE ‘%abc%’      不能使用索引，使用 LIKE ‘abc%’ 将能够使用索引

编写高效的 SQL (二）
   尽量使用exists代替select count(1)来判断是否存在记录，count函数
     只有 在统计表中所有行数时使用，而且count(1)比count(*)更有效率。
   如果在SQL里使用了MySQL部分自带函数，索引将失效，同时将无法      使用 MySQL 的 Query Cache，比如 LEFT(), SUBSTR(), TO_DAYS()      DATE_FORMAT(), 等，如果使用了 OR 或 IN，索引也将失效
   注意insert、update操作的数据量，防止与其他应用冲突。如果数据量
      超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升
      级成表级锁。
   使用 Explain 语句来帮助改进我们的SQL语句

存储过程优化
    尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过
      1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循
      环中再进行表连接的操作。
    注意存储过程中参数和数据类型的关系。
    多做集合操作，少做单行操作
   
MySQL技巧分享
    常用技巧

使用 Explain/ DESC 来分析SQL的执行情况
使用 SHOW PROCESSLIST 来查看当前MySQL服务器线程     执行情况，是否锁表，查看相应的SQL语句
   设置 my.cnf 中的 long-query-time 和 log-slow-queries 能够     记录服务器那些SQL执行速度比较慢
   另外有用的几个查询：SHOW VARIABLES、SHOW      STATUS、SHOW ENGINES
   使用 DESC TABLE xxx 来查看表结构，使用 SHOW INDEX      FROM xxx 来查看表索引
   使用 LOAD DATA 导入数据比 INSERT INTO 快多了
   SELECT COUNT(*) FROM Tbl 在 InnoDB 中将会扫描全表      MyISAM 中则效率很高

Explain 使用

语法：EXPLAIN SELECT select_options

Type:       类型，是否使用了索引还是全表扫描, const,eg_reg,ref,range,index,ALL
Key:         实际使用上的索引是哪个字段 Ken_len: 真正使用了哪些索引，不为 NULL 的就是真实使用的索引
Ref:          显示了哪些字段或者常量被用来和 key 合从表中查询记录出来
Rows:      显示了MySQL认为在查询中应该检索的记录数
Extra:      显示了查询中MySQL的附加信息，关心Using filesort 和 Using temporary，性能杀手


MySQL Slow Log 分析工具

   mysqldumpslow - mysql官方提供的慢查询日志分析工具
   mysqlsla - hackmysql.com推出的一款日志分析工具，功能     非常强大
   mysql-explain-slow-log – 德国工程师使用Perl开发的把Slow      Log 输出到屏幕，功能简单
   mysql-log-filter - Google code 上一个开源产品，报表简洁
   myprofi - 纯PHP开发的开源log查看工具，功能详细