索引简介:
索引结构: 根据存储引擎的不同,索引结构也就不同(各个存储引擎支持的索引不一样)
B-Tree(多路平衡查找树)
参考王道考研B+Tree:https://www.bilibili.com/video/BV1b7411N798?t=5.3&p=74
BTree可视化工具:https://www.cs.usfca.edu/~galles/visualization/BTree.html
B+Tree与BTree的区别
1、B+Tree的所有数据都会在叶子节点出现。
2、B+Tree的叶子节点形成了一个单向链表。
索引的分类:
查询过程
:当查询条件是二级索引所在的列,二级索引的叶子节点仅存放对应数据的主键。查询首先从该二级索引上查找出该数据的主键,再使用该主键去聚集索引中查找该主键对应的数据,这个查询过程叫做回表查询
。
4、索引语法
创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col,name,...);
unique:表示唯一索引;
fulltext:表示全文索引;
如果这2个参数都不加,表示创建的是一个常规索引。
一个索引可以关联多个字段:单个字段表示单列索引,多个字段表示联合索引或者关联索引;
查看索引
SHOW INDEX FROM table_name;
删除索引
DROP INDEX index_name ON table_name;
查询当前表有哪些索引:
INNODB 引擎创建索引,默认用的是B+tree
删除索引:
SQL性能分析:
通过慢查询日志可以知道哪些SQL语句效率比较低:
profile详情:
explain执行计划:
explain执行计划
在SELECT语句前加上EXPLAIN关键字即可查看执行计划。EXPLAIN执行计划个字段含义:
Id:select查询的序列号,表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行)(多表查询时)
select_type:表示SELECT的类型,常见的取值有SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、
SUBQUERY(SELECT/WHERE之后包含了子查询)等
type:表示连接类型,性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all(使用主键索引或者唯一索引type为const,使用非唯一索引type为ref,all代表全表扫描)
possible_key:显示可能应用在这张表上的索引,一个或多个
Key:实际使用的索引,如果为NULL,则没有使用索引
Key_len:表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好。
rows:MySQL认为必须要执行查询的行数,在innodb引擎的表中,是一个估计值,可能并不总是准确的。
filtered:表示返回结果的行数占需要读取行数的百分比,filtered的值越大越好
索引使用——验证索引效率:
索引使用——最左前缀法则:
最左前缀法则
如果索引了多列(联合索引
),要遵守最左前缀法则
。最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。如果跳跃某一列,索引将部分失效(后面的字段索引失效)。
索引使用——范围查询:
pro:47
age:49
sta:54
范围查询
联合索引中,出现范围查询(>,<)范围查询右侧的列索引失效。因此,查询的时候,尽量使用>=和<=来代替。
索引使用——索引使用失效情况——索引列运算
引列运算
不要再索引列上进行运算操作,索引将失效。
例如:EXPLAIN SELECT * FROM tb_user where substring(phont,10,2)
= ‘15’;
索引使用——索引使用失效情况——索引列运算
pro:47
age:49
sta:54
字符串不加引号
字符串类型字段使用时,不加引号,索引将失效。
索引使用——索引使用失效情况——模糊查询
模糊查询
如果仅仅是尾部模糊匹配,索引不会失效,如果是头部模糊匹配,索引将失效
索引使用——索引使用失效情况——or连接的条件
or连接的条件
用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。只有两侧都有索引,索引才会被用到。
索引使用——索引使用失效情况——数据分布影响
数据分布影响
如果MySQL评估使用索引比全表更慢,则不使用索引。
索引使用——SQL提示
索引使用——覆盖索引&回表查询
索引使用——前缀索引
email选择性最好
索引使用——单列索引与联合索引
单列索引与联合索引
单列索引
:即一个索引只包含单个列
联合索引
:即一个索引包含了多个列
在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引
,而非单列索引。
索引设计原则:
索引设计原则:
1、针对于数据量较大,且查询比较频繁的表建立索引。
2、针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
3、尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
4、如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
5、尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
6、要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
7、如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。
索引总结: