首页 > 数据库 >MYSQL的索引、并发控制、各种事务介绍以及日志管理

MYSQL的索引、并发控制、各种事务介绍以及日志管理

时间:2023-02-25 19:31:33浏览次数:38  
标签:hellodb log 索引 MYSQL 并发 mysql 日志 name

今天分享的是mysql数据库中的索引、并发控制、各种事务介绍以及日志管理,在学习过程中对这些内容的理解

INDEX 索引

索引介绍

索引:是排序的快速查找的特殊数据结构,定义作为查找条件的字段上,又称为键key,索引通过存储引 擎实现

索引类型:

  • B+ TREE、HASH、R TREE、FULL TEXT
  • 聚簇(集)索引、非聚簇索引:数据和索引是否存储在一起
  • 主键索引、二级(辅助)索引
  • 稠密索引、稀疏索引:是否索引了每一个数据项
  • 简单索引、组合索引: 是否是多个字段的索引
  • 左前缀索引:取前面的字符做索引
  • 覆盖索引:从索引中即可取出要查询的数据,性能高

可以使用B+Tree索引的查询类型:(假设前提: 姓,名,年龄三个字段建立了一个复合索引)

  • 全值匹配:精确所有索引列,如:姓wang,名xiaochun,年龄30
  • 匹配最左前缀:即只使用索引的第一列,如:姓wang
  • 匹配列前缀:只匹配一列值开头部分,如:姓以w开头的记录
  • 匹配范围值:如:姓ma和姓wang之间
  • 精确匹配某一列并范围匹配另一列:如:姓wang,名以x开头的记录
  • 只访问索引的查询

管理索引

创建索引:

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

删除索引:

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

查看索引:

SHOW INDEX FROM [db_name.]tbl_name;

优化表空间:

OPTIMIZE TABLE tb_name;

查看索引的使用

SET GLOBAL userstat=1;  #MySQL无此变量
SHOW INDEX_STATISTICS;

范例:

MariaDB [hellodb]> SET GLOBAL userstat=1;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> SHOW INDEX_STATISTICS;
Empty set (0.000 sec)
MariaDB [hellodb]> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)
MariaDB [hellodb]> show index from students\G
*************************** 1. row ***************************
Table: students
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: StuID
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.000 sec)
MariaDB [hellodb]> SHOW INDEX_STATISTICS;
Empty set (0.000 sec)
MariaDB [hellodb]> select * from students where stuid=10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
+-------+--------------+-----+--------+---------+-----------+
1 row in set (0.000 sec)
MariaDB [hellodb]> SHOW INDEX_STATISTICS;
+--------------+------------+------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+------------+------------+-----------+
| hellodb | students | PRIMARY | 1 |
+--------------+------------+------------+-----------+
MariaDB [hellodb]> select * from students where stuid=10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
+-------+--------------+-----+--------+---------+-----------+
1 row in set (0.000 sec)
MariaDB [hellodb]> SHOW INDEX_STATISTICS;
+--------------+------------+------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+------------+------------+-----------+
| hellodb | students | PRIMARY | 2 |
+--------------+------------+------------+-----------+
1 row in set (0.000 sec)

NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>in dex_subquery>range>index>ALL //最好到最差

备注:掌握以下10种常见的即可 NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

范例:创建索引和使用索引

MariaDB [hellodb]> create index idx_name on students(name(10));
Query OK, 0 rows affected (0.009 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [hellodb]> show indexes from students\G

MariaDB [hellodb]> explain select * from students where name like 'w%';

MariaDB [hellodb]> explain select * from students where name like 'x%';

范例: 复合索引

mysql> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

范例:创建复合索引

mysql> create index idx_name_age on students(name,age);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | MUL | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> show indexes from students\G

#跳过查询复合索引的前面字段,后续字段的条件查询无法利用复合索引
mysql> explain select * from students where age=20;

范例: 复合索引和覆盖索引

mysql> explain select * from testlog where salary=66666;

使用 profile 工具

#打开后,会显示语句执行详细的过程
set profiling = ON;
#查看语句,注意结果中的query_id值
show profiles ;
#显示语句的详细执行步骤和时长
Show profile for query #
MariaDB [hellodb]> show profile for query 4;
#显示cpu使用情况
Show profile cpu for query #
MariaDB [hellodb]> Show profile cpu for query 4;
#删除
dorp index idx_gebder on student;

并发控制

锁机制

锁类型:

读锁:共享锁,也称为 S 锁,只读不可写(包括当前事务) ,多个读互不阻塞

写锁:独占锁,排它锁,也称为 X 锁,写锁会阻塞其它事务(不包括当前事务)的读和写

锁粒度:

  • 表级锁:MyISAM
  • 行级锁:InnoDB

实现

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

分类:

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

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

显式使用锁

加锁

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]

