MySQL索引与事务、存储引擎MyISAM和InnoDB

最新推荐文章于 2024-07-18 20:04:15 发布

尽头海水逆流

最新推荐文章于 2024-07-18 20:04:15 发布

阅读量755

点赞数 14

文章标签： mysql 数据库运维 linux 云计算

本文链接：https://blog.csdn.net/FolloW_0616/article/details/140102685

版权

一、索引

定义：

索引是一个排序的列表，包含索引字段的值和其对应的行记录数据所在的物理地址

作用：

加快表的查询速度（主要作用），还可以对字段排序

副作用：

会额外占用磁盘空间；更新包含索引的表会花费更多的时间

工作方式：

没有索引的情况下，要查询某行记录数据时，需要先扫描全表，再定位某行记录数据的位置
有了索引后，会先通过索引查询到行记录数据所在的物理地址，即可直接访问相应的行记录数据，就像通过书目录的页码快速查找书内容一样。

创建索引的依据：

表的记录行数较多时（一般超过三五百行时），且读操作多的情况下应该要创建索引
建议在表的主键字段、外键字段、多表连接使用的公共字段、唯一性较好的字段、不经常更新的字段、where条件字段、分组(group by)字段、排序(order by)字段、短小的字段上创建索引
不建议在唯一性较差的字段、更新太频繁的字段、大文本字段上创建索引

二、索引类型：

【1】普通索引：最基本的索引类型,没有唯一性之类的限制.

create index 索引名 on 表名(字段(长度)); #直接创建索引
alter table 表名 add index 索引名(字段); #修改表方式创建
create table 表名 (.... , index 索引名(字段)); #创建表的时候指定索引

（1）直接创建索引

create index 索引名 on 表名(列名[ ( length) ]);

(列名(length) ): length是可选项。如果忽略length的值，则使用整个列的值作为索引。如果指定使用列前的length个字符来创建索引，这样有利于减小索引文件的大小。在不损失精确性的情况下，长度越短越好。
索引名建议以"index”结尾

mysql> create table xy101 (id int, name varchar(20), age int, sex char(2));  //创建一个表
Query OK, 0 rows affected (0.00 sec)
 
mysql> show tables;
+--------------+
| Tables_in_xy |
+--------------+
| xy101        |
+--------------+
1 row in set (0.00 sec)
 
mysql> desc xy101;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| seex  | char(2)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql> create index name_index on xy101(name);  //为表xy101指定name创建普通索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql>

（2）修改表方式创建

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

mysql> create table xy102 (id int, name varchar(20), age int, sex char(22));
Query OK, 0 rows affected (0.01 sec)
 
mysql> desc xy102;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| seex  | char(2)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql> alter table xy102 add index name_index (name);  //修改表方式创建
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show create table xy102\G;
*************************** 1. row ***************************
       Table: xy102
Create Table: CREATE TABLE "xy102" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "seex" char(2) DEFAULT NULL,
  KEY "name_index" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
ERROR: 
No query specified
 
mysql>

（3）创建表的时候指定索引:

create table 表名 ( 字段1 数据类型,字段2 数据类型,[...],index 索引名 (列名));

mysql> create table xy103 (id int, name varchar(20), age int, sex char(2), index name_index (name));  //创建表的时候指定索引
Query OK, 0 rows affected (0.00 sec)
 
mysql> desc xy103;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | MUL | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| sex   | char(2)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql> show create table xy103\G;
*************************** 1. row ***************************
       Table: xy103
Create Table: CREATE TABLE "xy103" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(2) DEFAULT NULL,
  KEY "name_index" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
ERROR: 
No query specified
 
mysql>

【2】唯一索引：

create unique index 索引名 on 表名(字段); #直接创建唯一索引
alter table 表名 add unique 索引名(字段); #修改表方式创建
create table 表名 (.... , unique 索引名(字段)); #创建表的时候指定

（1）直接创建唯一索引

create unique index 索引名 on 表名(列名);

mysql> create table xy104 (id int, name varchar(20), age int, sex char(2));
Query OK, 0 rows affected (0.00 sec)
 
mysql> show tables;
+--------------+
| Tables_in_xy |
+--------------+
| xy101        |
| xy102        |
| xy103        |
| xy104        |
+--------------+
4 rows in set (0.00 sec)
 
