MySQL学习总结--索引 & 排序

索引模型、索引类型、索引覆盖、索引下推

  • 索引的作用:提高数据查询效率,避免全表扫描 O(n),扫描时间随数据量变大线性增长。
  • 常见索引模型
    • 哈希表:使用哈希函数提高查询效率,只适合等值查询。
    • 有序数组:使用二分法提高查询效率 O(log(N)),但更新效率低,适合静态历史数据。
    • 搜索树:使用N叉树(二叉树树高过高)提高查询&更新效率 O(log(N)),适合关系型数据库。
  • InnoDB中的索引模型:B+树
    • InnoDB 的 B+树叶子节点是一个数据页(page),每个数据页默认16K,一个页里面可以存多个记录,数据页内部有个有序数组,可以理解为通过二分法再去定位行数据,数据页之间通过双向链表相互连接。
    • 页分裂:一个数据页满了,需要新增加一个数据页,会导致空间利用率降低大概50%。
    • 页合并:当相邻的两个数据页利用率很低的时候会做数据页合并,即页分裂的逆过程。
    • 读写数据是也是按数据页为单位来读写的。当需要读一条记录的时候,并不是将这个记录本身从磁盘读出来,而是以页为单位,将其整体读入内存。
  • 索引类型
    • 主键索引(聚簇索引):B+树叶子节点存的是整行的数据,无需回表。
    • 非主键索引(二级索引):B+树叶子节点存的是主键的值,需要回表(回到主键索引上再查一次,从而得到整行数据)。
  • 索引组织表结构
    • 每一张表其实就是一个主键索引 B+树,叶子结点的key是主键,value是行数据。没有设主键的表,InnoDB会默认创建一个 rowid(6 字节)做主键。
    • 二级索引就是新增一个B+树,key是索引字段,value是主键。查询如果未命中索引,则遍历主键B+树。
  • 索引覆盖
    • 是指索引上的信息足够满足查询请求,不需要再回表到主键索引上去取数据,高频请求使用索引覆盖,是一个常用的性能优化手段。
    • 如果查询条件使用的是二级索引(或是联合索引的最左原则字段),查询目标字段是联合索引的字段或是主键,不用回表操作,直接返回结果,减少IO磁盘读写读取整行数据。
  • 最左前缀匹配原则
    • 最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符。
    • B+ 树这种索引结构,可以利用索引的“最左前缀”,来定位记录。
    • 查询条件where name like ‘张 %’ 能够命中单个 name 索引,或 name 位于最左的联合索引。
  • 联合索引字段顺序
    • 最左前缀匹配原则:如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。
    • 空间:字段长度大的放最左,字段长度小的可以单独建索引。
  • 索引下推
    • 虽然本次查询需要回表,但在回表前,充分利用索引中的字段进行了where过滤,减少了回表次数。
    • MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
    • 查询条件like 'hello%’ and age >10,MySQL5.6版本之前,会对所有匹配like 'hello%’ 的数据进行回表查询。5.6版本后,会先过滤掉age <= 10的数据,再进行回表查询。
  • 自增主键的应用场景
    • 由于 InnoDB 是 B+ 索引组织表,从性能和存储空间方面考量,一般选择自增主键,并尽量使用主键查询,因为:
      • 自增主键每次插入数据时都是追加操作,不涉及到挪动其他记录,不会触发叶子节点的页分裂。
      • 自增主键长度较小,主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。
  • 业务逻辑字段做主键的应用场景
    • 典型的 KV 场景:只有一个索引,且该索引必须是唯一索引。
    • 所有的查询都是where k=N,并没有>= 或者<= 这种操作(没有range操作),在这种场景里,hash索引的效率就比N叉树高。
    • 由于没有其他索引,所以也就不用考虑其他索引的叶子节点大小的问题。直接将这个索引设置为主键,可以避免每次查询需要搜索两棵树。
  • “N叉树”的N值在MySQL中是否可以人工调整
    • 通过改变页的大小能间接影响 N 值
      • 页的大小默认为 16KB,5.6 以后可以通过 innodb_page_size 参数控制, 页越大,一页能存放的索引数量就越多,N值就越大。
      • 注意,页太小会导致树高过高;页太大,则加载到内存的时间和单个数据页查询时间会提高,需要达到平衡才行。
    • 通过改变索引key的大小能间接影响 N 值
      • N叉树中非叶子节点存放的是索引信息,索引包含Key和Point指针。 Point指针的大小是固定为的,因此Key越小,一个页(16K)中能存放的索引数量就越多,N值就越大。
  • update /delete 条件一定要加索引
    • RR 隔离级别下,对于无索引字段进行 updatedeleteselect .. for update 操作,代价极高,会锁住主键索引上所有的行和间隙,容易出现死锁。而有索引字段只有符合条件的记录本身和附近的间隔会加锁。
    • 事前预防:设置参数 sql_safe_updates = on。如果 delete 或者 update 语句中没有 where 条件,或者 where 条件里面没有包含索引字段,则执行会报错。

