mysql索引的数据结构和使用原则

索引是什么？

索引是帮助mysql高效获取数据的排好序的数据结构，以协助快速查询和更新数据。

索引的类型： 普通索引  唯一索引 全文索引

索引数据结构的演进：

链表 -->  二叉树  -->  AVL树  -->  B树  -->  B+树

InnoDB内部存储结构：表空间  段   区(簇)  页   行

索引的方式: B+ tree  、 hash（hash的查找快，但hash会存在hash冲突，会出现不能范围查询等缺点）

实际上，我们并不能创建hash索引，选中也会生成B+tree，mysql底层会自动对一些热点数据，创建hash索引，自适应hash索引

Innodb：

联合索引：

考虑的点：离散度、最左匹配原则-联合索引、覆盖索引、索引下推

索引建立原则：

在离散度高的字段上创建

联合索引，左边的区分度尽量大一点，尽量建联合索引

覆盖索引：

当索引覆盖查询需要的全部字段时，覆盖索引，效率高

原因：减少了回表操作，提升sql性能

示例：select * from  student where name = 'zhangsan'

           select  id from student where name = 'zhangsan'

           二级索引包含主键值，不需要回表

索引下推：

        对条件的查询和判断，下推到引擎层判断

        示例：

        index(name,age)

        select * from student where name like 'zhang%' and age = 10

        mysql 5.6之前：只会走name的索引，找到zhang开头的所有数据，回表返回server层做判断

        5.6开始：会在引擎层，判断索引的age==10，下推到引擎层处理了减少了api调用查询

索引使用原则：

1、在where条件，join on 上，order by上的字段，创建索引

2、在离散度高的字段上创建

3、注意不要让索引失效（函数、计算、左模糊、类型转换）

4、联合索引，左边的区分度尽量大一点

5、剔除无用查询字段，走覆盖索引可以减少回表带来的IO

6、索引个数不要太多，表数据量到达一定才需要创建索引（浪费空间，更新变慢）

7、频繁更新的字段尽量不要作为索引（页分裂）

8、尽量规避一次范围查询过大，会导致走ALL，可以拆分大范围为小范围

explain：

id：标记sql的执行顺序，大的id先执行，id相等时，从上往下依次执行

select_type:   simple简单查询    primary复杂查询时最外围的查询  subquery子查询    derived查询临时表

table：访问的表

partations：查询的分区

type： 性能从优到差 system > const > eq_ref > ref > range > index > ALL 

            system 特殊的cost，表里只有一条数据   

            cost  通过主键或者唯一索引，唯一匹配一行，show warnings 转化为常量

            eq_ref  主键或唯一索引被连接(join),只会返回一条数据

            ref  与eq_ref相比，使用普通索引，索引要和某个值比较，可能会找到多个行

            range 使用索引范围查询

            index  扫描二级索引，二级索引比较小，比all快

            ALL  全表扫描

possible_keys：可能使用的索引

key：实际使用的索引   force  index、ignore index 可以用来强制使用索引或者忽略使用

key_len:  使用索引的长度 varchar计算为3N+2 2是字符串索引的长度

ref：查表用到的常量

rows： 要读取需要检测的行数，不是结果集行数

Extra：  Using index 使用覆盖索引，不需要回表

              Using where 使用where 查询的列未被索引覆盖

              Using index condition  查询的列，未被索引覆盖，有范围筛选

              Using temporary  临时表

              Using filesort   将用外部排序而不是索引排序

联合索引：