MySQL索引

最新推荐文章于 2024-09-25 20:59:21 发布

半新半旧

最新推荐文章于 2024-09-25 20:59:21 发布

阅读量719

点赞数 25

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

本文链接：https://blog.csdn.net/wangqiang996/article/details/141401878

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

B树

B树可以理解为平衡二叉树的拓展，也是一颗平衡树，但是是多叉的。又可以把B树看成1个节点可以拥有多于2个子节点的多叉查找树。

B树具有如下几个特点：

每个节点都存储了真实的数据。
B树的查询效率与键在B树中的位置有关，最大时间复杂度与B+树的相同(数据在叶子节点上)，最小时间复杂度为1(数据在根节点上)。

关于索引的建议

索引并不是越多越好，创建索引虽然会提高查询性能，但是会降低写入性能，因为在写入数据时需要维护索引数据，以保证所有索引都是最新的、最准确的。如果是唯一索引，为了保证唯一性，每次修改都会检查唯一行，在REPEATABLE READ隔离级别下，出现死锁的概率也会大大增加。同时，索引也会占用更多的物理存储，所以在创建索引时，一定权衡利弊，确保性能最大化。

以下几种情况需要创建索引：

经常用作查询条件的列
经常用于表连接的列
经常排序分组的列
业务上具有唯一性的字段添加成唯一索引
经常同时出现作为条件的列可以创建联合索引
如果经常有某个字段作为条件查另一个字段的情况，则考虑为这两个字段添加联合索引进行优化

索引的注意事项主要包括以下几点：

不在低基数列上创建索引，如性别字段
不在索引列进行数学运算和函数运算，因为对索引字段执行函数操作可能会导致无法使用索引
索引不宜过多，单表索引过多不仅占空间，还会影响数据修改的效率
在创建联合索引时，把选择性最大的列放在联合索引的最左边

MySQL创建索引的命令

1、创建表时添加索引

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    INDEX index_name (column1, column2, ...)
);

2、为现有表添加索引

CREATE INDEX index_name ON table_name (column1, column2, ...);

3、使用 `ALTER TABLE` 命令添加索引

ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);

4、创建唯一索引

CREATE UNIQUE INDEX unique_index_name ON table_name (column1, column2, ...);

5、创建主键索引

ALTER TABLE table_name ADD PRIMARY KEY (column1);

6、创建全文索引

CREATE FULLTEXT INDEX fulltext_index_name ON table_name (column1, column2, ...);

7、创建空间索引（适用于地理空间数据）

CREATE SPATIAL INDEX spatial_index_name ON table_name (spatial_column);

查看索引使用情况

explain 命令

explain 命令的输出结果包含多个列，每个列都提供了有关查询计划的不同信息，其中显示一些重要的列包括：

id：查询的标识符，用于区分不同的查询
select_type：查询的类型，例如简单查询、连接查询或子查询（SIMPLE：普通查询，即没有联合查询、子查询类型。其他还有 PRIMARY：主查询、UNION：UNION 中后面的查询、SUBQUERY：子查询等。）
table：查询涉及的表名。
type：访问类型，表示MySQL在表中查找数据的方式，例如全表扫描、索引扫描或范围扫描
1. 结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL
2. system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。
3. eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。
4. ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描。
5. range：索引范围扫描，比如，<，>，between 等操作。
6. index：索引全表扫描，此时遍历整个索引树。
7. ALL：表示全表扫描，需要遍历全表来找到对应的行。
possible_keys：MySQL可能使用的可用索引
key：实际使用的索引
key_len：使用的索引的长度
ref：显示了索引的哪一列被使用了
rows：MySQL认为必须检查的行数才能返回结果集
filtered：查找到所需记录占总扫描记录数的比例
Extra：包含额外的信息，例如是否使用了文件排序或临时表等