msyq的索引

索引分类

按类型分类

普通索引 - NORMAL

普通索引就是经常用的那种，包括组合索引也是

唯一索引 - UNIQUE

唯一值

全文索引 - FULLTEXT

5.6 及以后的版本才支持

只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引

全文索引作用就是类似于like的范围查找，在大数据量的like下，比 like + % 快 N 倍，但是可能存在精度问题；

如果需要全文索引的是大量数据，建议先添加数据，再创建索引；

对于中文，可以使用 MySQL 5.7.6 之后的版本，或者第三方插件。

空间索引 - SPATIAL

是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON

只能在存储引擎为MyISAM的表中创建

上面4个是创建时候就可以看到的

主键索引

创建表时候自动创建的

按存储结构分类

B+树

Hash

只有Memory引擎显式的支持哈希搜索

哈希只支持等值比较，不支持任何范围查询。一旦哈希冲突很多的话，维护成本非常高。innoDB支持“自适应哈希索引”(adaptive hash index)。

查看是否使用索引

使用explain关键字，如果possible_keys行和key行都包含year_publication字段，则说明在查询时使用了该索引。

• id

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

• select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询

• table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集

1、如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名

2、表名是derivedN的形式，表示使用了id为N的查询产生的衍生表

3、当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id

• type

type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：

**system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL **

一般情况下，得保证查询至少达到range级别，最好能达到ref

• possible_keys

用了哪些索引

• key

实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

• key_len

表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

• ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

• rows

根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好

• extra

包含额外的信息。

--using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置

--using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除

--using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表明索引被用来读取数据，而不是真的查找

--using where:使用where进行条件过滤

--using join buffer:使用连接缓存

--impossible where：where语句的结果总是false

聚簇索引和非聚簇索引

聚簇索引

不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的索引紧凑的存储在一起。也就是存一张数据文件里

优点1、数据在一个文件夹2、访问快，因为叶子节点上就有数据3、使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点1、聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势2、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式。数据太大的话就会引起页分裂问题3、更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置

4、基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题

1. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

mysq页合并、页分裂

MySQL 5.6版本开始innodb_file_per_table参数默认设置为1。该配置下你的每一个表都会单独作为一个文件存储（如果有分区也可能有多个文件）。一个文件里有多个段(segments)，每个段由多个区组成，每个区由多个页组成。

每个页默认16kb 10384字符
每个区默认1M因此一个区是64个页

每个页面都有一个MERGE_THRESHOLD字段，默认是每页的50%大小。

当当前页面的最后一个数据存入发现存不下，就可能发生页分裂。反之MERGE_THRESHOLD< 50%，就会向前找一个页，进行页合并。

页合并和页分裂，都是不是按顺序去加页

比如已经有了#11和#12。当给#11存入数据过大，分裂一个#13，会在#11和#12中间导致页顺序乱了。这个时候就需要重新整理表。另一方面，要记住在合并和分裂的过程，InnoDB会在索引树上加写锁（x-latch）。在操作频繁的系统中这可能会是个隐患

非聚簇索引

数据文件跟索引文件分开存放

索引重构

当深度>=4 或者DEL_LF_ROWS/LF_ROWS>0.2或索引左右倾斜的情况下就要重建

• 删掉原索引，然后再创建索引： create index index_name on table_name (index_column); 这种方式相当耗时。

• 直接重建索引：alter index indexname rebuild online; 此方法较快，建议使用。

rebuild是快速重建索引的一种有效的办法，因为它是一种使用现有索引项来重建新索引的方法。alter index indexname rebuild就可以创建索引，但是为了防止重建索引时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题。由于新旧索引在建立时同时存在，因此，使用这种重建方法需要有额外的磁盘空间可供临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引移到新的表空间。

索引失效

1. 最佳左前缀法则

2. 计算、函数、类型转换(自动或手动)导致索引失效

3. 范围条件右边的列索引失效

4. 不等于(!= 或者<>)导致索引失效

5. is null可以使用索引，is not null无法使用索引

6. like以通配符%开头索引失效

1. 这个看到个有趣的设计，比如想要用like，但是%只能放后面。然后就有个空间换时间的操作，当前列(寸12345)外再加一列逆序(寸54321)。然后就可以like 后%相当于前一列的like前%。

7. OR 前后只要存在非索引的列，都会导致索引失效?

8. 左连接查询或者右连接查询查询关联的字段编码格式不一样

9. mysql认为不走索引更快的情况

最后，自测的一个索引覆盖小场景

当使用了组合索引name 和age，查询sql是select name where age时候，因为索引的叶子节点有这两个数据，不需要再去数据库表查询。就是使用了索引覆盖。但是效率type = index。

当只用单独age索引后，但是查询type = ref