专栏首页别先生CentOS7系统使用rpm方式安装MySQL5.7

CentOS7系统使用rpm方式安装MySQL5.7

参考:https://blog.csdn.net/wudinaniya/article/details/81094578

1、首先去mysql官网下载rpm包,一个是server包一个是client包。官网下载地址:https://www.mysql.com/downloads/

这里用mysql community server里边的。这里使用的是5.7.x版本了,不使用Mysql8.x版本了。

我这里使用的是Centos7.x版本的64位操作系统,使用mysql5.7版本的数据库。查看linux操作系统版本和系统内核版本,如下所示:

1 [root@k8s-node3 ~]# cat /etc/redhat-release  # 查看操作系统版本  
2 CentOS Linux release 7.6.1810 (Core) 
3 [root@k8s-node3 ~]# uname -r  # 检查系统内核版本
4 3.10.0-957.el7.x86_64
5 [root@k8s-node3 ~]# 

选择对应的Linux版本和x86/x64进行下载,可以选择 RPM Bundle,下载完记得解压 tar -xvf xxx.tar。

可以离线下载上传或者在线下载包,如下所示:

 1 [root@k8s-node3 package]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar
 2 --2020-07-15 13:46:49--  https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar
 3 Resolving downloads.mysql.com (downloads.mysql.com)... 137.254.60.14
 4 Connecting to downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443... failed: Connection refused.
 5 [root@k8s-node3 package]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar
 6 --2020-07-15 13:47:14--  https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar
 7 Resolving dev.mysql.com (dev.mysql.com)... 137.254.60.11
 8 Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:443... connected.
 9 HTTP request sent, awaiting response... 302 Found
10 Location: https://cdn.mysql.com//archives/mysql-5.7/mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar [following]
11 --2020-07-15 13:47:16--  https://cdn.mysql.com//archives/mysql-5.7/mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar
12 Resolving cdn.mysql.com (cdn.mysql.com)... 104.85.161.42
13 Connecting to cdn.mysql.com (cdn.mysql.com)|104.85.161.42|:443... connected.
14 HTTP request sent, awaiting response... 200 OK
15 Length: 598026240 (570M) [application/x-tar]
16 Saving to: ‘mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar’
17 
18 20% [=================>                                                                     ] 125,263,632 2.17MB/s  eta 4m 55s 

也可以细化下载,下载须要的mysql组件,有4个:分别是 server、client、common、libs。

卸载旧版本的MySql,如果没有的话,则跳过即可,直接安装mysql。

1 [root@k8s-node3 package]# rpm -qa | grep mysql
2 [root@k8s-node3 package]# 

如果有的话,将会列出旧版本MySql的组件列表,逐个删除掉旧的组件,使用命令rpm -e --nodeps {-file-name}进行移除操作,移除的时候可能会有依赖,要注意一定的顺序,{-file-name}是待删除的mysql的文件。我这里是没有,直接略过了。

2、使用 rpm 命令方式安装mysql组件,使用命令rpm -ivh {-file-name}进行安装操作。按照依赖关系依次安装rpm包 依赖关系依次common→libs→client→server。如下所示:

备注:在ivh中, i-install安装、v-verbose进度条、h-hash哈希校验。

 1 [root@k8s-node3 package]# rpm -ivh mysql-community-common-5.7.30-1.el7.x86_64.rpm
 2 warning: mysql-community-common-5.7.30-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
 3 Preparing...                          ################################# [100%]
 4     file /usr/share/mysql/czech/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
 5     file /usr/share/mysql/danish/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
 6     file /usr/share/mysql/dutch/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
 7     file /usr/share/mysql/english/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
 8     file /usr/share/mysql/estonian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
 9     file /usr/share/mysql/french/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
10     file /usr/share/mysql/german/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
11     file /usr/share/mysql/greek/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
12     file /usr/share/mysql/hungarian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
13     file /usr/share/mysql/italian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
14     file /usr/share/mysql/japanese/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
15     file /usr/share/mysql/korean/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
16     file /usr/share/mysql/norwegian-ny/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
17     file /usr/share/mysql/norwegian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
18     file /usr/share/mysql/polish/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
19     file /usr/share/mysql/portuguese/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
20     file /usr/share/mysql/romanian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
21     file /usr/share/mysql/russian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
22     file /usr/share/mysql/serbian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
23     file /usr/share/mysql/slovak/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
24     file /usr/share/mysql/spanish/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
25     file /usr/share/mysql/swedish/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
26     file /usr/share/mysql/ukrainian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
27     file /usr/share/mysql/charsets/Index.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
28     file /usr/share/mysql/charsets/armscii8.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
29     file /usr/share/mysql/charsets/ascii.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
30     file /usr/share/mysql/charsets/cp1250.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
31     file /usr/share/mysql/charsets/cp1251.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
32     file /usr/share/mysql/charsets/cp1256.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
33     file /usr/share/mysql/charsets/cp1257.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
34     file /usr/share/mysql/charsets/cp850.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
35     file /usr/share/mysql/charsets/cp852.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
36     file /usr/share/mysql/charsets/cp866.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
37     file /usr/share/mysql/charsets/dec8.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
38     file /usr/share/mysql/charsets/geostd8.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
39     file /usr/share/mysql/charsets/greek.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
40     file /usr/share/mysql/charsets/hebrew.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
41     file /usr/share/mysql/charsets/hp8.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
42     file /usr/share/mysql/charsets/keybcs2.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
43     file /usr/share/mysql/charsets/koi8r.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
44     file /usr/share/mysql/charsets/koi8u.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
45     file /usr/share/mysql/charsets/latin1.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
46     file /usr/share/mysql/charsets/latin2.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
47     file /usr/share/mysql/charsets/latin5.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
48     file /usr/share/mysql/charsets/latin7.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
49     file /usr/share/mysql/charsets/macce.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
50     file /usr/share/mysql/charsets/macroman.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
51     file /usr/share/mysql/charsets/swe7.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
52 [root@k8s-node3 package]# rpm -ivh mysql-community-libs-5.7.30-1.el7.x86_64.rpm
53 warning: mysql-community-libs-5.7.30-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
54 error: Failed dependencies:
55     mysql-community-common(x86-64) >= 5.7.9 is needed by mysql-community-libs-5.7.30-1.el7.x86_64
56     mariadb-libs is obsoleted by mysql-community-libs-5.7.30-1.el7.x86_64
57 [root@k8s-node3 package]# rpm -ivh mysql-community-server-5.7.30-1.el7.x86_64.rpm
58 warning: mysql-community-server-5.7.30-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
59 error: Failed dependencies:
60     mysql-community-client(x86-64) >= 5.7.9 is needed by mysql-community-server-5.7.30-1.el7.x86_64
61     mysql-community-common(x86-64) = 5.7.30-1.el7 is needed by mysql-community-server-5.7.30-1.el7.x86_64
62 [root@k8s-node3 package]# rpm -ivh mysql-community-client-5.7.30-1.el7.x86_64.rpm
63 warning: mysql-community-client-5.7.30-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
64 error: Failed dependencies:
65     mysql-community-libs(x86-64) >= 5.7.9 is needed by mysql-community-client-5.7.30-1.el7.x86_64
66 [root@k8s-node3 package]# 

在安装 mysql-community-libs-5.7.30-1.el7.x86_64.rpm 时有可能会报错,这个是mysql依赖错误,解决方法是:清除yum里所有mysql依赖包。

 1 [root@k8s-node3 package]# rpm -qa|grep mysql
 2 [root@k8s-node3 package]# yum remove mysql-libs
 3 Loaded plugins: fastestmirror, langpacks, product-id, search-disabled-repos, subscription-manager
 4 
 5 This system is not registered with an entitlement server. You can use subscription-manager to register.
 6 
 7 Resolving Dependencies
 8 --> Running transaction check
 9 ---> Package mariadb-libs.x86_64 1:5.5.60-1.el7_5 will be erased
10 --> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
11 --> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
12 --> Running transaction check
13 ---> Package postfix.x86_64 2:2.10.1-7.el7 will be erased
14 --> Processing Dependency: /usr/sbin/sendmail for package: redhat-lsb-core-4.1-27.el7.centos.1.x86_64
15 --> Restarting Dependency Resolution with new changes.
16 --> Running transaction check
17 ---> Package redhat-lsb-core.x86_64 0:4.1-27.el7.centos.1 will be erased
18 --> Finished Dependency Resolution
19 base/7/x86_64                                                                                            | 3.6 kB  00:00:00     
20 centos-gluster7/7/x86_64                                                                                 | 3.0 kB  00:00:00     
21 extras/7/x86_64                                                                                          | 2.9 kB  00:00:00     
22 extras/7/x86_64/primary_db                                                                               | 205 kB  00:00:00     
23 updates/7/x86_64                                                                                         | 2.9 kB  00:00:00     
24 updates/7/x86_64/primary_db                                                                              | 3.0 MB  00:00:00     
25 
26 Dependencies Resolved
27 
28 ================================================================================================================================
29  Package                          Arch                    Version                              Repository                  Size
30 ================================================================================================================================
31 Removing:
32  mariadb-libs                     x86_64                  1:5.5.60-1.el7_5                     @anaconda                  4.4 M
33 Removing for dependencies:
34  postfix                          x86_64                  2:2.10.1-7.el7                       @anaconda                   12 M
35  redhat-lsb-core                  x86_64                  4.1-27.el7.centos.1                  @base                       45 k
36 
37 Transaction Summary
38 ================================================================================================================================
39 Remove  1 Package (+2 Dependent packages)
40 
41 Installed size: 17 M
42 Is this ok [y/N]: y
43 Downloading packages:
44 Running transaction check
45 Running transaction test
46 Transaction test succeeded
47 Running transaction
48   Erasing    : redhat-lsb-core-4.1-27.el7.centos.1.x86_64                                                                   1/3 
49   Erasing    : 2:postfix-2.10.1-7.el7.x86_64                                                                                2/3 
50   Erasing    : 1:mariadb-libs-5.5.60-1.el7_5.x86_64                                                                         3/3 
51   Verifying  : redhat-lsb-core-4.1-27.el7.centos.1.x86_64                                                                   1/3 
52   Verifying  : 2:postfix-2.10.1-7.el7.x86_64                                                                                2/3 
53   Verifying  : 1:mariadb-libs-5.5.60-1.el7_5.x86_64                                                                         3/3 
54 
55 Removed:
56   mariadb-libs.x86_64 1:5.5.60-1.el7_5                                                                                          
57 
58 Dependency Removed:
59   postfix.x86_64 2:2.10.1-7.el7                           redhat-lsb-core.x86_64 0:4.1-27.el7.centos.1                          
60 
61 Complete!
62 [root@k8s-node3 package]# 

注意:有的系统可能不太一样,没有mysql-libs,而是mariadb-libs,此时要移除的则是mariadb-libs。

1 [root@k8s-node3 package]# rpm -qa|grep mariadb
2 [root@k8s-node3 package]# yum remove mariadb-libs

清除完yum里所有mysql的依赖包后,再次安装mysql组件就不会报错了,如下所示:

 1 [root@k8s-node3 package]# rpm -ivh mysql-community-common-5.7.30-1.el7.x86_64.rpm
 2 warning: mysql-community-common-5.7.30-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
 3 Preparing...                          ################################# [100%]
 4 Updating / installing...
 5    1:mysql-community-common-5.7.30-1.e################################# [100%]
 6 [root@k8s-node3 package]# rpm -ivh mysql-community-libs-5.7.30-1.el7.x86_64.rpm
 7 warning: mysql-community-libs-5.7.30-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
 8 Preparing...                          ################################# [100%]
 9 Updating / installing...
10    1:mysql-community-libs-5.7.30-1.el7################################# [100%]
11 [root@k8s-node3 package]# rpm -ivh mysql-community-server-5.7.30-1.el7.x86_64.rpm
12 warning: mysql-community-server-5.7.30-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
13 error: Failed dependencies:
14     mysql-community-client(x86-64) >= 5.7.9 is needed by mysql-community-server-5.7.30-1.el7.x86_64
15 [root@k8s-node3 package]# rpm -ivh mysql-community-client-5.7.30-1.el7.x86_64.rpm
16 warning: mysql-community-client-5.7.30-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
17 Preparing...                          ################################# [100%]
18 Updating / installing...
19    1:mysql-community-client-5.7.30-1.e################################# [100%]
20 [root@k8s-node3 package]# 

我这里server安装失败了,我这里强制安装,先安装一下,如下所示:

 1 [root@k8s-node3 package]# ls
 2 mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar        mysql-community-libs-5.7.30-1.el7.x86_64.rpm
 3 mysql-community-client-5.7.30-1.el7.x86_64.rpm  mysql-community-server-5.7.30-1.el7.x86_64.rpm
 4 mysql-community-common-5.7.30-1.el7.x86_64.rpm
 5 [root@k8s-node3 package]# rpm -ivh mysql-community-server-5.7.30-1.el7.x86_64.rpm --nodeps --force
 6 warning: mysql-community-server-5.7.30-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
 7 Preparing...                          ################################# [100%]
 8 Updating / installing...
 9    1:mysql-community-server-5.7.30-1.e################################# [100%]
10 [root@k8s-node3 package]# 

注意:执行 yum remove mysql-libs命令后,会自动删除掉 /etc/ 下的 my.cnf 文件,对于安装mysql组件,只有安装了 mysql-community-server-5.7.30-1.el7.x86_64.rpm 组件,才会在 /etc/下生成 my.cnf 文件 和 my.cnf.d 文件夹。

1 [root@k8s-node3 package]# ls /etc/my.cnf
2 /etc/my.cnf
3 [root@k8s-node3 package]# ls /etc/my.cnf.d/
4 [root@k8s-node3 package]# ls /etc/my*
5 /etc/my.cnf
6 
7 /etc/my.cnf.d:
8 [root@k8s-node3 package]# 

在/var/lib/下可以看到以下三个文件夹mysql、mysql-files、mysql-keyring,如下所示:

 1 [root@k8s-node3 package]# cd /var/lib/
 2 [root@k8s-node3 lib]# ls
 3 AccountsService  color       flatpak   glusterd   lldpad       mysql-keyring   polkit-1   rpm-state       tpm      yum
 4 alsa             colord      fprint    gssproxy   logrotate    net-snmp        postfix    rsyslog         tuned
 5 alternatives     containers  fwupd     hyperv     machines     NetworkManager  pulse      samba           udisks2
 6 authconfig       dbus        fwupdate  initramfs  misc         nfs             rasdaemon  selinux         unbound
 7 bluetooth        dhclient    games     iscsi      mlocate      os-prober       rhsm       setroubleshoot  upower
 8 boltd            dnsmasq     gdm       kubelet    mysql        PackageKit      rpcbind    stateless       vmware
 9 chrony           docker      geoclue   libvirt    mysql-files  plymouth        rpm        systemd         xkb
10 [root@k8s-node3 lib]# ls mysql mysql-
11 mysql-files/   mysql-keyring/ 
12 [root@k8s-node3 lib]# ls mysql mysql-
13 mysql-files/   mysql-keyring/ 
14 [root@k8s-node3 lib]# ls mysql mysql-files/ mysql-keyring/
15 mysql:
16 
17 mysql-files/:
18 
19 mysql-keyring/:
20 [root@k8s-node3 lib]# 

