MySQL索引总结

 索引

    类似于书目，用于快速检索

    优点：

        提高数据检索效率；

        提高表间的join效率；

        利用唯一性索引，保证数据的一致性；

        提高排序和分组效率；

    缺点：

         消耗更多的物理存储;

         数据变更时，索引也需要更新，降低更新效率

 

二叉树、B数、B+数、hash索引

二叉树

根节点即树的中间节点

二叉树的每个节点至多只有两颗子树，二叉树的子树有左右有序之分，次序不能颠倒

 

不适合做数据库索引：

    1、当数据量大的时候，树的高度会比较高，查询会比较慢；

    2、每个节点只存储一个记录，可能导致一次查询有很多次磁盘io；

B树

一个结点可以拥有多于2个子节点的多叉查找树

适合大量数据的读写操作，普遍运用在数据库和文件系统

B树的数据不会全部出现在叶子节点

一颗m阶（比如m=4）的B树满足下列条件

    1、树中每个节点至多有m个（m=4）子节点；

    2、除根节点和叶子节点外，其他每个节点至少有m/2（2个）的子节点；

    3、如果根节点不是子节点，则至少有两个子节点；

    4、所有叶子节点都出现在同一层，叶子节点不包含任何建值

B+树

B+树是B树的变体， 仍是多叉搜索树，在B树的基础上，做了一些改进：

     1、非叶子节点不再存储数据，数据只存储在同一层的叶子节点上；

     2、叶子之间，增加了链表，获取所有节点，不再需要中序遍历；

     3、非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引；  

     4、在MySQL中，为了方便，直接写成BTREE

    

哈希索引：

    建立在哈希表的基础上，它只对使用了索引中的每个值的精确查找有用

    对于每一行，存储引擎计算出了被索引的哈希码，他是一个较小的值，并且有可能和其他行的哈希码相同

    把哈希码保存在索引中，并且保证了一个指向哈希表中的每一行的指针

    MySQL中只有ndb这些存储引擎才用哈希索引

    哈希索引的话，如果数据量过大的话会出现哈希冲突

    innodb不支持hash索引（支持自适应hash索引）

B+树索引和哈希索引的比较

    大量唯一值的等值查询，哈希索引效率通常比B+tree高

    hash索引不支持模糊查找

    hash索引不支持联合索引中的最左匹配原则

    hash索引不支持排序

    hash索引不支持范围查询

    hash索引只能显示应用于memory、NDB表  

 

索引使用建议

    1、经常检索的列； 

    2、经常用于表连接的列；

    3、经常排序、分组的列；

 

索引不使用建议

    1、基数很低的列；

    2、更新频繁但检索不频繁的列；

    3、BLOB、TEXT等长内容列；

    4、很少用于检索的列；

 

数据库设计索引的原因

    1、用于提升数据库的查找速度

    2、提高聚合函数效率

    3、提高排序效率，order by  asc、desc

    4、有时可以避免回表

    5、减少多表关联时扫描行数

    6、列定义为default null 时，null值也会有索引，存放在索引数的最前端部分，因此尽量不要定义允许null

 

  

innodb索引类型

    聚集索引

    innodb表，只能够有一个，因为数据行在物理磁盘上只能有一份聚集存储。        

    innodb中，表即聚集索引，聚集索引即表

    mysiam没有聚集索引的概念

         聚集索引优先选择的列：

            1、int/bigint

            2、数据连续（单调）递增或自增

        不建议的聚集索引:

            1、修改频繁的列；

            2、新增数据太过离散随机

           不管innodb有没有主键，它都会有聚集索引，因为innodb是基于聚集索引的索引组织表

           主键一定是聚集索引，聚集索引不一定是主键

    主键索引

         innodb的主键索引与行记录是存储在一起的，故叫做聚集索引

            因为这个特性，innodb的表必须要有聚集索引：

            (1)如果表定义了PK，则PK就是聚集索引；
            (2)如果表没有定义PK，则第一个非空unique列是聚集索引；
            (3)否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
        主键由表中的一个或多个字段组成，它的值用于唯一的标识表中的某一条记录

        create table row_id(a int not null,b int null,c int not null,d int not null,unique key(b),unique key(d),unique key(c));

         insert into row_id select 1,2,3,4;

         insert into row_id select 5,6,7,8

        insert into row_id select 9,10,11,12;

        select a,b,c,d,_rowid from row_id;

        另外_rowid只能查看单列为主键的情况，对于多列组成的主键就显得无能为力了

        作用：

            1、保证数据的完整性；

            2、加快数据的操作速度；

            3、主键值不能重复，也不能包含null；

        主键选择建议：

            对业务无意义，不必受限于业务变化的影响；

            很少修改和删除；一般都是自增的

            不建议使用较长的列做主键，例如char(64)，因为所有的普通索引都会存储主键，会导致普通索引过于庞大；

            建议使用趋势递增的key做主键，由于数据行与索引一体，这样不至于插入记录时，有大量索引分裂，行记录移动；

            例如：select * from t where name='lisi';

            会先通过name辅助索引定位到B+树的叶子节点得到id=5，再通过聚集索引定位到行记录。所以，其实扫了2遍索引树。

 innodb主键特点：

            定义索引时，不管有无显示包含主键，实际都会存储主键值；

            在MySQL5.6.9版本后，优化器已能自动识别索引末尾的主键值，之前版本则需要显示加上主键列才可以被识别(经过测试，老版本也支持此特征)

               例如：

