文章目录
- 一、索引介绍
- 1.1 索引的概念
- 1.2 索引的作用
- 1.3 索引的分类
- 1.4 创建索引的原则依据
- 1.5 查看索引的方法
- 二、事务介绍
- 2.1 事务的ACID特点
- 2.2 事务控制语句
- 2.3 事务的控制方法
- 2.4 事务的操作
- 三、存储引擎介绍
- 3.1 存储引擎概念介绍
- 3.2 MyISAM的介绍
- 3.3 MyISAM适用的生产场景举例
- 3.4 innoDB特点介绍
- 3.5 innoDB适用生产场景分析
- 3.6 企业选择存储引擎依据
- 3.7 修改存储引擎
一、索引介绍
1.1 索引的概念
数据库索引
- 是一个排序的列表,存储着索引值和这个值所对应的物理地址
- 无须对整个表进行扫描,通过物理地址就可以找到所需数据
- 是表中一列或者若干列值排序的方法
- 需要额外的磁盘空间
1.2 索引的作用
- 数据库利用各种快速定位技术,能够大大加快查询速率
- 当表很大或查询涉及到多个表时,可以成千上万倍地提高查询速度
- 可以降低数据库的IO成本,并且还可以降低数据库的排序成本
- 通过创建唯一性索引保证数据表数据的唯一性
- 可以加快表与表之间的连接
- 在使用分组和排序时,可大大减少分组和排序时间
1.3 索引的分类
普通索引
- 最基本的索引类型,没有唯一性之类的限制
- 创建普通索引的方式
create index <索引的名字> on table_name(列的列表);
alter table table_name add index_name; '修改表方式创建'
mysql> desc tmp2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(5) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> create index id_index on tmp2(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tmp2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(5) | YES | MUL | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table tmp2 add index name_index (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tmp2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(5) | YES | MUL | NULL | |
| name | varchar(10) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
可以在创建表时,创建索引
mysql> create table tmp3 (id int,index id_index(id));
Query OK, 0 rows affected (0.01 sec)
mysql> desc tmp3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
唯一性索引
- 与“普通索引”基本相同
- 与普通索引的区别是索引列的所有值只能出现一次,即必须唯一
- 创建唯一索引的方式
- 唯一索引,可以为空,但只能出现一次
create unique index index_name on table_name(列的列表);
alter table table_name add unique index_name;
mysql> desc tmp2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(5) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> create unique index name_index on tmp2(name); '以name列建立唯一性索引'
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tmp2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(5) | YES | | NULL | |
| name | varchar(10) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show index from tmp2\G; '竖着查看索引'
*************************** 1. row ***************************
Table: tmp2
Non_unique: 0
Key_name: name_index
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
ERROR:
No query specified
主键索引
- 是一种特殊的唯一索引,指定为"PRIMARY KEY"
- 一个表只能有一个主键,不允许有空值
- 创建主键索引的方式
- 除了主键以外的其它字段都是候选键
create table tablename([...],primary key(列的列表));
alter table tablename add primary key(列的列表);
mysql> alter table tmp2 add primary key(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tmp2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(5) | NO | PRI | NULL | |
| name | varchar(10) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
组合索引(单索引与多列索引)
- 可以是单列上创建的索引,也可以是在多列上创建的索引
- 最左原则,从左往右依次执行
- 创建组合索引的方式
create table table_name (......),index table_name(列的列表)
mysql> create table mytable (id int not null,username varchar(16) not null,city varchar(40) not null,age int not null,index mytable(id,username,city,age));
Query OK, 0 rows affected (0.01 sec)
mysql> show index from mytable\G;
*************************** 1. row ***************************
Table: mytable
Non_unique: 1
Key_name: mytable
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: mytable
Non_unique: 1
Key_name: mytable
Seq_in_index: 2
Column_name: username
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
......
全文索引
通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询,但是,如果希望通过关键字的匹配来进行查询过滤,那么就需要基于相似度的查询,而不是原来的精确数值比较。全文索引就是为这种场景设计的。
语法:
create fulltext index <索引的名字> on tablename(列的列表);
alter table tablename add fulltext index name_index(列的列表);
mysql> alter table mytable add fulltext index city_index(city);
Query OK, 0 rows affected, 1 warning (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc mytable;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | MUL | NULL | |
| username | varchar(16) | NO | | NULL | |
| city | varchar(40) | NO | MUL | NULL | |
| age | int(11) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
1.4 创建索引的原则依据
- 表的主键、外键必须有索引
- 记录数超过300行的表应该有索引
- 经常与其他表进行连接的表,在连接字段上应该建立索引
- 唯一性太差的字段不适合建立索引
- 更新太频繁地字段不适合创建索引
- 经常出现在where子句中的字段,特别是大表的字段,应该建立索引
- 索引应该建在选择性高的字段上
- 索引应该建在小字段.上,对于大的文本字段甚至超长字段,不要建索引
1.5 查看索引的方法
查看索引语法
show index from table_name;
show keys from table_name;
加\G可以以竖行显示,更为友好
mysql> show index from mytable;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mytable | 1 | mytable | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| mytable | 1 | mytable | 2 | username | A | 0 | NULL | NULL | | BTREE | | |
| mytable | 1 | mytable | 3 | city | A | 0 | NULL | NULL | | BTREE | | |
| mytable | 1 | mytable | 4 | age | A | 0 | NULL | NULL | | BTREE | | |
| mytable | 1 | city_index | 1 | city | NULL | 0 | NULL | NULL | | FULLTEXT | | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
mysql> show index from mytable\G;
*************************** 1. row ***************************
Table: mytable
Non_unique: 1
Key_name: mytable
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: mytable
Non_unique: 1
Key_name: mytable
Seq_in_index: 2
Column_name: username
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
删除索引的方法
drop index index_name on table_name;
alter table table_name drop index index_name;
mysql> drop index city_index on mytable;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from mytable;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mytable | 1 | mytable | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| mytable | 1 | mytable | 2 | username | A | 0 | NULL | NULL | | BTREE | | |
| mytable | 1 | mytable | 3 | city | A | 0 | NULL | NULL | | BTREE | | |
| mytable | 1 | mytable | 4 | age | A | 0 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
二、事务介绍
在了解事务之前,先简单了解一下数据库设计三大格式
- 第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库表满足了第一 范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到"地址”这个属性,本来直接将地址“属性设计成一个数据库表的字段就行。但是如果系统经常会访问"地址”属性中的“城市”部分,那么就非要将"地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储,这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式。 - 第二范式(确保表中的每列都和主键相关)
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某-部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。 - 第三范式(确保每列都和主键列直接相关,而不是间接相关)
第三范式需要确保数据表中的每一列数据都和主键直接相关, 而不能间接相关。比如在设计一个订单数据表的时候,可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息(比如姓名、所属公司等)的字段。
2.1 事务的ACID特点
原子性(Atomicity)
- 事务是一个完整的操作,事务的各元素是不可分的
- 事务中的所有元素必须作为一个整体提交或回滚
- 如果事务中的任何元素失败,则整个事务将失败
一致性(Consistency)
- 当事务完成时,数据必须处于一致状态
- 在事务开始前,数据库中存储的数据处于一致状态
- 在正在进行的事务中,数据可能处于不一致的状态
- 当事务成功完成时,数据必须再次回到已知的一致状态
隔离性(Isolation)
- 对数据进行修改的所有并发事务是彼此隔离的,表明事务必须是独立的,它不应以任何方式依赖于或影响其他事务
- 修改数据的事务可在另-一个使用相同数据的事务开始之前访问这些数据,或者在另一一个使用相同数据的事务结束之后访问这些数据
持久性(Durability)
- 指不管系统是否发生故障,事务处理的结果都是永久的
- 一旦事务被提交,事务的效果会被永久地保留在数据库中
2.2 事务控制语句
MySQL事务默认是自动提交的,当SQL语句提交时事务便自动提交
事务控制语句
- BEGIN或START TRANSACTION 事务的开始
- COMMIT 提交, 无法回滚
- ROLLBACK 回滚
- SAVEPOINT identifier 设置存档点
- RELEASE SAVEPOINT identifier 删除存档点
- ROLLBACK TO identifier 回滚到某个存档点
- SET TRANSACTION 设置事务
2.3 事务的控制方法
手动对事务进行控制的方法
事务处理命令控制事务
- begin:开始一个事务
- commit:提交一个事务
- rollback:回滚一个事务
使用set命令进行控制
- set autocommit=0:禁止自动提交 (等同于begin)
- set autocommit=1:开启自动提交 (等同于commit)
2.4 事务的操作
创建一个新表
mysql> create table mytable (id int(4) not null primary key auto_increment,name char(10) not null,score decimal(5,2),address varchar(50) not null default '未知')engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mytable;
Empty set (0.00 sec)
mysql> begin; '开始事务'
Query OK, 0 rows affected (0.00 sec)
mysql> insert into mytable value (1,'zhangsan',80,'sh');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mytable value (2,'lisi',77,'nj');
Query OK, 1 row affected (0.00 sec)
mysql> commit; '结束事务'
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mytable; '记录添加成功'
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 80.00 | sh |
| 2 | lisi | 77.00 | nj |
+----+----------+-------+---------+
2 rows in set (0.00 sec)
mysql> begin; '开始事务'
Query OK, 0 rows affected (0.00 sec)
mysql> insert into mytable values (3,'wangwu',60,'nj');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mytable;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 80.00 | sh |
| 2 | lisi | 77.00 | nj |
| 3 | wangwu | 60.00 | nj |
+----+----------+-------+---------+
3 rows in set (0.00 sec)
mysql> rollback; '回滚'
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mytable; '记录回滚'
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 80.00 | sh |
| 2 | lisi | 77.00 | nj |
+----+----------+-------+---------+
2 rows in set (0.00 sec)
mysql> begin; '开始事务'
Query OK, 0 rows affected (0.00 sec)
mysql> insert into mytable values (3,'wangwu',90,'nj');
Query OK, 1 row affected (0.00 sec)
mysql> savepoint a; '设置存档点a'
Query OK, 0 rows affected (0.00 sec)
mysql> insert into mytable values (4,'zhaoliu',60,'hz');
Query OK, 1 row affected (0.00 sec)
mysql> savepoint b; '设置存档点b'
Query OK, 0 rows affected (0.00 sec)
mysql> insert into mytable values (5,'qiqi',66,'nj');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mytable;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 80.00 | sh |
| 2 | lisi | 77.00 | nj |
| 3 | wangwu | 90.00 | nj |
| 4 | zhaoliu | 60.00 | hz |
| 5 | qiqi | 66.00 | nj |
+----+----------+-------+---------+
5 rows in set (0.00 sec)
mysql> rollback to b; '回滚到存档点b'
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mytable;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 80.00 | sh |
| 2 | lisi | 77.00 | nj |
| 3 | wangwu | 90.00 | nj |
| 4 | zhaoliu | 60.00 | hz |
+----+----------+-------+---------+
4 rows in set (0.00 sec)
mysql> rollback to a; '回滚到存档点a'
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mytable;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 80.00 | sh |
| 2 | lisi | 77.00 | nj |
| 3 | wangwu | 90.00 | nj |
+----+----------+-------+
mysql> rollback to b; '此时已无法回档到b'
ERROR 1305 (42000): SAVEPOINT b does not exist
mysql> rollback; '回滚到开始'
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mytable;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 80.00 | sh |
| 2 | lisi | 77.00 | nj |
+----+----------+-------+---------+
2 rows in set (0.00 sec)
commit
set autocommit=1
rollback '3种命令都可以结束事务'
三、存储引擎介绍
3.1 存储引擎概念介绍
- MySQL中的数据用各种不同的技术存储在文件中,每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力,这些不同的技术以及配套的功能在MySQL中称为存储引擎
- 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式
- MySQL常用的存储引擎
MyISAM
InnoDB - MySQL数据库中的组件,负责执行实际的数据I/O操作
- MySQL系统中,存储引擎处于文件系统之上,在数据保存到数据文件之前会传输到存储引擎,之后按照各个存储引擎的存储格式进行存储
3.2 MyISAM的介绍
- MyISAM不支持事务,也不支持外键
- 访问速度快
- 对事务完整性没有要求
- MyISAM在磁盘上存储成三个文件
●.frm文件存储表定义
●数据文件的扩展名为.MYD (MYData)
●索引文件的扩展名是.MYI (MYIndex) - 表级锁定形式,数据在更新时锁定整个表
- 数据库在读写过程中相互阻塞
●会在数据写入的过程阻塞用户数据的读取
●也会在数据读取的过程中阻塞用户的数据写入 - 数据单独写入或读取,速度过程较快且占用资源相对少
- MyIAM支持的存储格式
●静态表
●动态表
●压缩表
3.3 MyISAM适用的生产场景举例
- 公司业务不需要事务的支持
- 单方面读取或写入数据比较多的业务
- MyISAM存储引擎数据读写都比较频繁场景不适合
- 使用读写并发访问相对较低的业务
- 数据修改相对较少的业务
- 对数据业务-致性要求不是非常高的业务
- 服务器硬件资源相对比较差
3.4 innoDB特点介绍
- 支持4个事务隔离级别
- 行级锁定,但是全表扫描仍然会是表级锁定
- 读写阻塞与事务隔离级别相关
- 能非常高效的缓存索引和数据
- 表与主键以簇的方式存储
- 支持分区、表空间,类似oracle数据库
- 支持外键约束,5.5前不支持全文索引,5.5后支持全文索引
- 对硬件资源要求还是比较高的场合
3.5 innoDB适用生产场景分析
- 业务需要事务的支持
- 行级锁定对高并发有很好的适应能力,但需确保查询是通过索引来完成
- 业务数据更新较为频繁的场景
如:论坛,微博等 - 业务数据一致性要求较高
如:银行业务 - 硬件设备内存较大,利用Innodb较好的缓存能力来提高内存利用率,减少磁盘IO的压力
3.6 企业选择存储引擎依据
- 需要考虑每个存储引擎提供的核心功能及应用场景
- 支持的字段和数据类型
●所有引擎都支持通用的数据类型
●但不是所有的弓|擎都支持其它的字段类型,如二进制对象 - 锁定类型:不同的存储引擎支持不同级别的锁定
●表锁定
●行锁定 - 索引的支持
●建立索引在搜索和恢复数据库中的数据时能显著提高性能
●不同的存储引擎提供不同的制作索引的技术
●有些存储弓|擎根本不支持索引 - 事务处理的支持
●提高在向表中更新和插入信息期间的可靠性
●可根据企业业务是否要支持事务选择存储引擎
3.7 修改存储引擎
方法一:alter table 修改
alter table table_name engine=引擎;
方法二:修改my.cnf,指定默认存储引擎并重启服务
default-storage-engine=innodb
方法三:create table创建表时指定存储引擎
create table 表名 (字段)engine=引擎
方法四:Mysql_convert_table_format 转化存储引擎
Mysql_convert_table_format-user=root -password=密码
-sock=/tmp/mysql.sock-engine=引擎 库名 表名 '此条命令在mysql5,7版本中被移除了,在5.7之前版本可以使用'
mysql> alter table mytable engine=innodb;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
[root@localhost ~]# vim /etc/my.cnf
...
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir=/usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
default-storage-engine=myisam '在mysqld配置中添加'
[root@localhost ~]# systemctl restart mysqld.service '重启后生效'