荐 MySQL数据库之数据库优化

三、数据库优化

1.批量插入

2.查询不要用*，将查询的列列出来

3.做索引

4.使用SSD

​ raid 10

5.适度调整swappiness
0 代表：最大限度地使用物理内存，然后才是 swap 分区，这种行为有可能导致系统内存溢出，从而导致mysql被意外kill掉。不建议这样去设置。
100则为：积极地使用使用 swap 分区，并且把内存上面的数据及时搬到 swap 分区里。
TIPS：这里比较建议使用默认 60 就可以。

6.尽可能大的给 innodb_buffer_pool
在服务器只跑数据库一个应用前提下大概为物理内存 50-80%。

7.推荐使用 xfs
不要再使用 ext3,ext4 之类的，因为 xfs 这种文件系统也是 B-tree 结构最接近于数据库的树状结构。

8.利用zabbix 做好对数据库的监控

 

3.1 影响Mysql 性能的重要参数

 

3.1.1 key_buffer_size的设置

说明：键缓存(变量 key_buffer_size)被所有线程共享；服务器使用的其它缓存则根据需要分配。此参数只适用于 myisam 存储引擎。

 
使用方法：

mysql5.1 以前只允许使用一个系统默认的 key_buffer

mysql5.1 以后提供了多个 key_buffer，可以将指定的表索引缓存入指定的key_buffer，这样可以更小的降低线程之间的竞争，相关语法如下：

例如，下面的语句将表 t1、t2 和 t3 的索引分配给名为 hot_cache 的 键高速缓冲：

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;

可以用 SET GLOBAL 参数设置语句或使用服务器启动选项设置在 CACHE INDEX 语句中引用的键高速缓冲的大小来创建键高速缓冲。例如：

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

 

要想删除键高速缓冲，将其大小设置为零：

mysql> SET GLOBAL keycache1.key_buffer_size=0;

请注意不能删除默认键高速缓冲。删除默认键高速缓冲的尝试将被忽略

 

CACHE INDEX 在一个表和 键高速缓冲之间建立一种联系，但每次服务器重启时该联系被丢失。如果你想要每次服务器重启时该联系生效，一个发办法是使用选项文件：

包括配置 键高速缓冲的变量设定值，和一个 init-file 选项用来命名包含待执行的

CACHE INDEX 语句的一个文件。例如：

key_buffer_size = 4G

hot_cache.key_buffer_size = 2G

cold_cache.key_buffer_size = 2G

init_file=/path/to/data-directory/mysqld_init.sql

每次服务器启动时执行mysqld_init.sql中的语句。该文件每行应包含一个SQL语句 。下面的例子分配几个表，分别对应 hot_cache 和 cold_cache：

CACHE INDEX a.t1, a.t2, b.t3 IN hot_cache

CACHE INDEX a.t4, b.t5, b.t6 IN cold_cache

 

要想将索引预装到缓存中，使用 LOAD INDEX INTO CACHE 语句。例如，下面的语句可以预装表 t1 和 t2 索引的非叶节点(索引块)：

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;

 

键高速缓冲可以通过更新其参数值随时重新构建。例如：

mysql> SET GLOBAL cold_cache.key_buffer_size=4*1024*1024；

 
如果你很少使用 MyISAM 表，那么也保留低于 16-32MB 的 key_buffer_size 以适应给予磁盘的临时表索引所需。

 
 

3.1.2 table_cache的设置

说明：数据库中打开表的缓存数量。table_cache 与 max_connections 有关。例如，对于 200 个并行运行的连接，应该让表的缓存至少有 200 * N，这里 N 是可以执行的查询的一个联接中表的最大数量。还需要为临时表和文件保留一些额外的文件描述符。

设置技巧：

可以通过检查 mysqld 的状态变量 Opened_tables 确定表缓存是否太小：

mysql> SHOW STATUS LIKE ‘Opened_tables’;

±--------------±------+

| Variable_name | Value |

±--------------±------+

| Opened_tables | 2741 |

±--------------±------+

如果值很大，即使你没有发出许多 FLUSH TABLES 语句，也应增加表缓存的大小。

 
 

3.1.3 innodb_buffer_pool_size 的设置

