首页 > 数据库 > MySQL > 正文

MySQL索引与事务解说

2024-07-24 12:35:56

字体：大中小

来源：转载

供稿：网友

　　MySQL索引与事务详解
　　一、前言
　　上一章我们讲解了MySQL的手工编译安装流程以及相关的数据库操作命令（sql语句），本文将要详细介绍MySQL索引与事务的概念及原理，并初步了解MySQL数据库视图概念，简述数据库的存储过程。

　　二、索引
　　2.1索引的概念——什么是索引?
　　一般来说，一篇论文，或者说一本书，都有其目录，而目录一般是所有章节的概述，或者说是要点核心，而索引的概念其实也与之类似。

　　索引，顾名思义，就是一个方便用户搜索所需资源的引导，只不过在数据库中，索引一般被认为是一种特殊的文件，尤其在Linux系统中（"一切皆文件"）。从专业术语上解释其含义就是“代表记录的引用指针”。

　　2.2索引的作用
　　加快查询速度，提高工作效率；
　　降低i/o成本，提供基础排序；
　　加快多表之间的连接；
　　支持唯一性索引（下面会介绍什么是唯一性索引），保证数据表中数据的唯一性性；
　　2.3索引的分类
　　2.3.1普通索引
　　就是一般的索引，只是为了区别于其他特殊索引的一个统称

　　2.3.2唯一性索引
　　与普通索引基本类同，区别在于，唯一性索引的列中的所有值都不相同，即“唯一”。

　　简单举例来说，学生数据表，年龄可以是普通索引，但不可以是唯一性索引，但是详细住址可以是。

　　2.3.3主键索引
　　本质上也是一种唯一性索引，但必须指定为“primary key”，该索引要求主键中的每个值都唯一。上篇文章中，我们提及到了主键的概念，其特点也是“非空唯一”。

　　2.3.4全文索引
　　索引类型为FULLTEXT，全文索引可以在char、vachar或者text类型的列上创建。

　　2.3.5单列索引与多列索引
　　可以在单列或多列上创建索引。多列索引一般用于区分其中一列可能有相同值的行。

　　2.4创建索引的原则：建立在表上的（索引依赖于表）
　　索引可以提升数据库的查询速度，但并非所有的数据表都需要创建索引。因为索引本身也是需要占用系统资源的，或许一般情况下这个问题不会很突出，因为服务器的资源在一定程度上还是能够正常支持的，但是如果索引文件过大，其大小可能达到操作系统允许的最大文件限制。

　　并且，如果说索引使用不当也会造成数据库的负担。因此，数据库创建索引也是有其原则的。

　　2.4.1创建索引的原则依据
　　表的主键、外键必须有索引；
　　达到一定量的表（300行记录）应该有索引；
　　表之间的连接字段上应该建立索引；
　　不能使用唯一性太差的字段作为索引；
　　更新频繁的字段也不适合作为索引；
　　小字段适合建立索引，长的字段则不适合建立索引；
　　2.5索引的优缺点
　　优点：快速查询所需资源

　　缺点：占用空间以及资源

　　2.6创建及查看索引
　　首先我们需要保证数据库中有表，且表内有数据；

　　mysql> select * from fruit_info;
　　+----+-------+---------+
　　| id | price | newtype |
　　+----+-------+---------+
　　| 1 | 2.50 | banana |
　　| 2 | 5.50 | apple   |
　　| 3 | 6.00 | peach   |
　　+----+-------+---------+
　　3 rows in set (0.00 sec)
　　2.6.1创建普通索引
　　命令格式：create index <索引名> on 数据表（列名）；

　　实例：

　　mysql> create index id_index on fruit_info(id);
　　Query OK, 0 rows affected (0.00 sec)
　　Records: 0 Duplicates: 0 Warnings: 0
　　mysql> show index from fruit_info; #查看索引语句也可以将index换成“keys”
　　+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
　　| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
　　+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
　　| fruit_info |          1 | id_index |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
　　+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
　　1 row in set (0.00 sec)
　　其中Non_unique为1，表示不是唯一性索引；Key_name 对应的是索引名称，这里就是id_index；

　　2.6.2创建唯一性索引
　　命令格式：create unique index <索引名称> on 数据表（列名）；

　　实例：

　　mysql> create unique index type_index on fruit_info(newtype);
　　Query OK, 0 rows affected (0.01 sec)
　　Records: 0 Duplicates: 0 Warnings: 0

　　mysql> show index from fruit_info;
　　+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
　　| Table      | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
　　+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
　　| fruit_info |          0 | type_index |            1 | newtype     | A         |           3 |     NULL | NULL   | YES | BTREE      |         |               |
　　| fruit_info |          1 | id_index   |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
　　+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
　　2 rows in set (0.00 sec)
　　这里的索引名称为type_index，与之对应的Non_unique的值为0，表示其为唯一性索引。并且唯一性索引的值都不一样。

　　2.6.3创建主键索引
　　命令格式:（1）创建表的时候创建主键：create table 表名（[ ... ]，primary key(列的列表)）；

　　（2）修改表结构加入主键：alter table 表名 add primary key；

　　实例：

　　mysql> create table student (id int not null,sex char(2),age int not null,hobby varchar(20),primary key(id,hobby));
　　Query OK, 0 rows affected (0.01 sec)

　　mysql> show index from student;
　　+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
　　| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
　　+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
　　| student |          0 | PRIMARY |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
　　| student |          0 | PRIMARY |            2 | hobby       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
　　+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
　　2 rows in set (0.01 sec)
　　一个表里面只能有一个主键，但一个主键可以由多个字段组成。

（编辑：武林网）

上一篇：如何利用mysql条件查询and和or及其优先级

下一篇：MySQL日期类别

学习交流

硬盘分区如何设置准确的分区空间

硬盘分区如何设置准确的分区空间...

热门图片

猜你喜欢的新闻

猜你喜欢的关注

新闻热点

最牛同桌！我考了696分我同桌考了703分

2024-06-26 22:28:41

650分！高二女生考入北大：遗憾不能上高三

2024-06-26 22:26:16

男生估分600只考了397 妈妈：高考虽重要，但不代表所有

2024-06-26 22:23:01

唐尚珺回应是否会直播带货：有人出100万想和他合作！

2024-06-25 19:29:23

名校抢人名场面：清华、北大太拼了！

2024-06-25 19:22:14

男生高考语文满分！网友：第一次听说

2024-06-25 19:19:15

疑难解答

图片精选

网友关注