方法一：使用COUNT()函数查询重复行COUNT()函数是MySQL中常用的聚合函数之一，它可以用于计算表中某个字段值的数量。利用这个函数，我们可以找到表中的重复值和它们的数量。以下是具体的步骤：编写SQL查询语句来选择你想要查找重复数据所在的数据表，同时选择你想要鉴定的字段。例如：SELECT field1, field2, COUNT(field2) FROM table_name GROUP BY field2 HAVING COUNT(field2)>1;以上语句将查询 table_name 表中 field2 字段的值，并找出出现次数大于1的记录。同时，该查询还会显示 field1 字段的值和该字段对应的 field2 记录中的重复次数。执行以上查询语句，你将会得到表中所有的重复数据以及对应的出现次数。你可以在查询结果中看到所有出现次数大于1的字段值，这意味着它出现了至少两次。方法二：使用DISTINCT关键字查询重复行DISTINCT 关键字可以帮助我们去除表中的重复数据。我们可以编写一条 SQL 查询语句来查找一列中的重复数据。以下是具体的步骤：编写SQL查询语句来选择你所需的表，同时选择需要查找的字段。例如：SELECT DISTINCT field1 FROM table_name WHERE field2=‘duplicate_value';以上语句将查询 table_name 表中所有的 field1 字段，并且只选择其中一个重复值。通过将查询结果与表中所有的唯一值进行比较，我们可以得到这个字段中的重复值。执行以上的查询语句，你将会得到表中所有的重复数据，同时还会得到所有唯一的 field1 字段值。方法三：使用自连接查询使用自连接查询是一种比较复杂的方法，但也是一种非常强大的方法，可以用于查找表中重复的行。以下是具体的步骤：编写SQL查询语句，将数据表自连接，使得查询结果中的数据表和原始表是同一个。我们需要选择所需的字段并指定必须相同的字段作为连接条件。例如，SELECT A. FROM table_name A INNER JOIN (SELECT field1, field2, COUNT() FROM table_name GROUP BY field1, field2 HAVING COUNT(*)>1) B ON A.field1=B.field1 AND A.field2=B.field2;以上语句将查询 table_name 表中两列数据：field1 和 field2。它们的值必须与表中的其他记录匹配，以帮助我们找出重复的行。在这个查询中，我们将表名设置为 A，将 inner join 自连接的副本称为 B。执行以上查询语句，你将会得到表中所有的重复数据。结论在MySQL中，查找表中重复的数据是一项常见的任务。本文介绍了三种常见的方法来查找表中的重复数据：使用 COUNT() 函数，使用 DISTINCT 关键字以及使用自连接查询。这些技巧都非常有效，你可以根据实际情况选择最合适的方法。无论哪种方法，都可以帮助你在数据库中有效地查找重复的数据。

 在数据库设计和开发过程中，多表关联查询是一种常见的操作。然而，对于大公司来说，他们通常会尽量避免使用多表关联查询，而是选择多次查询程序中匹配数据。这主要有以下几个原因：  1. 性能问题：多表关联查询通常需要消耗大量的系统资源和时间。特别是在处理大量数据时，查询效率会大大降低，严重影响系统的响应速度和用户体验。而多次查询虽然也需要消耗一定的资源，但相对来说，其性能损耗要小得多。  2. 可维护性：多表关联查询的代码通常比较复杂，不易于理解和修改。如果数据库结构发生变化，可能需要对整个查询进行大规模的修改。而多次查询的程序则相对简单，更容易维护和更新。  3. 数据一致性：多表关联查询可能会引发数据一致性问题。例如，当多个用户同时修改同一张表的数据时，可能会出现数据冲突的情况。而多次查询可以避免这种情况，因为每次查询都是基于数据的某个特定状态，不会受到其他查询的影响。  4. 扩展性：随着业务的发展，数据库中的数据量会不断增加，多表关联查询的性能问题会越来越严重。而多次查询的程序则可以通过增加服务器资源来提高查询速度，具有更好的扩展性。  5. 安全性：多表关联查询可能会暴露过多的数据信息，增加了数据泄露的风险。而多次查询可以只获取用户需要的数据，提高了数据的安全性。  因此，为了提高系统的性能、可维护性、数据一致性、扩展性和安全性，大公司通常会尽量减少多表关联查询，改用多次查询程序中匹配数据。 

MySQL是一种常用的关系型数据库管理系统，它支持多种数据类型和查询操作。在大批量数组中进行in查询时，MySQL的效率问题可能会影响应用程序的性能。本文将介绍MySQL大批量数组in查询时的效率问题及解决方案。效率问题在MySQL中，使用in查询可以快速地筛选出符合条件的记录。然而，当查询条件中的数组元素数量很大时，in查询的效率会受到影响。这是因为MySQL需要对每个元素进行全表扫描，以查找是否存在匹配的记录。这种方法的时间复杂度为O(n)，其中n为数组的长度。当数组长度非常大时，查询效率会非常低。此外，如果数组是无序的，我们还需要先对数组进行排序，这将增加额外的时间开销。解决方案为了提高MySQL大批量数组in查询的效率，我们可以采用以下几种方法：（1）使用子查询子查询是一种将一个查询语句嵌套在另一个查询语句中的技术。通过将in查询转换为子查询，我们可以减少查询的次数，从而提高查询效率。例如，假设我们有一个名为students的表，其中包含id、name和age三个字段。现在我们需要查询id在[1,2,3]中的学生信息。可以使用以下SQL语句实现：SELECT * FROM students WHERE id IN (1, 2, 3);可以将其转换为子查询：SELECT * FROM students WHERE id = any (1, 2, 3);这样，MySQL只需要执行一次子查询，而不是多次全表扫描。这种方法的时间复杂度取决于MySQL优化器的具体实现，但通常比直接使用in查询要快。（2）使用临时表临时表是一种在内存中存储数据的临时数据结构。通过将大批量数组中的元素插入到临时表中，我们可以减少查询的次数，从而提高查询效率。例如，假设我们有一个名为students的表，其中包含id、name和age三个字段。现在我们需要查询id在[1,2,3]中的学生信息。可以使用以下SQL语句实现：CREATE TEMPORARY TABLE temp_ids (id INT); INSERT INTO temp_ids VALUES (1), (2), (3); SELECT * FROM students WHERE id IN (SELECT id FROM temp_ids); DROP TEMPORARY TABLE temp_ids;这种方法的时间复杂度取决于MySQL优化器的具体实现，但通常比直接使用in查询要快。需要注意的是，临时表占用的内存空间有限，因此对于非常大的数组，可能需要调整MySQL的配置参数以允许更大的临时表空间。

 枚举类型字段是否有必要加索引  在数据库设计中，索引是一种非常有用的工具，它可以提高查询性能，加速数据的检索。然而，对于枚举类型字段来说，是否需要加索引这个问题并没有一个明确的答案。本文将从不同的角度来探讨这个问题。  1. 枚举类型字段的特点  枚举类型字段是一种特殊的数据类型，它只能包含预定义的一组值。这些值通常是整数或字符串，用于表示某种特定的状态或属性。例如，一个表示星期的枚举类型字段可能包含以下值：0（表示星期日）、1（表示星期一）等。  2. 枚举类型字段的优势  枚举类型字段具有以下优势：  - 数据完整性：枚举类型字段可以确保数据的准确性和一致性，因为它只能包含预定义的值。这有助于减少数据错误和不一致的可能性。  - 易于理解和维护：枚举类型字段的名称通常可以清楚地描述其含义，这使得其他开发人员更容易理解和使用这些字段。此外，由于枚举类型字段的值是有限的，因此维护这些字段也相对容易。  3. 枚举类型字段的缺点  尽管枚举类型字段具有一些优势，但它也有一些缺点：  - 限制性：枚举类型字段的值是有限的，这意味着它们不能表示无限数量的状态或属性。在某些情况下，这可能会限制应用程序的功能和灵活性。  - 可扩展性：如果需要向枚举类型字段添加新值，可能需要修改数据库模式，这可能会导致应用程序的兼容性问题。  4. 是否需要为枚举类型字段加索引？  对于是否需要为枚举类型字段加索引，这取决于具体的应用场景和需求。以下是一些建议：  - 如果枚举类型字段经常用于查询条件，那么为其添加索引可能是有意义的。索引可以提高查询性能，尤其是在处理大量数据时。  - 如果枚举类型字段的值分布不均匀，那么为其添加索引可能不是最佳选择。在这种情况下，索引可能无法充分利用其性能优势，甚至可能导致查询性能下降。  - 如果枚举类型字段的值很少发生变化，那么为其添加索引可能是不必要的。因为索引需要占用额外的存储空间，而且更新索引可能会影响查询性能。 

