MySQL索引、事务与存储引擎

最新推荐文章于 2024-09-06 23:32:02 发布

靑木312

最新推荐文章于 2024-09-06 23:32:02 发布

阅读量1.1k

点赞数 27

文章标签： mysql 数据库 linux 服务器运维

本文链接：https://blog.csdn.net/minly12/article/details/140895795

版权

一、索引简介

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址；在数据十分庞大的时候，索引可以大大加快查询的速度；

这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据

如果需要在一个无序字段上进行搜索，就要执行一个线性搜索（Linear Search）的过程，平均需要访问 N/2 的数据块，N 是表示所占据的数据块数目；这个字段是一个非主键字段（也就是说，不包含唯一的访问入口），那么需要在 N 个数据块上搜索整个表格空间
对于一个有序字段，可以运用二分查找（Binary Search），这样只需要访问 log2 (N)的数据块

索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。使用索引的副作用是需要额外的磁盘空间

Log2N是数学中的对数，是以2为底N的对数为多少，也就是2的多少次方是N
线性搜索：
- 线性搜索是在列表中查找元素的基本算法。它依次检查列表的每个元素，直到找到目标元素或确定目标不存在。线性搜索也称为蛮力搜索，因为它检查列表中的每个元素，而不管是否检查了任何先前的元素
- 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）
- 使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。
- 索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。
- 索引是表中一列或者若干列值排序的方法
- 建立索引的目的是加快对表中记录的查找或排序

//创建测试数据库和表
mysql> create database auth;
mysql> use auth
mysql> create table users (id int(10),user_name char(20),user_pass char(50));

1：普通索引

//(1)直接创建索引语法
mysql>CREATE INDEX index_name ON table_name(column(length));
//例：
mysql> create index aaa on users(user_name(20));
//数字类型的列不能指定其长度，字符串的可以

//(2)修改表结构的方式添加索引语法
mysql>ALTER TABLE table_name ADD INDEX index_name (column(length));
//例：
mysql> drop index aaa on users;
mysql> alter table users add index aaa (user_pass(45));    //45是为该字段定义的字符长度，只能比初始值小，不能比初始值大

//(3)创建表结构时，同时创建索引
//创建table01表，指定title列为普通索引
CREATE TABLE table01 ( 
id int(11) NOT NULL AUTO_INCREMENT , 
title char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , 
time int(10) NULL DEFAULT NULL , 
PRIMARY KEY (id), 
INDEX index_table01_title (title(11)) 
);

其中 length 是可选项。如果忽略 length 的值，则使用整个列的值作为索引，如果指定使用列前的 length 个字符来创建索引，就是使用列的一部分来创建索引，这样有利于减小索引文件的大小，节省索引列所占的空间
在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB（二进制大对象）或TEXT（文本）类型的列也必须使用前缀索引

2：唯一索引

唯一索引与普通索引类似，不同的就是：唯一索引的索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一

（1）创建唯一索引语法
mysql>CREATE UNIQUE INDEX index_name ON table_name(column(length)); 

（2）修改表结构的时候添加唯一索引语法
mysql>ALTER TABLE table_name ADD UNIQUE index_name (column(length)); 

（3）创建表的时候同时创建唯一索引
CREATE TABLE table02 ( 
id int(11) NOT NULL AUTO_INCREMENT , 
title char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , 
time int(10) NULL DEFAULT NULL , 
PRIMARY KEY (id), 
UNIQUE index_table02_title (title(11)) 
);

//例：创建一个表t1，在表中的id字段上使用UNIQUE关键字创建唯一索引
//创建表的时候同时创建唯一索引
CREATE TABLE t1
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);

3：主键索引

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

//创建主键索引语法
CREATE TABLE table_name ( 
id int(11) NOT NULL AUTO_INCREMENT , 
title char(255) NOT NULL , 
PRIMARY KEY (id) 
);

//创建表t2，并将表中的id列设为主键
CREATE TABLE t2 
( 
id int(11) NOT NULL AUTO_INCREMENT , 
title char(255) NOT NULL , 
PRIMARY KEY (id) 
);

表的约束
实体完整性约束（主键）
域完整性约束（数据类型）
引用完整性约束（外键--》主键）
用户自定义完整性（char(20)）

4：组合索引（最左前缀）

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

