20171110 MySQL进阶（一）

作者: 哈喽别样 | 来源:发表于2017-11-12 19:48 被阅读15次

数据库基本概念回顾

数据库的并发控制：锁

MySQL的事务设置

MySQL的索引设置

MySQL的用户和权限管理

一、数据库基本概念回顾

（一）数据库的分类

（1）关系型数据库：RDBMS

主流产品：Oracle, SQL Server, MySQL, MariaDB, PostgreSQL

（2）非关系型数据库：No-SQL DBMS

Document store：文档型数据库，e.g. MongoDB
Key-value store：键值存储数据库，e.g. Redis
Wide column store：列存储数据库
Graph DBMS：图形数据库
Search engine：搜索引擎，e.g. Solr, ElasticSearch
Time Series DBMS：时序数据库，e.g. InfluxDB

（二）MySQL的基本概念

（1）MySQL的三个分支

Oracle官方MySQL
MariaDB，MySQL创始人创建的分支
Percona Server，在InnoDB引擎的基础上开发的XtraDB

（2）MySQL的存储引擎

常见的存储引擎：SHOW ENGINES;
MyISAM, Aria, InnoDB, MRG_MYISAM, CSV, BLACKHOLE, MEMORY, PERFORMANCE_SCHEMA, ARCHIVE, FEDERATED
InnoDB：原由InnoBase公司开发，XtraDB是Percona开发的增强版
- 数据存储于表空间(table space)中，分两种方式：
  - 所有数据库中的所有类型为InnoDB的表的数据和索引存储于同一个表空间中
    文件：ibdata1, ibdata2, ...
  - 每表的数据文件（数据和索引，存储于数据库目录）存储于自己专用的表空间文件中
    需要在配置文件中设置innodb_file_per_table=ON
    数据文件：tbl_name.ibd
    表结构的定义：tbl_name.frm
- 事务型存储引擎，符合ACID（原子性，一致性，隔离性，持久性）原则，适合对事务要求较高的场景中
- 基于MVCC(Mutli Version Concurrency Control)多版本并发控制，支持高并发
- 支持四个隔离级别，默认级别为REPEATABLE-READ；间隙锁以防止幻读
- 使用聚集索引（主键索引）
- 支持自适应Hash索引
- 锁粒度：行级锁；间隙锁
MyISAM：MySQL原生引擎，性能不好
- 支持全文索引(FULLTEXT index)、表压缩、空间函数（GIS）
- 不支持事务
- 锁粒度：表级锁
- 崩溃无法保证表安全恢复
- 非聚集索引
其它的存储引擎：
- CSV：将CSV文件（以逗号分隔字段的文本文件）作为MySQL表文件
- MRG_MYISAM：将多个MyISAM表合并成的虚拟表
- BLACKHOLE：类似于/dev/null，不真正存储数据
- MEMORY：内存存储引擎，支持hash索引，表级锁，常用于临时表
- FEDERATED：用于访问其它远程MySQL服务器上表的存储引擎接口

二、数据库的并发控制：锁

（一）锁类型：

读锁：共享锁，可被多个读操作共享
写锁：排它锁，独占锁

（二）锁粒度：

表锁：在表级别施加锁，并发性较低
行锁：在行级别施加锁，并发性较高；维持锁状态的成本较大

（三）锁策略：在锁粒度及数据安全性之间寻求一种平衡机制

存储引擎：存储引擎自行决定锁级别以及何时施加或释放锁
MySQL Server：表级别，可自行决定，也允许显式请求

（四）锁类别：

显式锁：用户手动请求的锁
隐式锁：存储引擎自行根据需要施加的锁

显式锁的使用：
1. LOCK TABLES
  LOCK TABLES tbl_name read|write, tbl_name read|write, ...
  UNLOCK TABLES
2. FLUSH TABLES
  FLUSH TABLES tbl_name,... [WITH READ LOCK];
  UNLOCK TABLES;
3. SELECT cluase [FOR UPDATE | LOCK IN SHARE MODE]

（五）实验1：读锁、写锁的区别

对表students添加显式读锁

在另一个进程对此表进行读操作没有被阻塞，立即执行

释放对表students的读锁后，添加显式写锁

在另一个进程对此表进行读操作被阻塞，待手动释放写锁后才能执行

