MYSQL性能优化

Mysql优化，一方面是找出系统的瓶颈，提高mysql数据库整体的性能，另外一个方面需要合理的结构设计和参数调整，以提高用户操作响应的速度。同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。mysql数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统反应的速度。

一、 优化思路

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

二、常见数据库引擎对比

• MyISMA是MySQL的默认存储引擎。MyISMA不支持事务，不支持外键，优势是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用MyISMA引擎。比较适合Web、数据仓储等场景。
• InnoDB存储引擎提供具有提交、回滚和崩溃恢复的事务安全，支持外键。对数据一致性要求比较高或更新比较频繁的的应用可以选择InnoDB。比较适合类似计费和财务系统等准确度要求比较高的系统。
• MEMORY存储引擎-内存数据库，服务重启数据会丢失。适用于那些内容变化不频繁的代码表（常量表），或者作为统计结果的中间结果表。修改的数据不会写入磁盘。
• MERGE存储引擎是一组MyISMA表的组合，这些MyISMA表的结构必须完全相同，MERGE表本身没有数据，对MERGE表的操作实际上是对内部的MyISMA表进行的。较适合数据仓储。

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

三、数据库配置

四、索引优化

索引的类型
Ø 普通索引：这是最基本的索引类型，没唯一性之类的限制。
Ø 唯一性索引：和普通索引基本相同，但所有的索引列值保持唯一性。
Ø 主键：主键是一种唯一索引，但必须指定为”PRIMARY KEY”。
Ø 全文索引：MYSQL从3.23.23开始支持全文索引和全文检索。在MYSQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。
大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B树中存储。空间列类型的索引使用R-树，MEMORY表支持hash索引。
单列索引和多列索引（复合索引）
索引可以是单列索引，也可以是多列索引。对相关的列使用索引是提高SELECT操作性能的最佳途径之一。
多列索引：MySQL可以为多个列创建索引。一个索引可以包括15个列。对于某些列类型，可以索引列的左前缀，列的顺序非常重要。多列索引可以视为包含通过连接索引列的值而创建的值的排序的数组。一般来说，即使是限制最严格的单列索引，它的限制能力也远远低于多列索引。
最左前缀
多列索引有一个特点，即最左前缀（Leftmost Prefixing）。假如有一个多列索引为key(firstname lastname age)，当搜索条件是以下各种列的组合和顺序时，MySQL将使用该多列索引：firstname，lastname，agefirstname，lastnamefirstname也就是说，相当于还建立了key(firstname lastname)和key(firstname)。
索引主要用于下面的操作：
Ø 快速找出匹配一个WHERE子句的行。
Ø 删除行。当执行联接时，从其它表检索行。
Ø 对具体有索引的列key_col找出MAX()或MIN()值。由预处理器进行优化，检查是否对索引中在key_col之前发生所有关键字元素使用了WHERE key_part_# = constant。在这种情况下，MySQL为每个MIN()或MAX()表达式执行一次关键字查找，并用常数替换它。如果所有表达式替换为常量，查询立即返回。例如：SELECT MIN(key2), MAX (key2) FROM tb WHERE key1=10;
Ø 如果对一个可用关键字的最左面的前缀进行了排序或分组(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。如果所有关键字元素后面有DESC，关键字以倒序被读取。
Ø 在一些情况中，可以对一个查询进行优化以便不用查询数据行即可以检索值。如果查询只使用来自某个表的数字型并且构成某些关键字的最左面前缀的列，为了更快，可以从索引树检索出值。SELECT key_part3 FROM tb WHERE key_part1=1
有时MySQL不使用索引，即使有可用的索引。一种情形是当优化器估计到使用索引将需要MySQL访问表中的大部分行时。(在这种情况下，表扫描可能会更快些）。然而，如果此类查询使用LIMIT只搜索部分行，MySQL则使用索引，因为它可以更快地找到几行并在结果中返回。
合理的建立索引的建议：
(1) 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
(2) 简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
(3) 尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值
这部分是关于索引和写SQL语句时应当注意的一些琐碎建议和注意点。
1.当结果集只有一行数据时使用LIMIT 1
2.避免SELECT *，始终指定你需要的列
从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。
3.使用连接（JOIN）来代替子查询(Sub-Queries)
连接（JOIN）… 之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
4.使用ENUM、CHAR而不是VARCHAR，使用合理的字段属性长度
5.尽可能的使用NOT NULL
6.固定长度的表会更快
7.拆分大的DELETE或INSERT语句
8.查询的列越小越快
Where条件
在查询中，WHERE条件也是一个比较重要的因素，尽量少并且是合理的where条件是很重要的，尽量在多个条件的时候，把会提取尽量少数据量的条件放在前面，减少后一个where条件的查询时间。
有些where条件会导致索引无效：
Ø where子句的查询条件里有！=，MySQL将无法使用索引。
Ø where子句使用了Mysql函数的时候，索引将无效，比如：select * from tb where left(name, 4) = ‘xxx’
Ø 使用LIKE进行搜索匹配的时候，这样索引是有效的：select * from tbl1 where name like ‘xxx%’，而like ‘%xxx%’ 时索引无效
索引设计原则：

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

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

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

五、排序优化

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

• 尽量减少额外的排序，通过索引直接返回有序数据
• 适当加大max_length_for_sort_data系统变量，让更多的SQL可以在内存中完成排序，减少磁盘I/O操作。（因为排序区是每个线程独占的，设置过大会导致服务器SWAP严重）
• 尽量只使用必要的字段，select具体的字段名字，而不是select *，这样可以减少排序区的使用，提高SQL性能
• 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，按照官方文档进行配置，这里就不详细介绍了
7.配置服务端直接连接到amoeba即可

七、表结构优化

垂直拆分
把主键和一些常用的字段放到一个表中，把主键和其他的字段放到另一个表中。
优点：垂直拆分可以使一个数据页放更多的数据，可以较少IO次数。
缺点：查询所需的数据可能需要通过JOIN来查询。
适用场景：表过宽，包含text或blob字段，可以将不常用的列或text/blob列放到另外的表中存储。比如文章表可以将文章内容拆分到另外的表中。
水平拆分
根据某一列的值把数据放到多个独立的表中，比如历史数据放到另一张表里。
优点：减少大多数查询读取的数据量，降低索引层数，提高查询速度。
缺点：增加查询复杂度，查询多个表需要使用UNION，或者通过MERGE表。
适用场景：表中数据量过大，历史数据查询次数很少，比如订单信息、操作记录等。
1.range分区range分区是基于连续的范围值。
2.List分区list是基于确定值的范围，就好比集合。
3.hash分区指的是根据hash运算的模，最终确定在哪一个分区。比如2020/4=0，就落在分区0上。
4.线性hash指的是使用2的幂运算法则。运算起来比较麻烦。但是优点是可以使得数据分布均匀。
逆规范化
增加冗余列：在多个表中具有相同的列，避免联合查询
增加派生列：增加的列来自其他表的计算结果，可避免使用函数
重新组表：将经常联合查询的表组成一个表，减少联合查询

八、SQL优化

explain分析查询

通过对查询语句的分析，可以了解查询语句执行的情况，找出查询语句执行的瓶颈，从而优化查询语句。mysql中提供了EXPLAIN语句和DESCRIBE语句，用来分析查询语句。
EXPLAIN语句的基本语法如下：
EXPLAIN [EXTENDED] SELECT select_options;
使用EXTENED关键字,EXPLAIN语句将产生附加信息。select_options是select语句的查询选项，包括from where子句等等。
执行该语句，可以分析EXPLAIN后面的select语句的执行情况，并且能够分析出所查询的表的一些特征。
例如：EXPLAIN SELECT * FROM user;
explain分析查询使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询
  查询结果进行解释说明：
  a、id：select识别符，这是select的查询序列号。
  b、select_type：标识select语句的类型。
  它可以是以下几种取值：
  b1、SIMPLE（simple）表示简单查询，其中不包括连接查询和子查询。
  b2、PRIMARY（primary）表示主查询，或者是最外层的查询语句。
  b3、UNION（union）表示连接查询的第2个或者后面的查询语句。
  b4、DEPENDENT UNION（dependent union）连接查询中的第2个或者后面的select语句。取决于外面的查询。
  b5、UNION RESULT（union result）连接查询的结果。
  b6、SUBQUERY（subquery）子查询的第1个select语句。
  b7、DEPENDENT SUBQUERY（dependent subquery）子查询的第1个select,取决于外面的查询。
  b8、DERIVED（derived）导出表的SELECT(FROM子句的子查询)。
  c、type：表示查询的表。
  d、type：表示表的连接类型。
  下面按照从最佳类型到最差类型的顺序给出各种连接类型。
  d1、system，该表是仅有一行的系统表。这是const连接类型的一个特例。
  d2、const，数据表最多只有一个匹配行，它将在查询开始时被读取，并在余下的查询优化中作为常量对待。const表查询速度很快，因为它们只读一次。const用于使用常数值比较primary key或者unique索引的所有部分的场合。
  例如：EXPLAIN SELECT * FROM user WHERE id=1;
  d3、eq_ref，对于每个来自前面的表的行组合，从该表中读取一行。当一个索引的所有部分都在查询中使用并且索引是UNIQUE或者PRIMARY KEY时候，即可使用这种类型。eq_ref可以用于使用“=”操作符比较带索引的列。比较值可以为常量或者一个在该表前面所读取的表的列的表达式。
  例如：EXPLAIN SELECT * FROM user,db_company WHERE user.company_id = db_company.id;
  d4、ref对于来自前面的表的任意行组合，将从该表中读取所有匹配的行。这种类型用于所以既不是UNION也不是primaey key的情况，或者查询中使用了索引列的左子集，即索引中左边的部分组合。ref可以用于使用=或者<=>操作符的带索引的列。
  d5、ref_or_null，该连接类型如果ref，但是如果添加了mysql可以专门搜索包含null值的行，在解决子查询中经常使用该连接类型的优化。
  d6、index_merge，该连接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
  d7、unique_subquery，该类型替换了下面形式的in子查询的ref。是一个索引查询函数，可以完全替代子查询，效率更高。
  d8、index_subquery，该连接类型类似于unique_subquery，可以替换in子查询，但是只适合下列形式的子查询中非唯一索引。
  d9、range，只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。key_len包含所使用索引的最长关键元素。当使用=，<>,>,>=,<,<=,is null,<=>，between或者in操作符，用常量比较关键字列时，类型为range。
  d10、index,该连接类型与all相同，除了只扫描索引树。着通常比all快，引文索引问价通常比数据文件小。
  d11、all，对于前面的表的任意行组合，进行完整的表扫描。如果表是第一个没有标记const的表，这样不好，并且在其他情况下很差。通常可以增加更多的索引来避免使用all连接。
  e、possible_keys:possible_keys列指出mysql能使用那个索引在该表中找到行。如果该列是null，则没有相关的索引。在这种情况下，可以通过检查where子句看它是否引起某些列或者适合索引的列来提高查询性能。如果是这样，可以创建适合的索引来提高查询的性能。
  f、key：表示查询实际使用到的索引，如果没有选择索引，该列的值是null，要想强制mysql使用或者忽视possible_key列中的索引，在查询中使用force index、use index或者ignore index。
  g、key_len：表示mysql选择索引字段按照字节计算的长度，如果健是null，则长度为null。注意通过key_len值可以确定mysql将实际使用一个多列索引中的几个字段。
  h、ref:表示使用那个列或者常数或者索引一起来查询记录。
  i、rows:显示mysql在表中进行查询必须检查的行数。
  j、Extra:该列mysql在处理查询时的详细信息。主要有以下几种
  nusing index：只用到索引,可以避免访问表.nusing where：使用到where来过虑数据. 不是所有的where clause都要显示using where. 如以=方式访问索引.nusing tmporary：用到临时表nusing filesort：用到额外的排序. (当使用order by v1,而没用到索引时,就会使用额外的排序)nrange checked for eache record(index map:N)：没有好的索引。
  CRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。DESCRIBE语句的语法形式如下所示：DESCRIBE SELECT select_options。DESCRIBE可以缩写成DESC。

show status命令

Mysql中，可以使用SHOW STATUS语句查询一些Mysql数据库的性能参数。SHOW STATUS语句语法如下所示：
SHOW STATUS LIKE ‘value’;
其中，value是要查询的参数值，一些常用的性能参数如下：
a、Connections，连接mysql服务器的次数。
b、Uptime，mysql服务器的上线时间。
c、Slow_queries,慢查询的次数。
d、Com_select，查询操作的次数。
e、Com_insert，插入操作的次数。
f、Com_update，更新操作的次数。
g、Com_delete，删除操作的次数。

1 -- 查询mysql服务器的连接次数
2 SHOW STATUS LIKE 'Connections';
3 
4 -- 查询mysql服务器的慢查询次数。慢查询次数参数可以结合慢查询日志，找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。
5 SHOW STATUS LIKE 'Slow_queries';

profiling分析查询

通过慢日志查询可以知道哪些SQL语句执行效率低下，通过explain我们可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。如果觉得explain的信息不够详细，可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。profiling默认是关闭的。可以通过以下语句查看

select @@profiling

打开功能： mysql>set profiling=1; 执行需要测试的sql 语句
mysql> show profiles\G; 可以得到被执行的SQL语句的时间和ID
mysql>show profile for query 1; 得到对应SQL语句执行的详细信息
测试完毕以后 ，关闭参数：mysql> set profiling=0。