缓存 InnoDB 数据和索引的内存缓冲区的大小。你把这个值设得越高，访问表中数据需要得磁盘 I/O 越少。在一个专用的数据库服务器上，你可以设置这个参数达机器物理内存大小的 80%。尽管如此，还是不要把它设置得太大，因为对物理内存的竞争可能在操作系统上导致内存调度。

 
 

3.1.4 innodb_flush_log_at_trx_commit的设置

0：日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。

1：在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。

2：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。对日志文件每秒刷新一次。

默认值是 1，也是最安全的设置，即每个事务提交的时候都会从 log buffer 写到日志文件，而且会实际刷新磁盘，但是这样性能有一定的损失。如果可以容忍在数据库崩溃的时候损失一部分数据，那么设置成 0 或者 2 都会有所改善。设置成 0，则在数据库崩溃的时候会丢失那些没有被写入日志文件的事务，最多丢失 1 秒钟的事务，这种方式是最不安全的，也是效率最高的。设置成 2 的时候，因为只是没有刷新到磁盘，但是已经写入日志文件，所以只要操作系统没有崩溃，那么并没有丢失数据 ，比设置成 0 更安全一些。在 mysql 的手册中，为了确保事务的持久性和复制设置的耐受性、一致性，都是建议将这个参数设置为 1 的。

 
 

3.1.5 innodb_additional_mem_pool_size

InnoDB 用来存储数据目录信息和其它内部数据结构的内存池的大小。默认值是1MB。应用程序里的表越多，你需要在这里分配越多的内存。如果 InnoDB 用光了这个池内的内存，InnoDB 开始从操作系统分配内存，并且往 MySQL 错误日志写警告信息。没有必要给这个缓冲池分配非常大的空间，在应用相对稳定的情况下，这个缓冲池的大小也相对稳定。

 
 

3.1.6 innodb_table_locks

InnoDB 重视 LOCK TABLES，直到所有其它线程已经释放他们所有对表的锁定，MySQL 才从 LOCK TABLE … WRITE 返回。默认值是 1，这意为 LOCK TABLES 让 InnoDB内部锁定一个表。在使用 AUTOCOMMIT=1 的应用里，InnoDB 的内部表锁定会导致死锁 。可以通过设置 innodb_table_locks=0 来消除这个问题。

 
 

3.1.7 innodb_lock_wait_timeout

Mysql 可以自动的监测行锁导致的死锁并进行相应的处理，但是对于表锁导致的死锁不能自动的监测，所以该参数主要被用来在出现类似情况的时候对锁定进行的后续处理。默认值是 50 秒，根据应用的需要进行调整。

 
 

3.1.8 innodb_support_xa

通过该参数设置是否支持分布式事务，默认值是 ON 或者 1，表示支持分布式事务。如果确认应用中不需要使用分布式事务，则可以关闭这个参数，减少磁盘刷新的次数并获得更好的 InnoDB 性能。

 
 

3.1.9 innodb_doublewrite

默认地，InnoDB 存储所有数据两次，第一次存储到 doublewrite 缓冲，然后存储到确实的数据文件。如果对性能的要求高于对数据完整性的要求，那么可以通过–skip-innodb-doublewrite 关闭这个设置。

 
 

3.1.10 innodb_log_buffer_size

默认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存 – 它每秒都会刷新一次，因此无需设置超过 1 秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。

 
 

3.1.11 innodb_log_file_size

在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。

 
 

3.2 其他优化措施

 

1.使用持久的连接数据库以避免连接开销。

2.经常检查所有查询确实使用了必要的索引。

3.避免在频繁更新的表上执行复杂的 SELECT 查询，以避免与锁定表有关的由于读、写冲突发生的问题。

4.对于没有删除的行操作的 MyISAM 表，插入操作和查询操作可以并行进行，因为没有删除操作的表查询期间不会阻塞插入操作．对于确实需要执行删除操作的表，尽量在空闲时间进行批量删除操作，避免阻塞其他操作。

5.充分利用列有默认值的事实。只有当插入的值不同于默认值时，才明确地插入值。这减少 MySQL 需要做的语法分析从而提高插入速度。

6.对经常访问的可以重构的数据使用内存表，可以显著提高访问的效率。

