mysql高阶应用-索引、并发控制、事务、日志数据库

文章目录

一、索引
二、并发控制：锁
三、事务
四、日志
- （一）事务日志：
- （二）错误日志
- （三）通用日志
- （四）慢查询日志
- （五）客户端日志
- （六）二进制日志

一、索引 1.使用索引优缺点：
优点：

索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O

缺点：

索引占用额外空间，影响插入速度；一旦数据更新（如插入，删除），需要维护索引（数据重新排序）；

建议：查询频率高的建立索引，变更数据多的要考虑是否需要建立索引
2.索引原理：
将物理上存放的不规则的，无顺序的数据，通过索引在逻辑上有规则有顺序的连接在一起，提高查询效率。

数值顺序：
字符顺序：与对应的字符集的字符序相关，如按字母顺序：ab与aa,第一个字符相同的情况下，对第二个字符排序，以此类推

3.查询方式：

连续查询：如查年龄10~20岁的学生信息
单独查询：如查年龄为20岁的学生信息

4.分类：

B+TREE：
HASH：
R TREE：
聚簇（集）索引：数据和索引存储在一起
非聚簇索引：数据和索引分开存放
简单索引（单列索引）：仅一个字段充当索引
复合索引：两个以上字段作为索引（需要指定索引字段顺序）；第一个索引固定的情况下，- 第二个索引才是按顺序排序的，跳过第一个字段直接查询第二个字段，无法利用索引
主键索引：
主键和唯一键的区别：
主键只能有一个，唯一键可以有多个；
主键值不能为空，唯一键可以为空；
唯一键索引：保证字段值都是唯一的，如手机号
二级（辅助）索引
稠密索引、稀疏索引：是否索引了每一个数据项
简单索引、组合索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高

5.B-Tree 与 B+Tree
（1）B-Tree特性
磁盘块中存放的数据块（每个数据块包含的记录信息字段数越少）数越多，查询越快（树深越浅）
（2）B+Tree特性
（1）每个数据块存放的是索引字段的值，不存放具体记录的完整数据，记录完整信息放在叶子节点
（2）查询速度都一样，都要查询到叶子节点；
（3）链表：指定下一个数据块位置
6.建立B+tree索引条件：
（1）在查询的where查询条件建立索引；
（2）查询记录数重复比较少的情况，如性别建立索引无意义，可在电话号码上建立索引
（3）读多写少情况
7.B-Tree与B+Tree的区别（优缺点）
B-Tree 缺点
对应范围查找无能为力，范围内每个数都要从根找起
8.管理索引

创建索引

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

查看索引

SHOW INDEXES FROM [db_name.]tbl_name;

删除索引

DROP INDEX index_name ON tbl_name; ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

查看索引的使用

SET GLOBAL userstat=1; SHOW INDEX_STATISTICS;

优化表空间：

OPTIMIZE TABLE tb_name;

9.分析索引有效性
explain：获取查询执行计划信息，用来查看查询优化器如何执行查询
EXPLAIN Output Format 输出格式参见：
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
添加索引与未添加索引时间效率对比
二、并发控制：锁锁类型：

读锁：共享锁，只读不可写（包括当前事务），多个读互不阻塞
写锁：独占锁，排它锁，写锁会阻塞其它事务（不包括当前事务）的读和写

锁粒度：

表级锁
行级锁

锁调用方式：

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

显示调用锁

LOCK TABLES #加锁 tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ...lock_type: READ [LOCAL] | [LOW_PRIORITY] WRITEUNLOCK TABLES#解锁

关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁

FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]

查询时加写或读锁

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

三、事务 ACID特性：

A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态
如银行转账，A账户转账给B账户，A减去10000，B加10000；A不减，B不加，不会出现转来转去把钱转丢
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
如以下场景：原来工资10000，现正在执行加1000事务，这个事务没有结束，可能会撤销；另一个事务查看工资记录；不同隔离级别查看的结果不一样。
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中
类比开会后的决议案

作用域：
对DML语句作用：
对DDL语句没有作用：
启动事务：

BEGIN
BEGIN WORK
START TRANSACTION

结束事务：

COMMIT：提交
ROLLBACK: 回滚

事务隔离级别：
从上到下越来越严格：

read uncommitted：A事务commit前，B事务可查看A正在修改的数据
read committed：A事务commit的某个时间段在更新某个数据，在B事务里不同时间读到的数据不一样，可能A事务commit后的结果和B事务查看的结果不一致，导致不可连续读。
repeatable read：（mysql默认设置）A事务commit前某个时间段在更新某个数据，B事务看的数据不变，可连续读；但A事务commit更新后的数据，B事务看到的数据仍然是未变更前的数据，产生幻读。配合备份数据库，备份数据期间，备份的数据时稳定不变的。
serializabile：可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差