查询时加写或读锁

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

范例: 加读锁

mysql> lock tables students read ;
Query OK, 0 rows affected (0.00 sec)
mysql> update students set classid=2 where stuid=24;
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be
updated
#解锁
mysql> unlock tables ;
mysql> update students set classid=2 where stuid=24;
Query OK, 1 row affected (1 min 45.52 sec)
Rows matched: 1 Changed: 1 Warnings: 0

范例: 同时在两个终端对同一行记录修改

#同时对同一行记录执行update
#在第一终端提示1行成功
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#在第二终端提示0行修改
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 0 rows affected (0.000 sec)
Rows matched: 1 Changed: 0 Warnings: 0

事务

事务 Transactions:一组原子性的 SQL语句,或一个独立工作单元

事务特性

ACID特性:

  • A:atomicity 原子性;整个事务中的所有操作要么全部成功执行,要么全部失败后回滚
  • C:consistency一致性;数据库总是从一个一致性状态转换为另一个一致性状态,类似于能量守恒定 律(N50周启皓语录)
  • I:Isolation隔离性;一个事务所做出的操作在提交之前,是不能为其它事务所见;隔离有多种隔离 级别,实现并发
  • D:durability持久性;一旦事务提交,其所做的修改会永久保存于数据库中
管理事务

显式启动事务:

BEGIN
BEGIN WORK
START TRANSACTION

结束事务:

#提交,相当于vi中的wq保存退出
COMMIT
#回滚,相当于vi中的q!不保存退出
ROLLBACK

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

自动提交:

set autocommit={1|0}

默认为1,为0时设为非自动提交

建议:显式请求和提交事务,而不要使用"自动提交"功能

事务支持保存点:

SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

查看事务:

#查看当前正在进行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
#以下两张表在MySQL8.0中已取消
#查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
#查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

死锁:

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

范例:找到未完成的导致阻塞的事务(支持Mariadb)

#在第一会话中执行
MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> update students set classid=10;
#在第二个会话中执行
MariaDB [hellodb]> update students set classid=20; #因为上面的会话加锁,所以此处卡动不
动,无法执行
#在第三个会话中执行
MariaDB [hellodb]> show engine innodb status;
...省略...
---TRANSACTION 120, ACTIVE 673 sec
2 lock struct(s), heap size 1136, 28 row lock(s), undo log entries 27
MySQL thread id 13, OS thread handle 139719808595712, query id 206 localhost
root
...省略...
#此指令不支持MySQL8.0
MariaDB [hellodb]> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

#此指令不支持MySQL8.0
MariaDB [hellodb]> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

#查看正在进行的事务
MariaDB [hellodb]> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 123
trx_state: LOCK WAIT
trx_started: 2019-11-22 19:17:06
trx_requested_lock_id: 123:9:3:2
trx_wait_started: 2019-11-22 19:18:50
trx_weight: 2
trx_mysql_thread_id: 15 #线程ID
trx_query: update students set classid=20
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 2
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_is_read_only: 0
trx_autocommit_non_locking: 0

MariaDB [hellodb]> show processlist;

#杀掉未完成的事务
MariaDB [hellodb]> kill 13;
Query OK, 0 rows affected (0.000 sec)

#查看事务锁的超时时长,默认50s
MariaDB [hellodb]> show global variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.001 sec
事务隔离级别