在/var/log/ 下可以看到 mysqld.log 文件,如下所示:

 1 [root@k8s-node3 lib]# cd /var/lo
 2 local/ lock/  log/   
 3 [root@k8s-node3 lib]# cd /var/log/
 4 [root@k8s-node3 log]# ls
 5 anaconda           cron                libvirt            qemu-ga            spooler-20200714      vmware-vgauthsvc.log.0
 6 audit              cron-20200618       maillog            rhsm               swtpm                 vmware-vmsvc.log
 7 boot.log           cron-20200625       maillog-20200618   sa                 tallylog              vmware-vmusr.log
 8 boot.log-20200618  cron-20200628       maillog-20200625   samba              tuned                 wpa_supplicant.log
 9 boot.log-20200622  cron-20200714       maillog-20200628   secure             vmware-network.1.log  wtmp
10 boot.log-20200629  cups                maillog-20200714   secure-20200618    vmware-network.2.log  Xorg.0.log
11 boot.log-20200630  dmesg               messages           secure-20200625    vmware-network.3.log  Xorg.0.log.old
12 boot.log-20200702  dmesg.old           messages-20200618  secure-20200628    vmware-network.4.log  Xorg.9.log
13 boot.log-20200703  firewalld           messages-20200625  secure-20200714    vmware-network.5.log  yum.log
14 boot.log-20200714  gdm                 messages-20200628  speech-dispatcher  vmware-network.6.log
15 btmp               gitlab              messages-20200714  spooler            vmware-network.7.log
16 btmp-20200702      glusterfs           mysqld.log         spooler-20200618   vmware-network.8.log
17 chrony             grubby_prune_debug  pluto              spooler-20200625   vmware-network.9.log
18 containers         lastlog             ppp                spooler-20200628   vmware-network.log
19 [root@k8s-node3 log]# ll mysqld.log 
20 -rw-r----- 1 mysql mysql 0 Jul 15 14:17 mysqld.log
21 [root@k8s-node3 log]# 

在/var/run/ 下可以看到 mysqld 目录,如下所示:

 1 [root@k8s-node3 log]# cd /var/run/
 2 [root@k8s-node3 run]# ls
 3 abrt                     cups             gitlab           lock           NetworkManager  setrans         udev
 4 alsactl.pid              dbus             gluster          log            plymouth        setroubleshoot  udisks2
 5 atd.pid                  dmeventd-client  glusterd.pid     lsm            ppp             sm-notify.pid   user
 6 auditd.pid               dmeventd-server  glusterd.socket  lvm            radvd           spice-vdagentd  utmp
 7 avahi-daemon             docker           gssproxy.pid     lvmetad.pid    rhsm            sshd.pid        vmware
 8 blkid                    docker.pid       gssproxy.sock    mcelog-client  rpcbind         sudo            xtables.lock
 9 console                  docker.sock      initramfs        mcelog.pid     rpcbind.lock    sysconfig
10 container-storage-setup  ebtables.lock    ksmtune.pid      mdadm          rpcbind.sock    syslogd.pid
11 crond.pid                faillock         kubernetes       mount          runc            systemd
12 cron.reboot              flannel          libvirt          mysqld         samba           tmpfiles.d
13 cryptsetup               gdm              libvirtd.pid     netreport      sepermit        tuned
14 [root@k8s-node3 run]# ls mysqld/
15 [root@k8s-node3 run]# ll mysqld/
16 total 0
17 [root@k8s-node3 run]# 

3、登录并创建 mysql 密码,启动mysql,安装完后,使用命令 service mysqld start 或 systemctl start mysqld.service 启动MySQL服务,如果mysql服务无法启动,就重启一下系统。

 1 [root@k8s-node3 ~]# systemctl start mysqld.service
 2 [root@k8s-node3 ~]# systemctl status mysqld.service 
 3 ● mysqld.service - MySQL Server
 4    Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
 5    Active: active (running) since Wed 2020-07-15 14:23:18 CST; 11s ago
 6      Docs: man:mysqld(8)
 7            http://dev.mysql.com/doc/refman/en/using-systemd.html
 8   Process: 129166 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
 9   Process: 129095 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
10  Main PID: 129169 (mysqld)
11     Tasks: 27
12    Memory: 259.3M
13    CGroup: /system.slice/mysqld.service
14            └─129169 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
15 
16 Jul 15 14:23:12 k8s-node3 systemd[1]: Starting MySQL Server...
17 Jul 15 14:23:18 k8s-node3 systemd[1]: Started MySQL Server.
18 [root@k8s-node3 ~]# 

启动、停止、查看状态命令如下所示:

1 systemctl start mysqld.service     # 此命令是启动mysql
2 systemctl status mysqld.service     # 此命令是查看mysql状态
3 systemctl stop mysqld.service       # 此命令是关闭mysql

查看mysql进程 ps -ef | grep mysql,查看3306端口 netstat -anop | grep 3306。

1 [root@k8s-node3 ~]# ps -ef | grep mysql
2 mysql    129169      1  0 14:23 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
3 root     129514 121741  0 14:25 pts/2    00:00:00 grep --color=auto mysql
4 [root@k8s-node3 ~]# netstat -nultp | grep 3306
5 tcp6       0      0 :::3306                 :::*                    LISTEN      129169/mysqld       
6 [root@k8s-node3 ~]# 

登陆mysql修改root密码,由于MySQL5.7.4之前的版本中默认是没有密码的,登录后直接回车就可以进入数据库,进而进行设置密码等操作。其后版本对密码等安全相关操作进行了一些改变,在安装过程中,会在安装日志中生成一个临时密码。使用命令grep 'temporary password' /var/log/mysqld.log找到这个临时密码。

1 [root@k8s-node3 ~]# grep 'temporary password' /var/log/mysqld.log
2 2020-07-15T06:23:15.406927Z 1 [Note] A temporary password is generated for root@localhost: OUvw#frSq7iI
3 [root@k8s-node3 ~]# 

即可查询到类似于如下的一条日志记录,如下所示:

1 [root@k8s-node3 ~]# grep 'temporary password' /var/log/mysqld.log
2 2020-07-15T06:23:15.406927Z 1 [Note] A temporary password is generated for root@localhost: OUvw#frSq7iI
3 [root@k8s-node3 ~]# 

OUvw#frSq7iI即为登录密码。使用这个随机密码登录进去,然后修改密码,如下所示:

 1 [root@k8s-node3 ~]# grep 'temporary password' /var/log/mysqld.log
 2 2020-07-15T06:23:15.406927Z 1 [Note] A temporary password is generated for root@localhost: OUvw#frSq7iI
 3 [root@k8s-node3 ~]# mysql -uroot -p
 4 Enter password: 
 5 Welcome to the MySQL monitor.  Commands end with ; or \g.
 6 Your MySQL connection id is 2
 7 Server version: 5.7.30
 8 
 9 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
10 
11 Oracle is a registered trademark of Oracle Corporation and/or its
12 affiliates. Other names may be trademarks of their respective
13 owners.
14 
15 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
16 
17 mysql> set password for root@localhost=password('123456');
18 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
19 mysql> set global validate_password_policy=0;    # 此时,新密码长度大于等于8位才有效,否则报错
20 Query OK, 0 rows affected (0.00 sec)
21 
22 mysql> set global validate_password_length=1;    # 修改有效密码长度。 
23 Query OK, 0 rows affected (0.00 sec)
24 
25 mysql>  set password for root@localhost=password('123456');
26 Query OK, 0 rows affected, 1 warning (0.01 sec)
27 
28 mysql> 

执行下面的命令set password for root@localhost=password('123456');修改MySql root密码,但是在5.6后,mysql内置密码增强机制,低强度密码会报错:ERROR 1819 (HY000): Your password does not satisfy the current policy requirements。

 1 第一步是,更改策略,设置 validate_password_policy=0;
 2 mysql> set global validate_password_policy=0;    # 此时,新密码长度大于等于8位才有效,否则报错
 3 
 4 然后修改有效密码长度:
 5 mysql> set global validate_password_length=1;
 6 不管设置 validate_password_length=1,还是2,3,4 ,"使密码长度生效"这个参数的实际值都是4。超过4后设置是多少实际就是多少。
 7 
 8 第二步就可以重新设置密码了,如下所示:
 9 mysql> set password for root@localhost=password('123456');
10 Query OK, 0 rows affected, 1 warning (0.01 sec)

使用修改之后的密码就可以顺利登录,如下所示:

 1 [root@k8s-node3 ~]# mysql -uroot -p123456
 2 mysql: [Warning] Using a password on the command line interface can be insecure.
 3 Welcome to the MySQL monitor.  Commands end with ; or \g.
 4 Your MySQL connection id is 3
 5 Server version: 5.7.30 MySQL Community Server (GPL)
 6 
 7 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14 
15 mysql> 

此时,虽然防火墙我时关着的,但root用户只能用于本机访问,不能用于远程访问,否则会报以下错误。因此,接下来要做的是授予root用户远程访问权限。

查看当前授予过的权限,如下所示:

 1 [root@k8s-node3 ~]# mysql -uroot -p123456
 2 mysql: [Warning] Using a password on the command line interface can be insecure.
 3 Welcome to the MySQL monitor.  Commands end with ; or \g.
 4 Your MySQL connection id is 3
 5 Server version: 5.7.30 MySQL Community Server (GPL)
 6 
 7 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14 
15 mysql> use mysql;
16 Reading table information for completion of table and column names
17 You can turn off this feature to get a quicker startup with -A
18 
19 Database changed
20 mysql> select user,host from user;
21 +---------------+-----------+
22 | user          | host      |
23 +---------------+-----------+
24 | mysql.session | localhost |
25 | mysql.sys     | localhost |
26 | root          | localhost |
27 +---------------+-----------+
28 3 rows in set (0.00 sec)
29 
30 mysql> grant all privileges on *.* to root@'%' identified by '123456';   # 授予root用户远程访问权限。
31 Query OK, 0 rows affected, 1 warning (0.00 sec)
32 
33 mysql> flush privileges;  # 刷新权限,使设置生效。
34 Query OK, 0 rows affected (0.00 sec)
35 
36 mysql> 

现在可以远程链接服务器上面的mysql数据库,如下所示:

4、验证mysql安装成功,如下所示:

1 [root@k8s-node3 ~]# mysqladmin --version
2 mysqladmin  Ver 8.42 Distrib 5.7.30, for Linux on x86_64
3 [root@k8s-node3 ~]# 

查看mysql检查是否开机自启动,服务前面存在*的是开机自启动的,没有*的是开机不自启动的,如下所示:

 1 [root@k8s-node3 ~]# ntsysv 
 2 
 3 ntsysv 1.7.4 - (C) 2000-2001 Red Hat, Inc.                                                                                      
 4                                                                                                                                 
 5                                                                                                                                 
 6                                                                                         ─┐                                      
 7                                                                                          │                                      
 8                                         What services should be automatically started?   │                                      
 9                                                                                          │                                      
10                                         [*] mdmonitor.service                          ↑ │                                      
11                                         [*] microcode.service                          ? │                                      
12                                         [*] multipathd.service                         ? │                                      
13                                         [*] mysqld.service                             ? │                                      
14                                         [ ] ndctl-monitor.service                      ? │                                      
15                                         [ ] netcf-transaction.service                  ? │                                      
16                                         [ ] nfs-blkmap.service                         ? │                                      
17                                         [ ] nfs-rquotad.service                        ? │                                      
18                                         [ ] nfs-server.service                         ? │                                      
19                                         [ ] nfs.service                                ↓ │                                      
20                                                                                          │                                      
21                                                 ┌────┐                ┌────────┐         │                                      
22                                                 │ Ok │                │ Cancel │         │                                      
23                                                 └────┘                └────────┘         │                                      
24                                                                                          │                                      
25                                       │                                                  │                                      
26                                       └──────────────────────────────────────────────────┘                                      
27                                                                                                                                 
28                                                                                                                                 
29 Press <F1> for more information on a service.                  

mysql的核心目录,如下所示:

 1 1)、/var/lib/mysql,mysql的安装目录。
 2 2)、/usr/share/mysql/,mysql的配置文件。
 3 3)、/usr/bin/,mysql的命令目录。
 4 
 5 
 6 [root@k8s-node3 ~]# ls /var/lib/mysql
 7 auto.cnf    client-cert.pem  ibdata1      ibtmp1      mysql.sock.lock     public_key.pem   sys
 8 ca-key.pem  client-key.pem   ib_logfile0  mysql       performance_schema  server-cert.pem
 9 ca.pem      ib_buffer_pool   ib_logfile1  mysql.sock  private_key.pem     server-key.pem
10 [root@k8s-node3 ~]# ls /usr/share/mysql/
11 bulgarian             french                       mysql-log-rotate              romanian
12 charsets              german                       mysql_security_commands.sql   russian
13 czech                 greek                        mysql_sys_schema.sql          serbian
14 danish                hungarian                    mysql_system_tables_data.sql  slovak
15 dictionary.txt        innodb_memcached_config.sql  mysql_system_tables.sql       spanish
16 dutch                 install_rewriter.sql         mysql_test_data_timezone.sql  swedish
17 english               italian                      norwegian                     ukrainian
18 errmsg-utf8.txt       japanese                     norwegian-ny                  uninstall_rewriter.sql
19 estonian              korean                       polish
20 fill_help_tables.sql  magic                        portuguese
21 [root@k8s-node3 ~]# ls /usr/bin/

5、查看Mysql的字符集编码,如下所示:

 1 [root@k8s-node3 ~]# mysql -uroot -p123456
 2 mysql: [Warning] Using a password on the command line interface can be insecure.
 3 Welcome to the MySQL monitor.  Commands end with ; or \g.
 4 Your MySQL connection id is 4
 5 Server version: 5.7.30 MySQL Community Server (GPL)
 6 
 7 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14 
15 mysql> show variables like '%char%';
16 +--------------------------------------+----------------------------+
17 | Variable_name                        | Value                      |
18 +--------------------------------------+----------------------------+
19 | character_set_client                 | utf8                       |
20 | character_set_connection             | utf8                       |
21 | character_set_database               | latin1                     |
22 | character_set_filesystem             | binary                     |
23 | character_set_results                | utf8                       |
24 | character_set_server                 | latin1                     |
25 | character_set_system                 | utf8                       |
26 | character_sets_dir                   | /usr/share/mysql/charsets/ |
27 | validate_password_special_char_count | 1                          |
28 +--------------------------------------+----------------------------+
29 9 rows in set (0.00 sec)
30 
31 mysql> 

设置字符集编码为utf8格式的,如下所示:

 1 # For advice on how to change settings please see
 2 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
 3 
 4 [mysqld]
 5 init_connect='SET NAMES utf8'
 6 character-set-server=utf8
 7 # character_set_client=utf8
 8 collation-server=utf8_general_ci
 9 #
10 # Remove leading # and set to the amount of RAM for the most important data
11 # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
12 # innodb_buffer_pool_size = 128M
13 #
14 # Remove leading # to turn on a very important data integrity option: logging
15 # changes to the binary log between backups.
16 # log_bin
17 #
18 # Remove leading # to set options mainly useful for reporting servers.
19 # The server defaults are faster for transactions and fast SELECTs.
20 # Adjust sizes as needed, experiment to find the optimal values.
21 # join_buffer_size = 128M
22 # sort_buffer_size = 2M
23 # read_rnd_buffer_size = 2M
24 datadir=/var/lib/mysql
25 socket=/var/lib/mysql/mysql.sock
26 
27 # Disabling symbolic-links is recommended to prevent assorted security risks
28 symbolic-links=0
29 
30 log-error=/var/log/mysqld.log
31 pid-file=/var/run/mysqld/mysqld.pid
32 
33 [mysql]
34 default-character-set=utf8
35 
36 [mysql_safe]
37 default-character-set=utf8
38 "my.cnf" 41L, 1192C                       

