数据库索引

最新推荐文章于 2022-04-01 10:56:06 发布
最新推荐文章于 2022-04-01 10:56:06 发布
阅读量243 收藏 3
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/manchengpiaoxue/article/details/88015487
版权

一、数据库索引概述；

二、数据库索引分类；

三、Mysql数据库管理索引；

四、数据库事务概述；

五、数据库事务特性；

六、Mysql数据库管理事务；

 

 

一、数据库索引概述；

概述：索引是一种特殊的文件，包含着对数据表中所有记录的引用指针，如书本的目录类似，能够加快数据库的查询速度，更高效率的管理数据库；

作用：设置合适的索引后，数据库利用各种定位设置，可以大大的增加数据的查询速度；

           当表很大时，或者查询涉及到多个表时，使用索引可以加快查询速度甚至上千倍；

         可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本；

           通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

缺点：增加了数据库的存储空间；

           在插入和修改数据的同时会增加更多的时间，因为索引也要一同变化；

依据：表的主键必须有索引，主键索引中的值时唯一的；

           数据量超过300行的表应该有索引，不然会眼中影响数据库的查询性能；

           唯一性太差的字段不适合建立索引，比如一列中的1000行都是同一个字段；

           经常与其他表进行交互的表，其连接列中必须设置索引；

           更新太过于频发的字段不建议设置为索引列；

           经常出现在sql语句中的where中，建议设置为索引列；

         索引建议设置在小字段上，较大的字段上不建议设置为索引；

         

二、数据库索引分类；

普通索引：最基本的索引，无唯一性之类的限制；

唯一性索引：索引列中的所有值只能出现一次，如身份证号码列；

主键索引：是一种唯一性夺银，在一个表中创建主键后，则自动创建主键索引；

单列索引和多列索引：可以为单列或者多列，列中可能有重复的值，例如表中的性别列；

 

三、Mysql数据库管理索引；

准备数据库和表：

mysql> create database linuxfan;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

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

| Database           |

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

| information_schema |

| linuxfan           |

| mysql              |

| performance_schema |

| sys                |

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

mysql> use linuxfan;

Database changed

mysql> create table it(岗位 char(16),姓名 char(16),身份证号 char(48),学历 char(16),工资 int);

Query OK, 0 rows affected (0.00 sec)

mysql> insert into it values('网络工程师','张三','150404199201091132','高中','5500');

Query OK, 1 row affected (0.00 sec)

mysql> insert into it values('网络工程师','李四','150406187805271432','专科','12000');

Query OK, 1 row affected (0.00 sec)

mysql> insert into it values('java工程师','王五','111376199609101456','初中','10000');

Query OK, 1 row affected (0.00 sec)

mysql> insert into it values('网络工程师','刘备','345646199209103256','高中','15000');

Query OK, 1 row affected (0.00 sec)

mysql> insert into it values('ui工程师','关羽','178645199909101189','本科','7000');

Query OK, 1 row affected (0.00 sec)

mysql> select * from it;

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

| 岗位            | 姓名   | 身份证号           | 学历   | 工资   |

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

| 网络工程师      | 张三   | 150404199201091132 | 高中   |   5500 |

| 网络工程师      | 李四   | 150406187805271432 | 专科   |  12000 |

| java工程师      | 王五   | 111376199609101456 | 初中   |  10000 |

| 网络工程师      | 刘备   | 345646199209103256 | 高中   |  15000 |

| ui工程师        | 关羽   | 178645199909101189 | 本科   |   7000 |

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

 

普通索引语法：create index 索引名称  on 表名(列名);

mysql> create index putong on it(工资);

Query OK, 5 rows affected (0.00 sec)

mysql> show index from it;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| it    |          1 | putong   |            1 | 工资        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |

 

唯一性索引语法：create unique index  索引名称  on 表名(列名);

mysql> create unique index weiyi on it(身份证号);

Query OK, 5 rows affected (0.00 sec)

mysql> show index from it;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| it    |          0 | weiyi    |            1 | 身份证号     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |

| it    |          1 | putong   |            1 | 工资         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |

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

mysql> insert into it values('网络工程师','张飞','150404199201091132','小学','7800');

ERROR 1062 (23000): Duplicate entry '150404199201091132' for key 'weiyi'

mysql> select * from it;

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

| 岗位            | 姓名   | 身份证号           | 学历   | 工资   |

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

