【MySQL 教程（八）】索引、事务、用户管理、导入导出与分页查询

MySQL中的索引

索引介绍

索引是对数据库表中的一列或多列值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。索引是一种特殊的文件，它们包含着对数据表里所有记录的位置信息。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。MySQL 索引的建立对于MySQL 的高效运行是很重要的，索引可以大大提高MySQL 的检索速度。

索引的作用

索引相当于图书上的目录，可以根据目录上的页码快速找到所需的内容，提高性能（查询速度）。

索引优点：

1. 通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性；
2. 可以加快数据的检索速度；
3. 可以加速表与表之间的连接；
4. 在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间；

索引缺点

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
2. 索引需要占用物理空间，数据量越大，占用空间越大；
3. 会降低表的增删改的效率，因为每次增删改索引都需要进行动态维护；

什么时候需要创建索引

1. 频繁作为查询条件的字段应该创建索引；
2. 查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找）；
3. 查询中统计或者分组的字段；

什么时候不需要创建索引

1. 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文
   件；
2. where条件里用不到的字段，不创建索引；
3. 表记录太少，不需要创建索引；
4. 经常增删改的表；
5. 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大
   量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引；

MySQL中的索引类型

• 普通索引：
  最基本的索引，它没有任何限制。
• 唯一索引：
  索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。
• 主键索引：
  特殊的索引，唯一的标识一条记录，不能为空，一般用primary key来约束。
• 联合索引：
  在多个字段上建立索引，能够加速查询到速度。

普通索引

是最基本的索引，它没有任何限制。在创建索引时，可以指定索引长度。length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度，如果是 BLOB 和 TEXT 类型，必须指定 length。

查询索引

# SHOW INDEX FROM table_name; show index from table_name;

直接创建索引

# CREATE INDEX index_name ON table(column(length)); create index index_name on table(column(length));

示例：为 emp3 表中的 name 创建一个索引，索引名为 emp3_name_index；

create index emp3_name_index ON emp3(name);

修改表添加索引

# ALTER TABLE table_name ADD INDEX index_name (column(length)); alter table table_name add index index_name (column(length));

示例：修改 emp3 表，为 addrees 列添加索引，索引名为 emp3_address_index；

alter table emp3 add index emp3_address_index(address);

创建表时指定索引列

CREATE TABLE `table` ( COLUMN TYPE , PRIMARY KEY (`id`), INDEX index_name (column(length)) );

示例：创建 emp4 表，包含 emp_id,name,address 列， 同时为 name 列创建索引 ，索引名为 emp4_name_index。

create table emp4( emp_id int primary key auto_increment, name varchar(30), address varchar(50), index emp4_name_index(name));

删除索引

# DROP INDEX indexname ON tablename; drop index indexname on tablename;

示例：删除 mep3 表中索引名为 emp3_address_index 的索引。

drop index emp3_address_index on emp3;

唯一索引

唯一索引与普通索引类似，不同的就是： 索引列的值必须唯一，但允许有空值。

创建唯一索引

# CREATE UNIQUE INDEX indexName ON table(column(length)); create unique index indexname on table(column(length));

示例：为 emp 表中的 name 创建一个唯一索引，索引名为 emp_name_index。

create unique index emp_name_index on emp(name);

修改表添加唯一索引

ALTER TABLE table_name ADD UNIQUE indexName (column(length)); alter table table_name add unique indexname (column(length));

示例：修改 emp 表，为 salary 列添加唯一索引，索引名为 emp_salary_index。

alter table emp add unique emp_salary_index(salary);

创建表时指定唯一索引

CREATE TABLE `table` ( COLUMN TYPE , PRIMARY KEY (`id`), UNIQUE index_name (column(length)) );

示例：创建 emp5 表，包含 emp_id,name,address 列，同时为 name 列创建唯一索引。索引名为 emp5_name_index。

create table emp5( emp_id int primary key , name varchar(30), address varchar(30), unique emp5_name_index(name));

主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

修改表添加主键索引

# ALTER TABLE 表名 ADD PRIMARY KEY(列名); alter table 表名 add primary key(列名);

示例：修改 emp 表为 employee_id 添加主键索引。

alter table emp add primary key(employee_id);

创建表时指定主键索引

CREATE TABLE `table` ( COLUMN TYPE , PRIMARY KEY(column) );

示例：创建 emp6 表，包含 emp_id,name,address 列，同时为 emp_id 列创建主键索引。

create table emp6( employee_id int primary key auto_increment, name varchar(20), address varchar(50));

组合索引

组合索引是指使用多个字段创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用(最左前缀原则)。

最左前缀原则

就是最左优先。

如： 我们使用表中的 name ，address ，salary 创建组合索引，那么想要组合索引生效， 我们只能使用如下组合：

name/address/salary

name/address

name/

如果使用 addrees/salary 或者是 salary 则索引不会生效。

添加组合索引

# ALTER TABLE table_name ADD INDEX index_name (column(length),column(length)); alter table table_name add index index_name (column(length),column(length));

示例：修改 emp6 表，为 name ，address 列创建组合索引。

alter table emp6 add index emp6_index_n_a(name,address);

创建表时创建组合索引

CREATE TABLE `table` ( COLUMN TYPE , INDEX index_name (column(length),column(length)) );

示例：创建 emp7 表，包含 emp_id,name,address 列，同时为 name,address 列创建组合索引。