opengauss的MySQL兼容性是通过dolphin插件实现的是吗？也就是dolphin的手册就是MySQL兼容性的手册，可以这么理解吗？

 一、安装前准备1、关闭防火墙并取消开机自启动停止防火墙。systemctl stop firewalld.service关闭防火墙。systemctl disable firewalld.service查看防火墙。systemctl status firewalld.service2、关闭SELIinux设置SELinux成为permissive模式，临时关闭selinux。setenforce 0查看selinux状态，确认为Disabled模式。getenforce永久关闭selinux的方法：执行vim /etc/sysconfig/selinux命令，打开SELinux文件，把"SELINUX=enforcing" 改为 "SELINUX=disabled"。保存文件，并重启服务器。确认SELinux是否关闭，如果SELinux status参数显示为disabled即为关闭状态。/usr/sbin/sestatus -v3、创建用户组和用户创建mysql用户组。groupadd mysql创建mysql用户。useradd -g mysql mysql设置mysql用户密码。Huawei@123passwd mysql4、搭建数据盘非性能测试时，直接执行创建数据目录。mkdir /data第一次搭建数据盘（挂载单独硬盘操作）：mkdir /datals /dev/nvme*mkfs.xfs -f /dev/nvme0n1du -sh /dev/nvme0n1mount /dev/nvme0n1 /data/df -h非第一次搭建数据盘（挂载单独硬盘操作）：umount /data/ls /dev/nvme*mkfs.xfs -f /dev/nvme0n1du -sh /dev/nvme0n1mount /dev/nvme0n1 /data/df -h注意：如果执行umount /data/时报错，如下图所示。执行下面的操作解决。yum -y provides fuseryum -y install psmisc-*fuser -km /data/ 该操作需要执行多次，直到没有回显为止umount /data/df -h5、创建数据目录创建数据目录/data和进程所需的相关目录。mkdir -p /data/mysqlcd /data/mysqlmkdir data tmp run log修改数据目录/data的用户组和用户权限为mysql:mysql。chown -R mysql:mysql /datall /二、安装mysql 8.0.201、安装依赖包yum -y install bison ncurses ncurses-devel libaio-devel openssl openssl-devel gmp gmp-devel mpfr mpfr-devel libmpc libmpc-devel wget tar gcc gcc-c++ git rpcgen cmake m42、安装cmake系统自带的CMake软件不能满足当前数据库版本的编译要求，需要升级CMake版本至3.4.3或者以上，本文以升级到3.5.2版本为例。下载CMake 3.5.2。CMake 3.5.2下载地址：https://cmake.org/files/v3.5/cmake-3.5.2.tar.gz将软件包上传至服务器/home目录，并解压。cd /hometar -zxvf cmake-3.5.2.tar.gz进入解压后目录。cd cmake-3.5.2升级CMake。./bootstrapmake -jmake install确认CMake的版本是否为3.5.2。/usr/local/bin/cmake --version3、编译和安装mysql 8.0.20如果编译安装失败，需要执行如下命令清理环境，然后参照该章节的步骤重新解压并编译安装。rm -rf /home/mysql-8.0.20下载源码包。下载MySQL源码包（includes Boost Headers）。下载网站地址：https://downloads.mysql.com/archives/community/直接下载地址：wget https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-8.0.20.tar.gz将mysql-boost-8.0.20.tar.gz上传至服务器“/home”目录下，并解压。cd /hometar -zxvf mysql-boost-8.0.20.tar.gz进入“/home/mysql-8.0.20”源码文件夹，并建立一个编译目录。cd /home/mysql-8.0.20mkdir build进入编译目录，配置MySQL。cd buildcmake .. -DBUILD_CONFIG=mysql_release -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql/data -DWITH_BOOST=/home/mysql-8.0.20/boost/boost_1_70_0关键参数说明参数说明DBUILD_CONFIG设置为mysql_release的含义是指CMake编译参数采用Mysql官方发布release版本时的编译参数。DCMAKE_INSTALL_PREFIX用于指定软件的安装路径，本文安装路径为：/usr/local/mysql。文档中的安装路径只是参考，根据客户实际情况进行配置。DMYSQL_DATADIR创建数据库时，数据文件存放的路径。本次安装路径为：/data/mysql/data。DWITH_BOOST解压MySQL源码包后，解压文件中boost_1_70_0文件夹所在路径。例如，本文解压在“/home”目录下，则路径为：/home/mysql-8.0.20/boost/boost_1_70_0。编译MySQL。make -j说明：-j96 参数充分利用多核CPU优势，加快编译速度，参数-j后数字为CPU核数，可用“cat /proc/cpuinfo | grep processor | wc -l”进行查看，此数值应小于等于CPU核数。安装MySQL。make installls /usr/local/mysql/查看数据库版本。/usr/local/mysql/bin/mysql --version三、运行编译安装方式安装：软件安装目录默认为“/usr/local/mysql”1、修改配置文件编辑my.cnf文件。rm -f /etc/my.cnfecho -e "[mysqld_safe]\nlog-error=/data/mysql/log/mysql.log\npid-file=/data/mysql/run/mysqld.pid\n[mysqldump]\nquick\n[mysql]\nno-auto-rehash\n[client]\ndefault-character-set=utf8\n[mysqld]\nbasedir=/usr/local/mysql\nsocket=/data/mysql/run/mysql.sock\ntmpdir=/data/mysql/tmp\ndatadir=/data/mysql/data\ndefault_authentication_plugin=mysql_native_password\nport=3306\nuser=mysql\n" > /etc/my.cnf说明：其中文件路径（包括软件安装路径basedir、数据路径datadir等）根据实际情况修改。user=mysql是指操作系统层的用户，即创建用户组和用户中创建的用户。确保my.cnf配置文件修改正确。cat /etc/my.cnf修改配置文件/etc/my.cnf的用户组和用户权限为mysql:mysql。chown mysql:mysql /etc/my.cnfll /etc/my.cnf2、MySQL加入service服务。chmod 777 /usr/local/mysql/support-files/mysql.servercp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqlchkconfig mysql on修改/etc/init.d/mysql的用户组和用户权限为mysql:mysqlchown -R mysql:mysql /etc/init.d/mysqlll /etc/init.d/mysql3、配置环境变量。修改环境变量文件/etc/profile和/usr/local/mysql的用户组和用户权限为mysql:mysql。chown mysql:mysql /etc/profilell /etc/profilechown -R mysql:mysql /usr/local/mysqlll /usr/local/mysql切换到mysql用户。su - mysqlwhoami安装完成后，将MySQL二进制文件路径到PATH。echo export PATH=$PATH:/usr/local/mysql/bin >> /etc/profile注意：其中PATH中的“/usr/local/mysql/bin”路径，为MySQL软件安装目录下的bin文件的绝对路径，请根据实际情况修改。使环境变量配置生效。source /etc/profile查看环境变量。env4、初始化数据库。/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize说明（有报错在执行，正常不要执行此步操作）：以上步骤回显倒数第2行中有初始密码，请注意保存，后面会用到。如果初始化失败，提示“--initialize specified but the data directory has files in it.”则执行下面命令删除数据后重新初始化。ls /data/mysql/datarm -rf /data/mysql/data/初始化完成后，查看数据目录下数据文件/data/mysql/data的用户组和用户权限为mysql:mysql（因为前面/etc/my.cnf文件中配置的操作系统用户是user=mysql）。ll /data/mysql/data5、启动数据库(有3种方式)。启动数据库进程注意：如果以root用户（su - root）第一次启动数据库服务（service mysql start），则启动时会提示缺少mysql.log文件而导致失败。切换到mysql用户（su - mysql）启动数据库服务后，会在/data/mysql/log目录下生成mysql.log文件，停止数据库服务（service mysql stop），再次以root用户启动数据库服务则不会报错。如果采用的镜像站RPM方式安装或编译安装，执行一下三种中一种方式即可。service mysql start或者mysqld --defaults-file=/etc/my.cnf &或者/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &查看数据库进程。ps -ef | grep mysql查看数据库监测端口。netstat -anptnetstat -anpt | grep mysqlnetstat -anpt | grep 33066、登录数据库。说明：提示输入密码时，请输入上面初始化产生的初始密码。如果采用官网RPM安装方式，则mysql文件在/usr/bin目录下。登录数据库的命令根据实际情况修改。/usr/local/mysql/bin/mysql -uroot -p -S /data/mysql/run/mysql.sock配置数据库帐号密码。说明：登录数据库以后，修改通过root用户登录数据库的密码。alter user 'root'@'localhost' identified by "123456";创建全域root用户（允许root从其他服务器访问）。create user 'root'@'%' identified by '123456';进行授权。grant all privileges on *.* to 'root'@'%';flush privileges;退出数据库。执行\q或者exit退出数据库。exit用修改后的密码重新登录数据库。/usr/local/mysql/bin/mysql -uroot -p -S /data/mysql/run/mysql.sock退出数据库exit7、关闭数据库（可选）。service mysql stop查看数据库进程。ps -ef | grep mysql四、安装sysbench（可选）yum -y install unzip unzip automake libtool* mysql-develtar -zxvf sysbench-0.5.tar.gzcd sysbench-0.5./autogen.sh./configuremake -jmake install五、在mysql源码目录，打补丁（打补丁操作）1、编译安装卸载关闭数据库进程。ps -ef | grep mysql/usr/local/mysql/bin/mysqladmin -uroot -p123456 shutdown -S /data/mysql/run/mysql.sock源码编译安装只是生成对应的文件，不涉及卸载，直接删除对应的安装目录和数据目录即可ls /usr/local/mysqlrm -rf /usr/local/mysqlls /data/mysqlrm -rf /data/mysql2、重新执行步骤二的第三步，解压缩源码包后，把补丁传到源码包的根目录（/home/mysql-8.0.20）git apply --whitespace=nowarn -p2 < mtr-pq.patchgit apply --whitespace=nowarn -p2 < code-pq.patch编译完成运行截图：​

现在网上很多关于MySQL数据库安装教程新旧不一，并且由于centos系统版本不同，所用镜像源不同，很多解决方法已经不适用。这篇文章主要用于组内项目服务器开发过程展示总结，内容均为原创，转载请注明来源，文章中有纰漏之处还望斧正。当然如果能够帮助大家解决一些服务器搭建问题那就再好不过。1. MySQL安装过程中的常见问题MySQL依赖问题默认的rmp源不稳定下面我先给出MySQL安装的步骤及命令行代码，在遇到以上问题的时候我会给出解决方案。2. MySQL安装步骤依次执行下面三行代码:wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm yum -y install mysql57-community-release-el7-10.noarch.rpm yum -y install mysql-community-server --nogpgcheck centos7中默认安装有MariaDB，这个是MySQL的分支，但在安装完MySQL之后可以直接覆盖掉MariaDB。下面进行mysql的配置，执行以下命令，启动MySQL服务：systemctl start mysqld systemctl enable mysqld查看MySQL运行状态：systemctl status mysqld.service● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Tue 2022-05-17 17:19:25 CST; 1min 1s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 656 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 589 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 837 (mysqld) CGroup: /system.slice/mysqld.service └─837 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid May 17 17:19:22 hecs-340553 systemd[1]: Starting MySQL Server... May 17 17:19:25 hecs-340553 systemd[1]: Started MySQL Server.执行以下命令，获取安装MySQL时自动设置的root用户密码：grep 'temporary password' /var/log/mysqld.log如果回显信息中密码为空，则说明没有自动设置密码，如果有自动设置密码，需要复制在下一步中使用。执行以下命令，并按照回显提示信息进行操作，加固MySQL：mysql_secure_installationSecuring the MySQL server deployment. Enter password for user root: #输入上一步骤中获取的安装MySQL时自动设置的root用户密码 The existing password for the user account root has expired. Please set a new password. New password: #设置新的root用户密码 Re-enter new password: #再次输入密码 The 'validate_password' plugin is installed on the server. The subsequent steps will run with the existing configuration of the plugin. Using existing password for root. Estimated strength of the password: 100 Change the password for root ? ((Press y|Y for Yes, any other key for No) : N #是否更改root用户密码，输入N ... skipping. By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y #是否删除匿名用户，输入Y Success. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y #禁止root远程登录，输入Y Success. By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y #是否删除test库和对它的访问权限，输入Y - Dropping test database... Success. - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y #是否重新加载授权表，输入Y Success. All done!执行以下命令，再根据提示输入数据库管理员root账号的密码进入数据库：mysql -u root -p执行以下命令，使用MySQL数据库：use mysql;执行以下命令，查看用户列表：select host,user from user;执行以下命令，mysql默认不允许远程主机，%表示允许所有主机连接。刷新用户列表并允许所有IP对数据库进行访问,方面后续使用数据库软件进行管理：update user set host='%' where user='root' LIMIT 1;执行以下命令，强制刷新权限。允许同一子网中设置为允许访问的云服务器通过私有IP对MySQL数据库进行访问：flush privileges;执行以下命令，退出数据库：quit执行以下命令，重启MySQL服务systemctl start mysqld执行以下命令，设置开机自动启动MySQL服务:systemctl enable mysqld执行以下命令，关闭防火墙:systemctl stop firewalld.service重新查看防火墙状态是否为关闭:systemctl status firewalld● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: inactive (dead) Docs: man:firewalld(1)3.MySQL依赖问题出现依赖问题或版本冲突建议先将mysql相关文件全部删除，再重新进行mysql安装。yum remove mysql mysql-server mysql-libs mysql-server查找残留文件：rpm -qa | grep -i mysql将查询出来的文件逐个删除，这里需要用到删除命令，比如：yum remove mysql-community-common-5.7.29-1.el6.x86_64查找残留目录，如果有残留文件，再逐一删除（这样能将mysql文件删除干净，方面重新安装）：whereis mysql rm –rf /usr/lib64/mysql 检测系统是否存在mysql：yum list installed|grep mysql删除完毕后重新按照以上流程按照即可。4. 默认的rmp源不稳定，如何进行rmp源更新给CentOS添加rpm源，并且选择较新的源：wget dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm --no-check-certificate yum localinstall mysql-community-release-el6-5.noarch.rpm yum repolist all | grep mysql yum repolist enabled | grep mysql查看可获得的mysql版本，进行下载：yum list | grep mysql yum -y install mysql-community-server然后在根据流程进一步操作即可。

