Maison > Article > développement back-end > Mysql数据库某个表连接查询很慢,如何优化?
两个表连接查询很慢,但是如果单查一个表则很快,或者连接查询其他的表也很快。
查询语句(大约半秒的查询时间):
<code>SELECT * FROM member m LEFT JOIN gift g ON g.mid = m.id WHERE m.status = 0 AND m.wid = 236 ORDER BY m.id DESC LIMIT 0,20</code>
member表结构(约40000条数据):
<code>CREATE TABLE `member` ( `id` int(11) NOT NULL AUTO_INCREMENT, `wid` int(11) NOT NULL, `wxid` varchar(30) DEFAULT NULL, `wid_wxid` varchar(30) DEFAULT NULL, `cpai` varchar(32) NOT NULL, `name` varchar(20) DEFAULT NULL, `sex` varchar(10) DEFAULT NULL, `tel` varchar(11) DEFAULT NULL, `lxtel` varchar(11) DEFAULT NULL, `xingge` varchar(10) DEFAULT NULL, `qq` int(11) DEFAULT NULL, `birthday` varchar(12) DEFAULT NULL, `cartype` varchar(20) DEFAULT NULL, `carclass` varchar(20) DEFAULT NULL, `bxtime` varchar(20) DEFAULT '0.00', `next_bxtime` varchar(20) DEFAULT '0.00', `bytime` varchar(20) DEFAULT '0.00', `next_bytime` varchar(20) DEFAULT '0.00', `bylong` varchar(20) DEFAULT '0.00', `next_bylong` varchar(20) DEFAULT '0.00', `engine` varchar(50) DEFAULT NULL, `chejia` varchar(50) DEFAULT NULL, `danganhao` varchar(30) DEFAULT NULL, `buy_date` varchar(20) DEFAULT NULL, `first_date` varchar(20) DEFAULT NULL, `weixiu_times` tinyint(4) DEFAULT NULL, `weixiu_money` decimal(8,2) DEFAULT NULL, `last_date` varchar(20) DEFAULT NULL, `last_jieche` varchar(50) DEFAULT NULL, `cpai_register` varchar(20) DEFAULT NULL, `identify_number` varchar(20) DEFAULT NULL, `order_number` varchar(20) DEFAULT NULL, `sale_date` varchar(20) DEFAULT NULL, `carkilometre` int(11) DEFAULT '0', `address` varchar(32) DEFAULT NULL, `remark` varchar(32) DEFAULT NULL, `is_check` int(11) DEFAULT '0', `password` char(6) DEFAULT NULL, `salt` char(32) DEFAULT NULL, `pay_password` char(32) DEFAULT NULL, `pay_salt` char(6) DEFAULT NULL, `status` tinyint(4) DEFAULT '0', `time` varchar(24) NOT NULL, `headimgurl` varchar(200) DEFAULT NULL, `nickname` varchar(32) DEFAULT NULL, `isattention` tinyint(4) DEFAULT '0', `paid_price` decimal(8,2) DEFAULT '0.00', `source` int(11) DEFAULT NULL, `add_date` datetime DEFAULT NULL, `add_by` varchar(30) DEFAULT '', `update_date` datetime DEFAULT NULL, `update_by` varchar(30) DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=59151 DEFAULT CHARSET=utf8; </code>
gift表结构(约4000条数据):
<code>CREATE TABLE `member_giftamount` ( `id` int(16) NOT NULL AUTO_INCREMENT, `wid` int(32) NOT NULL, `mid` int(11) NOT NULL, `auth_wid` int(11) DEFAULT NULL, `wxid` varchar(64) DEFAULT NULL, `tel` varchar(30) DEFAULT NULL, `amount` decimal(8,2) DEFAULT '0.00', `gift_amount` decimal(8,2) DEFAULT '0.00', `refund_amount` decimal(8,2) DEFAULT '0.00', `credits` int(11) DEFAULT '0', `total` decimal(8,2) DEFAULT '0.00', `market_total` decimal(8,2) DEFAULT '0.00', `wid_wxid` varchar(64) DEFAULT NULL, `wait_total` decimal(8,2) DEFAULT '0.00', `add_date` datetime DEFAULT NULL, `add_by` varchar(30) DEFAULT NULL, `update_date` datetime DEFAULT NULL, `update_by` varchar(30) DEFAULT NULL, `sign_days` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=4254 DEFAULT CHARSET=utf8; </code>
两个表连接查询很慢,但是如果单查一个表则很快,或者连接查询其他的表也很快。
查询语句(大约半秒的查询时间):
<code>SELECT * FROM member m LEFT JOIN gift g ON g.mid = m.id WHERE m.status = 0 AND m.wid = 236 ORDER BY m.id DESC LIMIT 0,20</code>
member表结构(约40000条数据):
<code>CREATE TABLE `member` ( `id` int(11) NOT NULL AUTO_INCREMENT, `wid` int(11) NOT NULL, `wxid` varchar(30) DEFAULT NULL, `wid_wxid` varchar(30) DEFAULT NULL, `cpai` varchar(32) NOT NULL, `name` varchar(20) DEFAULT NULL, `sex` varchar(10) DEFAULT NULL, `tel` varchar(11) DEFAULT NULL, `lxtel` varchar(11) DEFAULT NULL, `xingge` varchar(10) DEFAULT NULL, `qq` int(11) DEFAULT NULL, `birthday` varchar(12) DEFAULT NULL, `cartype` varchar(20) DEFAULT NULL, `carclass` varchar(20) DEFAULT NULL, `bxtime` varchar(20) DEFAULT '0.00', `next_bxtime` varchar(20) DEFAULT '0.00', `bytime` varchar(20) DEFAULT '0.00', `next_bytime` varchar(20) DEFAULT '0.00', `bylong` varchar(20) DEFAULT '0.00', `next_bylong` varchar(20) DEFAULT '0.00', `engine` varchar(50) DEFAULT NULL, `chejia` varchar(50) DEFAULT NULL, `danganhao` varchar(30) DEFAULT NULL, `buy_date` varchar(20) DEFAULT NULL, `first_date` varchar(20) DEFAULT NULL, `weixiu_times` tinyint(4) DEFAULT NULL, `weixiu_money` decimal(8,2) DEFAULT NULL, `last_date` varchar(20) DEFAULT NULL, `last_jieche` varchar(50) DEFAULT NULL, `cpai_register` varchar(20) DEFAULT NULL, `identify_number` varchar(20) DEFAULT NULL, `order_number` varchar(20) DEFAULT NULL, `sale_date` varchar(20) DEFAULT NULL, `carkilometre` int(11) DEFAULT '0', `address` varchar(32) DEFAULT NULL, `remark` varchar(32) DEFAULT NULL, `is_check` int(11) DEFAULT '0', `password` char(6) DEFAULT NULL, `salt` char(32) DEFAULT NULL, `pay_password` char(32) DEFAULT NULL, `pay_salt` char(6) DEFAULT NULL, `status` tinyint(4) DEFAULT '0', `time` varchar(24) NOT NULL, `headimgurl` varchar(200) DEFAULT NULL, `nickname` varchar(32) DEFAULT NULL, `isattention` tinyint(4) DEFAULT '0', `paid_price` decimal(8,2) DEFAULT '0.00', `source` int(11) DEFAULT NULL, `add_date` datetime DEFAULT NULL, `add_by` varchar(30) DEFAULT '', `update_date` datetime DEFAULT NULL, `update_by` varchar(30) DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=59151 DEFAULT CHARSET=utf8; </code>
gift表结构(约4000条数据):
<code>CREATE TABLE `member_giftamount` ( `id` int(16) NOT NULL AUTO_INCREMENT, `wid` int(32) NOT NULL, `mid` int(11) NOT NULL, `auth_wid` int(11) DEFAULT NULL, `wxid` varchar(64) DEFAULT NULL, `tel` varchar(30) DEFAULT NULL, `amount` decimal(8,2) DEFAULT '0.00', `gift_amount` decimal(8,2) DEFAULT '0.00', `refund_amount` decimal(8,2) DEFAULT '0.00', `credits` int(11) DEFAULT '0', `total` decimal(8,2) DEFAULT '0.00', `market_total` decimal(8,2) DEFAULT '0.00', `wid_wxid` varchar(64) DEFAULT NULL, `wait_total` decimal(8,2) DEFAULT '0.00', `add_date` datetime DEFAULT NULL, `add_by` varchar(30) DEFAULT NULL, `update_date` datetime DEFAULT NULL, `update_by` varchar(30) DEFAULT NULL, `sign_days` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=4254 DEFAULT CHARSET=utf8; </code>
索引优化:g表mid加索引;m表status和wid加索引;慢查询看一下是否用到了索引,尽量让rows数小点
建立索引
将member表中的(id,wid,wxid,wid_wxid)建立索引
将gift表中的(id,wid,mid,auth_wid,wxid)建立索引
如果你where条件中经常出现一些字段,也要建立索引。
不要用*号:只列出需要的字段
SQL参考:sql是我直接写的,没测试
<code>select 不要用* from (SELECT m.status,m.wid,m.id FROM member m WHERE m.status = 0 AND m.wid = 236) a LEFT JOIN gift g ON g.mid = a.id ORDER BY a.id DESC LIMIT 0,20</code>
给gift的mid字段建个外键,关联member表的id
参考下有没有价值,我不懂数据的事!
http://www.zhihu.com/question/37777220
麻烦把执行计划贴出来
建立索引,使用子查询。