Linux中，MySQL索引、事物与存储引擎-CSDN博客

本文链接：https://blog.csdn.net/ssg18336543974/article/details/140717790

MySQL索引介绍

索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据十分庞大的时候，索引可以大大加快查询的速度。这是因为使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。索引的作用类似于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引概述

当数据保存在磁盘类存储介质上时，它是作为数据块存放。这些数据块是被当作一个整体来访问的，这样可以保证操作的原子性。硬盘数据块存储结构类似于链表，都包含数据部分，以及一个指向下一个节点(或数据块)的指针，不需要连续存储。
记录集只能在某个关键字段上进行排序，所以如果需要在一个无序字段上进行搜索，就要执行一个线性搜索(Linear Search)的过程，平均需要访问 N/2 的数据块，N 是表示所占据的数据块数目。如果这个字段是一个非主键字段(也就是说，不包含唯一的访问入口)那么需要在N个数据块上搜索整个表格空间。
但是对于一个有序字段,可以运用二分查找(Binary Search),这样只需要访问 log2(N)的数据块。这就是为什么数据表使用索引后性能可以得到本质上提高的原因。
索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引,将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。
使用索引的副作用是需要额外的磁盘空间。对于MyISAM 引擎而言，这些索引是被统一保存在一张表中的。如果很多字段都建立了索引，那么会占用大量的磁盘空间，这个文件将很快到达底层文件系统所能够支持的大小限制。

索引作用

在索引列上，除了上面提到的有序查找之外，数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。
例如，有3个未索引的表 t1、t2、t3，分别只包含列 c1、c2、c3，每个表分别含有 1000行数据组成，均为1~1000 的数值，查找对应值相等行的查询如下所示。

索引的分类

在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询和运行更加高效。索引是快速搜索的关键。MySQL索引的建立对于 MySQL的高效运行是非常重要的。下面介绍几种常见的 MySQL 索引类型。
从物理存储的角度来划分，索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了:聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索更快。
从逻辑的角度来划分，索引分为普通索引、唯一索引、主键索引、组合索引和全文索引。
这些索引分类的具体解释如下所示。

普通索引

普通索引是最基本的索引，它没有任何限制，也是大多数情况下用到的索引。它有以下几种创建方式。
直接创建索引:

修改表结构的方式添加索引：

创建表结构时，同时创建索引：

唯一索引

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

修改表结构的时候添加唯一索引

创建表的时候同时创建唯一索引

主键索引

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

组合索引

平时用的 SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取 MySQL的效率，就要考虑建立组合索引。在组合索引的创建中，有两种场景，即为单列索引和多列索引。下面通过一个场景来具体说明单列索引和多列索引。
在一个user用户表中，有name，age，sex三个字段，分别分三次建立了INDEX普通索引。那么在 select * from user where name ="AND age ="AND sex=";数据查询语句中就会分别检索三条索引，虽然扫描效率有所提升，但却还未达到最优。这个时候就需要使用到组合索引(即多列索引)，如下所示。

在 MySQL中，有一个知识点叫最左原则。下面的 select 语句的 where 条件是依次从左往右执行的。

若使用的是组合索引index user(name,age,sex)。在查询中，name、age、sex 的顺序必须如组合索引中一致排序，否则索引将不会生效，例如:

如果采用“select * from user where age ="AND name="AND sex=";"查询方式，这条组合索引将无效化，所以一般在建立索引时，要先想好相应的查询业务，尽量避免虽然有索引，但是使用不上的问题。

全文索引（FULLTEXT）

MySQL 从 3.23.23版开始支持全文索引和全文检索。在 MySQL5.6 版本以前FULLTEXT索引仅可用于 MyISAM 表，在 5.6之后innodb 引擎也支持 FULLTEXT 索引;他们可以从 CHAR、VARCHAR 或 TEXT 列中作为 CREATE TABLE 语句的一部分被创建，或是随后使用 ALTER TABLE 或 CREATEINDEX 被添加。
对于较大的数据集，将资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有 FULLTEXT 索引的速度更快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间、非常消耗硬盘空间的做法。
创建表的全文索引:

修改表结构添加全文索引

直接创建索引

创建索引的原则依据

