索引（index）

索引（index）

     1、什么是索引：

        索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。一张表的一个字段可以添加一个索引，当然多个字段联合起来也可以添加索引，索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

         在查询方面有两种方式：

                  第一种：全表扫描

                  第二种：根据索引检索。

     提醒1:在数据库表中的主、外键上都会自动添加索引对象;当字段上有unique约束,也会自动创建索引对象。  

     提醒2：在数据库中任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。

     提醒3：在MySQL中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，不论索引存储在哪里，索引在mysql当中都是以一个树的形式存在（B Tree）

     2、在什么条件下我们会考虑给字段添加索引：

          条件1：数据量庞大

          条件2：该字段经常出现在where后面，以条件的形式出现，也就是说这个字段经常被扫描。

          条件3：该字段很少进行DML语句操作（insert、delete、update）

        建议不要随便添加索引,因为索引也是需要维护的,如果太多反而会降低系统性能,建议通过主键查询,或通过unique约束的字段查询这样效率是比较高的。

     3、索引的创建和删除：

        创建索引：

        给emp表的ename字段添加索引  并起名：emp_ename_index

        create index emp_ename_index on emp(ename);

        删除索引：    

   删除emp表上的emp_ename_index索引对象。

        drop index emp_ename_index on emp;       

        查询表文件关联的索引:

        show index from emp;

     4、执行计划（explain）

        命令格式： explain + 查询语句

        命令作用： 展示当前查询语句是否通过索引来进行定位执行计划可以对查询语句的执行效率进行判断

        * 通过type属性展示查询语句执行效率

    * 执行效率分类：由慢到快

          - all 表示where对全表进行遍历得到的结果（最慢）（需要极力避免的查询方式）

          - type 表示where对全表进行遍历，只不过在select抓取字段内容时，从索引中抓取。

       虽然抓取速度有所提升，但是随着数据行的不断增加，检索效率会大幅度降低

      （也是需要极力避免的查询方式）

         - range 表示where不会对全表文件数据行进行遍历，而是直接从索引得到定位的数据行，将

        大幅度提升查询效率。这是进行sql优化时要保证的最低级别。但是这种级别存在

       【不稳定性】，当字段内容发生变化时，导致索引失效。

            create index sal_index on emp(sal);           #对emp表sal字段创建索引

            查询工资低于1000的职工信息语句的执行效率

            explain select * from emp where sal < 1000;       #range级别.没有创建索引是all级别

          【注意：当数据库发现从索引得到的数据行行数达到了表文件总行数的1/3时，此时考虑运行成本问题会放弃使用索引】

           - ref 表示where不会对全表数据行进行遍历，而是直接从索引得到定位的数据行，这种情况

                 根据定位条件一次只能得到一个数据行，属于比较稳定的执行效率，sql优化需要努力

                 达到的级别

                 create index ename_index on emp(ename);

                 explain select * from emp where ename = 'SMITH';      #ref级别

            - const 根据主键字段上索引进行定位，是执行效率最快的，但是在实际使用过程中几乎不会被用到，很难达到。

              explain select * from emp where empno = '7369';       #const级别

           5、索引也会失效：

        - 在进行模糊查询的时候以%开始索引会失效：select * from emp where ename like '%T';

        - 使用or的时候，如果其中一边字段没有索引，那么另一边的字段即使有索引也会失效，这就是为什么不建议使用or的原因。建议使用union。

        - 使用复合索引的时候，没有使用左侧的字段查找，索引失效。

          举例：

          create index emp_job_sal_index on emp(job,sal);//创建复合索引（job，sal）

          如果使用job字段检索，索引不会失效，如果使用sal字段检索，索引就会失效！

        - 在where当中索引字段参加了运算，索引失效

        - 在where当中索引字段使用了函数，索引会失效

     6、索引是各种数据库进行优化的重要手段，优化的时候优先考虑的因素就是索引。越是唯一效率越高。

     7、索引的分类：

              单一索引：一个字段上添加的索引

              复合索引：多个字段上添加的索引

              主键索引：主键上添加的索引        （主键上会自动条件索引）

              唯一性索引：具有unique约束的字段上添加的索引        （有unique约束的字段会字段添加索引）

    8、索引的实现原理：

通过B Tree缩小扫描范围，底层索引进行了排序和分区，索引会携带数据在表中的“物理地址”，

最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率

是最高的。

select ename from emp where ename = 'SMITH';

通过索引转换为：

select ename from emp where 物理地址 = 0x3; #0x3是'SMITH'这行数据在硬盘上存储的'物理地址'。