通过FLUSH命令对表添加读锁

在另一个对此表的读操作未被阻塞，而写操作被阻塞

总结：

读锁允许其他进程对表进行读操作，不允许写操作；
写锁不允许其他进程对表进行读写操作
SQL语言的运行时间除了与复杂性有关外，还受数据库所处的环境影响（例如大量写操作的环境下，很难迅速通过FLUSH命令添加读锁）

三、MySQL的事务设置

事务：一组原子性的SQL查询、或者是一个或多个SQL语句组成的独立工作单元

（一）事务日志的配置项：

innodb_log_files_in_group
innodb_log_group_home_dir
innodb_log_file_size
innodb_mirrored_log_groups

（二）ACID测试：

A：AUTOMICITY，原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：CONSISTENCY，一致性；数据库总是应该从一个一致性状态转为另一个一致性状态
I：ISOLATION，隔离性；一个事务所做出的操作在提交之前，是否能为其它事务可见；出于保证并发操作之目的，隔离有多种级别
D：DURABILITY，持久性；事务一旦提交，其所做出的修改会永久保存

（三）控制事务逻辑：

自动提交：单语句事务
查询自动提交设置：SELECT @@autocommit;
取消自动提交设置：SET @@session.autocommit=0;
手动控制事务：
启动：START TRANSACTION
提交：COMMIT
回滚：ROLLBACK
事务支持savepoints：
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

（四）实验2：手动控制事务

前提：关闭自动提交事务：SET @@session.autocommit=0;
表原内容如下

现在开始事务，增加一条记录，查看修改后的表内容如下

START TRANSACTION;
INSERT students (ID,NAME,AGE,GENDER,DESCRIPTION) VALUES (6,'Marry',25,'F','Art');
SELECT * FROM students;

执行回滚操作，再查看表内容恢复成原状，最后提交事务
```
ROLLBACK;
SELECT * FROM students;
COMMIT;   
```

（五）实验3：在实验2的基础上，在一个事务中建立多个保存点，分别恢复

开始一个事务，删除NAME='Frank'的表记录，此刻存为保存点test1

START TRANSACTION;
DELETE FROM students WHERE NAME='Frank';
SELECT * FROM students;
SAVEPOINT test1;

更新ID=2的表记录中AGE=22，此刻存为保存点test2

UPDATE students SET AGE=22 WHERE ID=2;
SAVEPOINT test2;
SELECT * FROM students;

删除NAME='Alice'的表记录

DELETE FROM students WHERE NAME='Alice';
SELECT * FROM students;

回滚至保存点test2，恢复了NAME='Alice'的表记录

ROLLBACK TO test2;
SELECT * FROM students;

回滚至保存点test1，ID=2的表记录中AGE=25

ROLLBACK TO test1;
SELECT * FROM students;

回滚至事务开始，恢复至NAME='Frank'的表记录

ROLLBACK;
SELECT * FROM students;
COMMIT;

（六）事务隔离：

事务隔离级别：
READ-UNCOMMITTED：读未提交 --> 脏读
READ-COMMITTED：读提交--> 不可重复读
REPEATABLE-READ：可重复读 --> 幻读，MySQL的默认级别
SERIALIZABLE：串行化
查看事务隔离级别：
SELECT @@session.tx_isolation;
查看InnoDB存储引擎的状态信息：
SHOW ENGINE innodb STATUS;

（七）实验4：区分四级事务隔离

实验环境：两个进程访问同一个数据库的同一个表，在一个进程修改表，在另一个进程查看表，以此区分四级事务隔离的区别
实验准备：两个进程都关闭自动提交事务SET @@session.autocommit=0;
测试READ-UNCOMMITTED级别

进程1添加一条记录，尚未提交事务

此时进程2中已发现了添加的记录，此时进程2的查询操作即是脏读，数据是脏数据
测试READ-COMMITTED级别

进程1删除一条记录，本进程查询已经记录删除

此时进程2中查询仍旧有被进程1删除的记录，当进程1提交事务后，查询表中已经删除了对应的记录；
READ-COMMITTED级别虽然保证了只有提交的事务结果才能被其他进程看到，但是反映在进程2却出现了未有任何修改操作，而数据发生变化的“奇怪”现象，此种现象为不可重复读
测试REPEATABLE-READ级别

