本站首页 收藏本站
广告位联系

APP正在开发中...
返回顶部
您的位置： 主页 > 数据库 > Mysql >

收藏
分享到

MySql索引原理与操作

Mysql 来源：互联网 作者：佚名 发布时间：2022-09-18 19:40:11 人浏览
摘要

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。 一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。 索引相当于一本书的目录

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。

一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。

索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

索引相当于一本书的目录

通过索引查询的方式被称为索引查询。

在 mysql 数据库当索引也是需要排序的，并且这个索引的排序和 TreeSet 数据结构相同。TreeSet（TreeMap）底层是一个自平衡二叉树！在 mysql 当中索引是一个 B-Tree 数据结构。

遵循左小右大原则存放。采用中序遍历方式遍历取数据。

2. 索引的实现原理

提醒：

在任何数据库中主键上都会自动添加索引对象，id 字段上自动有索引，因为 id 是主键。另外在 mysql 中，如果一个字段有 unique 约束的话，也会自动创建索引对象。

任何数据库中，然后一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。在 mysql 中，索引是一个单独的对象，在不同的存储引擎以不同的形式存在，

在 MyISAM 存储引擎中，索引存储在一个 .MYI 文件中。在 InnoDB 存储引擎中，索引存储在一个逻辑名叫做 tablespace 的当中。在 MEMORY 存储引擎中，索引存储在内存中。不管索引存储在哪里，索引在 mysql 中都是一个树的形式存在。

3. 添加索引的条件

  • 数据量庞大（具体多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
  • 该字段经常出现在 where 的后面，以条件的形式存在，也就是说这个字段总是被扫描。
  • 该字段很少的 DML（insert、delete、update）操作。（因为 DML 之后，索引需要重新排序）

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。

建议通过主键查询，建议通过 unique 约束的字段进行查询，效率是比较高的。

4. 索引的操作

1. 创建索引

给 emp 表的 ename 字段添加索引，起名：emp_ename_index

1

mysql> create index emp_ename_index on emp(ename);

2. 删除索引

将 emp 表上的 emp_ename_index 索引对象删除

1

mysql> drop index emp_ename_index on emp;

3. 查看一个sql语句是否使用了索引进行检索

1

2

3

4

5

6

7

mysql> explain select * from emp where ename = 'KING';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.01 sec)

扫描14条记录：说明没有使用索引。type = ALL

1

2

3

4

5

6

7

8

9

10

mysql> create index emp_ename_index on emp(ename);

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from emp where ename = 'KING';

+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |

+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+

|  1 | SIMPLE      | emp   | NULL       | ref  | emp_ename_index | emp_ename_index | 43      | const |    1 |   100.00 | NULL  |

+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

5. 索引的失效

失效的第1种情况：

1

mysql> select * from emp where ename like '%T';

ename 上即使添加了索引，也不会走索引进行查询，为什么？

因为模糊查询匹配中以 “%” 开头了。

尽量避免模糊查询的时候以 “%” 开始。

这是一种优化的手段。

1

2

3

4

5

6

7

mysql> explain select * from emp where ename like '%T';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    11.11 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.01 sec)

失效的第2种情况：

使用 or 的时候会失效，如果使用 or 那么要求 or 两边的条件字段都要有索引，才会进行索引查询，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会实现。索引这就是为什么不建议使用 or 的原因。

1

2

3

4

5

6

7

mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER';

+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | emp   | NULL       | ALL  | emp_ename_index | NULL | NULL    | NULL |   14 |    16.43 | Using where |

+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

失效的第3种情况：

使用复合索引的时候，没有使用左侧的列查找，索引失效。

什么是复合索引？

两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

mysql> explain select * from emp where job = 'MANAGER';

+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |

+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+

|  1 | SIMPLE      | emp   | NULL       | ref  | emp_job_sal_index | emp_job_sal_index | 39      | const |    3 |   100.00 | NULL  |

+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

 

mysql> explain select * from emp where sal = 800;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

失效的第4种情况：

在 where 中索引列参加了运算，索引失效。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

