MYSQL的存储引擎以及系统数据库

标签：存储 set name 数据库 sec MYSQL MariaDB root centos8

今天分享的是mysql的存储引擎，以及mysql数据库中相关配置状态和相关的变量

存储引擎

MyISAM 存储引擎

MyISAM 引擎特点

不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
MySQL5.5.5 前默认的数据库引擎

MyISAM 存储引擎适用场景

只读（或者写较少）
表较小（可以接受长时间进行修复操作）

MyISAM 引擎文件

tbl_name.frm 表格式定义
tbl_name.MYD 数据文件
tbl_name.MYI 索引文件

InnoDB 引擎

InnoDB引擎特点

行级锁
支持事务，适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚簇索引
崩溃恢复性更好
支持MVCC高并发
从MySQL5.5后支持全文索引
从MySQL5.5.5开始为默认的数据库引擎

MySQL 中的系统数据库

服务器配置和状态

可以通过mysqld选项，服务器系统变量和服务器状态变量进行MySQL的配置和查看状态

服务器选项

注意: 服务器选项用横线,不用下划线

获取系统变量

SHOW GLOBAL VARIABLES; #只查看global变量
SHOW [SESSION] VARIABLES;#查看所有变量(包括global和session)
#查看指定的系统变量
SHOW VARIABLES LIKE 'VAR_NAME';
SELECT @@VAR_NAME;
#查看选项和部分变量
[root@centos8 ~]#mysqladmin variables

修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效

SET GLOBAL system_var_name=value;
SET @@global.system_var_name=value;

修改会话变量：

SET [SESSION] system_var_name=value;
SET @@[session.]system_var_name=value;

范例: character_set_results是系统变量并非服务器选项

[root@centos8 ~]#mysql 
MariaDB [(none)]> show variables like 'character_set_results';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| character_set_results | utf8 |
+-----------------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]> set character_set_results="utf8mb4";
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show variables like 'character_set_results';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| character_set_results | utf8mb4 |
+-----------------------+---------+
1 row in set (0.001 sec)
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
character_set_results=utf8mb4 
#character_set_results不是服务器选项,写入配置文件将导致无法启动
[root@centos8 ~]#systemctl restart mariadb
Job for mariadb.service failed because the control process exited with error 
code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.

范例：修改mysql的最大并发连接数

注意: CentOS 8.2 已无此问题,CentOS7 仍有此问题

#默认值为151
[root@centos8 ~]#mysqladmin variables |grep 'max_connections'
| max_connections                                       | 151
[root@centos8 ~]#mysql
MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.001 sec)
MariaDB [hellodb]> set global max_connections=2000;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 2000 |
+-----------------+-------+
1 row in set (0.001 sec)
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
max_connections = 8000
[root@centos8 ~]#systemctl restart mariadb
#验证结果,因为系统限制无法生效
[root@centos8 ~]#mysql -uroot -p
Server version: 10.3.11-MariaDB-log MariaDB Server
.....
MariaDB [(none)]> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               594 |
+-------------------+
1 row in set (0.000 sec)
#修改系统限制
#方法1
[root@centos8 ~]#vim /usr/lib/systemd/system/mariadb.service 
[Service]
#加下面一行
LimitNOFILE=65535
#方法2
[root@centos8 ~]#mkdir /etc/systemd/system/mariadb.service.d/
[root@node3 ~]#vim /etc/systemd/system/mariadb.service.d/limits.conf
[Service]
LimitNOFILE=65535
[root@centos8 ~]#systemctl daemon-reload
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#mysql -uroot -p -e "select @@max_connections"
Enter password: 
+-------------------+
| @@max_connections |
+-------------------+
|              8000 |
+-------------------+

范例：修改页大小

说明：初始化数据目录后，不能更改此系统变量的值。在MariaDB实例启动时设置InnoDB的页面大小，此后保持不变。

