对MySQL应用的一些总结

彭立勋 http://www.PengLiXun.com/ [email protected]

MySQL Optimization

MySQL架构设计与优化最佳实践

Topics of Discussion(上午)

l  MySQL缺陷与适用场景 l  MyISAM vs InnoDB l  MyISAM与InnoDB性能调优 l  MySQL Server性能调优 l  MySQL Join算法 l  MySQL 优化器特性 l  索引设计杂谈

Topics of Discussion(下午)

l  编写高性能SQL l  设计高性能Schema l  数据备份与恢复策略 l  数据拆分方案 l  复制设计方案 l  典型故障案例分析 l  构建高性能高可用可扩展MySQL集群

Next Topic

MySQL缺陷与适用场景

MySQL架构

MySQL缺陷

Ø  MySQL没有Hash Join à 不适合无索引大数据量数据分析

Ø  MySQL没有完整的CBO à 不可依赖MySQL选择最优的执行计划，不可依赖MySQL自行优化SQL

Ø  MySQL没有备块级复制 à 不可依赖MySQL保证备库的实时一致性

Ø  MySQL无法有效利用主机资源 à 单机单实例会严重的浪费机器性能

MySQL适用场景

ü  应用会产生庞大连接数（Thread） ü  可以依赖索引完成的数据分析（Covering

Index） ü  高并发查询（MyISAM） ü  短小的在线事务处理（InnoDB） ü  简单条件查询（HandlerSocket） ü  数据拆分（Free）

Next Topic

MyISAM vs InnoDB

MyISAM的优势和劣势优势劣势

小内存下更快速的查询：因为没有复杂的MVCC多版本控制机制

数据文件容易损坏：因为数据回写机制没有原子性保证

更小的数据文件：因为数据文件中没有额外的行锁和事务信息

没有事务保证

非常容易冷备份：因为每张表三个文件，没有额外的全局信息，拷贝文件即可备份

只有索引缓存

表级锁，并发差

InnoDB的优势和劣势优势劣势

行级锁，并发高：只锁定被访问到的行，并不影响对不同行的操作。

小内存下纯读应用不如MyISAM快

ACID事务保证

备份操作更复杂：因为存在事务信息，多版本信息，不能简单的拷贝数据文件。但是支持热备份。

所有数据文件均可缓存： InnoDB主键索引即数据，缓存索引就包括了缓存数据

更占用磁盘空间：因为行结构中包含事务信息和行锁信息，另外有UNDO和REDO事务日志

读写互不阻塞：得益于MVCC多版本控制，读数据并不需要锁定修改，可以在UNDO日志中找到旧版本。

索引对比 MyISAM InnoDB

B-‐Tree结构，不能自动Balance B+Tree结构，自动Balance

堆表：数据文件独立于索引，数据文件以插入顺序组织，以物理位置为引用键，索引通过物理位置查找数据行。

索引组织表：数据文件以主键顺序组织，主键即数据，非主键索引通过主键查找数据行。所以主键插入无序时性能极差。

支持自适应哈希：当InnoDB发现表上经常存在某些唯一键上的KV查询时，会自动把这部分数据生成到自适应哈希表中，可进行KV查询优化。

主键索引包含事务信息：主键索引上唯一确定了一行数据，所以事务信息包含在主键索引上。如果没有显示定义主键索引，将自动建立隐含主键。

MyISAM场景

ü  数据仓库：大量读，批量写，可共享数据文件 ü  日志系统：只有插入没有修改，简单查询 ü  读为主的应用：没有写或写极少，几乎不阻塞读 ü  低并发的应用：表锁基本不影响并发 ü  大数据批量导入：避免InnoDB索引组织表主键乱序时插入慢

InnoDB场景

ü  高并发的应用：例如消息队列 ü  可靠性要求高的应用：例如用户登陆信息 ü  数据一致性要求高，宕机可恢复：例如用户数据 ü  要求事务和约束的应用：例如有数据关联的系统 ü  需要高性能缓冲写的应用：例如短时间内大量