文章图片

事务死锁
四、日志日志分类：

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

（一）事务日志：

#有#注释的是5.5.60-MariaDB的变量 MariaDB [hellodb]> show variables like '%innodb_log%'; +-------------------------------+------------+ | Variable_name| Value| +-------------------------------+------------+ | innodb_log_arch_dir|| | innodb_log_arch_expire_sec| 0| | innodb_log_archive| OFF| | innodb_log_block_size| 0|#日志块大小 | innodb_log_buffer_size| 16777216| #日志缓冲区大小 | innodb_log_checksum_algorithm | DEPRECATED | | innodb_log_checksums| ON| | innodb_log_compressed_pages| ON| | innodb_log_file_size| 50331648| #日志文件大小 | innodb_log_files_in_group| 2| #事务文件路径 | innodb_log_group_home_dir| ./| #事务文件路径 | innodb_log_optimize_ddl| ON| | innodb_log_write_ahead_size| 8192| +-------------------------------+------------+

事务日志文件：

#5.5.60-MariaDB 默认单个事务日志文件大小5M #10.2.25-MariaDB-log默认48M #建议事务日志大小设置大一些数量多一些，防止因事务日志太小，大事务来回覆盖日志文件无法执行下去 [root@CentOS7 ~]#ll /var/lib/mysql/ -rw-rw---- 1 mysql mysql48M Jul 11 10:24 ib_logfile0 -rw-rw---- 1 mysql mysql48M Jul 10 19:27 ib_logfile1

innodb_flush_log_at_trx_commit=1
1 默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。这是完全遵守ACID特性
0 提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。这样可以提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入日志缓冲区，但每秒都会进行一次刷新。性能比0略好一些，但操作系统或停电可能导致最后一秒的交易丢失
3 模拟MariaDB 5.5组提交（每组提交3个同步），此项MariaDB 10.0支持
建议将事务日志存放到单独磁盘，至少是单独分区

[root@CentOS7 ~]#vim /etc/my.cnf innodb_log_group_home_dir=/data/mariadb [root@CentOS7 ~]#ll -d /data/mariadb/ [root@CentOS7 ~]#chown mysql:mysql /data/mariadb/ [root@CentOS7 ~]#ll -d /data/mariadb/ [root@CentOS7 ~]#systemctl restart mariadb [root@CentOS7 ~]#ll -h /data/mariadb/

文章图片

（二）错误日志记录内容
1.mysqld启动和关闭过程中输出的事件信息
2.mysqld运行中产生的错误信息
3.event scheduler运行一个event时产生的日志信息
4.在主从复制架构中的从服务器上启动从服务器线程时产生的信息

#5.5.60-MariaDB 默认错误日志路径/var/log/mariadb/mariadb.log #10.2.25-MariaDB-lo默认未指定错误日志路径 MariaDB [hellodb]>show global variables like 'log_error';

设置错误日志路径：

[root@CentOS7 ~]#vim /etc/my.cnf log-error=/data/mariadb/mariadb.log

（三）通用日志记录数据库的操作，如各种SQL语句；对性能有影响，一般用于排错时启用。

#默认通用日志是关闭的，并且mariadb安装目录中没有CentOS7.log文件，只有开启通用日志重启服务后才生成 #general_log=ON|OFF #general_log_file=HOSTNAME.log #log_output=TABLE|FILE|NONE通用日志输出方式，table是放到mysql.general_log表中 MariaDB [hellodb]> show variables like 'general%'; +------------------+-------------+ | Variable_name| Value| +------------------+-------------+ | general_log| OFF| | general_log_file | CentOS7.log | +------------------+-------------+MariaDB [hellodb]> show variables like 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output| FILE| +---------------+-------+

通用日志放到文件方式：/var/lib/mysql/CentOS7.log

文章图片

通用日志放到数据库方式：mysql.general_log

文章图片

（四）慢查询日志

#默认慢查询日志记录查询时间：10s MariaDB [mysql]> show variables like 'long%'; +-----------------+-----------+ | Variable_name| Value| +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ #默认慢查询日志启用状态：未启用 #mariadb安装目录中没有CentOS7-slow.log文件，只有开启通用日志重启服务后才生成 MariaDB [mysql]> show variables like 'slow_query_log'; +----------------+-------+ | Variable_name| Value | +----------------+-------+ | slow_query_log | OFF| +----------------+-------+ MariaDB [hellodb]> show variables like 'log_queries_not_using_indexes'; +-------------------------------+-------+ | Variable_name| Value | +-------------------------------+-------+ | log_queries_not_using_indexes | OFF| +-------------------------------+-------+#是否启用查询执行过程详细信息 MariaDB [hellodb]> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling| OFF| +---------------+-------+

