数据库中的索引

已于 2022-04-19 20:27:52 修改
阅读量800 收藏
点赞数
分类专栏： 数据库 文章标签： 数据库索引
于 2022-04-19 20:27:02 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jcf52/article/details/124267140
版权

目录

一. 思考

二.解决办法

三.索引是什么

四.索引目的

五.索引原理

 六.索引的使用

6.1查看索引

6.2创建索引

6.3删除索引

七. 索引示例

7.1创建测试表testindex

7.2使用python程序（ipython也可以）通过pymsql模块 向表中加入100条数据

7.3查询

 八.注意

一. 思考

在图书馆中是如何找到一本书的？

一般的应用系统对比数据库的读写比例在10:1左右(即有10次查询操作时有1次写的操作)，
而且插入操作和更新操作很少出现性能问题，
遇到最多、最容易出问题还是一些复杂的查询操作，所以查询语句的优化显然是重中之重

二.解决办法

当数据库中数据量很大时，查找数据会变得很慢 ：优化方案：索引

三.索引是什么

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

四.索引目的

索引的目的在于提高查询效率，可以类比字典

如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。

如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

五.索引原理

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样
的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。

 六.索引的使用

6.1查看索引

show index from 表名;
mysql> show index from school_student;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| school_student |          1 | s_ke     |            1 | age         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
参数说明
Table表示创建索引的数据表名
Non_unique表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。
Key_name表示索引的名称。
Seq_in_index表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。
Column_name表示定义索引的列字段。
Collation表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。
Cardinality索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。
Sub_part表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。
Packed指示关键字如何被压缩。若没有被压缩，值为 NULL。
Null用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。
Index_type显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。
Comment显示评注。


 

6.2创建索引

  • 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
  • 字段类型如果不是字符串，可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度))

例子：

mysql> create index s_ke on school_student(age);
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

6.3删除索引

drop index 索引名称 on 表名;

七. 索引示例

7.1创建测试表testindex

create table test_index(title varchar(10)); 
mysql> create table test_index(
    -> title varchar(10)
    -> );
Query OK, 0 rows affected (0.09 sec)

7.2使用python程序（ipython也可以）通过pymsql模块 向表中加入100条数据

from pymysql import *

 #创建connection连接
coon = connect(host='127.0.0.1',port=3306,
                   database='school',user='root'
                   ,password='mysql',charset='utf8')
#获得cursor对象
cursor = coon.cursor()
#执行sql语句
for i in range(100):
    cursor.execute("insert into test_index VALUES('hello-%d')"%i)
    # 提交数据
    coon.commit()
#4.关闭cursor
cursor.close()
#5.关闭connection
coon.close()

然后切换到终端：查看

mysql> select * from test_index;
+----------+
| title    |
+----------+
| hello-0  |
| hello-1  |
| hello-2  |
| hello-3  |
| hello-4  |
| hello-5  |
| hello-6  |
| hello-7  |
| hello-8  |
| hello-9  |
| hello-10 |
| hello-11 |
| hello-12 |
| hello-13 |
| hello-14 |
| hello-15 |
| hello-16 |
| hello-17 |
| hello-18 |
| hello-19 |
| hello-20 |
| hello-21 |
| hello-22 |
| hello-23 |
| hello-24 |
| hello-25 |
| hello-26 |
| hello-27 |
| hello-28 |
| hello-29 |
| hello-30 |
| hello-31 |
| hello-32 |
| hello-33 |
| hello-34 |
| hello-35 |
| hello-36 |
| hello-37 |
| hello-38 |
| hello-39 |
| hello-40 |
| hello-41 |
| hello-42 |
| hello-43 |
| hello-44 |
| hello-45 |
| hello-46 |
| hello-47 |
| hello-48 |
| hello-49 |
| hello-50 |
| hello-51 |
| hello-52 |
| hello-53 |
| hello-54 |
| hello-55 |
| hello-56 |
| hello-57 |
| hello-58 |
| hello-59 |
| hello-60 |
| hello-61 |
| hello-62 |
| hello-63 |
| hello-64 |
| hello-65 |
| hello-66 |
| hello-67 |
| hello-68 |
| hello-69 |
| hello-70 |
| hello-71 |
| hello-72 |
| hello-73 |
| hello-74 |
| hello-75 |
| hello-76 |
| hello-77 |
| hello-78 |
| hello-79 |
| hello-80 |
| hello-81 |
| hello-82 |
| hello-83 |
| hello-84 |
| hello-85 |
| hello-86 |
| hello-87 |
| hello-88 |
| hello-89 |
| hello-90 |
| hello-91 |
| hello-92 |
| hello-93 |
| hello-94 |
| hello-95 |
| hello-96 |
| hello-97 |
| hello-98 |
| hello-99 |
+----------+
100 rows in set (0.19 sec)

添加成功

7.3查询

  • 开启运行时间监测：
set profiling=1;
  • 查找第100条数据
select * from test_index where title='hello-99';
  •  查看执行的时间：
show profiles;
  •  为表title_index的title列创建索引：
create index title_index on test_index(title);
  • 执行查询语句：
select * from test_index where title='hello-99';
  • 再次查看执行的时间
show profiles;
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.43 sec)

mysql> select * from test_index where title='hello-99';
+----------+
| title    |
+----------+
| hello-99 |
+----------+
1 row in set (0.50 sec)

