MySQL进阶

管理索引

创建索引

CREATE INDEX [UNIQUE] index_name ON tbl_name (index_col_name[(length)],...);
help CREATE INDEX;

删除索引

DROP INDEX index_name ON tbl_name;

查看索引

SHOW INDEXES FROM [db_name.]tbl_name;

优化表空间

OPTIMIZE TABLE tb_name;

查看索引的使用

SET GLOBAL userstat=1; #可写入配置文件，记录索引的使用情况，过一段你时间查看哪些索引从未被使用过，就可以考虑删除之。
SHOW INDEX_STATISTICS;

EXPLAIN

通过EXPLAIN来分析索引的有效性
查看执行select查询时是否使用索引

explain select clause

获取查询执行计划信息，用来查看查询优化器如何执行查询

输出信息说明：

参考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

MariaDB [hellodb]> explain select * from students where name='xiao qiao';
+------+-------------+----------+-------+---------------+----------+---------+-------+------+-------+
| id   | select_type | table    | type  | possible_keys | key      | key_len | ref   | rows | Extra |
+------+-------------+----------+-------+---------------+----------+---------+-------+------+-------+
|    1 | SIMPLE      | students | const | idx_name      | idx_name | 152     | const |    1 |       |
+------+-------------+----------+-------+---------------+----------+---------+-------+------+-------+

id:当前查询语句中，每个select语句的编号
复杂类型的查询有三种

简单子查询
用于from中的子查询
联合查询：UNION

注意：UNION查询的分析结果会出现一个额外匿名临时表

select_type
简单查询为SIMPLE
复杂查询

SUBQUERY 简单子查询
PRIMARY 最外面的SELECT
DERIVED 用于FROM中的子查询
UNION UNION语句的第一个之后的SELECT语句
UNION RESULT 匿名临时表

table：select语句关联到的表

type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以
下顺序，性能从低到高

ALL: 全表扫描
index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描
range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点
ref: 根据索引返回表中匹配某单个值的所有行
eq_ref：仅返回一个行，但与需要额外与某个参考值做比较
const, system: 直接返回单个行

possible_keys：查询可能会用到的索引
key: 查询中使用到的索引
key_len: 在索引使用的字节数
ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值
rows：MySQL估计为找所有的目标行而需要读取的行数
Extra：额外信息

Using index：MySQL将会使用覆盖索引，以避免访问表
Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤
Using temporary：MySQL对结果排序时会使用临时表
Using filesort：对结果使用一个外部索引排序

并发控制

锁粒度：

表级锁
行级锁

锁：

读锁：共享锁，只读不可写，多个读互不阻塞，所有人可读且不可写
写锁：独占锁,排它锁，一个写锁会阻塞其它读和写锁，自己可读可写，其他人不可读不可写

实现

存储引擎：自行实现其锁策略和锁粒度
服务器级：实现了锁，表级锁；用户可显式请求

分类：

隐式锁：由存储引擎自动施加锁
显式锁：用户手动请求

锁策略：在锁粒度及数据安全性寻求的平衡机制

显示使用锁
LOCK TABLES 加锁

tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... 
lock_type: READ ， WRITE

UNLOCK TABLES 解锁 或者退出当前登录会自动解锁

FLUSH TABLES [tb_name[,…]] [WITH READ LOCK]
如果不加with read lock表示关闭正在打开的表（清除查询缓存），加上的话通常在备份前加全局读锁，对整个数据库实例加读锁，所有人对本实例的所有库都无法更改。

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]
查询时加写或读锁

如果数据库被加了全局锁，导致无法正常使用，可以使用show processlist\G;查看哪个线程加的锁，然后kill process_id；杀掉该线程即可。

事务

事务Transactions：一组原子性的SQL语句，或一个独立工作单元
事务日志：记录事务信息，实现undo,redo等故障恢复功能
ACID特性：

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

启动事务：

BEGIN
BEGIN WORK
START TRANSACTION

结束事务：

COMMIT：提交
ROLLBACK: 回滚，回滚只能针对DML语言有效，对DDL语言无效

注意：只有事务型存储引擎中的DML语句方能支持此类操作

自动提交：set autocommit={1|0} 默认为1，为0时设为非自动提交
建议：显示请求和提交事务，而不要使用”自动提交”功能

事务支持保存点：savepoint
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

事务的隔离级别

事务的隔离级别：从上至下更加严格

1. read uncommitted 可读取到未提交数据，产生脏读
2. read committed 可读到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
3. repeatable read 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使由其他提交的事务修改数据，仍只能读取到未修改前的旧数据。此为mysql默认设置
4. serializabile 可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务，导致并发性能差。

查看mysql默认的隔离级别

MariaDB [(none)]> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
MariaDB [(none)]> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

指定事务的隔离级别
方法一：通过服务器变量tx_isolation指定,默认为REPEATABLE-READ，可在global和SESSION级进行设置

SET tx_isolation=''
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE

方法二：在服务器选项中指定：

[root@mariadb ~]#vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

事务中的并发控制

死锁：

两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态

mysql发现死锁后，会自动牺牲掉持续时间较小的事务，从而解锁。
避免发生死锁的方法：按同序修改表，不要在两个事务中交叉修改表。

事务日志：

