Mysql应用MySQL InnoDB索引介绍及优化(一)

《Mysql应用MySQL InnoDB索引介绍及优化(一)》要点：
本文介绍了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),(a,b,c)      ->不可取
(a,b,c)                ->正确,可以覆盖前两个

今天就到这了...待续

转载请注明本页网址：
http://www.vephp.com/jiaocheng/5800.html