mysql>

mysql> create unique index id_name on xy104(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc xy104;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| seex  | char(2)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql>

（2）修改表方式创建

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

mysql> create table xy105 (id int, name varchar(20), age int, sex char(22));
Query OK, 0 rows affected (0.00 sec)
 
mysql> desc xy105;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| seex  | char(2)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql>

mysql> alter table xy105 add unique id_index (id);  //修改表方式创建
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc xy105;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| seex  | char(2)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql> show create table xy105\G
*************************** 1. row ***************************
       Table: xy105
Create Table: CREATE TABLE "xy105" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "seex" char(2) DEFAULT NULL,
  UNIQUE KEY "id_index" ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
mysql>

（3）创建表的时候指定

create table 表名 (字段1 数据类型,字段2 数据类型,[...],unique 索引名 (列名));

mysql> create table xy106 (id int, name varchar(20), age int, sex char(2), unique ame_index (name));
Query OK, 0 rows affected (0.01 sec)
 
mysql> desc xy106;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| sex   | char(2)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql>

【3】主键索引：

是一种特殊的唯一索引，必须指定为==‘‘PRIMARY KEY’’==。一个表只能有一个主键，不允许有空值。添加主键将自动创建主键索引

alter table 表名 add primary key(字段); #修改表方式创建
create table 表名 (.... , primary key(字段)); #创建表的时候指定 11.30

（1）创建表的时候指定

mysql> create table xy107 (id int,name varchar(20), age int, sex char(2),
, primary key(id));
Query OK, 0 rows affected (0.00 sec)
 
mysql>  show create table xy107\G
*************************** 1. row ***************************
       Table: xy107
Create Table: CREATE TABLE "xy107" (
  "id" int(11) NOT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(2) DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
mysql>

（2）修改表方式创建

alter table 表名 add primary key (列名);

mysql> alter table xy106 add primary key (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc xy106;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   | PRI | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| sex   | char(2)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql>  show create table xy106\G
*************************** 1. row ***************************
       Table: xy106
Create Table: CREATE TABLE "xy106" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) NOT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(2) DEFAULT NULL,
  PRIMARY KEY ("name"),
  UNIQUE KEY "ame_index" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
mysql>

【4】多列组合索引：

单列索引与多列索引）：可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为select语句的where条件是依次从左往右执行的，所以在使用select语句查询时，where条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

create index 索引名 on 表名(字段1, 字段2, ....);
alter table 表名 add index 索引名(字段1, 字段2, ....);
select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ; #查询语句使用 and 做逻辑运算符时，字段顺序要与创建多列索引的字段顺序一致（要满足最左原则）

（1）直接创建索引

create index 索引名 on 表名(字段1, 字段2, ....);
select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ;   
#查询语句使用 and 做逻辑运算符时，字段顺序要与创建多列索引的字段顺序一致（要满足最左原则）

mysql> create table xy108 (id int, name varchar(20), age int, sex char(22));
Query OK, 0 rows affected (0.00 sec)
 
mysql>  create index tan_index on xy108 (sex, age, name)；  //直接创建索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql>  show create table xy108\G
*************************** 1. row ***************************
       Table: xy108
Create Table: CREATE TABLE "xy108" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(22) DEFAULT NULL,
  KEY "tan_index" ("sex","age","name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
mysql>

mysql> insert into xy108(id, name, sex, age) values(1, 'my', '女', 20);   //插入数据
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into xy108(id, name, sex, age) values(2, 'yf', '女', 88);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into xy108(id, name, sex, age) values(3, 'tmt', '女', 99);
 
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into xy108(id, name, sex, age) values(4, 'yzl', '女', 800);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
|    2 | yf   |   88 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql> select * from xy108 where sex='女' and age=20 and name='my';
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
+------+------+------+------+
1 row in set (0.00 sec)
 
mysql>

（2）修改表方式创建

mysql> create table xy109 (id int, name varchar(20), age int, sex char(222));
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into xy109(id, name, sex, age) values(1, 'my', '女', 20);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into xy109(id, name, sex, age) values(2, 'mm', '男', 21);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into xy109(id, name, sex, age) values(3, 'll', '男', 21);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into xy109(id, name, sex, age) values(4, 'xx', '女', 23);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from xy109;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
|    2 | mm   |   21 | 男   |
|    3 | ll   |   21 | 男   |
|    4 | xx   |   23 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql>

mysql> alter table xy109 add index tan_index (sex, age, name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show create table xy109\G
*************************** 1. row ***************************
       Table: xy109
Create Table: CREATE TABLE "xy109" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(22) DEFAULT NULL,
  KEY "tan_index" ("sex","age","name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
mysql>

【5】全文索引：

create fulltext index 索引名 on 表名(字段); #直接创建索引
alter table 表名 add fulltext 索引名(字段); #修改表方式创建
create table 表名 (.... , fulltext 索引名(字段)); #创建表的时候指定索引
全文索引可用作于模糊查询 select 字段列表 from 表名 where match(字段) against('单词');

全文索引只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引，只支持InnoDB和MyISAM引擎。
全文索引只支持英文全文索引，不支持中文全文索引，需要使用ngram全文解析器，用来支持中文全文索引

（1）直接创建索引:

create fulltext index 索引名 on 表名 (列名);

mysql> create table xy1010 (id int, name varchar(20), age int, sex char(222));
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into xy1010 (id, name, sex, age) values(4, 'xx', '女', 23);
;
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into xy1010 (id, name, sex, age) values(3, 'hh', '女', 23);
;
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into xy1010 (id, name, sex, age) values(2, 'gg', '女', 23);
;
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into xy1010 (id, name, sex, age) values(1, 'dd', '女', 25);;
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from xy1010;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    4 | xx   |   23 | 女   |
|    3 | hh   |   23 | 女   |
|    2 | gg   |   23 | 女   |
|    1 | dd   |   25 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql>

mysql> create fulltext index hh_name on xy1010(sex);
Query OK, 0 rows affected, 1 warning (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
mysql>  show create table xy1010\G
*************************** 1. row ***************************
       Table: xy1010
Create Table: CREATE TABLE "xy1010" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(22) DEFAULT NULL,
  FULLTEXT KEY "hh_name" ("sex")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
mysql>

（2）修改表方式创建

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

mysql> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
|    2 | yf   |   88 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql> alter table xy108 add fulltext 108_name (name);  修改表方式创建
Query OK, 0 rows affected, 1 warning (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
mysql> show create table xy108\G
*************************** 1. row ***************************
       Table: xy108
Create Table: CREATE TABLE "xy108" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(22) DEFAULT NULL,
  KEY "tan_index" ("sex","age","name"),
  FULLTEXT KEY "108_name" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
mysql>

（3）创建表的时候指定索引

create table 表名 (字段1 数据类型,[...],fulltext 索引名 (列名));
#数据类型可以为char、varchar或者text

mysql> create table xy88 (id int, name varchar(20), sex char(2), age int, remark text, fulltext remark_name (remark));
Query OK, 0 rows affected (0.02 sec)
 
mysql> show create table xy88\G
*************************** 1. row ***************************
       Table: xy88
Create Table: CREATE TABLE "xy88" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "sex" char(2) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "remark" text,
  FULLTEXT KEY "remark_name" ("remark")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
mysql>

（4）使用全文索引查询:

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

mysql> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
|    2 | yf   |   88 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql> select * from xy108 where match(name) against('yzl');
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
1 row in set (0.00 sec)
 
mysql>

（5）中文全文索引查询:

vim /etc/my.cfn
ngram_token_size=2       #指定查询的单词的最小字数
systemctl restart mysqld.service
 
create table xy011 (id int, name varchar(20), age int, sex char(2), remark text, fulltext (remark) with parser ngram);  #创建表
insert into xy011 values (1, 'zhang', 25, '女', '足球，篮球，台球');
insert into xy011 values (2, 'san', 26, '男', '吃饭，足球，乒乓球');
insert into xy011 values (3, 'li', 26, '女', '篮球，羽毛球，乒乓球');
insert into xy011 values (4, 'si', 26, '男', '篮球，乒乓球，足球');
#添加四行数据

mysql> create table xy011 (id int, name varchar(20), age int, sex char(2), remark text, fulltext (remark) with parser ngram);
Query OK, 0 rows affected (0.03 sec)
 
mysql> show create table xy011\G
*************************** 1. row ***************************
       Table: xy011
Create Table: CREATE TABLE "xy011" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(2) DEFAULT NULL,
  "remark" text,
  FULLTEXT KEY "remark" ("remark") /*!50100 WITH PARSER "ngram" */ 
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
mysql>

mysql> insert into xy011 values (1, 'zhang', 25, '女', '足球，篮球，台球');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into xy011 values (2, 'san', 26, '男', '吃饭，足球，乒乓球');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into xy011 values (3, 'li', 26, '女', '篮球，羽毛球，乒乓 球');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into xy011 values (4, 'si', 26, '男', '篮球，乒乓球，足球');
Query OK, 1 row affected (0.00 sec)

select 字段列表 from 表名 where match(字段) against('单词');     #默认使用自然语言模式
select 字段列表 from 表名 where match(字段) against('+单词1 -单词2' IN BOOLEAN MODE);     #使用BOOLEAN模式，必须包含"单词1"，且不能包含"单词2"
select 字段列表 from 表名 where match(字段) against('+单词1 +单词2' IN BOOLEAN MODE);     #使用BOOLEAN模式，必须同时包含"单词1"和"单词2"
select 字段列表 from 表名 where match(字段) against('单词1 单词2' IN BOOLEAN MODE);       #使用BOOLEAN模式，要么包含"单词1"，要么包含"单词2"

mysql> select * from xy011;
+------+-------+------+------+--------------------------------+
| id   | name  | age  | sex  | remark                         |
+------+-------+------+------+--------------------------------+
|    1 | zhang |   25 | 女   | 足球，篮球，台球               |
|    2 | san   |   26 | 男   | 吃饭，足球，乒乓球             |
|    3 | li    |   26 | 女   | 篮球，羽毛球，乒乓球           |
|    4 | si    |   26 | 男   | 篮球，乒乓球，足球             |
+------+-------+------+------+--------------------------------+
4 rows in set (0.00 sec)
 
mysql> select * from xy011 where match(remark) against('篮球');
+------+-------+------+------+--------------------------------+
| id   | name  | age  | sex  | remark                         |
+------+-------+------+------+--------------------------------+
|    1 | zhang |   25 | 女   | 足球，篮球，台球               |
|    3 | li    |   26 | 女   | 篮球，羽毛球，乒乓球           |
|    4 | si    |   26 | 男   | 篮球，乒乓球，足球             |
+------+-------+------+------+--------------------------------+
3 rows in set (0.00 sec)
 
mysql> select * from xy011 where match(remark) against('+.篮. -乒乓球' in boolean mode);  //包含篮球不包含乒乓球
+------+-------+------+------+--------------------------+
| id   | name  | age  | sex  | remark                   |
+------+-------+------+------+--------------------------+
|    1 | zhang |   25 | 女   | 足球，篮球，台球         |
+------+-------+------+------+--------------------------+
1 row in set (0.00 sec)
 
mysql> select * from xy011 where match(remark) against('+篮球 +乒乓球' inn boolean mode);
//包含篮球包含乒乓球
+------+------+------+------+--------------------------------+
| id   | name | age  | sex  | remark                         |
+------+------+------+------+--------------------------------+
|    3 | li   |   26 | 女   | 篮球，羽毛球，乒乓球           |
|    4 | si   |   26 | 男   | 篮球，乒乓球，足球             |
+------+------+------+------+--------------------------------+
2 rows in set (0.00 sec)
 
mysql> select * from xy011 where match(remark) against('乒乓球 吃饭' inboolean mode);  //要么包含乒乓球，要么包含吃饭
+------+------+------+------+--------------------------------+
| id   | name | age  | sex  | remark                         |
+------+------+------+------+--------------------------------+
|    2 | san  |   26 | 男   | 吃饭，足球，乒乓球             |
|    3 | li   |   26 | 女   | 篮球，羽毛球，乒乓球           |
|    4 | si   |   26 | 男   | 篮球，乒乓球，足球             |
+------+------+------+------+--------------------------------+
3 rows in set (0.00 sec)
 
mysql>

【6】删除索引：

drop index 索引名 on 表名; #这两种方法都可以用来删除普通索引、唯一索引、全文索引、组合索引
alter table 表名 drop index 索引名;

alter table 表名 drop primary key; #删除主键索引

（1）直接删除索引

drop index 索引名 on 表名;

（2）修改表方式删除索引

alter table 表名 drop index 索引名;

（3）删除主键索引

alter table 表名 drop primary key;

mysql> desc xy107;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   | PRI | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| sex   | char(2)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql> alter table xy107 drop primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc xy107;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| sex   | char(2)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql>

mysql> show index from xy011;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| xy011 |          1 | remark   |            1 | remark      | NULL      |           4 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
 
mysql>

三、遇到 select 查询语句执行速度慢该怎么办？

1）升级 CPU 内存硬盘硬件性能
2）对 MySQL 配置进行优化
3）对查询语句的结构进行优化，比如将嵌套子查询优化成表连接查询；或连接表时，可以先用where条件对表进行过滤，然后做表连接
4）进行索引优化：先使用 explain 分析 select 语句（看 key rows type 字段），判断这个查询语句是否正确的使用了索引
再根据查询语句中的 where 条件字段建立相应的单列索引或者多列组合索引（多列组合索引要满足最左原则）

四、事务的概念

（1）事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体，一起向系统提交或撤销操作请求，即这一组数据库命令行。要么都执行，要么都不执
（2）事务是一个不可分割的工作逻辑单元。在数据库系统上执行并发操作时，事务是最小的控制单元。
（3）事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。
（4）事务通过事务的整体性以保证数据的一致性。
（5）事务能够提高在向表中更新和插入信息期间的可靠性。

它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位

五、事务的ACID特点

ACID：是指在可靠数据库管理系统（DBMS）中，事务(transaction)应该具有的四个特性：

原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

原子性：指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

事务是一个完整的操作，事务的各元素是不可分的。
事务中的所有元素必须作为一个整体提交或回滚。
如果事务中的任何元素失败，则整个事务将失败。

案例：
A给B转帐100元钱的时候只执行了扣款语句，就提交了，此时如果突然断电，A账号已经发生了扣款，B账号却没收到加款，在生活中就会引起纠纷。这种情况就需要事务的原子性来保证事务要么都执行，要么就都不执行。

一致性：指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

当事务完成时，数据必须处于一致状态。
在事务开始前，数据库中存储的数据处于一致状态。
在正在进行的事务中，数据可能处于不一致的状态。
当事务成功完成时，数据必须再次回到已知的一致状态。

案例：
对银行转帐事务，不管事务成功还是失败，应该保证事务结束后表中A和B的存款总额跟事务执行前一致。

隔离性：指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。
也就是说并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。

当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：

（1）脏读：当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

（2）不可重复读：指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）

（3）幻读：一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有一个没有修改的数据行，就好象发生了幻觉一样。

（4）丢失更新：两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。

事务的隔离级别决定了事务之间可见的级别。
MySQL事务支持如下四种隔离，用以控制事务所做的修改，并将修改通告至其它并发的事务：

注意： mysql默认的事务处理级别是可重复读repeatable read ，而Oracle和SQL Server是提交读read committed 。

持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
指不管系统是否发生故障，事务处理的结果都是永久的。
一旦事务被提交，事务的效果会被永久地保留在数据库中。

总结：在事务管理中，原子性是基础，隔离性是手段，一致性是目的，持久性是结果。

六、事务隔离的操作

事务隔离级别的作用范围分为两种：

全局级：对所有的会话有效
会话级：只对当前的会话有效

（1）设置隔离级别：

set global transaction isolation level 隔离级别名称;      
 
#全局级隔离级别，可在所有会话有效，当前会话需要重新登录方可有效
 
 
set session transaction isolation level 隔离级别名称;    
 
#会话级隔离级别，仅在当前会话中立即有效

（2）查看隔离级别：

1.查询全局事务隔离级别

show global variables like '%isolation%';
 
SELECT @@global.tx_isolation;

2.查询会话事务隔离级别

show session variables like '%isolation%';
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

3.设置全局事务隔离级别

set global transaction isolation level read committed;
set @@global.tx_isolation='read-committed';   #重启服务后失效

4.设置会话事务隔离级别

set session transaction isolation level repeatable read;
set @@session.tx_isolation='repeatable-read';  #仅在当前会话中立即有效

（3）事务控制语句：

BEGIN 或 START TRANSACTION：显式地开启一个事务。
COMMIT 或 COMMIT WORK：提交事务，并使已对数据库进行的所有修改变为永久性的。
ROLLBACK 或 ROLLBACK WORK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
SAVEPOINT S1：使用 SAVEPOINT 允许在事务中创建一个回滚点，一个事务中可以有多个 SAVEPOINT；“S1”代表回滚点名称。
ROLLBACK TO [SAVEPOINT] S1：把事务回滚到标记点。

（4）事务管理操作：

begin;	#显式的开启一个事务
.... insert into update 表 set delete from	#事务性操作
savepoint XX;	#在事务中创建回滚点
rollback to XX;	#在事务中回滚到指定的回滚点位置
commit; 或 rollback;	#提交或回滚结束事务

begin;

开启一个事务，修改内容但不保存，退出重新登陆，数据未改变

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xy                 |
+--------------------+
5 rows in set (0.00 sec)
 
mysql> use xy;
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_xy |
+--------------+
| xy011        |
| xy101        |
| xy1010       |
| xy102        |
| xy103        |
| xy104        |
| xy105        |
| xy106        |
| xy107        |
| xy108        |
| xy109        |
| xy88         |
+--------------+
12 rows in set (0.00 sec)

mysql> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
|    2 | yf   |   88 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update xy108 set age=age-2 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   18 | 女   |
|    2 | yf   |   88 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql> quit
Bye
[root@l5 ~]# 
[root@l5 ~]# mysql -uroot -pabc123;
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.44 Source distribution
 
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> use xy;
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> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
|    2 | yf   |   88 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql>

begin;+commit;

开启事务，修改完提交事务，数据得到永久更改

mysql> use xy
Database changed
mysql> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
|    2 | yf   |   88 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update xy108 set age=age-2 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   18 | 女   |
|    2 | yf   |   88 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql>

rollback;

开启一个事务，修改完数据进行回滚操作，会结束事务，并撤销未提交的修改

mysql> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   18 | 女   |
|    2 | yf   |   88 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update xy108 set age=age+22 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> update xy108 set age=age-20 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   40 | 女   |
|    2 | yf   |   68 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   18 | 女   |
|    2 | yf   |   88 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql>

savepoint s1; 与 rollback to s1;

开启一个事务，修改数据后在此处创建一个回滚点无论之后如何修改，返回该回滚点，数据就会恢复

mysql> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   18 | 女   |
|    2 | yf   |   88 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update xy108 set age=age-80 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> update xy108 set age=age+2 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> savepoint xy1081;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
|    2 | yf   |    8 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql>

mysql> update xy108 set age=age-5 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> update xy108 set age=age-5 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   15 | 女   |
|    2 | yf   |    3 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql> rollback to xy108;
ERROR 1305 (42000): SAVEPOINT xy108 does not exist
mysql> rollback to xy1081;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from xy108;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
|    2 | yf   |    8 | 女   |
|    3 | tmt  |   99 | 女   |
|    4 | yzl  |  800 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql>

1、使用 set 设置控制事务

set global/sesion autocommit = 0/1 #golbal全局级别，session会话级别，0关闭自动提交，1开启自动提交
show global/session variables like 'autocommit';

创建测试表

mysql> select * from xy109;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
|    2 | mm   |   21 | 男   |
|    3 | ll   |   21 | 男   |
|    4 | xx   |   23 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql>

默认情况下autocommit是开启的，会自动保持修改的内容

mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)
 
mysql> show  session variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql>

关闭当前会话的自动提交

mysql> set session autocommit=0;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show  session variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql>

因为关闭了自动提交功能，修改内容后不会自动保存

mysql> select * from xy109;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
|    2 | mm   |   21 | 男   |
|    3 | ll   |   21 | 男   |
|    4 | xx   |   23 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql> update xy109 set age=age-10 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from xy109;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
|    2 | mm   |   11 | 男   |
|    3 | ll   |   21 | 男   |
|    4 | xx   |   23 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql>

[root@l5 ~]# mysql -uroot -pabc123;
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.44 Source distribution
 
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> use xy;
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> select * from xy109;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
|    2 | mm   |   21 | 男   |
|    3 | ll   |   21 | 男   |
|    4 | xx   |   23 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql>

如何在关闭自动提交后保存修改内容

mysql> select * from xy109;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   20 | 女   |
|    2 | mm   |   21 | 男   |
|    3 | ll   |   21 | 男   |
|    4 | xx   |   23 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql> update xy109 set age=age-10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> update xy109 set age=age-10 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from xy109;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   10 | 女   |
|    2 | mm   |   11 | 男   |
|    3 | ll   |   21 | 男   |
|    4 | xx   |   23 | 女   |
+------+------+------+------+
4 rows in set (0.00 sec)
 
mysql> commit;  //手动提交
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit
Bye

[root@l5 ~]# mysql -uroot -pabc123;
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.44 Source distribution
 
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> use xy;
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> select * from xy109;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | my   |   10 | 女   |
|    2 | mm   |   11 | 男   |
|    3 | ll   |   21 | 男   |
|    4 | xx   |   23 | 女   |
+------+------+------+------+
4 rows in set (0.01 sec)
 
mysql>

（5）自动提交事务：

set global/session autocommit = 0/1

#global全局级别，session会话级别，0关闭自动提交，1开启自动提交

show global/session variables like 'autocommit';

七、存储引擎：

（1）定义：

存储引擎是MySQL数据库的组件，负责执行实际的数据IO操作（数据的存储和提取）。工作在文件系统之上，数据库的数据会先传输到存储引擎，再按照存储引擎的存储格式保存到文件系统。

（2）常用的存储引擎：

InnoDB MyISAM

（3）MyISAM 和 InnoDB 的区别？

	MyISAM：	InnoDB：
	不支持事务、外键约束；支持全文索引	支持事务、外键约束；也支持全文索引；
	锁定类型只支持表级锁定；适合单独的查询和插入的操作；	锁定类型支持行级锁定（在全表扫描时仍会表级锁定）；
	读写会相互阻塞；	读写并发能力较好；
	硬件资源占用较小	缓存能力较好可以减少磁盘IO的压力；
	数据文件和索引文件是分开存储的，存储成三个文件：表结构文件.frm、数据文件.MYD、索引文件.MYI	数据文件也是索引文件，存储成：表结构文件.frm、表空间文件.ibd
使用场景：	适用于不需要事务支持，单独的查询或插入数据的业务场景	适用于需要事务支持，数据一致性要求较高，数据会频繁更新，读写并发高的业务场景

（4）InnoDB行锁与索引的关系：

InnoDB的行级锁是通过给索引项加锁来实现的。如果对没有索引的字段进行操作会使用全表扫描并表级锁定。

（5）MySQL 查询数据的执行过程：

客户端向 MySQL 服务器发送一条查询请求，连接器负责处理连接，并进行身份验证和权限控制。
MySQL 先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果；否则使用查询解析器进行SQL语句解析、预处理，再由优化器生成对应的执行计划。
MySQL 根据执行计划，调用存储引擎来执行查询。
将结果返回给客户端，同时缓存查询结果。

（6）查看存储引擎：

show create table 表名;
show table status [from 库名] where name = '表名';

1. 查看系统支持的存储引擎

show engines;

2. 查看表使用的存储引擎

方法一：

show table status from 库名 where name='表名'\G

mysql> show table status from xy where name='xy108';
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| xy108 | InnoDB |      10 | Dynamic    |    4 |           4096 |       16384 |               0 |        32768 |         0 |           NULL | 2024-06-28 16:41:48 | 2024-06-29 11:33:47 | NULL       | utf8_general_ci |     NULL |                |         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)
 
mysql>

mysql> show table status from xy where name='xy108'\G
*************************** 1. row ***************************
           Name: xy108
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 32768
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2024-06-28 16:41:48
    Update_time: 2024-06-29 11:33:47
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
 
mysql>

方法二：

use 库名;

show create table 表名;

mysql> use xy;
Database changed
mysql> show create table xy108;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| xy108 | CREATE TABLE "xy108" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(22) DEFAULT NULL,
  KEY "tan_index" ("sex","age","name"),
  FULLTEXT KEY "108_name" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> 
mysql> show create table xy108\G;
*************************** 1. row ***************************
       Table: xy108
Create Table: CREATE TABLE "xy108" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(22) DEFAULT NULL,
  KEY "tan_index" ("sex","age","name"),
  FULLTEXT KEY "108_name" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
ERROR: 
No query specified
 
mysql>

（7）存储引擎管理操作：

alter table 表名 engine=innodb/myisam; #针对已存在的表修改存储引擎

create table 表名 (....) engine=innodb/myisam; #新建表时指定存储引擎

set global/session default_storage_engine=innodb/myisam; #设置默认存储引擎

vim /etc/my.cnf
default_storage_engine=INnoDB/MyISAM

修改存储引擎

通过 alter table 修改

use 库名;

alter table 表名 engine=引擎名

例：将xy108表的引擎修改为MyISAM

use xy;
	##使用xy库
	
alter table xy108 engine=MyISAM;
	##修改account表的引擎为MyISAM

mysql> use xy;
Database changed
mysql> alter table xy108 engine=MyISAM;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> 
mysql> show create table xy108;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| xy108 | CREATE TABLE "xy108" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(22) DEFAULT NULL,
  KEY "tan_index" ("sex","age","name"),
  FULLTEXT KEY "108_name" ("name")
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql>

通过修改 /etc/my.cnf 配置文件，指定默认存储引擎并重启服务

vim /etc/my.cnf
	......
	[mysqld]
	......
	default-storage-engine=引擎名
 
systemctl restart mysqld
##注意：此方法只对修改了配置文件并重启mysql服务后新创建的表有效，已经存在的表不会有变更。

例：将默认的引擎修改为MyISAM

## MySQL内
show engines;
##
##系统命令
vim /etc/my.cnf
	......
	[mysqld]
	......
	default-storage-engine=MyISAM
		##默认的引擎为MyISAM
 
systemctl restart mysqld
##
## MySQL内
show engines;
##

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

[root@l5 ~]# systemctl restart mysqld.service
[root@l5 ~]# mysql -uroot -pabc123
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
 
mysql>

通过 create table 创建表时指定存储引擎

USER 库名;
 
CREATE TABLE 表名(字段1 数据类型,...) ENGINE=引擎名;

例：在xy库中创建存储引擎为MyISAM的home表

use xy
	##使用xy库
create table home(id int, name varchar(50)) engine=MyISAM;
	##创建home表字段1为id，字段2为name
 
show create table home;
	##查看home表属性

mysql> use xy;
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> create table home(id int, name varchar(50)) engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show create table home;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| home  | CREATE TABLE "home" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql>

八、死锁：

（1）定义：

死锁是指两个或多个事务在同一个资源上相互占用，并请求对方锁定的资源，从而导致相互阻塞的现象。

（2）如何避免死锁？

1）设置事务的锁等待超时时间 innodb_lock_wait_timeout
2）设置开启死锁检测功能 innodb_deadlock_detect
3）为表建立合理的索引，减少表锁发生的概率
4）如果业务允许，可以降低隔离级别，比如选用提交读 Read Committed 隔离级别，从而避免间隙锁导致死锁
5）建议开发人员尽量使用更合理的业务逻辑，比如多表操作时以固定顺序访问表，尽量避免同时锁定多个资源
6）建议开发人员尽量保持事务简短，减少对资源的占用时间和占用范围
7）建议开发人员在读多血少的场景下采用乐观锁机制

尽头海水逆流

关注

14
点赞
踩
15

收藏

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

索引是一个排序的列表，包含索引字段的值和其对应的行记录数据所在的物理地址存储引擎是MySQL数据库的组件，负责执行实际的数据IO操作（数据的存储和提取）。工作在文件系统之上，数据库的数据会先传输到存储引擎，再按照存储引擎的存储格式保存到文件系统。死锁是指两个或多个事务在同一个资源上相互占用，并请求对方锁定的资源，从而导致相互阻塞的现象。
复制链接

扫一扫