云耀服务器L实例搭建高可用mysql测评1. 华为云云耀服务器L实例介绍华为云云耀服务器L实例是一种高性能、高可靠性的云服务器实例，适用于大规模企业级应用、大数据分析等场景。它基于华为最新一代的硬件虚拟化技术，提供了更高的计算、存储和网络性能，同时保障了数据安全和隐私保护。华为云云耀服务器L实例拥有以下特点：高性能：采用华为自研的最新一代虚拟化技术，提高了计算、存储和网络性能，使得L实例可以轻松应对大规模企业级应用和大数据分析等场景的高性能需求。高可靠性：通过多重备份和快速恢复技术，保障了数据的安全性和可靠性。即使发生硬件故障或数据丢失，也能快速恢复业务，确保了业务的连续性。简单易用：提供了自动化运维和智能管理平台，使得部署和管理云服务器变得简单易用。用户只需通过简单的配置和命令行工具，即可完成部署和管理任务。灵活扩展：支持按需扩展资源，可根据业务需求自由调整计算、存储和网络资源，灵活应对业务增长和负载变化。安全可靠：严格遵守国内外安全标准和法律法规要求，保护用户数据的安全性和隐私。同时，提供了多种安全措施，包括访问控制、漏洞扫描等，保障了云服务器的安全可靠运行。2. 高可用环境介绍MySQL 高可用是一个针对 MySQL 数据库的解决方案，它提供了对于故障转移、负载均衡和数据复制等功能的管理和优化。这个方案可以解决因为硬件故障、系统崩溃或者其他不可预见的问题导致的 MySQL 数据库服务中断的问题，保证 MySQL 数据库服务的高可用性。MySQL 高可用性的实现方法有很多种，如主从复制、主主复制和中间件等。其中，主从复制是最常用的一种方法，它可以将 MySQL 数据在多个服务器上进行同步，以保证在主服务器出现故障时，可以从服务器可以无缝地接管主服务器的服务。主主复制则可以实现负载均衡，将多个 MySQL 数据库服务器的负载进行均衡分配，以提高 MySQL 数据库服务的可用性和性能。而中间件则可以将多个 MySQL 数据库服务器进行集成，以保证在任何一个服务器出现故障时，其他服务器可以接管该服务器的任务。高可用性（High Availability）是指系统在出现故障时，可以继续运行，不会中断或丢失数据，能够保持系统的连续性和稳定性。高可用环境是指为了实现高可用性而搭建的一种计算机系统环境，这种环境通常包括硬件、网络、存储、数据库、应用等方面的高可用性技术。以下是高可用环境的一些关键技术：负载均衡：通过负载均衡技术，将网络流量分发到多个服务器上，以提高系统的处理能力和响应速度。集群：通过将多台服务器组成一个集群，可以实现负载均衡和高可用性，当一台服务器出现故障时，其他服务器可以继续提供服务。存储备份：对于关键数据，需要进行备份并存储在可靠的数据中心或云存储服务上，以确保数据不会丢失。快速恢复：在系统出现故障时，需要快速恢复到正常运行状态。这通常需要采用一些快速恢复技术，如快速重启、自动备份恢复等。监控和告警：为了及时发现系统故障并采取相应的措施，需要建立监控和告警系统。这种系统可以实时监控系统的运行状态和性能指标，当出现异常情况时，会及时发出告警信息并通知管理员进行处理。3. mysql介绍MySQL是一种流行的开源关系型数据库管理系统（RDBMS），广泛应用于各种应用程序和网站。它最初由瑞典公司MySQL AB开发，后来被甲骨文公司（Oracle Corporation）收购。MySQL具有强大的性能和可靠性，可支持高并发访问、持久化存储和共享访问。它支持多种存储引擎，包括InnoDB、MyISAM、Memory等，可以满足不同的性能和可靠性需求。MySQL还提供了丰富的开发接口和工具，如PHP、Python、Java等，方便开发者进行应用程序的开发和集成。MySQL具有灵活的数据模型，支持关系型数据、非关系型数据和半结构化数据等多种数据类型，可以进行各种复杂的查询和数据处理操作。同时，MySQL也支持各种数据备份和恢复技术，以确保数据的一致性和安全性。MySQL还具有良好的可扩展性和可定制性，可以与其他技术进行集成，如Apache、Nginx、Drupal、WordPress等。同时，MySQL也支持各种云服务提供商，如Amazon RDS、Google Cloud SQL、Azure SQL Database等，方便用户选择适合自己的云服务方案。4. 华为云云备份CBR和主机安全HSS4.1 华为云云备份CBR云备份（Cloud Backup and Recovery, CBR）为云内的弹性云服务器（Elastic Cloud Server, ECS）、云耀云服务器（Hyper Elastic Cloud Server，HECS）、裸金属服务器（Bare Metal Server, BMS）（下文统称为服务器）、云硬盘（Elastic Volume Service, EVS）、SFS Turbo文件系统、云桌面（Workspace）、云下VMware虚拟化环境和本地文件目录，提供简单易用的备份服务，当发生病毒入侵、人为误删除、软硬件故障等事件时，可将数据恢复到任意备份点。产品架构云备份由备份、存储库和策略组成。备份备份即一个备份对象执行一次备份任务产生的备份数据，包括备份对象恢复所需要的全部数据。云备份产生的备份可以分为几种类型：云硬盘备份：云硬盘备份提供对云硬盘的基于快照技术的数据保护。云服务器备份：云服务器备份提供对弹性云服务器和裸金属服务器的基于多云硬盘一致性快照技术的数据保护。同时，未部署数据库等应用的服务器产生的备份为服务器备份，部署数据库等应用的服务器产生的备份为数据库服务器备份。SFS Turbo备份：SFS Turbo备份提供对SFS Turbo文件系统的数据保护。混合云备份：混合云备份提供对线下备份存储OceanStor Dorado阵列中的备份数据以及VMware服务器备份的数据保护。文件备份：文件备份提供对云上服务器或用户数据中心虚拟机中的单个或多个文件的数据保护，无需再以整机或整盘的形式进行备份。云桌面备份：云桌面备份提供对云桌面的数据保护。4.2 华为云主机安全HSS企业主机安全（Host Security Service，HSS）是以工作负载为中心的安全产品，集成了主机安全、容器安全和网页防篡改，旨在解决混合云、多云数据中心基础架构中服务器工作负载的独特保护要求。HSS不受地理位置影响，为主机、容器等提供统一的可视化和控制能力。HSS通过对主机、容器进行系统完整性的保护、应用程序控制、行为监控和基于主机的入侵防御等，保护工作负载免受攻击。5. 部署华为云云耀服务器L实例5.1 云耀服务器高可用L实例购买进入华为云官网: cid:link_0进入控制台搜索云耀服务器HECSg)选择登录L实例控制台如果没有应用实例，则可以选择购买资源云耀服务组合实例在购买阶段相对于传统的华为云ECS服务器购买十分简单便捷关于区域选择，可以按照下面规则选择合适的区域地理位置就近原则。根据用户群所在位置，应就近选择区域以减少网络时延，提高访问速度。不同区域价格差异。不同区域的服务器价格可能会有所不同，因此需考虑预算和成本效益。备案考虑。根据所在的行业和业务需求，有些区域可能需要特定的备案或审批手续，应该提前了解和考虑。多产品同区域内网互通。如果需要将多个华为云产品部署在同一区域内，以便实现内网互通，可以提高访问速度和数据传输效率。本次我选择的是Centos7.8版本套餐规格选择高可用套餐关于实例规格选择，这要根据大家的实际业务需求和预算进行综合考虑高可用服务器具有以下优势：数据安全：普通的IDC机房或服务器厂商，利用云服务商部署高性能云服务器能够大幅度降低黑客攻击或DDOS攻击的风险。用户不必担忧技术规格，云服务器服务商应用市面上最好的SSD和CPU芯片来保持低ping率和高计算能力。可靠性：云服务器ECS使用更严格的IDC标准、服务器准入标准以及运维标准，以保证云计算整个基础框架的高可用性、数据的可靠性以及云服务器的高可用性。每个地域都存在多可用区，当需要更高的可用性时，可以利用云的多可用区搭建自己的主备服务或者双活服务。弹性：大部分云服务商提供的云产品都有一些价钱等级，在技术规格和相关服务层面普遍存在差别。确认配置后需要支付费用付款后返回控制台查看创建情况等待创建完成5.2 云耀服务器高可用L实例初始化配置等云耀服务器高可用L实例创建完成，点击进入详细页面可以看到高可用环境的网络拓扑图云耀组合服务共包含两台相同规格服务器，云备份CBR，主机安全HSS6. mysql高可用部署mysql编译安装可以根据需要设定参数，按照需求进行定制安装，并且安装的版本可以根据项目需要灵活选择，整体可配置弹性大。其中YUM二进制方式部署配置简单，可自动解决软件包之间依赖关系问题，但YUM不能自定义软件模块和功能，不能自定义软件部署路径，增加后期维护成本。此方案提供了一键安装部署脚本，使脚本安装省去繁琐步骤，只需输入变量即可完成安装任务，也可作为资源池机器上架初始化的参考。6.1 MySQL源码编译安装6.1.1创建相关目录# 创建用户 useradd -s /sbin/nologin mysql # 创建安装目录并进入 cd /usr/local mkdir mysql cd mysql # 创建数据存放目录 mkdir data6.1.2. 下载依赖库yum -y install initscripts wget libaio ncurses ncurses-devel bison gcc gcc-c++ openssl openssl-devel6.1.3. 升级gcc和g++yum install -y centos-release-scl-rh yum install -y centos-release-scl # 安装gcc7 yum install devtoolset-7-gcc.x86_64 yum install devtoolset-7-gcc-c++.x86_64 # 启用 scl enable devtoolset-7 bash # 查看版本 gcc --version g++ --version # 防止失效方法1：修改软连接（推荐） mv /usr/bin/gcc /usr/bin/gcc4.8.5 ln -s /opt/rh/devtoolset-7/root/usr/bin/gcc /usr/bin/gcc mv /usr/bin/g++ /usr/bin/g++4.8.5 ln -s /opt/rh/devtoolset-7/root/usr/bin/g++ /usr/bin/g++ mv /usr/bin/cc /usr/bin/cc4.8.5 ln -s /opt/rh/devtoolset-7/root/usr/bin/cc /usr/bin/cc mv /usr/bin/c++ /usr/bin/c++4.8.5 ln -s /opt/rh/devtoolset-7/root/usr/bin/c++ /usr/bin/c++ # 防止失效方法2：修改环境变量 echo "source /opt/rh/devtoolset-7/enable" >>/etc/profile6.1.4. 安装最新版cmake# 安装目录：/opt cd /opt # 下载 wget -c https://github.com/Kitware/CMake/releases/download/v3.20.2/cmake-3.20.2.tar.gz # 解压 tar zxvf cmake-3.20.2.tar.gz # 进入解压目录 cd cmake-3.20.2 # 构建 ./bootstrap # 编译 gmake # 安装 gmake install # 链接 目的是添加到环境变量中 ln -s /opt/bin/cmake /usr/bin/cmake6.1.5. 下载MySQL源码并解压cd /usr/local/mysql 37.tar.gz tar -zxvf mysql-boost-5.7.37.tar.gz6.1.6. 构建、编译、安装cd /usr/local/mysql/mysql-5.7.37 cmake -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_BOOST=boost make && make install6.1.7. 更改配置文件6.1.7.1. 打开配置文件vim /etc/my.cnf6.1.7.2. 写入如下内容注意：将原有内容全部清除[client] port = 3306 socket = /tmp/mysql.sock [mysqld] user = mysql basedir = /usr/local/mysql datadir = /usr/local/mysql/data port = 3306 pid-file = /usr/local/mysql/data/mysql.pid socket = /tmp/mysql.sock log_error = /usr/local/mysql/data/mysql-error.log slow_query_log = 1 long_query_time = 1 slow_query_log_file = /usr/local/mysql/data/mysql-slow.log6.1.8. 修改用户权限cd /usr/local/ chown -R mysql:mysql /usr/local/mysql chown -R mysql:mysql /usr/local/mysql/data6.1.9. 初始化MySQLcd /usr/local/mysql/bin ./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data6.1.10. 复制相关文件cd /usr/local/mysql/support-files cp mysql.server /etc/init.d/mysql6.1.11. 配置环境变量6.1.11.1. 打开环境变量文件vim /etc/profile6.1.11.2. 修改PATH属性，并保存退出export MYSQL_HOME=/usr/local/mysql/ export PATH=${MYSQL_HOME}/bin:${MYSQL_HOME}/lib:$PATH6.1.11.3. 使环境变量生效source /etc/profile6.1.12. 在vscode中进行构建按下Ctrl + ,，选择workspace，在左侧选择extension，再选中Cmake，找到Configuration Args，添加以下参数：-DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/mysql/mysql-5.7.37/boost/boost_1_59_0/随后用vscode打开文件夹/usr/lcoal/mysql/mysql-5.7.37，并点击底栏中的⚙build即可构建6.1.13. 调试配置在.vscode文件夹中创建launch.json文件，内容如下：{ "version": "0.2.0", "configurations": [ { "name": "MySQL-debug", "type": "cppdbg", "request": "launch", "program": "/usr/local/mysql/bin/mysqld", "args": ["--user=mysql --datadir=/usr/local/mysql/data"], "stopAtEntry": true, "environment": [], "externalConsole": false, "MIMode": "gdb", "miDebuggerPath": "gdb", "miDebuggerArgs": "gdb", "linux": { "MIMode": "gdb", "miDebuggerPath": "/usr/bin/gdb" }, "logging": { "moduleLoad": false, "engineLogging": false, "trace": false }, "setupCommands": [ { "description": "Enable pretty-printing for gdb", "text": "-enable-pretty-printing", "ignoreFailures": true } ], "cwd": "${workspaceFolder}", } ] }参考断点位置：文件sql_parser.cc第5438行：SQL语句的处理入口文件item.cc第7522行：数据类型的解析入口6.1.14. 虚拟机开放端口14.1. 查看3306端口状态firewall-cmd --zone=public --query-port=3306/tcp如果输出no，则需要开放端口14.2. 开放端口firewall-cmd --zone=public --add-port=3306/tcp --permanent 14.3. 防火墙重载firewall-cmd --reload6.1.15. 修改连接权限use mysql; update user set Host='%' where User='root'; flush privileges;至此，mysql高可用环境搭建完成7. 总结本文介绍了华为云云耀服务器L实例的特点和部署方法，包括快速恢复、监控和告警、数据安全、可靠性和弹性等优势。同时也介绍了mysql高可用部署的过程。文章指出，使用云耀服务器L实例可以在云端提供高可用性和高性能的计算服务，快速恢复和告警功能能够及时处理系统故障并保证业务的连续性，数据安全和可靠性让用户不必担忧数据安全和丢失等问题，弹性能够根据业务需求灵活扩展资源。华为云云耀服务器L实例适用于需要高可用性、连续性和共享访问的在线应用、数据分析仓库等场景

现在网上很多关于MySQL数据库安装教程新旧不一，并且由于centos系统版本不同，所用镜像源不同，很多解决方法已经不适用。这篇文章主要用于组内项目服务器开发过程展示总结，内容均为原创，转载请注明来源，文章中有纰漏之处还望斧正。当然如果能够帮助大家解决一些服务器搭建问题那就再好不过。1. MySQL安装过程中的常见问题MySQL依赖问题默认的rmp源不稳定下面我先给出MySQL安装的步骤及命令行代码，在遇到以上问题的时候我会给出解决方案。2. MySQL安装步骤依次执行下面三行代码:wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm yum -y install mysql57-community-release-el7-10.noarch.rpm yum -y install mysql-community-server --nogpgcheck centos7中默认安装有MariaDB，这个是MySQL的分支，但在安装完MySQL之后可以直接覆盖掉MariaDB。下面进行mysql的配置，执行以下命令，启动MySQL服务：systemctl start mysqld systemctl enable mysqld查看MySQL运行状态：systemctl status mysqld.service● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Tue 2022-05-17 17:19:25 CST; 1min 1s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 656 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 589 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 837 (mysqld) CGroup: /system.slice/mysqld.service └─837 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid May 17 17:19:22 hecs-340553 systemd[1]: Starting MySQL Server... May 17 17:19:25 hecs-340553 systemd[1]: Started MySQL Server.执行以下命令，获取安装MySQL时自动设置的root用户密码：grep 'temporary password' /var/log/mysqld.log如果回显信息中密码为空，则说明没有自动设置密码，如果有自动设置密码，需要复制在下一步中使用。执行以下命令，并按照回显提示信息进行操作，加固MySQL：mysql_secure_installationSecuring the MySQL server deployment. Enter password for user root: #输入上一步骤中获取的安装MySQL时自动设置的root用户密码 The existing password for the user account root has expired. Please set a new password. New password: #设置新的root用户密码 Re-enter new password: #再次输入密码 The 'validate_password' plugin is installed on the server. The subsequent steps will run with the existing configuration of the plugin. Using existing password for root. Estimated strength of the password: 100 Change the password for root ? ((Press y|Y for Yes, any other key for No) : N #是否更改root用户密码，输入N ... skipping. By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y #是否删除匿名用户，输入Y Success. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y #禁止root远程登录，输入Y Success. By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y #是否删除test库和对它的访问权限，输入Y - Dropping test database... Success. - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y #是否重新加载授权表，输入Y Success. All done!执行以下命令，再根据提示输入数据库管理员root账号的密码进入数据库：mysql -u root -p执行以下命令，使用MySQL数据库：use mysql;执行以下命令，查看用户列表：select host,user from user;执行以下命令，mysql默认不允许远程主机，%表示允许所有主机连接。刷新用户列表并允许所有IP对数据库进行访问,方面后续使用数据库软件进行管理：update user set host='%' where user='root' LIMIT 1;执行以下命令，强制刷新权限。允许同一子网中设置为允许访问的云服务器通过私有IP对MySQL数据库进行访问：flush privileges;执行以下命令，退出数据库：quit执行以下命令，重启MySQL服务systemctl start mysqld执行以下命令，设置开机自动启动MySQL服务:systemctl enable mysqld执行以下命令，关闭防火墙:systemctl stop firewalld.service重新查看防火墙状态是否为关闭:systemctl status firewalld● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: inactive (dead) Docs: man:firewalld(1)3.MySQL依赖问题出现依赖问题或版本冲突建议先将mysql相关文件全部删除，再重新进行mysql安装。yum remove mysql mysql-server mysql-libs mysql-server查找残留文件：rpm -qa | grep -i mysql将查询出来的文件逐个删除，这里需要用到删除命令，比如：yum remove mysql-community-common-5.7.29-1.el6.x86_64查找残留目录，如果有残留文件，再逐一删除（这样能将mysql文件删除干净，方面重新安装）：whereis mysql rm –rf /usr/lib64/mysql 检测系统是否存在mysql：yum list installed|grep mysql删除完毕后重新按照以上流程按照即可。4. 默认的rmp源不稳定，如何进行rmp源更新给CentOS添加rpm源，并且选择较新的源：wget dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm --no-check-certificate yum localinstall mysql-community-release-el6-5.noarch.rpm yum repolist all | grep mysql yum repolist enabled | grep mysql查看可获得的mysql版本，进行下载：yum list | grep mysql yum -y install mysql-community-server然后在根据流程进一步操作即可。

