目录
一、索引的概念
1、数据库索引
- 是一个排序的列表，存储着索引值和这个值所对应的物理地址
- 无须对整个表进行扫描，通过物理地址就可以找到所需数据
- 是表中一列或者若干列值排序的方法
- 需要额外的磁盘空间
索引表
2、索引的作用
- 数据库利用各种快速定位技术，能够大大加快查询速率当表很大或查询涉及到多个表时，可以成干上万倍地提高查询速度
- 可以降低数据库的IO成本，并且还可以降低数据库的排序成本
- 通过创建唯一性索引保证数据表数据的唯一性可以加快表与表之间的连接
- 在使用分组和排序时，可大大减少分组和排序时间
3、索引的副作用:
- 案引需要占用额外的盘空间。
- 对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。
- 面noD引的表数据文件本身就是索引文件
- 更新一个包含家引的表需要比更新一个没有案引的表花费更多的时问，这是由于家引本分也需要更新。因此，理想的做法是仪仪在常常被搜索的列(以及表)上而创建索引
4、创建索引的原则依据
索引虽可以提升数据库查询的速度，但并不是任何情况下都适合创建索引，因为索引本身会消耗系统资源，再有索引的情况下，数据库会先进行索引查询
二、索引的分类和创建
1、普通索引（最基本的索引类型，没有唯一性之类的限制）
方法一：直接创建
create index 索引名 on table 表名 （列名（length））
注：length是可选项
例：
create index name_index on eee (name);
mysql> show tables;
+-------------------------------+
| Tables_in_student_information |
+-------------------------------+
| bbb |
| ccc |
| ddd |
| eee |
+-------------------------------+
4 rows in set (0.00 sec)
mysql> desc eee;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| b_id | varchar(20) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | varchar(2) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> create index name_index on eee (name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc eee;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| b_id | varchar(20) | NO | PRI | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| sex | varchar(2) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
方法二：修改表方式创建
alter table 表名 add index 索引名 (列名);
mysql> desc a2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table a2 add index name_index (name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc a2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
方法三：创建时指定索引
crrate table 表名 ( 字段1 数据类型,字段2 数据类型, index 索引名 (列名));
create table a3 (id int, name varchar(20), age int,sex char(10), index
mysql> create table a3 (id int, name varchar(20), age int,sex char(10), index name_index(name));
Query OK, 0 rows affected (0.01 sec)
mysql> desc a3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2、唯一性索引
与“普通索引”基本相同，区别是索引列的所有值只能出现一次，即必须唯一。
方法一：直接创建
create unique index 索引名 on 表名(字段);
mysql> create unique index id_index on a3(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc a3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show create table a3;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a3 | CREATE TABLE "a3" (
"id" int(11) DEFAULT NULL,
"name" varchar(20) DEFAULT NULL,
"age" int(11) DEFAULT NULL,
"sex" char(10) DEFAULT NULL,
UNIQUE KEY "id_index" ("id"),
KEY "name_index" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)方法二：修改表方式创建
alter table 表名 add unique 索引名(字段);
mysql> alter table a2 add unique id_index(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table a2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a2 | CREATE TABLE "a2" (
"id" int(11) DEFAULT NULL,
"name" varchar(20) DEFAULT NULL,
"age" int(11) DEFAULT NULL,
"sex" char(10) DEFAULT NULL,
UNIQUE KEY "id_index" ("id"),
KEY "name_index" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)方法三：创建时指定索引
create table 表名 (.... , unique 索引名(字段));
mysql> create table a4 (id int, name varchar(20), sex varchar(10), unique id_index(id));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table a4;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a4 | CREATE TABLE "a4" (
"id" int(11) DEFAULT NULL,
"name" varchar(20) DEFAULT NULL,
"sex" varchar(10) DEFAULT NULL,
UNIQUE KEY "id_index" ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)3、主键索引
方法一：
create table 表名 (.... , primary key(字段));
mysql> create table a5 (id int, name varchar(20), sex varchar(10), primary key (id,name));
Query OK, 0 rows affected (0.01 sec)
mysql> desc a5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
| sex | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
方法二：
alter table 表名 add primary key(字段);
mysql> desc a6;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> alter table a6 add primary key(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc a6;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
4、组合索引（单列索引与多列索引）
可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为 select 语句的 where 条件是依次从左往右执行的，所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。
CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));
select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';
5、全文索引
适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。在 MySQL5.6 版本以前
FULLTEXT 索引仅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。
方法一：直接创建索引
create fulltext index 索引名 on 表名 (列名);
使用全文索引查询
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');
mysql> create fulltext index remark_index on a8 (remark);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc a8;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
| remark | text | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from a8;
+------+--------+------+---------------+
| id | name | sex | remark |
+------+--------+------+---------------+
| 3 | wangwu | 女 | this is vvvip |
| 2 | lisi | 男 | this is vip |
+------+--------+------+---------------+
2 rows in set (0.00 sec)
mysql> select * from a8 where match(remark) against('vvvip');
+------+--------+------+---------------+
| id | name | sex | remark |
+------+--------+------+---------------+
| 3 | wangwu | 女 | this is vvvip |
+------+--------+------+---------------+
1 row in set (0.00 sec)方法二：修改表方式创建
mysql> select * from a7;
+------+----------+------+--------------+
| id | name | sex | remark |
+------+----------+------+--------------+
| 1 | zhangsan | 男 | this is vip |
| 2 | 李四 | 男 | this is vvip |
| 2 | 王五 | 男 | this is vip |
+------+----------+------+--------------+
3 rows in set (0.00 sec)
mysql> show create table a7;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a7 | CREATE TABLE "a7" (
"id" int(11) DEFAULT NULL,
"name" varchar(20) DEFAULT NULL,
"sex" varchar(10) DEFAULT NULL,
"remark" text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table a7 add fulltext remark_index (remark);
Query OK, 0 rows affected, 1 warning (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc a7;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
| remark | text | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from a7 where match(remark) against('vvip');
+------+--------+------+--------------+
| id | name | sex | remark |
+------+--------+------+--------------+
| 2 | 李四 | 男 | this is vvip |
+------+--------+------+--------------+
1 row in set (0.00 sec)方法三：创建表的时候指定索引
CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));
#数据类型可以为 CHAR、VARCHAR 或者 TEXT
mysql> create table a9 (id int, name varchar(20), sex varchar(10), remark text, fulltext remark_fulltext(remark)));
Query OK, 0 rows affected (0.04 sec)
mysql> desc a9;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
| remark | text | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
全文索引只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引，只支持InnoDB和MyISAM引擎。
全文索引只支持英文全文索引，不支持中文全文索引，需要使用ngram全文解析器，用来支持中文全文索引。
vim /etc/my.cnf
[mysqld]
ngram_token_size=2 #指定查询的单词的最小字数create fulltext index 索引名 on 表名(字段) WITH PARSER ngram;;
alter table 表名 add fulltext 索引名(字段) WITH PARSER ngram;;
create table 表名 (.... , fulltext 索引名(字段) WITH PARSER ngram);
6、删除索引
drop index 索引名 on 表名;
alter table 表名 drop index 索引名;
mysql> show create table a8;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a8 | CREATE TABLE "a8" (
"id" int(11) DEFAULT NULL,
"name" varchar(20) DEFAULT NULL,
"sex" varchar(10) DEFAULT NULL,
"remark" text,
FULLTEXT KEY "remark_index" ("remark")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> drop index remark_index on a8;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table a8;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a8 | CREATE TABLE "a8" (
"id" int(11) DEFAULT NULL,
"name" varchar(20) DEFAULT NULL,
"sex" varchar(10) DEFAULT NULL,
"remark" text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
删除主键索引：
alter table 表名 drop primary key;
7、查看索引
show create table 表名;
show index from 表名;
show keys from 表名;
总结：
索引类型：
普通索引 create index 索引名 on 表名(字段(长度));
alter table 表名 add index 索引名(字段);
create table 表名 (.... , index 索引名(字段));唯一索引 create unique index 索引名 on 表名(字段);
alter table 表名 add unique 索引名(字段);
create table 表名 (.... , unique 索引名(字段));主键索引 alter table 表名 add primary key(字段);
create table 表名 (.... , primary key(字段));多列组合索引 create index 索引名 on 表名(字段1, 字段2, ....);
alter table 表名 add index 索引名(字段1, 字段2, ....);
select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ; #查询语句使用 and 做逻辑运算符时，字段顺序要与创建多列索引的字段顺序一致（要满足最左原则）全文索引 create fulltext index 索引名 on 表名(字段);
alter table 表名 add fulltext 索引名(字段);
create table 表名 (.... , fulltext 索引名(字段));
全文索引可用作于模糊查询 select 字段列表 from 表名 where match(字段) against('单词'
三、事务
1、事务的概念
- 是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行
- 是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
- 适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等
- 通过事务的整体性以保证数据的一致性
2、事务的ACID特点
（1）原子性(Atomicity)
- 事务是一个完整的操作，事务的各元素是不可分的
- 事务中的所有元素必须作为一个整体提交或回滚。
- 如果事务中的任何元素失败，则整个事务将失败
（2）致性(Consistency)
- 当事务完成时，数据必须处于一致状态
- 在事务开始前，数据库中存储的数据处于一致状态
- 在正在进行的事务中，数据可能处于不一致的状态
- 当事务成功完成时，数据必须再次回到已知的一致状态
当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：
（1）脏读：当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
如上图所示，事务A中 取出100元后，还剩900元，之后事务B查询余额，看到账户为900，但事务A撤销取出100元的操作，此时事务B的过程中依然显示余额为900，之后再存入100变成1000，但是总额丢失了100。
（2）不可重复读：指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）
如上图所示，事务A第一次进行查询时余额为1000，之后事务B取出1000，，事务A再查询时显示余额为0，即两次读到的数据不一致。
（3）幻读：一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有一个没有修改的数据行，就好象发生了幻觉一样。
（4）丢失更新：两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。
隔离性
- 指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间
- 对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
- 修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。
- 也就是说并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。
事务的隔离级别
事务的隔离级别决定了事务之间可见的级别。
MySQL事务支持如下四种隔离，用以控制事务所做的修改，并将修改通告至其它并发的事务：
（1）未提交读(Read Uncommitted（RU）)：
允许脏读，即允许一个事务可以看到其他事务未提交的修改。
（2）提交读(Read Committed（RC）)：
允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。防止脏读。
（3）可重复读(Repeatable Read（RR）)：---mysql默认的隔离级别
确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改。可以防止脏读和不可重复读。
（4）串行读(Serializable)：---相当于锁表
完全串行化的读，将一个事务与其他事务完全地隔离。每次读都需要获得表级共享锁，读写相互都会阻塞。可以防止脏读，不可重复读取和幻读，(事务串行化)会降低数据库的效率。
事务隔离级别的作用范围
● 全局级：对所有的会话有效
● 会话级：只对当前的会话有效
查询全局事务隔离级别：
show global variables like '%isolation%';
或
SELECT @@global.tx_isolation;
查询会话事务隔离级别：
show session variables like '%isolation%';
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
设置会话事务隔离级别：
set session transaction isolation level repeatable read;
设置全局事务隔离级别：
set global transaction isolation level read committed;
持久性
- 在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
- 指不管系统是否发生故障，事务处理的结果都是永久的。
- 一旦事务被提交，事务的效果会被永久地保留在数据库中。
总结：在事务管理中，原子性是基础，隔离性是手段，一致性是目的，持久性是结果。
3、事务控制语句
begin; #显式的开启一个事务
.... insert into update 表 set delete from #事务性操作
savepoint XX; #在事务中创建回滚点
rollback to XX; #在事务中回滚到指定的回滚点位置
commit; 或 rollback; #提交或回滚 结束事务
例一：开启和关闭事务
例二：事务未完成直接退出
例三：回滚回结束用户的事务
例四：设置与返回回滚点
4、自动提交事务
set global/session autocommit = 0/1 #global全局级别，session会话级别，0关闭自动提交，1开启自动提交
show global/session variables like 'autocommit';
四、存储引擎
1、存储引擎概念介绍
- MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎
- 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式
- MySQL常用的存储引擎
- MyISAM
- InnoDB
（2）存储引擎概念介绍
MySQL数据库中的组件，负责执行实际的数据IO操作MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储
2、MyISAM的特点介绍
- MyISAM不支持事务，也不支持外键约束，只支持全索引，数据文件和索引文件是分开保存的
- 开访问速度快，对事务完整性没有要求
- MyISAM 适合查询、插入为主的应用
- MyISAM在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名分别为:
- .frm 文件存储表结构的定义
- 数据文件的扩展名为.MYD(MYData)
- 索引文件的扩展名是 .MYI(MYIndex)
(2)MyISAM的特点介绍
- 表级锁定形式，数据在更新时锁定整个表
- 数据库在读写过程中相互阻塞
- 会在数据写入的过程阻塞用户数据的读取
- 也会在数据读取的过程中阻塞用启的数据写入
- 数据单独写入或读取，速度过程较快且占用资源相对少
- MyIAM支持的存储格式
- 静态表
- 动态表
- 压缩表
3、MyISAM适用的生产场景举例
- 公司业务不需要事务的支持
- 单方面读取或写入数据比较多的业务
- MylSam存储数据读取都比较频繁场景不适合
- 使用读写并发访问相对较低的业务
- 数据修改相对较少的业务
- 对数据业务一致性要求不是非常高的业务
- 服务器硬件资源相对比较差
4、InnoDB特点介绍
- 支持事务，支持4个事务隔离级别
- MySQL从5.5.5版本开始，默认的存储引擎为InnoDB
- 读写阻塞与事务隔离级别相关
- 能非常高效的缓存索引和数据
- 表与主键以簇的方式存储
- 支持分区、表空间，类似oracle数据库
- 支持外键约束，5.5前不支持全文索引，5.5后支持全文索引
- 对硬件资源要求还是比较高的场合
- 行级锁定，但是全表扫描仍然会是表级锁定，如
- 29136update table set a=1 where user like "%zhang%';
注：
- 使用like进行模糊查询时，会进行全表扫描，锁定整个表。
- 对没有创建索引的字段进行查询，也会进行全表扫描锁定整个表。
- 使用索引进行查询，则是行级锁定。
5、InnoDB适用生产场景分析
- 业务需要事务的支持。
- 行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成。
- 业务数据更新较为频繁的场景。如：论坛，微博等。
- 业务数据一致性要求较高。如：银行业务。
- 硬件设备内存较大，利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO的压力。
6、企业选择存储引擎依据
1、需要考虑每个存储引擎提供了哪些不同的核心功能及应用场景
2、支持的字段和数据类型。
所有引擎都支持通用的数据类型。
但不是所有的引擎都支持其它的字段类型，如二进制对象
3、锁定类型:不同的存储引擎支持不同级别的锁定
表锁定: MyISAM 支持
行锁定:InnoDB 支持
4、索引的支持
建立索引在搜索和恢复数据库中的数据时能显著提高性能
不同的存储引擎提供不同的制作索引的技术
有些存储引擎根本不支持索引
5、事务处理的支持
提高在向表中更新和插入信息期间的可靠性
可根据企业业务是否要支持事务选择存储引擎
7、MyISAM 和 InnoDB 的区别
MyISAM：不支持事务、外键约束；支持全文索引；锁定类型只支持表级锁定；适合单独的查询和插入的操作；读写会相互阻塞；硬件资源占用较小；数据文件和索引文件是分开存储的，存储成三个文件：表结构文件.frm、数据文件.MYD、索引文件.MYI
使用场景：适用于不需要事务支持，单独的查询或插入数据的业务场景
InnoDB：支持事务、外键约束；也支持全文索引；锁定类型支持行级锁定（在全表扫描时仍会表级锁定）；读写并发能力较好；缓存能力较好可以减少磁盘IO的压力；数据文件也是索引文件，存储成：表结构文件.frm、表空间文件.ibd
使用场景：适用于需要事务支持，数据一致性要求较高，数据会频繁更新，读写并发高的业务场景
InnoDB行锁与索引的关系：
InnoDB的行级锁是通过给索引项加锁来实现的。如果对没有索引的字段进行操作会使用全表扫描并表级锁定。
五、MySQL 查询数据的执行过程
1）客户端向 MySQL 服务器发送一条查询请求，连接器负责处理连接，并进行身份验证和权限控制。
2）MySQL 先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果；否则使用查询解析器进行SQL语句解析、预处理，再由优化器生成对应的执行计划。
3）MySQL 根据执行计划，调用存储引擎来执行查询。
4）将结果返回给客户端，同时缓存查询结果。
六、存储引擎管理操作
1、查看存储引擎
show create table 表名;
show table status [from 库名] where name = '表名';
2、修改存储引擎
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
修改默认存储引擎
或者
vim /etc/my.cnf
3、行锁定
InnoDB的行级锁是通过给索引项加锁来实现的。如果对没有索引的字段进行操作会使用全表扫描并表级锁定。
七、死锁问题
是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象，若无外力作用，事务都将无法继续运行。此时称系统处于死锁状态或系统产生了死锁。
如何避免死锁？
1）设置事务的锁等待超时时间 innodb_lock_wait_timeout
2）设置开启死锁检测功能 innodb_deadlock_detect
3）为表建立合理的索引，减少表锁发生的概率
4）如果业务允许，可以降低隔离级别，比如选用 提交读 Read Committed 隔离级别，从而避免间隙锁导致死锁
5）建议开发人员尽量使用更合理的业务逻辑，比如多表操作时以固定顺序访问表，尽量避免同时锁定多个资源
6）建议开发人员尽量保持事务简短，减少对资源的占用时间和占用范围
7）建议开发人员在读多血少的场景下采用乐观锁机制
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
