设计索引的主要目的就是帮助我们快速获取查询结果,而以%开头的像查询则不能够使用b - tree索引。
考虑到innodb的表都是聚簇表(类似于甲骨文中的索引组织表),且二级索引叶节点中记录的结构为(索引字段→主键字段),我们可以通过改写sql (mysql优化器比较笨,需要给它足够的提示)采取一种轻量级的方式代替全表扫:
<强>使用索引全扫描找到主键,再根据主键回表获取数据的方法。
这种方式的速度优势在单行记录长度较大,表中记录较多的情况下体现的尤为明显,因为此时索引全扫描带来的IO开销相对于全表扫会小得多。
纸上得来终觉浅,绝知此事要躬行:
创建测试测试表,表上有自增主键主(id)和二级索引idx_name1 (name1),表中有500个万条数据。
<代码> mysql>desc测试; + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + | |字段类型零额外默认关键| | | | | + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + | | int id (11) | | PRI零| | auto_increment | | name1 | varchar(20) |是| MUL零| | | | name2 | varchar(20) |是| |零| | | name3 | varchar(20) |是| |零| | | name4 | varchar(20) |是| |零| | | name5 | varchar(20) |是| |零| | | name6 | varchar(20) |是| |零| | | name7 | varchar(20) |是| |零| | | name8 | varchar(20) |是| |零| | | name9 | varchar(20) |是| |零| | | name10 | varchar(20) |是| |零| | + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + 11行集(0.01秒) mysql>显示指数从测试\ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行* * * * * * * * * * * * * * * * * * * * * * * * * * * 表:测试 Non_unique: 0 Key_name:主 Seq_in_index: 1 Column_name: id 排序: 基数:4829778 Sub_part:零 包装:零 零: Index_type: BTREE 备注: Index_comment: * * * * * * * * * * * * * * * * * * * * * * * * * * * 2。行* * * * * * * * * * * * * * * * * * * * * * * * * * * 表:测试 Non_unique: 1 Key_name: idx_name1 Seq_in_index: 1 Column_name: name1 排序: 基数:2414889 Sub_part:零 包装:零 空:是的 Index_type: BTREE 备注: Index_comment: 2行集(0.00秒) mysql>select count(*)从测试; +----------+ | | count (*) +----------+ | 5000000 | +----------+ 1行集(1.59秒)
基于name1进行像查询,<强>耗时11.13 s ,从执行计划看,sql在执行时走的是全表扫描(类型:全部):
<代码> mysql>select *从测试等name1 % O4JljqZw % \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行* * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1167352 name1: BO4JljqZws name2: BrfLU7J69j name3: XFikCVEilI name4: lr0yz3qMsO name5: vUUDghq8dx name6: RvQvSHHg4p name7: ESiDbQuK8f name8: GugFnLtYe8 name9: OuPwY8BsiY name10: O0oNGPX9IW 1行集(11.13秒) mysql>解释从测试等name1 select * % O4JljqZw % \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行* * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 select_type:简单 表:测试 类型:所有 possible_keys:零 关键:零 key_len:零 裁判:零 行:4829778 额外的:使用 1行集(0.00秒)
将sql改写为“选择a .从测试,从测试(选择id name1像' % O4JljqZw % ') b, a.id=b.id;“
<>强提示优化器在子查询中使用二级索引idx_name1获取id:
<代码> mysql>选择一个。*从测试,从测试,选择id name1像' % O4JljqZw % ') b a.id=b.id \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行* * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1167352 name1: BO4JljqZws name2: BrfLU7J69j name3: XFikCVEilI name4: lr0yz3qMsO name5: vUUDghq8dx name6: RvQvSHHg4p name7: ESiDbQuK8f name8: GugFnLtYe8 name9: OuPwY8BsiY name10: O0oNGPX9IW 1行集(2.46秒) mysql>选择一个解释。*从测试,从测试,选择id name1像' % O4JljqZw % ') b a.id=b.id \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行* * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 select_type:主 表:& lt; derived2> 类型:所有 possible_keys:零 关键:零 key_len:零 裁判:零 行:4829778 额外的:空 * * * * * * * * * * * * * * * * * * * * * * * * * * * 2。行* * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 select_type:主 表: 类型:eq_ref possible_keys:主 关键:主 key_len: 4 裁判:b.id 行:1 额外的:空 * * * * * * * * * * * * * * * * * * * * * * * * * * * 3。行* * * * * * * * * * * * * * * * * * * * * * * * * * * id: 2 select_type:派生 表:测试 类型:指数 possible_keys:零 关键:idx_name1 key_len: 63 裁判:零 行:4829778 额外的:使用;使用索引 3行集(0.00秒)MySQL SQL优化之‘%’
