一个查询的where列如果多于3个，是不是不走索引更好！有例子，希望大家积极讨论一下。

显示全部楼层 · 2013-1-18 08:52:19

一个生产库的慢日志记录的sql：
select accountmod0_.account_id as account1_0_, accountmod0_.account_type_id as account2_0_, accountmod0_.hotel_id as hotel3_0_, accountmod0_.hotelgroup_id as hotelgroup4_0_, accountmod0_.register_id as register5_0_, accountmod0_.guest_id as guest6_0_, accountmod0_.group_id as group7_0_, accountmod0_.company_id as company8_0_, accountmod0_.money as money0_, accountmod0_.payment_method_id as payment10_0_, accountmod0_.is_reversed as is11_0_, accountmod0_.is_balanced as is12_0_, accountmod0_.from_account_id as from13_0_, accountmod0_.to_account_id as to14_0_, accountmod0_.cash_bill_no as cash15_0_, accountmod0_.shift_id as shift16_0_, accountmod0_.account_date as account17_0_, accountmod0_.created_at as created18_0_, accountmod0_.note as note0_, accountmod0_.hotel_operator_id as hotel20_0_, accountmod0_.pos_site_id as pos21_0_, accountmod0_.room_order_id as room22_0_, accountmod0_.close_date as close23_0_, accountmod0_.close_shift_id as close24_0_, accountmod0_.close_hotel_operator_id as close25_0_, accountmod0_.updated_at as updated26_0_, accountmod0_.credit_card_type_id as credit27_0_, accountmod0_.account_page as account28_0_, accountmod0_.ar_date as ar29_0_, accountmod0_.ar_shift_id as ar30_0_, accountmod0_.ar_operator_id as ar31_0_, accountmod0_.is_hedged as is32_0_, accountmod0_.source_room_id as source33_0_, accountmod0_.hedge_date as hedge34_0_, accountmod0_.hedge_account_id as hedge35_0_, accountmod0_.foreign_currency_code as foreign36_0_, accountmod0_.foreign_money as foreign37_0_, accountmod0_.card_type_id as card38_0_, accountmod0_.card_no as card39_0_, accountmod0_.voucher_type_id as voucher40_0_, accountmod0_.voucher_no as voucher41_0_, accountmod0_.owner_id as owner42_0_, accountmod0_.owner_type as owner43_0_, accountmod0_.balance_no as balance44_0_, accountmod0_.ar_no as ar45_0_, accountmod0_.meeting_room_id as meeting46_0_, accountmod0_.sign_guest as sign47_0_, accountmod0_.res_revenue_group_id as res48_0_, accountmod0_.res_discount_id as res49_0_, accountmod0_.credit_card_no as credit50_0_, accountmod0_.phone_bill_id as phone51_0_, accountmod0_.product_bill_id as product52_0_ from accounts as accountmod0_where 1=1 and accountmod0_.hotel_id='cnbjbjb132' and accountmod0_.account_type_id=10 and (accountmod0_.shift_id=3 or accountmod0_.shift_id=3+10) and accountmod0_.account_date>='2012-11-24' order by accountmod0_.account_id desc limit 18; 其执行计划： id | select_type | table| type| possible_keys | key | key_len | ref| rows | Extra |
+----+-------------+--------------+-------+-------------------------+---------+---------+------+------+-------------+
|1 | SIMPLE| accountmod0_ | index | FK_R_NA,FK_R_OG,FK_R_QV | PRIMARY | 77| NULL | 5094 | Using where |
+----+-------------+--------------+-------+-------------------------+---------+- 执行时间超过1分钟。大家觉得有优化的余地吗？怎么走索引或者不走？

千问 · 2013-1-18 08:52:19

手动将or改成UNION ALL试试。不过5.0后会自动将or变成union了。不要在where之后出现3+10这样的运算，给个定值。
将where后面的字段，包括order by后面的字段都加入联合索引中。
好多字段，有不需要的大字段没，数据是否也很多，考虑水平垂直切分下。

千问 · 2013-1-18 08:52:19

