理解MySQL数据库不可缺少的知识（索引，事务、存储引擎）！

最新推荐文章于 2024-04-30 17:02:38 发布

handsomeboy-东

最新推荐文章于 2024-04-30 17:02:38 发布

阅读量140

点赞数 2

文章标签： linux 运维

本文链接：https://blog.csdn.net/weixin_55496718/article/details/118554797

版权

一、索引

概述：索引时数据库管理系统中一个排序的数据结构，就如一本书前面的目录，以协助快速查询、更新数据库表中的数据

索引的作用

能够大大加快数据的查询速度，当表很大或要查询多个表时，甚至可以成千上万倍地提高查询速度
可以降低数据库的I/O（读写）成本，成本和排序成本
可以加快表与表之间的连接
通过唯一索引可以保证数据表中数据的唯一性
可大大减少分组和排序的时间

索引的缺点

索引需要额外的磁盘空间
插入和修改数据时需要更多的时间，因为索引也需要时间变动

索引的应用

表的主键、外键必须要索引，主键和索引值都具有唯一性，有助于查询，外键关联另一个表的主键，添加索引多表查询时可以快速定位
数据记录超过300行时要有索引，否则数据量太大，每次查询都要把表遍历一遍，会影响数据库的性能
经常与其他表进行连接的表，在连接字段上应该建立索引
唯一性差的字段不适合建立索引
更新太频繁的字段不适合建立索引
经常初见where判断子句中的字段，需要建立索引
索引应该建在选择性高的字段上，如果很少的字段拥有相同值，既有很多独特值，则选择性很高
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要键索引

索引的分类

普通索引

最基本的索引类型，没有唯一性之类的限制

直接创建索引

create index 索引名 on 表名 （列名[length]）;
####lengh是可选项，如果没有则表示整个列的值作为索引，可以指定使用列前的length值来创建索引，索引名最好为_insex结尾

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| whd                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use whd;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_whd |
+---------------+
| whd2          |
| whd3          |
| xuyichishi    |
+---------------+
3 rows in set (0.00 sec)

mysql> select * from whd2;
+----+---------+-----------+---------+
| id | name    | score_new | address |
+----+---------+-----------+---------+
|  1 | lisi    | 99        | 上海    |
|  2 | zhansan | 50        | 杭州    |
|  3 | wangwu  | 20        | 杭州    |
|  4 | xuyi    | 40        | 南京    |
|  5 | hh      | 50        | 湖北    |
+----+---------+-----------+---------+
5 rows in set (0.00 sec)
mysql> create index address_index on whd2(address);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table whd2\G;
*************************** 1. row ***************************
       Table: whd2
