在互联网应用中，通常我们只使用简单高效的SQL查询DB，大部分逻辑都需要用代码实现。今天给大家介绍一下，一些看似简单的SQL，也可能导致查询性能低下。WHERE条件字段使用函数假设我们有如下语句创建表mysql>CREATETABLE`tradelog`(`id`int(11)NOTNULL,`tradeid`varchar(32)DEFAULTNULL,`operator`int(11)DEFAULTNULL,`t_modified`datetimeDEFAULTNULL,PRIMARYKEY(`id`),KEY`tradeid`(`tradeid`),KEY`t_modified`(`t_modified`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;上面是一个时间维度的业务表，如果我们要查询所有数据中7月份的交易笔数就可以了。此时，我们可能会想到如下SQLmysql>selectcount(*)fromtradelogwheremonth(t_modified)=7;从上面的建表语句可以看出，索引是建立在t_modified之上的。这时候如果我们要查询上面的SQL查询，执行过程会是这样的：从上图可以看出，当对索引字段进行函数操作时，索引结构的顺序可能是无序的。因此，MySQL会放弃搜索树的查询结构，取而代之的是全索引扫描。（优化器之所以选择使用t_modified索引来遍历全表，而不是主键索引，是因为t_modified索引比较小。）通常，我们需要手动优化SQL。当然，这往往需要结合具体的业务数据进行处理。例如，上面的查询可以优化为以下情况：selectcount(*)fromtradelogwhere(t_modified>='2016-7-1'andt_modified<'2016-8-1')or(t_modified>='2017-7-1'andt_modified<'2017-8-1')or(t_modified>='2018-7-1'andt_modified<'2018-8-1');对于MySQL的简单查询而言，还有一个陷阱：SELECT*FROMtradelogWHEREid+1=999;这时MySQL不会主动优化“转账”，同样会造成全表扫描。字段的隐式转换MySQL中字段的隐式转换可能会导致索引不可用。我们先看一个比较字符和数字的例子。如下图：mysql>select'10'>9;当我们执行上面的SQL时，会得到如下结果从执行结果可以看出，字符类型默认会转换为数字类型。需要注意的地方是：'10'->10,'10A'->10,但'A10'->0,转换会过滤掉无效字符，但需要以数字开头，否则会转换为0。现在让我们看一下下面的语句：mysql>explainselect*fromtradelogwheretradeid=222;因为tradeid是VARCHAR类型，MySQL会把它转换成数字再进行比较，最终会导致索引不可用，全表扫描。我们在查询int类型字段的时候，对应的值可以随意使用10或者'10'，此时会转化为数字10，使用索引。上面语句的执行等同于：mysql>explainselect*fromtradelogwhereCAST(tradeidASsignedint)=222;即在查询字段上使用隐藏函数操作，导致全表扫描。隐式字符编码转换上面的案例介绍了不同类型字段之间的类型转换。隐式转换也可能发生在同一类型(VARCHAR)的不同字符集编码之间。接下来创建一个日志明细表（trade_detail），然后写入一些数据，如下：mysql>CREATETABLE`trade_detail`(`id`int(11)NOTNULL,`tradeid`varchar(32)DEFAULTNULL,`trade_step`int(11)DEFAULTNULL,/*操作步骤*/`step_info`varchar(32)DEFAULTNULL,/*步骤信息*/PRIMARYKEY(`id`),KEY`tradeid`(`tradeid`))ENGINE=InnoDBDEFAULTCHARSET=utf8;insertintotradelogvalues(1,'aaaaaaa',1000,now());insertintotradelogvalues(2,'aaaaaaab',1000,now());insertintotradelogvalues(3,'aaaaaaac',1000,now());insertintotrade_detailvalues(1,'aaaaaaaa',1,'add');insertintotrade_detailvalues(2,'aaaaaaaa',2,'update');insertintotrade_detailvalues(3,'aaaaaaaa',3,'commit');insertintotrade_detailvalues(4,'aaaaaaab',1,'add');insertintotrade_detailvalues(5,'aaaaaaab',2,'update');insertintotrade_detailvalues(6,'aaaaaaab',3,'updateagain');insertintotrade_detailvalues(7,'aaaaaaab',4,'commit');insertintotrade_detailvalues(8,'aaaaaaac',1,'add');insertintotrade_detailvalues(9,'aaaaaaac',2,'update');insertintotrade_detailvalues(10,'aaaaaaac',3,'updateagain');insertintotrade_detailvalues(11,'aaaaaaac',4,'commit');当我们需要查询一条交易记录（trade_log）中的所有交易明细（trade_detail）时，可以使用如下SQLmysql>explainselectd.*fromtradelogl,trade_detaildwhereed.tradeid=l.tradeidandl.id=2;以上是对trade_log的id=2的记录执行的查询，使用主键索引，扫描的行数为1；但是第二个没有在trade_detail上使用tradeid索引，是不是觉得有点奇怪？在上面的执行计划中，首先从trade_log中查询id=2的记录，然后匹配trade_detail。这里trade_log称为驱动表，trade_detail称为从动表。执行过程如下：那为什么上面的第二个执行计划没有索引呢？仔细看会发现上面两张表创建时使用的字符集编码是不一样的，一个是utf8，一个是utf8mb4。utfutf8mb4是utf8字符集的超集，当我们比较两个表的字段时，utf8将转换为utf8mb4（以避免丢失精度）。上图中的第3步可以看作是执行了如下操作（$L2.tradeid.value为utf8mb4的字符值）：mysql>select*fromtrade_detailwheretradeid=$L2.tradeid.value;隐式转换后的执行SQL如下：mysql>select*fromtrade_detailwhereCONVERT(tradeidUSINGutf8mb4)=$L2.tradeid.value;由此看来，在执行过程中，trade_detail的查询字段tradeid使用了一个函数，所以并没有使用索引。但是当我们反向查询时，即当我们将一个trade_detail关联到相应的trade_log时会发生什么？mysql>解释selectl.operatorfromtradelogl,trade_detaildwhereed.tradeid=l.tradeidandd.id=4;从上图可以看出，第二个查询使用的是tradelog的tradeid索引。当执行计划在trade_detail(R4)中找到id=4的记录，然后关联tradelog中对应的记录，执行的SQL如下：mysql>selectoperatorfromtradelogwheretraideid=$R4.tradeid.value;此时需要对等号右边的值进行隐式转换，没有对索引字段进行函数操作，如下所示：mysql>selectoperatorfromtradelogwheretraideid=CONVERT($R4.tradeid.valueUSINGutf8mb4);解决方法是不同字符集导致的索引不可用，可以使用以下2种中文方式解决。修改表的字符集编码。mysql>altertabletrade_detailmodifytradeidvarchar(32)CHARACTERSETutf8mb4defaultnull；手动字符编码转换。mysql>selectd.*fromtradelogl,trade_detaildwhereed.tradeid=CONVERT(l.tradeidUSINGutf8)andl.id=2;
