mysql索引有字符集

mysql索引有字符集

知识点：字符集转换，index hint生效规则

某日接到同事的要求，改写一个SQL，从股票日行情表(eodprice)里，按照每只股票，取出最新交易日的后复权因子，该表有800万行数据，记录全部A股，每一天的价格信息等。

表结构如下：

create table eodprice

(

code varchar(8)

,trade_dt varchar(8)

,adjfactor decimal(20,6)

,primary key(code,trade_dt)

)engine = myisam default charset = gbk;

写入几条测试数据

insert into eodprice(code,trade_dt,adjfactor)

values(‘000001.SZ’,’20160616’,104.758253);

insert into eodprice(code,trade_dt,adjfactor)

values(‘000001.SZ’,’20160617’,104.758253);

insert into eodprice(code,trade_dt,adjfactor)

values(‘000001.SZ’,’20160618’,104.758253);

insert into eodprice(code,trade_dt,adjfactor)

values(‘000001.SZ’,’20170721’,106.308791);

insert into eodprice(code,trade_dt,adjfactor)

values(‘000002.SZ’,’20160729’,132.782471);

insert into eodprice(code,trade_dt,adjfactor)

values(‘000002.SZ’,’20160730’,132.782471);

insert into eodprice(code,trade_dt,adjfactor)

values(‘000002.SZ’,’20160801’,132.782471);

insert into eodprice(code,trade_dt,adjfactor)

values(‘000002.SZ’,’20170829’,137.300049);

MySQL没有类似于SQL Server row number这种功能，所以必须自己写算法，思来想去，解决这个问题最好用临时表中继下

注：有人说用MAX处理，其实是错误的。

先把股票代码，最后一个交易日缓存到临时表

步骤一:

CREATE temporary TABLE IF NOT EXISTS tmp_trade_dt

(

code varchar(50)

,trade_dt varchar(8)

);

步骤二:

insert into tmp_trade_dt

(code,trade_dt)

select code

,max(trade_dt)

from edoprice

group by code;

这个时候，有两个表，一个是日行情表，一个是最后交易日的缓存表。那么只要将这两个表通过code 和 trade_dt字段关联查询，不就可以取出最后一个交易日的后复权因子了吗？

步骤三:

de

,t2.ADJFACTOR

,t1.trade_dt

from tmp_trade_dt as t1

inner join eodprices as t2 de = t2.code ade_dt = t2.trade_dt;

好了，前面都是废话。思路也是正确的。

但我在测试的时候发现了一个很大的问题。执行步骤三的时候特别慢。

理论上说：A股就3000多只，取每只股票最后一天的复权因子，那么也就三千多行数据，所以存在临时表里的数据也就3000多行。eodprices表虽说有800万行数据，但是关联查询的两个字段建有联合索引，几分钟都出不来数据，不应该啊。

explain 一下发现根本没用上eodprices的索引：

what？难道MySQL本身有问题，导致系统对eodprices的统计立方图有问题？可惜在5.7版本里，没法查看系统对表的统计立方图等信息。

那用index_hint强制使用索引呢？

de

,t2.ADJFACTOR

,t1.trade_dt

from tmp_trade_dt as t1

inner join eodprices as t2

force index for join(primary)

de = t2.code ade_dt = t2.trade_dt;

explain发现依然无效，why？？？逆天了？

折腾了半天，后面突然想起，会不会是字符集的问题，因为这个数据库比较特殊，用GBK编码，同个实例下，其它数据库，默认使用UTF8，客户端连接配置也是用UTF8。果然，临时表用的UTF8编码

当查询的时候，关联字段或者筛选字段与筛选字符串的编码不一致的时候，数据库会先对两边进行转码，转码的规则是将子集的列转化为超集，在本例中，拥有800万行数据的列为GBK，数据量小的临时表为UTF8，GBK是UTF8的子集，所以，数据库会把这800万行数据进行转码，然后加载到内存。。。。。这么一来就耗费好多时间，而且还有个很要命的问题—–没法用上索引，因为索引是有序的，索引的排序规则，按照所在字段的字符集的排序规则来排。如果查询中出现对该字段进行转码，那么MySQL会忽略这个索引。。。

至此，解决问题的方法已经很清楚，就是把临时表也改为GBK编码。问题就可以解决。

标签：