mysql> create index emp_sal_index on emp(sal);

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from emp where sal = 800;

+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |

+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

 

mysql> explain select * from emp where sal + 1 = 800;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.01 sec)

失效的第5种情况：

在 where 中索引列使用了函数

1

2

3

4

5

6

7

mysql> explain select * from emp where lower(ename) = 'smith';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.01 sec)

6. 索引的类型

  1. 单一索引：一个字段上添加索引
  2. 复合索引：两个或多个字段上添加索引
  3. 主键索引：主键上添加索引
  4. 唯一性索引：具有 unique 约束的字段上添加索引

注意： 唯一性比较弱的字段上添加索引用处不大。

相对来说，唯一性越高，效率越高。
版权声明 : 本文内容来源于互联网或用户自行发布贡献，该文观点仅代表原作者本人。本站仅提供信息存储空间服务和不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权， 违法违规的内容， 请发送邮件至2530232025#qq.cn(#换@)举报，一经查实，本站将立刻删除。

您可能感兴趣的文章 :

原文链接 : https://blog.csdn.net/qq_52354698/article/details/126680073
相关文章
  • 深入了解MySQL中的慢查询
    一、什么是慢查询 什么是MySQL慢查询呢？其实就是查询的SQL语句耗费较长的时间。 具体耗费多久算慢查询呢？这其实因人而异，有些公司慢

  • MySQL中with rollup的用法及说明

    MySQL中with rollup的用法及说明
    MySQL with rollup的用法 当需要对数据库数据进行分类统计的时候，往往会用上groupby进行分组。 而在groupby后面还可以加入withcube和withrollup等关

  • mysql分组统计并求出百分比的方法

    mysql分组统计并求出百分比的方法
    mysql分组统计并求出百分比 1、mysql 分组统计并列出百分比 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 SELECT point_id, pname_cn, play_
  • 30种SQL语句优化的方法总结
    1）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2）应尽量避免在 where 子句中使用!=或操作符

  • 达梦数据库获取SQL实际执行计划的方法

    达梦数据库获取SQL实际执行计划的方法
    环境说明： 操作系统：银河麒麟V10 数据库：DM8 相关关键字：DM数据库、SQL实际执行计划 一、set autotrace trace disql下执行set autotrace trace开启

  • MySQL数据库约束的介绍

    MySQL数据库约束的介绍
    基本介绍 约束用于确保数据库的数据满足特定的商业规则 在mysql中，约束包括：not null，unique，primary key，foreign key 和check5种 1.primary key(主键

  • MySQL索引的介绍

    MySQL索引的介绍
    1. MySQL 索引的最左前缀原则 左前缀原则是联合索引在使用时要遵循的原则，查询索引可以使用联合索引的一部分，但是必须从最左侧开始。
  • windows下Mysql多实例部署的操作方法
    当存在多个项目的时候，需要同时部署时，且只有一台服务器时，哪么就需要部署Mysql多个实例，原理很简单，多个mysql服务运行使用不同的

  • MySQL客户端/服务器运行架构介绍

    MySQL客户端/服务器运行架构介绍
    之前对MySQL的认知只限于会写些SQL，本篇开始进行对MySQL进行深入的学习，记录和整理下自己对MySQL不熟悉的地方。如果有需要可以关注我的

  • mysql8.0主从复制搭建与配置方案

    mysql8.0主从复制搭建与配置方案
    mysql主从搭建 环境：ubuntu20.04.1，mysql：8.0.22。 主：192.168.87.3 备：192.168.87.6 安装数据库 1 2 3 sudo apt-get install mysql-server sudo apt-get install mysql
推荐阅读
最新更新
友情链接
  • 本站所有内容来源于互联网或用户自行发布，本站仅提供信息存储空间服务，不拥有版权，不承担法律责任。如有侵犯您的权益，请您联系站长处理！
  • Copyright © 2017-2022 F11.CN All Rights Reserved. F11站长开发者网 版权所有 | 苏ICP备2022031554号-1 | 51LA统计