问题遇到的现象和发生背景
2个表 vipcard_ar_master ，vipcard
CREATE TABLE vipcard_ar_master (
accnt varchar(20) COLLATE utf8_bin NOT NULL,
hotelid varchar(7) COLLATE utf8_bin NOT NULL,
sign int(11) DEFAULT NULL,
name varchar(100) COLLATE utf8_bin NOT NULL,
sta varchar(1) COLLATE utf8_bin NOT NULL,
artag1 varchar(5) COLLATE utf8_bin DEFAULT NULL,
artag2 varchar(5) COLLATE utf8_bin DEFAULT NULL,
applname varchar(100) COLLATE utf8_bin DEFAULT NULL,
applunit varchar(100) COLLATE utf8_bin DEFAULT NULL,
mobile varchar(20) COLLATE utf8_bin DEFAULT NULL,
phone varchar(20) COLLATE utf8_bin DEFAULT NULL,
email varchar(100) COLLATE utf8_bin DEFAULT NULL,
fax varchar(20) COLLATE utf8_bin DEFAULT NULL,
address1 varchar(250) COLLATE utf8_bin DEFAULT NULL,
address2 varchar(250) COLLATE utf8_bin DEFAULT NULL,
brandids varchar(255) COLLATE utf8_bin DEFAULT NULL,
hotels varchar(1000) COLLATE utf8_bin DEFAULT NULL,
memo varchar(1000) COLLATE utf8_bin DEFAULT NULL,
charge decimal(19,4) NOT NULL,
credit decimal(19,4) NOT NULL,
charge0 decimal(19,4) NOT NULL,
credit0 decimal(19,4) NOT NULL,
credit1 decimal(19,4) DEFAULT NULL,
charge1 decimal(19,4) DEFAULT NULL,
vipcard_limite decimal(19,4) NOT NULL,
accredit decimal(19,4) NOT NULL,
arr date NOT NULL,
dep date NOT NULL,
cby varchar(10) COLLATE utf8_bin NOT NULL,
changed datetime DEFAULT NULL,
chargeby varchar(10) COLLATE utf8_bin DEFAULT NULL,
chargetime datetime DEFAULT NULL,
creditby varchar(10) COLLATE utf8_bin DEFAULT NULL,
credittime datetime DEFAULT NULL,
logmark int(11) NOT NULL,
PRIMARY KEY (hotelid,accnt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
CREATE TABLE vipcard (
id varchar(20) COLLATE utf8_bin NOT NULL,
hotelid varchar(7) COLLATE utf8_bin NOT NULL,
sign int(11) DEFAULT NULL,
no varchar(20) COLLATE utf8_bin NOT NULL,
sno varchar(20) COLLATE utf8_bin DEFAULT NULL,
mno varchar(20) COLLATE utf8_bin DEFAULT NULL,
mid varchar(20) COLLATE utf8_bin DEFAULT NULL,
sta varchar(1) COLLATE utf8_bin NOT NULL,
type varchar(10) COLLATE utf8_bin NOT NULL,
card_class varchar(10) COLLATE utf8_bin NOT NULL,
src varchar(3) COLLATE utf8_bin NOT NULL,
issue_source varchar(20) COLLATE utf8_bin DEFAULT NULL,
channel varchar(10) COLLATE utf8_bin DEFAULT NULL,
fixed varchar(1) COLLATE utf8_bin DEFAULT NULL,
name varchar(100) COLLATE utf8_bin DEFAULT NULL,
rmratecode varchar(20) COLLATE utf8_bin DEFAULT NULL,
posmode varchar(24) COLLATE utf8_bin DEFAULT NULL,
code3 varchar(30) COLLATE utf8_bin DEFAULT NULL,
code4 varchar(30) COLLATE utf8_bin DEFAULT NULL,
code5 varchar(30) COLLATE utf8_bin DEFAULT NULL,
araccnt1 varchar(20) COLLATE utf8_bin DEFAULT NULL,
pointaccnt varchar(20) COLLATE utf8_bin DEFAULT NULL,
ticketaccnt varchar(20) COLLATE utf8_bin DEFAULT NULL,
kno varchar(15) COLLATE utf8_bin DEFAULT NULL,
cno varchar(15) COLLATE utf8_bin DEFAULT NULL,
hno varchar(15) COLLATE utf8_bin DEFAULT NULL,
arr datetime DEFAULT NULL,
dep datetime DEFAULT NULL,
pwd varchar(10) COLLATE utf8_bin DEFAULT NULL,
checkpwd varchar(1) COLLATE utf8_bin DEFAULT NULL,
share varchar(1) COLLATE utf8_bin DEFAULT NULL,
pwd_q varchar(30) COLLATE utf8_bin DEFAULT NULL,
pwd_a varchar(30) COLLATE utf8_bin DEFAULT NULL,
crc varchar(20) COLLATE utf8_bin DEFAULT NULL,
flag varchar(40) COLLATE utf8_bin NOT NULL,
vipcard_limite decimal(19,4) NOT NULL,
charge decimal(19,4) NOT NULL,
credit decimal(19,4) NOT NULL,
lastnumb int(11) DEFAULT NULL,
card_hotelid varchar(20) COLLATE utf8_bin NOT NULL,
saleid varchar(50) COLLATE utf8_bin DEFAULT NULL,
invite_code varchar(20) COLLATE utf8_bin DEFAULT NULL,
iscommon varchar(1) COLLATE utf8_bin DEFAULT NULL,
recommender varchar(20) COLLATE utf8_bin DEFAULT NULL,
register_no varchar(20) COLLATE utf8_bin NOT NULL,
mobile_subscribe varchar(30) COLLATE utf8_bin DEFAULT NULL,
email_subscribe varchar(30) COLLATE utf8_bin DEFAULT NULL,
wechat_subscribe varchar(30) COLLATE utf8_bin DEFAULT NULL,
other_subscribe varchar(30) COLLATE utf8_bin DEFAULT NULL,
batch_id varchar(20) COLLATE utf8_bin DEFAULT NULL,
resby varchar(10) COLLATE utf8_bin NOT NULL,
reserved datetime NOT NULL,
ciby varchar(10) COLLATE utf8_bin DEFAULT NULL,
citime datetime DEFAULT NULL,
lostby varchar(10) COLLATE utf8_bin DEFAULT NULL,
losttime datetime DEFAULT NULL,
renewby varchar(10) COLLATE utf8_bin DEFAULT NULL,
renewtime datetime DEFAULT NULL,
applyby varchar(10) COLLATE utf8_bin DEFAULT NULL,
applytime datetime DEFAULT NULL,
haltby varchar(10) COLLATE utf8_bin DEFAULT NULL,
halttime datetime DEFAULT NULL,
cby varchar(10) COLLATE utf8_bin NOT NULL,
changed datetime NOT NULL,
tby varchar(10) COLLATE utf8_bin DEFAULT NULL,
ttime datetime DEFAULT NULL,
join_prom_code varchar(24) COLLATE utf8_bin DEFAULT NULL,
fv_date date DEFAULT NULL,
fv_hotel varchar(7) COLLATE utf8_bin DEFAULT NULL,
lv_date date DEFAULT NULL,
lv_hotel varchar(7) COLLATE utf8_bin DEFAULT NULL,
ref varchar(500) COLLATE utf8_bin DEFAULT NULL,
exp_s1 varchar(20) COLLATE utf8_bin DEFAULT NULL,
exp_s2 varchar(20) COLLATE utf8_bin DEFAULT NULL,
exp_s3 varchar(20) COLLATE utf8_bin DEFAULT NULL,
exp_s4 varchar(20) COLLATE utf8_bin DEFAULT NULL,
exp_s5 varchar(20) COLLATE utf8_bin DEFAULT NULL,
exp_s6 varchar(64) COLLATE utf8_bin DEFAULT NULL,
exp_s7 varchar(64) COLLATE utf8_bin DEFAULT NULL,
exp_s8 varchar(64) COLLATE utf8_bin DEFAULT NULL,
exp_s9 varchar(64) COLLATE utf8_bin DEFAULT NULL,
exp_s0 varchar(200) COLLATE utf8_bin DEFAULT NULL,
exp_m1 decimal(19,4) DEFAULT NULL,
exp_m2 decimal(19,4) DEFAULT NULL,
exp_m3 decimal(19,4) DEFAULT NULL,
exp_dt1 datetime DEFAULT NULL,
exp_dt2 datetime DEFAULT NULL,
exp_dt3 datetime DEFAULT NULL,
boss varchar(10) COLLATE utf8_bin DEFAULT NULL,
realname varchar(2) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (hotelid,id),
KEY index_applytime (applytime,applyby) USING BTREE,
KEY index_araccnt (araccnt1) USING BTREE,
KEY index_card_hotel (card_hotelid) USING BTREE,
KEY index_changed (changed) USING BTREE,
KEY index_cls (card_class) USING BTREE,
KEY index_cno (cno) USING BTREE,
KEY index_email_subscribe (email_subscribe) USING BTREE,
KEY index_flag (flag) USING BTREE,
KEY index_hno (hno) USING BTREE,
KEY index_hotelid (hotelid) USING BTREE,
KEY index_issue (issue_source) USING BTREE,
KEY index_mid (mid) USING BTREE,
KEY index_mobile_subscribe (mobile_subscribe) USING BTREE,
KEY index_no (no) USING BTREE,
KEY index_other_subscribe (other_subscribe) USING BTREE,
KEY index_pointaccnt (pointaccnt) USING BTREE,
KEY index_restime (reserved) USING BTREE,
KEY index_sno (sno) USING BTREE,
KEY index_sta (sta) USING BTREE,
KEY index_ticketaccnt (ticketaccnt) USING BTREE,
KEY index_type (type) USING BTREE,
KEY index_wechat_subscribe (wechat_subscribe) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
explain
select c.no,c.name,(d.credit-d.charge) as bal
from vipcard c left join vipcard_ar_master d on c.araccnt1=d.accnt;
结果如下，没有用到索引
1 SIMPLE c null ALL null null null null 48685 100.00 null
1 SIMPLE d null ALL null null null null 36640 100.00 Using where; Using join buffer (Block Nested Loop)
mysql 版本5.7.37-log
select 中 (d.credit-d.charge) as bal去了，能用到索引了。
请教高手。那里问题。
1、一个SQL是否利用索引会有很多很复杂的原因，光从SQL上看只能找到部分原因，需要看具体执行计划甚至数据结构
2、从SQL上看，你对左连接主表 vipcard 是全表数据提取，这个在所有数据库中都是全表扫描，想要利用索引，你得添加WHERE过滤条件，并且还得满足利用索引的各种复杂条件
3、而 vipcard_ar_master 表，虽然为左连接的从表且是主键关联，但在主表为全表扫描的前提下，这个表肯定只有全表扫描来对应了，走索引反而多些一举，更为低效
4、至于为啥 “ (d.credit-d.charge) as bal去了，能用到索引了 ”，这个是因为你的关联条件、提取结果集中涉及的所有列都有索引，只需要从各个索引中提取数据就能满足SQL的过滤和输出要求，不需要到表的数据区去提取数据，而且索引中的数据还是排好序的，比较起来更为快捷，这时系统判定走索引的远效率高于全表扫描的效率，自然只索引扫描了