当前位置:首页 > 开发 > 数据库 > 正文

sql优化笔记(个人知识积累备忘录)

发表于: 2012-09-23   作者:bingyingao   来源:转载   浏览次数:
摘要: 未完待续 数据库连接池是针对于一台数据库来说的还是对于一个数据引擎来说? 一台数据库服务器最多能建立多少数据库、一个数据库最多建多少张数据表? 哪些主要性能指标是dba特别关注的? 有时候会报数据库链接丢失 mysql并发性能: 单表:3000qps  1000tps 一个库 胜通:而且qps和tps是基于1条记录而言? 分库分表:
未完待续

数据库连接池是针对于一台数据库来说的还是对于一个数据引擎来说?
一台数据库服务器最多能建立多少数据库、一个数据库最多建多少张数据表?
哪些主要性能指标是dba特别关注的?
有时候会报数据库链接丢失
mysql并发性能
单表:3000qps  1000tps
一个库
胜通:而且qps和tps是基于1条记录而言?


分库分表:
分为垂直分表(原本为一张表的字段放到两张表)和水平分表两类(数据存储在多个具有相同结构的表中)。
mysql数据库单表达到100万以上数据才需要考虑分表?

表结构:
CREATE TABLE `act_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `status` tinyint(3) unsigned NOT NULL COMMENT '当前订单状态.0:未发货;1:发货中;2:确认收货',
  `buyer_id` bigint(20) unsigned NOT NULL COMMENT '买家id',
  `seller_id` bigint(20) unsigned NOT NULL COMMENT '卖家id',
  `shop_id` bigint(20) unsigned NOT NULL COMMENT '宝贝所在店铺id',
  `digital_id` bigint(20) unsigned NOT NULL COMMENT '电子凭证id',
  `order_id` bigint(20) unsigned NOT NULL COMMENT '订单id',
  `item_id` bigint(20) unsigned NOT NULL COMMENT '宝贝id',
  `category_id` bigint(20) unsigned NOT NULL COMMENT '后台类目id',
  `delivery_id` varchar(32) DEFAULT NULL COMMENT '快递id',
  `mobile` bigint(20) unsigned NOT NULL COMMENT '手机号码',

  `delivery_company` varchar(32) DEFAULT NULL COMMENT '快递公司名',
  `buyer_nick` varchar(32) NOT NULL COMMENT '买家用户名',
  `express_address` varchar(256) NOT NULL COMMENT '配送地址',
  `item_title` varchar(64) NOT NULL COMMENT '宝贝名称',
  `exprss_schedule_time` datetime DEFAULT NULL COMMENT '计算出来的配送时间',
  `express_time` datetime DEFAULT NULL COMMENT '实际真正配送时间',
  `validity_time` datetime NOT NULL COMMENT '订单有效期',
  `attributes` varchar(256) DEFAULT NULL COMMENT '扩展结构',
  `gmt_create` datetime NOT NULL,
  `gmt_modified` datetime NOT NULL,
  `token` varchar(32) DEFAULT NULL COMMENT '电子凭证安全码',
  `price` decimal(10,3) DEFAULT NULL COMMENT '宝贝价格',
  `pic` varchar(256) DEFAULT NULL COMMENT '宝贝的一个缩略图地址',
  `sub_order_id` bigint(20) DEFAULT NULL COMMENT '宝贝子订单id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_act_order_dm` (`digital_id`,`mobile`),
  KEY `ind_act_order_did` (`digital_id`),
  KEY `ind_act_order_bid` (`buyer_id`,`status`,`item_title`),
  KEY `ind_act_order_sid` (`seller_id`,`status`,`buyer_nick`,`item_title`,`order_id`,`exprss_schedule_time`)
) ENGINE=InnoDB AUTO_INCREMENT=967 DEFAULT CHARSET=gbk COMMENT='订单表,数据量50w';

索引、联合索引适用场景。
where 后面的条件应该统一起来建一个 nomral的索引
在数据量达到一定值之前,建立索引反而有可能使性能下降?

SELECT o.id,o.status,o.buyer_id,o.seller_id,o.shop_id,o.digital_id,o.order_id,o.sub_order_id,o.item_id FROM (SELECT id FROM act_order WHERE seller_id=#sellerId# and status=#status# and order_id=#orderId# and buyer_nick=#buyerNick# and item_title=#itemTitle#
    order by (id或者exprss_schedule_time) desc limit 1,10) a,act_order o where a.id=o.id order by o.(id或者exprss_schedule_time) desc;
应该建立索引:除主键id为primary索引外,还应建seller_id, status, buyer_nick, item_title, order_id, exprss_schedule_time为一个联合nomral索引。

in、exists区别

like语法使用注意事项。
like在做 %%查询的时候有可能特别慢,慎用
between呢?
between只要条件都建了索引一般没什么问题。
视图的原理及应用

关联查询都比子查询效率高
int 类型查询最快?日期列转化为long型存储利于查询?
in的查询效率也不高。
尽量不要用子查询,特别慢,例如下面的性能差别就很大。
sql子查询:select id from act_order where id in (select id from act_order group by digital_id having count(1) >= 2)  limit 100