UPDATE

同时使用MyISAM / InnoDB

Ø  不可共享缓存空间 Ø  SQL包含两种表将失去事务意义

总之不推荐这两种表同时存在同一实例，除非：两种表没有关联，MyISAM表只做备份冗余。但是，Master用InnoDB，Slave用MyISAM某些情况下是不错的方案。

Next Topic

MyISAM和InnoDB的优化

MyISAM优化

l  INSERT 优化 l  Key Buffer 优化

MyISAM 插入

Ø  定⻓长表优于变⻓长表，即尽可能不定义变⻓长字段 Ø  数据存储按插入顺序，除非删除了数据产生的空洞可以插入新行或使用了并发插入

Ø  可以加大bulk_insert_buffer_size变量，采用 ”INSERT INTO table VALUES (),(),...” 的批量插入语法，或者采用LOAD DATA语法

MyISAM 并发插入

虽然MyISAM是表锁，但是如果只在数据文件末尾插入数据，是可以并发的，通过concurrent_insert变量控制： n  0 – (Off)：不允许并发插入

n  1 – (Default)：如果数据文件没有空洞，可以在数据文件末尾并发插入

n  2 – Enable：如果表在被使用则在数据文件末尾并发插入，如果表没有被使用则一样在文件中找空洞插入 [建议]

Key Buffer 原理

Key Buffer 介绍

作用：缓存MyISAM的索引，默认16M 相关变量：key_buffer_size（Key Buffer大小）相关状态量： n  key_read/write_requests（索引逻辑读/写）

n  key_reads/writes（索引物理读/写，即磁盘读/写）

n  key_blocks_used/unused（已使用/未使用的缓冲块）

n  key_blocks_not_flushed（未回写的脏缓冲块）

Key Buffer 监控

Ø  命中率 = (requests - reads) / requests

Ø  利用率 = key_blocks_used / (key_blocks_used + key_blocks_unused)

Ø  赃⻚页率 = key_blocks_not_flushed / (key_blocks_used + key_blocks_unused)

Key Buffer 注意

key_buffer_size不能关闭，因为至少mysql系统库全部用的MyISAM表，就算整个实例都是InnoDB表，也应该设置key_buffer_size。

多段 Key Buffer

减少Key Buffer锁的竞争：将Key Buffer拆分为多个段，分别缓冲不同的表。

  root@localhost : (none) 04:16:36> SET GLOBAL hot_cache.key_buffer_size=128*1024;  Query OK, 0 rows affected (0.00 sec)  root@localhost : mysql 04:28:05> CACHE INDEX user IN hot_cache;   +------------+--------------------+----------+----------+  | Table | Op | Msg_type | Msg_text |  +------------+--------------------+----------+----------+  | mysql.user | assign_to_keycache | status | OK |  +------------+--------------------+----------+----------+  1 row in set (0.00 sec)

Key Buffer 建议

最好可以保证Key Buffer Size大于目前所有需要访问的表MYI文件大小总和。如无法保证则一般分三个区给不同业务类型的表： n  静态缓冲区，极少或不修改的表，这个区的Key Buffer访问没有写锁，都是Shared Read Lock。

n  修改缓冲区，大量修改或UPDATE的表，这个区的Key Buffer竞争严重，限制竞争在一个区。

n  普通缓冲区，除大量修改和只读表以外的正常读写比例的表。

刷新 Key Buffer

内存中缓存的索引块，有时候并不会及时刷新到磁盘上，所以对于正在运行的数据表的索引文件（MYI）一般都是不完整的。如果此时拷⻉贝或者移动这些索引文件。多半会出现索引文件损坏的情况。可以通过Flush table命令来将Key Buffer中的block都flush到磁盘上。

  mysql> flush tables;  +------------------------+----------+  | Variable_name | Value |  +------------------------+----------+  | Key_blocks_not_flushed | 0 | #所有修改的block都已经被flush了  | Key_blocks_unused | 0 |  | Key_blocks_used | 14497 |  | Key_read_requests | 38333936 |  | Key_reads | 207 |  | Key_write_requests | 8819898 |  | Key_writes | 1255245 |  +------------------------+----------+

