Linux——Mysql索引和事务

目录

一，Mysql索引介绍

1，索引概述

1，索引的优点

2，索引的缺点

2，索引作用

3，索引分类

普通索引

唯一索引

主键索引

组合索引

全文索引

4，查看索引

5，删除索引

6，索引的应用场景

7，不适合使用索引的场景

二，Mysql事务

1，事务的四大特性(ACID)

2，事务控制语句

3，mysql事务的隔离级别

 4，使用mysql事务

一，Mysql索引介绍

1，索引概述

MySQL 索引是一种用于提高数据库查询效率的数据结构，

• 就像书籍的目录一样，能帮助数据库快速定位到所需的数据，而不必全表扫描。例如，在一个包含大量用户信息的表中，如果经常要根据用户姓名来查询记录，那么为 “姓名” 字段建立索引后，查询速度会显著提升。
• 原理：索引通常是基于 B 树（B - Tree）或哈希表等数据结构来实现的。以 B 树为例，它将数据按照一定的规则组织成树形结构，节点中的数据是有序排列的。当进行查询时，数据库可以通过比较查询值与节点中的数据，快速决定是在当前节点的左子树还是右子树继续查找，从而大大减少了查找的范围和时间。

1，索引的优点

(1)，提高查询速度

• 索引可以显著加快数据检索速度，特别是对大表查询
• 类似于书籍的目录，可以快速定位到所需数据

(2)，加速表连接

• 在多表连接操作时，索引能极大提高连接效率

(3)，保证数据唯一性

• 唯一索引可以确保列中数据的唯一性

(4)，优化排序和分组

• 对索引列进行ORDER BY或GROUP BY操作时效率更高

(5)，减少服务器扫描1数据量

• 数据库引擎可以跳过不必要的数据行

2，索引的缺点

(1)：占用存储空间

• 索引需要额外的磁盘空间存储
• 对于大型表，索引可能占用相当可观的存储

(2)：减低写入性能

• 插入、更新和删除操作需要同时维护索引
• 每次数据修改都可能需要更新多个索引

(3)：维护成本

• 索引需要定期维护以保持高效
• 随着数据变化，索引可能变得碎片化

(4)：优化器选择问题

• 有时查询优化器可能选择不理想的索引
• 需要DBA监控和调整索引使用

(5)：设计复杂性

• 需要合理设计索引策略，过多或不当的索引反而会降低性能

2，索引作用

3，索引分类

在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引,可以令 MySQL 的查询和运行更加高效。索引是快速搜索的关键。MySQL 索引的建立对于 MySQL 的高效运行是非常重要的。

     从物理存储的角度来划分，索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索更快。
从逻辑的角度来划分，索引分为普通索引、唯一索引、主键索引、组合索引和全文索引。

• 普通索引

普通索引是最基本的索引，它没有任何限制，也是大多数情况下用到的索引

##准备创建索引的库和表
create database auth;             ##创建auth库
use auth;                        
create table users (id int(10),user_name char(20),user_pass char(50));    ##创建表##直接创建索引：语法:create index 索引名 on 表名 索引的值;
create index aaa on users(user_name(20));##修改表结构添加索引
alter table users(表名) add index bbb(索引名) （user_pass(50) "表中的值");user_name(20)其中 20 是可选项。如果忽略 20 的值，则使用整个列的值作为索引。如果指定使用列前的 20个字符来创建索引,就是使用列的一部分来创建索引，这样有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限255个字节(MyISAM和 InnoDB 表的最大上限为 1000 个字节)，如果索引列的长度超过了这个上限,就只能用列的前缀进行索引。另外，BLOB或TEXT类型的列也必须使用前缀索引。

 

• 唯一索引

唯一索引与普通索引类似，不同的就是:唯一索引的索引列的值必须唯一，但允许有空值(注意和主键不同)。如果是组合索引，则列值的组合必须唯一。唯一索引创建方法和普通索引类似。

##创建唯一索引
create unique index bbb on users(id);
mysql> create unique index bbb on users(id);mysql> show index from users\G             ##查看users表的索引
*************************** 1. row ***************************Table: usersNon_unique: 0Key_name: bbbSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: 
Index_comment: Visible: YESExpression: NULL
1 row in set (0.01 sec)##修改表结构的时候添加唯一索引：
alter table users add unique ccc(user_name);

 

• 主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值般是在建表的时候同时创建主键索引。

创建主键语法：

create table 表名 （列名 数据类型 primary key,）;

##创建主键索引，表名为students
mysql> CREATE TABLE students (->     student_id INT PRIMARY KEY,->     name VARCHAR(50),->     age INT-> );
Query OK, 0 rows affected (0.03 sec)mysql> show index from students\G      #查看表中的主键信息
*************************** 1. row ***************************Table: studentsNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: student_idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
Index_comment: Visible: YESExpression: NULL
1 row in set (0.00 sec)

 