//在一个t3用户表中，有 name，age，sex 三个字段，分别分三次建立了 INDEX 普 
通索引。那么在 select * from user where name = '' AND age = '' AND sex = '';数据查询语 
句中就会分别检索三条索引，虽然扫描效率有所提升，但却还未达到最优。这个时候就需要 
使用到组合索引（即多列索引）
create table t3
( 
name varchar(9), 
age int(3), 
sex tinyint(1), 
index eee(name, age, sex) 
);
//select 语句的 where 条件是依次从左往右执行的
mysql>select * from user where name = '' AND age = '' AND sex = ''; 
若使用的是组合索引 index user(name, age, sex)。在查询中，name、age、sex 的顺序必须如组合索引中一致排序，否则索引将不会生效

5：全文索引（FULLTEXT）

在1M大小的文件中搜索一个词，可能需要几秒，在100M的文件中可能需要几十秒，如果在更大的文件中搜索那么就需要更大的系统开销，这样的开销是不现实的。
在数据库中常用的查询方式一般是等价，范围方式。当然也有LIKE %的模糊查询，虽然用不到索引，在文本内容比较少时是比较合适，但是对于大量的文本数据检索，全文索引在大量的数据面前，能比 LIKE % 快很多，速度不是一个数量级

（1）创建表的全文索引语法： 
CREATE TABLE table ( 
id int(11) NOT NULL AUTO_INCREMENT , 
title char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , 
time int(10) NULL DEFAULT NULL , 
PRIMARY KEY (id), 
FULLTEXT (content) 
)ENGINE=MyISAM;

（2）修改表结构添加全文索引语法： 
mysql>ALTER TABLE article ADD FULLTEXT index_content(content);
//修改表结构添加全文索引语法： 
mysql> alter table t4 add fulltext fff(name);

（3）直接创建索引语法：
mysql>CREATE FULLTEXT INDEX index_content ON article(content);
//直接创建索引语法：
mysql> create fulltext index ggg on t4(info);

创建索引原则

创建数据库原则：
- 确定针对该表的操作是大量的查询操作还是大量的增删改操作；
- 尝试建立索引来帮助特定的查询。检查自己的 sql 语句，为那些频繁在 where 子句中出现的字段建立索引；
- 尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间，同时复合索引也占磁盘空间；
- 对于小型的表，建立索引可能会影响性能；
- 应该避免对具有较少值的字段进行索引；
- 避免选择大型数据类型的列作为索引。
索引建立的原则：
- 索引查询是数据库中重要的记录查询方法，要不要建立索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑
  - 表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是子表的主键，查询时可以快速定位
  - 记录数超过300行的表应该有索引。如果没有索引，需要把表遍历一遍，会严重影响数据库的性能
  - 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引
  - 经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。索引应该建在选择性高的字段上
  - 更新太频繁地字段不适合创建索引
  - 唯一性太差的字段不适合建立索引
  - 经常与其他表进行连接的表，在连接字段上应该建立索引

查看、删除索引

mysql>show index from t1\G; 
mysql>show keys from t2\G; 
mysql>show create table t3 \G;

//索引的删除有如下两种方法
DROP INDEX 索引名 ON 表名; 
ALTER TABLE 表名 DROP INDEX 索引名;
//例
mysql> drop index eee on t3;
mysql> alter table t1 drop index ccc;
//删除前可以先用show命令查看一下表中的索引名

Table：表的名称

Non unique：如果索引不能包括重复词，则为0;如果可以，则为1
Key_name：索引的名称
Seq_in_index：索引中的列序号，从1开始
Column name：列名称
Collation：列以什么方式存储在索引中。在 MySQL 中，有值'A(升序)或 NULL(无分类)
Cardinality：索引中唯一值数目的估计值。通过运行 ANALYZE TABLE 或 myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MSQL使用该索引的机会就越大
Sub_part:如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL
Packed:指示关键字如何被压缩。如果没有被压缩，则为NULL
Null:如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO
Index type:用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)
Comment:备注

二、MySQL事务

MySQL 事务主要用于处理操作量大，复杂度高的数据

是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行

是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元

适用于多用户同时操作的数据库系统的场景

原子性（Atomicity）
- 事务是一个完整的操作，事务的各元素是不可分的
- 事务中的所有元素必须作为一个整体提交或回滚
- 如果事务中的任何元素失败，则整个事务将失败
一致性（Consistency）
- 当事务完成时，数据必须处于一致状态
- 在事务开始前，数据库中存储的数据处于一致状态
- 在正在进行的事务中，数据可能处于不一致的状态
- 当事务成功完成时，数据必须再次回到已知的一致状态
隔离性（Isolation）
- 对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
- 修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据
持久性（Durability）
- 指不管系统是否发生故障，事务处理的结果都是永久的
- 一旦事务被提交，事务的效果会被永久地保留在数据库中