数据库建立索引的原则:
确定针对该表的操作是大量的查询操作还是大量的增删改操作:
尝试建立索引来帮助特定的查询。检查自己的sq语句，为那些频繁在 where 子句中出现的字段建立索引:
尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间，同时复合索引也占磁盘空间;
对于小型的表，建立索引可能会影响性能:
应该避免对具有较少值的字段进行索引:
避免选择大型数据类型的列作为索引。
索引建立的原则:
索引查询是数据库中重要的记录查询方法,要不要建立索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑，下面给出实际生产环境中的一些通用的原则:
在经常用作过滤暑的字段上建立索引:
在 SQL 语句中经常进行 GROUP BY、ORDER BY 的字段上建立索引:在不同值较少的字段上不必要建立索引，如性别字段:对于经常存取的列避免建立索引:
用于联接的列(主健/外健)上建立索引:
在经常存取的多个列上建立复合索引,但要注意复合索引的建立顺序要按照使用的频度来确定:
缺省情况下建立的是非簇集索引，但在以下情况下最好考虑族集索引，如:含有限数目(不是很少)唯一的列;进行大范围的查询;充分的利用案引可以减少表扫描I0的次数，有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中，也取决于 DBA 所设计的数据库结构。

查看索引

show create table table_name\G

删除索引

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

以 player 表为例，删除 player 表的索引。

MySQL事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，要删除一个人员，即需要删除人员的基本资料，又需要删除和该人员相关的信息，如信箱，文章等等。这样，这些数据库操作语句就构成一个事务!
在 MySQL 中只有使用了Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句。
一般来说，事务是必须满足4个条件(ACID):原子性(Atomicity，或称不可分割性)、一致性(Consistency)、隔离性(Isolation，又称独立性)、持久性(Durability)。
原子性:一个事务(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚(Rolback)到事务开始前的状态，就像这个事务从来没有执行过一样;
一致性:在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作:
隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务离分为不同级别，包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatableread)和串行化(Serializable):
持久性;事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT 操作，因此要显式地开启一个事务必须使用命令BEGIN或STARTTRANSACTION，或者执行命令 SETAUTOCOMMIT=0，用米禁止使用当前会话的自动提从
事务控制语句包含:
BEGIN或STARTTRANSACTION:显式地开启一个事务:
COMMIT:也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改变为永久性的:
ROLLBACK:又可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改:
SAVEPOINTidentifer:SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT:
RELEASE SAVEPOINT identifer:删除一个事务的保存点，当没有指定的保存点时,执行该语句会抛出一个异常:
ROLLBACKTO identifer:把事务回滚到标记点:
SETTRANSACTION:用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和SERIALIZABLE.
MYSQL 事务处理主要有两种方法:
(1)用 BEGIN,ROLLBACK,COMMIT 米实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
(2)直接用 SET米改变 MySQL 的自动提交模式
SETAUTOCOMMIT=0 禁止自动提交
SETAUTOCOMMIT=1 开启自动提交
以下是事务的示例:

MySQL存储引擎

在数据库中保存的是一张张有着千丝万缕关系的表，所以表设计的好坏，将直接影响着整个数据库。而在设计表的时候，最关注的一个问题是使用什么存储引。
MySOL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种都使用不同的存储机制、索引技巧、镇定水平并且最终提供广泛的、不同的功能和能力。通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体性。
例如，如果在研究大量的临时数据，也许需要使用内存类存储引擎。内存存储引擎能够在内存中存储相关数据。又或者，需要使用一个支持事务处理的数据库(以确保事务处理不成功时数据的回退力)。
这些不同的技术以及配套的相关功能在 MySQL中被称作存储引(也称作表类型)。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在 MYSQL服务器中启用。选择适用于服务器、数据库和表格的存储引擎，可以在存储信息、检索数据时，提供最大的灵活性。
关系数据库表是用于存储和组织信息的数据结构，可以将表理解为由行和列组成的表格，类似于Exoel电子表格的形式。有的表简单、有的表复条、有的表根本不用来存储任何长期数据、有的表读取时非常快，但是插入数据时却很差。在实际开发过程中，就可能需要各种各样的表，不同的表就意味着存储不同类型的数据，数据的处理上也会存在着差异。那么，对于MySOL来说，它提供了很多种类型的存储引擎(或者说不同的表类型)，根据对数据处理的需求，可以选择不同的存储引整，从而最大限度的利用MySQL强大的功能，
在 MYSOL客户端中，使用以下命令可以查看MySOL支持的引擎，

mylsam存储引擎

