MySQL 数据库索引

最新推荐文章于 2023-07-28 17:01:07 发布

张海玲

最新推荐文章于 2023-07-28 17:01:07 发布

阅读量666

点赞数

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

本文链接：https://blog.csdn.net/weixin_42014622/article/details/110292911

版权

数据库专栏收录该内容

11 篇文章 2 订阅

订阅专栏

文章目录

1.MySQL 索引介绍

MySQL索引的建立对于MySQL的高效运行是很重要的，合理设计且使用索引可以大大提高MySQL的检索速度。
打个比方，拿汉语字典的目录页（索引），我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引（但这不是组合索引）。组合索引，即一个索引包含多个列。

创建索引时，确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引优势：
①通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
②可以大大加快数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
③帮助服务器避免排序和临时表。
④将随机IO变为顺序IO
⑤可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

索引好处很多，但过多的使用索引将会造成滥用。
因此索引也会有其缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE操作。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，会降低SQL执行效率。
占用物理存储空间 ：索引需要使用物理文件存储，也会耗费一定空间。

2.常见索引结构

常见的索引结构有: B树， B+树和Hash索引。

2.1 B+树

B+树只有叶子节点存放 key 和 data，其他内节点只存放key。
B+树的叶子节点有一条引用链指向与它相邻的叶子节点.
B+树的检索效率很稳定，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
在这里插入图片描述

2.2 hash索引

Hash索引指的就是Hash表，最大的优点就是能够在很短的时间内，根据Hash函数定位到数据所在的位置，这是B+树所不能比的。

在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快。

有Hash冲突问题，不过对于数据库来说这还不算最大的缺点。
Hash索引不支持顺序和范围查询是它最大的缺点。
例如：

SELECT * FROM tb1 WHERE id < 500;

B+树是有序的，在这种范围查询中，优势非常大，直接遍历比500小的叶子节点就够了。而Hash索引是根据hash算法来定位的，难不成还要把 1 - 499的数据，每个都进行一次hash计算来定位吗?这就是Hash最大的缺点了。

3.索引分类

3.1普通索引(Index)

普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。
创建索引

这是最基本的索引，它没有任何限制。创建方式：

    CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

    ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

    CREATE TABLE mytable(  
     
    ID INT NOT NULL,   
     
    username VARCHAR(16) NOT NULL,  
     
    INDEX [indexName] (username(length))  
     
    );

删除索引

    DROP INDEX [indexName] ON mytable;

3.2唯一索引(Unique Key)

建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。一张表允许创建多个唯一索引。
与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

    CREATE TABLE mytable(  
     
    ID INT NOT NULL,   
     
    username VARCHAR(16) NOT NULL,  
     
    UNIQUE [indexName] (username(length))  
     
    );

使用ALTER 命令添加和删除索引

添加数据表的索引（四种方式）

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

在表中添加索引实例。

    mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

在 ALTER 命令中使用 DROP 子句来删除索引。

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

    mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
    mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

也可以使用 ALTER 命令删除主键

3.3前缀索引(Prefix)

前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。

3.4全文索引（Full Text）

全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6之前只有MYISAM引擎支持全文索引，5.6之后InnoDB也支持了全文索引。

4.索引创建原则

4.1合适字段

不为null的字段
被频繁查询的字段
被作为条件查询的字段
被频繁用于连接的字段
经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

4.2 不合适创建索引的字段

被频繁更新的字段应该慎重建立索引
不被经常查询的字段没有必要建立索引
尽可能地考虑建立联合索引而不是单列索引
尽量避免冗余索引
考虑在字符串类型的字段上建立前缀索引代替普通索引
前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

5.MySQL数据库索引优化策略

①索引列上不能使用表达式或者函数

②联合索引，如何选择索引的顺序

经常会被使用到的列优先
选择性高的列优先
宽度小的列优先

③索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为NULL。

④索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

⑤like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。
like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。

⑥mySQL只对以下操作符才使用索引

<、<=、=、>、>=、between、in以及某些时候的like(不以通配符%或_开头的情形)。理论上每张表里面最多可创建16个索引。

⑦合理使用联合索引的最左前缀原则
假设创建的联合索引由多个字段组成:

ALTER TABLE table ADD INDEX index_name (num,name,age)

那么当查询的条件有为:num / (num AND name) / (num AND name AND age)时，索引才生效。所以在创建联合索引时，尽量把查询最频繁的那个字段作为最左(第一个)字段。查询的时候也尽量以这个字段为第一条件。

参考：
1.IT楠老师教学视频 https://space.bilibili.com/384087053/
2.https://gitee.com/SnailClimb/JavaGuide/
写在最后：
谢谢面试官认可我写博客，我会继续加油哒~

张海玲

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL 数据库索引

1.MySQL 索引介绍MySQL索引的建立对于MySQL的高效运行是很重要的，合理设计且使用索引可以大大提高MySQL的检索速度。打个比方，拿汉语字典的目录页（索引），我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引（但这不是组合索引）。组合索引，即一个索引包含多个列。创建索引时，确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保
复制链接

扫一扫