MySQL 支持四种隔离级别,事务隔离级别从上至下更加严格

  • READ UNCOMMITTED:可读取到未提交数据,产生脏读
  • READ COMMITTED:可读取到提交数据,但未提交数据不可读,产生不可重复读,即可读取到多个提交数据,导致每次 读取数据不一致
  • REPEATABLE READ:可重复读,多次读取数据都一致,产生幻读,即读取过程中,即使有其它提交的事务修改数据,仍只能读取到未修改前的旧数据。此为MySQL默认设置
  • SERIALIZABLE:可串行化,未提交的读事务阻塞写事务(加读锁,但不阻塞读事务),或者未提交的写事务阻塞读 和写事务(加写锁,其它事务的读,写都不可以执行)。会导致并发性能差

MVCC和事务的隔离级别:

MVCC(多版本并发控制机制)只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。其 他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行,而不是符合当前 事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

指定事务隔离级别:

  • 服务器变量tx_isolation(MySQL8.0改名为transaction_isolation)指定,默认为REPEATABLEREAD,可在GLOBAL和SESSION级进行设置
#MySQL8.0之前版本
SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE|READ|SERIALIZABLE'
#MySQL8.0
SET transaction_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE|READ|SERIALIZABLE'
  • 服务器选项中指定
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

范例: MySQL8.0 事务隔离级别系统变量tx_isolation已取消

mysql> select @@tx_isolation;
ERROR 1193 (HY000): Unknown system variable 'tx_isolation'

mysql> select @@transaction_isolation;

日志管理

MySQL 支持丰富的日志类型,如下:

  • 事务日志:transaction log

事务日志文件: ib_logfile0, ib_logfile1

  • 错误日志 error log
  • 通用日志 general log
  • 慢查询日志 slow query log
  • 二进制日志 binary log
  • 中继日志 reley log,在主从复制架构中,从服务器用于保存从主服务器的二进制日志中读取的事件
事务日志

事务日志:transaction log

  • redo log:记录某数据块被修改后的值,数据更新前先记录redo log( WALWrite Ahead Log ),可以 用来恢复未写入data file的已成功事务更新的数据
  • undo log:保存与执行的操作相反的操作,即记录某数据被修改前的值,可以用来在事务失败时进 行rollback

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

Innodb事务日志相关配置:

show variables like '%innodb_log%';
innodb_log_file_size 50331648 #每个日志文件大小
innodb_log_files_in_group 2 #日志组成员个数
innodb_log_group_home_dir ./ #事务文件路径

事务日志性能优化

innodb_flush_log_at_trx_commit=0|1|2
1 此为默认值,日志缓冲区将写入日志文件,并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能,但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区,但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些,但操作系统或停电可能导致最后一秒的交易丢失
1.配置为2和配置为0,性能差异并不大,因为将数据从Log Buffer拷贝到OS cache,虽然跨越用户态与内核态,但毕竟只是内存的数据拷贝,速度很快
2.配置为2和配置为0,安全性差异巨大,操作系统崩溃的概率相比MySQL应用程序崩溃的概率,小很多,设置为2,只要操作系统不奔溃,也绝对不会丢数据
错误日志

错误日志

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

错误文件路径

SHOW GLOBAL VARIABLES LIKE 'log_error' ;

范例:

MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'log_error'; 
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| log_error | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.001 sec)

记录哪些警告信息至错误日志文件

#CentOS7 mariadb 5.5 默认值为1
#CentOS8 mariadb 10.3 默认值为2
log_warnings=0|1|2|3... #MySQL5.7之前
log_error_verbosity=0|1|2|3... #MySQL8.0

范例: MySQL8.0变量变化

https://dev.mysql.com/doc/refman/8.0/en/server-systemvariables.html#sysvar_log_error_verbosity
mysql> SHOW GLOBAL VARIABLES LIKE 'log_error_verbosity';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| log_error_verbosity | 2 |
+---------------------+-------+
1 row in set (0.00 sec)
通用日志

通用日志:记录对数据库的通用操作,包括:错误的SQL语句

通用日志可以保存在:file(默认值)或 table(mysql.general_log表)

范例: 启用通用日志并记录至文件中