MyISAM 存储引擎不支持事务，也不支持外键，特点是访问速度快，对事务完整性没有要求，以 SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。
每个MyISAM表在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为:
tm(存储表定义)
MYD(MYData，存储数据)
MYl(MYIndex，存储索引)
数据文件和索引文件可以放置在不同的目录，平均分配I〇，获取更快的速度。要指定数据文件和常引文件的路径，需要在创建表的时候通过DATA DIRECTORY 和INDEXDIRECTORY 语句指定，文件路径需要使用绝对路径。
每个MyISAM 表都有一个标志，服务器或myisamchk 程序在检查 MyISAM 数据表时会对这个标志进行设置。MYISAM表还有一个标志用来表明该数据表在上次使用后是不是被正常的关闭了。如果服务器意外宕机或崩溃，这个标志可以用来判断数据表是否需要枪查和修复。如果想让这种检查自动进行，可以在启动服务器时使用--my8am-recover实现。这会让服务器在每次打开一个MyISAM数据表时自动检查数据表的标志并进行必要的修复处理。MyISAM 类型的表可能会损坏，可以使用 CHECKTABLE 语句※检查 MyISAM 表的健康,并用 REPAIR TABLE 语句修复一个损坏的 MyISAM 表。

MyISAM 表还支持3种不同的存储格式:
静态(固定长度)表
动态表
压缩表
其中静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复;缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格,但是在访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。同时需要注意:在某些情况下可能需要返回字段后的空格，而使用这种格式时后面的空格会被自动处理掉。
动态表包含可变字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难。
压缩表由 myisamchk 工具创建，占据非常小的空间，因为每条记录都是被单独压缩的,所以只有非常小的访问开支。

innoDB存储引擎

InnoDB是一个健壮的事务型存储引整，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。MVSQL从5.5.5版本开始，默认的存储引擎为InnoDB。InnoDB存储引擎还引入了行级锁定和外键约束，在以下场景中使用InnoDB 存储引笔是最理想的选择:
更新密集的表:InnoDB 存储引单特别适合处理多重并发的更新请求。
事务:InnoDB 存储引笔是支持事务的标准 MYSOL存储引。
自动灾难恢复:与其它存储引整不同，InnoDB表能够自动从灾中恢复。
外健约束:MySQL支持外键的存储引整只有InnoDB
支排自动增加列 AUTO INCREMENT 属性。
一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择，面 MyISAM 和lnnoDB 两种存储引擎的区别主要表现在以下几个方面。
InnODB 支持事务，MYISAM不支持，这一点是非常重要的。事务是一种高级的处理方式。如对一些表中的列进行增副改的过程中只要哪个出错还可以回滚还原，而 MYISAM就不可以。
MyISAM 适合查询、插入为主的应用，InnoDB 适合频繁修改以及涉及到安全性较高的应用。
InnoDB 支持外键，MYISAM 不支持。
从 MySQL5.5.5 以后，InnoDB 是默认引繁。
MySQL从5.6版本开始InnoDB 引擎才支持 FULLTEXT 类型的索引
InnoDB 中不保存表的行数，如seectcounl(*)from lable时，InnoDB 需要扫描一遍整个表来计算有多少行，但是MYISAM只要简单的读出保存好的行数即可，需要注意的是，当count()语句包含where 系件时 MYISAM 也需要扫描整个表。
对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MYISAM 表中可以和其他字段一起建立组合索引。
清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM 则会重建表。
InnoDB 支持行锁(某些情况下还是锁整表，如update table set a=1 where user like"%lee%”)

3.关于 MyISAM 与InnoDB 选择使用

MyISAM 和 InnoDB 是 MySQL 数据库提供的两种存储引擎。两者的优劣可谓是各有千秋。InnoDB 会支持一些关系数据库的高级功能，如事务功能和行级锁，MyISAM 不支持。MyISAM 的性能更优，占用的存储空间少。所以，选择何种存储引擎，视具体应用而定。
(1)如果应用程序一定要使用事务，毫无疑问要选择InnoDB 引擎。但要注意，InnoDB的行级锁是有条件的。在 where 条件没有使用主键时,照样会锁全表。比如 DELETEFROMmytable 这样的删除语句。
(2)如果应用程序对查询性能要求较高，就要使用MyISAM 了。MyISAM 索引和数据是分开的,而且其索引是压缩的,可以更好地利用内存。所以它的查询性能明显优于InnoDB。压缩后的索引也能节约一些磁盘空间。
有人说 MVISAM 只能用于小型应用，其实这只是一种偏见。如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，而不是单纯地依赖存储引擎。