mysql> show profiles;
+----------+------------+-------------------------------------------------+
| Query_ID | Duration   | Query                                           |
+----------+------------+-------------------------------------------------+
|        1 | 0.40648700 | select * from test_index where title='hello-99' |
+----------+------------+-------------------------------------------------+
1 row in set, 1 warning (0.38 sec)
mysql> create index title_index on test_index(title);
Query OK, 0 rows affected (1.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_index where title='ha-99999';
Empty set (0.20 sec)

mysql> select * from test_index where title='hello-99';
+----------+
| title    |
+----------+
| hello-99 |
+----------+
1 row in set (0.18 sec)

mysql> show profiles;                    +----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.40648700 | select * from test_index where title='hello-99'   |
|        2 | 0.08342525 | create undex title_index on test_index(title(20)) |
|        3 | 0.34223600 | create index title_index on test_index(title(20)) |
|        4 | 1.17744275 | create index title_index on test_index(title)     |
|        5 | 0.20492250 | select * from test_index where title='ha-99999'   |
|        6 | 0.18099625 | select * from test_index where title='hello-99'   |
+----------+------------+---------------------------------------------------+
6 rows in set, 1 warning (0.08 sec)

mysql>

 建立索引大大的提高查询速率

 八.注意

要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对
于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对
于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。建立索引会占用磁盘空间

如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致 其他字段不需要写

回头科科
关注
专栏目录
数据库的索引
azhe12345690的博客
04-14 852
数据库的索引： 1、索引是什么：索引就类似于书的目录，是数据表所有记录的引用指针。 2、索引要解决的问题：避免过多的顺序查找，因为顺序查找要逐步进行查询，这样时间会很慢，有了索引就可以快速定位到需要的数据。这样可以加快查找的效率。 3、索引的应用场景：索引主要应用于查询较多，删除、更新、插入不频繁的工作中，因为删除、更新、插入需要同步设置调整索引，也会额外占用时间。 4、索引的数据结构：B+树 5、索引为什么不用哈希表：因为哈希表一般是判定相等或者不相等，无法解决大于、小于的情况。 6、为什么不用二叉树：比
数据库中索引的优缺点
12-14
在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。 　二、索引的特点 　1.索引可以加快数据库的检索速度 　2.索引降低了数据库插入、修改、删除等维护任务的速度 　3.索引创建在表...
Mysql的索引的目的及原理
念念不忘，必有回响。
04-27 2548
索引是什么 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。 更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度 索引原理 除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就...
数据库进阶篇 ：2.索引
最新发布
weixin_41822929的博客
08-27 1106
索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足 特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构 上实现高级查找算法，这种数据结构就是索引。MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持 情况。
面试知识点---数据库的索引
wei
10-15 2537
索引相关： 1.索引的分类：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。我们把这种内容本身就是一种按照一定规则排列的目录称为“聚集索引”。 我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。 由于聚集索引跟内容的存储顺序相关，因此一个表只能存在一个聚集索引。使
数据库进阶系列之一：漫谈数据库索引
weixin_33863087的博客
03-30 209
  一、引言 对数据库索引的关注从未淡出我的们的讨论，那么数据库索引是什么样的？聚集索引与非聚集索引有什么不同？希望本文对各位同仁有一定的帮助。有不少存疑的地方，诚心希望各位不吝赐教指正，共同进步。[最近首页之争沸沸扬扬，也不知道这个放在这合适么，苦劳？功劳？……]     二、B-Tree 我们常见的数据库系统，其索引使用的数据结构多是B-Tree或者B+Tree。例如，MsSql使...
关于ORACLE数据库中索引的几点讨论.pdf
10-10
本文主要探讨了在Oracle数据库中使用索引的一些关键点以及可能存在的误区。 首先，值得注意的是，索引并不总是查询的最佳选择。有时Oracle会选择全表扫描而非索引扫描。这可能是由于以下原因： 1. **统计信息不...
mysql 数据库中索引原理分析说明
09-11
在设计数据库索引时，应根据业务需求和查询模式来选择合适的索引类型，同时考虑到数据的分布特点、更新频率以及查询效率。合理的索引策略能显著提升数据库性能，减少全表扫描，提高用户体验。因此，深入理解索引原理...
面向对象数据库中索引实现技术.doc
06-09
面向对象数据库中索引实现技术
数据库索引的作用和创建
nqmysb的博客
11-06 932
索引是对数据库表中一列或者多列进行排序的一种结构（B树结构）balance –tree 不同与二叉树的是它具有多个子节点，而二叉树只有两个。通过这种索引他能都查询到数据库表中的特定内容，因为他内部进过某种算法的优化，所以他查询效率非常高。在数据量比较大的时候，利用好索引会给显著的提高查询效率。 索引有聚集索引和非聚集索引，聚集索引的物理顺序和逻辑顺序一致的，而非聚集索引的顺序的物理顺序是不同的。
MySQL为数据创建索引的目的_为数据表创建索引的目的是什么
weixin_32576389的博客
01-29 6589
一、SQL创建索引的目的如下：1、通过唯一性索引(unique)可确保数据的唯一性；2、加快数据的检索速度；3、加快表之间的连接；4、减少分组和排序时间；5、使用优化隐藏器提高系统性能。二、创建SQL索引的语法：CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED](索引类型) INDEX ON ( [ASC|DESC][, [ASC|DESC]...])。扩展资料：...
MySQL自学笔记2--索引
QilanAllen的博客
04-10 224
一、索引概述 MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据 库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数 据结构上实现高级查找算法，这种数据结构就是索引。如下面的示意图所示 : 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘...
mysql 数据库中索引的工作机制是什么
04-02
当我们对数据库进行查询时，MySQL会先检查索引，然后通过索引中的指针定位到实际的数据位置，从而加快查询的速度。 索引的工作机制是通过在数据库中预先建立一张表，这张表包含了需要查询的字段以及它们在数据库中...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值