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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

索引总结：