`

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

阅读更多
未完待续

数据库连接池是针对于一台数据库来说的还是对于一个数据引擎来说?
一台数据库服务器最多能建立多少数据库、一个数据库最多建多少张数据表?
哪些主要性能指标是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)
分享到:
评论

相关推荐

    iOS 备忘录小程序

    在iOS开发领域,备忘录应用是一个常见的项目,它能够帮助用户记录日常生活、工作中的重要信息。本项目名为“iOS备忘录小程序”,是一个自编的简易备忘录应用程序示例,特别适合初学者用来学习iOS开发,尤其是数据库...

    c# winform实现的备忘录项目及说明文档和设计图(内含四个不同界面的项目以及对应的数据库).rar

    备忘录,大家生活中都使用过,比如记笔记、手机备忘录等等,这些都是记录自己灵感时所想、定期内想做的事情,现在研究的备忘录为一个轻型的小系统,功能上也就是增删改查的操作,另加到时提醒、右键菜单 添加备忘录 ...

    基于Android的备忘录源码

    备忘录数据通常存储在SQLite数据库中,应用会包含一个SQLiteOpenHelper的子类,用于创建数据库表、执行SQL查询和更新操作。数据库操作通过ContentProvider进行封装,使得其他组件可以通过URI访问数据。 6. **数据...

    一款C#开发的轻量级笔记本,记事本,备忘录,简单易用,希望带给你方便运行环境: windows, .NET Framework

    标题中的“一款C#开发的轻量级笔记本,记事本,备忘录”表明这是一个基于C#编程语言创建的应用程序,它集成了笔记本、记事本和备忘录的功能,设计目标是轻巧便捷,便于用户记录和管理日常信息。这种类型的应用通常...

    时钟闹钟计时器秒表备忘录

    备忘录功能涉及到文本编辑和存储,需要一个笔记编辑界面,用户可以输入和保存文字。同样可以利用SQLite数据库存储这些备忘录,每个备忘录作为一个数据条目,包含标题和内容字段。 最后,秒表功能用于测量时间间隔,...

    小雪备忘录PHP开源系统(xNote) v1.0.zip

    "小雪备忘录PHP开源系统(xNote) v1.0.zip" 是一个基于PHP语言开发的开源备忘录系统,适用于个人或小型团队记录、管理日常事务。这个系统允许用户创建、编辑、删除备忘录,并可能具备权限管理、搜索功能等。从文件...

    java-个人生活助手源代码+sql

    "备忘录"功能可能使用了数据库来存储用户的待办事项或笔记。用户可以添加、编辑、删除备忘,并可能有搜索和分类备忘的功能。 在提供的压缩包文件 "lifeassistant" 中,很可能包含了这个个人生活助手应用的所有源...

    Android记事本、安卓备忘录.rar

    "Android记事本、安卓备忘录"是一款简单的Android应用,它允许用户创建个人笔记,对其进行编辑,并可随时查看或删除。这个项目的核心功能包括: 1. 添加记录:用户能够输入文本,点击保存按钮创建新的记事。 2. ...

    学习备忘录

    【学习备忘录】是关于AWS(Amazon Web Services)个人学习和笔记的集合,主要目的是为了方便回顾和理解。这份备忘录强调了在记录时应注意的要点,即尽可能清晰地写出目标、概述、架构图以及配置参数,以便于日后查阅...

    MySQL学习备忘笔记.zip

    这篇“MySQL学习备忘笔记”涵盖了MySQL的基础知识、安装与配置、SQL语句、表的设计、索引、事务处理、备份与恢复、性能优化等方面,旨在帮助初学者快速掌握MySQL的核心技能。 1. **MySQL基础** - 数据库概念:理解...

    Memoire.rar

    SQLite是Android内置的关系型数据库,适用于存储结构化的数据,如备忘录应用中的笔记内容。在"Memoire"中,我们需要创建一个SQLite数据库,包含一个笔记表,字段可能包括笔记ID、标题、内容、创建时间和修改时间等。...

    精选_基于Jsp和MySQL实现的个人记事备忘系统_源码打包

    【标题】"精选_基于Jsp和MySQL实现的个人记事备忘系统_源码打包"揭示了这个项目的核心——一个使用JSP(JavaServer Pages)技术和MySQL数据库构建的个人记事本或备忘录应用。这样的系统允许用户在线创建、编辑、存储...

    软件设计师软考笔记.zip

    3. **行为型模式**:责任链模式、命令模式、解释器模式、迭代器模式、访问者模式、备忘录模式、观察者模式、状态模式、策略模式、模板方法模式、访问者模式,这些模式主要涉及对象之间的交互和行为。 通过学习和...

    java后端-面试笔记和面试资料

    3. 行为型模式:策略、模板方法、观察者、迭代器、责任链、命令、解释器、备忘录、状态、访问者模式。 七、框架与工具 1. Spring框架:依赖注入、AOP、SpringMVC、Spring Boot、Spring Cloud等。 2. MyBatis:ORM...

    基于JavaEE的个人数字图书馆的设计与实现毕业论文.doc

    【个人数字图书馆】个人数字图书馆是一种现代化的信息管理工具,它允许用户存储、组织、检索和共享个人知识资源,包括电子书、文档、笔记等。它适应了信息时代对个性化信息管理的需求,帮助用户有效地整理和利用信息...

    C#+SQLServer人事管理系统(适合初学者、毕业设计等)【完整】

    5)备忘记录包括日常记事和通讯录。日常记事显示记事内容,可以增,删,改,按记事时间和记事类别查询。通讯录显示员工的联系方式,可以增,删,改,按类别查询。 6)数据库维护包括备份/还原数据库和清空数据库。...

    MYSQL笔记 .md

    每一行代表一个具体的实例,如一个用户的个人信息。 - **列**: 又称为字段,表示不同的数据属性,如用户的姓名、年龄等。 - **字段属性**: - 字段名: 每个字段都有唯一的名称。 - 数据类型: 指定字段存储的数据...

    ASP源码—AnyPIM个人信息管理系统.zip

    4. **备忘录**:提供文本编辑器,用户可以记录各种笔记,支持分类和搜索功能,方便信息的存储和检索。 5. **安全与权限**:系统通常会设有用户登录验证机制,保护用户数据的安全。可能还包括权限设置,让用户可以...

    c# 呼叫中心根据IP实时显示城市得到天气状况,日历功能

    备忘录功能需要提供一个文本编辑区域供用户输入和保存笔记。可以使用`TextBox`控件配合`RichTextBox`以提供格式化的文本输入。笔记的存储同样需要数据库支持,创建一个表来存储每个备忘录的标题、内容和创建日期。...

    学生通讯管理系统

    该系统集成了多种功能,如用户登录和注册、学生通讯录管理、学校会议安排、学习笔记记录、通知发布以及备忘录创建与修改。同时,它具备消息发送和接收的能力,为学生和教师提供了一个方便的在线交流平台。 在技术...

Global site tag (gtag.js) - Google Analytics