MySQL 架构和性能优化

数据文件：ibdata1, ibdata2,存放在datadir定义的目录下
表格式定义：tb_name.frm,存放在datadir定义的每个数据库对应的目录下

两类文件放在对应每个数据库独立目录中
数据文件(存储数据和索引)：tb_name.ibd
表格式定义：tb_name.frm

show engines;

show variables like '%storage_engine%';

vim /etc/my.cnf
[mysqld]
default_storage_engine= InnoDB

show table status from db_name;

show table status like 'tb_name';
show create table tb_name;

CREATE TABLE tb_name(... ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;

https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html

#查看mysqld可用选项列表和及当前值
mysqld --verbose --help
#获取mysqld当前启动选项
mysqld --print-defaults

#查看可用选项列表和当前值
[root@centos8 ~]#/usr/libexec/mysqld --verbose --help

#查看mysqld的当前启动选项
[root@centos8 ~]#/usr/libexec/mysqld --print-defaults
/usr/libexec/mysqld would have been started with the following arguments:
--plugin-load-add=auth_gssapi.so --datadir=/var/lib/mysql --
socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mariadb/mariadb.log --pid-
file=/run/mariadb/mariadb.pid

shell> /usr/bin/mysqld_safe --skip-name-resolve=1 #(跳过域名解析)
shell> /usr/libexec/mysqld --basedir=/usr

vim /etc/my.cnf
[mysqld]
skip_name_resolve=1
skip-grant-tables #(跳过权限表控制)

[root@centos8 ~]#mysqladmin variables |grep skip_grant_tables
[root@centos8 ~]#mysql
Welcome to the MariaDB monitor.Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.17-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 'skip_grant_tables';
Empty set (0.001 sec)

SHOW GLOBAL VARIABLES; #只查看global变量
SHOW [SESSION] VARIABLES;#查看所有变量(包括global和session)

#查看指定的系统变量
SHOW VARIABLES LIKE 'VAR_NAME';
SELECT @@VAR_NAME;

#查看选项和部分变量
[root@centos8 ~]#mysqladmin variables

help SET

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

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

[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.

#方法2
[root@centos8 ~]#mkdir /etc/systemd/system/mariadb.service.d/
[root@node3 ~]#vim /etc/systemd/system/mariadb.service.d/limits.conf
[Service]
LimitNOFILE=65535#默认值为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 |
+-------------------+

[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)

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'; #专门用来统计自数据库启动以来，执行了多少次 SELECT 查询语句。
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 5 |
+---------------+-------+
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)

https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_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

MariaDB [hellodb]> create table test (id int ,name varchar(3));
Query OK, 0 rows affected (0.04 sec)

MariaDB [hellodb]> insert test values(1,'abcde');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [hellodb]> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)

MariaDB [hellodb]> show variables like 'SQL_MODE';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [hellodb]> SET SQL_MODE=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> show variables like 'SQL_MODE';
+---------------+---------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
| Variable_name | Value
|
+---------------+---------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
| sql_mode |
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIV
ISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+---------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
1 row in set (0.00 sec)

MariaDB [hellodb]> insert test values(2,'magedu');
ERROR 1406 (22001): Data too long for column 'name' at row 1

https://developer.aliyun.com/topic/java2020

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)

EXPLAIN SELECT clause

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]> explain select * from students where stuid not in (5,10,20);
+------+-------------+----------+------+---------------+------+---------+------
+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------
+------+-------------+
| 1 | SIMPLE | students | ALL | PRIMARY | NULL | NULL | NULL |
25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------
+------+-------------+


MariaDB [hellodb]> explain select * from students where stuid <> 10 ;
+------+-------------+----------+-------+---------------+---------+---------+---
---+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+---
---+------+-------------+
| 1 | SIMPLE | students | range | PRIMARY | PRIMARY | 4 |
NULL | 24 | Using where |
+------+-------------+----------+-------+---------------+---------+---------+---
---+------+-------------+
1 row in set (0.000 sec)


MariaDB [hellodb]> explain select * from students where age > (select avg(age)
from teachers);
+------+-------------+----------+-------+---------------+---------+---------+---
---+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+---
---+------+-------------+
| 1 | PRIMARY | students | range | idx_age | idx_age | 1 |
NULL | 1 | Using where |
| 2 | SUBQUERY | teachers | ALL | NULL | NULL | NULL |
NULL | 4 | |
+------+-------------+----------+-------+---------------+---------+---------+---
---+------+-------------+

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

MariaDB [hellodb]> show indexes 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:
*************************** 2. row ***************************
Table: students
Non_unique: 1
Key_name: idx_name
Seq_in_index: 1
Column_name: Name
Collation: A
Cardinality: 25
Sub_part: 10
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.000 sec)

MariaDB [hellodb]> explain select * from students where name like 'w%';
+------+-------------+----------+-------+---------------+----------+---------+--
----+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+------+-------------+----------+-------+---------------+----------+---------+--
----+------+-----------------------+
| 1 | SIMPLE | students | range | idx_name | idx_name | 152 |
NULL | 1 | Using index condition |
+------+-------------+----------+-------+---------------+----------+---------+--
----+------+-----------------------+
1 row in set (0.000 sec)


MariaDB [hellodb]> explain select * from students where name like 'x%';
+------+-------------+----------+------+---------------+------+---------+------
+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------
+------+-------------+
| 1 | SIMPLE | students | ALL | idx_name | NULL | NULL | NULL |
25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------
+------+-------------+
1 row in set (0.000 sec)

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
*************************** 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:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: students
Non_unique: 1
Key_name: idx_name_age
Seq_in_index: 1
Column_name: Name
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 3. row ***************************
Table: students
Non_unique: 1
Key_name: idx_name_age
Seq_in_index: 2
Column_name: Age
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
3 rows in set (0.01 sec)