唯一索引 VS 普通索引

  • 【普通索引】写多读少场景,且业务上能确保字段值唯一:账单类、日志类的系统
    • 写操作性能:普通索引能利用内存中的 Change Buffer,比唯一索引更快。唯一索引必须将数据页从磁盘读到内存中,然后进行判断和插入。特别是,出于成本考虑使用机械硬盘的“历史数据”的归档库,尽量使用普通索引,把 change buffer 尽量开大,能显著提升写入速度。遇到大量插入数据慢、内存命中率低的时候(Hit rate),也可以从索引上进行排查。
    • 读操作性能:两者几乎没有差异。因为InnoDB 读写数据的单位是数据页,整页读到内存中(60KB),普通索引要读 2 行,读到不符合条件的行返回,唯一索引读到 1 行符合条件即返回。
  • 【唯一索引】写后立马会做查询场景
    • 会触发 change buffer 立即 merge 到磁盘,这样的场景磁盘 io 次数不会减少,反而会增加 change buffer 的维护代价。

change buffer

  • change buffer 是一种缓存,如果写操作涉及的数据页不在内存中,则由 change buffer 先缓存这次的操作,从而避免了先读后写的【随机读】数据页的磁盘 IO,下次对这个数据页进行读操作时会自动触发 merge(将 change buffer 中的操作应用到原数据页得到最新结果),系统也有后台线程会定期 merge。
    • 在内存中有一份:占用 Innodb buffer pool,参数innodb_change_buffer_max_size设置其占比。
    • 也会通过 redo log 持久化到磁盘,从而 crash-safe:用到了change buffer机制时,在 redo log 中记录的本次变更,是记录new change buffer item相关的信息,而不是直接的记录物理页的变更。
  • merge 的流程
    • 把老版本的数据页从磁盘读入到内存。
    • 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个),依次应用,得到新版本的数据页。
    • 写 redo log,包含了数据页的变更和 change buffer 的变更。
    • 此时,数据页和内存中 change buffer 对应的磁盘位置都还没有修改,属于脏页,之后各自刷回自己的物理数据,就是刷脏页的过程了,何时刷取决于刷脏页的策略。
  • change buffer 只对二级普通索引有用
    • 减少读磁盘:仅仅是减少的是对二级普通索引页的读磁盘操作,而对于其他类型的页(唯一索引,主键索引)还是要读磁盘的。
    • 减少内存占用:change buffer 虽然还是需要内存占用(记录数据更新动作),但相比于数据页来说(默认16K),所占的内存还是小了很多的。
  • insert 语句优化效果更明显:把多个唯一索引改为普通索引,从而利用 change buffer 减少随机读。
    • 写操作(插入,更新,删除)会同时更新主键索引树、所有二级索引树。
    • insert:主键索引树和唯一索引树的肯定都要更新,无法用到 change buffer;但是普通索引树的更新,可以使用 change buffer。
    • update:只要涉及到相关字段更新,就要同时更新相应的索引树,道理同上。
  • redo log 与 change buffer 分别优化了先读后写操作流程的不同阶段
    • 先把要变更的数据页从磁盘读到内存中。Change buffer 优化此步骤的随机【读】,仅当所需数据页不在内存中,需要先读磁盘时,才有用武之地。
    • 对内存数据页执行变更 。
    • 将变更后的数据页,写入至磁盘中。 Redo log 优化此步骤的随机【写】,转化为顺序写,这是确保crash-safe的必须步骤,一直都会用到。

