为什么用索引
索引可以大大提高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) ;
使用索引需要注意什么
- 创建索引字段的列要尽可能少的占用存储空间
- 在满足业务系统的需求内尽可能自增
- 索引字段尽可能不要未NULL
- 索引尽量创建在重复值少的字段上
- 不要给表所有字段创建索引,并不是索引越多越好
- 尽量避免索引失效
- 索引字段尽量不要频繁改动
什么情况下索引失效
- 在使用like查询的时候 左边不要加%
- 索引字段上不要添加任何表达式操作
- 索引字段在使用的时候不要出现类型的隐式转换 例如 字段phone时 varchar 类型 ,查询时 where phone= 10086 这样索引时失效的 应该使用where phone= ‘10086’;
- 组合索引在进行使用的时候要遵循最左匹配原则
- in 或者or 在很多情况下会导致索引失效,但是要根据实际情况来进行判断 尽量不使用吧
- 在使用组合索引的时候,如果中间的索引列使用了范围从查询,会导致索引失效
相关知识
回表:查找过程中根据索引找到对应的与数据绑定的索引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