InnoDB优化

l  Buffer Pool 调优 l  日志调优

l  XtraDB调优

InnoDB 简介

Ø  InnoDB是MySQL上应用最广的事务引擎，淘宝商品库、阿里巴巴商品信息全部跑在InnoDB上。

Ø  InnoDB目前包含两个版本，InnoBase和InnoDB Plugin，5.1之前只有InnoBase，5.1之后开始有InnoDB Plugin。另外还有基于MySQL Server和 InnoDB Plugin优化的Percona Server和XtraDB。

Ø  前阿里巴巴正在使用的就是Percona+XtraDB高性能版本，完全兼容MySQL。

InnoDB 文件结构

InnoDB 数据文件组织

Ø  最基本的修改单位是Row Ø  最基本的IO单位是Page，包含2个以上Row Ø  最基本的分配单位是Extent，包含64个Page Ø  InnoDB的索引都是指到Page no为止，为了B-

Tree保持高效，InnoDB要求一个Page内至少有2行数据，所以我们建议每行数据设计⻓长度不要超过4K，这样我们可以方便的迁移到最平衡的8K⻚页大小。

InnoDB 数据文件组织

InnoDB 索引结构

InnoDB 主线程

Buffer Pool

InnoDB最重要的一块内存就是Buffer Pool，它包含了几乎所有InnoDB数据和索引的缓冲，主要有： n  数据缓存（主键缓存）

n  索引缓存（非主键索引缓存）

n  自适应哈希

n  数据字典

n  ⻚页哈希等

Buffer Pool 设置

innodb_buffer_pool_size设置Buffer Pool的大小。建议将除了连接使用的内存，操作系统使用的内存之外的全部内存给Buffer Pool，不要靠操作系统的OS Cache来缓冲缓存数据。

经验参考值： n  48G内存：32G Buffer Pool

n  32G内存：20G Buffer Pool



Buffer Pool 赃⻚页比例

Buffer Pool同时担任缓冲和缓存工作，因此Buffer Pool中会产生赃⻚页，即在内存中修改了但未刷新到磁盘上的⻚页。 innodb_max_dirty_pages_pct变量可以配置最多允许多少比例的赃⻚页在内存中，超过比例将触发刷新。

经验参考值： n  75%适用于绝大部分情况，Google/Facebook/Alibaba均使用

n  5%适用于非常在意宕机快速恢复的情况，Taobao/Alipay使用

InnoDB 事务安全

Ø  REDO LOG：所有变更内容全部写入REDO LOG。一旦数据库Crash，将redo中已经标记为commit状态的事务全部重做到数据文件，将没有标记为commit的事务在数据文件中回滚。

Ø  UNDO LOG：所有数据被修改的旧版本数据和版本号写入undo，当需要回滚事务时，在undo中找到旧版本信息，覆盖数据文件。

Ø  Double Write Buffer：所有的⻚页级回写，都先将⻚页写入Double Write Buffer区域，确认CheckSum与内存中的⻚页一致，再正式写入数据文件中，如果数据库Crash，数据文件中的⻚页没有回写完成，可以从Double Write Buffer中读取正确的⻚页重写。

InnoDB 日志

Log Buffer

Ø  事务安全使得InnoDB会产生事务日志，包括REDO和UNDO。日志回写到磁盘会有一个Log Buffer缓冲日志写

Ø  innodb_log_buffer_size设置事务日志缓冲大小 Ø  经验参考值：

n  默认只有1M，存在大事务或者高并发情况，建议设置8~16M

InnoDB 事务提交