仍然遵循最左前缀原则：

唯一索引

        不允许具有索引值相同的行，从而禁止重复的索引或键值（在唯一约束上，和主键一样）

        唯一索引允许有空值（NULL）；

        一个表只能有一个主键，但可以有多个唯一索引；

        innodb表中主键必须是唯一索引，但唯一索引不一定是主键；

    联合索引

        多列组成的索引

        适合where条件中的多列组合

        可以避免回表（覆盖索引）

        支持多列不同的排序规则（8.0开始支持倒序索引）

        联合索引建议：

            where条件中，经常同时出现的列放在联合索引中；

            把选择性大的列放在联合索引最左边

        覆盖索引

        通过索引数据结构即可完成查询返回数据，不需要回表

        执行计划中，Extra为关键字 using index；

          desc select name from t1 where name like '%zyq%';

 

    前缀索引

        使用的原因：

            char、varcahr列太长，全部创建索引的话，效率太差，存在浪费；

            或者blob、text类型不能整列作为索引列，因此需要使用前缀索引

        部分索引选择建议：

            1、统计平均值；

2、满足10%-30%的覆盖度就可以

        缺点：

              无法利用前缀索引完成排序    

与全部索引对比：

全文索引

        5.6之前，全文索引支持mysiam引擎，5.6以后也支持innodb引擎

 

索引长度

索引的最大长度767bytes

启用innodb_lagrge_prefix,增加到3072bytes，只针对dynamic，compressed格式管用

对于redundant、compact格式，最大索引长度还是767bytes

mysiam表索引最大长度是1000bytes

最大排序长度默认是1024（max_sort_length）

 

索引管理

    创建删除索引

        alter table  t  add index idx(c1) using btree;

        create index idx_name on t(c1) using btree;

        create table 表时也可以顺便创建索引；

        alter  table t drop index idx_name;

        drop index idx_name on t;

    MySQL各版本，对于add Index的处理方式是不同的，主要有三种：

Copy Table方式

这是InnoDB最早支持的创建索引的方式。创建索引是通过临时表拷贝的方式实现的。

1. 新建一个带有新索引的临时表。

2. 然后锁原表，禁止DML操作，允许读操作。

3. 将原表数据全部拷贝到临时表（无排序，一行行拷贝）。

4. 然后Rename，升级字典锁，禁止读写。

5. 完成创建索引的操作。

这种copy方式的效率没有inplace好 ，因为copy需要记录undo和redo log，而且因为临时占用buffer pool引起短时间内性能受影响。

Inplace方式

这是原生MySQL 5.5,以及innodb_plugin中提供的创建索引的方式。所谓Inplace，也就是索引创建在原表上直接进行，不会拷贝临时表。

1. 新建一个带有新索引的临时表。

2. 然后锁原表，禁止DML操作，允许读操作。

3. 读取聚集索引，构造新的索引项，排序并插入新索引。

4. 然后Rename，升级字典锁，禁止读写。

5. 完成创建索引的操作。

可以避免重建表带来的IO和CPU消耗，保证DDL期间依然有良好的性能和并发。

Inplace方式创建索引，创建过程中，原表同样可读的，但是不可写。

Online方式

这是MySQL5.6.7中提供的创建索引的方式。无论是CopyTable方式，还是Inplace方式，创建索引的过程中，原表只能允许读取，不可写。

对应用有较大的限制，因此MySQL最新版本中，InnoDB支持了所谓的Online方式创建索引。

InnoDB的Online Add Index，首先是Inplace方式创建索引，无需使用临时表。在遍历聚簇索引，收集记录并插入到新索引的过程中，原表记录可修改。而修改的记录保存在Row Log中。当聚簇索引遍历完毕，并全部插入到新索引之后，重放Row Log中的记录修改，使得新索引与聚簇索引记录达到一致状态。

与Copy Table方式相比，Online Add Index采用的是Inplace方式，无需Copy Table，减少了空间开销；与此同时，Online Add Index只有在重放Row Log最后一个Block时锁表，减少了锁表的时间。

与Inplace方式相比，Online Add Index吸收了Inplace方式的优势，却减少了锁表的时间。

 