| 网络工程师      | 张三   | 150404199201091132 | 高中   |   5500 |

| 网络工程师      | 李四   | 150406187805271432 | 专科   |  12000 |

| java工程师      | 王五   | 111376199609101456 | 初中   |  10000 |

| 网络工程师      | 刘备   | 345646199209103256 | 高中   |  15000 |

| ui工程师        | 关羽   | 178645199909101189 | 本科   |   7000 |

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

 

创建主键：create table 表名  (字段一,字段二,primary key(列名));

mysql> create table it2 (name char(16),id int,PRIMARY KEY (name));

Query OK, 0 rows affected (0.01 sec)

 

多列索引：create index 索引名称  on 表名(列名一,列名二);

mysql> create index duolie on it(工资,学历);

Query OK, 5 rows affected (0.01 sec)

mysql> show index from it;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| it    |          0 | weiyi    |            1 | 身份证号     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |

| it    |          1 | putong   |            1 | 工资         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |

| it    |          1 | duolie   |            1 | 工资         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |

| it    |          1 | duolie   |            2 | 学历         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |

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

 

查看索引：show index from 表名;

 

四、数据库事务概述；

概述：事务是一种机制，一个操作序列，包含了一组数据库操作指令，并且将多个操作指令当作一个整体，一同向系统提交或者回滚的操作，事务保证了数据的一致性，如支付宝中转账的操作，转账的账户和收钱的账户，两个操作组成一个完整的事务；

 

五、数据库事务特性；

原子性：事务是一个完整的操作，数据必须处于一致的状态，事务中的操作，要么全部成功，要么全部失败，此特性就是原子性；

一致性：当事务执行完毕后，数据必须处于一致状态，在开始事务时数据保持一致状态，执行事务的过程中事务可以出现不一致的情况，事务完毕后，数据必须保证一致状态；

隔离性：多个事务之间互不依靠，互补影响，相对独立；

持久性：事务处理完毕后，在系统中发生的变化是永久性的，会永久的保留在数据库中；

 

六、Mysql数据库管理事务；

管理事务的三个命令：

begin：开始事务，后边有多条数据库操作语句开始执行；

commit：开始提交一个事务，对应前边的begin操作，将事务处理的结果保存到数据文件中；

rollback：开始回滚一个事务，在begin和commit之间，将事务中的全部语句撤回，恢复到执行begin之前的数据状态；

 

管理事务：

mysql> use linuxfan;

Database changed

mysql> show table status where name="it";                    ##确保it表存储引擎为InnoDB，如若不是按以下命令修改；

mysql> alter table it engine=InnoDB;

mysql> select * from it;

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

| 岗位            | 姓名   | 身份证号           | 学历   | 工资   |

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

| 网络工程师      | 张三   | 150404199201091132 | 高中   |   5500 |

| 网络工程师      | 李四   | 150406187805271432 | 专科   |  12000 |

| java工程师      | 王五   | 111376199609101456 | 初中   |  10000 |

| 网络工程师      | 刘备   | 345646199209103256 | 高中   |  15000 |

| ui工程师        | 关羽   | 178645199909101189 | 本科   |   7000 |

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

mysql> begin;                                                 ##开始事务

Query OK, 0 rows affected (0.00 sec)

mysql> insert into it values('php工程师','曹操','891878195409081198','本科','9000');

Query OK, 1 row affected (0.00 sec)

mysql> commit;                                            ##提交事务

Query OK, 0 rows affected (0.00 sec)

mysql> select * from it;

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

| 岗位            | 姓名   | 身份证号           | 学历   | 工资   |

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

| 网络工程师      | 张三   | 150404199201091132 | 高中   |   5500 |

| 网络工程师      | 李四   | 150406187805271432 | 专科   |  12000 |

| java工程师      | 王五   | 111376199609101456 | 初中   |  10000 |

| 网络工程师      | 刘备   | 345646199209103256 | 高中   |  15000 |

| ui工程师        | 关羽   | 178645199909101189 | 本科   |   7000 |

| php工程师       | 曹操   | 891878195409081198 | 本科   |   9000 |

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

6 rows in set (0.00 sec)

 

mysql> begin;                                                 ##开始事务

Query OK, 0 rows affected (0.00 sec)

mysql> insert into it values('php工程师','赵云','987867199012081198','专科','9500');

Query OK, 1 row affected (0.00 sec)