• 组合索引

平时用的 SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL 的效率，就要考虑建立组合索引。在组合索引的创建中，有两种场景，即为单列索引和多列索引。

语法：

   CREATE TABLE 表名 (
    列1 数据类型,
    列2 数据类型,
    ...,
    INDEX 索引名 (列1, 列2, ...)
);

 

mysql> CREATE TABLE orders (->     order_id INT,->     customer_id INT,->     order_date DATE,->     INDEX idx_customer_order (customer_id, order_date)-> );mysql> show index from ordes\G        ##查看创建的组合索引
ERROR 1146 (42S02): Table 'auth.ordes' doesn't exist
mysql> show index from orders\G
*************************** 1. row ***************************Table: ordersNon_unique: 1Key_name: idx_customer_orderSeq_in_index: 1Column_name: customer_idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: 
Index_comment: Visible: YESExpression: NULL
*************************** 2. row ***************************Table: ordersNon_unique: 1Key_name: idx_customer_orderSeq_in_index: 2Column_name: order_dateCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: 
Index_comment: Visible: YESExpression: NULL
2 rows in set (0.01 sec)

 

• 全文索引

对于较大的数据集，将资料输入一个没有FULLTEXT 索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT 索引的速度更快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间、非常消耗硬盘空间的做法。

语法：

CREATE TABLE 表名 (
    列1 数据类型,
    列2 数据类型,
    ...,
    FULLTEXT INDEX 索引名 (列名) [WITH PARSER ngram]
) ENGINE=InnoDB;

 

##创建全文索引
mysql> CREATE TABLE articles (->     id INT AUTO_INCREMENT PRIMARY KEY,->     title VARCHAR(200),->     content TEXT,->     FULLTEXT INDEX ft_idx_title_content (title, content)-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)##查看全文索引
mysql> show index from articles\G
*************************** 1. row ***************************Table: articlesNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
Index_comment: Visible: YESExpression: NULL
....../省略部分内容

4，查看索引

MySQL 数据表索引已经创建好了，那么如何才能查看刚刚创建的索引?或者怎么去查看表内已经存在的索引?有以下两种查看当前索引的方式。

##查看某个表的索引

show index from 表名;     

show keys from 表名

mysql> show index from users\G             ##查看users表的索引
*************************** 1. row ***************************Table: users                 ##表的名称Non_unique: 0                     ##如果索引不能包括重复词，则为0;如果可以，则为1。Key_name: bbb                   ##索引的名称Seq_in_index: 1                     ##索引中的列序号，从 1开始。Column_name: id                    ##列名称Collation: A                     ##列以什么方式存储在索引中。在 MySQL 中，有值'A’(升序)或 NULL(无分类)。Cardinality: 0                     ##索引中唯一值数目的估计值。通过运行 ANALYZE TABLE 或myisamchk-a 可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。Sub_part: NULL                  ##如果列只是被部分地编入索引，则为被编入索引的字符的数目。
如果整列被编入索引，则为 NULL。Packed: NULL                  ##如果列含有 NULL，则含有YES。如果没有，则该列含有 NO。Index_type: BTREE                 ##用过的索引方法(BTREE,FULLTEXT, HASH,RTREE)。Comment:                       ##备注。

5，删除索引

索引在创建之后,是会占用一定的磁盘空间的,因此表内如果有不再使用的索引从数据库性能方面考虑，最好是删除无用索引。索引的删除有如下两种方法。

drop index 索引名 on 表名;

alter table 表名 drop index 索引名;

6，索引的应用场景

• 快速查找：在大型数据库表中，通过索引可以快速定位到满足特定条件的数据行。例如，在一个包含数百万条记录的用户表中，根据用户 ID 或用户名进行查询时，索引可以大大减少查询时间。
• 多条件查询：当查询涉及多个条件时，索引可以帮助数据库快速定位到满足所有条件的数据。例如，在一个订单表中，查询特定日期范围内、特定客户的订单，通过对订单日期和客户 ID 建立索引，可以快速找到符合条件的订单记录。

• 快速排序：索引可以按照特定的列进行排序，从而加快数据的排序速度。例如，在一个产品表中，按照价格对产品进行排序，如果价格列上有索引，数据库可以直接使用索引来快速获取排序后的结果，而无需对整个表进行排序操作。
• 分组排序：在进行分组查询时，索引也可以帮助提高排序效率。例如，在一个销售表中，按照地区对销售数据进行分组，并对每个地区的销售额进行排序。通过对地区列和销售额列建立索引，可以快速完成分组和排序操作。