修改内容,如下所示:

此时,需要重启mysql的,如下所示:

 1 [root@k8s-node3 etc]# systemctl restart mysqld.service 
 2 [root@k8s-node3 etc]# systemctl status mysqld.service
 3 ● mysqld.service - MySQL Server
 4    Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
 5    Active: active (running) since Wed 2020-07-15 18:49:25 CST; 2s ago
 6      Docs: man:mysqld(8)
 7            http://dev.mysql.com/doc/refman/en/using-systemd.html
 8   Process: 26346 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
 9   Process: 26322 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
10  Main PID: 26349 (mysqld)
11     Tasks: 27
12    Memory: 218.8M
13    CGroup: /system.slice/mysqld.service
14            └─26349 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
15 
16 Jul 15 18:49:23 k8s-node3 systemd[1]: Starting MySQL Server...
17 Jul 15 18:49:25 k8s-node3 systemd[1]: Started MySQL Server.

再次查看字符集编码,如下所示:

 1 mysql> SHOW VARIABLES LIKE 'character_set_%';
 2 +--------------------------+----------------------------+
 3 | Variable_name            | Value                      |
 4 +--------------------------+----------------------------+
 5 | character_set_client     | utf8                       |
 6 | character_set_connection | utf8                       |
 7 | character_set_database   | utf8                       |
 8 | character_set_filesystem | binary                     |
 9 | character_set_results    | utf8                       |
10 | character_set_server     | utf8                       |
11 | character_set_system     | utf8                       |
12 | character_sets_dir       | /usr/share/mysql/charsets/ |
13 +--------------------------+----------------------------+
14 8 rows in set (0.01 sec)
15 
16 mysql> SHOW VARIABLES LIKE 'collation_%';
17 +----------------------+-----------------+
18 | Variable_name        | Value           |
19 +----------------------+-----------------+
20 | collation_connection | utf8_general_ci |
21 | collation_database   | utf8_general_ci |
22 | collation_server     | utf8_general_ci |
23 +----------------------+-----------------+
24 3 rows in set (0.01 sec)
25 
26 mysql> 

注意,修改字符集编码,只对后面创建的数据库生效,之前创建的数据库字符集还是保持之前的不发生变化。

6、Mysql的命令行清屏命令system clear或者ctrl + L命令,如下所示:

1 mysql> system clear
2 mysql> 

mysql的逻辑分层,分别是连接层、服务层、引擎层、存储层,如下所示:

查看mysql的存储引擎,首先查看mysql支持的引擎,如下所示:

 1 [root@k8s-node3 ~]# mysql -uroot -p123456
 2 mysql: [Warning] Using a password on the command line interface can be insecure.
 3 Welcome to the MySQL monitor.  Commands end with ; or \g.
 4 Your MySQL connection id is 5
 5 Server version: 5.7.30 MySQL Community Server (GPL)
 6 
 7 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14 
15 mysql> show engines;
16 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
17 | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
18 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
19 | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
20 | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
21 | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
22 | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
23 | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
24 | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
25 | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
26 | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
27 | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
28 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
29 9 rows in set (0.01 sec)
30 
31 mysql> 

查看当前Mysql的存储引擎,如下所示:

 1 mysql> show variables like '%storage_engine%';
 2 +----------------------------------+--------+
 3 | Variable_name                    | Value  |
 4 +----------------------------------+--------+
 5 | default_storage_engine           | InnoDB |
 6 | default_tmp_storage_engine       | InnoDB |
 7 | disabled_storage_engines         |        |
 8 | internal_tmp_disk_storage_engine | InnoDB |
 9 +----------------------------------+--------+
10 4 rows in set (0.00 sec)
11 
12 mysql> 

查看某个数据表使用了什么存储引擎,在显示结果里参数engine后面的就表示该表当前用的存储引擎。

 1 mysql> show create table user_info;
 2 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 3 | Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
 4 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 5 | user_info | CREATE TABLE `user_info` (
 6   `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
 7   `user_account` varchar(15) DEFAULT NULL COMMENT '用户账号',
 8   `user_pw` varchar(15) DEFAULT NULL COMMENT '用户密码',
 9   `user_number` varchar(15) DEFAULT NULL COMMENT '用户手机号',
10   `user_name` varchar(10) DEFAULT NULL COMMENT '用户姓名',
11   `user_age` int(11) DEFAULT NULL COMMENT '用户年龄',
12   `user_sex` varchar(5) DEFAULT NULL COMMENT '用户性别',
13   `user_mark` varchar(5) DEFAULT NULL COMMENT '用户标志',
14   `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '用户创建时间',
15   `is_sync` int(3) DEFAULT '0' COMMENT '用户标识',
16   `is_money` varchar(255) DEFAULT NULL COMMENT '是否缴纳押金',
17   PRIMARY KEY (`user_id`) USING BTREE,
18   KEY `user_id` (`user_id`) USING BTREE
19 ) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户信息表'                                                       |
20 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
21 1 row in set (0.00 sec)
22 
23 mysql> 

7、mysql的sql优化,原因可能是性能低,执行时间太长,等待时间太长,sql语句欠佳,比如连接查询,索引失效,服务器参数设置不佳。

Sql的编写过程、解析过程,如下所示:

1 编写过程:select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
2 解析过程:from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

  SQL优化,主要就是在于优化索引,索引相当于是书的目录。索引,index是帮助Mysql高效获取数据的数据结构,索引是数据结构(树,常见的B树,默认是B树,Hash树)。

  索引的弊端,索引本身很大,可以存放在内存或者硬盘中,通常为硬盘。索引不是所有情况均适用的,如果是少量数据不适用。如果是频繁更新的字段不适用。很少使用的列也不适合添加索引。索引会降低增删改的效率,提高查询的效率。

  索引的优点,提高查询效率,降低IO使用率,降低CPU使用率,因为B树索引本事就是一个排好序的结构,因此在排序的时候,可以直接使用的。

8、mysql如何进行优化呢,查看使用索引的情况,查询执行计划,explain + SQL语句。

1 mysql> explain select * from book_info;
2 +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
3 | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
4 +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
5 |  1 | SIMPLE      | book_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   16 |   100.00 | NULL  |
6 +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
7 1 row in set, 1 warning (0.01 sec)

详细信息介绍,如下所示:

 1 id :编号
 2 select_type :查询类型 
 3 table :数据表,指的查询的那张表。
 4 partitions :
 5 type :类型
 6 possible_keys : 预测使用到的索引
 7 key  :实际使用的索引 
 8 key_len : 实际使用索引的长度
 9 ref  : 表之间的引用
10 rows : 通过索引查询到的数据量
11 filtered : 
12 Extra :额外的信息

8.1、id编号的用法,多表关联查询(备注:多表联查都可以转换为子查询得到想要的结果)特点:

  1)、id编号值相同,数据小的表,优先查询。

  2)、如果是id编号值不同,id编号值越大越优先查询(执行本质,在嵌套子查询的时候,先查询内层,再查询外层)。

  3)、id值有相同,又有不同,id值越大越优先。id值相同从上往下,顺序执行。这里的id编号指定的是explain的查询结果字段id哦。

 1 mysql> explain
 2     -> select * from book_info bi,book_sort bs WHERE bi.book_sort_id = bs.book_sort_id and bi.book_name = 'Java';
 3 +----+-------------+-------+------------+--------+------------------------+-----------+---------+----------------------+------+----------+-------------+
 4 | id | select_type | table | partitions | type   | possible_keys          | key       | key_len | ref                  | rows | filtered | Extra       |
 5 +----+-------------+-------+------------+--------+------------------------+-----------+---------+----------------------+------+----------+-------------+
 6 |  1 | SIMPLE      | bi    | NULL       | ref    | book_sort_id,book_name | book_name | 48      | const                |    1 |   100.00 | Using where |
 7 |  1 | SIMPLE      | bs    | NULL       | eq_ref | PRIMARY                | PRIMARY   | 4       | book.bi.book_sort_id |    1 |   100.00 | NULL        |
 8 +----+-------------+-------+------------+--------+------------------------+-----------+---------+----------------------+------+----------+-------------+
 9 2 rows in set, 1 warning (0.00 sec)
10 
11 mysql> 
 1 mysql> explain
 2     -> select * from book_info bi WHERE bi.book_sort_id = (SELECT book_sort_id from book_sort bs WHERE bs.book_sort_name = '自然科学总论')
 3     -> ;
 4 +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
 5 | id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
 6 +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
 7 |  1 | PRIMARY     | bi    | NULL       | ref  | book_sort_id  | book_sort_id | 5       | const |    2 |   100.00 | Using where |
 8 |  2 | SUBQUERY    | bs    | NULL       | ALL  | NULL          | NULL         | NULL    | NULL  |   21 |    10.00 | Using where |
 9 +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
10 2 rows in set, 1 warning (0.00 sec)
11 
12 mysql> 

8.2)、select_type查询类型的用法,如下所示:

  1)、PRIMARY:包含子查询SQL中的主查询,一般是SQL嵌套的最外层。   2)、SUBQUERY:包含子查询SQL中的子查询,一般是SQL嵌套的非最外层。   3)、SIMPLE:简单查询,不包含子查询,不包含union查询。   4)、DERIVED:衍生查询。在查询的时候使用到了临时表。两种情况,第一种是from子查询中只有一张表。第二种是在from子查询中,如果有两张表table1 union table2连接查询,则table1这个左表就是衍生查询。

 1 mysql> explain
 2     -> SELECT b.book_name from (SELECT * from book_info WHERE book_name in ('Java') UNION SELECT * from book_info WHERE book_name in ('python') ) b;
 3 +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
 4 | id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
 5 +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
 6 |  1 | PRIMARY      | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL            |
 7 |  2 | DERIVED      | book_info  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   16 |    10.00 | Using where     |
 8 |  3 | UNION        | book_info  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   16 |    10.00 | Using where     |
 9 | NULL | UNION RESULT | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
10 +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
11 4 rows in set, 1 warning (0.00 sec)
12 
13 mysql> 

  DERIVED代表了衍生表,table的值是derived2代表了是主查询涉及到衍生表,而2代表了指明了id编号是2,代表了衍生了那张表,临时表是从id为2的这个table是book_info来的。

8.3)、type成为索引类型,或者称为类型。system > const > eq_ref > ref > index > all,其中性能由左向右依次降低,但是system、const只是理想状态,实际可以达到的一般是ref、range。

  1)、system,只有一条数据的系统表,基本达不到,或者衍生表只有一条数据的主查询,偶尔可以达到。   2)、const,仅仅可以查询到一条数据的sql,并且用于Primary key或者unique索引的时候就是const。必须针对Primary key或者unique索引的时候,与索引类型有关。   3)、eq_ref,唯一性索引,对于每个索引键的查询,返回匹配唯一行数据,唯一的意思是有且只有一个,不能多也不能为0。常见于唯一索引和主键索引。   4)、ref,非唯一性索引,对于每个索引键的查询,返回匹配的所有行(可以是0个或者多个)。   5)、range,检索指定范围的行,具体就是where后面是一个范围查询,比如between and、in、>、<等等。特殊的in有时候会失效,从而转换为无索引All,即in范围查询会导致索引失效,可以将in范围查询放到字段查询的后面。   6)、index,查询全部索引的数据。只需要扫描索引表,不需要扫描全部数据。   7)、all,查询全部表中数据。需要扫描全表,即需要所有表中的所有数据。

1 mysql> explain select * from book_info where book_id > 5;
2 +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
3 | id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
4 +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
5 |  1 | SIMPLE      | book_info | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   13 |   100.00 | Using where |
6 +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
7 1 row in set, 1 warning (0.00 sec)

8.4)、possible_keys,可能用到的索引,是一种预测,不准。

8.5)、key,实际使用到的索引。如果possible_keys或者key为NULL,说明没有使用到索引。

1 mysql> explain select * from book_info where book_id > 5;
2 +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
3 | id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
4 +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
5 |  1 | SIMPLE      | book_info | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   13 |   100.00 | Using where |
6 +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
7 1 row in set, 1 warning (0.00 sec)

8.6)、key_len,索引的长度。作用就是用于判断复合索引是否被完全使用。

  1)、utf8中,一个字符占用三个字节。如果索引字段可以为null,则会使用1个字节用于标识。如果是固定长度char就是char(n)中的n乘以三,如果可以为null,再加一即可。

  2)、utf8中,一个字符占用三个字节。如果索引字段可以为null,则会使用1个字节用于标识。如果是可变长度varchar,就是varchar(n)中的n乘以三,如果可以为null,再加一,然后varhar是可变长度,然后再加二,用两个字节来表示可变长度。比如book_name是varchar(15),15 * 3 + 1 + 2 = 48。如果是复合索引,book_type是varchar(200),组合book_name是varchar(15),就是200 * 3 + 1 + 2 + 15 * 3 + 1 + 2 = 651。

 1 mysql> EXPLAIN
 2     -> SELECT * from book_info WHERE book_name = 'Java' and book_type = '自然科学总论';
 3 +----+-------------+-----------+------------+------+-----------------------------------------+-----------+---------+-------+------+----------+-------------+
 4 | id | select_type | table     | partitions | type | possible_keys                           | key       | key_len | ref   | rows | filtered | Extra       |
 5 +----+-------------+-----------+------------+------+-----------------------------------------+-----------+---------+-------+------+----------+-------------+
 6 |  1 | SIMPLE      | book_info | NULL       | ref  | book_type,book_name,book_name_book_type | book_name | 48      | const |    1 |    12.50 | Using where |
 7 +----+-------------+-----------+------------+------+-----------------------------------------+-----------+---------+-------+------+----------+-------------+
 8 1 row in set, 1 warning (0.00 sec)
 9 
10 mysql> EXPLAIN
11     -> SELECT * from book_info WHERE book_name = 'Java' and book_type = '自然科学总论';
12 +----+-------------+-----------+------------+------+-------------------------------+---------------------+---------+-------------+------+----------+-------+
13 | id | select_type | table     | partitions | type | possible_keys                 | key                 | key_len | ref         | rows | filtered | Extra |
14 +----+-------------+-----------+------------+------+-------------------------------+---------------------+---------+-------------+------+----------+-------+
15 |  1 | SIMPLE      | book_info | NULL       | ref  | book_type,book_name_book_type | book_name_book_type | 651     | const,const |    1 |   100.00 | NULL  |
16 +----+-------------+-----------+------------+------+-------------------------------+---------------------+---------+-------------+------+----------+-------+
17 1 row in set, 1 warning (0.00 sec)
18 
19 mysql> 

注意:utf8是一个字符三个节点,gbk是一个字符两个字节,latin是一个字符一个字节。

8.7)、ref,注意与type中的ref值进行区分,作用是指明当前表所参照的字段。如果是常量,值就是const。

 1 mysql> EXPLAIN
 2     -> SELECT bi.* from book_info bi,book_sort bs WHERE bi.book_sort_id = bs.book_sort_id and bi.book_name = 'Java'; 
 3 +----+-------------+-------+------------+--------+----------------------------------+---------------------+---------+----------------------+------+----------+-------------+
 4 | id | select_type | table | partitions | type   | possible_keys                    | key                 | key_len | ref                  | rows | filtered | Extra       |
 5 +----+-------------+-------+------------+--------+----------------------------------+---------------------+---------+----------------------+------+----------+-------------+
 6 |  1 | SIMPLE      | bi    | NULL       | ref    | book_sort_id,book_name_book_type | book_name_book_type | 48      | const                |    1 |   100.00 | Using where |
 7 |  1 | SIMPLE      | bs    | NULL       | eq_ref | PRIMARY                          | PRIMARY             | 4       | book.bi.book_sort_id |    1 |   100.00 | Using index |
 8 +----+-------------+-------+------------+--------+----------------------------------+---------------------+---------+----------------------+------+----------+-------------+
 9 2 rows in set, 1 warning (0.00 sec)