慢查询日志常用变量

slow_query_log=ON|OFF#开启或关闭慢查询 long_query_time=N#慢查询的阀值，单位秒 slow_query_log_file=HOSTNAME-slow.log#慢查询日志文件路径，主机名-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#记录内容

启用慢查询日志配置：

[root@CentOS7 data]#vim /etc/my.cnf slow_query_log long_query_time=3 #设置超过3秒记录慢查询日志 profiling

慢查询语句

MariaDB [hellodb]> select sleep(1) from students;

文章图片

查看某条慢查询语句具体执行时间

MariaDB [hellodb]> set profiling=on; MariaDB [hellodb]> select sleep(1) from students; MariaDB [hellodb]> show profiles; +----------+------------+---------------------------------+ | Query_ID | Duration| Query| +----------+------------+---------------------------------+ |1 | 0.00035217 | show variables like 'profiling' | |2 | 4.00351785 | select sleep(1) from teachers| |3 | 0.00005712 | show profiles for query 2| +----------+------------+---------------------------------+ 3 rows in set (0.00 sec)MariaDB [hellodb]> show profile for query 2; +----------------------+----------+ | Status| Duration | +----------------------+----------+ | starting| 0.000127 |开始 | checking permissions | 0.000007 |检查权限 | Opening tables| 0.000012 |打开表 | After opening tables | 0.000006 |打开表后 | System lock| 0.000004 |系统锁 | Table lock| 0.000002 |锁表 | After table lock| 0.000004 |锁表后 | init| 0.000016 |初始化 | optimizing| 0.000007 |优化 | statistics| 0.000014 |统计 | preparing| 0.000008 |准备 | executing| 0.000003 |执行 | Sending data| 0.000078 |发送数据 | User sleep| 1.000871 | | User sleep| 1.000847 | | User sleep| 1.001120 | | User sleep| 1.000285 | | end| 0.000014 |结束 | query end| 0.000023 |查询结束 | closing tables| 0.000008 |关闭表 | freeing items| 0.000006 |释放项目 | updating status| 0.000013 |更新状态 | logging slow query| 0.000041 |记录慢查询 | cleaning up| 0.000002 |清理 +----------------------+----------+

注：
1.执行的事务中，如果有插入数据的操作，在事务结束前，除了事务日志文件大小在变大，同时数据文件大小也会随事务中插入数据量变大；但是如果事务以rollback回滚方式结束事务，则数据文件显示大小并没有变化；
2.如果此时不通过事务再插入数据，在为达到之前事务插入的数据量前，数据文件大小显示一直没有变化，直到插入数据量大于之前事务准备插入的数据量，数据文件大小才不断增加！
3.即使把表情况，数据文件大小仍然不会缩减

[root@CentOS7 ~]#ll -h /var/lib/mysql/hellodb/teachers.ibd #10K [root@CentOS7 ~]#msyql -uroot -p MariaDB [hellodb]> begin; MariaDB [hellodb]> call sp_testlog; #创建testlog表并插入10万条数据的存储过程 MariaDB [hellodb]> rollback; [root@CentOS7 ~]#ll -h /var/lib/mysql/hellodb/teachers.ibd #12M MariaDB [hellodb]> call sp_testlog; [root@CentOS7 ~]#ll -h /var/lib/mysql/hellodb/teachers.ibd #12M MariaDB [hellodb]> call sp_testlog; [root@CentOS7 ~]#ll -h /var/lib/mysql/hellodb/teachers.ibd #24M MariaDB [hellodb]> delete from testlog; [root@CentOS7 ~]#ll -h /var/lib/mysql/hellodb/teachers.ibd #24M MariaDB [hellodb]> optimize tables testlog; #优化数据库 [root@CentOS7 ~]#ll -h /var/lib/mysql/hellodb/teachers.ibd #0M

（五）客户端日志

#用户家目录.mysql_history存放客户端执行的SQL语句 [root@CentOS7 ~]#cd [root@CentOS7 ~]#ll -a [root@CentOS7 ~]#cat .mysql_history

文章图片

（六）二进制日志记录SQL语句，记录对数据库的增删改，不记录查操作；日志不像事务日志有覆盖现象，日志一直累积。
强烈建议：二进制日志单独存放磁盘，至少单独分区

#两个变量都启用才行 MariaDB [hellodb]> show variables like 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin| ON| +---------------+-------+ 1 row in set (0.00 sec)MariaDB [hellodb]> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin| OFF| +---------------+-------+ 1 row in set (0.00 sec)#log_bin必须写配置文件开启 #sql_log_bin用于临时开启、关闭二进制日志; 会话级变量，只能在一个ssh的shell窗口临时关闭 MariaDB [hellodb]> set log_bin=on; ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

