本文主要是介绍Mysql安装、配置调优（二），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

目录

引言

MySQL优化三大方向

优化思路

常见数据库引擎对比

数据库配置 

​一、慢查询 

1.查看慢查询相关参数

2.设置方法

3.重启MySQL服务

4.测试

二、连接数 

1.查看参数

三、Key_buffer_size 

四、临时表 

五、表锁情况  

六、索引优化

索引设计原则：

查看mysql执行sql历史记录

五、排序优化

六、读写分离配置

七、表结构优化

引言

        关于数据库优化，网上有不少资料和方法，但是不少质量参差不齐，有些总结的不够到位，内容冗杂；偶尔发现了这篇文章，总结得很经典，文章流量也很大，所以拿到自己的总结文集中，积累优质文章，提升个人能力，希望对大家今后开发中也有帮助

MySQL优化三大方向

        ① 优化MySQL所在服务器内核(此优化一般由运维人员完成)。
        ② 对MySQL配置参数进行优化（my.cnf）此优化需要进行压力测试来进行参数调整。
        ③ 对SQL语句以及表优化。

优化思路

1. 选择合适的数据库引擎：
2. 配置优化：
3. Sql优化：性能瓶颈定位、show status命令、慢查询日志、explain分析查询、profiling分析查询
4. 索引优化：
5. 优化排序：
6. 读写分离：
7. 表结构优化：水平拆分、垂直拆分和逆规范化，见第七点
8. 硬件升级：是用RAID10磁盘阵列，RAID10兼具RAID1的可靠性和RAID0的优良并发读写性能
9. 使用表分区： 跨多个磁盘来分散查询，能获得更大的吞吐量，需要一定的硬件条件

常见数据库引擎对比

• MyISMA是MySQL的默认存储引擎。MyISMA不支持事务，不支持外键，优势是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用MyISMA引擎。比较适合Web、数据仓储等场景。

• InnoDB存储引擎提供具有提交、回滚和崩溃恢复的事务安全，支持外键。对数据一致性要求比较高或更新比较频繁的的应用可以选择InnoDB。比较适合类似计费和财务系统等准确度要求比较高的系统。

• MEMORY存储引擎-内存数据库，服务重启数据会丢失。适用于那些内容变化不频繁的代码表（常量表），或者作为统计结果的中间结果表。修改的数据不会写入磁盘。

• MERGE存储引擎是一组MyISMA表的组合，这些MyISMA表的结构必须完全相同，MERGE表本身没有数据，对MERGE表的操作实际上是对内部的MyISMA表进行的。较适合数据仓储。

-- 查看数据库支持的存储引擎
show engines;

数据库配置 

MySql官方已经决定在以后的版本中取消缓存技术，可以使用第三方缓存技术（ehcache或者redis等），所以缓存配置就不要看了

​一、慢查询 

• slow_query_log 启动停止技术慢查询日志
• slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
• long_query_time 指定记录慢查询日志SQL执行时间得阈值（单位：秒，默认10秒）
• log_queries_not_using_indexes  是否记录未使用索引的SQL
• log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

配置了慢查询后，它会记录符合条件的SQL包括：

• 查询语句
• 数据修改语句
• 已经回滚得SQL

1.查看慢查询相关参数

show variables like '%slow%'; 

show global status like '%slow%'; 

  配置中打开了记录慢查询，执行时间超过2秒的即为慢查询，系统显示有6593个慢查询 

2.设置方法

将 slow_query_log 全局变量设置为“ON”状态

set global slow_query_log='ON'; 

设置记录方式为table，该表位于mysql数据库slow_log

set global log_output='TABLE';

设置查询记录时间范围

set global long_query_time=1;

3.重启MySQL服务

service mysqld restart

4.测试

 select sleep(10);

如果表记录存在，MySQL开启慢查询设置成功！

二、连接数 

        遇见”MySQL: ERROR 1040: Too many connections”的情况，一种是访问量确实很高，MySQL服务器抗不住；另外一种情况是MySQL配置文件中max_connections值过小：最大可设置16384,超过没用

1.查看参数

查询最大连接数，这台MySQL服务器最大连接数是5000

 show variables like 'max_connections'; 

查询一下服务器响应的最大连接数：

 show global status like 'Max_used_connections';

 MySQL服务器过去的最大连接数是295，没有达到服务器连接数上限，理想配置为
