mysql-索引Index 个人笔记

1、索引是排好序的快速查找数据的数据结构

        目的：用于排序，提高查询效率

        优点：类似大学图书馆建立书目索引，提高数据检索效率，降低了数据的IO成本

                   通过索引列对数据进行排序，减低数据排序的成本，降低cpu的成本

        缺点：①索引实际上也是一张表，保存了主键和索引字段，并且指向实体表的记录，索引要占用空间；

                 ②、提高了查询效率，但会降低更新表的速度，如对表进行增删改时。更新时，MySql不仅要保存数据，还要保存一下索引文件每次添加了索引列的字段，并且mysql还会调整数据的排序。

        有时候删除数据，不在数据库删除数据，只是设为不可用状态

        原因：①可以浏览历史记录 ， ② 不用修改索引

        数据结构：B树  （多路搜索树，不一定是二叉树）

2、索引的分类

        单值索引：即一个索引只包含单个列，一个表有多个单值索引。

        唯一索引：索引列的值必须唯一，可以为null值

        复合索引：即一个索引包含多个列

        覆盖索引

        聚集索引

        非聚集索引

        索引语法：

                创建：create [unique] INDEX  indexName on tableName(columnName(length))

                    alter  tableName add [unique] INDEX 【indexName】on (columnName(length))

                删除   drop INDEX  【indexName】 on tableName

                查看  show INDEX from tableName

3、索引的结构

        A、BTree索引：

        B、Hash索引

        C、full-text索引

4、索引建立

        A、适合：①、主键字段默认建立索引

                          ②、频繁作为查询字段的字段

                          ③、查询中与其它表关联的字段，从表的主键建立索引

                        ④、频繁更新的字段不适合建立索引

                        ⑤、where条件里用不到的字段不用建立索引

                          ⑥、查询中，排序、统计、分组的字段建立索引

        B、不适合

                        ①、表记录太少

                        ②、经常增删改的表

                        ③、数据列的值重复太多，不适合建立索引

5、索引分析

          A、语法： explain +SQL语句

                作用：explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句。分析你的查询语句或表结构的性能瓶颈

                show index from 表名   显示表建立的索引

          B、执行计划包含的信息:id、select_type、table、type、possible_keys,key,，key_len,ref,rows,extra

① 、id  select 查询的序列号，包含一组数字，表示查询中执行select 子句或操作表的顺序

         id相同，执行顺序由上至下

         id不同时，如果是子查询，id值递增，id 值越大优先级越高，越先被执行

        id时相同不同，同时存在。id值越高越先执行，id值相同由上至下顺序执行

②、select_type   查询类型，用于区别  普通查询、复杂查询、子查询等的复杂查询

 ③、table   表名

        SIMPLE：简单的select查询，查询中不包含子查询或者UNION

        PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为

        SUBQUERY：在SELECT或WHERE列表中包含了子查询

        DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）MYSQL会递归执行这些子查询，把结果放在临时表里

        UNION：若第二个SELECT出现在UNION之后，则被标记为UNION若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

        UNION RESULT：从UNION表获取结果的SELECT

④、type    显示的是访问级别，反应查询的性能，结果值是从好到坏

        System>const>eq_ref>ref>range>index>all

        const: 直接按主键或唯一键读取  例如 where id=1

         eq_ref  用于连表查询的情况，用连表的主键或唯一性约束查询。   

                        例如  where t1.id=t2.id   对应的结果只有一条

        ref         非唯一性扫描，有个行与条件匹配

        range            范围查询     between，<,>,in()

        index          索引查询,只遍历索引树

        All               全表查询

         一般最少能达到rang，最好达到ref

⑤、possible_keys    这张表 可能存在的索引

⑥、key     实际被使用的索引。如果为null，没有使用索引或没有建立索引，

                查询中使用了覆盖索引，则该索引进出现在key列表中

                覆盖索引  查询的字段与建立索引的字段相同，就从索引中查询

⑦ key_len  索引字段的最大可能长度，通过表的定义获得

⑧ref     哪些表的索引列被引用或者常量

