MySQL优化班13期课后作业,20180710

1、有个列dt存储日期时间数据,其查询条件如下:dt >=‘2018-06-01’ and dt<‘2018-06-02’,现在请你对这个业务需求进行优化,说说所有可以做的事

2、举几个你遇到的类型隐式转换案例

3、为什么列类型建议不要允许NULL
 
用下面我提供的小程序,可以测试 UTF8MB4 字符集模式下,插入4字节:
http://imysql.com/utf8mb4/utf8mb4.php
别给我乱搞飞机哦
已邀请:

aaron8219 - Oracle DBA

赞同来自: yejr

问题一:
1.查看dt列上有无索引,如果没有创建一个单列索引或dt开头的联合索引(如果还有其他查询条件的话)
2.查看dt列的数据类型,如果是date的,找时间修改为datetime的(5.6版本以上)
3.将dt >='2018-06-01' and dt<'2018-06-02'使用between替换
问题二:
经常碰到的隐式转换的情况就是漏加引号的场景,使原来可以走索引的变成全表扫描
问题三:
1.使用count(*)可以统计行数,但是count(列名),这列中含有null值,则不会参与统计
2.列中含有null值会使SQL语句变得复杂,如,需要使用ifnull()对列进行判断,否则会得出错误的结果
3.在聚合函数group by(),max(),mix()等中,把所有的null值都看作相等,会造成统计结果错误

yejr

赞同来自: liyh

同学们都没提到过另一种方案,那就是把 dt 列冗余存储,用int型,存储内容只到日期,比如 20180601,这样上面的查询就可以改成等值方式了,对提高索引效率有大大的帮助哦

xcy

赞同来自:

 

xcy

赞同来自:

问题一:
 1 如果可以的话 讲日期列改为int类型,使用unix_timestamp函数将日期转化为数字进行存储时间戳。
 1 改sql逻辑可改为dt='2018-06-01' 不过性能应该无太大差别。
 2 dt如果可以的话 建议加索引。
 3 该SQL存在隐士转换,
   explain select * from t01 where dt>='2018-06-01' and dt<'2018-06-02';
   等同于
   explain select * from t01 where dt>=str_to_date('2018-06-01','%Y-%m-%d') and dt<str_to_date('2018-06-02','%Y-%m-%d');
 4 mysql有绑定变量吗,有的话也建议用。
问题二:
    1 上例子就是隐士转换。
    2  如果没有添加‘’ “”会存在隐士转换。
    3  如果俩个表的字符集不一样也容易存在隐士转换。
    4  如果俩个列的类型不是一致的也会存在隐士转换。
 问题三:
     1 索引排空,如果列允许为空,执行sql的时候需要设定列 is not null,否则执行计划会不走索引。
     2 count不统计空,会造成统计数据不准确。
     

liyh

赞同来自:

问题1:
首先看查询的频率和资源消耗程度,如果执行频率不高,只是少量执行,在整体资源消耗上占比很小,可以不去管它。可以用pt-query-digests 分析下慢日志,看看情况。如果针对这个字段的查询已经在资源消耗中占比高,高频率执行,就得优化了。
其次,对于日期时间型,可以考虑存储时采用int类型,这样节省存储空间,和检索的查询速度。在查询时需要用到int与日期时间类型转换。比较适合流水日志类,这类表查询和写入主要依赖时间。采用int后也可以做为联合主键使用,也方便分区。
再次,课上老师说5.7以后的版本日期时间型,底层存储是int型,因此用‘字符串’形式查询会隐式转换。不过这个我没测试过,不知道怎么验证。如果要是有隐式转换应该可以将dt >=unix_tampstamp('2018-06-01') and dt<unix_timestamp('2018-06-02')
问题2:
发生隐式转换的情况,给定的条件值跟表字段类型不匹配,会发生隐式转换。
问题3:
null值对于统计是一个干扰

要回复问题请先登录注册