7.通过复制可以提高某些操作的性能。可以在复制服务器中分布客户的检索以均分负载。为了防止备份期间对应用的影响，可以在复制服务器上执行备份操作。

8.表的字段尽量不使用自增长变量，在高并发情况下该字段的自增可能对效率有比较大的影响，推荐通过应用来实现字段的自增长。

 
 

3.3 mysql升降级

3.3.1 mysql升级

方法 1 最简单，适合于任何存储引擎（不一定速度最快）

安装新数据库

将老数据库导出为文本，导入到新数据库上

shell> mysqladmin -h hostname -P port -u user -p passwd create db_name

shell> mysqldump --opt db_name | mysql -h hostname -P port -u user -p passwd db_name

注：如果网络较慢，可以在导出选项中加上–compress 来减少网络传输

升级权限表

将原库中的 mysql 数据库目录全部 cp 过来覆盖新库中 mysql 数据库

在 shell 里面执行 mysql_fix_privilege_tables 命令升级权限表

shell>mysql_fix_privilege_tables

重启数据库服务

 

方法 2 适合于任何存储引擎，速度较快

安装新数据库

旧库中创建保存输出文件的目录并备份数据库：

shell> mkdir DUMPDIR

shell>mysqldump --tab=DUMPDIR db_name

将 DUMPDIR 目录中的文件转移到目标机上相应的目录中并将文件装载到 MySQL：

shell> mysqladmin create db_name # create database

