MySQL学习二（索引及使用、场景）

索引！！！
索引的不同角度分类：
1.从“功能逻辑”上说，分为：普通索引、唯一索引、主键索引、全文索引
2.从“物理实现方式”上说，分为：聚簇索引、非聚簇索引（二级索引）
3.从“作用字段个数”上说，分为：单列索引和联合索引

1.普通索引：不附加任何限制条件，用于提高查询效率，可以创建在“任何数据类型”，查询时可以直接使用where条件查询
2.唯一性索引：凡是声明了唯一性约束的字段，自动添加了唯一性索引，在一张表中可以有多个唯一索引
3.主键索引：是一种特殊的唯一性索引，在唯一索引上添加了不为空约束，在表中只能有一个主键索引（也可以称为聚簇索引）
	为什么呢？
	答：这是由主键索引的物理实现方式决定的，因为数据存储在文件中只按照一种顺序进行存储
4.单列索引：单列索引作用在一个字段上的，可以是普通索引、唯一性索引、全文索引
5.多列索引：作用在多个字段上的。使用时遵循“最左前缀集合”，就是：多个（id，name，sex）字段建立了多列索引，查询条件中使用了id时才会被使用
6.全文索引：是目前“搜索引擎”的关键技术，它能够利用“分词技术”等多算法智能分析出文本文字中关键词的频率和重要性，然后按照算法规则智能筛选出结果

（使用FULLTEXT来设置全文索引），
典型的两个类型：自然语言索引 + 布尔全文索引，从mysql3.23.23开始支持，5.6以后才支持innodb
7.空间索引，使用(SPATIAL)来设置空间索引，只能建立在空间数据类型上，可以提高系统获取空间数据的效率，如（GEOMETRY/POINT/LINESTRING）等

小结！！！！：
Innodb：支持B-Tree、Full-text等索引，不支持hash索引
MyISAM：支持B-Tree、Full-text等索引，不支持hash索引

创建索引！！！！！！！！！
查看索引命令：
SHOW INDEX FROM 表名
创建表时创建索引！！！：
CREATE table mytest(
id int PRIMARY key AUTO_INCREMENT, #隐式创建了主键索引
name varchar(20));
表已经创建成功时添加索引！！！
语句格式：
ALTER TABLE 表名 add 【UNIQUE|FULL-text|Spatial】 index 索引名称(索引字段)
或者！！
CREATE INDEX 索引名称 ON 表名(索引字段)
2.显示创建索引语法：普通索引、单列索引
CREATE TABLE 表名(
字段 字段类型，
【UNIQUE|FULL-text|Spatial】 【index|key】列名 【asc|desc】
)
例子：普通索引 + 单列索引
CREATE table book(
id int ,
name varchar(100),
Authors VARCHAR(100),
info VARCHAR(100),
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
INDEX idx_name(name)
);
3.内置的性能分析工具：
1.使用EXPLAN命令查看
4.可以通过删除主键约束的方式删除主键索引，自增列（AOTO_INCREMENT约束）会报错
ALTER TABLE 表名
DROP PRIMARY KEY;
5.创建联合索引（多列索引）语法是相同的
例子：联合索引（多列索引）
CREATE table book1(
id int ,
name varchar(100),
Authors VARCHAR(100),
info VARCHAR(100),
COMMENT VARCHAR(100),
year_publication YEAR,
#声明联合索引
INDEX mul_id_name_info(id,name,info)
);
6.全文索引用match+against方式查询！！！！！！！
如：SELECT * FROM 表名 WHERE MATCH(索引字段) AGAINST(‘查询的字符’);
很快，但是存在精度问题！！！MySQL5.5之前Innodb不支持

删除索引！！！
1.使用alter table 删除
ALTER TABLE 表名 DROP INDEX 索引名称
注意！自增约束唯一索引不能删除！！

2.使用drop index 删除
Drop index 索引名称 on 表名

MySQL8索引新特性！！！
1.支持降序索引！在my5.7中，DESC是不支持的，声明的还是 升序
2.隐藏索引！！声明索引时，添加invisible关键词
3.修改索引可见性：
alter table 表名 alter index 索引名称 invisible
当索引被隐藏时，它的内容仍然是正常和索引一样实时更新的，如果一个索引需要长期被隐藏，那么可以将其删除，
因为索引的存在会影响插入、更新和删除性能。
4.设置隐藏索引对查询优化器可见
命令：select @@optimizer_switch 查看use_invisible_indexes变量
设置 set session optimizer_switch=“use_invisible_indexes=on”

索引使用场景！！！
1.数值有唯一性的字段
2.where查询常用字段
3.orderby和groupby字段，groupby索引命中优先orderby （使用联合索引效率更高）
4.update、delete的where条件列
5.distince字段需要创建索引
6.多表join链接操作时，但是表不要超过3张，且对where条件创建索引，对用于连接的条件创建索引
7.使用列的类型小的创建索引
8.使用字符串前缀创建索引
9.区分度高（散列性高）的列创建索引
10.使用最频繁的列放在联合索引的左侧
11.在多个字段都要创建索引的情况下，联合索引优于单列索引

不适用索引的场景！！！
1.在where条件（group、order）使用不到的字段，不要设置
2.数据量小的表最好不要使用索引
3.有大量重复数据的列上不要设置索引
4.避免对经常更新的表创建过多的索引
5.不建议用无序的值作为索引（MD5、UUID等）
6.删除不在使用或者很少使用的索引（冗余索引、重复索引（如主键列单独增加索引））