Max_used_connections / max_connections * 100% ≈ 85%

三、Key_buffer_size 

　key_buffer_size是对MyISAM表性能影响最大的一个参数，下面一台以MyISAM为主要存储引擎服务器的配置： 

 show variables like 'key_buffer_size'; 

 分配了290MB内存给key_buffer_size=（304087040/1024平方）

查询key_buffer_size的使用情况： 

show global status like 'key_read%'; 

 一共有398943个索引读取请求，有4672个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：
　　key_cache_miss_rate = Key_reads / Key_read_requests * 100%

key_cache_miss_rate在0.1%以下都很好(每1000个请求有一个直接读硬盘)，若key_cache_miss_rate在0.01%以下的话，key_buffer_size分配的过多，可以适当减少。

MySQL服务器还提供了key_blocks_*参数： 

show global status like 'key_blocks_u%'; 

  Key_blocks_unused表示未使用的缓存簇(blocks)数，Key_blocks_used表示曾经用到的最大的blocks数，比如这台服务器，所有的缓存都用到了，要么增加key_buffer_size，要么就是过渡索引了，把缓存占满了。比较理想的设置： 
　　Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80% 

四、临时表 

show global status like 'created_tmp%'; 

   每次创建临时表，Created_tmp_tables增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数，比较理想的配置是：
　　Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25% 
　　比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% = 1.20%，应该相当好了。我们再看一下MySQL服务器对临时表的配置： 

 show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size'); 

  只有256MB以下的临时表才能全部放内存，超过的就会用到硬盘临时表。

五、表锁情况  

show global status like 'table_locks%'; 

 Table_locks_immediate表示立即释放表锁数，Table_locks_waited表示需要等待的表锁数，如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。示例中的服务器Table_locks_immediate / Table_locks_waited = 235，MyISAM就足够了。

六、索引优化

索引设计原则：

1. 最适合索引的列是在where子句中的列，或连接子句中的列，而不是出现在select关键字后的列
2. 使用唯一索引。考虑某列中值的分布。索引列的基数越大，效果越好（一列中相同的数据越少，索引越好）
3. 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度。这样可以节省索引空间和磁盘IO。（alter tableName add key indexName (columnName(7)) --给表tableName的columnName字段的前7位建立前缀做引，索引名字为indexName）
4. 利用最左前缀。比如创建了一个多列索引 index_c1_c2_c3 (c1,c2,c3)，相当于创建了(c1)单列索引，(c1,c2)的组合做引以及(c1,c2,c3)的组合索引。根据这个原则，在创建多列索引时，要根据业务需求 ，where子句中使用最频繁的一列要放在索引的最左边。
5. 不要过度索引。索引过多，会导致磁盘占用较高，insert和update操作耗时增加，查询优化效率会变低。

以下不会使用索引的几种情况：

1. 以%开头的like查询不能使用索引
2. 数据类型出现隐式转换的不能使用索引。数据INT类型，而用varchar查询
3. 复合索引的情况下，假如查询条件不包含索引列最左边部分，不使用索引
4. 如果MySQL估计使用索引比全表扫描慢，不使用索引
5. 用or分隔开的条件，如果or前的列中有索引，而后边的列中没有索引，不会使用索引。（or的所有条件必须全部使用索引字段才会走索引

索引对查询速度的影响。

        mysql中提高性能的最有效的方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快了查询的速度。因此，索引对查询的速度有着至关重要的影响。使用所以可以快速的定位到表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。如果查询的时候没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询的记录，从而减少查询的记录数，达到提高查询速度的目的。

举例说明，使用索引和不使用索引的区别：

不使用索引rows列的值是26，说明此查询语句扫描了26条记录。

使用索引rows列的值是1，说明此查询语句扫描了1条记录。查询速度自然变快了。这种方法也可以判断自己添加的所以是否起了作用。

 6、使用索引查询，几种特殊情况。

　　索引可以提高查询的速度，但并不是使用带有索引的字段查询时，索引都会起到作用。下面是几种比较特殊的情况。在这些情况下，有可能使用带有索引的字段查询时，索引并没有起到作用。

6.1、使用like关键字的查询语句。

        在使用like关键字进行查询的查询语句中，如果匹配字符串的第一个字符为"%"，索引不会起作用。只有"%"不在第一个位置，索引才会起到作用。

使用模糊查询，”%“在前面的结果，显示索引没有起了作用了的。

