MySQL日志与存储引擎
2019-05-26 本文已影响0人
在牛魔角上狂码
MySQL体系结构
数据库
数据库（数据库文件）是一个或者一组二进制文件，通常来说存在与文件系统之上。
数据库实例
由数据库后台进程/线程以及一个共享区域组成(程序的概念),数据库实例是用来操作数据库文件的
注意：MySQL中，数据库实例和数据库是一一对应的。没有Oracle的一对多(RAC)的机制。
MySQL体系结构
-
单进程多线程结构
- 不会影响MySQL的性能，看程序如何写。（多进程程序，进程间通信开销大于多线程）
-
存储引擎的概念
- 可以理解成文件系统，例如FAT32, NTFS, EXT4。 一个表是一个分区，引擎就是分区的文件系统
- 存储引擎的对象就是表
- show tables; 可以看到每个表对应的是上面引擎(Engine)
- 除了特殊情况，我们现在就只考虑INNODB
-
体系结构图
image.png
-
逻辑存储结构
MySQL逻辑存储结构：MySQL Instance -----> Database ----> Schema ---> | tables --- views |
- 一个DB对应一个schema
- 一个DB对应一个文件夹
- 一个表对应一组文件
注意： MySQL中一个Database对应一个Schema,之所以要有这个schema, 是为了兼容其他数据
information_schema数据库不是文件夹，存在于内存中，在启动时创建
MySQL物理存储结构
- MySQL配置文件
-
datadir：存储数据二进制文件的路径
- 表结构的组成
-
frm：表结构定义文件 -
MYI：索引文件 -
MYD：数据文件
-
可以用hexdump -c XXX.frm查看二进制文件(意义不大)
show create table tablename;
mysqlfrm (utilities工具包)
mysqlfrm --diagnostic /data/mysql_data/aaa/.a.frm #可将frm文件转成create table的语句
- 错误日志文件
-
log_err：建议配置成统一的名字，方便定位错误
- 慢查询日志文件
将运行超过某一个时间阈(yu四声)值的SQL语句记录到文件
- MySQL < 5.1 ：以秒为单位
- MySQL >= 5.1 : 以毫秒为单位
- MySQL >= 5.5 : 可以将慢查询日志记录到表
- MySQL >= 5.6 : 以更细的粒度记录慢查询
- MySQL >= 5.7 : 增加timestamps支持
slow_query_log_file： 建议配置成统一的名字，用于优化查询
MySQL慢查询日志
相关参数
image.png
-
slow_query_log：是否开启慢查询日志 -
slow_query_log_file：慢查询日志文件名, 在my.cnf我们已经定义为slow.log，默认是 机器名-slow.log -
log_slow_admin_statements：记录超时的管理操作SQL到慢查询日志，比如ALTER/ANALYZE TABLE -
log_slow_slave_statements：在从服务器上开启慢查询日志 -
long_query_time：制定慢查询阈值, 单位是秒，且当版本 >=5.5.X，支持毫秒。例如0.5即为500ms。大于该值，不包括值本身。例如该值为2，则执行时间正好等于2的SQL语句不会记录
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql>
-
log_queries_not_using_indexes：将没有使用索引的SQL记录到慢查询日志 ，如果一开始因为数据少，查表快，耗时的SQL语句没被记录，当数据量大时，该SQL可能会执行很长时间，需要测试阶段就要发现问题，减小上线后出现问题的概率
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)
mysql>
-
log_throttle_queries_not_using_indexes：限制每分钟内，在慢查询日志中，去记录没有使用索引的SQL语句的次数；版本需要>=5.6.X 。因为没有使用索引的SQL可能会短时间重复执行，为了避免日志快速增大，限制每分钟的记录次数
mysql> show variables like 'log_throttle_queries_not_using_indexes';
+----------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------+-------+
| log_throttle_queries_not_using_indexes | 10 |
+----------------------------------------+-------+
1 row in set (0.01 sec)
mysql>
-
min_examined_row_limit：扫描记录少于改值的SQL不记录到慢查询日志 。结合去记录没有使用索引的SQL语句的例子，有可能存在某一个表，数据量维持在百行左右，且没有建立索引。这种表即使不建立索引，查询也很快，扫描记录很小，如果确定有这种表，则可以通过此参数设置，将这个SQL不记录到慢查询日志。
mysql> show variables like 'min_examined_row_limit';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| min_examined_row_limit | 100 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql>
-
log_output：查询日志的格式，[FILE | TABLE | NONE]，默认是FILE；版本>=5.5。如果设置为TABLE，则记录到mysql.slow_log
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
-
log_timestamps：写入时区信息。可根据需求记录UTC时间或者服务器本地系统时间
mysql> show variables like 'log_timestamps';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| log_timestamps | SYSTEM |
+----------------+--------+
1 row in set (0.00 sec)
mysql>
慢查询日志操作
查看慢查询日志是否开启
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
1 row in set (0.00 sec)
mysql>
如果没有开启
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.01 sec)
mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
1 row in set (0.00 sec)
mysql>
查看慢查询日志的阈(yu 四声)值，如果sql执行时间超过这个阈值，就会记录到慢查询日志中
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql>
mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
| 0 |
+----------+
1 row in set (3.00 sec)
mysql>
当我执行了一个select sleep(3)这个执行时间为3秒的sql语句，明显超过设定的记录慢查询日志的阈值，查看慢查询日志文件记录的内容tail -n 100 /usr/local/mysql/data/slow.log
# Time: 2019-05-24T15:59:24.826670+08:00
# User@Host: root[root] @ localhost [] Id: 28
# Query_time: 3.000250 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1558684764;
select sleep(3);
这里记录：执行时间，用户，执行的sql语句，扫描记录值等信息
当我设置，扫描值少于100时，不记录慢查询日志
mysql> show variables like 'min%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| min_examined_row_limit | 0 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> set min_examined_row_limit = 100;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'min%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| min_examined_row_limit | 100 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> select sleep(4);
+----------+
| sleep(4) |
+----------+
| 0 |
+----------+
1 row in set (4.00 sec)
mysql>
查看慢日志文件，执行select sleep(4);前已清空慢查询日志文件了>/usr/local/mysql/data/slow.log
tail -n 100 /usr/local/mysql/data/slow.log
没有内容显示，刚才我们已经看到扫描记录的数量为0，而设置记录的慢查询的阈值为100，所以是不会记录任何信息
我们都知道慢查询日志的记录方式不单单只有File文件这种方式，还有记录到Table表中。现在我们来操作下将慢查询日志记录到Table中
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set global log_output = 'table';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> show variables like 'min_examined_row_limit';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| min_examined_row_limit | 100 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> set min_examined_row_limit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'min_examined_row_limit';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| min_examined_row_limit | 0 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> select * from slow_log;
Empty set (0.00 sec)
mysql>
mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.00 sec)
mysql> select * from slow_log;
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+-----------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+-----------------+-----------+
| 2019-05-24 16:28:24.713651 | root[root] @ localhost [] | 00:00:05.000274 | 00:00:00.000000 | 1 | 0 | mysql | 0 | 0 | 11 | select sleep(5) | 28 |
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+-----------------+-----------+
1 row in set (0.00 sec)
mysql> mysql> select * from slow_log\G
*************************** 1. row ***************************
start_time: 2019-05-24 16:28:24.713651
user_host: root[root] @ localhost []
query_time: 00:00:05.000274
lock_time: 00:00:00.000000
rows_sent: 1
rows_examined: 0
db: mysql
last_insert_id: 0
insert_id: 0
server_id: 11
sql_text: select sleep(5)
thread_id: 28
*************************** 2. row ***************************
start_time: 2019-05-24 16:28:39.251837
user_host: root[root] @ localhost []
query_time: 00:00:00.000228
lock_time: 00:00:00.000100
rows_sent: 1
rows_examined: 1
db: mysql
last_insert_id: 0
insert_id: 0
server_id: 11
sql_text: select * from slow_log
thread_id: 28
2 rows in set (0.00 sec)
mysql> show create table slow_log\G;
*************************** 1. row ***************************
Table: slow_log
Create Table: CREATE TABLE `slow_log` (
`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`query_time` time(6) NOT NULL,
`lock_time` time(6) NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumblob NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)
mysql>
slow_log表的存储引擎是CSV，性能不好，修改存储为Myisam
mysql> alter table slow_log engine = myisam;
ERROR 1580 (HY000): You cannot 'ALTER' a log table if logging is enabled
# 慢查询在开启中，先把慢查询关闭
mysql>
mysql> set global slow_query_log = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table slow_log engine = myisam;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table slow_log\G;
*************************** 1. row ***************************
Table: slow_log
Create Table: CREATE TABLE `slow_log` (
`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`query_time` time(6) NOT NULL,
`lock_time` time(6) NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumblob NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
现在slow_log的存储引擎已经修改为MYiSAM
MySQL存储引擎
存储引擎的概念
用来处理数据库的相关CRUD操作
每个数据库都有存储引擎，只是MySQL比较强调存储引擎的概念。
MySQL支持的存储引擎
mysql> show engines\G;
*************************** 1. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
9 rows in set (0.00 sec)
ERROR:
No query specified
mysql>
存储引擎
mysql官方存储引擎
- MyISAM
- InnoDB – 推荐；其他引擎已经体停止维护和开发
- Memory
- Federated
- CSV
- Archive
第三方存储引擎
- TokuDB – 开源，适合插入密集型
- InfoBright – 商业，开源版本有数据量限制。属于列存储，面向OLAP场景
-
Spider
第三方存储引擎在特定场合下比较适合，除此之外，都应该使用InnoDB
image.png
存储引擎之MyISAM
MySQL5.1版本之前的默认存储引擎
- 堆表数据结构
- 表锁设计
- 支持数据静态压缩
- 不支持事物
- 数据容易丢失
- 索引容易损坏
- 唯一优点 ：数据文件可以直接拷贝到另一台服务器使用
现在MySQL中还有用MyISAM的表，主要是历史原因。数据库文件以MY开头的基本都是MyISAM的表
- MyISAM还在使用的原因
- 历史原因，需要逐步替换
- 部分如User，DB等系统表(MyISAM引擎)，可以直接拷贝，比较方便
- 性能好，或者存储小不是MyISAM的优点，也不是存在的原因
- MyISAM文件组成
- frm 表结构文件
- MYI 索引文件
- MYD 数据文件
数据文件是堆表数据结构，堆是无序数据的集合
MYI中的叶子节点，指向MYD中的数据页
当数据移动到页外时，需要修改对应指针
- myisamchk
myisamchk通过扫描MYD文件来重建MYI文件；如果MYD文件中某条记录有问题，将跳过该记录
存储引擎之CSV
image.png
- CSV介绍
- CSV - Comma-Separated Values，使用逗号分隔
- 不支持特殊字符
- CSV是一种标准文件格式
- 文件以纯文本形式存储表格数据
- 使用广泛
- CSV文件组成
- frm 表结构
- CSV 数据文件
- CSM 元数据信息
-
CSV特性
image.png
- MySQL CSV存储引擎运行时，即创建CSV文件
- 通过MySQL标准接口来查看和修改CSV文件
- 无需将CSV文件导入到数据库，只需创建相同字段的表结构，拷贝CSV文件即可
- CSV存储引擎表每个字段必须是NOT NULL属性
存储引擎之Federated
image.png
- Federated介绍
- 允许本地访问远程MySQL数据库中表的数据
- 本地不存储任何数据文件
- 类似Oracle中的DBLink
- Federated存储引擎默认不开启, 需要在my.cnf的[mysqld]标签下添加 federated
- MySQL的Federated不支持异构数据库访问，MariaDB中的FederatedX支持
- Federated 语法
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
CONNECTION='mysql://username:password@hostname:port/database/tablename'
例子：
CREATE TABLE `T1` (
`A` VARCHAR(100),
UNIQUE KEY (`A` (30))
) ENGINE=FEDERATED
CONNECTION='MYSQL://david:123@127.0.0.1:3306/TEST/T1';
存储引擎之Memory
- Memory介绍
- 全内存存储的引擎
- 数据库重启后数据丢失
- 支持哈希索引
- 不支持事物
- Memory特性
- 千万不要用Memory存储引擎去做缓存(Cache), 性能上不及Redis和Memcahced
- Memory不能禁用，当涉及内部排序操作的临时表时，使用该存储引擎
- max_heap_table_size决定使用内存的大小，默认时16M
- 无论该表使用的什么引擎，只要使用到临时表，或者指定Memory，都受参数影响
- 当上面设置的内存放不下数据时，(>=5.6)转为MyISAM,(>=5.7)转为InnoDB
注意磁盘上临时路径空间的大小(tmpdir)- 内存使用为会话(SESSION)级别，当心内核OOM
- max_heap_table_size决定使用内存的大小，默认时16M
- 支持哈希索引，且仅支持等值查询
mysql> show global status like "%tmp%tables";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 | -- 内存放不下，转成磁盘存储的数量,如果过大，考虑增大内存参数
| Created_tmp_tables | 4 | -- 创建临时表的数量
+-------------------------+-------+
2 rows in set (0.00 sec)
mysql> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir | /tmp | -- memory转成磁盘存储的路径
+---------------+-------+
1 row in set (0.00 sec)
mysql> show create table User\G
*************************** 1. row ***************************
Table: User
Create Table: CREATE TABLE `User` (
`id` int(11) NOT NULL,
`name` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`) USING HASH -- 对这个字段使用USING HASH,创建hash索引
) ENGINE=MEMORY DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
-
Memory的物理特性
image.png
- 内存不会一次性分配最大空间，而是随着使用逐步增到到最大值
- 通过链表管理空闲空间
- 使用固定长度存储数据
- 不支持BLOB和TEXT类型
- 可以创建自增主键