Create Table: CREATE TABLE "whd2" (
  "id" int(11) NOT NULL,
  "name" varchar(10) NOT NULL,
  "score_new" varchar(3) DEFAULT NULL,
  "address" varchar(40) DEFAULT NULL,
  PRIMARY KEY ("id"),
  KEY "address_index" ("address")				
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

修改表方式创建索引

alter table 表名 add index 索引名 （列名）;

mysql> alter table whd2 add index name_index (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table whd2\G;
*************************** 1. row ***************************
       Table: whd2
Create Table: CREATE TABLE "whd2" (
  "id" int(11) NOT NULL,
  "name" varchar(10) NOT NULL,
  "score_new" varchar(3) DEFAULT NULL,
  "address" varchar(40) DEFAULT NULL,
  PRIMARY KEY ("id"),
  KEY "address_index" ("address"),
  KEY "name_index" ("name")					#查看添加结果
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

创建表的时候指定索引

create table 表名（字段1 数据类型，字段2 数据类型………… ，index 索引名 (列名));

mysql> alter table whd2 add index name_index (name);
ERROR 1061 (42000): Duplicate key name 'name_index'
mysql> create table test (id int not null,name varchar(20),index name_inxex (name));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE "test" (
  "id" int(11) NOT NULL,
  "name" varchar(20) DEFAULT NULL,
  KEY "name_inxex" ("name")						
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

唯一性索引

与普通索引不同于唯一索引列的的每个值都唯一，唯一索引允许为空，但是如果用组合索引创建，则列值的组合必须唯一，添加唯一键将自动添加唯一索引

直接创建唯一索引

create unique index 索引名 on 表名 （列名）;

mysql> create unique index whd_index on test(id);
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE "test" (
  "id" int(11) NOT NULL,
  "name" varchar(20) DEFAULT NULL,
  UNIQUE KEY "whd_index" ("id"),
  KEY "name_inxex" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

创建表时指定唯一索引

mysql> create table test1 (id int not null,name varchar(20),address varchar(40),index address_index (address));
Query OK, 0 rows affected (0.00 sec)
mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE "test1" (
  "id" int(11) NOT NULL,
  "name" varchar(20) DEFAULT NULL,
  "address" varchar(40) DEFAULT NULL,
  KEY "address_index" ("address")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

修改表方式创建

alter table 表名 add unique 索引名 （列名）;

mysql> alter table test1 add unique name_index (name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE "test1" (
  "id" int(11) NOT NULL,
  "name" varchar(20) DEFAULT NULL,
  "address" varchar(40) DEFAULT NULL,
  UNIQUE KEY "name_index" ("name"),
  KEY "address_index" ("address")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

主键索引

是一种特殊的唯一索引，必须指定为primary key

创建表的时候指定

mysql> create table test2 (id int,name varchar(40),primary key(id));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE "test2" (
  "id" int(11) NOT NULL,
  "name" varchar(40) DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

修改表方式时指定

alter table 表名 on primary key (列名);			#主要一个表中只能由一个主键

组合索引

可以在单列或多列上创建的索引

create table 表名 （字段名1 数据类型，字段民2 数据类型，字段名3 数据类型，index 索引名 （列名1，列名2，列名3））;

mysql> create table test3 (id int not null,name varchar(40),age varchar(3),address varchar(20),index id_index (id,name,agge));
Query OK, 0 rows affected (0.00 sec)

mysql> show create table test3\G
*************************** 1. row ***************************
       Table: test3
Create Table: CREATE TABLE "test3" (
  "id" int(11) NOT NULL,
  "name" varchar(40) DEFAULT NULL,
  "age" varchar(3) DEFAULT NULL,
  "address" varchar(20) DEFAULT NULL,
  KEY "id_index" ("id","name","age")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

全文索引

适合在进行模糊查询的时候使用，可用于在一片文章中检索文本信息

创建表时指定索引

create table 表名 （字段1 数据类型，字段2 数据类型[，……]，fulltext 索引名 （列名））;

直接创建索引

create fulltext index 索引名 on 表名（列名）:

修改表方式创建

alter table 表名 add fulltext 索引名 (列名);

使用全文索引查询

select * from 表名 where match(列名) against('查询内容');

查看索引

show index from 表名;
show index from 表名\G;
show keys from 表名;
show keys from 表名\G:

mysql> show index from test;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | test_index |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          0 | id_index   |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          0 | whd_index  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | name_inxex |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

删除索引

drop index 索引名 on 表名;			#直接修改索引
alter table 表名 drop index 索引名;	#就该方式删除索引
alter table 表名 drop primary key;	#删除主键索引

mysql> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE "test" (
  "id" int(11) NOT NULL,
  "name" varchar(20) DEFAULT NULL,
  UNIQUE KEY "test_index" ("id"),
  UNIQUE KEY "id_index" ("id"),
  UNIQUE KEY "whd_index" ("id"),
  KEY "name_inxex" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> alter table test drop index whd_index;
Query OK, 0 rows affected (0.01 sec)
mysql> drop index test_index on test;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE "test" (
  "id" int(11) NOT NULL,
  "name" varchar(20) DEFAULT NULL,
  UNIQUE KEY "id_index" ("id"),
  KEY "name_inxex" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> update whd set money= money +200 where name='tianyi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from whd;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | tianyi |   300 |
|  2 | dier   |   100 |
+----+--------+-------+
2 rows in set (0.00 sec)

二、事务

事务是一个操作序列，这些操作要么执行，要么不执行，它是一个不可分割的工作单位，在数据库系统上执行并发操作时，事务是最小的控制单元，事务适用于多用户同时操作数据库的场景，如：银行，保险公司等

事务的ACID特点

原子性：指事务是一个不可再分割的工作单位，要么执行，要么不执行
一致性：表示事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
隔离性：表示在并发环境中，当不同的事务处理相同的数据时，每个事务都有自己的完整数据空间
持久性：表示在事务完成后，该事务所对数据库所作的更改将持久的保存在数据库中，并不会被删除

数据不一致产生的结果

脏读（读取未提交的数据）：表示读取了别的事务回滚前的脏数据，也就是说读取了一个事务想修改却在事务提交之前回滚而未修改成功的事务
不可重复读（前后多次读取的数据内容不一致），表示一个事务内的两个相同的查询却返回了不同的结果，如：事务A查询一个数据，事务B又对该数据进行了修改，事务A再查询该数据是修改后的结果
幻读（前后多次读取，数据的总量不一致）：如：一个事务修改了表中的全部行数据行，而另一个事务又插入了一行数据，这就导致前一个事务的修改过后发现还有为被修改的行，如发现幻觉
丢失更新：两个事务同时修改一条数据，而后面修改的不知道前面事务的修改，所有会导致前面事务的修改记录被后面事务的修改而覆盖

MySQL事务隔离级别

1、read uncommitted ：读取尚未提交的数据，不解决脏读
2、read conmmitted ：读取已提交的数据，可以解决脏读
3、repeatable read ：重复读取，可以解决脏读和不可重复读，这是mysql默认的隔离级别
4、serializable ：串行化，可以解决脏读，不可重复读，和虚读（相当于锁表）

set global transaction isolation level read committed;
#设置全局事务隔离级别
show global variable like '%isolation%';
select @@session.tx_isolation;
#查询全局隔离级别

set session tansaction isolation level read committed;
#设置会话事务隔离级别
show session variable like ’%isolation%‘
select @@tx_isolation;

事务控制语句

begin 或 start transaction ：显示地开启一个事务
commit 或 commit work ：提交事务，并使已对数据库进行的所有修改变为永久性的
rollback 或 rollback work ：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
savepoint 回滚点名称：允许在事务中创建一个回滚点，一个事务中可以有多个savepoint
rollback to [savepoint] 回滚点名称：把事务回滚到标记点

（1）新建表，添加数据，测试提交事务

mysql> create table whd (id int(10) primary key not null,name varchar(40),money double);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into whd values(1,'tianyi',100);

mysql> insert into whd values(2,'dier',100);
Query OK, 1 row affected (0.00 sec)
mysql> begin;							#表示事务的开始
Query OK, 0 rows affected (0.00 sec)
mysql> update whd set money= money +100 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

再新开一个终端，进入数据库，查看whd表单的内容

mysql> select * from whd;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | tianyi |   300 |
|  2 | dier   |   100 |				#此时数据还未变化
+----+--------+-------+
2 rows in set (0.00 sec)

再提交事务然后查询

mysql> select * from whd;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | tianyi |   300 |
|  2 | dier   |   200 |
+----+--------+-------+

测试回滚

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update whd set money= money -100 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from whd;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | tianyi |   300 |
|  2 | dier   |   200 |
+----+--------+-------+
2 rows in set (0.00 sec)
mysql> rollback;					#回滚到事务原点，并推出事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from whd;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | tianyi |   300 |
|  2 | dier   |   200 |				#查询结果
+----+--------+-------+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update whd set money= money -100 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> savepoint a;									#打上标签a
Query OK, 0 rows affected (0.00 sec)

mysql> update whd set money= money +600 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> savepoint b;									#打上标签b
Query OK, 0 rows affected (0.00 sec)
mysql> rollback to a;								#回滚至标签a，rollbac加上参数后只是回滚至节点处，不会结束事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from whd;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | tianyi |   200 |
|  2 | dier   |   100 |
+----+--------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

使用set设置自动提交

set autocommt=0或者1;		#0表示禁止自动提交，1表示自动提交，mysql默认为1
show variables like ’autcommit‘；			#查看mysql中autocommit值

三、MySQL存储引擎

MySQL中的存储引擎将MySQL将数据存储在文件系统中的存储方式或者存储格式，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，然后按照存储引擎的格式进行存储，MySQL中有两种存储引擎：MyISAM，InnoDB

MyISAM特点

MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件时分开保存的
访问速度快，对事务的完整性没有要求，它适合查询、插入为主的应用
MyISAM在磁盘上存储成三个文件，文件名和表名都相同，但是拓展名分别为：.frm（文件存储表结构的定义），数据文件的拓展名为：.MYD（Mydata）、.MYI（Myindex）

表级锁定形式：

数据在更新时会锁定整个表，其读和写的过程不能同时进行
好处是单独写入或读取，速度过程快且占用资源相对少

MyISAM的三种存储格式

静态表：
静态表是默认的存储格式，静态表中的字段都是非可变字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复，缺点是占用空间比动态多
动态表：
动态表包含可变字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁更新、删除记录会产生碎片，需要定期执行optimize table或myisamchk -r来改善性能，并且出现故障的时候恢复相对比较困难
压缩表：
压缩表由myisamchk工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支

MyISAM适用的场景

公司业务不需要事务的支持
单方面读取或写于数据比较多的业务
数据修改相对较少的业务
对数据业务一致性要求不是非常高的业务
服务器硬件资源相对比较差

InnoDB

InnoDB特点

InnoDB是MySQL5.5后的默认存储引擎，它是它支持事务和事务的四个隔离级别
读写阻塞与事务隔离级别相关，能非常高效的缓存索引和数据
表与主键以簇的方式存储，支持分区、表空间，类似oracle数据库
支持外键约束，5.5版本后还支持全文索引
行级锁定：InnoDB是行级锁定，当时当进行全表扫描时任然会是表级锁定（select）
InnoDB不保存表的行数
对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立组合索引
清空整个表时，InnoDB是一行一行的删除，效率非常慢，而MyISAM则会重建表

mysql> select count(id) from whd;		#查询whd表的id行数时，要扫描一遍整个表
+-----------+
| count(id) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

查看存储引擎

查看系统支持的存储引擎

mysql> show engines\G;					#查询系统支持的存储引擎
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
……………………………………

查看表使用的存储引擎

1、show table status from 库名 where name=’表名‘\G;
2、use 库名;
show create table 表名;

mysql> show table status from whd where name='whd'\G;
*************************** 1. row ***************************
           Name: whd
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
…………………………………………
mysql> show create table whd\G;
*************************** 1. row ***************************
       Table: whd
Create Table: CREATE TABLE "whd" (
  "id" int(10) NOT NULL,
  "name" varchar(40) DEFAULT NULL,
  "money" double DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

修改存储引擎

1、alter table 表名 engine=MyISAM;			#直接在库中修改，这种只是对单个表修改
2、vim /etc/my.cnf			#通过修改配置文件，指定默认存储引擎并重启服务，这种方法是批量修改
(mysqld)
default-storage-engine-INNODB
3、create table 表名(字段1 数据类型，……) engine=MyISAM;

mysql> alter table whd engine=myisam;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table whd\G;
*************************** 1. row ***************************
       Table: whd
Create Table: CREATE TABLE "whd" (
  "id" int(10) NOT NULL,
  "name" varchar(40) DEFAULT NULL,
  "money" double DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> create table info(id int not null) engine-MyISAM;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-MyISAM' at line 1
mysql> create table info(id int not null) engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table info\G;
*************************** 1. row ***************************
       Table: info
Create Table: CREATE TABLE "info" (
  "id" int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

总结

MySQL索引分为：普通索引，唯一索引，主键索引，组合索引，全文索引
数据库事务的ACID特性：原子性，一致性，隔离性，持久性
数据不一致产生的结果：脏读、不可重复读、幻读、丢失更新
事务的隔离级别：read uncommit（未提交读）、read commit（已提交读）、repeatable read（重复度）、serialzable（串行化）

handsomeboy-东

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
理解MySQL数据库不可缺少的知识（索引，事务、存储引擎）！

MySQL索引索引的作用索引的缺点索引的应用索引的分类普通索引唯一性索引主键索引组合索引全文索引事务索引概述：索引时数据库管理系统中一个排序的数据结构，就如一本书前面的目录，以协助快速查询、更新数据库表中的数据索引的作用能够大大加快数据的查询速度，当表很大或要查询多个表时，甚至可以成千上万倍地提高查询速度可以降低数据库的I/O（读写）成本，成本和排序成本可以加快表与表之间的连接通过唯一索引可以保证数据表中数据的唯一性可大大减少分组和排序的时间索引的缺点索引需要额外的磁盘空间插
复制链接

扫一扫