重建索引/表

  • 为什么要重建索引/表 – 为了收缩空间
    • 索引可能因为删除,或者页分裂等原因,导致数据页有空洞。
    • 重建索引的过程会创建一个新的索引,把数据递增插入,这样页面的利用率最高,索引更紧凑、更省空间。
  • 空洞:被标记为可复用,而没有被使用的空间,看起来就像是“空洞”。
    • 删除行,被删除的行只是被标记为可复用,但磁盘文件的大小是不会变的,即 delete 命令不能回收表空间。
    • 插入行,索引递增插入时紧凑,随机插入时可能造成数据页分裂,形成空洞。
    • 更新索引上的值,实际上是把旧值标记为删除,然后插入一个新值,也会形成空洞。
  • 重建二级索引
     # 正确示例
     alter table T drop index k;
     alter table T add index(k);
     ```
    
  • 重建主键索引/表
     # 错误示例:
     # 直接删掉主键索引是不好的,它会使得所有的二级索引都失效,并且会用ROWID来作主键索引;
     # 不论是删除主键还是创建主键,都会将整个表重建。所以连着执行这两个语句的话,第一个语句就白做了。
     alter table T drop primary key;
     alter table T add primary key(id);
     
     # 正确示例:
     alter table T engine=InnoDB;
     ```
    
  • alter table t engine=InnoDB 反而让表空间变大
    • 在 DDL 期间,如果刚好有外部的 DML 在执行,这期间可能会引入一些新的空洞。
    • 在重建表的时候,InnoDB 不会把整张表占满,每个页留了 1/16 给后续的更新用。也就是说,其实重建表之后不是“最”紧凑的。
  • alter / analyze / optimize table
    • alter table 重建表,减少页空洞,回收空间。
    • analyze table 重新统计索引信息,解决优化器索引选择异常(预估扫描行数不准确)。
    • optimize table 重建表+重新统计索引信息。
  • online / inplace / copy
     # alter table 重建表默认使用 inplace 方式,即在引擎层中创建临时文件,server 层无感
     alter table t engine=innodb,ALGORITHM=inplace;
     # 也可手动指定使用 copy 方式,即在 server 层创建临时表
     alter table t engine=innodb,ALGORITHM=copy;
    
    • online:在执行 DDL 时不阻塞对原表的写操作。online 一定 inplace,反过来不一定成立,例如:全文索引(FULLTEXT index)、空间索引 (SPATIAL index)。
    • inplace :在引擎层中创建临时文件,server 层无感。如果你有一个 1TB 的表,现在磁盘间是 1.2TB,能不能做一个 inplace 的 DDL 呢?答案是不能。因为,tmp_file 也是要占用临时空间的。
    • copy :在 server 层创建临时表。
  • DDL 过程
    • DDL 过程:新建一个表、递增插入旧表的数据、交换表名、删除旧表。
    • Online DDL 过程:在转存数据过程中,对旧表的写操作记录日志,最后对新表重放此日志。MySQL 5.6 版本开始引入。
    • 建议只对小表在业务低峰期使用 Online DDL,线上大表建议用 GitHub 开源的 gh-ost 来做。