进程1删除一条记录，本进程查询记录已经删除

此时进程2中查询仍旧有被进程1删除的记录，当进程1提交事务后，查询表中被进程1删除的记录仍旧存在，故REPEATABLE-READ级别解决了不可重复读的问题

此时在进程2中尝试删除被进程1删除的记录，发现返回结果提示没有找到符合条件的记录被删除，而再次尝试查询表记录，发现被“删除”的记录仍旧存在，似乎词条记录有时存在，有时不存在，此种现象为幻读
测试SERIALIZABLE级别

进程1添加一条表记录

此时在进程2中查询表信息，发现处于等待状态。待进程1提交事务后，进程2的查询命令才运行出结果。进程2的查询语句所耗费的时间远大于一般情况，大部分时间进程2被阻塞，直到进程1完成提交事务后才能够运行；
这种事务隔离级别虽然避免了之前三种级别的所有问题，但是完全禁止了并行状态，数据库的性能十分低下。

四、MySQL的索引设置

（一）索引的基本概念：

定义：提取索引的创建在的表上字段中的数据，构建出一个独特的数据结构
作用：加速查询操作；副作用：降低写操作性能
原理：索引即是表中数据子集，通过把表中某个或某些字段的数据提取出来另存为一个特定数据结构组织的数据
索引某个字段或某些字段的策略：WHERE子句中用到的字段

（二）索引的类型：

索引类型：B+ TREE，HASH

（1）B+ TREE索引：

顺序存储，每一个叶子结点到根结点的距离相同；MySQL采用左前缀索引，适合于范围类型的数据查询

适用于B+ TREE索引的查询类型：全键值、键值范围或键前缀；
1. 全值匹配：精确匹配某个值
WHERE COLUMN = 'value';
2. 匹配最左前缀：只精确匹配开始的部分
WEHRE COLUMN LIKE 'PREFIX%';
3. 匹配范围值
精确匹配某一列，范围匹配另一列
只用访问索引的查询：覆盖索引
index(Name)
SELECT Name FROM students WHERE Name LIKE 'L%';
不适用B+ TREE索引：
1. 如果查询条件不是从最左侧列开始，索引无效
index(age,Fname), WHERE Fname='Jerry'; , WHERE age>30 AND Fname='Smith';
2. 不能跳过索引中的某列
index(name,age,gender)
WHERE name='black' and age > 30;
WHERE name='black' AND gender='F';
3. 如果查询中的某个列是为范围查询，那么其右侧的列都无法再使用索引优化查询
WHERE age>30 AND Fname='Smith';

（2）Hash索引：

基于哈希表实现，特别适用于值的精确匹配查询；

适用场景：
只支持等值比较查询，例如=, IN(), <=>
不适用场景：
所有非精确值查询；MySQL仅对memory存储引擎支持显式的hash索引
索引优点：
降低需要扫描的数据量，减少IO次数
可以帮助避免排序操作，避免使用临时表
帮助将随机IO转为顺序IO

（三）高性能索引策略：

在WHERE中独立使用列，尽量避免其参与运算
反例：WHERE age+2 > 32;
左前缀索引：索引构建于字段的最左侧的多少个字符，要通过索引选择性来评估
索引选择性：不重复的索引值和数据表的记录总数的比值
多列索引：
AND连接的多个查询条件更适合使用多列索引，而非多个单键索引
选择合适的索引列次序：选择性最高的放左侧

（四）EXPLAIN来分析索引有效性：

（1）语法：

EXPLAIN [explain_type] SELECT select_options

（2）输出结果分析：

id：当前查询语句中，第#个SELECT语句的编号
select_type：查询类型
- 简单查询：SIMPLE
- 复杂查询：
  简单子查询：SUBQUERY
  用于FROM中的子查询：DERIVED
  联合查询中的第一个查询：PRIMARY
  联合查询中的第一个查询之后的其它查询：UNION
  联合查询生成的临时表：UNION RESULT
