MySQL 性能优化概述

离开的123

贡献于2013-10-20

字数:0 关键词: MySQL 数据库服务器 SQL

Copyright 2007 MySQL AB MySQL性能优化概述 陈慧 MySQL 工程师 Copyright 2007 MySQL AB 内容 •MySQL调优概述 • 如何定位性能瓶颈 • 存储引擎和调优 • MySQL Cluster Copyright 2007 MySQL AB MySQL调优概述 • 硬件 软件 网络环境 • 数据表结构 • 索引 •SQL语句 • 参数 • 存储引擎 Copyright 2007 MySQL AB MySQL调优概述 Client2 ClientN Connection Thread Pool Parser Query 101101 Client1 Optimizer Storage Engines ƒ InnoDB ƒ MyISAM ƒ MERGE ƒ MEMORY ƒ Federated ƒ ARCHIVE ƒ NDBCluster Query Cache MySQL Server Copyright 2007 MySQL AB 如何定位性能瓶颈 Copyright 2007 MySQL AB 定位性能问题 • MySQL Monitor & Advisor •Explain • Profiling Copyright 2007 MySQL AB MySQL Monitor & Advisor Copyright 2007 MySQL AB MySQL Monitor & Advisor Copyright 2007 MySQL AB Query Execution Plan (EXPLAIN) • EXPLAIN 模拟优化器执行查询,返回执行计划 • EXPLAIN tells you: – In which order the tables are read – What types of read operations that are made – Which indexes could have been used – Which indexes are used – How the tables refer to each other – How many rows the optimizer estimates to retrieve from each table Copyright 2007 MySQL AB Query Execution Plan (EXPLAIN) mysql> EXPLAIN SELECT Country.Name FROM Country JOIN CountryLanguage -> ON Code=CountryCode WHERE Language = 'Russian' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Country type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 239 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: CountryLanguage type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 33 ref: world_test.Country.Code,const rows: 1 Extra: Using where; Using index 2 rows in set (0.00 sec) Copyright 2007 MySQL AB Query Execution Plan (EXPLAIN) mysql> ALTER TABLE CountryLanguage ADD INDEX (Language); Query OK, 984 rows affected (0.24 sec) Records: 984 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT Country.Name FROM Country JOIN CountryLanguage -> ON Code=CountryCode WHERE Language = 'Russian' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: CountryLanguage type: ref possible_keys: PRIMARY,Language key: Language key_len: 30 ref: const rows: 15 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: Country type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 3 ref: world_test.CountryLanguage.CountryCode rows: 1 Extra: 2 rows in set (0.00 sec) Copyright 2007 MySQL AB Profiling Copyright 2007 MySQL AB Profiling Copyright 2007 MySQL AB Profiling Copyright 2007 MySQL AB 存储引擎和调优 Copyright 2007 MySQL AB MySQL的存储引擎 Client2 ClientN Connection Thread Pool Parser Query 101101 Client1 Optimizer Storage Engines ƒ InnoDB ƒ MyISAM ƒ MERGE ƒ MEMORY ƒ Federated ƒ ARCHIVE ƒ NDBCluster Query Cache MySQL Server Copyright 2007 MySQL AB 可插存储引擎结构 • MySQL 支持许多存储引擎,它们处理不同类型的表 • 通过选择,创建或扩展存储引擎来更好的适应应用的特殊 要求 • 对你来说最重要的是什么? - 集中读 - 复制 -OLTP(联机事务处理) - 在线备份 - 事务处理 - 数据仓库 - 性能 - 不相关关键字 - 可伸缩性 - 占用空间小 - 并发级别 - 行级别锁 - 索引类型 - 嵌入式 - 存储利用 - 表级别锁 - 高可靠性 - 集群 Copyright 2007 MySQL AB 存储引擎特点 • MyISAM – 特点: 非常有效率的存储, 易于处理高速数据加载 – 适用于: 高流量网站 数据仓库 • InnoDB – 特点: 提供ACID 事务处理支持 – 适用于: 在线事务处理应用 • Archive – 特点: 自动数据压缩 – 适用于: 历史数据仓库,数据存档, 数据审计 • NDB – 特点: 支持事务处理 提供高可靠机制 – 适用于: 高可靠 不停顿业务, 快速目录/关键字查找应用 Copyright 2007 MySQL AB 存储引擎的特点 • 锁的机制 • 索引 • 对事务的支持 • 参数 • 备份机制 Copyright 2007 MySQL AB 锁 • InnoDB 支持 – 行级锁 • Finest level of lock granularity • Only the row that is read or updated is locked • Allows other concurrent transactions to access other rows on the same page – 表级锁 • Only used when there are changes to the table structure itself (as is the case with Alter table) • Ensures the utmost integrity of the data it contains when doing direct changes to the table structure itself • Controlled by the --InnoDB-table-locks server setting Copyright 2007 MySQL AB 索引 InnoDB 11.5 InnoDB Indexes Row Row Row Row Row PKV PKV PKV PKV PKV Primary Key ValuesPrimary Key Values Non-leaf index pages Leaf index pages Clustered index Primary Key Index Secondary index Non-leaf index pages Leaf index pages Clustered index Primary Key Index Secondary index Copyright 2007 MySQL AB 参数 [mysqld] # You can write your other MySQL server options here # ... # Data files must be able to hold your data and indexes. # Make sure that you have enough free disk space. innodb_data_file_path = ibdata1:10M:autoextend # # Set buffer pool size to 50-80% of your computer's memory innodb_buffer_pool_size=1024M innodb_additional_mem_pool_size=10M # # Set the log file size to about 25% of the buffer pool size innodb_log_file_size=250M innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 Copyright 2007 MySQL AB InnoDB调优窍门 • 尽量使用短的,整型主键 • Load/Insert 数据时尽量用主键的顺序 • 增加日志文件大小 • 避免大的事务回滚 • 避免大量插入 • 尽量使用前缀索引 Copyright 2007 MySQL AB MySQL Cluster Copyright 2007 MySQL AB 高可靠性解决方案: 集群 • 成本 – Use commodity hardware to accommodate the growth of users, traffic, and data • 容错 – No single point of failure • 高可靠性 – Data is replicated across nodes and always available – Automatic fail-over • 可扩展性 – Distributes large workloads – Replicas for Read – Partitions for Write – Supports “Scale Out” • 高性能 – Load balanced – Memory based storage engine – Designed to handle thousands of requests per second • 简化管理 – Cluster management utilities – Commodity components Applications MySQL Server Nodes Data Nodes Management Server & Client Copyright 2007 MySQL AB MySQL Cluster 概述 • 并行数据库 – Shared-nothing architecture • 数据在节点上分散存储 – In a RAID10 fashion • 冗余 – Synchronous replication – Automatic fail over and recovery • 性能 – Ability to mix and match disk and memory tables – Different access methods (SQL or native NDB API) Copyright 2007 MySQL AB 高可靠性解决方案: MySQL 集群 Copyright 2007 MySQL AB 2节点集群的数据分布 Copyright 2007 MySQL AB 高性能配置 Copyright 2007 MySQL AB 内容 •MySQL调优概述 • 如何定位性能瓶颈 • 存储引擎和调优 • MySQL Cluster Copyright 2007 MySQL AB 谢谢大家! 欢迎访问我们的网站 http://www.greatopensource.com

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档

相关文档