MySQL--索引、事务、存储引擎

索引

索引种类

普通索引

• 最基本的索引类型，没有唯一性之类的限制
• 三种创建普通索引的方式

mysql> create table wuliao (id int(2),fruit char(10));
Query OK, 0 rows affected (0.02 sec)
##方式一：create直接创建
##语法：create index 索引名 on 表名(属性(length));  length可选
mysql> create index id_index on wuliao(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
##MUL表示索引
mysql> describe wuliao;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(2)   | YES  | MUL | NULL    |       |
| fruit | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
##方式二：alter
##语法：alter table 表名 add index index_name (属性（length）)
mysql> mysql> alter table wuliao add index id_name (id);
##方式三：在创建表时，创建索引
mysql>create table wuliao (.....,index index_name (title(length));

唯一性索引

• 与”普通索引” 基本相同
• 与普通索引的区别是索引列的所有值只能出现一次，即必须唯一，可以为空值

##方式一：
mysql> create unique index name_name on score (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe score;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(2)       | NO   | PRI | NULL    | auto_increment |
| name    | char(10)     | NO   | UNI | NULL    |                |
| score   | decimal(5,1) | YES  |     | NULL    |                |
| address | varchar(50)  | YES  |     | 未知    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
##方式二：alter
mysql> alter table wuliao add unique fruit_name (friut);
##方式三：
创建表时添加

主键索引

• 是一种特殊的唯一索引，指定为”PRIMARY KEY”
• 一个表只能有一个主键，不允许有空值

##在创建表时创建,primary key
mysql> create table address (id int(4) not null primary key auto_increment,name char(10),address varchar(50) default '未知');
Query OK, 0 rows affected (0.00 sec)
##在后面申明
mysql> create table car (id int(4) not null auto_increment,name char(10),car varchar(10),primary key(id));
Query OK, 0 rows affected (0.01 sec)

mysql> describe car;

组合索引（单列索引与多列索引）

• 可以是单列上创建的索引，也可以是在多列上创建的索引
• 最左原则，从左往右依次执行

mysql> create table user(name varchar(9),age int(3),sex tinyint(1),index user(name,age,sex));
Query OK, 0 rows affected (0.01 sec)

全文索引fulltest

mysql> alter table address add fulltext index addr_index(address);

删除索引

## 语法
drop idnex 索引名 on 表名
alter table 表名 drop index 索引名
##
mysql> drop index addr_name on address;
mysql> alter table address drop index addr_index;

查看索引

##\G表示竖着排列
mysql> show index from score\G
mysql> show index from score;
mysql> show keys from address;
mysql> show keys from address\G

创建索引的依据

• 表的主键、外键必须有索引
• 记录数超过300行的表应该由索引
• 经常与其他表进行连接的表，在连接字段上应该建立索引
• 唯一性太差的字段不适合建立索引
• 更新太频繁的字段不适合创建索引
• 经常出现在where子句中的字段，特别是大表字段，应该建立索引
• 索引应该建立在选择性高的字段上
• 索引应该建立在小字段上，对应大的文本字段甚至超长字段，不要建立索引

事务

事务的概念

• 是一种机制、一个操作序列，包含了-组数据库操作命令,并且把所有的命令作为一-个整体-起向系统提交或撤销操作请求，即这一-组数据库命令要么都执行，要么都不执行
• 是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元
• 适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等
• 通过事务的整体性以保证数据的一致性

事务的ACID特点

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

事务控制语句

• begin表示事务的开始，如果回滚也是到begin的状态
• commit 提交 一旦提交 不可回滚
• rollback 回滚
• savepoint 名称 存档点
• release savepoint名称 删除存档点
• rollback to 回滚到某个点
• set transaction设置事务

配置示例

mysql事务在默认情况下set autocommit=1 表示自动提交，也就表示我们每条语句都相当于一个事务，也就不需要我们去敲commit了,所以也就无法回滚

##先在一个数据库里创建一张sore表，再插入一条记录，在此基础上操作
mysql> create table score (id int(4) not null primary key auto_increment,name char(10) not null,score decimal(5,1),address varchhar(50) default '未知')engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into score (id,name,score,address) values(1,'zhangsan',90.5,nanjing);
##插入一条数据，并查看
mysql> insert into score (id,name,score,address) values(2,'lisi',80,fujiang);
mysql> select * from score;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan |  90.5 | nanjing |
|  2 | lisi     |  80.0 | fujiang |
+----+----------+-------+---------+
2 rows in set (0.00 sec)
##执行rollback，没有对表中的记录有影响，所以系统默认设autocommit=1，自动提交
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from score;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan |  90.5 | nanjing |
|  2 | lisi     |  80.0 | fujiang |
+----+----------+-------+---------+
2 rows in set (0.00 sec)

将autocommit设置为0

##set autocommit=0,只要我们没有commit，就可以回滚
mysql> set autocommit=0 ;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into score (name,score,address) values ('wangwu',70.5,'fuzhou');
Query OK, 1 row affected (0.00 sec)

mysql> select * from score;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan |  90.5 | nanjing |
|  2 | lisi     |  80.0 | fujiang |
|  3 | wangwu   |  70.5 | fuzhou  |
+----+----------+-------+---------+
3 rows in set (0.00 sec)

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

mysql> select * from score;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan |  90.5 | nanjing |
|  2 | lisi     |  80.0 | fujiang |
+----+----------+-------+---------+
2 rows in set (0.00 sec)

mysql> 

创建存档点
在事务中只要没有提交commit，所以的操作均保存在缓存中，在事务中创建存档点a、存档点b…时，后面的存档点依赖前面的存档点，一旦回滚到相对较前的存档点，后面的

##改为1
mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)
##
mysql> insert into score (name,score,address) values ('wangwu',70.5,'xiamen');
Query OK, 1 row affected (0.00 sec)

mysql> select * from score;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan |  90.5 | nanjing |
|  2 | lisi     |  80.0 | fujiang |
|  4 | wangwu   |  70.5 | xiamen  |
+----+----------+-------+---------+
3 rows in set (0.00 sec)
##存档点a
mysql> savepoint a;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into score (name,score,address) values ('zhaoliu',60.5,'chanzhou');
Query OK, 1 row affected (0.00 sec)
##存档档点b
mysql> savepoint b;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into score (name,score,address) values ('heiba',59.5,'guanzhou');
Query OK, 1 row affected (0.00 sec)

mysql> select * from score;
+----+----------+-------+----------+
| id | name     | score | address  |
+----+----------+-------+----------+
|  1 | zhangsan |  90.5 | nanjing  |
|  2 | lisi     |  80.0 | fujiang  |
|  4 | wangwu   |  70.5 | xiamen   |
|  5 | zhaoliu  |  60.5 | chanzhou |
|  6 | heiba    |  59.5 | guanzhou |
+----+----------+-------+----------+
5 rows in set (0.00 sec)
##回滚到a，添加到王五那里
mysql> rollback to a;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from score;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan |  90.5 | nanjing |
|  2 | lisi     |  80.0 | fujiang |
| 13 | wangwu   |  70.5 | xiamen  |
+----+----------+-------+---------+
3 rows in set (0.00 sec)
##尝试回滚到b，就会报错了，显示没有存档点
mysql> rollback to b;
ERROR 1305 (42000): SAVEPOINT b does not exist

结束事务的三种方式

• commit
• autocommit
• rollback

##只演示第三种
##开始
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
##插入一条记录
mysql> insert into score (name,score,address) values ('zhaoliu',59.5,'guanzhou');
Query OK, 1 row affected (0.00 sec)

mysql> select * from score;
+----+----------+-------+----------+
| id | name     | score | address  |
+----+----------+-------+----------+
|  1 | zhangsan |  90.5 | nanjing  |
|  2 | lisi     |  80.0 | fujiang  |
| 13 | wangwu   |  70.5 | xiamen   |
| 16 | zhaoliu  |  59.5 | guanzhou |
+----+----------+-------+----------+
4 rows in set (0.00 sec)
##回滚到begin状态
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from score;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan |  90.5 | nanjing |
|  2 | lisi     |  80.0 | fujiang |
| 13 | wangwu   |  70.5 | xiamen  |
+----+----------+-------+---------+
3 rows in set (0.00 sec)
##再尝试插入一条记录
mysql> insert into score (name,score,address) values ('zhaoliu',59.5,'guanzhou'); 
Query OK, 1 row affected (0.00 sec)

mysql> select * from score;
+----+----------+-------+----------+
| id | name     | score | address  |
+----+----------+-------+----------+
|  1 | zhangsan |  90.5 | nanjing  |
|  2 | lisi     |  80.0 | fujiang  |
| 13 | wangwu   |  70.5 | xiamen   |
| 17 | zhaoliu  |  59.5 | guanzhou |
+----+----------+-------+----------+
4 rows in set (0.00 sec)
##再使用rollback，如果生效表示事务没有结束，如果没生效表示事务已结束
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
##查看表，可以看到记录依然在上面，说明rollback没有生效，之前的事务已结束
mysql> select * from score;
+----+----------+-------+----------+
| id | name     | score | address  |
+----+----------+-------+----------+
|  1 | zhangsan |  90.5 | nanjing  |
|  2 | lisi     |  80.0 | fujiang  |
| 13 | wangwu   |  70.5 | xiamen   |
| 17 | zhaoliu  |  59.5 | guanzhou |
+----+----------+-------+----------+
4 rows in set (0.00 sec)

mysql> 

存储引擎

存储引擎概述

存储引擎是MySQL将数据存储在文件系统中的存储方式或者格式

• mysql5.5的默认存储引擎为MyISAM
• mysql5.7默认innodb引擎支持事务

常用的存储引擎

• MyISAM
• InnoDB

MyISAM

• MyISAM不支持事务，也不支持外键
• 访问速度快
• 对事务的完整性没有要求
• MyISAM在磁盘上存储成三个文件
  • .frm文件存储表定义，表的结构
  • 数据的文件拓展名为.MYD（MYData）
  • 索引文件的拓展名为.MYI（MYIndex）
• 表级锁定形式，数据在更新时锁定整个表
• 数据库在读写过程中相互阻塞
  • 会在数据写入的过程阻塞用户数据的读取
  • 也会在数据读取的过程中阻塞用户的数据写入
• 数据单独写入或读取，速度过程较快且占用资源相对少
• MyIAM支持的存储格式
  • 静态表
  • 动态表
  • 压缩表

MyISAM适用的生产场景举例

• 公司业务不需要事务的支持
• 单方面读取或写入数据比较多的业务
• MyISAM存储引擎数据读写都比较频繁场景不适合
• 使用读写并发访问相对较低的业务
• 数据修改相对较少的业务
• 对数据业务一致性要求不是非常高的业务
• 服务器硬件资源相对比较差

InnoDB

• 支持4个事务隔离级别
• 行级锁定，但是全表扫描仍然会是表级锁定
• 读写阻塞与事务隔离级别相关
• 能非常高效的缓存索引和数据
• 表与主键以簇的方式存储
• 支持分区、表空间，类似oracle数据库
• 支持外键约束，5.5前不支持全文索引，5.5后支持全文索引
• 对硬件资源要求还是比较高的场合

InnoDB使用场景

• 业务需要事务的支持
• 行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成
• 业务数据更新较为频繁的场景
• 如:论坛，微博等
• 业务数据一致性要求较高
• 如:银行业务
• 硬件设备内存较大，利用Innodb较好的缓存能力来提高内存利用率，减少磁盘I0的压力

配置示例

修改存储引擎的方法

##方法1：
语法：alter table 表名 engine=引擎
##方法2：修改了默认的存储引擎
default-storage-engine=InnoDB
##方法3：create table创建表时指定存储引擎
create table 表名 （字段） engine=引擎
##方法4：此方法已在mysql 5.7版本中删除了
Mysql_convert_table_format -user=root -password=密码
-sock=/tmp/mysql.sock -engine = 引擎 库名 表名

Additon

关系型数据库—数据库的三大范式
第一范式，所有字段都不可再被分割

第二范式，确保表中的每一列都是和主键相关，而不是和主键的一部分相关

第三范式，确保每列都是主键列直接相关，而不是间接相关