⑨rows   找到记录所需要大概的行数

⑩extra    包含不适合在其它列显示但是否重要的额外信息

        Using filesort   只利用了索引的实现查找，无法利用索引完成的排序操作称为"文件排序" ，需要使用外部的索引排序

         using temporary    使用了临时表保存中间结果

         using index   表示相应的列实现了覆盖索引，避免了访问数据行

                                  同时出现using where  则表示索引被用来执行索引键值的查找

                                   没有出现using where  则表示引用来读取数据而非进行查找

        using where   索引被用于索引键值查找

        using join  buffer   多表连接时，连接缓存

  5、索引优化

         1、优化

          A、字段范围搜索会使索引失效 

        alter table 'user' add index  idx_user (className,age,score)

        select  name  from  user where className='xx' and age>18 order by score 

           //age之后的索引会失效，即score失效

        //所以应该修改为

         alter table 'user' add index  idx_user (className,score)

        B、连接    左连接    在右边建立索引     （主表不建索引，从表建索引）

        2、索引失效（应该避免）

                最佳左前缀原则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配 

              索引优化：  1、索引字段上进行运算会使索引失效

                             2、避免使用！=,<>,is null 或 is not null ,in ,not in等这样的操作符，不然会进行全表查询

                               3、范围查询会使部分索引失效

                                4、模糊查询     '%ss'会使索引失效  

                                5、select 字符串不加单引号，会使索引失效

                                6、尽量少使用or，会使索引失效

                                不等空值还有or，索引失效要少用。   

问题：解决 like '%ss%'  失效的方法                覆盖索引

覆盖索引：select的数据列只用从索引中就能够取得，不必回表读数据。

        尽量使用覆盖索引查询，少使用select *

        alter  table  staffs add index idx_nameage on staffs(name,age)

        //select  要与索引部分或全部匹配，不匹配则全表查询

        select   name，age from staffs  where  name like  '%aa'

                    

              group by  基本上都先进行排序，然后又临时表产生

3、索引优化

        单键索引，尽量选择当前查询过滤性最好的字段

        选择复合索引，当前查询中过滤性最好的字段在索引顺序中，位置越靠左

        尽量选择能够包含当前查询的where子句中更多字段作为索引

        尽可能通过分析统计信息和调整查询的写法来达到选择合适索引的目的

4、查询优化

        A、小表连接大表，减少表与表之间的连接次数

                 //用于A表的数量大于B表

                     select * from A where  id in（select id from B ）

                //将主查询的数据放到子查询做条件验证，根据验证结果决定数据是否保留

                //select  ....  from table where exit( select 1 from table where A.xx=B.xx)

                //用于A表的数据量小于B表

                select * from A  where exist （selecr 1 from B where A.id =B.id）

        B、order by 优化   尽量不使用select *

               1、 order使用两种排序方式 

                a、index方式，mysql扫描索引本身完成排序，效率高

                b、filesort方式    两种实现   效率差

                         双路排序    第一次读取磁盘IO，取出order by的字段和指向数据的指针信息，在缓存buffer进行排序后，在第二次从磁盘IO取出数据。

                        单路排序     第一次就把所有的行数据取出来，放到缓存buffer进行排序然后输出

               c、优点：单路排序只进行一个磁盘IO操作，减少了时间

                d、缺点：

                        单路排序占据更大的缓存空间。当表的数据大时，单路排序需要多次进行磁盘IO取出，花费的时间比双路还多

             2、order by满足两种情况，使用index方式

                a、order by 后的字段 是索引的最左前列（字段相同，顺序不同也不行）

                b、where 、order by 同时使用时，字段的条件组合满足最左前列（字段相同，顺序不同也不一定行）

                3、order by 尽量使用index方式，少使用filesort方式。

                4、group by  优化  更order by 差不多

                        group by  实质是先排序后分组，遵循索引键的最左原则

                        where高于having  能写在where 的条件  就写在where

        

       4、show profile  是mysql提供用来分析当前会话中语句被执行的资源消耗情况。