Mysql应用MySQL InnoDB索引介绍及优化(一)
发布时间：2020-12-12 00:48:02 所属栏目：MySql教程 来源：网络整理
导读：《Mysql应用MySQL InnoDB索引介绍及优化(一)》要点： 本文介绍了Mysql应用MySQL InnoDB索引介绍及优化(一)，希望对您有用。如果有疑问，可以联系我们。 导读：一、先说说什么是索引?索引(index)翻译为一个目录,用于快速定位我们想要找的数据的位置.例如：我
|
《Mysql应用MySQL InnoDB索引介绍及优化(一)》要点： 索引(index)翻译为一个目录,而索引(index)就是书中的目录,此刻要找到书的某个感兴趣的内容,我们一般是不会整本书翻完再去确认该内容在哪里,而是通过书的目录,定位到该内容章节所在页数,最后直接翻到该页面 我们来看看在数据库中的索引： 全表扫描 VS 索引扫描 以字典为例,全表扫描就是如果我们查找某个字时,那么通读一遍新华字典,然后找到我们想要找到的字 而跟全表扫描相对应的就是索引查找,索引查找就是在表的索引部分找到我们想要找的数据具体位置,然后会到表里面将我们想要找的数据全部查出 实例：在一张学生表找到一个名字叫Dev的学生
左边全表扫描：需要从第一行开始一行行的扫描,直到找到100008行Dev这个学生的信息为止,将这个数据返回回来,但有可能该表中还有同名的学生,因此扫描并没有结束,通常全表扫描要找到一个数据,是需要将整张表的数据遍历一遍,然后才能确定是否将所有数据返回 右边索引扫描：索引查找是根据首字母排序找到D开头的Dev,如果首字母相同,那么再根据第二个字母排序找到,以此类推,我们找到ID为100008,然后回表查出ID为100008的数据 结论：因此索引(对应InnoDB)的索引值对应的是主键ID 二、如何找到索引对应的值 InnoDB引擎主要根据 (1)B+tree (2)二分查找法
B+tree: B+树拥有整棵树的根节点、支节点和页节点,上层会存储下层节点的管理范围,直到页节点的具体信息 二分查找法：根据B+树存储的各个节点的范围,进行比较,逐步缩小范围,最后定位到页节点中我们想要的位置 三、介绍下InnoDB表也是一张索引表
如上图InnoDB表是聚簇表,意思是InnoDB本身是一张大的索引组织表,也是一个根据主键排序的大索引的B+树结构,我们在InnoDB里面另外建立自己想要索引的表的字段 聚簇索引就意味着InnoDB表本身,而我们把这些根据其他字段排序的索引称为二级索引(secondery class) 四、在数据库中如何建立索引 在MySQL中主要建立两种类型的索引 1.单列索引 create index idx_name on tb_student(name); ????????????? 索引名??????? 表名?? 字段名 2.联合索引 create index idx_name_age on tb_student(name,age); #索引中先根据name排序,name相同的情况下根据age排序 五、索引维护 首先介绍下什么是索引维护?这是一个关乎性能的重要概念 如果索引所在字段发生了修改、删除、插入等操作,那么索引项就会发生变化,因此如果不能保证索引的有序,那么就不能索引的准确与效率,而索引的排序发生了变化的这个行为,我们称为索引维护 在insert/delete/update操作时,为了维护索引的排序,数据库会自动的完成索引项的维护,索引的排序,这些行为对用户是透明的,感觉不到的 在一个有索引的表中,创建它时,实际上还同时创建了索引排序的表,因此在DML中,插入等操作不再是普通的插入,MySQL将它封装成了一个事务,连着索引项的排序表一起操作 因此,我们应当严格控制表上的索引数量,否则容易影响数据库的性能 总结索引维护如下： 1、索引维护由数据库自动完成 2、插入/修改/删除每一个索引行都变成一个内部封装的事务 3、索引越多,事务越大,代价越高 4、索引越多,对表的插入和索引字段的修改就越慢 因此可以看出索引并非是越多越好,在工作中也要慎用,尤其对于写操作较为频繁的业务 六、如何正确的使用索引? 1、依据where查询条件建立索引 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)? ->正确 2、根据排序order by,group by,distinct 字段添加索引 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)? ->正确 七、到底哪些字段适合创建索引? 1、字段值的重复程度,如图：
身份证号码由于基本上不可能重复,因此选择性非常好,而人的名字重复性较低,选择性也不错,性别选择性较差,重复度非常高 2、选择性很差的字段通常不适合创建索引,但也有例外 如：男女比例相仿的表中,性别不适合创建单列索引,如果走索引不如走全表扫描, 因为走索引的I/O开销更大 ??? 但如果男女比例极度不平衡,要查询的又是少数方,如：理工学校、IT公司等可以考虑使用索引 3、联合索引中选择性好的字段应该排在前面 select * from tab_a where gender=? and name=? idx_name_gender(name,gender)?? ->正确 4、联合索引可以为单列、复列查询提供帮助 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=? ->正确 5、合理创建联合索引,避免冗余 (a),(a,b),c)????? ->不可取 (a,c)??????????????? ->正确,可以覆盖前两个 今天就到这了...待续 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