使用模糊查询，”%“在后面的结果，显示索引起了作用了的。

6.2、使用多列索引的查询语句。

        mysql可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中第1个字段的时候，索引才会被使用。

多列所有的第一个字段作为查询条件的时候，发现索引起了作用了的:

6.3、使用or关键字的查询语句。

        使用语句的查询条件中只有or关键字，且or前后的两个条件中的列都有索引时，查询中才使用索引。否则，查询将不适用索引。

使用name索引，sex索引，查询出符合条件的。

查看mysql执行sql历史记录

开启&关闭日志模式（可选择输出到表或文件中）

SET GLOBAL log_output = 'FILE'; 
SET GLOBAL general_log = 'ON'; //日志开启（日志输出到文件） 
SET GLOBAL log_output = 'FILE'; 
SET GLOBAL general_log = 'OFF'; //日志关闭 或者 
SET GLOBAL log_output = 'TABLE'; 
SET GLOBAL general_log = 'ON'; //日志开启（日志输出到表：mysql.general_log）
SET GLOBAL log_output = 'TABLE'; （推荐） 
SET GLOBAL general_log = 'OFF'; //日志关闭

查看当前配置

show variables like '%log_output%'; 
show variables like '%general_log%';

查看历史记录

SELECT * from mysql.general_log ORDER BY event_time DESC;

五、排序优化

MySql排序算法的执行方式： 将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个块进行排序，然后将各个块合并成有序的结果集。

优化方案：

1. 尽量减少额外的排序，通过索引直接返回有序数据
2. 适当加大max_length_for_sort_data系统变量，让更多的SQL可以在内存中完成排序，减少磁盘I/O操作。（因为排序区是每个线程独占的，设置过大会导致服务器SWAP严重）
3. 尽量只使用必要的字段，select具体的字段名字，而不是select *，这样可以减少排序区的使用，提高SQL性能
4. MySQL会对GROUP BY后的所有字段排序，group by a1,a2,a3相当于后边默认加了order by a1,a2,a3 ，如果要避免排序带来的消耗，可以使用order by null禁止排序

六、读写分离配置

1.修改master配置文件：

log-bin=mysql-bin        #slave会基于此log-bin来做replication
server-id=1    #master的标示
binlog-do-db = amoeba_study        #用于master-slave的具体数据库

2.添加专门用于replication的用户

mysql> GRANT REPLICATION SLAVE ON *.* TO username@host IDENTIFIED BY 'password';

3.如果库中已有数据，需要记下file和position

mysql> flush tables with read lock;
mysql> show master status;   # 得到file和position
mysql> unlock tables;

4.编辑slave的配置文件，添加server-id

server-id=2    #slave的标示,需要唯一

5.配置生效后，配置与master的连接：

mysql> CHANGE MASTER TO
-> MASTER_HOST='masterhost',
-> MASTER_USER='2中的username',
-> MASTER_PASSWORD='2中的password',
-> MASTER_LOG_FILE='3中的file',
-> MASTER_LOG_POS='3中的posiition';

mysql> start slave;  # 启动从库，开始同步数据

6.安装amoeba，按照官方文档进行配置，这里就不详细介绍了，配置服务端直接连接到amoeba即可

七、表结构优化

垂直拆分
把主键和一些常用的字段放到一个表中，把主键和其他的字段放到另一个表中。
优点：垂直拆分可以使一个数据页放更多的数据，可以较少IO次数。
缺点：查询所需的数据可能需要通过JOIN来查询。
适用场景：表过宽，包含text或blob字段，可以将不常用的列或text/blob列放到另外的表中存储。比如文章表可以将文章内容拆分到另外的表中。

水平拆分
根据某一列的值把数据放到多个独立的表中，比如历史数据放到另一张表里。
优点：减少大多数查询读取的数据量，降低索引层数，提高查询速度。
缺点：增加查询复杂度，查询多个表需要使用UNION，或者通过MERGE表。
适用场景：表中数据量过大，历史数据查询次数很少，比如订单信息、操作记录等。

逆规范化
增加冗余列：在多个表中具有相同的列，避免联合查询
增加派生列：增加的列来自其他表的计算结果，可避免使用函数
重新组表：将经常联合查询的表组成一个表，减少联合查询

MySql性能优化 - 简书

这篇关于Mysql安装、配置调优（二）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！