2012 oracle数据库会议总结

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:
此次会议人爆满的程度有点像平时挤300路公交车的场面,最后连坐的地方都没有,我在最后一排站了3小时,听完的Oracle SQL优化,录的音只听见关门声,咳嗽声,各种嘈杂声,幸亏笔头还好使,把一些重点给记录下来,得以分享之。
 
言简意赅,下面主要是性能优化一块。
 
一、从硬件角度上优化
1、加内存
InnoDB_Buffer_Pool缓冲池的大小决定了数据库的性能,若数据库中的数据可以完全存放于缓冲池中,则这时数据库的性能是最优秀的。
 
2、传统SATA磁盘升级为固态硬盘(fusion-io)
在生产环境中,数据库的大小通常都会大于内存的大小,因此不可避免的存在磁盘的读取操作。而传统磁盘的随机读写性能很差,往往在压力大时,CPU的I/O WAIT值就会很大。固态硬盘(fusion-io)采用闪存作为存储介质,读取速度相对机械硬盘更快,固态硬盘不用磁头,寻道时间几乎为0,持续写入的速度非常惊人,目前机械硬盘最快也要14毫秒左右,而固态硬盘可以轻易达到0.1毫秒甚至更低。
 
在日常家用超级本市场中,很多都配置上了固态硬盘。
 
二、从业务角度上优化
1、数据迁移减少query请求量
迁移数据,把一台机器的部分数据迁移到另一台机器里,从而减缓某一台机器压力大的问题。
 
2、前端增加Cache层(Memcache)
把一些不频繁更新的数据缓存在Cache层上,从而减缓后端数据库的压力。
 
3、拆分表
比如一张70G的大表,数据记录数达到上亿条,这时在对大表操作就会比对小表操作消耗性能大,所以通过取模的形式
insert into table_new_0 select * from table_old where mod(user_id,100)=0; 
insert into table_new_1 select * from table_old where mod(user_id,100)=1; 
insert into table_new_2 select * from table_old where mod(user_id,100)=2;
……
insert into table_new_99 select * from table_old where mod(user_id,100)=99;
搜狐、人人网也采用了此类方法。
 
4、读写分离
Master负责写,Slave负责读。
 
三、从软件角度上优化
1、替换掉MySQL5.1,升级到MySQL5.5,这样可以充分利用CPU多核。附上官方压力测试对比图:

四、优化索引
建立适当的索引,避免全表扫描,避免不必要的排序。
1、合理的建立索引能够加速数据读取效率,不合理的建立索引反而会拖慢数据库的响应速度。
2、索引越多,更新数据的速度越慢。
3、用EXPLAIN来分析SQL语句的性能。
 
例1、order by优化
mysql> desc student;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | 0       |       | 
| name  | varchar(6) | YES  |     | NULL    |       | 
| class | int(11)    | YES  | MUL | NULL    |       | 
| score | int(11)    | YES  |     | NULL    |       | 
+-------+------------+------+-----+---------+-------+
4 rows in set (0.12 sec)
 
student表学生id为主键,班级ID是索引。
 
mysql> explain select * from student where class = 1 order by score DESC;
+----+-------------+---------+------+---------------+-------+---------+-------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key   | key_len | ref   | rows | Extra                       |
+----+-------------+---------+------+---------------+-------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | student | ref  | class         | class | 5       | const |    2 | Using where; Using filesort | 
+----+-------------+---------+------+---------------+-------+---------+-------+------+-----------------------------+
1 row in set (0.03 sec)
 
可以看到Using filesort使用到了排序。这是因为通过班级ID等于1,先过滤出符合的记录,然后再把符合的记录一一排序。
所以,建立class和score的联合索引,减少排序。
 
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               | 
| student |          1 | class    |            1 | class       | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               | 
| student |          1 | class    |            2 | score       | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               | 
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.03 sec)
 
mysql> explain select * from student where class = 1 order by score DESC;
+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key   | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+
|  1 | SIMPLE      | student | ref  | class         | class | 5       | const |    2 | Using where | 
+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+
1 row in set (0.02 sec)
 
五、SQL优化几个实例
例1、避免where条件使用函数
mysql> explain select * from tt3 where date(t)=CURDATE();
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tt3   | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.03 sec)
 
这种情况是不会用到索引的,改下SQL即可。
 
mysql> explain select * from tt3 where t > DATE_FORMAT(CURDATE(),'%Y-%m-%d');
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | tt3   | range | IX_time       | IX_time | 4       | NULL |    2 | Using index condition | 
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.02 sec)
 
例2、避免使用子查询
MySQL5.6以下版本,优化器对子查询支持不是很好,性能很差,所以一般用join表连接代替。
 
mysql> select SQL_NO_CACHE count(*) from test1 where id not in(select id from test2);
+----------+
| count(*) |
+----------+
|   215203 |
+----------+
1 row in set (5.81 sec)
 
mysql> select SQL_NO_CACHE count(*) from test1 where not exists (select * from test2 where test2.id=test1.id);
+----------+
| count(*) |
+----------+
|   215203 |
+----------+
1 row in set (5.25 sec)
 
mysql> select SQL_NO_CACHE count(*) from test1 left join test2 on test1.id=test2.id where test2.id is null;             
+----------+
| count(*) |
+----------+
|   215203 |
+----------+
1 row in set (4.63 sec)
 
在MySQL5.6版本,优化器优化了子查询,不必再改写为join。
 
例3、数据类型,原则是够用就好,减少不必要的大字段,增大I/O量,
如能用varchar就不要用text,能用timestamp,就不要用datetime。
 
===================================================================
人人网介绍了他们的架构,是采用keepalive(双master架构,对大表做水平切分100张小表放到不同的服务器上减缓压力)
 
自己开发的中间件做读写分离。
 
目前正在尝试MMM架构(3台机器轮训切换)

 






















本文转自hcymysql51CTO博客,原文链接:http://blog.51cto.com/hcymysql/1057375 ,如需转载请自行联系原作者



相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
5天前
|
SQL Oracle 安全
Oracle11g更改数据库名(详细教程)
Oracle11g更改数据库名(详细教程)
15 1
|
1天前
|
Oracle 关系型数据库 MySQL
实时计算 Flink版操作报错合集之采集oracle的时候报ORA-65040:不允许从可插入数据库内部执行该操作如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
17 3
|
2天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之可以通过配置Oracle数据库的schema注册表来监测表结构的变化吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
10 1
|
5天前
|
Oracle 关系型数据库 数据库
Oracle数据库协议适配器错误解决方法
Oracle数据库协议适配器错误解决方法
12 2
|
5天前
|
存储 Oracle 关系型数据库
实验三 Oracle数据库的创建和管理
实验三 Oracle数据库的创建和管理
15 1
|
5天前
|
SQL Oracle 关系型数据库
实验一 安装和使用Oracle数据库
实验一 安装和使用Oracle数据库
16 1
|
5天前
|
Oracle Java 关系型数据库
【服务器】python通过JDBC连接到位于Linux远程服务器上的Oracle数据库
【服务器】python通过JDBC连接到位于Linux远程服务器上的Oracle数据库
17 6
|
12月前
|
SQL Oracle 关系型数据库
修改oracle数据库字符集
修改oracle数据库字符集
|
SQL Oracle 关系型数据库
oracle修改本机上数据库字符集
oracle修改本机上数据库字符集
|
SQL Oracle 关系型数据库
Oracle 数据库-服务器端字符集查看方法
Oracle 数据库-服务器端字符集查看方法
173 0
Oracle 数据库-服务器端字符集查看方法