Ø  Log Buffer中缓冲的日志在检查点或事务提交时回写文件。

Ø  Buffer Pool中的⻚页在检查点时回写磁盘。 Ø  innodb_flush_log_at_trx_commit控制回写：

n  0: 每秒刷新日志到文件（不是磁盘） n  1: 每次事务提交刷新日志到磁盘 [default] n  2: 每次事务提交刷新日志到文件，每秒同步到磁盘 [建议]

日志大小设置

Ø  更大的事务日志意味着什么？ n  可以记录更大的事务信息 [可以重做更大的事务]

n  可以记录更⻓长的事务历史 [可以重做更多的事务]

n  更少的检查点 [减少磁盘IO]

n  更⻓长的恢复时间 [宕机重启时间更⻓长] Ø  经验参考值：

n  64M的日志5分钟以内就可以恢复

n  1G的日志全部重做需要30分钟以上

n  我们设置为3个1G的日志

InnoDB 锁机制

Ø  InnoDB锁算法：写数据有索引时采用间隙锁（Gap Lock），无索引时采取表锁。读利用MVCC机制，不加锁。所以DELETE / UPDATE的条件没有加索引后果很严重。

n  WHERE pk = 1，锁定pk=1的行，不可以修改pk=1的数据。 n  WHERE pk > 1，锁定pk>1后的所有行，不可以操作pk>1的数据，包括插入删除修改。

n  WHERE pk BETWEEN 1 AND 10，锁定1~10内所有行，不可以操作pk在1~10的所有数据。

Ø  InnoDB锁结构：通过为每个⻚页生成一个锁定位图，判定行是否被锁定，每⻚页的第一行和最后一行是系统行，如果被锁定说明整⻚页被锁。

InnoDB MVCC

Ø  多版本实现：   数据旧版本写入UNDO LOG，数据Page中保留最新数据和版本号，当Query发现Page中的数据版本太新，则顺着undo log pointor到UNDO LOG寻找到需要的历史版本，所以读取数据不需要加锁。

Ø  事务可⻅见性判定：   MVCC需要做版本可⻅见性判定，以确认一个版本号的数据是否可以为当前事务所⻅见。InnoDB使用read view结构来实现版本可⻅见，一个事务起始时，会被分配一个read view，包含当前正在执行的事务号，根据不同的隔离级别需求，可以依据read view的事物号信息来判定行数据可⻅见性。

  例如MySQL默认隔离级别为可重复读（Repeatable Read），那么只要读取到的数据事务号大于read view中的最小事务号，就是不可⻅见的。

自适应哈希

Ø  自适应哈希是InnoDB的天才设计之一，InnoDB存储引擎会智能的监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则自动建立哈希索引。

Ø  自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。并不将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些⻚页建立哈希索引。

  -------------------------------------   INSERT BUFFER AND ADAPTIVE HASH INDEX   -------------------------------------   Ibuf: size 2249, free list len 3346, seg size 5596,   374650 inserts, 51897 merged recs, 14300 merges   Hash table size 4980499, node heap has 1246 buffer(s)   1640.60 hash searches/s, 3709.46 non-hash searches/s

Insert Buffering

Ø  Insert Buffer是InnoDB的另一个天才设计，当有数据更新时，不立刻更新Index叶子节点的Page，而是将这些更新缓冲起来，因此对同一个Page的更新可以一起回写，这种将离散IO整合为顺序IO的设计，非常适合现在的SSD盘。

Ø  但是Insert Buffer只对非唯一索引起作用，因为在做Merge之前检查值是否唯一是不可能的。

  -------------------------------------  INSERT BUFFER AND ADAPTIVE HASH INDEX  -------------------------------------  Ibuf: size 7545, free list len 3790, seg size 11336,  8075308 inserts, 7540969 merged recs, 2246304 merges

InnoDB 优化实践