#默认没有启用通用日志
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
#启用
mysql> set global general_log=1;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
#默认通用日志存放在文件中
mysql> SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)
#通用日志存放的文件路径
mysql> select @@general_log_file;
+----------------------------+
| @@general_log_file |
+----------------------------+
| /var/lib/mysql/centos8.log |
+----------------------------+
1 row in set (0.00 sec)

范例:通用日志记录到表中

#修改通用日志,记录通用日志至mysql.general_log表中
MariaDB [mysql]> set global log_output="table";
MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE 'log_output';

#general_log表是CSV格式的存储引擎
mysql> show table status like 'general_log'\G

#general_log表是CSV的文本文件
[root@centos8 ~]#file /var/lib/mysql/mysql/general_log.CSV
/var/lib/mysql/mysql/general_log.CSV: ASCII text
[root@centos8 ~]#head /var/lib/mysql/mysql/general_log.CSV

MariaDB [mysql]> select * from mysql.general_log\G

#注意:MySQL8.0 argument字段是加密形式

范例: 查找执行次数最多的前三条语句

MariaDB [mysql]> select argument,count(argument) num from mysql.general_log group by argument order by num desc limit 3;
+---------------------------+-----------------+
| argument | num |
+---------------------------+-----------------+
| select * from teachers | 6 |
| select * from general_log | 4 |
| select * from students | 3 |
+---------------------------+-----------------+
3 rows in set (0.002 sec)

范例: Mariadb对访问的语句进行排序

[root@centos8 ~]#mysql -e 'select argument from mysql.general_log' | awk '{sql[$0]++}END{for(i in sql){print sql[i],i}}'|sort -nr
[root@centos8 ~]#mysql -e 'select argument from mysql.general_log' |sort |uniq -c |sort -nr



今天分享的到这就结束了,谢谢大家观看!

标签:hellodb,log,索引,MYSQL,并发,mysql,日志,name
From: https://blog.51cto.com/chengpitang/6085659

相关文章

  • 三天吃透MySQL八股文(2023最新整理)
    本文已经收录到Github仓库,该仓库包含计算机基础、Java基础、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校......
  • 哈希表日志追加式方法的优缺点
    优点追加和分段合并主要是顺序写入,通常比随机写入要快得多。在旋转式磁盘上和SSD中都是适合的。并发和崩溃恢复要简单的多合并旧段可以解决数据文件的碎片化问题缺陷......
  • nginx代理mysql
    [[email protected]]#catmysql.confupstreammysql{server192.168.2.6:3306;}server{listen8083;proxy_passmysql;}[roo......
  • mysqldump使用帮助
    导出sql文件可以使用mysqldump。主要有如下几种操作:导出整个数据库(包括数据库中的数据):mysqldump-h主机-uusername-ppassworddbname>dbname.sql;导出数据库中的......
  • golang 日志
    packagelogimport( "NOONASN/global" "github.com/natefinch/lumberjack" "go.uber.org/zap" "go.uber.org/zap/zapcore" "os" "path" "path/filepath")func......
  • 收集日志方式1:k8s集群通过ELK收集容器控制台输出日志(logstash方式)
    集群架构:控制台日志,相当于容器的1号进程。或者容器内的日志正确和错误日志重定向到了如下/var/log/nginx/access.log->/dev/stdout /var/log/nginx/error.log->/dev/st......
  • PyMySQL连接
    title:Pymysql连接author:杨晓东permalink:Pymysql连接date:2021-10-0211:27:04categories:-投篮tags:-demoPymysqlMySQL数据库_连接"""一、python实......
  • iOS日志记录和异常捕获
    日志记录iOS日志记录当前文件的堆栈、类名、函数名、行号及文件路径等信息NSArray*array=[NSThreadcallStackSymbols];NSLog(@"堆栈信息:%@",array);NSLog(@"当......
  • MySQL_demo_50道习题
    title:MySQL_demo_50道习题author:杨晓东permalink:MySQL_demodate:2021-10-0211:27:04categories:-投篮tags:-demo表名和字段–1.学生表Student(s_id,s......
  • PyMySQL查询
    title:PyMySQL查询author:杨晓东permalink:PyMySQL查询date:2021-10-0211:27:04categories:-投篮tags:-demoPyMySQLMySQL数据库_查询"""数据库查询操......