shell> cat DUMPDIR/*.sql | mysql db_name # create tables in database

shell> mysqlimport db_name DUMPDIR/*.txt # load data into tables

（实际测试的时候，发现 txt 要放到 data 下才能执行，否则提示文件找不到）

升级权限表

将原库中的 mysql 数据库目录全部 cp 过来覆盖新库中 mysql 数据库

在 shell 里面执行 mysql_fix_privilege_tables 命令升级权限表

shell>mysql_fix_privilege_tables

重启数据库服务

 

方法 3 适合于 myisam 表，速度最快

安装新数据库

将原库中的数据目录下的所有文件（.frm，.MYD，MYI）cp 到新库下的相应目录下

升级权限表

将原库中的 mysql 数据库目录全部 cp 过来覆盖新库中 mysql 数据库

在 shell 里面执行 mysql_fix_privilege_tables 命令升级权限表

shell>mysql_fix_privilege_tables

flush tables 或者重启数据库服务生效

 
 

3.3.2 mysql降级

对于 myisam 存储引擎，直接将数据文件 cp 到低版本数据库上的数据目录下

如果发生表格式冲突，或者是其他存储引擎的表，用mysqldump 导出文本后导入低版本的数据库

 
 

3.4 Mysql 安全

 

3.4.1 正确设置目录权限

设置目录权限的原则是软件和数据分开，具体如下：

1.将 mysql 安装在单独的用户下

2.安装时，以 root 用户进行安装，mysql 的软件默认都为 root 权限

3.安装完毕后，将数据目录权限设置为实际运行 mysql 的用户权限，比如：

Chown –R mysql:mysql /home/mysql/data

 
 

3.4.2 尽量避免以root 权限运行mysql

将 4.1 的目录权限设置完毕后，启动、停止 mysql 以及日常的维护工作都可以在mysql 用户下进行，没有必要 su 到 root 后再用—user=mysql 来启动和关闭 mysql，这样就没有必要授权维护人员 root 权限，而且最重要的一定是因为任何具有 FILE权限的用户能够用 root 创建文件。

 
 

3.4.3 删除匿名帐号

安装完毕 mysql 后，会自动安装一个空帐号，普通用户只需要执行 mysql 命令即可

登陆 mysql，给系统造成隐患，建议删除此空帐号：

drop user ‘’@‘localhost’;

drop user ‘’@’ localhost.localdomain’;

 
 

3.4.4 给 mysql root 帐号设置口令

Mysql 安装完毕后，root 默认口令为空，需要马上修改 root 口令：

[zzx@localhost data]$ mysql **–**uroot

mysql> set password=password(‘123’);

Query OK, 0 rows affected (0.00 sec)

 
 

3.4.5 设置安全密码并定期修改

尽量使用安全密码，建议使用 6 位以上字母、数字、下画线和一些特殊字符组合而成的字符串

 
 

3.4.6 只授予帐号必须的权限

只需要赋予普通用户必须的权限，比如：

Grant select,insert,update,delete on tablename to‘username’@’hostname’;

 
 

3.4.7 除 root 外，任何用户不应有mysql 库 user 表的存取权限

如果拥有 mysql 库中 user 表的存取权限（select、update、insert、delete），就可以轻易的增加、修改、删除其他的用户权限，造成系统的安全隐患。

 
 

3.4.8 不要把FILE、PROCESS 或 SUPER 权限授予管理员以外的帐号

FILE 权限可以被滥用于将服务器主机上 MySQL能读取的任何文件读入到数据库表中。包括任何人可读的文件和服务器数据目录中的文件。可以使用 SELECT 访问数据库表，然后将其内容传输到客户端上。

不要向非管理用户授予 FILE 权限。有这权限的任何用户能在拥有 mysqld 守护进程权限的文件系统那里写一个文件！为了更加安全，由SELECT … INTO OUTFILE 生成的所有文件对每个人是可写的，并且你不能覆盖已经存在的文件。

file 权限也可以被用来读取任何作为运行服务器的 Unix 用户可读取或访问的文件。使用该权限，你可以将任何文件读入数据库表。这可能被滥用，例如，通过使用 LOADDATA 装载“/etc/passwd”进一个数据库表，然后能用 SELECT 显示它。

 

PROCESS 权限能被用来察看当前执行的查询的明文文本，包括设定或改变密码的查询。

 

SUPER 权限能用来终止其它用户或更改服务器的操作方式。比如 kill 进程

 

不要将 PROCESS 或 SUPER 权限授给非管理用户。mysqladmin processlist 的输出显示出当前执行的查询正文，如果另外的用户发出一个 UPDATE user SETpassword=PASSWORD(‘not_secure’)查询，被允许执行那个命令的任何用户可能看得到

 
 

3.4.9 load data local 带来的安全问题

由 MySQL 服务器启动文件从客户端向服务器主机的传输。理论上，打过补丁的服务器可以告诉客户端程序传输服务器选择的文件，而不是客户用 LOAD DATA 语句指定的文件。这样服务器可以访问客户端上客户有读访问权限的任何文件。

在 Web 环境中，客户从 Web 服务器连接，用户可以使用 LOAD DATA LOCAL 来读取Web服务器进程有读访问权限的任何文件(假定用户可以运行SQL服务器的任何命 令)。在这种环境中，MySQL 服务器的客户实际上是 Web 服务器，而不是连接 Web 服务器的用户运行的程序。

 

解决方法：

可以用–local-infile=0 选项启动 mysqld 从服务器端禁用所有 LOAD DATA

LOCAL 命令。

 
对于 mysql 命令行客户端，可以通过指定–local-infile[=1]选项启用 LOADDATA LOCAL，或通过–local-infile=0 选项禁用。类似地，对于 mysqlimport，–local or -L 选项启用本地数据文件装载。在任何情况下，成功进行本地装载需要服务器启用相关选项。

 
 

3.4.10 尽量避免通过symlinks 访问表

不要允许使用表的符号链接。(可以用–skip-symbolic-links 选项禁用）。如果你 用 root 运行 mysqld则特别重要，因为任何对服务器的数据目录有写访问权限的人则能够删除系统中的任何文件！

 
 

3.4.11 使用merge 存储引擎潜藏的安全漏洞

Merge 表在某些版本中可能存在以下安全漏洞：

用户 A 赋予表 T 的权限给用户 B

用户 B 创建一个包含 T 的 merge 表，做各种操作

用户 A 收回对 T 的权限

安全隐患：用户 B 通过 merge 表仍然可以访问表 A 中的数据

 
 

3.4.12 防止DNS 欺骗

如果你不信任你的 DNS，你应该在授权表中使用 IP 数字而不是主机名。在任何情况下，你应该非常小心地使用包含通配符的主机名来创建 授权表条目！

 
 

3.4.13 drop table 命令并不收回以前的相关访问授权

drop 表的时候，其他用户对此表的权限并没有被收回，这样导致重新创建同名的表时，以前其他用户对此表的权限会自动赋予，导致权限外流。因此，要在删除表时，同时取消其他用户在此表上的相应权限。

 
 

3.4.14 使用SSL

下面列出了规定 SSL、证书文件和密钥文件使用的选项。它们可以位于命令行中或选项文件中。

• --ssl

对于服务器，该选项规定该服务器允许 SSL 连接。对于客户端程序，它允许客户使用 SSL 连接服务器。单单该选项不足以使用 SSL 连接。还必须指定–ssl-ca、–ssl-cert 和–ssl-key 选项。

通常从反向使用该选项表示不应使用 SSL。要想实现，将选项指定为 --skip-ssl 或–ssl=0。

请注意使用–ssl 不需要 SSL 连接。例如，如果编译的服务器或客户不支持 SSL，则使用普通的未加密的连接。

确保使用 SSL 连接的安全方式是使用含 REQUIRE SSL 子句的 GRANT 语句在服务器上创建一个账户。然后使用该账户来连接服务器，服务器和客户端均应启用 SSL 支持。

• --ssl-ca=file_name

含可信 SSL CA 的清单的文件的路径。

• --ssl-capath=directory_name

包含 pem 格式的可信 SSL CA 证书的目录的路径。

• --ssl-cert=file_name

SSL 证书文件名，用于建立安全连接。

• --ssl-cipher=cipher_list

允许的用于 SSL 加密的密码的清单。cipher_list 的格式与 OpenSSL ciphers 命令

相同。

示例：--ssl-cipher=ALL:-AES:-EXP

• --ssl-key=file_name

SSL 密钥文件名，用于建立安全连接。

 
 

3.4.15 如果可能，给所有用户加*问IP 限制

给所有用户加上 ip 限制将拒绝所有未知的主机进行的连接，保证只有受信任的主机才可以进行连接。例如：

Grant select on dbname.* to ‘username’@’ip’ identified by ‘passwd’;

 
 

3.4.16 严格控制操作系统帐号和权限

在数据库服务器上要严格控制操作系统的帐号和权限，比如：

锁定 mysql 用户

其他任何用户都采取独立的帐号登陆，管理员通过普通用户管理 mysql；或者通过 root su到 mysql 用户下进行管理。

禁止修改 mysql 用户下的任何资源

 
 

3.4.17 增加防火墙

购买防火墙。这样可以保护你防范各种软件中至少 50%的各种类型的攻击。把MySQL放到防火墙后或隔离区(DMZ)。

 
 

3.4.18 其他安全设置选项

allow-suspicious-udfs

该选项控制是否可以载入主函数只有 xxx 符的用户定义函数。默认情况下，该选项被关闭，并且只能载入至少有辅助符的 UDF。这样可以防止从未包含合法 UDF 的共享对象文件载入函数。

 

old-passwords

强制服务器为新密码生成短(pre-4.1)密码哈希。当服务器必须支持旧版本客户端程序时，为了保证兼容性这很有用。

 
safe-user-create

如果启用，用户不能用 GRANT 语句创建新用户，除非用户有 mysql.user 表的 INSERT权限。如果你想让用户具有授权权限来创建新用户，你应给用户授予下面的权限：

mysql> GRANT INSERT(user) ON mysql.user TO ‘user_name’@‘host_name’;

这样确保用户不能直接更改权限列，必须使用 GRANT 语句给其它用户授予该权限。

 
secure-auth

不允许鉴定有旧(pre-4.1)密码的账户。

 

skip-grant-tables

这个选项导致服务器根本不使用权限系统。这给每个人以完全访问所有的数据库的权力！（通过执行 mysqladmin flush-privileges 或 mysqladmin reload 命令，或执行 FLUSH PRIVILEGES 语句，你能告诉一个正在运行的服务器再次开始使用授权表 。）

 
skip-networking

在网络上不允许 TCP/IP 连接。所有到 mysqld 的连接必须经由 Unix 套接字进行

 
skip-show-database

使用该选项，只允许有 SHOW DATABASES 权限的用户执行 SHOW DATABASES 语句，该语句显示所有数据库名。不使用该选项，允许所有用户执行 SHOW DATABASES，但只显示用户有 SHOW DATABASES 权限或部分数据库权限的数据库名。请注意全局权限指数据库的权限。

本文地址：https://blog.csdn.net/weixin_46108954/article/details/107296511