10 
11 mysql> 

8.8)、rows,被索引优化查询的数据个数。

1 mysql> EXPLAIN
2     -> SELECT bi.* from book_info bi,book_sort bs WHERE bi.book_sort_id = bs.book_sort_id and bi.book_name = 'Java'; 
3 +----+-------------+-------+------------+--------+----------------------------------+---------------------+---------+----------------------+------+----------+-------------+
4 | id | select_type | table | partitions | type   | possible_keys                    | key                 | key_len | ref                  | rows | filtered | Extra       |
5 +----+-------------+-------+------------+--------+----------------------------------+---------------------+---------+----------------------+------+----------+-------------+
6 |  1 | SIMPLE      | bi    | NULL       | ref    | book_sort_id,book_name_book_type | book_name_book_type | 48      | const                |    1 |   100.00 | Using where |
7 |  1 | SIMPLE      | bs    | NULL       | eq_ref | PRIMARY                          | PRIMARY             | 4       | book.bi.book_sort_id |    1 |   100.00 | Using index |
8 +----+-------------+-------+------------+--------+----------------------------------+---------------------+---------+----------------------+------+----------+-------------+
9 2 rows in set, 1 warning (0.00 sec)

8.9)、Extra,就是额外的,剩下的意思,常见的有using filesort(出现这种情况要避免)、Using temporary(出现这种情况要避免)、Using where、Using index。

  1)、using filesort:表示性能低,消耗比较大,当前的sql语句需要额外的一次排序查询。理解起来就是先根据这个字段查询,然后根据这个字段排序。如果是先根据一个字段查询,根据另外一个字段排序,那么当前的sql语句需要额外的一次排序查询。常见于order by语句中。

 1 mysql> SELECT * from book_info WHERE book_name = 'Java' ORDER BY book_name;
 2 +---------+--------------+-----------+-------------+------------+--------------------+-----------------+----------+-----------+---------------------+------------+-------------------------------------+
 3 | book_id | book_sort_id | book_name | book_author | book_price | book_type          | book_publish    | book_sum | book_mark | create_time         | book_money | book_desc                           |
 4 +---------+--------------+-----------+-------------+------------+--------------------+-----------------+----------+-----------+---------------------+------------+-------------------------------------+
 5 |       3 |           19 | Java      | 别先生      |      29.90 | 自然科学总论       | 科学出版社      |        6 | NULL      | 2019-02-15 19:09:45 |     199.00 | <p>Java大神如何练成的?</p>         |
 6 +---------+--------------+-----------+-------------+------------+--------------------+-----------------+----------+-----------+---------------------+------------+-------------------------------------+
 7 1 row in set (0.00 sec)
 8 
 9 mysql> EXPLAIN
10     -> SELECT * from book_info WHERE book_name = 'Java' ORDER BY book_author;
11 +----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+---------------------------------------+
12 | id | select_type | table     | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra                                 |
13 +----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+---------------------------------------+
14 |  1 | SIMPLE      | book_info | NULL       | ref  | book_name_book_type | book_name_book_type | 48      | const |    1 |   100.00 | Using index condition; Using filesort |
15 +----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+---------------------------------------+
16 1 row in set, 1 warning (0.00 sec)
17 
18 mysql> 

  总结:对于单索引,如果排序和查找是同一字段,则不会出现using filesort,如果排序和查找不是同一字段,则会出现using filesort。

  对于复合索引,不能跨列(最佳左前缀,即从左到右依次使用,不能跨列使用,比如三个字段的索引,不能跨过第二个索引)。where和order by按照复合索引的顺序使用,不要跨列或者无序使用。

 1 mysql> EXPLAIN
 2     -> SELECT * from book_info WHERE book_name = 'Java' ORDER BY book_author;
 3 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+---------------------------------------+
 4 | id | select_type | table     | partitions | type | possible_keys                   | key                             | key_len | ref   | rows | filtered | Extra                                 |
 5 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+---------------------------------------+
 6 |  1 | SIMPLE      | book_info | NULL       | ref  | book_name_book_type_book_author | book_name_book_type_book_author | 48      | const |    1 |   100.00 | Using index condition; Using filesort |
 7 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+---------------------------------------+
 8 1 row in set, 1 warning (0.00 sec)
 9 
10 mysql> EXPLAIN
11     -> SELECT * from book_info WHERE book_name = 'Java' ORDER BY book_type;
12 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-----------------------+
13 | id | select_type | table     | partitions | type | possible_keys                   | key                             | key_len | ref   | rows | filtered | Extra                 |
14 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-----------------------+
15 |  1 | SIMPLE      | book_info | NULL       | ref  | book_name_book_type_book_author | book_name_book_type_book_author | 48      | const |    1 |   100.00 | Using index condition |
16 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-----------------------+
17 1 row in set, 1 warning (0.00 sec)
18 
19 mysql> 

  2)、Using temporary:性能损耗大,用到了临时表。一般出现在group by的语句中。如何避免?查询那些列,就根据那些列进行group by进行分组。出现的原因就是已经有表了,但是不使用,必须再来一张临时表进行分组查询出结果。

 1 mysql> EXPLAIN
 2     -> SELECT book_name from book_info WHERE book_name = 'Java' GROUP BY book_name;
 3 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-------------+
 4 | id | select_type | table     | partitions | type | possible_keys                   | key                             | key_len | ref   | rows | filtered | Extra       |
 5 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-------------+
 6 |  1 | SIMPLE      | book_info | NULL       | ref  | book_name_book_type_book_author | book_name_book_type_book_author | 48      | const |    1 |   100.00 | Using index |
 7 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-------------+
 8 1 row in set, 1 warning (0.00 sec)
 9 
10 mysql> EXPLAIN
11     -> SELECT book_name from book_info WHERE book_name = 'Java' GROUP BY book_publish;
12 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+--------------------------------------------------------+
13 | id | select_type | table     | partitions | type | possible_keys                   | key                             | key_len | ref   | rows | filtered | Extra                                                  |
14 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+--------------------------------------------------------+
15 |  1 | SIMPLE      | book_info | NULL       | ref  | book_name_book_type_book_author | book_name_book_type_book_author | 48      | const |    1 |   100.00 | Using index condition; Using temporary; Using filesort |
16 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+--------------------------------------------------------+
17 1 row in set, 1 warning (0.00 sec)
18 
19 mysql> 

重点,解析过程:from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

  3)、Using index:表示性能提升了,意思是索引覆盖,原因在于不读取原文件,只从索引文件中获取数据,不需要回表查询。只要使用到的列全部都在索引中,就是索引覆盖Using index。只要使用到的列不都在索引中,就不会出现索引覆盖Using index。

 1 mysql> EXPLAIN
 2     -> SELECT book_name from book_info WHERE book_name = 'Java';
 3 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-------------+
 4 | id | select_type | table     | partitions | type | possible_keys                   | key                             | key_len | ref   | rows | filtered | Extra       |
 5 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-------------+
 6 |  1 | SIMPLE      | book_info | NULL       | ref  | book_name_book_type_book_author | book_name_book_type_book_author | 48      | const |    1 |   100.00 | Using index |
 7 +----+-------------+-----------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-------------+
 8 1 row in set, 1 warning (0.00 sec)
 9 
10 mysql> EXPLAIN
11     -> SELECT book_name from book_info WHERE book_name = 'Java' or book_type = '自然科学总论';
12 +----+-------------+-----------+------------+-------+-------------------------------------------+---------------------------------+---------+------+------+----------+--------------------------+
13 | id | select_type | table     | partitions | type  | possible_keys                             | key                             | key_len | ref  | rows | filtered | Extra                    |
14 +----+-------------+-----------+------------+-------+-------------------------------------------+---------------------------------+---------+------+------+----------+--------------------------+
15 |  1 | SIMPLE      | book_info | NULL       | index | book_type,book_name_book_type_book_author | book_name_book_type_book_author | 699     | NULL |   16 |    19.00 | Using where; Using index |
16 +----+-------------+-----------+------------+-------+-------------------------------------------+---------------------------------+---------+------+------+----------+--------------------------+
17 1 row in set, 1 warning (0.00 sec)
18 
19 mysql> EXPLAIN
20     -> SELECT book_name from book_info WHERE book_type = '自然科学总论' or book_name = 'Java'  ;
21 +----+-------------+-----------+------------+-------+-------------------------------------------+---------------------------------+---------+------+------+----------+--------------------------+
22 | id | select_type | table     | partitions | type  | possible_keys                             | key                             | key_len | ref  | rows | filtered | Extra                    |
23 +----+-------------+-----------+------------+-------+-------------------------------------------+---------------------------------+---------+------+------+----------+--------------------------+
24 |  1 | SIMPLE      | book_info | NULL       | index | book_type,book_name_book_type_book_author | book_name_book_type_book_author | 699     | NULL |   16 |    19.00 | Using where; Using index |
25 +----+-------------+-----------+------------+-------+-------------------------------------------+---------------------------------+---------+------+------+----------+--------------------------+
26 1 row in set, 1 warning (0.00 sec)
27 
28 mysql> 

  如果用到了索引覆盖,索引覆盖Using index会对possible_keys、key造成影响,如果没有where,则索引只出现在key中,如果有where,则索引出现在key和possible_keys中。

 1 mysql> EXPLAIN SELECT book_name from book_info;
 2 +----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
 3 | id | select_type | table     | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra       |
 4 +----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
 5 |  1 | SIMPLE      | book_info | NULL       | index | NULL          | book_name_book_author | 96      | NULL |   16 |   100.00 | Using index |
 6 +----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
 7 1 row in set, 1 warning (0.00 sec)
 8 
 9 
10 mysql> EXPLAIN SELECT book_name from book_info WHERE book_name = '自然科学总论';
11 +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
12 | id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra       |
13 +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
14 |  1 | SIMPLE      | book_info | NULL       | ref  | book_name_book_author | book_name_book_author | 48      | const |    1 |   100.00 | Using index |
15 +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
16 1 row in set, 1 warning (0.00 sec)
17 
18 mysql> 

  4)、Using where:表示需要从原表中查询。即需要回表查询的。

 1 mysql> EXPLAIN
 2     -> SELECT book_name,book_type from book_info WHERE book_type = 'Java' ;
 3 +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
 4 | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 5 +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
 6 |  1 | SIMPLE      | book_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   16 |    10.00 | Using where |
 7 +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
 8 1 row in set, 1 warning (0.01 sec)
 9 
10 mysql> 

  5)、impossibal where,where子句永远为false。

 1 mysql> EXPLAIN
 2     -> SELECT book_name,book_type from book_info WHERE book_type = 'Java' AND book_type = 'Python' ;
 3 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
 4 | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
 5 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
 6 |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
 7 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
 8 1 row in set, 1 warning (0.01 sec)
 9 
10 mysql> 

  条件字段的顺序最好和组合索引的顺序一致,如果不一致,SQL优化器可以会对sql进行优化,使用到索引,但是强烈推荐保持条件字段顺序和组合索引顺序一致。如果复合索引和使用的顺序一致(且不跨列使用),则复合索引全部使用,如果部分一致,则使用部分索引。

9、mysql的单表索引优化案例,如下所示:

 1 SET NAMES utf8mb4;
 2 SET FOREIGN_KEY_CHECKS = 0;
 3 
 4 -- ----------------------------
 5 -- Table structure for user_info
 6 -- ----------------------------
 7 DROP TABLE IF EXISTS `user_info`;
 8 CREATE TABLE `user_info`  (
 9   `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
10   `user_account` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户账号',
11   `user_pw` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户密码',
12   `user_number` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户手机号',
13   `user_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户姓名',
14   `user_age` int(11) DEFAULT NULL COMMENT '用户年龄',
15   `user_sex` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户性别',
16   `user_mark` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户标志',
17   `create_time` datetime(0) DEFAULT CURRENT_TIMESTAMP COMMENT '用户创建时间',
18   `is_sync` int(3) DEFAULT 0 COMMENT '用户标识',
19   `is_money` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '是否缴纳押金',
20   PRIMARY KEY (`user_id`) USING BTREE
21 ) ENGINE = InnoDB AUTO_INCREMENT = 42 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户信息表' ROW_FORMAT = Dynamic;
22 
23 SET FOREIGN_KEY_CHECKS = 1;

索引一旦进行升级优化,需要将之前废弃的索引删除掉,防止干扰。

首先进行需求查询,如下所示:

 1 mysql> EXPLAIN 
 2     -> SELECT user_account from user_info WHERE user_age in (22,33) and user_name = '小别同志' ORDER BY user_age
 3     -> ;
 4 +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
 5 | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
 6 +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
 7 |  1 | SIMPLE      | user_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   21 |     4.76 | Using where; Using filesort |
 8 +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
 9 1 row in set, 1 warning (0.00 sec)
10 
11 mysql> 

很明显是没有使用到索引的,那么如何才能进行优化呢。需要注意的是,需要根据sql实际解析的顺序,来调整索引的顺序。

1 编写过程:select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
2 解析过程:from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

如果索引顺序是这样的`user_age`, `user_name`, `user_account`,那么查询的时候是使用了索引的,符合sql的解析过程,首先是where后面的字段,然后才是select后面的字段。

1 INDEX `user_age_name_account`(`user_age`, `user_name`, `user_account`) USING BTREE
 1 mysql> EXPLAIN 
 2     -> SELECT user_account from user_info WHERE user_age in (22,23) and user_name = '小别同志' ORDER BY user_age;
 3 +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
 4 | id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                    |
 5 +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
 6 |  1 | SIMPLE      | user_info | NULL       | range | user_age_name_account | user_age_name_account | 38      | NULL |    2 |   100.00 | Using where; Using index |
 7 +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
 8 1 row in set, 1 warning (0.00 sec)
 9 
10 mysql> 

如果是查询出所有的字段,就没有进行回表查询的,如下所示:

1 mysql> explain SELECT * from user_info WHERE user_age in (22,23) and user_name = '小别同志' ORDER BY user_age;
2 +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
3 | id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
4 +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
5 |  1 | SIMPLE      | user_info | NULL       | range | user_age_name_account | user_age_name_account | 38      | NULL |    2 |   100.00 | Using index condition |
6 +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
7 1 row in set, 1 warning (0.00 sec)
8 
9 mysql> 

索引一旦进行升级优化,需要将之前废弃的索引删除掉,防止干扰。

如果索引顺序是这样的`user_account`, `user_age`, `user_name`,也使用到了索引,但是进行了Using filesort,查询行数rows由2变成了21,效率下降了。

1 INDEX `user_account_age_name`(`user_account`, `user_age`, `user_name`) USING BTREE
 1 mysql> EXPLAIN 
 2     -> SELECT user_account from user_info WHERE user_age in (22,23) and user_name = '小别同志' ORDER BY user_age;
 3 +----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+------------------------------------------+
 4 | id | select_type | table     | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra                                    |
 5 +----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+------------------------------------------+
 6 |  1 | SIMPLE      | user_info | NULL       | index | NULL          | user_account_age_name | 86      | NULL |   21 |     4.76 | Using where; Using index; Using filesort |
 7 +----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+------------------------------------------+
 8 1 row in set, 1 warning (0.00 sec)
 9 
