2012 oracle数据库会议总结-阿里云开发者社区

开发者社区> 数据库> 正文

2012 oracle数据库会议总结

简介:
此次会议人爆满的程度有点像平时挤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台机器轮训切换)
详见:http://www.mysqlops.com/2011/06/11/mysql-mmm-architecture.html

 






















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



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章