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

mysql  排序这么优化？感觉就算根据主键排序，数据多点儿还是慢

对第一个语句，我尝试去掉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)

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

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

我一直有一个疑问，记录创建时间字段使用datetime和int类型有什么区别吗？两者对索引优化是否存在影响？

顶，学习中。。。

<%
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楼

mysql如何查询需要优化的语句，比如慢查询的步奏，如何找出需要通知程序员修改或者优化的sql语句，如何快速找到mysql瓶颈

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

普通服务器有这种工具吗

你怎么能这么谦虚呢，大家都在努力膜拜呢

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

如果这类查询很多，建议就不要查库了，考虑使用更专业的检索工具，比如Lucence

电商项目中有这样一张表（使用的mysql），一共76个字段，其中有12个字段需要用于比较频繁的搜索（并且有3个字段需要进行模糊匹配搜索），我有两个问题：
（1）如何建立索引可以加快查询速度？
（2）如果不使用全文索引，有没有什么替代方案，可以解决模糊匹配搜索的问题（比如select * from t where name like '%words%'）
谢谢大师

十分想请大师分享下建立索引的经验
我平时简历索引是这样的
比如订单信息的话
建立
订单号  唯一聚集索引
其他的比如   客户编号 供应商编号 商品编号 这些建立非聚集不唯一索引 仅此而已 想更深入的跟大师学学

可以详细说明一下“最后建议不要在数据库中使用外键，让应用程序来保证。 ”的原因吗？我们公司在项目中经常使用外键，用程序来保证不是相对而言更加复杂了吗？

对于多维度分类（七八个分类甚至十几个，或叫标签也成），同时又需要排序的内容（文章内容也很大），大家有什么好的建议

对于这类应用，我很想分表，但又需要排序，所以纠结中....

至少我目前是将文章内容分出去了，不过一到查询文章内容排序的时候问题就又来了

我也是这样，简单的会，仔细写也会写出来，但是就是不知道有没有更快或者更好的

大师，想问问mysql的“全文索引”该如何准确的运用到中文的搜索上？

问个小白的问题，大师建议一般索引类型和索引方法使用哪种，还是说针对不同的逻辑用不同的》？

分页该怎么优化才行？？？

无关技术方面：
咨询一下，现在mysql新的版本，5.5.45后貌似修改了开源协议。
是否意味着今后我们商业化使用mysql将受到限制？
如果甲骨文真周到那一步，rds是否会受到影响？

一个疑惑：
为什么很少见到有人用mysql正则匹配？性能不好还是什么原因？

有个问题：分类表TQueCategory，问题表TQuestion（T-SQL)

CREATE TABLE TQueCategory
(
ID INT IDENTITY(1,1) PRIMARY KEY,        --问题分类ID
NAME VARCHAR(20)        --问题分类名称
)

CREATE TABLE TQuestion
(
ID INT IDENTITY(1,1) PRIMARY KEY,        --问题ID
CateID INT NOT NULL,        --问题分类ID
TITLE VARCHAR(50),        --问题标题
CONTENT VARCHAR(500)        --问题内容
)

当前要统计某个分类下的问题数，有两种方式：

1.每次统计，在TQuestion通过CateID进行分组统计
SELECT CateID,COUNT(1) AS QueNum FROM TQuestion GROUP BY CateID WHERE 1=1

2.在TQueCategory表增加字段QueNum，用于标识该分类下的问题数量

ALTER TABLE TQueCategory ADD QueNum INT

SELECT CateID,QueNum FROM TQueCategory

问：在哪种业务应用场景下采用上面哪种方式性能比较好，为什么？

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

“最后建议不要在数据库中使用外键，让应用程序来保证。 ”
这个不敢苟同，用不用外键，这个还是得看具体的业务环境吧，不能一刀切。

这篇博文的讨论不错。
《数据库设计中一个矛盾:数据库外键,用还是不用？你怎么看.?》
www点cnblogs点com/chenkai/archive/2009/11/11/1601193.html

难得大师出现。我想问下,sql优化一般从那几个方面入手？多表之间的连接方式：Nested Loops，Hash Join 和 Sort Merge Join，是不是Hash Join最优连接？

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

很给力的建议，有机会学习下

请教一下有关模糊查询的优化，有没有什么比较成熟的好的策略？