10 mysql> 

索引级别type最好达到ref或者range级别的,可以再次进行优化,这里效果不明显,但是in的范围查询可能会导致索引失效,最好放到后面,如下所示:

 1 mysql> EXPLAIN 
 2     -> SELECT user_account from user_info WHERE  user_name = '小别同志' and user_age in (22,23)  ORDER BY user_age;
 3 +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
 4 | id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                    |
 5 +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
 6 |  1 | SIMPLE      | user_info | NULL       | range | user_age_name_account | user_age_name_account | 38      | NULL |    2 |   100.00 | Using where; Using index |
 7 +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
 8 1 row in set, 1 warning (0.00 sec)
 9 
10 mysql> 

  总结:mysql的索引不能跨列使用即跳过中间的索引字段(最佳左前缀),保持索引的定义和使用的顺序一致性。索引需要逐步进行优化,将含In的范围查询放到where条件的最后,防止失效,如果失效了就需要回原表进行查询了。

  此案例中同时出现了Using where需要回原表查询,Using index不需要回原表查询,是因为in范围查询可能会导致索引失效就需要回原表进行查询了,如果where后面的字段在索引中就不需要回原表,可以在索引表中查询到。

1 mysql> EXPLAIN  SELECT user_account from user_info WHERE  user_name = '小别同志' and user_age =23  ORDER BY user_age;
2 +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------------+
3 | id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra       |
4 +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------------+
5 |  1 | SIMPLE      | user_info | NULL       | ref  | user_name_age_account | user_name_age_account | 38      | const,const |    1 |   100.00 | Using index |
6 +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------------+
7 1 row in set, 1 warning (0.00 sec)
8 
9 mysql> 