MySQL存储过程中，定义变量有两种方式：1.使用set或select直接赋值，变量名以 @ 开头.例如:set @var=1;可以在一个会话的任何地方声明，作用域是整个会话，称为用户变量。2.以 DECLARE 关键字声明的变量，只能在存储过程中使用，称为存储过程变量，例如：DECLARE var1 INT DEFAULT 0;主要用在存储过程中，或者是给存储传参数中。两者的区别是：在调用存储过程时，以DECLARE声明的变量都会被初始化为 NULL。而会话变量（即@开头的变量）则不会被再初始化，在一个会话内，只须初始化一次，之后在会话内都是对上一次计算的结果，就相当于在是这个会话内的全局变量。主体内容1、局部变量2、用户变量3、会话变量4、全局变量会话变量和全局变量叫系统变量。一、局部变量。只在当前begin/end代码块中有效局部变量一般用在sql语句块中，比如存储过程的begin/end。其作用域仅限于该语句块，在该语句块执行完毕后，局部变量就消失了。declare语句专门用于定义局部变量，可以使用default来说明默认值。set语句是设置不同类型的变量，包括会话变量和全局变量。局部变量定义语法形式DECLARE var_name [, var_name]... data_type [ DEFAULT value ];例如在begin/end语句块中添加如下一段语句，接受函数传进来的a/b变量然后相加，通过set语句赋值给c变量。set语句语法形式SET var_name=expr [, var_name=expr]…; set语句既可以用于局部变量的赋值，也可以用于用户变量的申明并赋值。DECLARE c int DEFAULT 0;SET c=a+b;SELECT c AS C;或者用select …. into…形式赋值select into 语句句式：SELECT col_name[,...] INTO var_name[,...] table_expr [WHERE...];例子DECLARE v_employee_name VARCHAR(100);DECLARE v_employee_salary DECIMAL(8,4);SELECT employee_name, employee_salaryINTO v_employee_name, v_employee_salaryFROM employeesWHERE employee_id=1;二、用户变量 在客户端链接到数据库实例整个过程中用户变量都是有效的。http://www.cnblogs.com/qixuejia/archive/2010/12/21/1913203.htmlmysql中用户变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。第一种用法：set @num=1; 或set @num:=1; //这里要使用set语句创建并初始化变量，直接使用@num变量第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……，select语句一般用来输出用户变量，比如select @变量名,用于输出数据源不是表格的数据。注意上面两种赋值符号，使用set时可以用“=”或“：=”，但是使用select时必须用“：=赋值”http://blog.163.com/longsu2010@yeah/blog/static/173612348201162595425697/用户变量与数据库连接有关，在连接中声明的变量，在存储过程中创建了用户变量后一直到数据库实例接断开的时候，变量就会消失。在此连接中声明的变量无法在另一连接中使用。用户变量的变量名的形式为@varname的形式。名字必须以@开头。声明变量的时候需要使用set语句，比如下面的语句声明了一个名为@a的变量。set @a = 1;声明一个名为@a的变量，并将它赋值为1，mysql里面的变量是不严格限制数据类型的，它的数据类型根据你赋给它的值而随时变化 。（SQL SERVER中使用declare语句声明变量，且严格限制数据类型。）　　我们还可以使用select 语句为变量赋值 。　　比如：set @name = '';select @name:=password from user limit 0,1;#从数据表中获取一条记录password字段的值给@name变量。在执行后输出到查询结果集上面。（注意等于号前面有一个冒号，后面的limit 0,1是用来限制返回结果的，表示可以是0或1个。相当于SQL SERVER里面的top 1）如果直接写：select @name:=password from user;如果这个查询返回多个值的话，那@name变量的值就是最后一条记录的password字段的值 。　　用户变量可以作用于当前整个连接，但当当前连接断开后，其所定义的用户变量都会消失。用户变量使用如下(我们无须使用declare关键字对用户变量进行定义，可以直接这样使用) 定义，变量名必须以@开始:#定义select @变量名 或者 select @变量名:= 字段名 from 表名 where 过滤语句;set @变量名;#赋值 @num为变量名，value为值set @num=value; 或 select @num:=value;对用户变量赋值有两种方式，一种是直接用”=”号，另一种是用”:=”号。其区别在于使用set命令对用户变量进行赋值时，两种方式都可以使用；当使用select语句对用户变量进行赋值时，只能使用”:=”方式，因为在select语句中，”=”号declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量。例如BEGIN#Routine body goes here...#SELECT c AS c;DECLARE c int DEFAULT 0;SET @var1=143; #定义一个用户变量，并初始化为143SET @var2=34;SET c=a+b;SET @d=c;SELECT @sum:=(@var1+@var2) AS sum, @dif:=(@var1-@var2) AS dif, @d AS C;#使用用户变量。@var1表示变量名SET c=100;SELECT c AS CA;END在查询中执行下面语句段CALL `order`(12,13); #执行上面定义的存储过程SELECT @var1; #看定义的用户变量在存储过程执行完后，是否还可以输出，结果是可以输出用户变量@var1,@var2两个变量的。SELECT @var2;在执行完order存储过程后，在存储过程中新建的var1，var2用户变量还是可以用select语句输出的，但是存储过程里面定义的局部变量c不能识别。http://blog.163.com/longsu2010@yeah/blog/static/173612348201162595425697/系统变量：系统变量又分为全局变量与会话变量。全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。（也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。）全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。我们可以利用SHOW SESSION VARIABLES;语句将所有的会话变量输出：（可以简写为show variables，没有指定是输出全局变量还是会话变量的话，默认就输出会话变量。）如果想输出所有全局变量：SHOW GLOBAL VARIABLES有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的。对于那些可以更改的系统变量，我们可以利用set语句进行更改。系统变量在变量名前面有两个@；　　如果想要更改会话变量的值，利用语句：set session varname = value;或者set @@session.varname = value;比如：mysql> set session sort_buffer_size = 40000;Query OK, 0 rows affected(0.00 sec)用select @@sort_buffer_size;输出看更改后的值是什么。如果想要更改全局变量的值，将session改成global：set global sort_buffer_size = 40000;set @@global.sort_buffer_size = 40000;不过要想更改全局变量的值，需要拥有SUPER权限 。（注意，ROOT只是一个内置的账号，而不是一种权限 ，这个账号拥有了MYSQL数据库里的所有权限。任何账号只要它拥有了名为SUPER的这个权限，就可以更改全局变量的值，正如任何用户只要拥有FILE权限就可以调用load_file或者into outfile ,into dumpfile,load data infile一样。）利用select语句我们可以查询单个会话变量或者全局变量的值：select @@session.sort_buffer_sizeselect @@global.sort_buffer_sizeselect @@global.tmpdir凡是上面提到的session，都可以用local这个关键字来代替。比如：select @@local.sort_buffer_sizelocal 是 session的近义词。无论是在设置系统变量还是查询系统变量值的时候，只要没有指定到底是全局变量还是会话变量。都当做会话变量来处理。　　比如：　　set @@sort_buffer_size = 50000;　　select @@sort_buffer_size;　　上面都没有指定是GLOBAL还是SESSION，所以全部当做SESSION处理。三、会话变量服务器为每个连接的客户端维护一系列会话变量。在客户端连接数据库实例时，使用相应全局变量的当前值对客户端的会话变量进行初始化。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量，而不能更改其它客户端的会话变量。会话变量的作用域与用户变量一样，仅限于当前连接。当当前连接断开后，其设置的所有会话变量均失效。设置会话变量有如下三种方式更改会话变量的值：set session var_name = value;set @@session.var_name = value;set var_name = value; #缺省session关键字默认认为是session查看所有的会话变量SHOW SESSION VARIABLES;查看一个会话变量也有如下三种方式：select @@var_name;select @@session.var_name;show session variables like "%var%";凡是上面提到的session，都可以用local这个关键字来代替。比如：　　select @@local.sort_buffer_size　　local 是 session的近义词。四、全局变量全局变量影响服务器整体操作。当服务器启动时，它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。要想更改全局变量，必须具有SUPER权限。全局变量作用于server的整个生命周期，但是不能跨重启。即重启后所有设置的全局变量均失效。要想让全局变量重启后继续生效，需要更改相应的配置文件。要设置一个全局变量，有如下两种方式：set global var_name = value; //注意：此处的global不能省略。根据手册，set命令设置变量时若不指定GLOBAL、SESSION或者LOCAL，默认使用SESSIONset @@global.var_name = value; //同上查看所有的全局变量show global variables;要想查看一个全局变量，有如下两种方式：select @@global.var_name;show global variables like “%var%”;

一、in关键字确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。select * from A where id in (select id from B)#等价于for select id from B：先执行；子查询 for select id from A where A.id = B.id：再执行外面的查询；执行过程：in是先查询内表【select id from B】，再把内表结果与外表【select * from A where id in …】匹配，对外表使用索引，而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。 小总结：当A表的数据集大于B表的数据集时，用in优于exists。【in适合外部表数据大于子查询的表数据的业务场景】二、exists关键字 指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。语法格式：select ... from table where exists (subquery)；可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或者 FALSE）来决定主查询数据结果是否得到保留。如下:select * from A where exists (select 1 from B where B.id = A.id)#等价于for select id from A：先执行外层的查询；for select id from B where B.id = A.id：再执行子查询；执行过程：exists是对外表【select * from A where exists …】做loop循环，每次loop循环再对内表（子查询）【select 1 from B where B.id = A.id】进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（所以尽量用小表），故内表大的使用exists，可加快效率。例如：select * from A where exists (select 1 from B where B.id = A.id)提示1. T 清单， 因此没有区别；EXISTS (subquery) 只返回 True 或 False ， 因此查询的 SELET * 也可以是SELET 1 或其他，官方说法是执行时会忽略SELEC2. EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解的逐条比对，如果担忧效率问题，可以进行实际检验以确定是否有效率问题；3. EXISTS 子查询往往也可以使用条件表达式、其他子查询或者 JOIN 来代替，何种最优化需要具体分析；小总结：当A表的数据集小于B表的数据集时，用exists优于in。【exist适合子查询中表数据大于外查询表中数据的业务场景】三、in 与 exists 的区别1、exists、not exists 一般都是与子查询一起使用，In 可以与子查询一起使用，也可以直接in (a,b.....)2、exists 会针对子查询的表使用索引，not exists 会对主子查询都会使用索引。in 与子查询一起使用的时候，只能针对主查询使用索引，not in 则不会使用任何索引。　　注意：一直以来认为 exists 比 in 效率高的说法是不准确的。　　in 是把外表和内表作 hash 连接，而 exists 是对外表作 loop 循环，每次 loop 循环再对内表进行查询。　　如果查询的两个表大小相当，那么用 in 和 exists 差别不大。　　如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：四、总结select * from A where id in (select id from B)select * from A where exists (select 1 from B where B.id = A.id)1、如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in；反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。　　其实我们区分 in 和 exists 主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。(都是以小表驱动大表);2、in 是把外表和内表作 hash 连接，而 exists 是对外表作 loop 循环，每次 loop 循环再对内表进行查询。一直以来认为 exists 比 in 效率高的说法是不准确的。3、如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