7，不适合使用索引的场景

• 数据量小的表：当表中的数据量较少时，全表扫描的成本很低，使用索引可能会增加额外的开销，而不会带来明显的性能提升。例如，一个只有几十条记录的表，直接全表扫描查找数据可能比通过索引查找更快，因为索引的维护和查找本身也需要消耗一定的资源。
• 频繁更新的表：对于频繁进行插入、更新和删除操作的表，索引的维护成本较高。每次数据发生变化时，都需要更新相应的索引，这会增加数据库的负担，降低数据更新的性能。例如，在一个实时交易系统中，交易记录表可能会频繁地插入新记录，如果为该表的每个列都建立索引，那么每次插入新交易记录时，都需要更新多个索引，这会大大影响系统的插入性能。

二，Mysql事务

MySQL 事务是数据库管理系统执行过程中的一个逻辑单位，由一组 SQL 语句组成，这些语句要么全部成功执行，要么全部不执行。

1，事务的四大特性(ACID)

• 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成，不会停留在中间某个环节。如果事务执行过程中发生错误，会被回滚到事务开始前的状态。
• 一致性（Consistency）：事务将数据库从一种一致状态转换到另一种一致状态。例如，在转账操作中，无论事务是否成功，转账者和收款者的总金额应该保持不变。
• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不能被其他事务干扰，每个事务都感觉不到系统中其他事务在并发执行。
• 持久性（Durability）：事务一旦提交，它对数据库的改变就应该是永久性的，不会因系统故障而丢失。

2，事务控制语句

• BEGIN 或 START TRANSACTION：显式地开始一个事务。
• COMMIT：提交事务，将事务中所有的操作永久保存到数据库中。
• ROLLBACK：回滚事务，撤销事务中所有未提交的操作。
• SAVEPOINT：在事务中创建保存点，方便部分回滚。
• RELEASE SAVEPOINT：删除指定的保存点。
• ROLLBACK TO SAVEPOINT：将事务回滚到指定的保存点。

3，mysql事务的隔离级别

MySQL 提供了四种隔离级别，用于控制事务之间的可见性和干扰程度：

• READ UNCOMMITTED（读未提交）：最低的隔离级别，允许一个事务读取另一个事务未提交的数据，可能会导致脏读、不可重复读和幻读问题。
• READ COMMITTED（读已提交）：一个事务只能读取另一个事务已经提交的数据，避免了脏读，但仍可能出现不可重复读和幻读。
• REPEATABLE READ（可重复读）：MySQL 的默认隔离级别，确保在同一个事务中多次读取同一数据的结果是一致的，避免了脏读和不可重复读，但可能存在幻读。
• SERIALIZABLE（串行化）：最高的隔离级别，强制事务串行执行，避免了所有的并发问题，但会降低数据库的并发性能。

 4，使用mysql事务

提交事务：

commit；

回滚事务；

rollback;

开始事务：

begin;

commit;

自动提交事务：

set autocommit=0   禁止自动提交

set autocommit=0   ##开启自动提交

mysql> set autocommit=0             ##关闭自动提交-> ;
Query OK, 0 rows affected (0.00 sec)mysql> insert into users value(2,'lisi','123456');    ##插入一个数据
Query OK, 1 row affected (0.00 sec)mysql> select * from users;               ##查看表中的内容
+------+-----------+-----------+
| id   | user_name | user_pass |
+------+-----------+-----------+
|    1 | zhangsan  | 123456    |
|    2 | lisi      | 123456    |
+------+-----------+-----------+
2 rows in set (0.00 sec)mysql> rollback;                            ##事务回滚
Query OK, 0 rows affected (0.00 sec)mysql> select * from users;                ##回滚后数据会消失
+------+-----------+-----------+
| id   | user_name | user_pass |
+------+-----------+-----------+
|    1 | zhangsan  | 123456    |
+------+-----------+-----------+
1 row in set (0.00 sec)

 

mysql> begin;                                  ##开启事务
Query OK, 0 rows affected (0.00 sec)mysql> insert into users value(3,'aaaa','123456');
Query OK, 1 row affected (0.00 sec)mysql> insert into users value(4,'bbb','123456');
Query OK, 1 row affected (0.00 sec)mysql> commit;                              ##结束事务
Query OK, 0 rows affected (0.00 sec)mysql> select * from users;                   ##查看表中的内容
+------+-----------+-----------+
| id   | user_name | user_pass |
+------+-----------+-----------+
|    1 | zhangsan  | 123456    |
|    3 | aaaa      | 123456    |
|    4 | bbb       | 123456    |
+------+-----------+-----------+
3 rows in set (0.00 sec)