[root@centos8 ~]#mysqladmin variables |grep innodb_page_size
| innodb_page_size                                       | 16384
[root@centos8 ~]#mysql
MariaDB [(none)]> show variables like "innodb_page_size";
+------------------+-------+
| Variable_name   | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.001 sec)
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
innodb_page_size=64k 

[root@centos8 ~]#rm -rf /var/lib/mysql/*
[root@centos8 ~]#systemctl restart mariadb

[root@centos8 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like "innodb_page_size";
+------------------+-------+
| Variable_name   | Value |
+------------------+-------+
| innodb_page_size | 65536 |
+------------------+-------+
1 row in set (0.001 sec)

服务器状态变量

服务器状态变量：分全局和会话两种

状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改

SHOW GLOBAL STATUS;
SHOW [SESSION] STATUS;

范例：

MariaDB [(none)]> show status like "innodb_page_size";
+------------------+-------+
| Variable_name   | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.001 sec)
MariaDB [hellodb]> SHOW GLOBAL STATUS like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select   | 5     |
+---------------+-------+
1 row in set (0.001 sec)

服务器变量 SQL_MODE

SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置

常见MODE:

NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户
NO_ZERO_DATE：在严格模式，不允许使用'0000-00-00'的时间
ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY 中出现，那么将认为这个SQL是不合法的
NO_BACKSLASH_ESCAPES：反斜杠"\"作为普通字符而非转义字符
PIPES_AS_CONCAT：将"||"视为连接操作符而非"或"运算符

范例：CentOS 8 修改SQL_MODE变量实现分组语句控制

MariaDB [hellodb]> show variables like 'sql_mode';
+---------------+----------------------------------------------------------------
---------------------------+
| Variable_name | Value                                                         
                            |
+---------------+----------------------------------------------------------------
---------------------------+
| sql_mode     | 
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUB
STITUTION |
+---------------+----------------------------------------------------------------
---------------------------+
1 row in set (0.001 sec)
MariaDB [hellodb]> select classid,count(*) from students group by classid;
+---------+----------+
| classid | count(*) |
+---------+----------+
|    NULL |        2 |
|       1 |        4 |
|       2 |        3 |
|       3 |        4 |
|       4 |        4 |
|       5 |        1 |
|       6 |        4 |
|       7 |        3 |
+---------+----------+
8 rows in set (0.000 sec)
MariaDB [hellodb]> select stuid,classid,count(*) from students group by classid;
+-------+---------+----------+
| stuid | classid | count(*) |
+-------+---------+----------+
|    24 |    NULL |        2 |
|     2 |       1 |        4 |
|     1 |       2 |        3 |
|     5 |       3 |        4 |
|     4 |       4 |        4 |
|     6 |       5 |        1 |
|     9 |       6 |        4 |
|     8 |       7 |        3 |
+-------+---------+----------+
8 rows in set (0.001 sec)
#修改SQL_MODE
MariaDB [hellodb]> set sql_mode="ONLY_FULL_GROUP_BY";
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> show variables like 'sql_mode';
+---------------+--------------------+
| Variable_name | Value             |
+---------------+--------------------+
| sql_mode     | ONLY_FULL_GROUP_BY |
+---------------+--------------------+
1 row in set (0.001 sec)
MariaDB [hellodb]> select classid,count(*) from students group by classid;
+---------+----------+
| classid | count(*) |
+---------+----------+
|    NULL |        2 |
|       1 |        4 |
|       2 |        3 |
|       3 |        4 |
|       4 |        4 |
|       5 |        1 |
|       6 |        4 |
|       7 |        3 |
+---------+----------+
8 rows in set (0.001 sec)
MariaDB [hellodb]> select stuid,classid,count(*) from students group by classid;
ERROR 1055 (42000): 'hellodb.students.StuID' isn't in GROUP BY

谢谢各位观看！

标签：存储,set,name,数据库,sec,MYSQL,MariaDB,root,centos8
From： https://blog.51cto.com/chengpitang/6083896