参考: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查询语句进行加锁。