#跳过查询复合索引的前面字段,后续字段的条件查询无法利用复合索引
mysql> explain select * from students where age=20;
+----+-------------+----------+------------+------+---------------+------+------
---+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key |
key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+------
---+------+------+----------+-------------+
| 1 | SIMPLE | students | NULL | ALL | NULL | NULL | NULL
| NULL | 25 | 10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+------
---+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

ysql> desc testlog;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | | NULL | |
| salary | int(11) | YES | | 20 | |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

#创建复合索引
mysql> create index idx_name_salary on testlog(name,salary);
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc testlog;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | MUL | NULL | |
| salary | int(11) | YES | | 20 | |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

ysql> show indexes from testlog\G
*************************** 1. row ***************************
Table: testlog
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 90620
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: testlog
Non_unique: 1
Key_name: idx_name_salary
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 95087
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 3. row ***************************
Table: testlog
Non_unique: 1
Key_name: idx_name_salary
Seq_in_index: 2
Column_name: salary
Collation: A
Cardinality: 99852
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
3 rows in set (0.00 sec)

#覆盖索引
mysql> explain select * from testlog where salary=66666;
+----+-------------+---------+------------+-------+-----------------+-----------
------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key
| key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-----------------+-----------
------+---------+------+--------+----------+--------------------------+
| 1 | SIMPLE | testlog | NULL | index | idx_name_salary |
idx_name_salary | 36 | NULL | 100290 | 10.00 | Using where; Using index
|
+----+-------------+---------+------------+-------+-----------------+-----------
------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

#打开后，会显示语句执行详细的过程
set profiling = ON;

#查看语句,注意结果中的query_id值
show profiles ;

MariaDB [hellodb]> show profiles ;
+----------+------------+-------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------+
| 1 | 0.00019238 | select @@profiling |
| 2 | 0.00115590 | select * from students where age=20 |
| 3 | 0.00006616 | show profiles for query 2 |
| 4 | 4.00319568 | select sleep(1) from teachers |
+----------+------------+-------------------------------------+
4 rows in set (0.000 sec)

#显示语句的详细执行步骤和时长
Show profile for query #;

MariaDB [hellodb]> show profile for query 4;
+------------------------+----------+
| Status | Duration |
+------------------------+----------+
| Starting | 0.000157 |
| Checking permissions | 0.000009 |
| Opening tables | 0.000025 |
| After opening tables | 0.000005 |
| System lock | 0.000004 |
| Table lock | 0.000006 |
| Init | 0.000017 |
| Optimizing | 0.000009 |
| Statistics | 0.000018 |
| Preparing | 0.000028 |
| Executing | 0.000003 |
| Sending data | 0.000070 |
| User sleep | 1.001128 |
| User sleep | 1.000313 |
| User sleep | 1.000834 |
| User sleep | 1.000348 |
| End of update loop | 0.000032 |
| Query end | 0.000003 |
| Commit | 0.000014 |
| Closing tables | 0.000004 |
| Unlocking tables | 0.000003 |
| Closing tables | 0.000012 |
| Starting cleanup | 0.000003 |
| Freeing items | 0.000056 |
| Updating status | 0.000024 |
| Logging slow query | 0.000069 |
| Reset for next command | 0.000004 |
+------------------------+----------+
27 rows in set (0.000 sec)

MariaDB [hellodb]>

#显示cpu使用情况
Show profile cpu for query #

MariaDB [hellodb]> Show profile cpu for query 4;
MariaDB [hellodb]> Show profile cpu for query 4;
+------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+------------------------+----------+----------+------------+
| Starting | 0.000157 | 0.000090 | 0.000065 |
| Checking permissions | 0.000009 | 0.000005 | 0.000004 |
| Opening tables | 0.000025 | 0.000014 | 0.000010 |
| After opening tables | 0.000005 | 0.000003 | 0.000002 |
| System lock | 0.000004 | 0.000002 | 0.000002 |
| Table lock | 0.000006 | 0.000004 | 0.000002 |
| Init | 0.000017 | 0.000010 | 0.000007 |
| Optimizing | 0.000009 | 0.000005 | 0.000004 |
| Statistics | 0.000018 | 0.000010 | 0.000007 |
| Preparing | 0.000028 | 0.000016 | 0.000012 |
| Executing | 0.000003 | 0.000002 | 0.000002 |
| Sending data | 0.000070 | 0.000059 | 0.000000 |
| User sleep | 1.001128 | 0.000665 | 0.000000 |
| User sleep | 1.000313 | 0.000716 | 0.000000 |
| User sleep | 1.000834 | 0.000379 | 0.000100 |
| User sleep | 1.000348 | 0.000319 | 0.000231 |
| End of update loop | 0.000032 | 0.000017 | 0.000012 |
| Query end | 0.000003 | 0.000002 | 0.000002 |
| Commit | 0.000014 | 0.000008 | 0.000005 |
| Closing tables | 0.000004 | 0.000002 | 0.000002 |
| Unlocking tables | 0.000003 | 0.000002 | 0.000001 |
| Closing tables | 0.000012 | 0.000007 | 0.000005 |
| Starting cleanup | 0.000003 | 0.000001 | 0.000001 |
| Freeing items | 0.000056 | 0.000034 | 0.000024 |
| Updating status | 0.000024 | 0.000013 | 0.000010 |
| Logging slow query | 0.000069 | 0.000040 | 0.000029 |
| Reset for next command | 0.000004 | 0.000002 | 0.000001 |
+------------------------+----------+----------+------------+
27 rows in set (0.000 sec)
MariaDB [hellodb]>