mysql> select * from it;                               ##未手动提交事务，但仍然能够看到数据，因为在当前已经开启的事务中可以看见，退出连接后，则自动提交事务

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

| 岗位            | 姓名   | 身份证号           | 学历   | 工资   |

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

| 网络工程师      | 张三   | 150404199201091132 | 高中   |   5500 |

| 网络工程师      | 李四   | 150406187805271432 | 专科   |  12000 |

| java工程师      | 王五   | 111376199609101456 | 初中   |  10000 |

| 网络工程师      | 刘备   | 345646199209103256 | 高中   |  15000 |

| ui工程师        | 关羽   | 178645199909101189 | 本科   |   7000 |

| php工程师       | 曹操   | 891878195409081198 | 本科   |   9000 |

| php工程师       | 赵云   | 987867199012081198 | 专科   |   9500 |

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

mysql> exit

Bye

[root@lwh ~]# mysql -uroot -p123456

mysql> use linuxfan;

Database changed

mysql> select * from it;

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

| 岗位            | 姓名   | 身份证号           | 学历   | 工资   |

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

| 网络工程师      | 张三   | 150404199201091132 | 高中   |   5500 |

| 网络工程师      | 李四   | 150406187805271432 | 专科   |  12000 |

| java工程师      | 王五   | 111376199609101456 | 初中   |  10000 |

| 网络工程师      | 刘备   | 345646199209103256 | 高中   |  15000 |

| ui工程师        | 关羽   | 178645199909101189 | 本科   |   7000 |

| php工程师       | 曹操   | 891878195409081198 | 本科   |   9000 |

| php工程师       | 赵云   | 987867199012081198 | 专科   |   9500 |

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

 

mysql> set autocommit = 0;                                                  ##设置禁用自动提交事务

Query OK, 0 rows affected (0.00 sec)

mysql> begin;                                                                             ##开始事务

Query OK, 0 rows affected (0.00 sec)

mysql> insert into it values('nginx工程师','诸葛亮','187967198712081198','专科','6700');

Query OK, 1 row affected (0.00 sec)

mysql> rollback;                                                                        ##回滚事务

Query OK, 0 rows affected (0.00 sec)

mysql> select * from it;                                                           ##查询已经无数据记录

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

| 岗位            | 姓名   | 身份证号           | 学历   | 工资   |

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

| 网络工程师      | 张三   | 150404199201091132 | 高中   |   5500 |

| 网络工程师      | 李四   | 150406187805271432 | 专科   |  12000 |

| java工程师      | 王五   | 111376199609101456 | 初中   |  10000 |

| 网络工程师      | 刘备   | 345646199209103256 | 高中   |  15000 |

| ui工程师        | 关羽   | 178645199909101189 | 本科   |   7000 |

| php工程师       | 曹操   | 891878195409081198 | 本科   |   9000 |

| php工程师       | 赵云   | 987867199012081198 | 专科   |   9500 |

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