ü  尽可能使用小的主键：减少非主键索引大小 ü  按主键顺序插入数据：避免主键节点分裂 ü  加大日志大小：避免过多的检查点 ü  避免大事务回滚：会产生大量的随机IO，拆分事务

ü  避免零散的插入：尽可能批量插入 ü  尽量复用前缀索引：InnoDB不会压缩索引

InnoDB 其他重要变量

Ø  innodb_additional_mem_pool_size：内部元信息缓存，16M足以胜任任何情况

Ø  innodb_support_xa：是否支持两段提交协议，没有必要开启，关闭可获得10%的性能提升。

InnoDB Plugin 增强

Ø  IO容量：innodb_io_capacity Ø  IO线程数：

n  innodb_read_io_threads(预读)

n  innodb_write_io_threads(赃⻚页回写)

n  innodb_use_purge_thread(清理UNDO)

Ø  可变⻚页大小（innodb_page_size） Ø  可配置多回滚段（innodb_extra_rsegments） Ø  Compressed表结构：CPU换IO，压缩空间 Ø  扩展INFORMATION_SCHEMA

InnoDB Plugin 配置

Ø  建议经验值： n  innodb_io_capacity = 磁盘数*200 (SSD>4000)

n  innodb_read_io_threads = 4

n  innodb_write_io_threads = CPU核数/实例数*2

n  innodb_use_purge_thread = 回滚段 / 2

n  innodb_page_size = 8K (全表扫描16K，小数据4K)

n  innodb_extra_rsegments = 平均并发插入数，但不要超过CPU数*2

n  字符型列为主的表设置Compressed表结构

Next Topic

MySQL Server 性能调优

Table Cache 调优

建议经验值： n  一般table_open_cache=1024适用于大部分情况。

n  如果opened_tables不断增⻓长，应调大table_open_cache。

查看目前打开的表：   root@localhost : (none) 12:39:24> show open tables;  +----------+--------------+--------+-------------+  | Database | Table | In_use | Name_locked |  +----------+--------------+--------+-------------+  | mysql | servers | 0 | 0 |  | mysql | event | 0 | 0 |  | test | t3 | 0 | 0 |  | mysql | procs_priv | 0 | 0 |  | mysql | db | 0 | 0 |  | mysql | host | 0 | 0 |  | test | t2 | 0 | 0 |  +----------+--------------+--------+-------------+

Query Cache 介绍

Query Cache是独立于存储引擎的SQL级缓存，它实际上是SQL文本的MD5值和SQL执行结果的哈希键对。因此Query Cache的使用受到很大限制： n  SQL文本必须完全相同，包括大小写，空格

n  存在不确定函数的SQL不被缓存，例如NOW()

n  PREPARE方式执行的SQL不被缓存

n  自定义函数，存储过程，视图均不能被缓存

Query Cache 设置

Ø  配置参数： n  query_cache_type：

n  OFF：不对SELECT结果集缓存 n  ON：对SELECT结果集缓存 [默认] n  DEMAND：只有当SELECT语句中显示写明“SQL_CACHE”时候才对结果集缓存 [推荐]

n  query_cache_size：Query Cache大小 n  query_cache_limit：可缓存的最大结果集，默认1MB n  query_cache_min_res_unit：内存分配最小单位，默认4KB

Ø  经验参考值： n  InnoDB应该关闭QCache，因为InnoDB有MVCC n  MyISAM如果确实数据很少变化，可以测试常用SQL的结果集大小，设置为最常用的SQL的结果集

Query Cache 优化

Ø  如果Qcache_lowmem_prunes很大，说明Query Cache大小不足

Ø  如果命中率很低，说明Query Cache没有必要 Ø  如果Qcache_free_blocks很大，说明Query

Cache需要整理碎片，Flush Query Cache。 Ø  query_cache_min_res_unit =