table：查询针对的表
type：关联类型，或称为访问类型，即MySQL如何去查询表中的行
- ALL：全表扫描
- index：根据索引的顺序进行的全表扫描；但同时如果Extra列出现了"Using index”表示使用了覆盖索引
- range：有范围限制地根据索引实现范围扫描；扫描位置始于索引中的某一项，结束于另一项
- ref：根据索引返回的表中匹配到某单个值的所有行（匹配给定值的行不止一个）
- eq_ref：根据索引返回的表中匹配到某单个值的单一行，仅返回一个行，但需要与某个额外的参考值比较，而不是常数
- const，system：与某个常数比较，且只返回一行
possiable_keys：查询中可能会用到的索引
key：查询中使用的索引
key_len：查询中用到的索引长度
ref：在利用key字段所显示的索引完成查询操作时所引用的列或常量值
rows：MySQL估计出的为找到所有的目标项而需要读取的行数
Extra：额外信息
- Using index：使用了覆盖索引进行的查询
- Using where：拿到数据后还要再次进行过滤
- Using temporary：使用了临时表以完成查询
- Using filesort：对结果使用了一个外部索引排序

（五）实验5：索引的使用

环境准备：建立一个足够大的表

// 创建表
MariaDB [test]> CREATE TABLE employees (ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(10) NOT NULL,AGE TINYINT UNSIGNED,GENDER ENUM('M','F'),MAJOR VARCHAR(50));
// 脚本批量添加表记录，共500条
GENDER=("M" "F")
for i in {1..500};do mysql -e "INSERT INTO test.employees (NAME,AGE,GENDER) VALUES ('emp$i','$[$RANDOM%43+18]','${GENDER[$RANDOM%2]}')"; done

此时分析查询语句：EXPLAIN SELECT * FROM employees WHERE AGE>50;

select_type='SIMPLE'表示当前为简单查询，type='ALL'表示为全表查询，此时尚未用到索引，rows代表预计查询需要读取的行数

创建索引
AGE单字段索引：CREATE INDEX age ON employees (AGE);
AGE, NAME多字段索引：CREATE INDEX age_and_name ON employees (AGE,NAME);
NAME, AGE多字段索引：CREATE INDEX name_and_age ON employees (NAME,AGE);
显示索引信息：SHOW INDEXES FROM employees;

分析查询语句：EXPLAIN SELECT NAME FROM employees WHERE AGE>50;

type='range'表示根据索引范围搜索，possible_keys='age, age_and_name'表示可能用到的索引为age, age_and_name，key='age_and_name'表示真正使用的索引为age_and_name；

由于搜索字段为AGE，索引age, age_and_name都是以字段AGE开始，故系统认为他们可能被用到；而由于age_and_name索引包含了查询结果字段，故被选做实际用到的索引；

此时rows=112，相较没有设置索引时读入的行数大大减少，查询效率提升明显

分析查询语句：EXPLAIN SELECT NAME FROM employees WHERE NAME LIKE 'emp1%';

此时搜索条件为NAME，故只有起始字段为NAME的索引name_and_age成为可能和实际被使用的索引；

type='range'表示查询为根据索引的范围查询

分析复合查询语句：EXPLAIN SELECT NAME,AGE FROM employees WHERE AGE< (SELECT avg(AGE) FROM employees);

这是一个复合查询语句，主句的查询条件是子句的查询结果，在本例中通过子句查询年龄的平均值，再查询年龄小于平均值的记录的姓名与年龄字段值，select_type明确表明了查询语句的主从关系；

当type='index'并且Extra='Using index'，表示查询使用了覆盖索引，效率较高

分析精确查询语句：EXPLAIN SELECT NAME,AGE FROM employees WHERE AGE=30;

type='ref'表示查询属于精确查询，查询条件匹配单个值，而匹配结果可能不止一个

分析使用主键索引查询语句：EXPLAIN SELECT NAME,AGE FROM employees WHERE ID=50;

type='const'表示查询条件为精确查询某个常数，并且返回结果只有一行，通常在主键索引查询时出现

五、MySQL的用户和权限管理

（一）用户账号：user@host

user：账户名称
host：此账户可通过哪些客户端主机请求创建连接线程，可使用通配符
%：任意长度的任意字符；
_：任意单个字符；
建议在mysql设置中跳过主机地址解析：skip_name_resolve=ON

（二）MySQL权限类别：

