今天分享的是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
谢谢各位观看！