sql关联查询:select * from act_order ao,(select id from act_order group by digital_id having count(1) >= 2) rdata where ao.id=rdata.id limit 100

sql右连接查询:select * from act_order ao right join (select id from act_order group by digital_id having count(1) >= 2) rdata on ao.id=rdata.id limit 100
未完待续,未完待续,未完待续,未完待续

其他一些比较好的参考数据库文章:
http://www.cnblogs.com/inrie/archive/2011/02/22/1961415.html


count(*)、count(1)、count(id)哪个性能高一点儿。
count(1)和count(*)基本没有差别!

主库(只写)备库(只读)关系,所谓8主8备是数据copy16份吗?

分页查询优化:
==========以下内容来源于网络==================
关于mysql的分页优化
1、优化写法: (先根据过滤条件取出主键id进行排序,再进行join操作取出其他相关字段)

select  t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip,
t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from
(select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 0, 15) a,group_thread_reply_0029 t where a.id = t.id;

2、普通写法 :(一次性根据过滤条件取出所有字段进行排序返回)

select  t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip,
t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t
where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 0, 15;

3、两种写法的执行计划(从执行计划可以看出优化写法的好处是第一个查询不需要回表,直接通过包含索引取得数据)

root@snsgroup 06:38:11>explain select t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip,
-> t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from
-> (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 450, 15) a,
-> group_thread_reply_0029 t where a.id = t.id;
+----+-------------+-------------------------+--------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------+--------+---------------+-----------+---------+------+------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 15 | |
| 1 | PRIMARY | t | eq_ref | PRIMARY | PRIMARY | 8 | a.id | 1 | |
| 2 | DERIVED | group_thread_reply_0029 | ref | thread_id | thread_id | 8 | | 1210 | Using where; Using index|
+----+-------------+-------------------------+--------+---------------+-----------+---------+------+------+--------------------------+
3 rows in set (0.00 sec)

root@snsgroup 06:39:13>explain select t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip,

-> t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time -> from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 450, 15; +----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+ | 1 | SIMPLE | t | ref | thread_id | thread_id | 8 | const | 1210 | Using where | +----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+ 1 row in set (0.00 sec)

4、分析SQL性能,做了多次试验,结果稍有不同,大概都是:翻前3或4页,普通写法效率高,翻第4或5页及之后,优化的分页写法效率高。

  (本例中语句1,2代表第1页,......语句9,10代表第5页。每页显示15条记录。)


[root@snsgroup root@snsgroup] 10:45:38>show profiles; +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 0.00076600| select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 order by gmt_create as | | 2 | 0.00046700| select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 0, 1 | | 3 | 0.00071700| select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 order by gmt_create as | | 4 | 0.00058000 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 15, | | 5 | 0.00072400 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 order by gmt_create as | | 6 | 0.00074800| select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 30, | | 7 | 0.00082100 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 order by gmt_create as | | 8 | 0.00077600| select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 45, | | 9 | 0.00074100| select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 order by gmt_create as | | 10 |0.00119700| select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 60, | | 11 | 0.00084400 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 order by gmt_create as | | 12 | 0.00097600 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 75, | | 13 | 0.00076200 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 order by gmt_create as | | 14 | 0.00118200| select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 100, | +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

14 rows in set (0.00 sec)

5、具体分析性能消耗在哪里 (对比语句1,2和语句9,10;主要在Opening tables;statistics;Sending data )

root@snsgroup 10:45:43>show profile cpu,block io for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000021 | 0.000000 | 0.000000 | 0 | 0 |
| checking query cache for query | 0.000068 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000147 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| Table lock | 0.000041 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000058 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| Sorting result | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000058 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000163 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000076 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
27 rows in set (0.00 sec)

root@snsgroup 10:46:31>show profile cpu,block io for query 2;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000042 | 0.000000 | 0.000000 | 0 | 0 |
| checking query cache for query | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000065 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| Table lock | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000048 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| Sorting result | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000183 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000018 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
19 rows in set (0.00 sec)

[mailto:root@snsgroup root@snsgroup] 10:47:36>show profile cpu,block io for query 9; +--------------------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | | checking query cache for query | 0.000051 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000133 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | Table lock | 0.000039 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000056 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | Sorting result | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000091 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000013 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000013 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000179 | 0.001000 | 0.000000 | 0 | 0 | | end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | removing tmp table | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000024 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | +--------------------------------+----------+----------+------------+--------------+---------------+ 27 rows in set (0.00 sec)

root@snsgroup 10:47:59>show profile cpu,block io for query 10; +--------------------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | | checking query cache for query | 0.000050 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000073 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | Table lock | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000048 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | Sorting result | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000924 | 0.001000 | 0.000000 | 0 | 0 | | end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | +--------------------------------+----------+----------+------------+--------------+---------------+ 19 rows in set (0.00 sec)

6、既然优化后的写法在翻后面页的时候优于普通写法是因为包含索引的缘故,下面测试了where中有个条件不在索引内的情况:

1) 也是分成7页来测试,优化写法与普通写法如下:

    (多了个gmt_modified 条件,此条件不在索引中)

