MySQL索引

为什么用索引

索引可以大大提高MySQL的检索速度。

索引类型 （ 逻辑角度）

• 普通索引：普通索引时最基本的索引，他没有任何限制，允许在定义索引的列中出现重复值 NULL值 （建议索引使用的列最好不为NULL, 不建议重复值过多的列创建索引）
• 唯一索引：索引的值必须唯一，允许又NULL值，如果时唯一组合索引，列值的组合必须唯一
• 组合索引：多个字段上创建的索引，遵循最左原则
• 主键索引：他是一种特殊的唯一索引，一张表只能又一个主键，不允许又NULL值，一般在创建表的时候会指定主键，主键默认就是主键索引。
• 全文索引：它更像一个索引引擎,用来查找文本中的关键字，而不是直接与索引中的值比较，全文索引需要配合match against 操作使用，而不是一般的where和like
• 空间索引：空间索引是对空间数据类型的字段建立的索引，创建空间索引的列，必须被声明为not null。

索引使用

查看索引信息
	SHOW INDEX FROM table_name\G

普通索引

创建索引
	CREATE INDEX indexName ON table_name (column_name); 
修改表结构（添加索引）
	ALTER table tableName ADD INDEX indexName(columnName); 
创建表的使用直接指定
	CREATE TABLE mytable(  
		ID INT NOT NULL,   
		username VARCHAR(16) NOT NULL,  
		INDEX [indexName] (username(length))  
	);  
删除索引
	DROP INDEX [indexName] ON table_name; 

唯一索引

创建索引
	CREATE UNIQUE INDEX indexName ON table(column_name(length)) ;
修改表结构（添加索引）
	ALTER table column_name ADD UNIQUE [indexName] (column_name (length)); 
创建表的使用直接指定
	CREATE TABLE mytable(  
		ID INT NOT NULL,    
		username VARCHAR(16) NOT NULL,  
		UNIQUE [indexName] (username(length))  
	);   
删除索引
	DROP INDEX [indexName] ON table_name; 

组合索引

 组合索引的创建方式 时使用以上 普通、唯一索引的创建方式 column_name  传多个 ”,“分割。
 例如: CREATE INDEX indexName ON table(A,B,C) ;

使用索引需要注意什么

1. 创建索引字段的列要尽可能少的占用存储空间
2. 在满足业务系统的需求内尽可能自增
3. 索引字段尽可能不要未NULL
4. 索引尽量创建在重复值少的字段上
5. 不要给表所有字段创建索引，并不是索引越多越好
6. 尽量避免索引失效
7. 索引字段尽量不要频繁改动

什么情况下索引失效

1. 在使用like查询的时候 左边不要加%
2. 索引字段上不要添加任何表达式操作
3. 索引字段在使用的时候不要出现类型的隐式转换 例如 字段phone时 varchar 类型 ，查询时 where phone= 10086 这样索引时失效的 应该使用where phone= ‘10086’;
4. 组合索引在进行使用的时候要遵循最左匹配原则
5. in 或者or 在很多情况下会导致索引失效，但是要根据实际情况来进行判断 尽量不使用吧
6. 在使用组合索引的时候，如果中间的索引列使用了范围从查询，会导致索引失效

相关知识

回表：查找过程中根据索引找到对应的与数据绑定的索引id，在根据索引id查到全部的结果这个过程叫做 回表。

索引覆盖：查找过程中查的字段与where索引字段相同，这个时候就不需要在根据索引id查询数据 这个过程叫做索引覆盖

最左匹配原则：组合索引在查询的时候必须从左向右匹配，下图中 第二个会索引失效，第四个优化器会优化

索引下推：索引条件下推优化，MySQL索引下推(5.6版本+) 默认启用，索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数

通过index_condition_pushdown标志来控制
SET optimizer_switch = 'index_condition_pushdown=off';

InnoDB 索引的存储原理

InnoDB中使用B+TREE 的结构来存储索引的；

为什么使用B+TREE？

https://blog.csdn.net/qq825478739/article/details/121398608