bingyingao
- 浏览: 347404 次
- 性别:
- 来自: 杭州
-
文章分类
- 全部博客 (160)
- webservice (2)
- 数据库开发 (9)
- LINUX (6)
- 团队协作 (5)
- 前端技术 (4)
- J2EE (31)
- web服务器 (9)
- 经验常识 (13)
- 开发工具 (17)
- 项目管理 (7)
- 重构与设计模式 (8)
- 安全技术 (1)
- 并发编程 (1)
- 移动互联网 (2)
- 编码风格 (1)
- 领域建模 (1)
- 随想杂记 (12)
- 翻译 (2)
- 问题排查 (2)
- 数据挖掘 (4)
- 软件架构 (1)
- java语言基础知识 (13)
- 文件读写 (1)
- mac研发笔记 (1)
- 网络问题 (1)
- python学习 (0)
- Java8新特性 (1)
- soft kes collections (1)
最新评论
-
cremains：
...
java8新特性学习笔记 -
bingyingao：
guooo 写道很好的总结，不知能否转载？可以，多谢关注
又四年了，再看如何快速融入一个新团队 -
guooo：
很好的总结，不知能否转载？
又四年了，再看如何快速融入一个新团队 -
omeweb：
又过了好几年了，有啥新感悟没有？
两年已过去，再看该如何快速融入新团队 -
kely39：
感谢楼主，问题已解决
包冲突问题的解决方法
未完待续
数据库连接池是针对于一台数据库来说的还是对于一个数据引擎来说？
一台数据库服务器最多能建立多少数据库、一个数据库最多建多少张数据表？
哪些主要性能指标是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)
数据库连接池是针对于一台数据库来说的还是对于一个数据引擎来说？
一台数据库服务器最多能建立多少数据库、一个数据库最多建多少张数据表？
哪些主要性能指标是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备忘
2014-12-17 20:45 1105ORACLE 下面这个效率很低 SELECT * FROM ( ... -
mysql字符串与日期类型互转
2014-03-30 17:53 59024小时格式 select str_to_date('2014 ... -
linux下安装oracle11g步骤与体会
2011-05-01 10:14 111809年掌上城市开发经验 ... -
Oracle Pack用法详解
2011-05-01 09:57 1208Oracle Package有哪些作用呢？ 简化应用设计、提 ... -
join用法
2011-04-28 15:42 709条件连接(join) T1 { [INNER] | { LE ... -
Oracle Group By 用法之 —— Having
2011-04-28 15:35 2776客户需求分析： ... -
oracle游标使用详解
2011-04-27 19:59 1150游标（CURSOR）也叫光标，在关系数据库中经常使用，在PL/ ... -
org.logicalcobwebs.proxool参数说明[proxool-0.9.1.jar]
2011-04-27 19:45 9100package org.logicalcobwebs.prox ...
相关推荐
sqlserver优化笔记,自己无聊的时候整理的一些优化笔记
SQL优化 SQL优化软件 SQL优化工具 很好用的工具，可以分析优化TSQL语句，oracle数据库语句优化工具
基于SpringBoot+Vue的备忘录系统源码+sql数据库(课程大作业).zip 基于SpringBoot+Vue的备忘录系统源码+sql数据库(课程大作业).zip 基于SpringBoot+Vue的备忘录系统源码+sql数据库(课程大作业).zip 【资源说明】 项目...
vc++ SqlServer 公司备忘录系统 源自 清华大学Visual C++ SQL Server数据库开发与实例
sql笔记sql笔记sql笔记sql笔记sql笔记sql笔记
企业备忘录管理系统 前台C++ 后台数据库SQL、 代码 、 安装包 、 实践报告、完整。
一、基础 1 二、提升 3 三、技巧 6 数据开发-经典 10 SQL SERVER基本函数 14 常识 15 SQLSERVER2000同步复制技术实现步骤 15
备忘录，大家生活中都使用过，比如记笔记、手机备忘录等等，这些都是记录自己灵感时所想、定期内想做的事情，现在研究的备忘录为一个轻型的小系统，功能上也就是增删改查的操作，另加到时提醒、右键菜单 添加备忘录 ...
SQL优化器相关知识.pptx
毕业设计基于JavaWeb实现的一个备忘录系统项目源码。难度适中，新手自己可操作 备忘录 介绍 后端部分基于Servlet、Jdbc实现. 前端部分基于Layui、jqury实现。 一个简单的前后端分离Demo，前后端交互JSON数据格式 ...
海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化...
这是一个用c++和sql server设计的企业管理系统， 它适合中小企业和学习使用，其中有完整的c++代码和系统的数据库，可以直接下载使用。
, 现在《收获，不止SQL优化——抓住SQL的本质》开始带你抛除烦恼，走进优化的可乐世界！, 首先教你SQL整体优化、快速优化实施、如何读懂执行计划、如何左右执行计划这四大必杀招。整这些干嘛呢？答案是，传授一个先...
sqlserver个人学习笔记
SQL学习笔记
基于Oracle的SQL优化
sql server笔记 sql server笔记 sql server笔记sql server笔记sql server笔记sql server笔记
SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化
SQL学习笔记(pdf)
1.FORALL 用法小结 2.如何使用批挷定提高性能 3.FORALL 如何影响回滚 4.用%BULK_ROWCOUNT 属性计算FORALL迭代影响行数 ,用%BULK_ROWCOUNT 属性计算FORALL...8.SQL优化学习笔记 9.给Oracle存储过程传入数组(这是自己的)