DBA专家门诊一期:索引与sql优化-问答-阿里云开发者社区-阿里云

开发者社区> 问答> 正文

DBA专家门诊一期:索引与sql优化

xiaofanqie 2014-12-16 15:15:51 27425
亲爱的云友,
       11月份的RDS产品经理访谈推出后,大家反应非常火爆,很多云友都提了数据库运维方面的问题。为了更好更多帮助大家,RDS正式推出数据库运维系列专题:DBA专家门诊。我们邀请阿里集团资深数据库专家坐诊RDS版块,与广大RDS用户和数据库用户进行深度交流、学习。

DBA专家门诊一期:索引与sql优化
做过数据库管理的同学都知道,sql性能很大程度上决定着数据库系统的工作能力,很多业务可能会因为sql写的不好,缺失索引等导致数据库系统性能低下,甚至导致故障发生,我们本期的门诊专题就是索引与sql优化。

无论你是否使用RDS,只要你有相关的问题和疑惑都可跟帖提问,主任医师将会针对云友的问题和大家交流学习。门诊结束后,所有提问的朋友,每人都将获赠1本《凌云》杂志  第四期,赶快留下您的问题吧!
提问时间:即日起——12月23日15:00
答疑时间:12月23日15:00-17:00
提问范围:索引与sql优化

本期主任医师介绍:
本期的DBA专家门诊主任医师 玄惭,阿里云集团资深DBA,2010年加入阿里巴巴集团DBA团队,负责淘宝,天猫等数据库系统的运维工作,精通MySQL,MSSQL,Oracle,对数据库运维,调优具有丰富的经验,2012年开始负责阿里云RDS产品运维至今,对RDS数据库系统具有很深刻的了解。

后期我们的门诊也会开设其它专题,有兴趣的朋友请持续关注我们的门诊专题 。