索引选择异常

  • 如何定位索引选择异常这样的问题?
    • set long_query_time=0在慢查询日志中记录所有语句,对比force index(a)强制指定索引、优化器自动选择索引两个语句的执行时间。
    • explain查看优化器如何选择索引,同时使用show warnings可以看到MySQL 实际执行的语句,包括字符集的转换。
    • show index table_name查看索引基数。
    • analyze table table_name重新统计索引信息。
    • force index(a)强制指定索引。
    • 修改语句引导优化器,或增加、删除索引。
  • 优化器选错索引的情况
    • 由于不断地在一张表上删除、插入,导致索引统计信息不准确,导致判断扫描行数不准确,可用 analyze table table_name来重新统计索引信息。
    • 由于临时表,排序字段,导致优化器误判,可用force index强制指定索引,或修改语句引导优化器,或增加或者删除索引来绕过这个问题。
  • 慢查询日志是真正判断SQL效率的依据
     # 将慢查询日志的阈值设置为 0,这个线程接下来的所有语句都会被记录入慢查询日志中;
     set long_query_time=0;
     # 普通查询语句,由优化器选择索引
     select * from t where a between 10000 and 20000; /*Q1*/
     # force index(a) 手动指定使用索引 a
     select * from t force index(a) where a between 10000 and 20000;/*Q2*/
    
  • explain 查看优化器如何选择索引
    • 影响优化器选择索引的因素
      • 扫描行数(索引行数+回表行数)
      • 是否使用临时表
      • 是否需要排序
    • explain 输出
      • key:选择的索引
      • rows:预估的扫描行数
      • Extra
        • Using filesort:排序字段不在本次使用的索引中,需要额外排序。
        • Using index:使用了覆盖索引,无需回表。
        • Using index condition:使用了索引下推,虽然本次查询需要回表,但在回表前,充分利用索引中的字段进行了where过滤,减少了回表次数。
        • Using where:表示本次查询要进行筛选过滤。
        • Using temporary:表示需要使用临时表。
        • Block Nested Loopjoin_buffer:join 语句使用了 BNL 算法,驱动表数据暂存到了 join_buffer。
        • Using MRR:使用了 MRR 多范围读优化,Multi-Range Read,二级索引的多值查询,在回表前先对主键 id 排序(借助 read_rnd_buffer),从而将随机读转化为顺序读,提高查询效率。
        • Using join buffer (Batched Key Access):join 语句使用了 BKA 算法,先把驱动表的数据先放入 join_buffer,把 join 字段作为 key 批量提交给引擎层的 MRR 接口,而 MRR 会先在被驱动表 join 字段二级索引上进行多值查询,在回表前对主键 id 排序,从而将随机读转化为顺序读。
        • Using join buffer (hash join):join 语句使用了哈希 join,在 join_buffer 里维护一个哈希表放入驱动表的数据,扫描被驱动表时用 join 字段做 hash 查找。8.0.20 版本开始全面用 hash join 替代了 BNL,能显著提升无索引 join 语句的性能。
  • 优化器怎么判断要扫描的行数
    • 根据索引统计信息/ 索引基数 / 区分度(cardinality)来估算要扫描的行数。 一个索引上不同的值越多,这个索引的区分度就越好,即索引基数越大。
    • show index 查看索引基数
    • 索引基数如何计算? 通过哪个参数可以设置索引统计的存储方式?
      • 索引基数本身是通过采样统计估算出来的,不是准确结果。
      • 索引基数 = 统计 N 个数据页上不同值的平均个数 * 这个索引的页面数
      • 当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。
      • 参数innodb_stats_persistenton 表示统计信息会持久化存储,默认 N 是 20,M 是 10。 off 表示统计信息只存储在内存中,默认 N 是 8,M 是 16。