(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

Thread Cache 介绍

每个连接到MySQL都需要创建一个线程，Thread Cache就缓存了已经断开的连接线程描述符，如果创建线程前检测到Thread Cache还有可用线程，就不需要新创建，从缓存中获取即可。

  void create_thread_to_handle_connection(THD *thd) {   if (cached_thread_count > wake_thread) { //看线程缓存(thread_cache)中有否空余的线程

  thread_cache.append(thd);   pthread_cond_signal(&COND_thread_cache); // 有的话则唤醒一个线程来用   } else {   threads.append(thd);   pthread_create(&thd->real_id,&connection_attrib,

handle_one_connection, (void*) thd))); //没有可用空闲线程则创建一个新的线程   }   }

Thread Cache 配置

Ø  经验建议值： n  thread_cache_size = 1024 适应绝大部分场景

n  并发连接数很少时，相应降低配置

Ø  优化建议： n  如果threads_created增⻓长很快，应该调大thread_cache_size的值

连接数

Ø  配置变量： n  max_connections限制全局连接数

n  max_user_connections限制每个用户的连接数

Ø  建议经验值： n  max_user_connections：没有特殊情况不用限制

n  max_connections：默认100。一般1000个连接可以满足绝大部分应用，但是应注意，每个连接都会申请线程级内存（包括Sort Buffer，Join Buffer，Binlog Cache等），要考虑内存是否可以承受。

n  我们设置为3000，因为很少有连接需要排序和JOIN，一般不需要多少线程级内存。

Sort Buffer 介绍

Ø  每个线程需要排序的时候都会申请一块Sort Buffer，线程第一次使用的时候分配，连接断开后线程不销毁则内存不释放。

Ø  如果要排序的数据大于Sort Buffer将会用归并排序法分块排序，超出内存部分写入磁盘暂存。

Ø  sort_buffer_size 根据需要排序的数据集大小而定，可以按照Session在连接内重新设定。

Sort Buffer 监控

Ø  Sort_merge_passes：归并排序次数，这个值很大说明Sort Buffer不够，很多排序数据写到磁盘上了

Ø  Sort_rows：被排序数据的行数 Ø  Sort_range：通过索引选择了一个范围的数据后排序 Ø  Sort_scan：通过全表扫描进行排序的次数，这个值很大应该优化索引避免排序

Join / Read Buffer

Ø  join_buffer_size：JOIN时如果不能用到索引就会使用Join Buffer进行JOIN，最好优化索引避免使用Join Buffer。

Ø  read_buffer_size：全表扫描时缓存读取的数据，最好通过索引避免全表扫描。

Tmp Table 介绍

当执行计划中有Using temporary时表示要采用临时表完成SQL。 tmp_table_size配置在内存中分配临时表的最大限制，超过部分将建到磁盘上。

  root@localhost : test 04:21:53> explain select * from t1,t2 where t1.info=t2.info order by t1.id;

  +----+-------------+-------+------+---------------+------+---------+--------------+------+---------------------------------+

  | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

  +----+-------------+-------+------+---------------+------+---------+--------------+------+---------------------------------+

  | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using temporary; Using filesort |

  | 1 | SIMPLE | t1 | ref | idx | idx | 32 | test.t2.info | 1 | Using index |

  +----+-------------+-------+------+---------------+------+---------+--------------+------+---------------------------------+

Tmp Table 监控

Ø  重要状态： n  Created_tmp_disk_tables：穿件磁盘临时表数量

n  Created_tmp_tables：创建临时表数据

Ø  建议经验值： n  一般tmp_table_size设置为64M足以应对大部分情况

n  如果Created_tmp_tables增⻓长很快，最好优化SQL和索引避免临时表

n  如果Created_tmp_disk_tables增⻓长很快，应调大tmp_table_size的值

Next Topic

MySQL Join 算法

JOIN 算法概述

Ø  MySQL只有一种JOIN算法：Nested Loop Join