create table emp7(emp_id int primary key auto_increment ,name varchar(20),address varchar(30),index emp7_index_n_a(name,address));

MySQL事务

事务简介

事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

事务定义(Transaction)

• 事务是一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业
  务，该业务就是一个最小的工作单元)
• 一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成
• 事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML
  语句的个数不同

事务四大特征(ACID)

• 原子性(ATOMICITY)
  事务中的操作要么都不做，要么就全做。

• 一致性(CONSISTENCY)
  一个事务应该保护所有定义在数据上的不变的属性(例如完整性约束)。在完成了一个成功的事务时，数据应处于一致的状态。

• 隔离性(ISOLATION)
  一个事务的执行不能被其他事务干扰。

• 持久性(DURABILITY)
  一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

事务类型

• 显式事务
  需要我们手动的提交或回滚。DML 语言中的所有操作都是显示事务操作。
• 隐式事务
  数据库自动提交不需要我们做任何处理，同时也不具备回滚性。DDL、DCL 语言都是隐式事务操作

使用事务

示例一：创建account账户表，包含id、卡号、用户名、余额。

create table account( id int primary key auto_increment, cardnum varchar(20) not null, username varchar(30) not null, balance double(10,2) );

示例二：向account表中插入两条数据。

insert into account(cardnum,username,balance) VALUES('123456789','张三',2000); insert into account(cardnum,username,balance) VALUES('987654321','李四',2000);

示例三：在一个事务中完成转账业务。

start transaction update account set balance = balance-200 where cardnum = '123456789'; update account set balance = balance+200 where cardnum = '987654321'; select * from account; -- 当我们关闭数据库重新打开后，张三和李四的账户余额并没发生任何变化。 -- 这是因为当我们使用“START TRANSACTION”开启一个事务后，该事务的提交方式不再是自动的， -- 而是需要手动提交，而在这里，我们并没有使用事务提交语句COMMIT， -- 所以对account表中数据的修改并没有永久的保存到数据库中，也就是说我们的转账事务并没有执行成功 -- 提交转账事务 commit; -- 事务的回滚让数据库恢复到了执行事务操作前的状态。 -- 需要注意的是事务的回滚必须在事务提交之前，因为事务一旦提交就不能再进行回滚操作。 rollback;

事务的并发问题

脏读（读取未提交数据）

指一个事务读取了另外一个事务未提交的数据。

A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。

不可重复读（前后多次读取，数据内容不一致

在一个事务内读取表中的某一行数据，多次读取结果不同。事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。

幻读（前后多次读取，数据总量不一致

是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。

事务的隔离级别

事务的隔离级别用于决定如何控制并发用户读写数据的操作。数据库是允许多用户并发访问的，如果多个用户同时开启事务并对同一数据进行读写操作的话，有可能会出现脏读、不可重复读和幻读问题，所以MySQL中提供了四种隔离级别来解决上述问题。

事务的隔离级别从低到高依次为：

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

隔离级别越低，越能支持高并发的数据库操作。

查看MySQL默认事务隔离级别

SELECT @@transaction_isolation;

设置事务隔离级别

对当前session有效。

set session transaction isolation level read uncommitted; set session transaction isolation level read committed; set session transaction isolation level repeatable read; set session transaction isolation level serializable;

MySQL的用户管理

MySQL 是一个多用户的数据库系统，按权限，用户可以分为两种： root 用户，超级管理员，和由 root 用户创建的普通用户。

用户管理
创建用户

# CREATE USER username IDENTIFIED BY 'password'; create user username IDENTIFIED by 'password';

查看用户

# SELECT USER,HOST FROM mysql.user; select user,host from mysql.user;

示例：创建一个 u_sxt 的用户，并查看创建是否成功。

create user u_sxt IDENTIFIED by 'sxt'; select user,host from mysql.user;

权限管理

新用户创建完后是无法登陆的，需要分配权限。

GRANT 权限 ON 数据库.表 TO 用户名@登录主机 IDENTIFIED BY “密码”

登陆主机：

权限列表

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password'

示例：为 u_sxt 用户分配只能查询 bjsxt 库中的 emp 表，并且只能在本机登陆的权限。

grant select ON bjsxt.emp to 'u_sxt'@'localhost' IDENTIFIED by 'sxt';

刷新权限

每当调整权限后，通常需要执行以下语句刷新权限。

FLUSH PRIVILEGES;

删除用户

DROP USER username@localhost;

示例：删除 u_sxt 用户。

drop user 'u_sxt'@'%';

Navicat工具

创建用户

分配权限

删除用户

数据的导入导出

导出sql脚本文件

导入sql脚本文件

MySQL分页查询

MySQL 分页查询原则：

• 在 MySQL 数据库中使用 LIMIT 子句进行分页查询。
• MySQL 分页中开始位置为 0。
• 分页子句在查询语句的最后侧。

LIMIT子句

SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY LIMIT 开始位置，查询数量;

示例：查询雇员表中所有数据按 id 排序，实现分页查询，每次返回两条结果。

select * from employees e order by e.employee_id limit 0,2;

LIMIT OFFSET子句

SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY LIMIT 查询数量 OFFSET 开始位置;

示例：查询雇员表中所有数据按 id 排序，使用 LIMIT OFFSET 实现分页查询，每次返回两条结果。

select * from employees order by employees_id limit 2 offset 4;