索引相关知识

索引

	索引的优点：加快检索速度
	索引的缺点：降低索引列删除、插入、更新的速度，会占用磁盘的空间

索引结构

1. BTree（Myisam）： Myisam默认索引，MyISAM的索引文件仅仅保存数据记录的地址
2. B+Tree（InnoDB）： InnoDB索引，InnoDB的索引文件保存的是具体的数据
3. 聚簇索引和非聚簇索引 ：

1. 聚簇索引：在主键上建立的索引,k,v(主键，数据行)
2. 非聚簇索引：在主键上建立的索引，k,v(k，主键)，通过找到主键再去找对应的数据行

4. 全文索引（FULLTEXT）: 用来搜索指定字符，mysql5.7之后支持中文。详细信息见下文
5. Hash索引： Hash索引只有Memory，NDB两种引擎支持。如果多个hash值相同会出现碰撞现象
6. R-Tree索引： 仅支持geometry数据类型，支持存储该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种，相对于Btree，RTree的优势在于范围查找

注：黑框为Mysql8.0.26；其余为Mysql5.6.19

索引类型

1. 主键索引： 设定为主键后数据库会自动建立索引，innodb为聚簇索引 增加：Alter table tablename add
primary key (column) 删除：Alter table jsh_accounthead drop primary key
如果主键是自增，先取消自增再删除。
2. 普通索引： 单一索引：由一个列组成 组合索引：由多个列组成 增加：Alter table tablename add unique
index_name (column) 删除：Drop index index_name on table
3. 唯一索引： 索引列的值必须唯一，但允许有空值 增加：Create UNIQUE index IndexName On
tablename (column)
Alter table tablename add unique (column) 删除：Drop index x on jsh_accounthead
4. 全文索引（全文检索）： 查找的是文本中的关键词，主要用于全文检索(只有字段的数据类型为 char、varchar、text
及其系列才可以建全文索引) 增加：create FULLTEXT index index_name on tablename
(column) 删除：Drop index index_name on table 语法：select * from test
where match(content) against(‘a’);

a.自然语言的全文索引：
默认情况下，或者使用 in natural language mode 修饰符时，match()
函数对文本集合执行自然语言搜索。(自然语言搜索引擎将计算每一个文档对象 查询的相关度，说白了就是出现次数，出现次数如果超过50%的记录中都出现了，那么自然语言将不会搜索这类词。他适用于搜索文档中出现频率较低的词。这个需要在MyISAM引擎下)

验证：
```sql
CREATE TABLE articles (
	   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	      title VARCHAR(200),
	      body TEXT,
	        FULLTEXT (title,body)
	       ) ENGINE=Myisam; INSERT INTO articles (title,body) VALUES
	       ('MySQL Tutorial','DBMS stands for DataBase ...'),
	       ('How To Use MySQL Well','After you went through a ...'),
	       ('Optimizing MySQL','In this tutorial we show ...'),
	       ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
	       ('MySQL vs. YourSQL','In the following database comparison ...'),
	       ('MySQL Security','When configured properly, MySQL ...');
	        SELECT * FROM articles
	     WHERE MATCH (title,body)
	     AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
```

结果：

```sql
修改引擎为InnoDB后：执行SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
```

结果：

涉及到最小搜索长度通过这个sql查看：show variables like '%ft%';
红色的显示为MyISAM引擎下的长度；
黄色的显示为InnoDB引擎下的长度；

验证：三位字符

两位字符：

配置最小搜索长度:

>   全文索引的相关参数都无法进行动态修改，必须通过修改 MySQL 的配置文件来完成。修改最小搜索长度的值为 1，首先打开 MySQL
> 的配置文件 /etc/my.cnf，在 [mysqld] 的下面追加以下内容 innodb_ft_min_token_size = 1
> ft_min_word_len = 1 然后重启 MySQL
> 服务器，并修复全文索引。注意，修改完参数以后，一定要修复下索引，不然参数不会生效。 两种修复方式，可以使用下面的命令修复 repair
> table test quick; 或者直接删掉重新建立索引

b.布尔全文索引（不适用于MyISAM）😗*

> 在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。 MySQL
> 内置的修饰符，上面查询最小搜索长度时，搜索结果 ft_boolean_syntax
> 变量的值就是内置的修饰符，下面简单解释几个，更多修饰符的作用可以查手册  			   
必须包含该词，+代表AND
必须不包含该词，-代表NOT

```sql
例：SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
```

详情见官方文档：[https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html](https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html)