//事务控制语句
//BEGIN 或 START TRANSACTION:显式地开启一个事务
//COMMIT:也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改变为永久性的
//ROLLBACK:又可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
//SAVEPOINT identifier:SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT
//RELEASE SAVEPOINT identifier:删除一个事务的保存点，当没有指定的保存点时,执行该语句会抛出一个异常
//ROLLBACK TO identifier:把事务回滚到标记点
//SETTRANSACTION:用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和SERIALIZABLE
(1)用 BEGIN,ROLLBACK, COMMIT 来实现
    BEGIN 开始一个事务
    ROLLBACK 事务回滚
    COMMIT 事务确认
(2)直接用 SET 来改变 MySQL 的自动提交模式
    SETAUTOCOMMIT=0 禁止自动提交
    SETAUTOCOMMIT=1 开启自动提交
//在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
//例
mysql> SET AUTOCOMMIT=0;

mysql>use auth; 
mysql>CREATE TABLE kgc_transaction_test( id int(5)) engine=innodb;
mysql>select * from kgc_transaction_test; 

mysql>begin;     //开始事务 
mysql>insert into kgc_transaction_test value(1); 
mysql> insert into kgc_transaction_test value(2); 
mysql> commit;      //提交事务 
mysql>select * from kgc_transaction_test;

mysql>begin;     //开始事务 
mysql>insert into kgc_transaction_test values(3); 
mysql>rollback;     //回滚 
mysql> select * from kgc_transaction_test;     //因为回滚所以数据没有插入

三、MySQL存储引擎

MySQL 中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的、不同的功能和能力
如果在研究大量的临时数据，也许需要使用内存类存储引擎。内存存储引警能够在内存中存储相关数据。又或者，需要使用一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)

//查看MySQL支持的引擎
mysql>show engines;

1：MyISAM存储引擎

MyISAM 存储引擎不支持事务，也不支持外键，特点是访问速度快，对事务完整性没有要求，以 SELECT、INSERT 为主的应用基本都可以使用这个引擎来创建表
每个 MISAM 表在磁盘上存储成3个文件，其中文件名和表名都相同，扩展名：
- frm(存储表定义)
- MYD(MYData，存储数据)
- MYI(MYIndex，存储索引)
MyISAM 表还支持3种不同的存储格式：
- 静态(固定长度)表
  - 中静态表是默认的存储格式。静态表中的字段都是非可变字段,这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复;缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格,但是在访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉
- 动态表
  - 动态表包含可变字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难
- 压缩表
  - 压缩表由 myisamchk 工具创建,占据非常小的空间，因为每条记录都是被单独压缩的,所以只有非常小的访问开支

2：InnoDB存储引擎

更新密集的表:InnoDB 存储引擎特别适合处理多重并发的更新请求
事务:InnoDB 存储引擎是支持事务的标准 MySQL 存储引擎
自动灾难恢复:与其它存储引擎不同，InnoDB表能够自动从灾难中恢复
外键约束:MySQL支持外键的存储引擎只有InnoDB
支持自动增加列 AUTO INCREMENT 属性

区分使用，修改默认存储引擎

如果应用程序一定要使用事务，毫无疑问要选择 InnoDB 引擎。但要注意，InnoDB的行级锁是有条件的。在 where 条件没有使用主键时,照样会锁全表。比如 DELETE FROMmytable 这样的删除语句

如果应用程序对查询性能要求较高，就要使用MyISAM 了。MVISAM 索引和数据是分开的,而且其索引是压缩的,可以更好地利用内存。所以它的查询性能明显优于InnoDB.压缩后的索引也能节约一些磁盘空间

//通过 alter table 修改
MySQL>alter table user info engine=MylSAM;

//通过修改 my.cnf，指定默认存储引擎并重启服务
[root@Mysql /#vim my.cnfdefault-storage-engine=InnoDB

//通过 create table 创建表时指定存储引擎
MySQL>create table engineTest(id int) engine=MylSAM;

//通过 Mysql convert table format 转化存储引擎
[root@Mysql /]# yum -y install perl-DBl perl-DBD-MySQL
[root@Mysql/]# /usr/localmysql/bin/mysgl convert table format--user=root --password='123456'--sock=/tmp/mysql.sock auth

靑木312

关注

27
点赞
踩
27

收藏

觉得还不错? 一键收藏
0
评论
MySQL索引、事务与存储引擎

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址；在数据十分庞大的时候，索引可以大大加快查询的速度；这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。使用索引的副作用是需要额外的磁盘空间。
复制链接

扫一扫