二进制日志相关变量

sql_log_bin=ON|OFF #否记录二进制日志，默认ON log_bin=/PATH/BIN_LOG_FILE #指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可 binlog_format=STATEMENT|ROW|MIXED #二进制日志记录的格式，默认STATEMENT,建议最好改为ROW，其次使用Mixed max_binlog_size=1073741824 #单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G #说明：文件达到上限时的大小未必为指定的精确值 sync_binlog=1|0 #设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘考虑性能选择0,考虑数据安全性选1 expire_logs_days=N #二进制日志可以自动删除的天数。默认为0，即不自动删除

设置开启二进制日志配置：

[root@CentOS7 ~]#mkdir /data/logbin [root@CentOS7 ~]#chown mysql.mysql /data/logbin/ [root@CentOS7 ~]#vim /etc/my.cnf log_bin=/data/logbin/mysql-bin#log_bin=文件名路径+前缀，不写按默认路径和文件名前缀生成 binlog_format=row#二进制日志记录形式 [root@CentOS7 data]#systemctl restart mariadb [root@CentOS7 data]#ll /var/lib/mysql/ CentOS7-bin.000001 #10.2.25-MariaDB-lo 默认二进制文件名 [root@CentOS7 data]#ll /var/lib/mysql/ mariadb-bin.000001 #5.5.60-MariaDB 默认二进制文件名

文章图片

如何查看二进制文件？
（1）数据库自带命令方式查看：

#查看所有二进制日志文件 MariaDB [hellodb]> show binary logs; +------------------+-----------+ | Log_name| File_size | +------------------+-----------+ | mysql-bin.000001 |29078035 | +------------------+-----------+ 1 row in set (0.00 sec)MariaDB [hellodb]> show master logs; +------------------+-----------+ | Log_name| File_size | +------------------+-----------+ | mysql-bin.000001 |29078035 | +------------------+-----------+ 1 row in set (0.00 sec)#查看当前正在使用的二进制日志文件 #Position 通过大小标记日志位置，用于数据库还原判定起始位置 MariaDB [hellodb]> show master status; +------------------+----------+--------------+------------------+ | File| Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 29078035 ||| +------------------+----------+--------------+------------------+#以文件形式查看二进制文件 MariaDB [hellodb]> show binlog events in 'CentOS7-bin.000001'; #查看某个位置之后的 MariaDB [hellodb]> show master status; MariaDB [hellodb]> insert students (name,age) values('xiaomi',30); MariaDB [hellodb]> show master status; MariaDB [hellodb]> show binlog events in 'CentOS7-bin.000001' from 54756314; +--------------------+----------+------------+-----------+-------------+---------------------------------------------------------------+ | Log_name| Pos| Event_type | Server_id | End_log_pos | Info| +--------------------+----------+------------+-----------+-------------+---------------------------------------------------------------+ | CentOS7-bin.000001 | 54756314 | Gtid|1 |54756356 | BEGIN GTID 0-1-200003| | CentOS7-bin.000001 | 54756356 | Intvar|1 |54756388 | INSERT_ID=27| | CentOS7-bin.000001 | 54756388 | Query|1 |54756504 | use `hellodb`; insert students (name,age) values('xiaomi',30) | | CentOS7-bin.000001 | 54756504 | Xid|1 |54756535 | COMMIT /* xid=600027 */| +--------------------+----------+------------+-----------+-------------+---------------------------------------------------------------+

文章图片

（2）客户端工具查看二进制日志：mysqlbinlog
使用语法

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

[root@CentOS7 data]# mysqlbinlog --start-position=54756314 /var/lib/mysql/CentOS7-bin.000001 [root@CentOS7 data]# mysqlbinlog --start-position=54756314 /var/lib/mysql/CentOS7-bin.000001 -v #-v在最后能看到SQL语句

加v与不加v的区别：
不加v：

文章图片

加v：

文章图片

清除指定二进制日志：
PURGE { BINARY | MASTER } LOGS
{ TO ‘log_name’ | BEFORE datetime_expr }
示例：
PURGE BINARY LOGS TO ‘mariadb-bin.000003’; 删除3之前的日志
PURGE BINARY LOGS BEFORE ‘2019-01-23’;
PURGE BINARY LOGS BEFORE ‘2019-03-22 09:25:30’;
删除所有二进制日志，index文件重新记数
【mysql高阶应用-索引、并发控制、事务、日志】RESET MASTER [TO #]; 删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB10.1.6开始支持TO #
切换日志文件：
FLUSH LOGS;