10、mysql的双表索引优化案例,如下所示:

 1 SET NAMES utf8mb4;
 2 SET FOREIGN_KEY_CHECKS = 0;
 3 
 4 -- ----------------------------
 5 -- Table structure for book_info
 6 -- ----------------------------
 7 DROP TABLE IF EXISTS `book_info`;
 8 CREATE TABLE `book_info`  (
 9   `book_id` int(11) NOT NULL COMMENT '图书编号',
10   `book_sort_id` int(11) DEFAULT NULL COMMENT '图书类型编号',
11   `book_name` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书名称',
12   `book_author` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书作者',
13   `book_price` decimal(5, 2) DEFAULT NULL COMMENT '图书价格',
14   `book_type` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书类型',
15   `book_publish` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书出版社',
16   `book_sum` int(255) DEFAULT NULL COMMENT '图书数量',
17   `book_mark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书标识',
18   `create_time` datetime(0) DEFAULT CURRENT_TIMESTAMP COMMENT '图书创建时间',
19   `book_money` decimal(5, 2) DEFAULT NULL COMMENT '图书租金',
20   `book_desc` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '图书说明'
21 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '图书信息表' ROW_FORMAT = Dynamic;
22 
23 -- ----------------------------
24 -- Table structure for book_sort
25 -- ----------------------------
26 DROP TABLE IF EXISTS `book_sort`;
27 CREATE TABLE `book_sort`  (
28   `book_sort_id` int(11) NOT NULL COMMENT '图书类别编号',
29   `book_sort_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书类别名称',
30   `book_sort_extend` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书类别扩展信息',
31   `book_sort_mark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书类别标识',
32   `create_time` datetime(0) DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
33 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '图书类别表' ROW_FORMAT = Dynamic;
34 
35 SET FOREIGN_KEY_CHECKS = 1;

两张表组合查询,进行左外关联,如下所示:

Using join buffer (Block Nested Loop),extra中的一个选项,作用就是mysql的引擎使用了连接缓存,其实就是你的sql太差了,mysql给你添加了一个缓存。

 1 mysql> EXPLAIN
 2     -> SELECT * from book_info bi LEFT OUTER JOIN book_sort bs ON bi.book_sort_id = bs.book_sort_id WHERE bi.book_name = 'Java';
 3 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
 4 | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
 5 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
 6 |  1 | SIMPLE      | bi    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   16 |    10.00 | Using where                                        |
 7 |  1 | SIMPLE      | bs    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
 8 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
 9 2 rows in set, 1 warning (0.01 sec)
10 
11 mysql> 

  对于两张表的关联查询,索引向那张表加呢,这里有一个原则,叫做小表驱动大表,即where后面跟的是,小表放左边,大表放右表。索引建立到经常使用的字段上。一般情况下,对于左外连接,给左表加索引,右外连接给右表加索引。

此时可以将左表的查询字段添加上索引,增加查询速度。

 1 mysql> EXPLAIN
 2     -> SELECT * from book_info bi LEFT OUTER JOIN book_sort bs ON bi.book_sort_id = bs.book_sort_id WHERE bi.book_name = 'Java';
 3 +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------------------------------------------+
 4 | id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                                              |
 5 +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------------------------------------------+
 6 |  1 | SIMPLE      | bi    | NULL       | ref  | book_name     | book_name | 48      | const |    1 |   100.00 | NULL                                               |
 7 |  1 | SIMPLE      | bs    | NULL       | ALL  | NULL          | NULL      | NULL    | NULL  |   21 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
 8 +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------------------------------------------+
 9 2 rows in set, 1 warning (0.00 sec)
10 
11 mysql> 
12 mysql> 

如果此时右表的book_sort_id添加主键索引,此时两条都使用了索引查询,增加了查询效率,如下所示:

 1 mysql> EXPLAIN
 2     -> SELECT * from book_info bi LEFT OUTER JOIN book_sort bs ON bi.book_sort_id = bs.book_sort_id WHERE bi.book_name = 'Java';
 3 +----+-------------+-------+------------+--------+---------------+-----------+---------+----------------------+------+----------+-------+
 4 | id | select_type | table | partitions | type   | possible_keys | key       | key_len | ref                  | rows | filtered | Extra |
 5 +----+-------------+-------+------------+--------+---------------+-----------+---------+----------------------+------+----------+-------+
 6 |  1 | SIMPLE      | bi    | NULL       | ref    | book_name     | book_name | 48      | const                |    1 |   100.00 | NULL  |
 7 |  1 | SIMPLE      | bs    | NULL       | eq_ref | PRIMARY       | PRIMARY   | 4       | book.bi.book_sort_id |    1 |   100.00 | NULL  |
 8 +----+-------------+-------+------------+--------+---------------+-----------+---------+----------------------+------+----------+-------+
 9 2 rows in set, 1 warning (0.01 sec)
10 
11 mysql> 

11、避免mysql索引失效的一些原则。sql优化,是一种概率层面的优化,至于是否实际使用了我们的优化,需要通过explain进行推测。索引优化,是一个大部分情况适用的结论,但是由于SQL优化器等原因,该结论不是100%正确的。

  1)、复合索引的时候不要跨列或者无序使用(最佳左前缀原则)。复合索引,尽量使用全索引匹配。对于复合索引,如果前面的字段索引失效,后面的字段索引就失效了。   2)、不要在索引上进行任何操作(进行计算,函数,类型转换),否则索引失效。   3)、复合索引不能使用不等于(!= 或者 <>)或者is null(is not null),否则自身以及右侧所有全部失效。复合索引中如果有>大于号,则自身和右侧索引全部失效。一般而言,范围查询>、<、in之后的索引失效。   4)、like尽量以"常量"开头,不要以'%'开头,可以将%加到常量的后面,否则索引失效。如果非要以%开头,可以使用索引覆盖,即模糊查询的字段作为查询出的字段,不用回表查询。   5)、尽量不要使用类型转换(显式转换、隐式转换),否则都会使索引失效。   6)、尽量不要使用or,否则索引失效。会将or左侧的索引失效了。   7)、对于exist和in,如果主查询的数据集大,则使用In,此时In的效率高些。如果子查询的数据集大,则使用exist,此时exist的效率高些。exist的含义就是将主查询的结果,放到子查询的结果中进行校验(看子查询是否有数据,如果有数据,则校验成功),如果复合校验,则保留数据。   8)、order by优化,using filesort有两种算法,双路排序、单路排序(根据IO的次数)。Mysql4.1之前默认使用的是双路排序,Mysql4.1之后使用的是单路排序。双路就是两次IO,会扫描两次磁盘,第一次从磁盘读取排序字段,对排序字段进行排序,第二次扫描其他字段,IO消耗性能。单路排序只读取一次,这一次就是全部的字段,在buffer中进行排序,但是单路排序,会有一定的隐患,不一定是真的一次IO,有可能是多次IO,如果数据量特别大,则无法将所有字段的数据一次性读取完毕,因此会进行分片读取。注意,单路排序会比双路排序占用更多的buffer,单路排序在使用的时候,如果数据量过大,可以考虑调大buffer的容量大小,set max_length_for_sort_data=1024,单位是字节。如果max_length_for_sort_data值太低,则mysql会自动从单路切换到双路,太低的条件是需要排序的列的总大小超过了max_length_for_sort_data定义的字节数。   总结:提高order by查询的策略,可以选择使用单路,双路,调整buffer的容量大小。避免select *的查询,只查询出需要的字段即可。复合索引,不要跨列使用,避免出现using filesort。保证全部的排序字段排序的一致性,都是升序排序或者都是降序排列。

12、mysql的慢查询日志,mysql提供的一种日志记录,用于记录mysql中响应时间超过阈值的sql语句long_query_time,默认是10秒。慢查询日志默认是关闭的,建议,在开发调优的时候打开,项目上线关闭即可。

检查是否开启了慢查询日志,如下所示:

 1 mysql> show variables like '%slow_query_log%';
 2 +---------------------+-----------------------------------+
 3 | Variable_name       | Value                             |
 4 +---------------------+-----------------------------------+
 5 | slow_query_log      | OFF                               |
 6 | slow_query_log_file | /var/lib/mysql/k8s-node3-slow.log |
 7 +---------------------+-----------------------------------+
 8 2 rows in set (0.00 sec)
 9 
10 mysql> 

开启mysql的慢查询日志,临时开启,重启mysql就关闭mysql的慢查询了,在内存中开启,如下所示:

 1 mysql> set global slow_query_log = 1;
 2 Query OK, 0 rows affected (0.02 sec)
 3 
 4 mysql> show variables like '%slow_query_log%';
 5 +---------------------+-----------------------------------+
 6 | Variable_name       | Value                             |
 7 +---------------------+-----------------------------------+
 8 | slow_query_log      | ON                                |
 9 | slow_query_log_file | /var/lib/mysql/k8s-node3-slow.log |
10 +---------------------+-----------------------------------+
11 2 rows in set (0.01 sec)
12 
13 mysql> 

开启mysql的慢查询日志,永久开启,修改配置文件/etc/my.cnf,如下所示:

1 [mysqld]
2 slow_query_log=1
3 slow_query_log_file=/var/lib/mysql/localhost-slow.log

重启mysql,然后查看是否已经开启了慢查询,如下所示:

 1 [root@k8s-node3 ~]# vim /etc/my.cnf
 2 [root@k8s-node3 ~]# systemctl restart mysqld.service 
 3 [root@k8s-node3 ~]# mysql -uroot -h127.0.0.1 -p123456
 4 mysql: [Warning] Using a password on the command line interface can be insecure.
 5 Welcome to the MySQL monitor.  Commands end with ; or \g.
 6 Your MySQL connection id is 2
 7 Server version: 5.7.30-log MySQL Community Server (GPL)
 8 
 9 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
10 
11 Oracle is a registered trademark of Oracle Corporation and/or its
12 affiliates. Other names may be trademarks of their respective
13 owners.
14 
15 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
16 
17 mysql> show variables like '%slow_query_log%';
18 +---------------------+-----------------------------------+
19 | Variable_name       | Value                             |
20 +---------------------+-----------------------------------+
21 | slow_query_log      | ON                                |
22 | slow_query_log_file | /var/lib/mysql/localhost-slow.log |
23 +---------------------+-----------------------------------+
24 2 rows in set (0.01 sec)
25 
26 mysql> 

查看慢查询的默认超时时间,默认超时时间是10秒钟,如下所示:

1 mysql> show variables like '%long_query_time%';
2 +-----------------+-----------+
3 | Variable_name   | Value     |
4 +-----------------+-----------+
5 | long_query_time | 10.000000 |
6 +-----------------+-----------+
7 1 row in set (0.00 sec)
8 
9 mysql> 

mysql的慢查询日志,临时超时阈值时间设置set global long_query_time = 5.000000;,需要重新登录mysql才生效,如下所示:

 1 mysql> show variables like '%long_query_time%';
 2 +-----------------+-----------+
 3 | Variable_name   | Value     |
 4 +-----------------+-----------+
 5 | long_query_time | 10.000000 |
 6 +-----------------+-----------+
 7 1 row in set (0.00 sec)
 8 
 9 mysql> set global long_query_time = 5.000000;
10 Query OK, 0 rows affected (0.00 sec)
11 
12 mysql> show variables like '%long_query_time%';
13 +-----------------+-----------+
14 | Variable_name   | Value     |
15 +-----------------+-----------+
16 | long_query_time | 10.000000 |
17 +-----------------+-----------+
18 1 row in set (0.00 sec)
19 
20 mysql> exit;
21 Bye
22 [root@k8s-node3 ~]# mysql -uroot -h127.0.0.1 -p123456
23 mysql: [Warning] Using a password on the command line interface can be insecure.
24 Welcome to the MySQL monitor.  Commands end with ; or \g.
25 Your MySQL connection id is 3
26 Server version: 5.7.30-log MySQL Community Server (GPL)
27 
28 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
29 
30 Oracle is a registered trademark of Oracle Corporation and/or its
31 affiliates. Other names may be trademarks of their respective
32 owners.
33 
34 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
35 
36 mysql> show variables like '%long_query_time%';
37 +-----------------+----------+
38 | Variable_name   | Value    |
39 +-----------------+----------+
40 | long_query_time | 5.000000 |
41 +-----------------+----------+
42 1 row in set (0.00 sec)
43 
44 mysql> 

mysql的慢查询日志,永久超时阈值时间设置,需要重启mysql服务,如下所示:

1 [mysqld]
2 slow_query_log=1
3 slow_query_log_file=/var/lib/mysql/localhost-slow.log
4 long_query_time=3

重启mysql服务,如下所示:

 1 [root@k8s-node3 ~]# systemctl restart mysqld.service 
 2 [root@k8s-node3 ~]# mysql -uroot -h127.0.0.1 -p123456
 3 mysql: [Warning] Using a password on the command line interface can be insecure.
 4 Welcome to the MySQL monitor.  Commands end with ; or \g.
 5 Your MySQL connection id is 2
 6 Server version: 5.7.30-log MySQL Community Server (GPL)
 7 
 8 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 9 
10 Oracle is a registered trademark of Oracle Corporation and/or its
11 affiliates. Other names may be trademarks of their respective
12 owners.
13 
14 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
15 
16 mysql> show variables like '%long_query_time%';
17 +-----------------+----------+
18 | Variable_name   | Value    |
19 +-----------------+----------+
20 | long_query_time | 3.000000 |
21 +-----------------+----------+
22 1 row in set (0.00 sec)
23 
24 mysql> 

mysql的慢查询日志,查看慢查询的条数,如下所示:

 1 mysql> select sleep(4);
 2 +----------+
 3 | sleep(4) |
 4 +----------+
 5 |        0 |
 6 +----------+
 7 1 row in set (4.01 sec)
 8 
 9 mysql> show global status like '%slow_queries%';
10 +---------------+-------+
11 | Variable_name | Value |
12 +---------------+-------+
13 | Slow_queries  | 1     |
14 +---------------+-------+
15 1 row in set (0.00 sec)
16 
17 mysql> 

慢查询的sql被记录在了日志中,我们可以通过日志查看具体的慢sql,目录var/lib/mysql/localhost-slow.log是我们自己配置的,如下所示:

 1 [root@k8s-node3 ~]# cat /var/lib/mysql/localhost-slow.log 
 2 /usr/sbin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
 3 Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
 4 Time                 Id Command    Argument
 5 /usr/sbin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
 6 Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
 7 Time                 Id Command    Argument
 8 # Time: 2020-07-19T13:24:44.111649Z
 9 # User@Host: root[root] @ localhost [127.0.0.1]  Id:     2
10 # Query_time: 4.003324  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
11 SET timestamp=1595165084;
12 select sleep(4);
13 [root@k8s-node3 ~]# 

也可以通过mysqldumpslow工具查看慢sql,此是mysql自带的工具,s代表了排序方式,r代表了逆序,l代表了锁定时间,g代表了正则匹配模式,如下所示:

 1 [root@k8s-node3 ~]# mysqldumpslow --help
 2 Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
 3 
 4 Parse and summarize the MySQL slow query log. Options are
 5 
 6   --verbose    verbose
 7   --debug      debug
 8   --help       write this text to standard output
 9 
10   -v           verbose
11   -d           debug
12   -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
13                 al: average lock time
14                 ar: average rows sent
15                 at: average query time
16                  c: count
17                  l: lock time
18                  r: rows sent
19                  t: query time  
20   -r           reverse the sort order (largest last instead of first)
21   -t NUM       just show the top n queries
22   -a           don't abstract all numbers to N and strings to 'S'
23   -n NUM       abstract numbers with at least n digits within names
24   -g PATTERN   grep: only consider stmts that include this string
25   -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
26                default is '*', i.e. match all
27   -i NAME      name of server instance (if using mysql.server startup script)
28   -l           don't subtract lock time from total time
29 
30 [root@k8s-node3 ~]# 

mysqldumpslow具体使用,如下所示:

 1 [root@k8s-node3 ~]# mysqldumpslow -s -r -t 3 /var/lib/mysql/localhost-slow.log 
 2 
 3 Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
 4 Count: 6  Time=4.33s (26s)  Lock=0.00s (0s)  Rows=1.0 (6), root[root]@localhost
 5   select sleep(N)
 6 
 7 Died at /usr/bin/mysqldumpslow line 167, <> chunk 6.
 8 [root@k8s-node3 ~]# mysqldumpslow -s -c -t 3 /var/lib/mysql/localhost-slow.log 
 9 
10 Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
11 Count: 6  Time=4.33s (26s)  Lock=0.00s (0s)  Rows=1.0 (6), root[root]@localhost
12   select sleep(N)
13 
14 Died at /usr/bin/mysqldumpslow line 167, <> chunk 6.
15 [root@k8s-node3 ~]# mysqldumpslow -s t -t 3 "left join" /var/lib/mysql/localhost-slow.log 
16 
17 Reading mysql slow query log from left join /var/lib/mysql/localhost-slow.log
18 Can't open left join: No such file or directory at /usr/bin/mysqldumpslow line 97.
19 Count: 6  Time=4.33s (26s)  Lock=0.00s (0s)  Rows=1.0 (6), root[root]@localhost
20   select sleep(N)
21 
22 Died at /usr/bin/mysqldumpslow line 167, <> chunk 6.
23 [root@k8s-node3 ~]# mysqldumpslow -s t -t 3 -g "left join" /var/lib/mysql/localhost-slow.log 
24 
25 Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
26 Died at /usr/bin/mysqldumpslow line 167, <> chunk 6.
27 [root@k8s-node3 ~]# 

13、通过profiles分析mysql的海量数据。首先创建两个数据表,如下所示:

 1 SET NAMES utf8mb4;
 2 SET FOREIGN_KEY_CHECKS = 0;
 3 
 4 -- ----------------------------
 5 -- Table structure for dept
 6 -- ----------------------------
 7 DROP TABLE IF EXISTS `dept`;
 8 CREATE TABLE `dept`  (
 9   `dno` int(11) NOT NULL,
10   `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
11   `location` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
12   PRIMARY KEY (`dno`) USING BTREE
13 ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
14 
15 -- ----------------------------
16 -- Table structure for emp
17 -- ----------------------------
18 DROP TABLE IF EXISTS `emp`;
19 CREATE TABLE `emp`  (
20   `eid` int(11) NOT NULL,
21   `ename` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
22   `job` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
23   `deptno` int(5) DEFAULT NULL,
24   PRIMARY KEY (`eid`) USING BTREE
25 ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

创建一个存储函数,方便海量插入数据,如下所示:

 1 -- ----------------------------
 2 -- Function structure for randstring
 3 -- ----------------------------
 4 DROP FUNCTION IF EXISTS `randstring`;
 5 delimiter ;;
 6 CREATE DEFINER=`root`@`localhost` FUNCTION `randstring`( n INT ) RETURNS varchar(255) CHARSET utf8
 7 BEGIN
 8 DECLARE
 9 all_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
10 DECLARE
11 return_str VARCHAR ( 255 ) DEFAULT '';
12 DECLARE
13 i INT DEFAULT 0;
14 WHILE
15 i < n DO
16 
17 SET return_str = CONCAT( return_str, SUBSTRING( all_str, FLOOR( 1 + RAND( ) * 52 ), 1 ) );
18 
19 SET i = i + 1;
20 
21 END WHILE;
22 RETURN return_str;
23 
24 END
25 ;;
26 delimiter ;
27 
28 SET FOREIGN_KEY_CHECKS = 1;
29 
30 -- 执行,如下所示:
31 
32 
33 mysql> delimiter $
34 mysql> CREATE FUNCTION randstring ( n INT ) RETURNS VARCHAR ( 255 ) BEGIN
35     -> DECLARE
36     -> all_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
37     -> DECLARE
38     -> return_str VARCHAR ( 255 ) DEFAULT '';
39     -> DECLARE
40     -> i INT DEFAULT 0;
41     -> WHILE
42     -> i < n DO
43     -> 
44     -> SET return_str = CONCAT( return_str, SUBSTRING( all_str, FLOOR( 1 + RAND( ) * 52 ), 1 ) );
45     -> 
46     -> SET i = i + 1;
47     -> 
48     -> END WHILE;
49     -> RETURN return_str;
50     -> 
51     -> END $
52 Query OK, 0 rows affected (0.03 sec)
53 
54 mysql> 

创建一个生成随机数0-99的存储函数,如下所示:

 1 -- 防止分号造成语义中断。
 2 
 3 delimiter $
 4 CREATE FUNCTION rand_num ( ) RETURNS INT ( 5 ) BEGIN
 5 DECLARE
 6     i INT DEFAULT 0;
 7 
 8 SET i = FLOOR( rand( ) * 100 );
 9 RETURN i;
10 
11 END $

通过存储过程插入海量数据,把上面的两个存储函数使用上,注意,存储函数有返回值,存储过程没有返回值,如下所示:

 1 mysql> 
 2 mysql> delimiter $
 3 mysql> CREATE PROCEDURE insert_emp ( IN eid_start INT ( 10 ), IN data_times INT ( 10 ) ) BEGIN
 4     -> DECLARE
 5     -> i INT DEFAULT 0;
 6     -> 
 7     -> SET autocommit = 0;
 8     -> REPEAT
 9     -> INSERT INTO emp
10     -> VALUES
11     -> ( eid_start + i, randstring ( 5 ), 'other', rand_num ( ) );
12     -> 
13     -> SET i = i + 1;
14     -> UNTIL i = data_times 
15     -> END REPEAT;
16     -> COMMIT;
17     -> 
18     -> END $
19 Query OK, 0 rows affected (0.01 sec)
20 
21 mysql> 
 1 mysql> 
 2 mysql> delimiter $
 3 mysql> CREATE PROCEDURE insert_dept ( IN dno_start INT ( 10 ), IN data_times INT ( 10 ) ) BEGIN
 4     -> DECLARE
 5     -> i INT DEFAULT 0;
 6     -> 
 7     -> SET autocommit = 0;
 8     -> REPEAT
 9     -> INSERT INTO dept
10     -> VALUES
11     -> ( dno_start + i, randstring ( 6 ), randstring ( 8 ) );
12     -> 
13     -> SET i = i + 1;
14     -> UNTIL i = data_times 
15     -> END REPEAT;
16     -> COMMIT;
17     -> 
18     -> END $
19 Query OK, 0 rows affected (0.00 sec)
20 
21 mysql> 

编写好存储过程之后,就可以开始调用存储过程了,开始批量插入数据,如下所示:

 1 mysql> delimiter ;
 2 mysql> CALL insert_emp(1000,800000);
 3 Query OK, 0 rows affected (1 min 8.33 sec)
 4 
 5 mysql> 
 6 
 7 mysql> delimiter ;
 8 mysql> CALL insert_dept(10,30);
 9 Query OK, 0 rows affected (0.01 sec)
10 
11 mysql> 
12 
13 mysql> select count(*) from emp;
14 +----------+
15 | count(*) |
16 +----------+
17 |   800000 |
18 +----------+
19 1 row in set (0.22 sec)
20 
21 mysql> select count(*) from dept;
22 +----------+
23 | count(*) |
24 +----------+
25 |       30 |
26 +----------+
27 1 row in set (0.00 sec)
28 
29 mysql> 

海量数据已经生成了,这里通过profiles分析mysql的海量数据。 默认关闭,需要打开,如下所示:

 1 mysql> show profiles;
 2 Empty set, 1 warning (0.00 sec)
 3 
 4 mysql> show variables like '%profiling%';
 5 +------------------------+-------+
 6 | Variable_name          | Value |
 7 +------------------------+-------+
 8 | have_profiling         | YES   |
 9 | profiling              | OFF   |
10 | profiling_history_size | 15    |
11 +------------------------+-------+
12 3 rows in set (0.04 sec)
13 
14 mysql> set profiling = on;
15 Query OK, 0 rows affected, 1 warning (0.00 sec)
16 
17 mysql> show variables like '%profiling%';
18 +------------------------+-------+
19 | Variable_name          | Value |
20 +------------------------+-------+
21 | have_profiling         | YES   |
22 | profiling              | ON    |
23 | profiling_history_size | 15    |
24 +------------------------+-------+
25 3 rows in set (0.01 sec)
26 
27 mysql> show profiles;
28 +----------+------------+-----------------------------------+
29 | Query_ID | Duration   | Query                             |
30 +----------+------------+-----------------------------------+
31 |        1 | 0.00209325 | show variables like '%profiling%' |
32 +----------+------------+-----------------------------------+
33 1 row in set, 1 warning (0.00 sec)
34 
35 mysql> 

show profiles会记录所有profiling打开之后的的所有sql查询语句。Query_ID是编号,Duration表示花费的时间,单位是秒,Query是查询的语句。

 1 mysql> show profiles;
 2 +----------+------------+-----------------------------------+
 3 | Query_ID | Duration   | Query                             |
 4 +----------+------------+-----------------------------------+
 5 |        1 | 0.00209325 | show variables like '%profiling%' |
 6 +----------+------------+-----------------------------------+
 7 1 row in set, 1 warning (0.00 sec)
 8 
 9 mysql> select count(*) from dept;
10 +----------+
11 | count(*) |
12 +----------+
13 |       30 |
14 +----------+
15 1 row in set (0.00 sec)
16 
17 mysql> show profiles;
18 +----------+------------+-----------------------------------+
19 | Query_ID | Duration   | Query                             |
20 +----------+------------+-----------------------------------+
21 |        1 | 0.00209325 | show variables like '%profiling%' |
22 |        2 | 0.00075825 | select count(*) from dept         |
23 +----------+------------+-----------------------------------+
24 2 rows in set, 1 warning (0.00 sec)
25 
26 mysql> 

缺点,是Duration显示的时间不够精准,只是将cpu、IO、内存等等消费的总和。只能看到总共消费的时间,不能看到各个硬件消费的时间。

如何进行精准分析呢,需要通过一个sql诊断show profile all for query 上一步查询的Query_ID。

 1 mysql> show profiles;
 2 +----------+------------+-----------------------------------+
 3 | Query_ID | Duration   | Query                             |
 4 +----------+------------+-----------------------------------+
 5 |        1 | 0.00209325 | show variables like '%profiling%' |
 6 |        2 | 0.00075825 | select count(*) from dept         |
 7 |        3 | 0.00071025 | select * from dept                |
 8 +----------+------------+-----------------------------------+
 9 3 rows in set, 1 warning (0.00 sec)
10 
11 mysql> show profile all for query 3;
12 +----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
13 | Status               | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function       | Source_file          | Source_line |
14 +----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
15 | starting             | 0.000102 | 0.000097 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | NULL                  | NULL                 |        NULL |
16 | checking permissions | 0.000017 | 0.000012 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access          | sql_authorization.cc |         809 |
17 | Opening tables       | 0.000061 | 0.000064 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc          |        5781 |
18 | init                 | 0.000077 | 0.000077 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         128 |
19 | System lock          | 0.000019 | 0.000016 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc              |         330 |
20 | optimizing           | 0.000007 | 0.000006 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         158 |
21 | statistics           | 0.000054 | 0.000058 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         374 |
22 | preparing            | 0.000022 | 0.000018 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         482 |
23 | executing            | 0.000005 | 0.000005 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         126 |
24 | Sending data         | 0.000164 | 0.000169 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         202 |
25 | end                  | 0.000013 | 0.000009 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         206 |
26 | query end            | 0.000009 | 0.000009 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        4956 |
27 | closing tables       | 0.000009 | 0.000009 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        5009 |
28 | freeing items        | 0.000115 | 0.000118 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc         |        5622 |
29 | cleaning up          | 0.000037 | 0.000034 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command      | sql_parse.cc         |        1931 |
30 +----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
31 15 rows in set, 1 warning (0.01 sec)
32 
33 mysql> 

如果只关心部分字段,可以进行部分字段的查询,如下所示:

 1 mysql> show profile cpu,block io for query 3;
 2 +----------------------+----------+----------+------------+--------------+---------------+
 3 | Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
 4 +----------------------+----------+----------+------------+--------------+---------------+
 5 | starting             | 0.000102 | 0.000097 |   0.000000 |            0 |             0 |
 6 | checking permissions | 0.000017 | 0.000012 |   0.000000 |            0 |             0 |
 7 | Opening tables       | 0.000061 | 0.000064 |   0.000000 |            0 |             0 |
 8 | init                 | 0.000077 | 0.000077 |   0.000000 |            0 |             0 |
 9 | System lock          | 0.000019 | 0.000016 |   0.000000 |            0 |             0 |
10 | optimizing           | 0.000007 | 0.000006 |   0.000000 |            0 |             0 |
11 | statistics           | 0.000054 | 0.000058 |   0.000000 |            0 |             0 |
12 | preparing            | 0.000022 | 0.000018 |   0.000000 |            0 |             0 |
13 | executing            | 0.000005 | 0.000005 |   0.000000 |            0 |             0 |
14 | Sending data         | 0.000164 | 0.000169 |   0.000000 |            0 |             0 |
15 | end                  | 0.000013 | 0.000009 |   0.000000 |            0 |             0 |
16 | query end            | 0.000009 | 0.000009 |   0.000000 |            0 |             0 |
17 | closing tables       | 0.000009 | 0.000009 |   0.000000 |            0 |             0 |
18 | freeing items        | 0.000115 | 0.000118 |   0.000000 |            0 |             0 |
19 | cleaning up          | 0.000037 | 0.000034 |   0.000000 |            0 |             0 |
20 +----------------------+----------+----------+------------+--------------+---------------+
21 15 rows in set, 1 warning (0.00 sec)
22 
23 mysql> 

查看全局的查询日志(默认关闭的,需要手动开启),记录开启之后的,全部sql语句。这些全局的记录操作,仅仅在调优和开发过程中打开即可,在正式环境要进行关闭的,如下所示:

 1 mysql> show variables like '%general_log%';
 2 +------------------+------------------------------+
 3 | Variable_name    | Value                        |
 4 +------------------+------------------------------+
 5 | general_log      | OFF                          |
 6 | general_log_file | /var/lib/mysql/k8s-node3.log |
 7 +------------------+------------------------------+
 8 2 rows in set (0.01 sec)
 9 
10 mysql> set global general_log = 1;
11 Query OK, 0 rows affected (0.00 sec)
12 
13 mysql> show variables like '%general_log%';
14 +------------------+------------------------------+
15 | Variable_name    | Value                        |
16 +------------------+------------------------------+
17 | general_log      | ON                           |
18 | general_log_file | /var/lib/mysql/k8s-node3.log |
19 +------------------+------------------------------+
20 2 rows in set (0.00 sec)
21 
22 mysql> 
1 mysql> set global log_output='table';
2 Query OK, 0 rows affected (0.00 sec)

开启之后,会记录所有的sql语句的,会被记录到mysql.general_log数据表中,如下所示:

 1 [root@k8s-node3 ~]# cat /var/lib/mysql/k8s-node3.log
 2 /usr/sbin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
 3 Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
 4 Time                 Id Command    Argument
 5 2020-07-20T07:07:09.834131Z       12 Query    show variables like '%general_log%'
 6 2020-07-20T07:07:57.165503Z       12 Query    SELECT DATABASE()
 7 2020-07-20T07:07:57.166003Z       12 Init DB    bigdata
 8 2020-07-20T07:07:57.168232Z       12 Query    show databases
 9 2020-07-20T07:07:57.169327Z       12 Query    show tables
10 2020-07-20T07:07:57.169786Z       12 Field List    dept 
11 2020-07-20T07:07:57.171001Z       12 Field List    emp 
12 2020-07-20T07:07:59.196072Z       12 Query    show tables
13 2020-07-20T07:08:11.721455Z       12 Query    select * from dept
14 2020-07-20T07:08:19.914896Z       12 Query    select * from emp limit 3000
15 2020-07-20T07:08:32.237933Z       12 Query    show variables like '%general_log%'
16 [root@k8s-node3 ~]# 

如果开启了mysql> set global log_output='table';也可以查看mysql的数据表来查看sql语句,如下所示:

注意:这里既可以指定表也可以指定文件,看个人需求了:set global log_output='table';或者set global log_output='file';

 1 mysql> select * from mysql.general_log;
 2 +----------------------------+------------------------------------+-----------+-----------+--------------+---------------------------------+
 3 | event_time                 | user_host                          | thread_id | server_id | command_type | argument                        |
 4 +----------------------------+------------------------------------+-----------+-----------+--------------+---------------------------------+
 5 | 2020-07-20 15:10:52.374558 | root[root] @ localhost [127.0.0.1] |        12 |         0 | Query        | select * from mysql.general_log |
 6 | 2020-07-20 15:11:27.112186 | root[root] @ localhost [127.0.0.1] |        12 |         0 | Query        | select * from dept              |
 7 | 2020-07-20 15:11:30.430782 | root[root] @ localhost [127.0.0.1] |        12 |         0 | Query        | select * from emp limit 3000    |
 8 | 2020-07-20 15:11:32.430116 | root[root] @ localhost [127.0.0.1] |        12 |         0 | Query        | select * from mysql.general_log |
 9 +----------------------------+------------------------------------+-----------+-----------+--------------+---------------------------------+
10 4 rows in set (0.00 sec)
11 
12 mysql> 

14、锁机制,解决因资源共享而造成的并发问题。锁的分类,根据操作类型分,可以分为读锁(读锁也称为共享锁)、写锁(写锁也称为互斥锁)。根据操作范围来分,可以分为表级锁、行级锁、页级锁。

 1 SET NAMES utf8mb4;
 2 SET FOREIGN_KEY_CHECKS = 0;
 3 
 4 -- ----------------------------
 5 -- Table structure for user
 6 -- ----------------------------
 7 DROP TABLE IF EXISTS `user`;
 8 CREATE TABLE `user`  (
 9   `id` int(11) NOT NULL AUTO_INCREMENT,
10   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
11   PRIMARY KEY (`id`) USING BTREE
12 ) ENGINE = MyISAM AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
13 
14 -- ----------------------------
15 -- Records of user
16 -- ----------------------------
17 INSERT INTO `user` VALUES (1, '张三');
18 INSERT INTO `user` VALUES (2, '李四');
19 INSERT INTO `user` VALUES (3, '王五');
20 INSERT INTO `user` VALUES (4, '赵六');
21 INSERT INTO `user` VALUES (5, '孙七');
22 
23 SET FOREIGN_KEY_CHECKS = 1;

14.1、根据操作类型分,可以分为读锁、写锁。

1)、读锁(共享锁):对同一个数据,多个读操作可以同时进行,互不干扰。   2)、写锁(互斥锁):如果当前写操作没有完毕,则无法进行其他的读操作,写操作。 14.2、根据操作范围来分,可以分为表级锁、行级锁、页级锁。

  1)、表级锁:一次性对一张表整体加锁。MyISAM存储引擎(开销小,加锁快,缺点就是锁的范围大容易发生锁冲突,并发度低),是表级锁,性能优化。   2)、行级锁:一次性对一行数据进行加锁。InnoDB存储引擎(开销大,加锁慢,容易出现死锁,锁的范围较小,不易发生锁冲突,并发度高,很小的概率发生高并发问题,这些问题分别是脏读、幻读、不可重复读、丢失更新),是行级锁,事务优先,适合高并发操作。