help create index;

    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

        [index_type]

        ON tbl_name (index_col_name,...)

        [index_option]

        [algorithm_option | lock_option] ...

    index_col_name:

        col_name [(length)] [ASC | DESC]

    index_option:

        KEY_BLOCK_SIZE [=] value

      | index_type

      | WITH PARSER parser_name

      | COMMENT 'string'

    index_type:

        USING {BTREE | HASH}

create index idx_c on sbtest1(c(20)) ALGORITHM=DEFAULT;

create index idx_c on sbtest1(c(20)) ALGORITHM=INPLACE;

写不会阻塞，会先写到缓存，然后完成之后同步数据；

copy的时候会阻塞写操作

create index idx_c on sbtest1(c(20)) ALGORITHM=COPY;

innodb_online_alter_log_max_size

如果DDL执行时间很长，期间又产生了大量的dml操作，以至于超过了innodb_online_alter_log_max_size变量所指定的大小，会引起DB_ONLINE_LOG_TOO_BIG错误。默认为128M，特别对于需要拷贝大表的alter操作，考虑临时加大该值，以此获得更大的日志缓存空间。

指定InnoDB表的联机DDL操作期间使用的临时日志文件大小的上限（以字节为单位）。每个正在创建的索引或要更改的表都有一个这样的日志文件。此日志文件存储在DDL操作期间在表中插入，更新或删除的数据。临时日志文件在需要时由innodb_sort_buffer_size的值扩展，最大为innodb_online_alter_log_max_size指定的最大值。如果临时日志文件超出大小上限，则ALTER TABLE操作将失败，并且将回滚所有未提交的并发DML操作。因此，此选项的较大值允许在联机DDL操作期间发生更多DML，但在表被锁定以应用日志中的数据时，还会延长DDL操作结束时的时间段。

 

    冗余索引
        根据最左匹配原则，一个索引是另一个索引的子集；

        使用pt-duplicate-key-checker检查

         select * from sys.schema_redundant_indexes;  

   无用索引：

        几乎从未被使用过的索引

        pt-index-usage检查低利用率索引，提供删除建议

        select * from sys.schema_unused_indexes;    

    全表扫描

        select * from sys.schema_tables_with_full_table_scans;

 

使用索引

    1、让MySQL自动选择

        select ... from  t where ...

    2、建议选择：

        select .. from t  use index(idx_name) where ...

    3、强制索引

        select ...  from t force index(idex_name) where...

 

 索引统计

    表统计信息
        show table status ;

        select * from information_schema.tables;    

        select * from mysql.innodb_table_stats;

     索引统计信息

        show index from  table；

        select * from information_schema.STATISTICS;

        select * from mysql.innodb_index_stats; 

 

 innodb_stats_auto_recalc

                默认开启，当修改数据量大于10%，自动更新统计信息；

           innodb_stats_persistent

                默认开启,统计信息持久化存储;当关闭时,统计信息不持久化,每次动态采集,存储在内存中,重启实例(需要重新统计),不推荐

            innodb_stats_persistent_sample_pages

               统计信息持久化存储时，默认每次采集20个page页

           innodb_stats_on_metadata 

                默认禁用，访问元数据时更新统计信息；

            iinnodb_stats_transient_sample_pages

                动态采集page，默认8个

 

            MySQL -A登录不会去更新统计信息

            不接-A的话当表或者分区表比较多的时候登录会比较慢

            use database是也需要更新统计信息，所以有时候很慢

 

执行计划

type

ALL 扫描全表数据

index 遍历索引

range 索引范围查找

ref 使用非唯一索引查找数据

fulltext 使用全文索引

const 使用主键或者唯一索引，且匹配的结果只有一条记录。

system const 连接类型的特例，查询的表为系统表。

possible_keys

可能使用的索引，但不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为NULL时就需考虑当前的SQL是否需要优化。

key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中

extra

extra的信息非常丰富，常见的有： 1.Using index 使用覆盖索引 2.Using where 使用了用where子句来过滤结果集 3.Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。 4.Using temporary 使用了临时表

 

索引不可用的情况

    1、通过索引扫描的记录数超过20%-30%，可能会变成全表扫描；

    2、联合索引中，第一个索引列使用范围查询；（这时用的部分索引）

    3、联合索引中，第一个查询不是最左索引列；

    4、模糊查询条件列最左以通配符%开始（覆盖索引除外）；

    5、两个独立索引，其中一个用于检索，一个用于排序（只能用到一个索引）

    6、join查询时，关联列数据类型（以及字符集）不一致也会导致索引不可用

        隐式类型转换

            u1='123' 不会转换

            u1='a' 可以正常走索引

        联合索引

desc select * from t7 where name='zyq';

            desc select * from t7 where c='abc';

            desc select * from t7 where c='zyq' and pad='sdfafadfasfdaf';

desc select * from t7 where c='abc' and name='zyq';