Ø  Nested Loop Join：选择一张驱动表，用关联字段循环匹配被驱动表

  FOR EACH ROW row1 IN table1 {

  FOR EACH ROW row2 IN table2 IF(关联条件) {

  result := row1 || row2;

  OUTPUT result;

  } } Ø  支持条件下推(Push Down)：先用条件过滤驱动表和被驱动表，再把过滤后的结果集进行JOIN

内连接

Ø  假定我们有一个如下形式的表T1、T2、T3的联接查询：SELECT * FROM t1 INNER JOIN t2 ON c1(t1,t2) INNER JOIN t3 ON c2 (t2,t3) WHERE c(t1,t2,t3)

Ø  MySQL首先由优化器判定JOIN顺序，假设生成执行计划为t1驱动t2驱动t3：

  FOR EACH ROW row1IN t1 IF (c(t1)){

  FOR EACH ROW row2 IN t2 IF (c1(t1,t2) && c(t2)) {

  FOR EACH ROW row3 IN t3 IF (c2(t2,t3) && c(t3)) {

  result := row1 || row2 || row3;

  OUTPUT result;

  } } }

Ø  将行传给下一层被驱动表匹配时，先用条件进行过滤，减少参与JOIN的行数

外连接

Ø  SQL同前例：   FOR EACH ROW row1 IN t1 IF c1(t1) {

  BOOL f1 := FALSE;

  FOR EACH ROW row2 IN t2 IF c1(t1,t2) AND (f1 ? c2(t2) : TRUE) {

  BOOL f2 := FALSE;

  FOR EACH ROW row3 IN t3 IF c2(t2,t3) AND (f1&&f2 ? c3(t3) : TRUE) {

  IF (f1&&f2 ? TRUE : (c2(t2) AND c3(t3))) {

  result := row1 || row2 || row3; OUTPUT result; }

  f2 := TRUE; f1 := TRUE; }

  IF (!f2) {

  IF (f1 ? TRUE : c2(t2) && c(t1,t2,NULL)) {

  result := row1 || row2 || NULL; OUTPUT t; }

  f1 := TRUE; } }

  IF (!f1 && c(t1,NULL,NULL)) {

  result := row1||NULL||NULL; OUTPUT t; } } Ø  经过测试，三张10000行的表用外连接平均比内连接慢0.01s，内连接效率高于外连接。

Next Topic

MySQL 优化器特性

MySQL 优化器概述

Ø  MySQL优化器属于RBO（基于规则的优化器），5.1版本后带有少量的CBO（基于开销的优化器）特性。

Ø  MySQL优化器缺陷： n  不支持函数索引：WHERE func(col) = x 无法使用col字段上的索引

n  不支持索引过滤 (Index Filter) 但支持索引覆盖扫描： n  SELECT col1,col2,col3,col4 FROM t WHERE col1 > x AND col2 < y AND col3 >z; 无法利用

(col1,col2,col3)索引过滤col2和col3，需要回表查询。 n  SELECT col1,col2,col3 FROM t WHERE col1 > x AND col2 < y AND col3 >z; 可以利用

(col1,col2,col3)索引扫描到col1,col2,col3的值不用回表。

n  无法自动优化子查询：MySQL无法将子查询优化为JOIN方式，而是将子查询存为临时表（无索引），在外层SQL需要时调用临时表数据进行匹配，这是效率非常低的执行方式，所以尽量避免在SQL中使用子查询。

MySQL 优化器判定算法

Ø  MySQL利用索引的原则：尽可能用主键索引>尽可能筛去更多的数据>尽可能使用⻓长度小的索引>尽可能使用更多索引列。

  idx_Col1索引⻓长度368，过滤基数9000   idx_Col3索引⻓长度128，过滤基数3000   idx_Col1_Col2索引⻓长度768，过滤基数11000   idx_Col2_Col3索引⻓长度255，过滤基数 9000

  SELECT Col1, Col2, Col3 FROM t WHERE col1 = x AND col2 = y AND col3 = z;

  优先使用idx_Col2_Col3索引，因为过滤基数差不多，索引⻓长度小得多。

  SELECT Col1, Col2, Col3 FROM t WHERE col1 = x AND col3 = z;   优先使用idx_Col1索引，因为col1过滤基数远大于col3，而idx_Col1_Col2因为索引⻓长度⻓长的多过滤基数却相近则不被使用