select t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip,
t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from
(select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 and gmt_modified >'2009-08-17' order by gmt_create asc limit 0, 15) a,group_thread_reply_0029 t where a.id = t.id;

select t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip,
t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t
where thread_id = 771025 and deleted = 0 and gmt_modified >'2009-08-17' order by gmt_create asc limit 0, 15;


2)分析SQL性能 (做了多次试验(查询前7页,每页15条记录),都是普通写法性能高,优化写法没有了包含索引的优势)

root@snsgroup 02:43:23>show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.00078100 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 and gmt_modified >'200 |
| 2 | 0.00054600 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 and gmt_modified >'2009-08-17' ord |
| 3 | 0.00074900 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 and gmt_modified >'200 |
| 4 | 0.00057000 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 and gmt_modified >'2009-08-17' ord |
| 5 | 0.00094900 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 and gmt_modified >'200 |
| 6 | 0.00064900 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 and gmt_modified >'2009-08-17' ord |
| 7 | 0.00123900 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 and gmt_modified >'200 |
| 8 | 0.00076900 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 and gmt_modified >'2009-08-17' ord |
| 9 | 0.00102700 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 and gmt_modified >'200 |
| 10 | 0.00116000 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 and gmt_modified >'2009-08-17' ord |
| 11 | 0.00169200 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 and gmt_modified >'200 |
| 12 | 0.00114500 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 and gmt_modified >'2009-08-17' ord |
| 13 | 0.00160600 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id,t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from (select id from group_thread_reply_0029 where thread_id = 771025 and deleted = 0 and gmt_modified >'200 |
| 14 | 0.00107200 | select sql_no_cache t.id, t.thread_id, t.group_id, t.deleted, t.author_id, t.author_nick, t.author_ip, t.content, t.last_modified, t.gmt_create, t.gmt_modified, t.floor, t.reply, t.reply_time from group_thread_reply_0029 t where thread_id = 771025 and deleted = 0 and gmt_modified >'2009-08-17' ord |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
14 rows in set (0.00 sec)

3)查看具体性能消耗 (主要在Opening tables;statistics;Sending data )

[mailto:root@snsgroup root@snsgroup] 02:43:32>show profile cpu,block io for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000027 | 0.000000 | 0.000000 | 0 | 0 |
| checking query cache for query | 0.000054 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000138 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Table lock | 0.000026 | 0.002000 | 0.000000 | 0 | 0 |
| optimizing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000085 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| Sorting result | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000162 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000143 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000022 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
26 rows in set (0.00 sec)

root@snsgroup 02:44:11>show profile cpu,block io for query 2;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000025 | 0.000000 | 0.000000 | 0 | 0 |
| checking query cache for query | 0.000045 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000074 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Table lock | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000114 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| Sorting result | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000185 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
19 rows in set (0.00 sec)
root@snsgroup 02:44:13>show profile cpu,block io for query 11;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000018 | 0.000000 | 0.000000 | 0 | 0 |
| checking query cache for query | 0.000048 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000121 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| Table lock | 0.000030 | 0.001000 | 0.000000 | 0 | 0 |
| optimizing | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000073 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| Sorting result | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000884 | 0.001000 | 0.000000 | 0 | 0 |
| init | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000386 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
26 rows in set (0.00 sec)

root@snsgroup 02:45:18>show profile cpu,block io for query 12;

+--------------------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000035 | 0.000000 | 0.000000 | 0 | 0 | | checking query cache for query | 0.000066 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000127 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | Table lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000024 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000013 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000089 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | Sorting result | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000714 | 0.002999 | 0.000000 | 0 | 0 | | end | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | +--------------------------------+----------+----------+------------+--------------+---------------+ 19 rows in set (0.00 sec)

sql优化笔记(个人知识积累备忘录)

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
mysql与oracle在一些基本的sql中存在差异 表结构如下 create table test( id int, name varchar(10)
调用天气: <iframe src="http://www.thinkpage.cn/weather/weather.aspx?uid=&c=CHXX0008&l=zh-
Big Data技术综述   Big Data是近来的一个技术热点,但从名字就能判断它并不是什么新词。毕竟,大
原生DOM知识的积累 现在各中大型公司都有一个中坚队伍为团队搭好架子,处理绝大多数的难题。但上天
来源:qq群里大家的讨论 1,为知 wiz(有人推荐) 网址:http://www.wiz.cn/ 2 onenote(微软的Micr
使用J2ME的MVC2开源框架KBOX http://www.sina.com.cn 2009年08月05日 16:50 IT168.com 转载http://t
(1)什么是内存模型 究竟什么是内存模型?内存模型描述了程序中各个变量(实例域、静态域和数组元
字段合并: select EVFOUNDATIONTYPEA || EVFOUNDATIONTYPEB|| EVFOUNDATIONTYPEC ||EVFOUNDATIONTY
知识管理(Knowledge Management)是一项在1990年代中期开始在全球崛起的学术与商业应用主题,针对
本文更新版本已挪至 http://www.zhoujingen.cn/blog/2019.html -----------------------    今天
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号