事务日志的写入类型为"追加"，因此其操作为"顺序IO"；通常也被称为：预写式日志 write ahead logging
事务日志文件：ib_logfile0,ib_logfile1

mysql中的日志管理

日志类型

事务日志 transaction log
错误日志 error log
通用日志 general log
慢查询日志 slow query log
二进制日志 binary log
中继日志 reley log

事务日志 transaction log

事务型存储引擎自行管理和使用，建议和数据文件分开放

redo log
undo log

innodb事务日志相关配置

show variables like '%innodb_log%';
innodb_log_file_size 5242880 每个日志文件大小
innodb_log_files_in_group 2 日志组成员个数
innodb_log_group_home_dir ./ 事务文件路径
innodb_flush_log_at_trx_commit 默认为1

上述各项均可根据实际需求定制大小，写入配置文件永久生效

innodb_flush_log_at_trx_commit选项说明
说明：设置为1，同时sync_binlog=1表示最高级别的容错
innodb_use_global_flush_log_at_trx_commit的值确定是否可以使用SET语句重置此变量

1默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。
	这是完全遵守ACID特性
0提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。 这样可以提
	供更好的性能，但服务器崩溃可以清除最后一秒的事务
2每次提交后都会写入日志缓冲区，但每秒都会进行一次刷新。 性能比0略好一
	些，但操作系统或停电可能导致最后一秒的交易丢失,一般设置为2比较合理，
	但是涉及金融等数据比较重要的场合慎用
3模拟MariaDB 5.5组提交（每组提交3个同步），此项MariaDB 10.0支持

错误日志

错误日志的记录内容

mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误日志的相关配置

show variables like 'log_error';

错误日志的文件路径

log_error=/path/to/log_error_file

是否记录警告信息至错误日志

log_warnings=1|0 默认值1

通用日志

记录对数据库的通用操作，包括错误的SQL语句
文件：file，默认值
表：table

通用日志的相关设置

general_log=ON|OFF
general_log_file=hostname.log
log_output=table|file|none

查看是否开启通用日志

MariaDB [(none)]> show variables like 'general_log%';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| general_log      | OFF         |
| general_log_file | mariadb.log |
+------------------+-------------+

查看通用日志的存储格式

MariaDB [(none)]> select @@log_output;
+--------------+
| @@log_output |
+--------------+
| FILE         |
+--------------+

查看通用日志表格的信息

MariaDB [mysql]> show table status like 'general_log'\G;

慢查询日志

记录执行查询时长超出指定时长的操作

slow_query_log=ON|OFF 开启或关闭慢查询（建议开启）
long_query_time=N 慢查询的阀值，单位秒（建议设置为2）
slow_query_log_file=HOSTNAME-slow.log 慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
	query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
	上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON 不使用索引或使用全索引扫描，不论
	是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录（建议开启）
log_slow_rate_limit = 1 多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain 记录内容
log_slow_queries = OFF 同slow_query_log 新版已废弃

profiling工具
profiling可一分析一条查询语句各个环节所用的时间，从而我们可以借助profiling分析一条语句查询慢的原因。用法如下；

MariaDB [hellodb]> set profiling=1; #开启profiling功能
MariaDB [hellodb]> show profiles; #查看记录下来的查询速度慢的语句
MariaDB [(none)]> show profile for query num; #查看各个环节用时

二进制日志

记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
功能：通过“重放”日志文件中的事件来生成数据副本
	注意：建议二进制日志和数据文件分开存放

中继日志：relay log
主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

二进制日志记录的三种格式

基于“语句”记录：statement，记录语句，默认模式
基于“行”记录：row，记录数据，日志量较大
混合模式：mixed, 让系统自行判定该基于哪种方式进行

二进制日志文件的构成
有两类文件

日志文件：mysql|mariadb-bin.文件名后缀，二进制格式
	如：mariadb-bin.000001
索引文件：mysql|mariadb-bin.index，文本格式

二进制日志相关的服务器变量

sql_log_bin=ON|OFF：是否记录二进制日志，默认ON;支持session级设定，可用于临时禁用二进制日志。
log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默认OFF，表示不启用二
	进制日志功能，上述两项都开启才可
binlog_format=STATEMENT|ROW|MIXED：二进制日志记录的格式，默认STATEMENT
max_binlog_size=1073741824：单个二进制日志文件的最大体积，到达最
	大值会自动滚动，默认为1G
		说明：文件达到上限时的大小未必为指定的精确值
sync_binlog=1|0：设定是否启动二进制日志即时同步磁盘功能，默认0，由
	操作系统负责同步日志到磁盘
expire_logs_days=N：二进制日志可以自动删除的天数。 默认为0，即不自动删除

二进制日志相关配置
查看mariadb自行管理使用中的二进制日志文件列表，及大小

show {binary|master} logs

查看使用中的二进制日志文件

show master status

查看二进制文件中的指定内容

show binlog events [in 'log_name'] [from pos] [limit [offset,] row_count]
show binlog events in 'mysql-bin.000001' from 6516 limit 2,3

mysqlbinlog：二进制日志查看工具

命令格式：mysqlbinlog [options] log_file…
options

--start-position=# 指定开始位置
--stop-position=#
--start-datetime=
--stop-datetime=
时间格式：YYYY-MM-DD hh:mm:ss
--base64-output[=name]
-v -vvv