Next Topic

索引设计杂谈

如何平衡查询性能和修改代价

Ø  索引对修改数据的影响有多大？ Ø  为每个列建单列索引？ Ø  字符串类型怎么建索引划算？ Ø  怎么利用好前缀索引？ Ø  JOIN还有排序怎么办？ Ø  JOIN还有WHERE条件怎么处理？ Ø  子查询怎么建索引？ Ø  数据迁移该怎么处理索引？

Ø  Any Questions?

Next Topic

编写高性能SQL

编写高性能SQL

Ø  不写不必要的条件（索引判定缺陷） Ø  只选择必要的列（覆盖索引优化） Ø  关联列类型相同（类型转换速度） Ø  计数全部用count(*)（优化器特性） Ø  减少子查询，优化为JOIN（优化器缺陷） Ø  尽量不要使用主键排序，除非有主键作为条件（优化器缺陷）

Ø  尽量将复杂查询拆分为简单查询（优化器缺陷） Ø  尽量将OR查询拆分为多个SQL做UNION ALL或在程序拼接（优化器缺陷）

Ø  批量提交修改（减少日志刷新）

Next Topic

设计高性能Schema

设计高性能Schema

Ø  主键必须是顺序插入（索引组织表特性） Ø  可以用空间换时间多建索引（变更缓冲优化） Ø  尽量少用唯一键（变更缓冲局限） Ø  索引可以在SQL需要的列不多的情况下包含不在条件中的列（覆盖索引）

Ø  尽量不要设计超过768字节需要作为查询条件的列（索引限制）

Ø  不要设计超过4K行⻓长的表（⻚页结构限制）

Next Topic

备份与恢复策略

可选方案

Ø  cp l  直接拷⻉贝文件，只适合停机备份 l  InnoDB必须拷⻉贝日志才能保证恢复

Ø  mysqldump l  MyISAM无法保证数据一致，必须锁定写入 l  InnoDB可以使用—single-transation

Ø  xtrabackup l  在线备份物理文件，导出速度快，可以增量 l  恢复前需要预处理，恢复时临时预处理时间⻓长 l  可以在备份完成后立刻预处理，提升恢复速度

Ø  replication l  搭建延时Slave，可按需导出需要的数据 l  大集群如何优化维护难题

Next Topic

数据拆分方案

可选方案

Ø  水平拆分 l  相同拆分键的数据在一起 l  同一分区键的数据可以JOIN

Ø  垂直拆分 l  可以不分库 l  功能之间调用只能使用程序接口

Ø  中间件 l  自动分库，对应用透明 l  扩展不灵活 l  开发难度较大，不适合中小企业

Ø  程序分库[推荐] l  可自定义分库规则 l  灵活扩展 l  无需中间件，直连数据库

Next Topic

复制设计方案

可选方案

Ø  binlog replication l  推荐ROW格式，绕过Parse操作 l  原生可靠 l  只能1对N模式

Ø  trigger l  用触发器记录需要的表 l  不用全库复制，容易过滤 l  可以实现任意多主库向任意多备库同步

Ø  中间件 l  从可配置主库读取binlog，过滤需要的日志部分，传输给需要的目标库

l  灵活性极强 l  ROW格式binlog处理速度快 l  开发成本较高

Next Topic

典型故障案例分析

典型故障

Ø  不合理的Schema设计 Ø  不合理的热点数据估算 Ø  不合理的任务调度 Ø  不合理的操作

对MySQL应用的一些总结

Technology

Transcript of 对MySQL应用的一些总结