文章目录            优化器概述            逻辑转换            基于成本的优化                控制优化程度                设置成本常量                数据字典与统计信息            控制优化行为            优化器和索引提示            总结大家好，我是只谈技术不剪发的 Tony 老师。我们在 MySQL 体系结构中介绍了 MySQL 的服务器逻辑结构，其中查询优化器（optimizer）负责生成 SQL 语句的执行计划，是决定查询性能的一个关键组件。本文将会深入分析 MySQL 优化器工作的原理以及如何控制优化器来实现 SQL 语句的优化。优化器概述MySQL 优化器使用基于成本的优化方式（Cost-based Optimization），以 SQL 语句作为输入，利用内置的成本模型和数据字典信息以及存储引擎的统计信息决定使用哪些步骤实现查询语句，也就是查询计划。optimizer查询优化和地图导航的概念非常相似，我们通常只需要输入想要的结果（目的地），优化器负责找到最有效的实现方式（最佳路线）。需要注意的是，导航并不一定总是返回最快的路线，因为系统获得的交通数据并不可能是绝对准确的；与此类似，优化器也是基于特定模型、各种配置和统计信息进行选择，因此也不可能总是获得最佳执行方式。从高层次来说，MySQL Server 可以分为两部分：服务器层以及存储引擎层。其中，优化器工作在服务器层，位于存储引擎 API 之上。优化器的工作过程从语义上可以分为四个阶段：    逻辑转换，包括否定消除、等值传递和常量传递、常量表达式求值、外连接转换为内连接、子查询转换、视图合并等；    优化准备，例如索引 ref 和 range 访问方法分析、查询条件扇出值（fan out，过滤后的记录数）分析、常量表检测；    基于成本优化，包括访问方法和连接顺序的选择等；    执行计划改进，例如表条件下推、访问方法调整、排序避免以及索引条件下推。逻辑转换MySQL 优化器首先可能会以不影响结果的方式对查询进行转换，转换的目标是尝试消除某些操作从而更快地执行查询。例如（数据来源）：mysql> explain    -> select *    -> from employee    -> where salary > 10000 and 1=1;+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | employee | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 |    33.33 | Using where |+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> show warnings\G*************************** 1. row ***************************  Level: Note   Code: 1003Message: /* select#1 */ select `hrdb`.`employee`.`emp_id` AS `emp_id`,`hrdb`.`employee`.`emp_name` AS `emp_name`,`hrdb`.`employee`.`sex` AS `sex`,`hrdb`.`employee`.`dept_id` AS `dept_id`,`hrdb`.`employee`.`manager` AS `manager`,`hrdb`.`employee`.`hire_date` AS `hire_date`,`hrdb`.`employee`.`job_id` AS `job_id`,`hrdb`.`employee`.`salary` AS `salary`,`hrdb`.`employee`.`bonus` AS `bonus`,`hrdb`.`employee`.`email` AS `email` from `hrdb`.`employee` where (`hrdb`.`employee`.`salary` > 10000.00)1 row in set (0.00 sec)    1    2    3    4    5    6    7    8    9    10    11    12    13    14    15    16    17显然，查询条件中的 1=1 是完全多余的。没有必要为每一行数据都执行一次计算；删除这个条件也不会影响最终的结果。执行EXPLAIN语句之后，通过SHOW WARNINGS命令可以查看逻辑转换之后的 SQL 语句，从上面的结果可以看出 1=1 已经不存在了。    📝关于 MySQL 执行计划和 EXPLAIN 语句的详细介绍可以参考这篇文章。我们也可以通过优化器跟踪进一步了解优化器的执行过程，例如：mysql> SET optimizer_trace="enabled=on";Query OK, 0 rows affected (0.03 sec)mysql> select * from employee where emp_id = 1 and dept_id = emp_id;+--------+----------+-----+---------+---------+------------+--------+----------+----------+-------------------+| emp_id | emp_name | sex | dept_id | manager | hire_date  | job_id | salary   | bonus    | email             |+--------+----------+-----+---------+---------+------------+--------+----------+----------+-------------------+|      1 | 刘备     | 男  |       1 |    NULL | 2000-01-01 |      1 | 30000.00 | 10000.00 | liubei@shuguo.com |+--------+----------+-----+---------+---------+------------+--------+----------+----------+-------------------+1 row in set (0.00 sec)mysql> select * from information_schema.optimizer_trace\G*************************** 1. row ***************************                            QUERY: select * from employee where emp_id = 1 and dept_id = emp_id                            TRACE: {  "steps": [    {      "join_preparation": {        "select#": 1,        "steps": [          {            "expanded_query": "/* select#1 */ select `employee`.`emp_id` AS `emp_id`,`employee`.`emp_name` AS `emp_name`,`employee`.`sex` AS `sex`,`employee`.`dept_id` AS `dept_id`,`employee`.`manager` AS `manager`,`employee`.`hire_date` AS `hire_date`,`employee`.`job_id` AS `job_id`,`employee`.`salary` AS `salary`,`employee`.`bonus` AS `bonus`,`employee`.`email` AS `email` from `employee` where ((`employee`.`emp_id` = 1) and (`employee`.`dept_id` = `employee`.`emp_id`))"          }        ]      }    },    {      "join_optimization": {        "select#": 1,        "steps": [          {            "condition_processing": {              "condition": "WHERE",              "original_condition": "((`employee`.`emp_id` = 1) and (`employee`.`dept_id` = `employee`.`emp_id`))",              "steps": [                {                  "transformation": "equality_propagation",                  "resulting_condition": "(multiple equal(1, `employee`.`emp_id`, `employee`.`dept_id`))"                },                {                  "transformation": "constant_propagation",                  "resulting_condition": "(multiple equal(1, `employee`.`emp_id`, `employee`.`dept_id`))"                },                {                  "transformation": "trivial_condition_removal",                  "resulting_condition": "multiple equal(1, `employee`.`emp_id`, `employee`.`dept_id`)"                }              ]            }          },          ...        ]      }    },    {      "join_execution": {        "select#": 1,        "steps": [        ]      }    }  ]}MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0          INSUFFICIENT_PRIVILEGES: 01 row in set (0.00 sec)    1    2    3    4    5    6    7    8    9    10    11    12    13    14    15    16    17    18    19    20    21    22    23    24    25    26    27    28    29    30    31    32    33    34    35    36    37    38    39    40    41    42    43    44    45    46    47    48    49    50    51    52    53    54    55    56    57    58    59    60    61    62    63    64    65    66优化器跟踪输出主要包含了三个部分：    join_preparation，准备阶段，返回了字段名扩展之后的 SQL 语句。对于 1=1 这种多余的条件，也会在这个步骤被删除；    join_optimization，优化阶段。其中 condition_processing 中包含了各种逻辑转换，经过等值传递（equality_propagation）之后将条件 dept_id = emp_id 转换为了 dept_id = 1。另外 constant_propagation 表示常量传递，trivial_condition_removal 表示无效条件移除    join_execution，执行阶段。优化器跟踪还可以显示其他基于成本优化的过程，后续我们还会使用该功能。关闭优化器跟踪功能的方式如下：SET optimizer_trace="enabled=off";    1下表列出了一些逻辑转换的示例：原始语句    重写形式    备注select *from employeewhere emp_id = 1;    /* select#1 */ select ‘1’ AS `emp_id`,‘刘备’ AS `emp_name`,‘男’ AS `sex`,‘1’ AS `dept_id`,NULL AS `manager`,‘2000-01-01’ AS `hire_date`,‘1’ AS `job_id`,‘30000.00’ AS `salary`,‘10000.00’ AS `bonus`,‘liubei@shuguo.com’ AS `email` from `hrdb`.`employee` where true    通过主键或唯一索引进行等值查找时，在选择执行计划之前就完成了转换，重写为查询常量。select *from employeewhere emp_id = 0;    /* select#1 */ select NULL AS `emp_id`,NULL AS `emp_name`,NULL AS `sex`,NULL AS `dept_id`,NULL AS `manager`,NULL AS `hire_date`,NULL AS `job_id`,NULL AS `salary`,NULL AS `bonus`,NULL AS `email` from `hrdb`.`employee` where multiple equal(0, NULL)    通过主键或唯一索引查找不存在的值。select emp_name from employee e,(select * from department where dept_name =‘研发部’) as dwhere d.dept_id = e.dept_id and e.salary > 10000;    /* select#1 */ select `hrdb`.`e`.`emp_name` AS `emp_name` from `hrdb`.`employee` `e` join `hrdb`.`department` where ((`hrdb`.`e`.`dept_id` = `hrdb`.`department`.`dept_id`) and (`hrdb`.`e`.`salary` > 10000.00) and (`hrdb`.`department`.`dept_name` = ‘研发部’))    派生表子查询转换为连接查询基于成本的优化MySQL 优化器采用基于成本的优化方式，简化的步骤如下：    为每个操作指定一个成本；    计算每个可能的执行计划各个步骤的成本总和；    选择总成本最小的执行计划。为了找到最佳执行计划，优化器需要比较不同的查询方案。随着查询中表的数量增加，可能的执行计划会呈现指数级增长；因为每个表都可能使用全表扫描或者不同的索引访问方法，连接查询可能使用任意顺序。对于少量表的连接查询（通常少于 7 到 10 个）可能不会产生问题，但是更多的表可能会导致查询优化的时间比执行时间还要长。所以优化器不可能遍历所有的执行方案，一种更灵活的优化方法是允许用户控制优化器在查找最佳查询计划时的遍历程度。一般来说，优化器评估的计划越少，则编译查询所花费的时间就越少；但另一方面，由于优化器忽略了一些计划，因此可能找到的不是最佳计划。控制优化程度MySQL 提供了两个系统变量，可以用于控制优化器的优化程度：    optimizer_prune_level， 基于返回行数的评估忽略某些执行计划，这种启发式的方法可以极大地减少优化时间而且很少丢失最佳计划。因此，该参数的默认设置为 1；如果确认优化器错过了最佳计划，可以将该参数设置为 0，不过这样可能导致优化时间的增加。    optimizer_search_depth，优化器查找的深度。如果该参数大于查询中表的数量，可以得到更好的执行计划，但是优化时间更长；如果小于表的数量，可以更快完成优化，但可能获得的不是最优计划。例如，对于 12、13 个或者更多表的连接查询，如果将该参数设置为表的个数，可能需要几小时或者几天时间才能完成优化；如果将该参数修改为 3 或者 4，优化时间可能少于 1 分钟。该参数的默认值为 62；如果不确定是否合适，可以将其设置为 0，让优化器自动决定搜索的深度。设置成本常量MySQL 优化器计算的成本主要包括 I/O 成本和 CPU 成本，每个步骤的成本由内置的“成本常量”进行估计。另外，这些成本常量可以通过 mysql 系统数据库中的 server_cost 和 engine_cost 两个表进行查询和设置。server_cost 中存储的是常规服务器操作的成本估计值：select * from mysql.server_cost;cost_name                   |cost_value|last_update        |comment|default_value|----------------------------|----------|-------------------|-------|-------------|disk_temptable_create_cost  |          |2018-05-17 10:12:12|       |         20.0|disk_temptable_row_cost     |          |2018-05-17 10:12:12|       |          0.5|key_compare_cost            |          |2018-05-17 10:12:12|       |         0.05|memory_temptable_create_cost|          |2018-05-17 10:12:12|       |          1.0|memory_temptable_row_cost   |          |2018-05-17 10:12:12|       |          0.1|row_evaluate_cost           |          |2018-05-17 10:12:12|       |          0.1|    1    2    3    4    5    6    7    8    9cost_value 为空表示使用 default_value。其中，    disk_temptable_create_cost 和 disk_temptable_row_cost 代表了在基于磁盘的存储引擎（InnoDB 或 MyISAM）中使用内部临时表的评估成本。增加这些值会使得优化器倾向于较少使用内部临时表的查询计划。    key_compare_cost 代表了比较记录键的评估成本。增加该值将导致需要比较多个键值的查询计划变得更加昂贵。例如，执行 filesort 排序的查询计划比通过索引避免排序的查询计划相对更加昂贵。    memory_temptable_create_cost 和 memory_temptable_row_cost 代表了在 MEMORY 存储引擎中使用内部临时表的评估成本。增加这些值会使得优化器倾向于较少使用内部临时表的查询计划。    row_evaluate_cost 代表了计算记录条件的评估成本。增加该值会导致检查许多数据行的查询计划变得更加昂贵。例如，与读取少量数据行的索引范围扫描相比，全表扫描变得相对昂贵。engine_cost 中存储的是特定存储引擎相关操作的成本估计值：select * from mysql.engine_cost;engine_name|device_type|cost_name             |cost_value|last_update        |comment|default_value|-----------|-----------|----------------------|----------|-------------------|-------|-------------|default    |          0|io_block_read_cost    |          |2018-05-17 10:12:12|       |          1.0|default    |          0|memory_block_read_cost|          |2018-05-17 10:12:12|       |         0.25|    1    2    3    4    5engine_name 表示存储引擎，“default”表示所有存储引擎，也可以为不同的存储引擎插入特定的数据。cost_value 为空表示使用 default_value。其中，    io_block_read_cost 代表了从磁盘读取索引或数据块的成本。增加该值会使读取许多磁盘块的查询计划变得更加昂贵。例如，与读取较少块的索引范围扫描相比，全表扫描变得相对昂贵。    memory_block_read_cost 与 io_block_read_cost 类似，但它表示从数据库缓冲区读取索引或数据块的成本。我们来看一个例子，执行以下语句：explain format=jsonselect *from employeewhere dept_id between 4 and 5;{  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "2.75"    },    "table": {      "table_name": "employee",      "access_type": "ALL",      "possible_keys": [        "idx_emp_dept"      ],      "rows_examined_per_scan": 25,      "rows_produced_per_join": 17,      "filtered": "68.00",      "cost_info": {        "read_cost": "1.05",        "eval_cost": "1.70",        "prefix_cost": "2.75",        "data_read_per_join": "9K"      },      "used_columns": [        "emp_id",        "emp_name",        "sex",        "dept_id",        "manager",        "hire_date",        "job_id",        "salary",        "bonus",        "email"      ],      "attached_condition": "(`hrdb`.`employee`.`dept_id` between 4 and 5)"    }  }}    1    2    3    4    5    6    7    8    9    10    11    12    13    14    15    16    17    18    19    20    21    22    23    24    25    26    27    28    29    30    31    32    33    34    35    36    37    38    39    40    41    42查询计划显示使用了全表扫描（access_type = ALL），而没有选择 idx_emp_dept。通过优化器跟踪可以看到具体原因：                  "analyzing_range_alternatives": {                    "range_scan_alternatives": [                      {                        "index": "idx_emp_dept",                        "ranges": [                          "4 <= dept_id <= 5"                        ],                        "index_dives_for_eq_ranges": true,                        "rowid_ordered": false,                        "using_mrr": false,                        "index_only": false,                        "rows": 17,                        "cost": 6.21,                        "chosen": false,                        "cause": "cost"                      }                    ],                    "analyzing_roworder_intersect": {                      "usable": false,                      "cause": "too_few_roworder_scans"                    }                  }    1    2    3    4    5    6    7    8    9    10    11    12    13    14    15    16    17    18    19    20    21    22使用全表扫描的总成本为 2.75，使用范围扫描的总成本为 6.21。这是因为查询返回了 employee 表中大部分的数据，通过索引范围扫描，然后再回表反而会比直接扫描表更慢。接下来我们将数据行比较的成本常量 row_evaluate_cost 从 0.1 改为 1，并且刷新内存中的值：update mysql.server_costset cost_value=1where cost_name='row_evaluate_cost';flush optimizer_costs;    1    2    3    4    5然后重新连接数据库，再次获取执行计划的结果如下：{  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "38.51"    },    "table": {      "table_name": "employee",      "access_type": "range",      "possible_keys": [        "idx_emp_dept"      ],      "key": "idx_emp_dept",      "used_key_parts": [        "dept_id"      ],      "key_length": "4",      "rows_examined_per_scan": 17,      "rows_produced_per_join": 17,      "filtered": "100.00",      "index_condition": "(`hrdb`.`employee`.`dept_id` between 4 and 5)",      "cost_info": {        "read_cost": "21.51",        "eval_cost": "17.00",        "prefix_cost": "38.51",        "data_read_per_join": "9K"      },      "used_columns": [        "emp_id",        "emp_name",        "sex",        "dept_id",        "manager",        "hire_date",        "job_id",        "salary",        "bonus",        "email"      ]    }  }}    1    2    3    4    5    6    7    8    9    10    11    12    13    14    15    16    17    18    19    20    21    22    23    24    25    26    27    28    29    30    31    32    33    34    35    36    37    38    39    40    41    42此时，优化器选择的范围扫描（access_type = range）。虽然它的成本增加为 38.51，但是使用全表扫描的代价更高。最后，记得将 row_evaluate_cost 的还原成默认设置并重新连接数据库：update mysql.server_costset cost_value= nullwhere cost_name='row_evaluate_cost';flush optimizer_costs;    1    2    3    4    5    ⚠️不要轻易修改成本常量，因为这样可能导致许多查询计划变得更糟！在大多数生产情况下，推荐通过添加优化器提示（optimizer hint）控制查询计划的选择。数据字典与统计信息除了成本常量之外，MySQL 优化器在优化的过程中还会使用数据字典和存储引擎中的统计信息。例如表的数据量、索引、索引的唯一性以及字段是否可空都会影响到执行计划的选择，包括数据的访问方法和表的连接顺序等。MySQL 会在日常操作过程中粗略统计表的大小和索引的基数（Cardinality），我们也可以使用 ANALYZE TABLE 语句手动更新表的统计信息和索引的数据分布。ANALYZE TABLE tbl_name [, tbl_name] ...;    1这些统计信息默认会持久化到数据字典表 mysql.innodb_index_stats 和 mysql.innodb_table_stats 中，也可以通过 INFORMATION_SCHEMA 视图 TABLES、STATISTICS 以及 INNODB_INDEXES 进行查看。另外，从 MySQL 8.0 开始增加了直方图统计（histogram statistics），也就是字段值的分布情况。用户同样可以通过ANALYZE TABLE语句生成或者删除字段的直方图：ANALYZE TABLE tbl_nameUPDATE HISTOGRAM ON col_name [, col_name] ...[WITH N BUCKETS];ANALYZE TABLE tbl_nameDROP HISTOGRAM ON col_name [, col_name] ...;    1    2    3    4    5    6其中，WITH N BUCKETS 用于指定直方图统计时桶的个数，取值范围从 1 到 1024，默认为 100。直方图统计主要用于没有创建索引的字段，当查询使用这些字段与常量进行比较时，MySQL 优化器会使用直方图统计评估过滤之后的行数。例如，以下语句显示了没有直方图统计时的优化器评估：explain analyzeselect *from employeewhere salary = 10000;-> Filter: (employee.salary = 10000.00)  (cost=2.75 rows=3) (actual time=0.612..0.655 rows=1 loops=1)    -> Table scan on employee  (cost=2.75 rows=25) (actual time=0.455..0.529 rows=25 loops=1)    1    2    3    4    5    6由于 salary 字段上既没有索引也没有直方图统计，因此优化器评估返回的行数为 3，但实际返回的行数为 1。我们为 salary 字段创建直方图统计：analyze table employee update histogram on salary;Table        |Op       |Msg_type|Msg_text                                         |-------------|---------|--------|-------------------------------------------------|hrdb.employee|histogram|status  |Histogram statistics created for column 'salary'.|    1    2    3    4然后再次查看执行计划：explain analyzeselect *from employeewhere salary = 10000;-> Filter: (employee.salary = 10000.00)  (cost=2.75 rows=1) (actual time=0.265..0.291 rows=1 loops=1)    -> Table scan on employee  (cost=2.75 rows=25) (actual time=0.206..0.258 rows=25 loops=1)    1    2    3    4    5    6此时，优化器评估的行数和实际返回的行数一致，都是 1。MySQL 使用数据字典表 column_statistics 存储字段值分布的直方图统计，用户可以通过查询视图 INFORMATION_SCHEMA.COLUMN_STATISTICS 获得直方图信息：select * from information_schema.column_statistics;SCHEMA_NAME|TABLE_NAME|COLUMN_NAME|HISTOGRAM                                                                                                                                                                                                                                                      |-----------|----------|-----------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|hrdb       |employee  |salary     |{"buckets": [[4000.00, 0.08], [4100.00, 0.12], [4200.00, 0.16], [4300.00, 0.2], [4700.00, 0.24000000000000002], [4800.00, 0.28], [5800.00, 0.32], [6000.00, 0.4], [6500.00, 0.48000000000000004], [6600.00, 0.52], [6800.00, 0.56], [7000.00, 0.600000000000000|    1    2    3    4删除以上直方图统计的命令如下：analyze table employee drop histogram on salary;    1索引和直方图之间的区别在于：    索引需要随着数据的修改而更新；    直方图通过命令手动更新，不会影响数据更新的性能。但是，直方图统计会随着数据修改变得过时。相对于直方图统计，优化器会优先选择索引范围优化评估返回的数据行。因为对于索引字段而言，范围优化可以获得更加准确的评估。控制优化行为MySQL 提供了一个系统变量 optimizer_switch，用于控制优化器的优化行为。select @@optimizer_switch;@@optimizer_switch                                                                                                                                                                                                                                             |---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on|    1    2    3    4    5    6    7它的值由一组标识组成，每个标识的值都可以为 on 或 off，表示启用或者禁用了相应的优化行为。该变量支持全局和会话级别的设置，可以在运行时进行更改。SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';    1其中，command 可以是以下形式：    default，将所有优化行为设置为默认值。    opt_name=default，将指定优化行为设置为默认值。    opt_name=off，禁用指定的优化行为。    opt_name=on，启用指定的优化行为。我们以索引条件下推（index_condition_pushdown）优化为例，演示修改 optimizer_switch 的效果。首先执行以下语句查看执行计划：explainselect *from employee ewhere e.email like 'zhang%';id|select_type|table|partitions|type |possible_keys|key         |key_len|ref|rows|filtered|Extra                |--|-----------|-----|----------|-----|-------------|------------|-------|---|----|--------|---------------------| 1|SIMPLE     |e    |          |range|uk_emp_email |uk_emp_email|302    |   |   2|   100.0|Using index condition|    1    2    3    4    5    6    7    8其中，Extra 字段中的“Using index condition”表示使用了索引条件下推。然后禁用索引条件下推优化：set @@optimizer_switch='index_condition_pushdown=off';    1然后再次查看执行计划：id|select_type|table|partitions|type |possible_keys|key         |key_len|ref|rows|filtered|Extra      |--|-----------|-----|----------|-----|-------------|------------|-------|---|----|--------|-----------| 1|SIMPLE     |e    |          |range|uk_emp_email |uk_emp_email|302    |   |   2|   100.0|Using where|    1    2    3Extra 字段变成了“Using where”，意味着需要访问表中的数据然后再应用该条件过滤。如果使用优化器跟踪，可以看到更详细的差异。优化器和索引提示虽然通过系统变量 optimizer_switch 可以控制优化器的优化策略，但是一旦改变它的值，后续的查询都会受到影响，除非再次进行设置。另一种控制优化器策略的方法就是优化器提示（Optimizer Hint）和索引提示（Index Hint），它们只对单个语句有效，而且优先级比 optimizer_switch 更高。优化器提示使用 /*+ … */ 注释风格的语法，可以对连接顺序、表访问方式、索引使用方式、子查询、语句执行时间限制、系统变量以及资源组等进行语句级别的设置。例如，在没有使用优化器提示的情况下：explainselect *from employee ejoin department d on d.dept_id = e.dept_idwhere e.salary = 10000;id|select_type|table|partitions|type  |possible_keys|key    |key_len|ref           |rows|filtered|Extra      |--|-----------|-----|----------|------|-------------|-------|-------|--------------|----|--------|-----------| 1|SIMPLE     |e    |          |ALL   |idx_emp_dept |       |       |              |  25|     4.0|Using where| 1|SIMPLE     |d    |          |eq_ref|PRIMARY      |PRIMARY|4      |hrdb.e.dept_id|   1|   100.0|           |    1    2    3    4    5    6    7    8    9优化器选择 employee 作为驱动表，并且使用全表扫描返回 salary = 10000 的数据；然后通过主键查找 department 中的记录。然后我们通过优化器提示 join_order 修改两个表的连接顺序：explainselect /*+ join_order(d, e) */ *from employee ejoin department d on d.dept_id = e.dept_idwhere e.salary = 10000;id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra                                     |--|-----------|-----|----------|----|-------------|---|-------|---|----|--------|------------------------------------------| 1|SIMPLE     |d    |          |ALL |PRIMARY      |   |       |   |   6|   100.0|                                          | 1|SIMPLE     |e    |          |ALL |idx_emp_dept |   |       |   |  25|     4.0|Using where; Using join buffer (hash join)|    1    2    3    4    5    6    7    8    9此时，优化器选择了 department 作为驱动表；同时访问 employee 时选择了全表扫描。我们可以再增加一个索引相关的优化器提示 index：explainselect /*+ join_order(d, e) index(e idx_emp_dept) */ *from employee ejoin department d on d.dept_id = e.dept_idwhere e.salary = 10000;id|select_type|table|partitions|type|possible_keys|key         |key_len|ref           |rows|filtered|Extra      |--|-----------|-----|----------|----|-------------|------------|-------|--------------|----|--------|-----------| 1|SIMPLE     |d    |          |ALL |PRIMARY      |            |       |              |   6|   100.0|           | 1|SIMPLE     |e    |          |ref |idx_emp_dept |idx_emp_dept|4      |hrdb.d.dept_id|   5|    10.0|Using where|    1    2    3    4    5    6    7    8    9最终，优化器选择了通过索引 idx_emp_dept 查找 employee 中的数据。需要注意的是，通过提示禁用某个优化行为可以阻止优化器使用该优化；但是启用某个优化行为不代表优化器一定会使用该优化，它可以选择使用或者不使用。    ⚠️开发和测试过程可以使用优化器提示和索引提示，但是生产环境中需要小心使用。因为实际数据和环境会随着时间发生变化，而且 MySQL 优化器也会越来越智能，合理的参数配置定时的统计更新通常是更好地选择。索引提示为优化器提供了如何选择索引的信息，直接出现在表名之后：tbl_name [[AS] alias]    USE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (index_name, ...)  | {IGNORE|FORCE} {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (index_name, ...)    1    2    3USE INDEX 提示优化器使用某个索引，IGNORE INDEX 提示优化器忽略某个索引，FORCE INDEX 强制使用某个索引。例如，以下语句使用了 USE INDEX 索引提示：explainselect *from employee e use index (idx_emp_job)join department d on d.dept_id = e.dept_idwhere e.salary = 10000;id|select_type|table|partitions|type  |possible_keys|key    |key_len|ref           |rows|filtered|Extra      |--|-----------|-----|----------|------|-------------|-------|-------|--------------|----|--------|-----------| 1|SIMPLE     |e    |          |ALL   |             |       |       |              |  25|    10.0|Using where| 1|SIMPLE     |d    |          |eq_ref|PRIMARY      |PRIMARY|4      |hrdb.e.dept_id|   1|   100.0|           |    1    2    3    4    5    6    7    8    9虽然我们使用了索引提示，但是由于索引 idx_emp_job 和查询完全无关，优化器最终还是没有选择使用该索引。以下示例使用了 IGNORE INDEX 索引提示：explainselect *from employee ejoin department d ignore index (PRIMARY)on d.dept_id = e.dept_idwhere e.salary = 10000;id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra                                     |--|-----------|-----|----------|----|-------------|---|-------|---|----|--------|------------------------------------------| 1|SIMPLE     |e    |          |ALL |idx_emp_dept |   |       |   |  25|    10.0|Using where                               | 1|SIMPLE     |d    |          |ALL |             |   |       |   |   6|   16.67|Using where; Using join buffer (hash join)|    1    2    3    4    5    6    7    8    9    10IGNORE INDEX 使得优化器放弃了 department 的主键查找，最终选择了 hash join 连接两个表。该示例也可以通过优化器提示 no_index 实现：explainselect /*+ no_index(d PRIMARY) */ *from employee ejoin department don d.dept_id = e.dept_idwhere e.salary = 10000;    1    2    3    4    5    6    ⚠️从 MySQL 8.0.20 开始，提供了等价形式的索引级别优化器提示，将来可能会废弃传统形式的索引提示。总结MySQL 优化器使用基于成本的优化方式，利用数据字典和统计信息选择 SQL 语句的最佳执行方式。同时，MySQL 为我们提供了控制优化器的各种选项，包括控制优化程度、设置成本常量、统计信息收集、启用/禁用优化行为以及使用优化器提示等。版权声明：本文为CSDN博主「不剪发的Tony老师」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/horses/article/details/105841886

MySQL数据库优化的八种方式(经典必看)引言： 关于数据库优化，网上有不少资料和方法，但是不少质量参差不齐，有些总结的不够到位，内容冗杂偶尔发现了这篇文章，总结得很经典，文章流量也很大，所以拿到自己的总结文集中，积累优质文章，提升个人能力，希望对大家今后开发中也有帮助1、选取最适用的字段属性MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。2、使用连接（JOIN）来代替子查询(Sub-Queries)MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：DELETE FROM customerinfoWHERE CustomerID NOT in (SELECT customerid FROM salesinfo)使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）..替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：SELECT * FROM customerinfoWHERE customerid NOT IN (SELECT customerid FROM salesinfo)如果使用连接（JOIN）..来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：SELECT * FROM customerinfoLEFT JOIN salesinfo ON customerinfo.customerid =salesinfo.customeridWHERE salesinfo.customerid IS NULL连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。3、使用联合(UNION)来代替手动创建的临时表MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。SELECT name,phone FROM client UNIONSELECT name,birthdate FROM author UNIONSELECT name,supplier FROM product4、事务尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。BEGIN; INSERT INTO salesinfo SET customerid=14; UPDATE inventory SET quantity =11 WHERE item='book';COMMIT;事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。5、锁定表尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。LOCK TABLE inventory WRITE SELECT quantity FROM inventory WHERE Item='book';...UPDATE inventory SET Quantity=11 WHERE Item='book';UNLOCKTABLES这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。6、使用外键锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。CREATE TABLE customerinfo( customerid int primary key) engine = innodb;CREATE TABLE salesinfo( salesid int not null,customerid int not null, primary key(customerid,salesid),foreign key(customerid) references customerinfo(customerid) on delete cascade)engine = innodb;注意例子中的参数“ON DELETE CASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATETABLE语句中加上TYPE=INNODB。如例中所示。7、使用索引索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。那该对哪些字段建立索引呢？一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。8、优化的查询语句绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。下面是应该注意的几个方面。首先，最好是在相同类型的字段间进行比较的操作。在MySQL3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。其次，在建有索引的字段上尽量不要使用函数进行操作。例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。第三，在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。例如下面的查询将会比较表中的每一条记录。SELECT * FROM books WHERE name like "MySQL%"但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：SELECT * FROM books WHERE name ＞= "MySQL" and name ＜"MySQM"最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。优化Mysql数据库的8个方法本文通过8个方法优化Mysql数据库：创建索引、复合索引、索引不会包含有NULL值的列、使用短索引、排序的索引问题、like语句操作、不要在列上进行运算、不使用NOT IN和<>操作1、创建索引对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。2、复合索引比如有一条语句是这样的：select * from users where area='beijing' and age=22;如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。3、索引不会包含有NULL值的列只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。4、使用短索引对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。5、排序的索引问题mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。6、like语句操作一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。7、不要在列上进行运算select * from users where YEAR(adddate)<2007;将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成select * from users where adddate<‘2007-01-01';8、不使用NOT IN和<>操作NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。数据库SQL优化大总结之 百万级数据库优化方案网上关于SQL优化的教程很多，但是比较杂乱。近日有空整理了一下，写出来跟大家分享一下，其中有错误和不足的地方，还请大家纠正补充。这篇文章我花费了大量的时间查找资料、修改、排版，希望大家阅读之后，感觉好的话推荐给更多的人，让更多的人看到、纠正以及补充。1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num = 03.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or Name = 'admin'可以这样查询：select id from t where num = 10union allselect id from t where Name = 'admin'5.in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)6.下面的查询也将导致全表扫描：select id from t where name like ‘%abc%’若要提高效率，可以考虑全文检索。7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t where num = @num可以改为强制查询使用索引：select id from t with(index(索引名)) where num = @num.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2 = 100应改为:select id from t where num = 100*29.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3) = ’abc’ -–name以abc开头的idselect id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ --生成的id应改为:select id from t where name like 'abc%'select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。12.不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create table #t(…)13.Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。14.对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。15.select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。16.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。17.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。18.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。19.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。20.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。21.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。22. 避免频繁创建和删除临时表，以减少系统表资源的消耗。临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件， 最好使用导出表。23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。29.尽量避免大事务操作，提高系统并发能力。30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。实际案例分析：拆分大的 DELETE 或INSERT 语句，批量提交SQL语句　　如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。　　Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。　　如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让你的WEB服务崩溃，还可能会让你的整台服务器马上挂了。　　所以，如果你有一个大的处理，你一定把其拆分，使用 LIMIT oracle(rownum),sqlserver(top)条件是一个好的方法。下面是一个mysql示例：while(1){ 　　//每次只做1000条　　 mysql_query(“delete from logs where log_date <= ’2012-11-01’ limit 1000”); 　　if(mysql_affected_rows() == 0){　　 　　//删除完成，退出！　　 　　break；　　}//每次暂停一段时间，释放表让其他进程/线程访问。usleep(50000)}好了，到这里就写完了。我知道还有很多没有写到的，还请大家补充。后面有空会介绍一些SQL优化工具给大家。让我们一起学习，一起进步吧！运维角度浅谈MySQL数据库优化 一个成熟的数据库架构并不是一开始设计就具备高可用、高伸缩等特性的，它是随着用户量的增加，基础架构才逐渐完善。这篇博文主要谈MySQL数据库发展周期中所面临的问题及优化方案，暂且抛开前端应用不说，大致分为以下五个阶段：1、数据库表设计  项目立项后，开发部根据产品部需求开发项目，开发工程师工作其中一部分就是对表结构设计。对于数据库来说，这点很重要，如果设计不当，会直接影响访问速度和用户体验。影响的因素很多，比如慢查询、低效的查询语句、没有适当建立索引、数据库堵塞（死锁）等。当然，有测试工程师的团队，会做压力测试，找bug。对于没有测试工程师的团队来说，大多数开发工程师初期不会太多考虑数据库设计是否合理，而是尽快完成功能实现和交付，等项目有一定访问量后，隐藏的问题就会暴露，这时再去修改就不是这么容易的事了。2、数据库部署  该运维工程师出场了，项目初期访问量不会很大，所以单台部署足以应对在1500左右的QPS（每秒查询率）。考虑到高可用性，可采用MySQL主从复制+Keepalived做双击热备，常见集群软件有Keepalived、Heartbeat。双机热备博文：http://lizhenliang.blog.51cto.com/7876557/13623133、数据库性能优化  如果将MySQL部署到普通的X86服务器上，在不经过任何优化情况下，MySQL理论值正常可以处理2000左右QPS，经过优化后，有可能会提升到2500左右QPS，否则，访问量当达到1500左右并发连接时，数据库处理性能就会变慢，而且硬件资源还很富裕，这时就该考虑软件问题了。那么怎样让数据库最大化发挥性能呢？一方面可以单台运行多个MySQL实例让服务器性能发挥到最大化，另一方面是对数据库进行优化，往往操作系统和数据库默认配置都比较保守，会对数据库发挥有一定限制，可对这些配置进行适当的调整，尽可能的处理更多连接数。具体优化有以下三个层面：  3.1 数据库配置优化  MySQL常用有两种存储引擎，一个是MyISAM，不支持事务处理，读性能处理快，表级别锁。另一个是InnoDB，支持事务处理（ACID），设计目标是为处理大容量数据发挥最大化性能，行级别锁。  表锁：开销小，锁定粒度大，发生死锁概率高，相对并发也低。  行锁：开销大，锁定粒度小，发生死锁概率低，相对并发也高。  为什么会出现表锁和行锁呢？主要是为了保证数据的完整性，举个例子，一个用户在操作一张表，其他用户也想操作这张表，那么就要等第一个用户操作完，其他用户才能操作，表锁和行锁就是这个作用。否则多个用户同时操作一张表，肯定会数据产生冲突或者异常。  根据以上看来，使用InnoDB存储引擎是最好的选择，也是MySQL5.5以后版本中默认存储引擎。每个存储引擎相关联参数比较多，以下列出主要影响数据库性能的参数。  公共参数默认值：123456max_connections = 151#同时处理最大连接数，推荐设置最大连接数是上限连接数的80%左右   sort_buffer_size = 2M#查询排序时缓冲区大小，只对order by和group by起作用，可增大此值为16Mopen_files_limit = 1024 #打开文件数限制，如果show global status like 'open_files'查看的值等于或者大于open_files_limit值时，程序会无法连接数据库或卡死  MyISAM参数默认值：12345678910key_buffer_size = 16M#索引缓存区大小，一般设置物理内存的30-40%read_buffer_size = 128K  #读操作缓冲区大小，推荐设置16M或32Mquery_cache_type = ON#打开查询缓存功能query_cache_limit = 1M  #查询缓存限制，只有1M以下查询结果才会被缓存，以免结果数据较大把缓存池覆盖query_cache_size = 16M  #查看缓冲区大小，用于缓存SELECT查询结果，下一次有同样SELECT查询将直接从缓存池返回结果，可适当成倍增加此值  InnoDB参数默认值：12345678910innodb_buffer_pool_size = 128M#索引和数据缓冲区大小，一般设置物理内存的60%-70%innodb_buffer_pool_instances = 1    #缓冲池实例个数，推荐设置4个或8个innodb_flush_log_at_trx_commit = 1  #关键参数，0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低。2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。对数据安全性要求不是很高的推荐设置2，性能高，修改后效果明显。innodb_file_per_table = OFF  #默认是共享表空间，共享表空间idbdata文件不断增大，影响一定的I/O性能。推荐开启独立表空间模式，每个表的索引和数据都存在自己独立的表空间中，可以实现单表在不同数据库中移动。innodb_log_buffer_size = 8M  #日志缓冲区大小，由于日志最长每秒钟刷新一次，所以一般不用超过16M  3.2 系统内核优化  大多数MySQL都部署在linux系统上，所以操作系统的一些参数也会影响到MySQL性能，以下对linux内核进行适当优化。12345678910net.ipv4.tcp_fin_timeout = 30#TIME_WAIT超时时间，默认是60snet.ipv4.tcp_tw_reuse = 1    #1表示开启复用，允许TIME_WAIT socket重新用于新的TCP连接，0表示关闭net.ipv4.tcp_tw_recycle = 1  #1表示开启TIME_WAIT socket快速回收，0表示关闭net.ipv4.tcp_max_tw_buckets = 4096   #系统保持TIME_WAIT socket最大数量，如果超出这个数，系统将随机清除一些TIME_WAIT并打印警告信息net.ipv4.tcp_max_syn_backlog = 4096#进入SYN队列最大长度，加大队列长度可容纳更多的等待连接  在linux系统中，如果进程打开的文件句柄数量超过系统默认值1024，就会提示“too many files open”信息，所以要调整打开文件句柄限制。1234# vi /etc/security/limits.conf  #加入以下配置，*代表所有用户，也可以指定用户，重启系统生效* soft nofile 65535* hard nofile 65535# ulimit -SHn 65535   #立刻生效  3.3 硬件配置  加大物理内存，提高文件系统性能。linux内核会从内存中分配出缓存区（系统缓存和数据缓存）来存放热数据，通过文件系统延迟写入机制，等满足条件时（如缓存区大小到达一定百分比或者执行sync命令）才会同步到磁盘。也就是说物理内存越大，分配缓存区越大，缓存数据越多。当然，服务器故障会丢失一定的缓存数据。  SSD硬盘代替SAS硬盘，将RAID级别调整为RAID1+0，相对于RAID1和RAID5有更好的读写性能（IOPS），毕竟数据库的压力主要来自磁盘I/O方面。4、数据库架构扩展  随着业务量越来越大，单台数据库服务器性能已无法满足业务需求，该考虑加机器了，该做集群了~~~。主要思想是分解单台数据库负载，突破磁盘I/O性能，热数据存放缓存中，降低磁盘I/O访问频率。  4.1 主从复制与读写分离  因为生产环境中，数据库大多都是读操作，所以部署一主多从架构，主数据库负责写操作，并做双击热备，多台从数据库做负载均衡，负责读操作，主流的负载均衡器有LVS、HAProxy、Nginx。  怎么来实现读写分离呢？大多数企业是在代码层面实现读写分离，效率比较高。另一个种方式通过代理程序实现读写分离，企业中应用较少，常见代理程序有MySQL Proxy、Amoeba。在这样数据库集群架构中，大大增加数据库高并发能力，解决单台性能瓶颈问题。如果从数据库一台从库能处理2000 QPS，那么5台就能处理1w QPS，数据库横向扩展性也很容易。  有时，面对大量写操作的应用时，单台写性能达不到业务需求。如果做双主，就会遇到数据库数据不一致现象，产生这个原因是在应用程序不同的用户会有可能操作两台数据库，同时的更新操作造成两台数据库数据库数据发生冲突或者不一致。在单库时MySQL利用存储引擎机制表锁和行锁来保证数据完整性，怎样在多台主库时解决这个问题呢？有一套基于perl语言开发的主从复制管理工具，叫MySQL-MMM（Master-Master replication managerfor Mysql，Mysql主主复制管理器），这个工具最大的优点是在同一时间只提供一台数据库写操作，有效保证数据一致性。  主从复制博文：http://lizhenliang.blog.51cto.com/7876557/1290431  读写分离博文：http://lizhenliang.blog.51cto.com/7876557/1305083 MySQL-MMM博文：http://lizhenliang.blog.51cto.com/7876557/1354576  4.2 增加缓存  给数据库增加缓存系统，把热数据缓存到内存中，如果缓存中有要请求的数据就不再去数据库中返回结果，提高读性能。缓存实现有本地缓存和分布式缓存，本地缓存是将数据缓存到本地服务器内存中或者文件中。分布式缓存可以缓存海量数据，扩展性好，主流的分布式缓存系统有memcached、redis，memcached性能稳定，数据缓存在内存中，速度很快，QPS可达8w左右。如果想数据持久化就选择用redis，性能不低于memcached。  工作过程：    4.3 分库  分库是根据业务不同把相关的表切分到不同的数据库中，比如web、bbs、blog等库。如果业务量很大，还可将切分后的库做主从架构，进一步避免单个库压力过大。  4.4 分表  数据量的日剧增加，数据库中某个表有几百万条数据，导致查询和插入耗时太长，怎么能解决单表压力呢？你就该考虑是否把这个表拆分成多个小表，来减轻单个表的压力，提高处理效率，此方式称为分表。  分表技术比较麻烦，要修改程序代码里的SQL语句，还要手动去创建其他表，也可以用merge存储引擎实现分表，相对简单许多。分表后，程序是对一个总表进行操作，这个总表不存放数据，只有一些分表的关系，以及更新数据的方式，总表会根据不同的查询，将压力分到不同的小表上，因此提高并发能力和磁盘I/O性能。  分表分为垂直拆分和水平拆分：  垂直拆分：把原来的一个很多字段的表拆分多个表，解决表的宽度问题。你可以把不常用的字段单独放到一个表中，也可以把大字段独立放一个表中，或者把关联密切的字段放一个表中。  水平拆分：把原来一个表拆分成多个表，每个表的结构都一样，解决单表数据量大的问题。  4.5 分区  分区就是把一张表的数据根据表结构中的字段（如range、list、hash等）分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上，分区后，表面上还是一张表，但数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘I/O读写性能，实现比较简单。注：增加缓存、分库、分表和分区主要由程序猿来实现。5、数据库维护  数据库维护是运维工程师或者DBA主要工作，包括性能监控、性能分析、性能调优、数据库备份和恢复等。  5.1 性能状态关键指标  QPS，Queries Per Second：每秒查询数，一台数据库每秒能够处理的查询次数  TPS，Transactions Per Second：每秒处理事务数  通过show status查看运行状态，会有300多条状态信息记录，其中有几个值帮可以我们计算出QPS和TPS，如下：  Uptime：服务器已经运行的实际，单位秒  Questions：已经发送给数据库查询数  Com_select：查询次数，实际操作数据库的  Com_insert：插入次数  Com_delete：删除次数  Com_update：更新次数  Com_commit：事务次数  Com_rollback：回滚次数  那么，计算方法来了，基于Questions计算出QPS：12  mysql> show global status like 'Questions';  mysql> show global status like 'Uptime';  QPS = Questions / Uptime  基于Com_commit和Com_rollback计算出TPS：123  mysql> show global status like 'Com_commit';  mysql> show global status like 'Com_rollback';  mysql> show global status like 'Uptime';  TPS = (Com_commit + Com_rollback) / Uptime  另一计算方式：基于Com_select、Com_insert、Com_delete、Com_update计算出QPS1  mysql> show global status where Variable_name in('com_select','com_insert','com_delete','com_update');  等待1秒再执行，获取间隔差值，第二次每个变量值减去第一次对应的变量值，就是QPS  TPS计算方法：1  mysql> show global status where Variable_name in('com_insert','com_delete','com_update');  计算TPS，就不算查询操作了，计算出插入、删除、更新四个值即可。  经网友对这两个计算方式的测试得出，当数据库中myisam表比较多时，使用Questions计算比较准确。当数据库中innodb表比较多时，则以Com_*计算比较准确。  5.2 开启慢查询日志  MySQL开启慢查询日志，分析出哪条SQL语句比较慢，使用set设置变量，重启服务失效，可以在my.cnf添加参数永久生效。1234mysql> set global slow-query-log=on  #开启慢查询功能mysql> set global slow_query_log_file='/var/log/mysql/mysql-slow.log';  #指定慢查询日志文件位置mysql> set global log_queries_not_using_indexes=on;   #记录没有使用索引的查询mysql> set global long_query_time=1;   #只记录处理时间1s以上的慢查询  分析慢查询日志，可以使用MySQL自带的mysqldumpslow工具，分析的日志较为简单。  # mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log    #查看最慢的前三个查询  也可以使用percona公司的pt-query-digest工具，日志分析功能全面，可分析slow log、binlog、general log。  分析慢查询日志：pt-query-digest /var/log/mysql/mysql-slow.log  分析binlog日志：mysqlbinlog mysql-bin.000001 >mysql-bin.000001.sql   pt-query-digest --type=binlog mysql-bin.000001.sql   分析普通日志：pt-query-digest --type=genlog localhost.log  5.3 数据库备份  备份数据库是最基本的工作，也是最重要的，否则后果很严重，你懂得！但由于数据库比较大，上百G，往往备份都很耗费时间，所以就该选择一个效率高的备份策略，对于数据量大的数据库，一般都采用增量备份。常用的备份工具有mysqldump、mysqlhotcopy、xtrabackup等，mysqldump比较适用于小的数据库，因为是逻辑备份，所以备份和恢复耗时都比较长。mysqlhotcopy和xtrabackup是物理备份，备份和恢复速度快，不影响数据库服务情况下进行热拷贝，建议使用xtrabackup，支持增量备份。  Xtrabackup备份工具使用博文：http://lizhenliang.blog.51cto.com/7876557/1612800  5.4 数据库修复  有时候MySQL服务器突然断电、异常关闭，会导致表损坏，无法读取表数据。这时就可以用到MySQL自带的两个工具进行修复，myisamchk和mysqlcheck。  myisamchk：只能修复myisam表，需要停止数据库  常用参数：  -f --force    强制修复，覆盖老的临时文件，一般不使用  -r --recover  恢复模式  -q --quik     快速恢复  -a --analyze  分析表  -o --safe-recover 老的恢复模式，如果-r无法修复，可以使用此参数试试  -F --fast     只检查没有正常关闭的表  快速修复weibo数据库:  # cd /var/lib/mysql/weibo   # myisamchk -r -q *.MYI  mysqlcheck：myisam和innodb表都可以用，不需要停止数据库，如修复单个表，可在数据库后面添加表名，以空格分割  常用参数：  -a  --all-databases  检查所有的库  -r  --repair   修复表  -c  --check    检查表，默认选项  -a  --analyze  分析表  -o  --optimize 优化表  -q  --quik   最快检查或修复表  -F  --fast   只检查没有正常关闭的表  快速修复weibo数据库:  mysqlcheck -r -q -uroot -p123 weibo   5.5 另外，查看CPU和I/O性能方法  #查看CPU性能  #参数-P是显示CPU数，ALL为所有，也可以只显示第几颗CPU  #查看I/O性能  #参数-m是以M单位显示，默认K  #%util：当达到100%时，说明I/O很忙。  #await：请求在队列中等待时间，直接影响read时间。  I/O极限：IOPS（r/s+w/s）,一般RAID0/10在1200左右。（IOPS，每秒进行读写（I/O）操作次数）  I/O带宽：在顺序读写模式下SAS硬盘理论值在300M/s左右，SSD硬盘理论值在600M/s左右。

 场景介绍 人有时会身兼数职，需要查找出其中担任某一职务的都有哪些人，如下面position字段，不同的职务用数字表示，多个职务以逗号隔开。 先要查找出担任1职务的人员，通过以下两种方式来查询。  方式一 采用模糊查询，匹配出1职务的记录，如下SQL： select * from user where position like '%1%' 查询结果如下，仔细观察你会发现position为10的也被查出来了，但这个不符合业务要求。  方式二 采用MySQL的原生函数find_in_set(str,array)来查询，SQL如下： select * from user where find_in_set(1,position) 查询结果如下，符合要求。  函数介绍 FIND_IN_SET(str,strlist)，注意其中strlist只识别英文逗号。  ———————————————— 原文链接：https://blog.csdn.net/loongshawn/article/details/78611636 

多机部署LNMP是一个很重要的项目，L代表Linux系统，N代表Nginx服务， M代表Mysql服务，P代表PHP服务。LNMP平台应该是应用比较广泛的网站服务架构。随着Nginx在企业中的使用越来越多。LNMP架构也受到越来越多的linux系统工程师的青睐。因此，掌握多级部署LNMP是非常重要的。vim lnmp.sh#!/bin/bash#build lnmp#移除原有的yum仓库，并备份到/root/old目录下[-d /root/old] $$rm-rf /root/old;mkdir /root/old || mkdir /root/oldmv /etc/yum.repos.d/* /root/oldcp -r /root/old/*/etc/yum.repos.d/#更新安装epel源rpm -Uvh https://mirror.webtatic.com/yu,/e17/epel-release.rpmsleep 10#更新安装Centos源rpm -Uvh https://mirror.webtatic.com/yum/e17/webtatic-release.rpmyum clean allyum repolistsleep 10#安装依赖软件包yum -y install openssl-devel gcc-c++ gcc makeecho waiting install php......yum install -y php56w-fpm php56w-common pjp56w-mbstring php56w-mcrypt php56w-pdo php56w-mysqlnd php56w-bcmath php56w-xml php56w-ldap#修改相关配置sed -i 's/post_max_size=8M/post_max_size=16M/g' /etc/php.inised -i 's/max_execution_time=30/max_execution_time=300/g'/etc/php.inised -i 's/max_input_time=60/max_execution_time=300/g/etc/php.ini'sed -i 's/listen,allowed_clients=127.0.0.1/#listen.allowed_clients=127.0.0.1/g'/etc/php-fpm.d/www.conf#启动并设置开机自启动systemctl enable php-fpmsystemctl start pho-fpmsleep 10echo waiting for nginxyum -y install nginxnginx -tnginxsleep 10echo waiting for mysql15.6......[-d /usr/local/src]||mkdir -p /usr/local/srccd/usr/local/srcrpm -Uvh http://dev.mysql.com/get/mysql-community-release -e17-5.noarch.rpmyum repolist all |grep ''mysql.*-community.*''yum -y install mysql-community-serversystemctl enable mysqldsystemctl start mysqld.serviceecho $ip >> passwords.txtawk -F:'/temporary password""/{print $NF}' /var/log/mysqld.log>>passwords.txtsleep3以上是在一台服务器上实现部署LNMP，项目需求是要在多态服务器上实现，其思路和多级部署mysql一样，需要使用shell循环来实现多态机器的LNMP部署。vim ip.txt10.0.104.510.0.104.2710.0.104.3410.0.104.13610.0.104.108#!/bin/bash#mainwhile read ipdo{ping -c 1-w 2 $ ip>/dev/nullif [$?-eq 0];thenscp -r lnmp.sh root@ip:/tmp/ssh root@ip "/tmp/lnmp.sh"}&done <ip.txtwaitecho all finish以上代码是针对ip.txt文件中的主机ip地址进行多机部署LNMP。首先是先ping一下ip.txt文件中的ip地址，判断机器是否正常；然后把安装lnmp脚本复制到多态服务器上/tmp/下，远程使用管理员权限，执行安装LNMP脚本，如果显示 all finish 则表示所有服务器安装都已完成。