字符串字段如何加索引

  • 直接创建完整索引,可能比较占用空间,优点是简单,能利用覆盖索引。
  • 创建前缀索引,节省空间,缺点是会增加查询扫描次数,不能使用覆盖索引。需要判断出前缀的长度为多少时,其损失的区分度能接受。
     # 计算不同前缀长度下,此列上有多少不重复的值
     mysql> select 
     count(distinct left(email,4)as L4,
     count(distinct left(email,5)as L5,
     count(distinct left(email,6)as L6,
     count(distinct left(email,7)as L7,
     from SUser;
    
  • 使用reverse函数倒序存储,再创建前缀索引,适于前缀区分度不高,而后缀区分度高的场景,目的还是要提高索引的区分度,缺点是不支持范围扫描。
     # reverse 函数
     mysql> select field_list from t where id_card = reverse('input_id_card_string');
    
  • 使用crc32函数创建 hash 整数字段,再创建索引,查询性能稳定,有额外的存储和计算消耗,缺点是不支持范围扫描。
     # 在表上再创建一个整数字段,来保存身份证的校验码,同时在这个字段上创建索引。
     mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
     # crc32() 函数得到校验码
     mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
    

order by 排序字段要加索引

  • 为什么排序字段要加索引
    • 因为 B+ 树索引本身是有序的,MySQL 只需按索引顺序直接取出数据,无需放入 sort_buffer 进行额外排序,即 explain语句Extra字段中没有 Using filesort
    • 如果排序字有索引,则无需回表取排序字段,实现索引覆盖,即 explain语句Extra字段有 Using index
    • 需要多个字段一起排序时,可以按照最左前缀匹配原则建立组合索引。
    • 分页 limit 过大时会导致大量排序,可以记录上一页最后的 id,下一页查询条件带上where id > last_id limit 100
  • 优化器如何选择排序策略
    • sort_buffer 是 MySQL 给每个线程分配的一块用于排序的内存。
    • sort_buffer_size :如果数据总体大小不超过此上限,则在在内存中排序(快排 or 堆选择),超出就利用磁盘临时文件辅助(归并),进行外部排序。
    • max_length_for_sort_data :决定使用哪种排序策略,默认策略为全字段排序,如果单行的长度超过此值,就使用 rowid 排序。即,如果 MySQL 认为内存足够大,会优先选择全字段排序,否则使用 rowid 排序(多一次回表)。因为如果 select 的字段很多,sort_buffer 里能够同时放下的行数很少,要分成很多个临时文件,排序的性能会很差。
    • rowid:对于有主键的 innodb,rowid就是主键。 对于没有主键的innodb,rowid由系统生成(6个字节)。 对于Memory引擎,rowid就是数组下标。
    • 对于 InnoDB 磁盘临时表,执行全字段排序会减少磁盘访问,因此会被优先选择。
    • 对于内存临时表,回表过程只是简单地根据数据行的位置,直接访问内存得到数据,根本不会导致多访问磁盘,此时排序的行越小越好,因此优化器会优先选择 rowid 排序。
    • 内存临时表,使用 Memory 引擎创建的临时表。tmp_table_size 参数限制了内存临时表的大小,默认值是 16M,超过则只能使用磁盘临时表。
    • 磁盘临时表,使用引擎 InnoDB(默认)或 MyISAM 创建的临时表 。参数 internal_tmp_disk_storage_engine指定磁盘临时表的默认引擎。
  • 全字段排序流程
    • where 普通索引上查找,回表取【排序字段 + 所有 select 的字段】,放入 sort_buffer 中。
    • sort_buffer 中的数据按照排序字段进行排序,取 limit 行。
  • rowid 排序流程
    • where 普通索引上查找,回表取【排序字段 + rowid】,放入 sort_buffer 中。
    • sort_buffer 中的数据按照排序字段进行排序,取 limit 行。
    • 再次回表取所有 select 的字段。
  • 如何确定一个排序语句是否使用了临时文件
     /* 打开optimizer_trace,只对本线程有效 */
     SET optimizer_trace='enabled=on'; 
     /* @a保存Innodb_rows_read的初始值 */
     select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
     /* 执行语句 */
     select city, name,age from t where city='杭州' order by name limit 1000; 
     /* 查看 OPTIMIZER_TRACE 输出 */
     SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
     /* @b保存Innodb_rows_read的当前值 */
     select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
     /* 计算Innodb_rows_read差值 */
     select @b-@a;
     - set tmp_table_size=1024;
     set sort_buffer_size=32768;
     set max_length_for_sort_data=16;
     /* 打开 optimizer_trace,只对本线程有效 */
     SET optimizer_trace='enabled=on';
     - /* 执行语句 */
     select word from words order by rand() limit 3;
     - /* 查看 OPTIMIZER_TRACE 输出 */
     SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
    
    OPTIMIZER_TRACE 输出
    • number_of_tmp_files :使用的临时文件数量。sort_buffer_size 越小,需要分成的份数越多,此值就越大。
    • examined_rows:参与排序的行数。
    • sort_modepacked_additional_fields 表示排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是varchar(16),在排序过程中还是按照实际长度来分配空间的。rowid 表示使用 rowid 排序策略,没有表示使用默认的全字段排序策略。
    • select @b-@a :表示整个执行过程扫描了多少行,如果采用 rowid 排序,此值还包含第二次回表扫描的行数,例如 4000 + limit 1000 = 5000。
    • filesort_priority_queue_optimizationchosen=true,表示使用了优先队列排序算法(堆选择排序),这个过程不需要临时文件,因此对应的 number_of_tmp_files 是 0。 limit 行数 * (排序字段 + rowid) 长度 + 维护堆结构代价 < sort_buffer_size 会使用堆选择排序,超出则使用归并排序。
  • order by rand() 随机排序对大表性能很差
    bash mysql> select word from words order by rand() limit 3;
    • order by rand() 会创建随机值临时表,再额外排序。
    • 创建一个没有索引的内存临时表 Using temporary,放入 select 字段、rand() 函数生成的随机数,此过程为全表扫描。
    • 借助 sort_buffer 对内存临时表排序 Using filesort,根据 MySQL 版本,可能又对内存临时表全表扫描一次。
  • 随机排序方法
    • 只随机取一个 word 值
       # 取得整个表的行数,并记为 C。
       select count(*) into @C from t;
       # 取得 Y = floor(C * rand())。 floor 函数在这里的作用,就是取整数部分。
       set @Y = floor(@C * rand());
       # 再用 limit Y,1 取得一行。
       set @sql = concat("select * from t limit ", @Y, ",1");
       # 由于 limit 后面的参数不能直接跟变量,使用了 prepare+execute 的方法,也可以改为拼接 SQL。 
       prepare stmt from @sql;
       execute stmt;
       DEALLOCATE prepare stmt;
      
      MySQL 处理limit Y,1的做法就是按顺序一个一个地读出来,丢掉前 Y 个,然后把下一个记录作为返回结果,因此这一步需要扫描 Y+1 行。再加上,第一步扫描的 C 行,总共需要扫描 C+Y+1 行。但是也比 order by rand() 代价小的多,因为select * from t limit @Y1,1是直接在主键索引上按序取 limit 行数据,利用了索引本身的有序性。远比 order by rand() 组成随机值临时表,再额外排序的代价小。
    • 随机取 3 个 word 值
       # 执行三个 limit Y, 1 语句得到三行数据。
       select count(*) into @C from t;
       set @Y1 = floor(@C * rand());
       set @Y2 = floor(@C * rand());
       set @Y3 = floor(@C * rand());
       select * from t limit @Y11//在应用代码里面取Y1、Y2、Y3值,拼出SQL后执行
       select * from t limit @Y21select * from t limit @Y31# 总扫描行数是 C+(Y1+1)+(Y2+1)+(Y3+1),实际上它还是可以继续优化,来进一步减少扫描行数的。
       # 取 Y1、Y2 和 Y3 里面最大的一个数,记为 M,最小的一个数记为 N,总共只需要 C+M+1 行
       select * from t limit N, M-N+1;
      
  • limit n order by 分页查询时有重复数据
    • limit n, a 获取 a 条记录,会把 n + a 行都读出来,n 很大时性能会很差。
    • limit n+a, a 获取第二组 a 条记录。
    • 这两组 a 条记录出现了重复数据,是因为limit 有可能出现两种算法,比如直接排序和优先队列排序,就是不同的结果。而 limit 后面的参数,是会影响算法的。

索引字段不要应用函数,其传参可以应用函数

  • 因为对索引字段做函数操作,可能会破坏索引值的有序性,从而导致优化器放弃树搜索,使用全索引扫描。
    • 索引字段应用函数:显式函数、隐式的字段类型转换 CAST、隐式的字符集转换 CONVERT
    • 联合索引对左侧字段做 in 查询。
    • 联合索引 order by 一个字段升序,另一个字段降序。
  • 显式的函数操作
     # 【错误】索引字段应用了函数,导致全索引扫描
     select count(*) from tradelog where month(t_modified)=7;
     # 【正确】利用索引
     select count(*) from tradelog where (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or (t_modified >= '2018-7-1' and t_modified<'2018-8-1');
     # 【错误】
     select * from tradelog where id + 1 = 10000;
     # 【正确】
     select * from tradelog where id = 10000 - 1;
    
  • 隐式的字段类型转换:字符串 -> 数字
     # 【错误】tradeid 是字符串类型,隐式转换相当于 where CAST(tradid AS signed int) = 110717
     select * from tradelog where tradeid=110717;
     # 【正确】id 是整数类型,隐式转换相当于 where id = CAST("83126" AS signed int)
     select * from tradelog where id="83126";
    
  • 隐式的字符集转换:子集(utf8) -> 超集(utf8mb4)
    • join 的两个表字符集不同时,可能对被驱动表的索引字段应用CONVERT函数,导致优化器放弃树搜索,使用全索引扫描。
    • 解决思路:选字符集和排序集时,字符串都选超集 utf8mb4;或者改 sql 语句。
    • 执行explain语句后,使用show warnings; 可以看到MySQL 实际执行的SQL,包括字符集的转换。
       # 【错误】条件 l.id=2 确定驱动表是 l ,将 l.tradeid 作为传参值(超集),在被驱动表 d 中查找(子集) where d.tradeid="val",相当于 where CONVERT(d.traideid USING utf8mb4)="val",导致不能对索引 d.traideid 做树搜索,只能全索引扫描。
       select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
       
       # 【正确】条件 l.id=2 确定驱动表是 l ,将 l.tradeid 作为传参值(超集),对传参值应用转换函数(子集),在被驱动表 d 中查找(子集)时就能利用索引 d.traideid 做树搜索。
       select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 
       
       # 【正确】条件 d.id=4 确定驱动表是 d ,将 d.tradeid 作为传参值(子集),在被驱动表 l 中查找(超集) where l.tradeid="val",相当于 where l.tradeid=CONVERT("val" USING utf8mb4),是对传参应用函数,能利用索引 l.traideid 做树搜索。
       select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;
      
  • 查看数据类型转换规则简单方法
    • select “10” > 9;
      • 如果转换规则是“将字符串转成数字”,那么就是做数字比较,结果应该是 1。
      • 如果转换规则是“将数字转成字符串”,那么就是做字符串比较,结果应该是 0。字符串比较大小是逐位从高位到低位逐个比较(按ascii码) 那么“10”的“1”的ascii比“9”小,所以结果为0。
    • select 'a' = 0;
      • 如果结果是1,说明无法转换成数字的字符串都被转换成0来处理了。
    • 字符集转换规则是子集(utf8) -> 超集(utf8mb4)
  • 传参超出字符串长度被截断造成慢查询
     # 字段 b 定义的是 varchar(10),where 传参长度超出10
     select * from table_a where b='1234567890abcd';
     # 最理想的情况是 MySQL 看到参数长度超了,直接返回空,然而并没有
     # 再不济,将参数拿到索引里面去做匹配,肯定也没能够快速判断出索引树 b 上并没有这个值,也很快就能返回空,然而也没有
     # 实际执行很慢,原因是:引擎层做了字符截断,用截断的参数在索引 b 上查找到 10 万行,回表 10 万次;但是每次回表以后查出整行,到 server 层一判断,b 的值都不是’1234567890abcd’;返回结果是空。
    
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值