凌云杂志:
[attachment=66313]
SQL 运维 关系型数据库 数据库 数据库管理 索引 RDS
分享到
取消 提交回答
全部回答(29)
  • switch-case
    2017-02-15 14:00:45
    ReDBA专家门诊一期:索引与sql优化
    mysql  排序这么优化?感觉就算根据主键排序,数据多点儿还是慢
    0 0
  • yuantel
    2014-12-24 11:08:38
    回47楼yuantel的帖子
    对第一个语句,我尝试去掉Left Join,仍然还有此问题,所以比较困惑
    mysql> explain SELECT b.brand_id, b.brand_name, COUNT(*) AS goods_num FROM `hcsyw`.`ecs_brand`AS b, `hcsyw`.`ecs_goods` AS g   WHERE g.brand_id = b.brand_id AND g.cat_id='1671'  AND b.is_show = 1  AND g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0 GROUP BY b.brand_id HAVING goods_num > 0 ORDER BY b.sort_order, b.brand_id ASC;
    +----+-------------+-------+--------+-------------------------------------------------------+---------+---------+------------------+------+----------------------------------------------+
    | id | select_type | table | type   | possible_keys                                         | key     | key_len | ref              | rows | Extra                                        |
    +----+-------------+-------+--------+-------------------------------------------------------+---------+---------+------------------+------+----------------------------------------------+
    |  1 | SIMPLE      | g     | ref    | cat_id,brand_id,idx_catid_isdel_ison,idx_sal_sort_gid | cat_id  | 2       | const            | 1826 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE      | b     | eq_ref | PRIMARY,is_show                                       | PRIMARY | 2       | hcsyw.g.brand_id |    1 | Using where                                  |
    +----+-------------+-------+--------+-------------------------------------------------------+---------+---------+------------------+------+----------------------------------------------+
    2 rows in set (0.00 sec)

    -------------------------

    回49楼玄惭的帖子
    mysql> show create table ecs_brand;
    | Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
    | ecs_brand | CREATE TABLE `ecs_brand` (
      `brand_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
      `brand_name` varchar(60) NOT NULL DEFAULT '',
      `brand_logo` varchar(80) NOT NULL DEFAULT '',
      `brand_desc` text NOT NULL,
      `site_url` varchar(255) NOT NULL DEFAULT '',
      `sort_order` tinyint(3) unsigned NOT NULL DEFAULT '50',
      `is_show` tinyint(1) unsigned NOT NULL DEFAULT '1',
      `brand_name1` varchar(60) DEFAULT NULL,
      `lang_flag` int(2) DEFAULT '0',
      PRIMARY KEY (`brand_id`),
      KEY `is_show` (`is_show`),
      KEY `idx_sort` (`sort_order`)
    ) ENGINE=MyISAM AUTO_INCREMENT=52167 DEFAULT CHARSET=utf8 |

    mysql> show create table ecs_goods;
    | Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
    | ecs_goods | CREATE TABLE `ecs_goods` (
      `goods_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
      `cat_id` smallint(5) unsigned NOT NULL DEFAULT '0',
      `goods_sn` varchar(60) NOT NULL DEFAULT '',
      `goods_name` varchar(120) NOT NULL DEFAULT '',
      `goods_name_style` varchar(60) NOT NULL DEFAULT '+',
      `click_count` int(10) unsigned NOT NULL DEFAULT '0',
      `brand_id` smallint(5) unsigned NOT NULL DEFAULT '0',
      `provider_name` varchar(100) NOT NULL DEFAULT '',
      `goods_number` smallint(5) unsigned NOT NULL DEFAULT '0',
      `goods_weight` decimal(10,3) unsigned NOT NULL DEFAULT '1.000',
      `market_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
      `shop_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
      `promote_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
      `promote_start_date` int(11) unsigned NOT NULL DEFAULT '0',
      `promote_end_date` int(11) unsigned NOT NULL DEFAULT '0',
      `warn_number` tinyint(3) unsigned NOT NULL DEFAULT '1',
      `keywords` varchar(255) NOT NULL DEFAULT '',
      `goods_brief` varchar(255) NOT NULL DEFAULT '',
      `goods_desc` text NOT NULL,
      `goods_thumb` varchar(255) NOT NULL DEFAULT '',
      `goods_img` varchar(255) NOT NULL DEFAULT '',
      `original_img` varchar(255) NOT NULL DEFAULT '',
      `is_real` tinyint(3) unsigned NOT NULL DEFAULT '1',
      `extension_code` varchar(30) NOT NULL DEFAULT '',
      `is_on_sale` tinyint(1) unsigned NOT NULL DEFAULT '1',
      `is_alone_sale` tinyint(1) unsigned NOT NULL DEFAULT '1',
      `is_shipping` tinyint(1) unsigned NOT NULL DEFAULT '0',
      `integral` int(10) unsigned NOT NULL DEFAULT '0',
      `add_time` int(10) unsigned NOT NULL DEFAULT '0',
      `sort_order` smallint(4) unsigned NOT NULL DEFAULT '100',
      `is_delete` tinyint(1) unsigned NOT NULL DEFAULT '0',
      `is_best` tinyint(1) unsigned NOT NULL DEFAULT '0',
      `is_new` tinyint(1) unsigned NOT NULL DEFAULT '0',
      `is_hot` tinyint(1) unsigned NOT NULL DEFAULT '0',
      `is_promote` tinyint(1) unsigned NOT NULL DEFAULT '0',
      `bonus_type_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
      `last_update` int(10) unsigned NOT NULL DEFAULT '0',
      `goods_type` smallint(5) unsigned NOT NULL DEFAULT '0',
      `seller_note` varchar(255) NOT NULL DEFAULT '',
      `give_integral` int(11) NOT NULL DEFAULT '-1',
      `rank_integral` int(11) NOT NULL DEFAULT '-1',
      `suppliers_id` smallint(5) unsigned DEFAULT NULL,
      `is_check` tinyint(1) unsigned DEFAULT NULL,
      `num_iid` bigint(11) NOT NULL DEFAULT '0',
      `nick` varchar(100) DEFAULT NULL,
      `click_url` text,
      `shop_title` varchar(180) DEFAULT NULL,
      `shop_click_url` varchar(255) DEFAULT NULL,
      `item_location` varchar(60) DEFAULT NULL,
      `volume` int(11) DEFAULT NULL,
      `commission_rate` decimal(10,2) DEFAULT NULL,
      `commission` decimal(10,2) DEFAULT NULL,
      `commission_num` int(11) DEFAULT NULL,
      `commission_volume` decimal(10,2) DEFAULT NULL,
      `post_fee` decimal(10,2) DEFAULT NULL,
      `express_fee` decimal(10,2) DEFAULT NULL,
      `ems_fee` decimal(10,2) DEFAULT NULL,
      `seller_credit_score` varchar(120) DEFAULT NULL,
      `is_tbao` tinyint(1) unsigned NOT NULL DEFAULT '0',
      `procotent` varchar(40) DEFAULT NULL,
      `lang_flag` int(2) DEFAULT '0',
      PRIMARY KEY (`goods_id`),
      KEY `goods_sn` (`goods_sn`),
      KEY `cat_id` (`cat_id`),
      KEY `last_update` (`last_update`),
      KEY `brand_id` (`brand_id`),
      KEY `goods_weight` (`goods_weight`),
      KEY `promote_end_date` (`promote_end_date`),
      KEY `promote_start_date` (`promote_start_date`),
      KEY `goods_number` (`goods_number`),
      KEY `sort_order` (`sort_order`),
      KEY `idx_catid_isdel_ison` (`cat_id`,`is_delete`,`is_on_sale`),
      KEY `idx_gid_bid_cid_onsal_isdel_gnum` (`goods_id`,`brand_id`,`cat_id`,`is_delete`,`is_on_sale`,`goods_name`),
      KEY `idx_sal_sort_gid` (`is_on_sale`,`sort_order`,`goods_id`),
      KEY `idx_gid_sort` (`goods_id`,`sort_order`),
      KEY `idx_sort_gid` (`sort_order`,`goods_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1029092 DEFAULT CHARSET=utf8
    0 0
  • dentrite
    2014-12-24 10:02:58
    ReDBA专家门诊一期:索引与sql优化
    我一直有一个疑问,记录创建时间字段使用datetime和int类型有什么区别吗?两者对索引优化是否存在影响?
    0 0
  • silan_nta
    2014-12-24 08:36:47
    ReDBA专家门诊一期:索引与sql优化
    顶,学习中。。。
    0 0
  • zuijh
    2014-12-24 08:34:36
    <%
    Set conn=Server.CreateObject("ADODB.CONNECTION")
    Set rs=Server.CreateObject("ADODB.RecordSet")
    conn.open Application("sjjh_usermdb")
    rs.Open "Select * from sjjh_jhjl  Order by a DESC", conn, 1,1
    do while not rs.bof and not rs.eof
    %>
      <tr bgcolor="" onMouseOver="this.bgColor='#85C2E0';" onMouseOut="this.bgColor='';">
        <td align="center"><%=rs("a")%></td>
        <td align="left"><%=rs("d")%></td>
      </tr>
    <%
    rs.movenext
    loop
    rs.close
    set rs=nothing
    conn.close
    set conn=nothing
    %>
    代码二:
    <%
    rs.open "select top 10 jh_name,jh_peiou from sjjh_jhuser where jh_zt='正常' and jh_sex='男' and jh_peiou<>'无' order by jh_jhdate desc",conn,1,1
    %>
    <font color="#38e9f2"><b>江湖10对金婚</b></font><br />
    <%do while not rs.eof
    x=x+1%>
    <%=rs("jh_name")%><br />
    (<%=rs("jh_peiou")%>)<br />
    <%
    if x=10 then exit do
    rs.movenext
    loop
    rs.close
    x=0%>
    楼主 我这两段代码的数据调用有问题么? asp+mssql

    -------------------------

    砖家兄 为嘛无视我问题呀   41楼

    0 0
滑动查看更多
+ 订阅

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

推荐文章
相似问题