分为：库级别，表级别，字段级别，管理类，程序类
管理类：
CREATE USER
RELOAD
LOCK TABLES
REPLICATION CLIENT， REPLICATION SLAVE
SHUTDOWN
FILE
SHOW DATABASES
PROCESS
SUPER
程序类：
FUNCTION，PROCEDURE，TRIGGER，分别拥有以下操作
操作：CREATE，ALTER，DROP，EXECUTE
库和表级别：
CREATE，ALTER，DROP
INDEX
CREATE VIEW
SHOW VIEW
GRANT：能够把自己获得的权限生成一个副本转赠给其它用户，不推荐使用
OPTION
数据操作：
- 表：
  INSERT/DELETE/UPDATE/SELECT
- 字段：
  SELECT(col1,col2,...)
  UPDATE(col1,col2,...)
  INSERT(col1,col2,...)
所有权限：ALL， ALL PRIVILEGES
元数据数据库（数据字典）：mysql
mysql数据库用于存放各级别的权限设置，具体包含如下存放授权信息的表
db, host, user
tables_priv, column_priv, procs_priv, proxies_priv

（三）用户管理命令

创建用户：
CREATE USER 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'] [,'user'@'host' [IDENTIFIED BY [PASSWORD] 'password']...]
重命名用户：
RENAME USER old_user TO new_user[, old_user TO new_user] ...
删除用户：
DROP USER 'user'@'host' [, 'user'@'host'] ...
让MySQL重新加载授权表：
FLUSH PRIVILEGES
以上用户管理命令自带重载授权表功能，此命令在直接修改mysql数据库的授权表后执行
修改用户密码：
- SET PASSWORD [FOR 'user'@'host'] = PASSWORD('cleartext password');
- UPDATE mysql.user SET password=PASSWORD('cleartext password') WHERE User='USERNAME' AND Host='HOST';
- mysqladmin -uUSERNAME -hHOST -p password 'NEW_PASS'
- 修改后记得生效：FLUSH PRIVILEGES
忘记管理员密码的解决办法：
(1) 启动mysqld进程时，使用--skip-grant-tables和--skip-networking选项
CentOS 7：mariadb.service
CentOS 6：/etc/init.d/mysqld
(2) 通过UPDATE命令修改管理员密码
(3) 以正常方式启动mysqld进程

实验6：实现MySQL忘记管理员密码时重置密码

第1步：修改进程启动选项

systemctl stop mariadb.service

vim /usr/lib/systemd/system/mariadb.service
ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables --skip-networking

systemctl daemon-reload
systemctl start mariadb.service

第2步：修改管理员密码

MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('hellomysql') WHERE user='root';
MariaDB [(none)]> exit

第3步：正常方式启动mysql

systemctl stop mariadb.service

vim /usr/lib/systemd/system/mariadb.service
ExecStart=/usr/bin/mysqld_safe --basedir=/usr

systemctl daemon-reload 
systemctl start mariadb.service
mysql -phellomysql

（四）授权管理

授权：GRANT
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]

object_type:
TABLE| FUNCTION| PROCEDURE
                  
priv_level:
*| *.*| db_name.*| db_name.tbl_name| tbl_name| db_name.routine_name
                                      
ssl_option:
SSL| X509| CIPHER 'cipher'| ISSUER 'issuer'| SUBJECT 'subject'                
              
with_option:
GRANT OPTION| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count

查看授权：SHOW GRANTS
SHOW GRANTS [FOR 'user'@'host']
取消授权：REVOKE
REVOKE priv_type [(column_list)][, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM 'user'@'host' [, 'user'@'host'] ...
实验7：授权管理
- 授予指定用户对指定表的查询权限
  GRANT SELECT ON hellodb.students TO 'testusr'@'192.168.136.%' IDENTIFIED BY 'centos';
此时用户没有对本表增加记录的权限
- 增加授予指定用户对指定表的增加记录权限
  GRANT INSERT ON hellodb.students TO 'testusr'@'192.168.136.%' IDENTIFIED BY 'centos';
此时用户没有对本表指定字段内容的更新权限
- 增加授予指定用户对指定表指定字段内容的更新权限
  GRANT UPDATE(AGE) ON hellodb.students TO 'testusr'@'192.168.136.%' IDENTIFIED BY 'centos';
- 查看当前本用户已获得的授权
  SHOW GRANTS;
- 取消之前对本用户的授权
  REVOKE ALL PRIVILEGES ON hellodb.students FROM 'testusr'@'192.168.136.%';