14.3、如何查看加锁的表呢,如下所示:

  1 mysql> show open tables;
  2 +--------------------+------------------------------------------------------+--------+-------------+
  3 | Database           | Table                                                | In_use | Name_locked |
  4 +--------------------+------------------------------------------------------+--------+-------------+
  5 | performance_schema | events_waits_summary_by_user_by_event_name           |      0 |           0 |
  6 | performance_schema | events_waits_summary_global_by_event_name            |      0 |           0 |
  7 | performance_schema | events_transactions_summary_global_by_event_name     |      0 |           0 |
  8 | performance_schema | replication_connection_status                        |      0 |           0 |
  9 | performance_schema | events_waits_summary_by_account_by_event_name        |      0 |           0 |
 10 | mysql              | engine_cost                                          |      0 |           0 |
 11 | performance_schema | metadata_locks                                       |      0 |           0 |
 12 | performance_schema | status_by_user                                       |      0 |           0 |
 13 | performance_schema | replication_group_member_stats                       |      0 |           0 |
 14 | performance_schema | events_statements_summary_by_account_by_event_name   |      0 |           0 |
 15 | performance_schema | socket_summary_by_event_name                         |      0 |           0 |
 16 | performance_schema | prepared_statements_instances                        |      0 |           0 |
 17 | performance_schema | events_statements_history_long                       |      0 |           0 |
 18 | performance_schema | objects_summary_global_by_type                       |      0 |           0 |
 19 | performance_schema | file_instances                                       |      0 |           0 |
 20 | performance_schema | events_stages_summary_by_user_by_event_name          |      0 |           0 |
 21 | performance_schema | memory_summary_by_thread_by_event_name               |      0 |           0 |
 22 | performance_schema | events_stages_history_long                           |      0 |           0 |
 23 | performance_schema | cond_instances                                       |      0 |           0 |
 24 | performance_schema | global_status                                        |      0 |           0 |
 25 | performance_schema | socket_summary_by_instance                           |      0 |           0 |
 26 | book               | user_info                                            |      0 |           0 |
 27 | performance_schema | session_status                                       |      0 |           0 |
 28 | performance_schema | session_connect_attrs                                |      0 |           0 |
 29 | mysql              | plugin                                               |      0 |           0 |
 30 | mysql              | time_zone_name                                       |      0 |           0 |
 31 | performance_schema | events_statements_summary_by_program                 |      0 |           0 |
 32 | performance_schema | events_stages_current                                |      0 |           0 |
 33 | performance_schema | setup_instruments                                    |      0 |           0 |
 34 | book               | book_sort                                            |      0 |           0 |
 35 | book               | book_recommendation                                  |      0 |           0 |
 36 | mysql              | func                                                 |      0 |           0 |
 37 | performance_schema | events_waits_history_long                            |      0 |           0 |
 38 | performance_schema | rwlock_instances                                     |      0 |           0 |
 39 | mysql              | time_zone_leap_second                                |      0 |           0 |
 40 | performance_schema | table_io_waits_summary_by_table                      |      0 |           0 |
 41 | performance_schema | events_transactions_summary_by_account_by_event_name |      0 |           0 |
 42 | mysql              | time_zone_transition_type                            |      0 |           0 |
 43 | performance_schema | events_waits_current                                 |      0 |           0 |
 44 | performance_schema | replication_connection_configuration                 |      0 |           0 |
 45 | mysql              | procs_priv                                           |      0 |           0 |
 46 | performance_schema | events_transactions_summary_by_user_by_event_name    |      0 |           0 |
 47 | performance_schema | replication_applier_configuration                    |      0 |           0 |
 48 | performance_schema | events_statements_summary_by_user_by_event_name      |      0 |           0 |
 49 | performance_schema | events_stages_summary_global_by_event_name           |      0 |           0 |
 50 | performance_schema | replication_applier_status_by_worker                 |      0 |           0 |
 51 | performance_schema | events_waits_summary_by_thread_by_event_name         |      0 |           0 |
 52 | performance_schema | session_account_connect_attrs                        |      0 |           0 |
 53 | performance_schema | performance_timers                                   |      0 |           0 |
 54 | performance_schema | setup_consumers                                      |      0 |           0 |
 55 | performance_schema | events_statements_history                            |      0 |           0 |
 56 | bigdata            | emp                                                  |      0 |           0 |
 57 | performance_schema | global_variables                                     |      0 |           0 |
 58 | mysql              | gtid_executed                                        |      0 |           0 |
 59 | mysql              | columns_priv                                         |      0 |           0 |
 60 | performance_schema | events_transactions_summary_by_thread_by_event_name  |      0 |           0 |
 61 | performance_schema | replication_applier_status_by_coordinator            |      0 |           0 |
 62 | mysql              | db                                                   |      0 |           0 |
 63 | mysql              | general_log                                          |      0 |           0 |
 64 | performance_schema | events_stages_summary_by_account_by_event_name       |      0 |           0 |
 65 | performance_schema | variables_by_thread                                  |      0 |           0 |
 66 | book               | user_book                                            |      0 |           0 |
 67 | performance_schema | events_stages_history                                |      0 |           0 |
 68 | bigdata            | dept                                                 |      0 |           0 |
 69 | performance_schema | socket_instances                                     |      0 |           0 |
 70 | performance_schema | table_lock_waits_summary_by_table                    |      0 |           0 |
 71 | mysql              | time_zone                                            |      0 |           0 |
 72 | performance_schema | events_statements_summary_by_thread_by_event_name    |      0 |           0 |
 73 | performance_schema | users                                                |      0 |           0 |
 74 | performance_schema | setup_timers                                         |      0 |           0 |
 75 | performance_schema | memory_summary_by_host_by_event_name                 |      0 |           0 |
 76 | performance_schema | setup_objects                                        |      0 |           0 |
 77 | performance_schema | host_cache                                           |      0 |           0 |
 78 | performance_schema | status_by_account                                    |      0 |           0 |
 79 | mysql              | proxies_priv                                         |      0 |           0 |
 80 | performance_schema | memory_summary_by_account_by_event_name              |      0 |           0 |
 81 | performance_schema | accounts                                             |      0 |           0 |
 82 | performance_schema | replication_group_members                            |      0 |           0 |
 83 | mysql              | tables_priv                                          |      0 |           0 |
 84 | performance_schema | events_stages_summary_by_host_by_event_name          |      0 |           0 |
 85 | performance_schema | events_statements_current                            |      0 |           0 |
 86 | mysql              | proc                                                 |      0 |           0 |
 87 | performance_schema | events_waits_summary_by_instance                     |      0 |           0 |
 88 | performance_schema | events_statements_summary_by_host_by_event_name      |      0 |           0 |
 89 | performance_schema | memory_summary_by_user_by_event_name                 |      0 |           0 |
 90 | performance_schema | events_transactions_history                          |      0 |           0 |
 91 | mysql              | event                                                |      0 |           0 |
 92 | performance_schema | status_by_host                                       |      0 |           0 |
 93 | performance_schema | setup_actors                                         |      0 |           0 |
 94 | performance_schema | threads                                              |      0 |           0 |
 95 | performance_schema | events_statements_summary_global_by_event_name       |      0 |           0 |
 96 | performance_schema | status_by_thread                                     |      0 |           0 |
 97 | performance_schema | file_summary_by_event_name                           |      0 |           0 |
 98 | performance_schema | mutex_instances                                      |      0 |           0 |
 99 | performance_schema | table_handles                                        |      0 |           0 |
100 | performance_schema | session_variables                                    |      0 |           0 |
101 | bigdata            | user                                                 |      0 |           0 |
102 | book               | book_info                                            |      0 |           0 |
103 | performance_schema | events_transactions_current                          |      0 |           0 |
104 | performance_schema | user_variables_by_thread                             |      0 |           0 |
105 | mysql              | time_zone_transition                                 |      0 |           0 |
106 | performance_schema | table_io_waits_summary_by_index_usage                |      0 |           0 |
107 | performance_schema | events_transactions_history_long                     |      0 |           0 |
108 | performance_schema | memory_summary_global_by_event_name                  |      0 |           0 |
109 | performance_schema | events_statements_summary_by_digest                  |      0 |           0 |
110 | performance_schema | events_transactions_summary_by_host_by_event_name    |      0 |           0 |
111 | performance_schema | events_waits_history                                 |      0 |           0 |
112 | mysql              | user                                                 |      0 |           0 |
113 | performance_schema | events_waits_summary_by_host_by_event_name           |      0 |           0 |
114 | performance_schema | events_stages_summary_by_thread_by_event_name        |      0 |           0 |
115 | performance_schema | file_summary_by_instance                             |      0 |           0 |
116 | mysql              | server_cost                                          |      0 |           0 |
117 | performance_schema | hosts                                                |      0 |           0 |
118 | performance_schema | replication_applier_status                           |      0 |           0 |
119 | mysql              | servers                                              |      0 |           0 |
120 +--------------------+------------------------------------------------------+--------+-------------+
121 115 rows in set (0.01 sec)
122 
123 mysql> 

如何加锁呢,如是,lock table 表1 read/write,表2 read/write...。首先研究的是MyISAM存储引擎的读锁,读锁(共享锁):对同一个数据,多个读操作可以同时进行,互不干扰。

 1 mysql> lock table user read;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 mysql> select * from user;
 5 +----+--------+
 6 | id | name   |
 7 +----+--------+
 8 |  1 | 张三   |
 9 |  2 | 李四   |