我的mysql版本时5.5的。accounts表目前有2千万条左右。我觉得主要考虑一下索引问题，建立了联合索引，但是并不能走。

千问 · 2013-1-18 08:52:19

楼主可以看看这个帖子
http://www.itpub.net/thread-1364684-1-1.html

千问 · 2013-1-18 08:52:19

现在的情况是hotel_id有索引，account_type_id有索引，account_date和hotel_id建立的联合索引。我希望能走index merge，但却扫描了主键索引。一个原因是mysql好像不能有3个索引merge。

千问 · 2013-1-18 08:52:19

show create table accounts来看下

千问 · 2013-1-18 08:52:19

rushm 发表于 2012-12-7 13:26
show create table accounts来看下
mysql> show create table accounts\G
*************************** 1. row ***************************
Table: accounts
Create Table: CREATE TABLE `accounts` (
`account_id` varchar(25) NOT NULL,
`account_type_id` int(11) NOT NULL,
`hotel_id` char(10) DEFAULT NULL,
`register_id` varchar(25) DEFAULT NULL,
`guest_id` varchar(50) DEFAULT NULL,
`group_id` varchar(50) DEFAULT NULL,
`company_id` int(11) DEFAULT NULL,
`money` double NOT NULL,
`phone_bill_id` varchar(50) DEFAULT NULL,
`product_bill_id` varchar(50) DEFAULT NULL,
`payment_method_id` tinyint(4) DEFAULT NULL,
`is_reversed` tinyint(1) NOT NULL DEFAULT '0',
`is_balanced` tinyint(1) DEFAULT '0',
`from_account_id` varchar(25) DEFAULT NULL,
`to_account_id` varchar(25) DEFAULT NULL,
`cash_bill_no` varchar(25) DEFAULT NULL,
`shift_id` tinyint(4) DEFAULT NULL,
`account_date` date NOT NULL,
`created_at` datetime DEFAULT NULL,
`note` varchar(255) DEFAULT NULL,
`hotel_operator_id` varchar(50) NOT NULL,
`pos_site_id` int(11) DEFAULT NULL,
`room_order_id` varchar(50) DEFAULT NULL,
`close_date` date DEFAULT NULL,
`close_shift_id` tinyint(4) DEFAULT NULL,
`close_hotel_operator_id` varchar(50) DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`credit_card_type_id` int(11) DEFAULT NULL,
`account_page` tinyint(4) DEFAULT '0',
`ar_date` date DEFAULT NULL,
`ar_shift_id` tinyint(4) DEFAULT NULL,
`ar_operator_id` varchar(50) DEFAULT NULL,
`is_hedged` tinyint(1) NOT NULL DEFAULT '0',
`source_room_id` int(11) DEFAULT NULL,
`hedge_date` date DEFAULT NULL,
`hedge_account_id` varchar(25) DEFAULT NULL,
`foreign_currency_code` varchar(10) DEFAULT NULL,
`foreign_money` double DEFAULT NULL,
`card_type_id` varchar(25) DEFAULT NULL,
`card_no` varchar(25) DEFAULT NULL,
`voucher_type_id` varchar(25) DEFAULT NULL,
`voucher_no` varchar(25) DEFAULT NULL,
`owner_id` varchar(25) DEFAULT NULL,
`owner_type` tinyint(4) DEFAULT NULL,
`hotelgroup_id` varchar(50) DEFAULT NULL,
`meeting_room_id` int(11) DEFAULT NULL,
`balance_no` varchar(25) DEFAULT NULL,
`ar_no` varchar(25) DEFAULT NULL,
`sign_guest` varchar(50) DEFAULT NULL,
`res_revenue_group_id` int(11) DEFAULT NULL,
`res_discount_id` int(11) DEFAULT NULL,
`credit_card_no` varchar(25) DEFAULT NULL,
PRIMARY KEY (`account_id`),
KEY `FK_R_LT` (`phone_bill_id`),
KEY `FK_R_LX` (`from_account_id`),
KEY `FK_R_LY` (`to_account_id`),
KEY `FK_R_LZ` (`hotel_operator_id`),
KEY `FK_R_MQ` (`product_bill_id`),
KEY `FK_R_NA` (`account_type_id`),
KEY `FK_R_OE` (`room_order_id`),
KEY `FK_R_NV` (`group_id`),
KEY `FK_R_NU` (`register_id`),
KEY `FK_R_NS` (`company_id`),
KEY `FK_R_OG` (`hotel_id`,`updated_at`),
KEY `owner_id` (`owner_id`),
KEY `FK_R_FX` (`hotelgroup_id`),
KEY `FK_R_IQ` (`meeting_room_id`),
KEY `FK_R_NR` (`pos_site_id`),
KEY `FK_R_KF` (`res_revenue_group_id`),
KEY `voucher_type_id` (`voucher_type_id`),
KEY `FK_R_QV` (`account_date`,`hotel_id`),
KEY `idx_close_date` (`close_date`,`hotel_id`),
KEY `idx_bill` (`cash_bill_no`,`hotel_id`),
KEY `idx_guest` (`guest_id`),
KEY `idx_ar_date` (`ar_date`,`hotel_id`),
KEY `idx_card` (`card_type_id`,`card_no`),
CONSTRAINT `FK_R_FX` FOREIGN KEY (`hotelgroup_id`) REFERENCES `hotelgroups` (`hotelgroup_id`),
CONSTRAINT `FK_R_IQ` FOREIGN KEY (`meeting_room_id`) REFERENCES `meeting_rooms` (`id`),
CONSTRAINT `FK_R_KF` FOREIGN KEY (`res_revenue_group_id`) REFERENCES `res_revenue_groups` (`id`),
CONSTRAINT `FK_R_LZ` FOREIGN KEY (`hotel_operator_id`) REFERENCES `users` (`user_id`),
CONSTRAINT `FK_R_MQ` FOREIGN KEY (`product_bill_id`) REFERENCES `product_bills` (`id`),
CONSTRAINT `FK_R_NA` FOREIGN KEY (`account_type_id`) REFERENCES `account_types` (`id`),
CONSTRAINT `FK_R_NR` FOREIGN KEY (`pos_site_id`) REFERENCES `pos_sites` (`id`),
CONSTRAINT `FK_R_NS` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
CONSTRAINT `FK_R_NU` FOREIGN KEY (`register_id`) REFERENCES `registers` (`id`),
CONSTRAINT `FK_R_NV` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`),
CONSTRAINT `FK_R_OG` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`hotel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

千问 · 2013-1-18 08:52:19

本帖最后由 rushm 于 2012-12-11 13:46 编辑
ifeelstupid 发表于 2012-12-10 12:40
mysql> show create table accounts\G
*************************** 1. row ************************** ...
索引方面：含null值的联合索引是无效的。试试where后面的字段建个联合，用sql_no_cache调试，强制走索引试试效率有没快。
建议将DEFAULT NULL替换成NOT NULL DEFAULT 0
表体积：还有这表也太不苗条了吧，字段多，约束多，为了效率得瘦下身

千问 · 2013-1-18 08:52:19

rushm 发表于 2012-12-11 13:42
索引方面：含null值的联合索引是无效的。试试where后面的字段建个联合，用sql_no_cache调试，强制走索引试 ...
嗯，好的，谢谢。我测试了很多索引结果，发现有个速度很快，索引现在优化结果是比较明显的。至于你说的对表对象瘦身，我也觉得很有必要，因为这个表的数据每天都在增。但是请问一下，你有什么好的方法吗？最好能具体点，因为在实际生产中，对大表瘦身还没做过。

千问 · 2013-1-18 08:52:19

ifeelstupid 发表于 2012-12-11 14:11
嗯，好的，谢谢。我测试了很多索引结果，发现有个速度很快，索引现在优化结果是比较明显的。至于你说的对 ...
结构or数据拆分，分好多种方式啊，维护停机肯定要的，得找开发配合弄，商量哪种维护成本低就用那种