MySQL InnoDB索引介绍及优化-写给初学者
wengjc
8年前
<h2><strong>一、先说说什么是索引？</strong></h2> <p>索引(index)翻译为一个目录，用于快速定位我们想要找的数据的位置。例如：我们把一个数据库比作一本书，而索引(index)就是书中的目录，此刻要找到书的某个感兴趣的内容，我们一般是不会整本书翻完再去确认该内容在哪里，而是通过书的目录，定位到该内容章节所在页数，最后直接翻到该页面</p> <p>我们来看看在数据库中的索引：</p> <p>全表扫描 VS 索引扫描</p> <p>以字典为例，全表扫描就是如果我们查找某个字时，那么通读一遍新华字典，然后找到我们想要找到的字</p> <p>而跟全表扫描相对应的就是索引查找，索引查找就是在表的索引部分找到我们想要找的数据具体位置，然后会到表里面将我们想要找的数据全部查出</p> <p>实例：在一张学生表找到一个名字叫Dev的学生</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/1cfff203e1a77e0f5d0ce44897a00f4d.png"></p> <p>左边全表扫描：需要从第一行开始一行行的扫描，直到找到100008行Dev这个学生的信息为止，将这个数据返回回来，但有可能该表中还有同名的学生，因此扫描并没有结束，通常全表扫描要找到一个数据，是需要将整张表的数据遍历一遍，然后才能确定是否将所有数据返回</p> <p>右边索引扫描：索引查找是根据首字母排序找到D开头的Dev,如果首字母相同，那么再根据第二个字母排序找到，以此类推，我们找到ID为100008，然后回表查出ID为100008的数据</p> <p>结论：因此索引(对应InnoDB)的索引值对应的是主键ID</p> <h2><strong>二、如何找到索引对应的值</strong></h2> <p>InnoDB引擎主要根据</p> <p>(1)B+tree</p> <p>(2)二分查找法</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/abb9889bfb61667842c8b1ae35145fc1.png"></p> <p>B+tree: B+树拥有整棵树的根节点、支节点和页节点，上层会存储下层节点的管理范围，直到页节点的具体信息</p> <p>二分查找法：根据B+树存储的各个节点的范围，进行比较，逐步缩小范围，最后定位到页节点中我们想要的位置</p> <h2><strong>三、介绍下InnoDB表也是一张索引表</strong></h2> <p style="text-align: center;"><img src="https://simg.open-open.com/show/0f3ab889f4c26319ac9fd58215038d48.png"></p> <p>如上图InnoDB表是聚簇表，意思是InnoDB本身是一张大的索引组织表，也是一个根据主键排序的大索引的B+树结构，我们在InnoDB里面另外建立自己想要索引的表的字段</p> <p>聚簇索引就意味着InnoDB表本身，而我们把这些根据其他字段排序的索引称为二级索引(secondery class)</p> <h2><strong>四、在数据库中如何建立索引</strong></h2> <p>在MySQL中主要建立两种类型的索引</p> <p>1.单列索引</p> <pre> create index idx_name on tb_student(name); 索引名 表名 字段名</pre> <p>2.联合索引</p> <pre> create index idx_name_age on tb_student(name,age); #索引中先根据name排序，name相同的情况下根据age排序</pre> <h2><strong>五、索引维护</strong></h2> <p>首先介绍下什么是索引维护？这是一个关乎性能的重要概念</p> <pre> 如果索引所在字段发生了修改、删除、插入等操作，那么索引项就会发生变化，因此如果不能保证索引的有序，那么就不能索引的准确与效率，而索引的排序发生了变化的这个行为，我们称为索引维护 在insert/delete/update操作时，为了维护索引的排序，数据库会自动的完成索引项的维护，索引的排序，这些行为对用户是透明的，感觉不到的 在一个有索引的表中，创建它时，实际上还同时创建了索引排序的表，因此在DML中，插入等操作不再是普通的插入，MySQL将它封装成了一个事务，连着索引项的排序表一起操作 因此，我们应当严格控制表上的索引数量，否则容易影响数据库的性能</pre> <p>总结索引维护如下：</p> <pre> 1、索引维护由数据库自动完成 2、插入/修改/删除每一个索引行都变成一个内部封装的事务 3、索引越多，事务越大，代价越高 4、索引越多，对表的插入和索引字段的修改就越慢</pre> <p>因此可以看出索引并非是越多越好，在工作中也要慎用，尤其对于写操作较为频繁的业务</p> <h2><strong>六、如何正确的使用索引？</strong></h2> <p>1、依据where查询条件建立索引</p> <pre> eg: select a,b from tb_test where c = ?; idx_c(c) ->正确 select a,b from tb_test where c = ? and b = ? idx_cd(c,d) ->正确</pre> <p>2、根据排序order by ,group by , distinct 字段添加索引</p> <pre> eg: select * from tb_test order by a; select a,count(*) from tb_test group by a; idx_a(a) ->正确 select * from tb_test order by a,b; idx_a_b(a,b) ->正确 select * from tb_test order where c = ? by a; idx_c_a(c,a) ->正确</pre> <h2><strong>七、到底哪些字段适合创建索引？</strong></h2> <p>1、字段值的重复程度，如图：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/bdac5e97a178f56b6f719d4589924247.png"></p> <p>身份证号码由于基本上不可能重复，因此选择性非常好，而人的名字重复性较低，选择性也不错， 性别选择性较差，重复度非常高</p> <p>2、选择性很差的字段通常不适合创建索引，但也有例外</p> <pre> 如：男女比例相仿的表中，性别不适合创建单列索引，如果走索引不如走全表扫描， 因为走索引的I/O开销更大 但如果男女比例极度不平衡，要查询的又是少数方,如：理工学校、IT公司等可以考虑使用索引</pre> <p>3、联合索引中选择性好的字段应该排在前面</p> <pre> select * from tab_a where gender=? and name=? idx_name_gender(name,gender) ->正确</pre> <p>4、联合索引可以为单列、复列查询提供帮助</p> <pre> idx_smp(a,b,c) where a=?; ->正确 where a=? and b=?; ->正确 where a=? and c=?; ->正确 （注：需要MySQL5.6版本以上；在5.5及以前版本，可以对a字段进行索引扫描，但c字段不行 ） where a=? and b=? and c=? ->正确</pre> <p>5、合理创建联合索引，避免冗余</p> <pre> (a),(a,b),(a,b,c) ->不可取 (a,b,c) ->正确，可以覆盖前两个</pre> <h2><strong>八、再来看看如何在长字段上建立索引呢</strong></h2> <p>首先，在较长的字段上建立索引是非常影响性能的，比如文章等超大varchar或者text字段，如果不是非建不可，一般不推荐，另外对InnoDB索引单字段（utf8）只能取前767bytes</p> <p>那么如何处理长字段索引？</p> <pre> 主要根据类型来分别处理： 1、Email类，可以建立前缀索引 mail_addr varchar(2048) idx_mailadd(mail_addr(39)) -> 正确 解析：由于email邮件类型字段，一般后缀都有较大可能相同，如.com .cn等等，而前缀相同的可能性较低，且邮箱一般长度较短，因此可以建立前缀索引 2、住址类，分拆字段 home_addr varchar(2048) idx_homeadd(home_addr(30)) ->错误，很可能前半段是相同的省市区街道名 province_add varchar(1024),city_add varchar(1024), district_add `varchar(1024),lolcal_add varchar(1024) --建立联合索引或者单列索引 ->正确`</pre> <h2><strong>九、对核心SQL索引做覆盖扫描</strong></h2> <p>对于最核心的SQL，我们可以考虑使用索引覆盖，什么是索引覆盖呢，下面是个例子</p> <pre> select name from tb_user where userid=? key idx_uid_name(userid,name) ->覆盖索引扫描</pre> <p>我们查询用户名这种操作频率非常高，而索引里面又存储了字段的值，因此在我们做查询时，name字段的值直接在索引中返回，而不需要回表;还有一个使用非常广泛的例子：用户登陆，我们可以将username password做覆盖索引，这样大大提高登陆验证的速度</p> <p>因此覆盖索引覆盖就是将你要查询的字段和条件字段一起建立联合索引，这样的好处是不需要回表获取name字段，IO最小，速度块</p> <h2><strong>十、哪些情况无法使用索引？</strong></h2> <p>1、索引列进行数据运算或者函数运算</p> <pre> eg: where id+1=10; ->错误，无法利用到索引 where id=(10-1) ->正确 where year(id) < 2016 ->错误，无法利用到索引 where col < '2016-01-01' ->正确</pre> <p>2、未含复合索引的前缀字段</p> <pre> idx_abc(a,b,c) where b=? and c=? ->错误，无法利用到索引 正确的建立索引方式(b,c)</pre> <p>3、前缀通配符"_" "%"等</p> <pre> like '%ttt%' ->错误，无法利用到索引 like "ttt%" ->正确</pre> <p>4、where条件使用NOT,<>,!= 通常也无法使用到索引</p> <p>5、字段类型不匹配</p> <pre> 字段类型并不绝对匹配时，可能会导致无法使用索引 a int(11) ,idx_a(a) where a = '123' ->错误，可能导致未知的错误,这个跟编码有关系 where a = 123 ->正确</pre> <h2><strong>十一、利用索引做排序操作</strong></h2> <p>以 idx_ab(a,b)索引为例</p> <p>1、能使用上述索引进行排序的操作是：</p> <pre> order by a; a = 3 order by b; order by a,b; order by a desc ,b desc; a > 5 order by a;</pre> <p>2、不能使用索引帮助排序的查询</p> <pre> order by b; #没有使用到联合索引的第一个字段 a > 5 order by b; #一旦前缀操作是一个range而非＝操作，那么就无法利用到索引， 这里 a>5无法利用索引，二联合索引的第一个字段未利用， 因此 order by b也无法利用索引查询 a in (1,3) order by b; #in里面的值没有建立索引，因此无法利用索引，a未用因此order by b也无法使用 order by a asc, b desc; #这里order by a esc是利用了索引，但是b desc未利用到，因为b要和a排序方式一致才可利用到索引</pre> <h2><strong>十二、如何确定一个查询有没有走索引，走了哪些索引？</strong></h2> <p>MySQL中自带命令行工具 explain 来查看一个sql语句是否了索引</p> <p>使用方式：</p> <pre> explain select * from tb_test;</pre> <p>关注的项：</p> <pre> 1、type : 查询access的方式，表的连接类型 index | 索引 full | 全表扫描 ref | 参照查询，也就是等值查询 range | 范围查询 2、key : 本次查询最终选择使用哪个索引，NULL为未使用索引 3、key_len : 选择的索引使用的前缀长度或者整个长度 4、rows : 查询逻辑扫描过的记录行数 5、extra : 额外信息，主要是指fetch data的具体方式</pre> <p>总结：索引的本质还是提升我们查询数据库的速度，减少服务器I/O开销，提供更稳定快捷的服务</p> <p> </p> <p>来自：https://segmentfault.com/a/1190000007445807</p> <p> </p>