java——mysql随笔——索引

 索引简介：

 索引结构：    根据存储引擎的不同，索引结构也就不同(各个存储引擎支持的索引不一样)

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值时，它可以更好地确定哪个索引最有效地用于查询。

索引总结：