限制：mysql5.6.4以前只有Myisam支持，5.6.4版本以后innodb才支持，但是官方版本不支持中文分词，需要第三方分词插件。5.7以后官方支持中文分词。

5.SPATIAL 空间索引：MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

基本语法：
创建：Alter table add index indexname (columnname)
    create index indexname on table (columnname)
删除：drop index indexname on table
查看：show index from table

哪些情况需要建立索引？

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引（where后面的语句）
3. 查询中与其他表关联的字段，外键关系建立索引
4. 单值、组合索引的选择问题（在高并发下倾向于创建组合索引）
5. 查询中排序的字段，排序字段若是通过索引去访问将大大提高排序速度
6. 查询中统计或者分组字段

如何挑选索引列？

为用于搜索、排序和分组的列创建索引，而用于输出显示的列则不用创建索引

1. 认真考虑数据列基数（是指列中重复值越低越好）
2. 索引短小值，应尽量选用较小的数据类型 短小值可以让比较操作更快，从而加快索引的查找速度 短小值可以让索引短小，从而减少对磁盘I/O的请求 对于更短小的键值，索引块可以存更多的键值
3. 索引字符串值的前缀 对于BLOB或TEXT列只能创建前缀型索引 FULLTEXT索引里的列是以满列值的方式进行索引的，不能进行前缀索引，即使指定了前缀长度，也是无效的
4. 利用最左前缀 创建复合索引的时候相当于创建了多个索引。
5. 不要建立过多的索引 建多了占内存，而且可能会导致MySql无法使用最优索引
6. 让参与比较的索引类型保持匹配 HASH索引适用于=或者<=>,查询效率特别高 B树适用于范围查找
7. 利用慢查询日志找出那些性能低劣的查询

哪些情况下不要创建索引？

1、表记录少
2、经常增删改的表（提升查询速度，降低增删改速度，因为要额外保存索引）
3、where条件用不到的字段
4、数据重复切分布平均的表字段，否则索引效果不大

如何查看当前SQL使用了什么索引？

Explain+SQL；
possible_keys为可能使用的索引类型
key为实际使用的索引类型

如何优化索引？

主要看上图的type、key、rows、Extra这几个字段

1. type （何种类型的查询）：常见参数：性能由最优----->最差

const： 表示通过索引一次就查到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，Mysql
eq_ref： 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描
ref： 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
range： 只检索给定范围的值，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询
index： full index scan，index与all的区别为，index只遍历索引树。这通常比all快，因为索引文件通常比数据文件小，索引会加载到内存中，数据是存在硬盘上
all： full table scan，遍历全表数据找到匹配的行

2. key： 表示实际所用的索引，结合type，看看你建的索引是否为最优索引（满足需求是第一要义）
3. rows： 查询到的结果行
4. filtered： 符合条件的记录数占百分比
5. Extra： 扩展列

常见参数：

**Using filesort：**说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MYSQL中无法利用索引完成的排序操作成为“文件排序”
**Using temporary：**使用了临时表保存中间结果

以上这两种情况出现需要进行sql优化！！！

**Using index：**使用了覆盖索引
**Using where：**使用了where条件
**Using join buffer：**使用了连接缓存
**impossible where：**where条件的值总是false 例如 where name=‘a’ and name=‘b’

索引什么情况下会失效？

1、左前缀匹配原则
如果索引了多列，需要遵守最左前缀匹配原则，查询从索引的最左前列开始，并且不跳过索引中的列。
例：建立组合索引（name,age,pos）

可以理解成增加了三个索引(name)，(name,age)，(name,age,pos）三个索引

第一条sql就出现了索引失效，因为ref中只有一个const，可以理解成第一个sql只用了（name）这个索引，而pos没有使用索引
(name,age,pos）当覆盖索引中的所有列都出现在查询条件中时，可以不遵照顺序，查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高，最后才能生成真正的执行计划，所以不论以何种顺序都可使用到联合索引

2、在索引上进行计算、函数、类型转换（自动、手动）等操作

3、覆盖索引不能使用范围查询右边的列

4、mysql在使用（!=或<>）的时候会导致全表扫描。这个是区分版本的
5.6版本是索引失效的

8.0版本是不会失效的

5、is not null是索引失效的 is null不是

6、模糊查询（%name%，%name）是失效的，（name%）不失效

7、不加单引号，导致底层进行了一次类型转换，这个可以归属为第二类

8、使用or会导致索引失效

最后：虽然索引的优点远大于缺点，但是如果不需要特定的索引来增加查询速度，请勿添加索引。