RoderRick
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MYSQL——索引设计原则
庄小焱
12-28 1435
之前优化器选择使用索引 b，是因为它认为使用索引 b 可以避免排序（b 本身是索引，已经是有序的了，如果选择索引 b 的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为代价更小。但是，对于这个查询语句来说，如果你定义的 index2 不是 email(6) 而是 email(7），也就是说取 email 字段的前 7 个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到 ID2，只扫描一行就结束了。其中，Q1 的结果还是符合预期的，rows 的值是 104620；
MySQL如何优化身份证字段的索引？
学亮编程手记
11-26 741
创建前缀索引：身份证号码的前缀通常具有较高的选择性，因此可以考虑创建前缀索引。例如，如果身份证号码的前缀通常是固定的前几位，可以创建一个前缀索引来覆盖这些前缀。维护索引的选择性：索引的选择性是指索引上不同值的数量与总行数的比例。使用哈希索引：如果身份证号码的长度较长，且查询中经常使用完整的身份证号码进行匹配，可以考虑使用哈希索引。根据表的大小、数据分布和查询需求等因素，选择适当的索引策略，并进行测试和评估，以确定最佳的优化方案。避免过度索引：过多的索引可能会降低写操作的性能，并增加索引的维护成本。
给字符串字段加索引
lianggx3的博客
04-28 266
如果我们要给身份证作索引，身份证号码太长了，那么做索引会花费很大的空间。 Mysql是支持前缀索引的。比如，这两个在email字段上创建索引的语句： mysql> alter table SUser add index index1(email); mysql> alter table SUser add index index2(email(6)); 看看以下两个索引结构，分别是 接下来看看语句的执行。 select id,name,email from SUser whe
MySQL实战45讲-09 | 普通索引和唯一索引，应该怎么选择？
dark
04-01 313
极客学院-《MySQL实战45讲》 学习笔记
如何给特殊字符串加索引：如身份证、邮箱等
weixin_39035120的博客
05-14 3745
1 建表语句 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `id_card` varchar(18) NOT NULL, `email` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB INSERT INTO `test`.`user`(`id`, `id_card`, `email`) VALUES (1, '32102319930316..
[MySQL]Mysql自己怎么选取索引，怎么给字符串字段加上索引？(涉及到身份证怎么加索引)
新一的技术笔记
12-22 2362
目录 前言： 1. 实例分析 1.1 如何创建循环函数 1.2 进行查询操作 2. 索引选择 2.1 优化器 2.2扫描行数是怎么判断的？ 2.3 Mysql怎么得到索引的基数的呢？ 2.4 选错索引的原因？ 2.4.1 怎么看预计扫描的行数？ 2.4.2 为什么选错了索引？ 2.4.3 如果表的统计数据不准确，使用analyze table t 3. 选错索引，处理方...
数据库索引的作用和优点缺点
12-14
什么是索引 　拿汉语字典的目录页（索引）打比方：正如汉语字典中的汉字按页存放一样，SQL Server中的数据记录也是按页存放的，每页容量一般为4K 。为了加快查找的速度，汉语字（词）典一般都有按拼音、笔画、偏旁...
数据库索引设计和优化
06-24
数据库索引设计与优化是数据库管理系统中至关重要的一个环节，它直接影响到数据查询的效率、存储空间的使用以及系统的整体性能。在这个主题中，我们将深入探讨数据库索引的基础概念、设计原则、优化策略以及实际应用...
数据库索引的实现原理
12-14
数据库索引是数据库管理系统中用于加速数据查询的关键技术。它是一种特殊的数据结构，通常基于B树或其变种B+树实现，目的是提供快速的数据定位，使得查询操作可以在较短的时间内完成。索引并不只是单纯地存储数据，...
数据库 索引
12-14
文章目录数据库 索引1、概述2、索引的种类3、索引的底层实现原理3.1 索引的基础知识3.1 索引提高检索速度3.3 哈希索引4、聚集索引与非聚集索引4.1 聚集索引4.2 非聚集索引4.3 覆盖索引5、索引的最左分配原则6、总结 ...
数据库索引和分类
12-14
数据库索引是数据库管理系统中用于加速数据检索的一种数据结构，主要目的是提高查询速度。在日常开发中，数据库索引的应用非常广泛，对于处理大量数据的高效查询至关重要。根据描述，数据库索引主要有以下几种类型：...
性别字段建立索引问题
热门推荐
p571912102的博客
09-25 6万+
性别字段能不能建立索引 之前面试被问到一个问题 什么字段适合建索引，什么字段不适合建索引。 性别字段可以建索引吗？ 我回答得不是很好。 性别字段这种重复性很强的字段，不要建立索引。为什么不能呢？ 下面建立表测试下。如：我建了个tb_test表，往里面插入了七百多万数据，先给性别建立索引，查询开启 sql运行时间记录。 创建名为tb_index_test  的索引 alter ta...
oracle 建立索引常用的规则
一切都是浮云
01-05 2132
1、表的主键、外键必须有索引； 2、数据量超过300的表应该有索引； 3、经常与其他表进行连接的表，在连接字段上应该建立索引； 4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； 5、索引应该建在选择性高的字段上； 6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； 7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替： A、正确选...
MySQL-索引
weixin_40805079的博客
02-14 176
在下面这个表T中，如果我执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？ 下面是这个表的初始化语句。 mysql> create table T ( ID int primary key, k int NOT NULL DEFAULT 0, s varchar(16) NOT NULL DEFAULT '', index k(k)) engine=InnoDB; insert into T values(100,1, '
MySQL索引原理以及查询优化
weixin_34377919的博客
10-30 1457
一、介绍 1.什么是索引？ 一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。 2.为什么要有索引呢？ 索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是...
my share source
最新发布
08-15
my share source
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值