MySQL进阶：索引

最新推荐文章于 2024-08-03 23:07:09 发布

突样未成年

最新推荐文章于 2024-08-03 23:07:09 发布

阅读量33

点赞数

分类专栏： MySQL 文章标签： mysql b树数据库

本文链接：https://blog.csdn.net/qq_39251549/article/details/131253831

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

在这里插入图片描述

索引

什么是索引

帮助数据库快速查询数据的数据结构。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JPgKp1Wt-1686921684599)(image-1.png)]

索引结构

B+树

二叉搜索树

顺序插入会形成链表，效率极低

红黑树？

层级也会很深

B树

多路平衡查找树

数据结构可视化在线网站

B+树

所有数据存储在叶子节点，叶子节点连成一个单向链表，其余节点起到一个索引的作用。

mysql对B+树加以改进，每个叶子节点增加一个指向相邻节点的指针（双向链表？）

相对于b树：树节点存放在页（16k）中，数据存放在叶子节点，而其他节点只存放索引，占用空间变小，从而一个节点可以存放更多索引，进而减少树的层级，提升索引效率。

hash索引

链表解决hash冲突
只支持等值匹配，不支持范围查找
不能排序
但是通常查询效率很高。

Memory引擎支持hash索引，InnoDB会在特定条件下自动根据b+树索引构建hash索引。

索引分类

主键索引(PRIMARY)：自动为主键建立的索引，只能有一个。
唯一索引(UNIQUE)：防止一列中有重复值，可以有多个。
常规索引：加快数据定位，可以有多个。
全文索引(FULLTEXT)：查找文本中的关键字，可以有多个。

根据存储形式：
聚集索引：数据和索引存放在一块，索引叶子节点上存放行数据，必须有且只能有一个。（默认主键索引就是聚集索引，如果表没有主键，那么第一个唯一索引作为聚集索引，没有唯一索引，innodb自动生成rowid的隐藏索引）
二级索引（辅助索引）：数据和索引分开存储，索引叶子节点上存储对应主键，可以有多个。

回表查询： 先在二级索引中找到对应的key，再通过key在聚集索引中找到对应的行数据。
例子：

slect * from persons where name='bob';

索引语法

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_column_name,...);

查看索引

SHOW INDEX FROM  table_name;

删除索引

DROP INDEX index_name ON table_name;

SQL性能分析

查看执行频次

SHOW [GLOBAL|SESSION] STATUS LIKE 'cOM_______';

对于查询语句比较多的表，优化的优先级高
主要优化查询语句

慢查询日志

vim /etc/mysql/mysql.conf.d/mysqld.cnf # mysql配置文件
# vim /etc/my.cnf # 黑马课程中的配置文件路径

slow_query_log=1 # 开启慢查询日志服务
long_query_time=2 # 查询时间超过2s则视为慢查询

sudo service mysql restart # 配置完成后，要重启mysql服务

查询profiles

profiles能够更加具体的看到时间消耗到哪去了。

select @@have_profiling;
select @@profiling;
SET profiling=1;
SHOW profiles;
SHOW profile [cpu] FOR QUERY query_id;

explain

EXPLAIN或DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT
语句执行过程中表如何连接和连接的顺序。
语法：

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

EXPLAIN结果各字段含义：
id：id大的步骤先执行，相同则自上向下执行。
select_type: simple,primary,union,subquery.
type: 表示连接类型，性能由好到差：NULL, system, const, eq_ref, ref, range, index, all。

优化时尽量往前优化，但一般不可能优化为NULL，NULL表示不访问任何表。
const：根据主键或唯一索引查询
ref: 根据非唯一索引查询
all：代表全表扫描，性能最低
index：用了索引，但也要对索引树进行全部遍历，性能也比较低。

possible_key: 展示可能用到的索引，一个或多个。

key: 实际用到的索引。

key_len: 使用的索引的字段中最大可能字节长度，并非实际使用长度，在不损失精度情况下，越小越好。

rows: MySQL认为必须要查询的行数，在InnoDB中，这是一个预估值，可能并不总是准确的。

filtered：查询结果的行数占需读取行数的比值，值越大越好。

Extra：额外信息。

索引使用原则

最左前缀法则

主要针对联合索引（多个字段），查询时先查索引最左边的字段，并且不跳过索引中的列。
如果跳过某一列，则后面的索引会失效。

只要字段存在于where条件中就会查询，跟放在where后面的顺序无关。

联合索引中，范围查询右侧的字段索引会失效。（如果业务允许，尽量使用大于等于或小于等于，而不是大于或等于）

索引失效

对索引列运算会使索引失效
字符串不加单引号，会发生隐式类型转换，会导致索引失效
模糊查询，如果仅仅是尾部模糊匹配，索引不会失效，但是若在头部模糊匹配，索引就会失效。
or连接的条件。如果or前面的列有索引，而or后的列没有索引，那么涉及的索引都不会用到。
数据分布的影响，当MySQL评估走索引查询比全表扫描还慢，就不会使用索引。

SQL提示

就是在SQL语句中加入一些人为的提示来达到优化操作的目的

use index(索引名)（建议用这个索引），ignore index(索引名)（不用这个索引），force index(索引名)（必须走这个索引），这个子句放在where前。

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且要返回的列都在索引树当中，避免回表查询），避免使用"select *"。

前缀索引

当某些字段为varchar或text类型时，字符串长度可能很长，比如一篇文章，对这样的字段直接建立索引，会使索引变得很大，降低磁盘IO效率。此时，可以使用前缀索引，即对字符串的前缀建立索引。

语法

create index index_name on table_name(col_name(n)); #n表示取字符串前n个字符为前缀建立索引

n的取值由选择性来决定，选择性：不重复个数与所有行数的比值。选择性越大越好，唯一属性的选择性为1，性能最好。
求选择性：

select count(distinct email) / count(*) from tb_usr;
select count(distinct substring(email, 1, n)) / count(*) from tb_usr; #设置不同的n，来看选择性的变化

前缀索引一定会发生回表查询？找到前缀，后要看完整字符串是不是我们要找的

单列索引&联合索引

如果查询涉及多个字段，建议建立联合索引，避免回表查询。

联合索引中，把多个字段放一块当索引，按最左边顺序构建索引树，如果大小相同，再按下一个字段的大小排序。（最左前缀法则的根本原因）

索引设计原则

针对数据量大，且查询比较频繁的表建立索引。
针对常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
对于较长的字符串字段，针对字段特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表查询。
要控制索引数量，索引影响增删改效率，且占用额外磁盘空间。
如果索引列不能存储NULL值，在创建表时用NOT NULL约束它。当优化器知道每列是否包含NULL时，可以更好的确定用哪个索引来进行最有效的查询。
引，区分度越高，使用索引的效率越高。
对于较长的字符串字段，针对字段特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表查询。
要控制索引数量，索引影响增删改效率，且占用额外磁盘空间。
如果索引列不能存储NULL值，在创建表时用NOT NULL约束它。当优化器知道每列是否包含NULL时，可以更好的确定用哪个索引来进行最有效的查询。