10 |  3 | 王五   |
11 |  4 | 赵六   |
12 |  5 | 孙七   |
13 +----+--------+
14 5 rows in set (0.00 sec)
15 
16 mysql> 

加了读锁,是不可以进行写的(写操作就是增加、修改、删除),读就是查询。

1 mysql> delete from user where id = 1;
2 ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
3 mysql> 

  总结:MyISAM存储引擎,如果某一个会话,对A表加了read锁,则该会话可以对A表进行读操作,不能进行写操作,且该会话不能对其他表进行读、写操作。会话0给A表加了锁,其它会话,是可以对其他表(A表以外的表)进行读、写操作。对A表是可以读的,但是写操作的时候需要等待读锁的释放。 14.4、加写锁,会话0加写锁,如下所示:

  总结:当前会话即会话0,可以对加了写锁的表进行任何操作(增删改查操作),但是不能操作(增删改查)其他表。对会话0中加写锁的表,可以进行增删改查的前提是,等待会话0释放写锁。

14.5、Mysql中表级锁的锁模式,MyISAM在执行查询语句Select前,会自动给涉及到的所有表加读锁,在执行更新操作DML前,会自动给涉及的表加写锁。所以对MyISAM表进行操作的时候会有以下情况:

  a、对MyISAM表的读操作(加读锁),不会阻塞其他进程(会话)对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其他进程的写操作。   b、对MyISAM表的写操作(加写锁),会阻塞其他进程(会话)对同一表的读和写操作,只有当写锁释放后,才会执行其他进程的读写操作。

15、表锁情况分析及行锁解析,分析表锁定。

  1)、查看那些表加了锁:show open tables;其中1代表加了锁。   2)、分析表锁定的严重程度:show status like 'table%';其中Table_locks_immediate表示立刻可以获取到的锁。Table_locks_waited代表了需要等待的表锁数,如果该值越大说明存在越大的锁竞争。   3)、一般建议使用Table_locks_immediate/Table_locks_waited比率来看,如果大于5000采用InnoDB引擎,如果小于5000采用MyISAM存储引擎。表锁是通过unlock tables解锁的,也可以通过事务进行解锁的,行锁是通过事务commit/rollback解锁的。

  1 mysql> show open tables;
  2 +--------------------+------------------------------------------------------+--------+-------------+
  3 | Database           | Table                                                | In_use | Name_locked |
  4 +--------------------+------------------------------------------------------+--------+-------------+
  5 | performance_schema | events_waits_summary_by_user_by_event_name           |      0 |           0 |
  6 | performance_schema | events_waits_summary_global_by_event_name            |      0 |           0 |
  7 | performance_schema | events_transactions_summary_global_by_event_name     |      0 |           0 |
  8 | performance_schema | replication_connection_status                        |      0 |           0 |
  9 | performance_schema | events_waits_summary_by_account_by_event_name        |      0 |           0 |
 10 | mysql              | engine_cost                                          |      0 |           0 |
 11 | performance_schema | metadata_locks                                       |      0 |           0 |
 12 | performance_schema | status_by_user                                       |      0 |           0 |
 13 | performance_schema | replication_group_member_stats                       |      0 |           0 |
 14 | performance_schema | events_statements_summary_by_account_by_event_name   |      0 |           0 |
 15 | performance_schema | socket_summary_by_event_name                         |      0 |           0 |
 16 | performance_schema | prepared_statements_instances                        |      0 |           0 |
 17 | performance_schema | events_statements_history_long                       |      0 |           0 |
 18 | performance_schema | objects_summary_global_by_type                       |      0 |           0 |
 19 | performance_schema | file_instances                                       |      0 |           0 |
 20 | performance_schema | events_stages_summary_by_user_by_event_name          |      0 |           0 |
 21 | performance_schema | memory_summary_by_thread_by_event_name               |      0 |           0 |
 22 | performance_schema | events_stages_history_long                           |      0 |           0 |
 23 | performance_schema | cond_instances                                       |      0 |           0 |
 24 | performance_schema | global_status                                        |      0 |           0 |
 25 | performance_schema | socket_summary_by_instance                           |      0 |           0 |
 26 | book               | user_info                                            |      0 |           0 |
 27 | performance_schema | session_status                                       |      0 |           0 |
 28 | performance_schema | session_connect_attrs                                |      0 |           0 |
 29 | mysql              | plugin                                               |      0 |           0 |
 30 | mysql              | time_zone_name                                       |      0 |           0 |
 31 | performance_schema | events_statements_summary_by_program                 |      0 |           0 |
 32 | performance_schema | events_stages_current                                |      0 |           0 |
 33 | performance_schema | setup_instruments                                    |      0 |           0 |
 34 | book               | book_sort                                            |      0 |           0 |
 35 | book               | book_recommendation                                  |      0 |           0 |
 36 | mysql              | func                                                 |      0 |           0 |
 37 | performance_schema | events_waits_history_long                            |      0 |           0 |
 38 | performance_schema | rwlock_instances                                     |      0 |           0 |
 39 | mysql              | time_zone_leap_second                                |      0 |           0 |
 40 | performance_schema | table_io_waits_summary_by_table                      |      0 |           0 |
 41 | performance_schema | events_transactions_summary_by_account_by_event_name |      0 |           0 |
 42 | mysql              | time_zone_transition_type                            |      0 |           0 |
 43 | performance_schema | events_waits_current                                 |      0 |           0 |
 44 | performance_schema | replication_connection_configuration                 |      0 |           0 |
 45 | mysql              | procs_priv                                           |      0 |           0 |
 46 | performance_schema | events_transactions_summary_by_user_by_event_name    |      0 |           0 |
 47 | performance_schema | replication_applier_configuration                    |      0 |           0 |
 48 | performance_schema | events_statements_summary_by_user_by_event_name      |      0 |           0 |
 49 | performance_schema | events_stages_summary_global_by_event_name           |      0 |           0 |
 50 | performance_schema | replication_applier_status_by_worker                 |      0 |           0 |
 51 | performance_schema | events_waits_summary_by_thread_by_event_name         |      0 |           0 |
 52 | performance_schema | session_account_connect_attrs                        |      0 |           0 |
 53 | performance_schema | performance_timers                                   |      0 |           0 |
 54 | performance_schema | setup_consumers                                      |      0 |           0 |
 55 | performance_schema | events_statements_history                            |      0 |           0 |
 56 | bigdata            | emp                                                  |      0 |           0 |
 57 | performance_schema | global_variables                                     |      0 |           0 |
 58 | mysql              | gtid_executed                                        |      0 |           0 |
 59 | mysql              | columns_priv                                         |      0 |           0 |
 60 | performance_schema | events_transactions_summary_by_thread_by_event_name  |      0 |           0 |
 61 | performance_schema | replication_applier_status_by_coordinator            |      0 |           0 |
 62 | mysql              | db                                                   |      0 |           0 |
 63 | mysql              | general_log                                          |      0 |           0 |
 64 | performance_schema | events_stages_summary_by_account_by_event_name       |      0 |           0 |
 65 | performance_schema | variables_by_thread                                  |      0 |           0 |
 66 | book               | user_book                                            |      0 |           0 |
 67 | performance_schema | events_stages_history                                |      0 |           0 |
 68 | bigdata            | dept                                                 |      0 |           0 |
 69 | performance_schema | socket_instances                                     |      0 |           0 |
 70 | performance_schema | table_lock_waits_summary_by_table                    |      0 |           0 |
 71 | mysql              | time_zone                                            |      0 |           0 |
 72 | performance_schema | events_statements_summary_by_thread_by_event_name    |      0 |           0 |
 73 | performance_schema | users                                                |      0 |           0 |
 74 | performance_schema | setup_timers                                         |      0 |           0 |
 75 | performance_schema | memory_summary_by_host_by_event_name                 |      0 |           0 |
 76 | performance_schema | setup_objects                                        |      0 |           0 |
 77 | performance_schema | host_cache                                           |      0 |           0 |
 78 | performance_schema | status_by_account                                    |      0 |           0 |
 79 | mysql              | proxies_priv                                         |      0 |           0 |
 80 | performance_schema | memory_summary_by_account_by_event_name              |      0 |           0 |
 81 | performance_schema | accounts                                             |      0 |           0 |
 82 | performance_schema | replication_group_members                            |      0 |           0 |
 83 | mysql              | tables_priv                                          |      0 |           0 |
 84 | performance_schema | events_stages_summary_by_host_by_event_name          |      0 |           0 |
 85 | performance_schema | events_statements_current                            |      0 |           0 |
 86 | mysql              | proc                                                 |      0 |           0 |
 87 | performance_schema | events_waits_summary_by_instance                     |      0 |           0 |
 88 | performance_schema | events_statements_summary_by_host_by_event_name      |      0 |           0 |
 89 | performance_schema | memory_summary_by_user_by_event_name                 |      0 |           0 |
 90 | performance_schema | events_transactions_history                          |      0 |           0 |
 91 | mysql              | event                                                |      0 |           0 |
 92 | performance_schema | status_by_host                                       |      0 |           0 |
 93 | performance_schema | setup_actors                                         |      0 |           0 |
 94 | performance_schema | threads                                              |      0 |           0 |
 95 | performance_schema | events_statements_summary_global_by_event_name       |      0 |           0 |
 96 | performance_schema | status_by_thread                                     |      0 |           0 |
 97 | performance_schema | file_summary_by_event_name                           |      0 |           0 |
 98 | performance_schema | mutex_instances                                      |      0 |           0 |
 99 | performance_schema | table_handles                                        |      0 |           0 |
100 | performance_schema | session_variables                                    |      0 |           0 |
101 | bigdata            | user                                                 |      1 |           0 |
102 | book               | book_info                                            |      0 |           0 |
103 | performance_schema | events_transactions_current                          |      0 |           0 |
104 | performance_schema | user_variables_by_thread                             |      0 |           0 |
105 | mysql              | time_zone_transition                                 |      0 |           0 |
106 | performance_schema | table_io_waits_summary_by_index_usage                |      0 |           0 |
107 | performance_schema | events_transactions_history_long                     |      0 |           0 |
108 | performance_schema | memory_summary_global_by_event_name                  |      0 |           0 |
109 | performance_schema | events_statements_summary_by_digest                  |      0 |           0 |
110 | performance_schema | events_transactions_summary_by_host_by_event_name    |      0 |           0 |
111 | performance_schema | events_waits_history                                 |      0 |           0 |
112 | mysql              | user                                                 |      0 |           0 |
113 | performance_schema | events_waits_summary_by_host_by_event_name           |      0 |           0 |
114 | mysql              | slow_log                                             |      0 |           0 |
115 | performance_schema | file_summary_by_instance                             |      0 |           0 |
116 | mysql              | server_cost                                          |      0 |           0 |
117 | performance_schema | hosts                                                |      0 |           0 |
118 | performance_schema | replication_applier_status                           |      0 |           0 |
119 | mysql              | servers                                              |      0 |           0 |
120 | performance_schema | events_stages_summary_by_thread_by_event_name        |      0 |           0 |
121 +--------------------+------------------------------------------------------+--------+-------------+
122 116 rows in set (0.01 sec)
123 
124 mysql> show status like 'table%';
125 +----------------------------+-------+
126 | Variable_name              | Value |
127 +----------------------------+-------+
128 | Table_locks_immediate      | 411   |
129 | Table_locks_waited         | 0     |
130 | Table_open_cache_hits      | 3     |
131 | Table_open_cache_misses    | 1     |
132 | Table_open_cache_overflows | 0     |
133 +----------------------------+-------+
134 5 rows in set (0.01 sec)
135 
136 mysql> 

16、行锁的注意事项及使用情况分析:

1)、行锁的注意事项,如果没有索引,则行锁会转为表锁。如果索引列发生了类型转换,则索引失效。比如整数转换为字符串类型,导致索引失效,会导致行锁升级为表锁。   2)、行锁的一种特殊情况,间隙锁,值在范围内,但却不存在。行锁如果有where则实际加锁的范围就是where后面的范围,不是数据表中保存的实际的值。   3)、行锁的缺点比表锁性能损耗大,优点是并发能力强,效率高。InnoDB默认采用的是行锁。因此建议高并发采用InnoDB,否则使用MyISAM存储引擎。

17、行锁分析 show status like '%innodb_row_lock%';

  1)、Innodb_row_lock_current_waits代表了当前正在等待锁的数量。   2)、Innodb_row_lock_time代表了等待的总时长,从系统启动到现在一共等待的时间。   3)、Innodb_row_lock_time_avg代表了平均等待时长,从系统启动到现在平均等待的时间。   4)、Innodb_row_lock_time_max代表了最大等待时长,从系统启动到现在最大一次等待的时间。   5)、Innodb_row_lock_waits代表了等待次数,从系统启动到现在一共等待的次数。

 1 mysql> show status like '%innodb_row_lock%';
 2 +-------------------------------+-------+
 3 | Variable_name                 | Value |
 4 +-------------------------------+-------+
 5 | Innodb_row_lock_current_waits | 0     |
 6 | Innodb_row_lock_time          | 0     |
 7 | Innodb_row_lock_time_avg      | 0     |
 8 | Innodb_row_lock_time_max      | 0     |
 9 | Innodb_row_lock_waits         | 0     |
10 +-------------------------------+-------+
11 5 rows in set (0.00 sec)
12 
13 mysql>  

将Mysql的自动提交关闭的三种方式,第一种方式set autocommit=0;第二种方式start transaction;第三种方式begin;通过for update对query查询语句进行加锁。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • JSP连接mysql数据库的重点

    1:用mysql驱动把mysql与tomcat的连接起来。把mysql驱动包(不用解压)放到Tomcat安装目录中lib文件夹下即可。 ? 2:然后在自己的新...

    别先生
  • CentOS7系统yum方式安装MySQL5.7

    参考:https://www.cnblogs.com/bigbrotherer/p/7241845.html#top

    别先生
  • Mysql的主从复制

    答:主从同步的核心是二进制日志文件binary log,对数据库所有的增加、修改、删除操作都会在日志表里面记录一下的。mysql主从复制是异步的,串行化的,有延...

    别先生
  • message from server: "Host 'xxx' is not allowed to connect to this MySQL server的解决

    可能是你的帐号不允许从远程登陆,只能在localhost。这个时候只要在localhost的那台电脑,登入mysql后,更改 "mysql" 数据库里的 "us...

    大道七哥
  • docker 安装 mysql5.7

    故久
  • python

    Dwyane
  • 1 mysql底层解析——连接层,包括连接、解析、缓存、引擎、存储等

    很多时候,程序员对mysql处于频繁使用,但都一知半解的程度,除了会加个索引,貌似也没啥优化的技能了。事实上,mysql能有今日的成就,必然不是靠个索引就吃饭的...

    天涯泪小武
  • mysql 语法集锦

    使用SHOW语句找出在服务器上当前存在什么数据库: mysql> SHOW DATABASES; 创建一个数据库MYSQLDATA mysql> CREATE...

    赵腰静
  • mysql 命令小集锦

    1:使用SHOW语句找出在服务器上当前存在什么数据库: mysql> SHOW DATABASES; 2:创建一个数据库MYSQLDATA mysql> CRE...

    赵腰静
  • MySql常用命令总结

    1:使用SHOW语句找出在服务器上当前存在什么数据库: mysql> SHOW DATABASES; 2:2、创建一个数据库MYSQLDATA mysql> C...

    叫我可儿呀

扫码关注云+社区

领取腾讯云代金券