MySQL全面优化

Mysql优化

三个方面：

• SQL优化
• 设计优化
• 硬件优化

SQL优化

通过优化SQL语句以及索引来提高MySQL数据库的运行效率

分页优化

SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

优化方案：

• 延迟关联

  先通过where条件提取出主键，再将该表与原数据表关联，通过主键id提取数据行，而不是通过原来的二级索引提取数据行

  SELECT film.film_id,film.description
  FROM film INNER JOIN (
      SELECT film_id FROM film ORDER BY title LIMIT 50,5
  ) AS tmp USING(film_id);
  

• 书签方式

  找到limit第一个参数对应得主键值，再根据这个主键值去过滤并limit（使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用offset）

  SELECT id FROM t LIMIT 10000, 10;
  

  SELECT id FROM t WHERE id > 10000 LIMIT 10;
  

索引优化

正确使用索引

假如我们没有添加索引，那么在查询时就会触发全表扫描，因此查询的数据就会很多，并且查询效率会很低，为了提高查询的性能，我们就需要给最常使用的查询字段上添加相应的索引，这样才能提高查询的性能

1. 建立覆盖索引

   InnoDB使用辅助索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它就没有必要再回表查询了，这就叫覆盖索引

   select name from test where city = '广州';
   

   将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取

   alter table test add index idx_city_name(city,name);
   

2. 在MySQL5.0之前的版本尽量避免使用or查询

   • 在MySQL5.0之前的版本要尽量避免使用or查询，可以使用union或者子查询来替代，因为早期的MySQL版本使用or查询可能会导致索引失效，在MySQL5.0之后的版本中引入了索引合并
   • 索引合并简单来说就是把多条件查询，比如or或and查询对多个索引分别进行条件扫描，然后将他们各自的结果进行合并，因此就不会出现导致索引失效的问题
   • 从Explain执行计划的type列的值是index_merge可以看出MySQL使用索引合并的方式来执行对表的查询

3. 避免在where查询条件中使用!=或者<>操作符

   • SQL中，不等于操作符会导致查询引擎放弃使用索引，引起全表扫描，即使比较的字段上有索引

   解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描

   column <> 'aaa';
   column > 'aaa' or column < 'aaa';
   

4. 注意不要使用Like双百分号

   • Like的%放在前面会不满足最左前缀原则而导致索引失效

5. 适当使用前缀索引

   • MySQL支持前缀索引，我们可以定义字符串的一部分来作为索引

   • 索引越长占用的磁盘空间就越大，那么在相同数据叶能放下的索引值也就越少，这就意味着搜索索引需要的查询时间越长，进而查询的效率就会降低，所以可以适当的选择使用前缀索引，以减少空间的占用和提高查询效率

     //文章的后缀都是固定的“@xxx.com”,这种后面几位为固定值的字段就非常适合定义为前缀索引
     alter table test add index newindex(email(6));
     

   • 使用前缀索引，定义好长度，就可以做到既节省空间，有不用额外增加太多的成本

   • 当也有缺点：MySQL无法利用前缀索引做order by和group by操作，也无法作为覆盖索引

6. 查询具体字段而非全部字段

   • 要尽量避免select *，而是查询需要的字段，这样可以提高查询速度，以及减少网络传输的带宽压力

7. 优化子查询

   • 尽量使用Join语句来代替子查询，因为子查询是嵌套查询，而嵌套查询会创建一张新表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集计较大的子查询，其对查询性能的影响更大

8. 小表驱动大表

   • 要尽量使用小表驱动大表的方式进行查询，也就是如果B表的数据小于A表的数据，那执行的顺序就是先查B表再查A表

     select name from A where id in (select id from B);
     

9. 不要在列上进行运算操作

   • 不要在列字段上进行算术运算或者其他表达式运算，否则可能会导致查询引擎无法正确使用索引，从影响了查询的效率

     select * from test where id + 1 = 50;
     

   • 隐式类型转换

     select * from test where sid = 123456
     

     sid这个字段上有索引，但是explain的结果却显示这条语句会全表扫描，原因在于sid的字符类型是varchar(32)，比较值却是整型，故需要做类型转换

适当增加冗余字段

• 增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所以可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的策略

正确使用联合索引

• 使用了B+树的MySQL数据库引擎，比如InnoDB引擎，在每次查询复合字段时是从左往右匹配的，因此在创建联合索引的时候需要注意索引创建的顺序（满足最左匹配原则）
• 例如，我们创建了一个联合索引是idx(name,age,sex)，当使用姓名+年龄+性别、姓名+年龄、姓名等这种最左前缀查询条件时，就会触发联合索引进行查询，如果不是最左匹配的查询条件，例如，性别+姓名这种查询条件就不会触发联合索引

Join优化

• MySQL的join语句连接表使用的是nested-loop join算法，这个过程类似于嵌套循环，简单来说就是遍历驱动表(外层表)，每读出一行数据，取出连接字段到被驱动表(内层表)里面查找满足条件的行，组成结果行
• 要提高join语句的性能，就要尽可能减少嵌套循环的循环次数
• 一个显著的优化方式就是对被驱动表的join字段建立索引，利用索引能快速匹配到对应的行，避免与内层表每一行记录作比较，极大的减少总循环次数
• 另一个优化点就是连接的时候用小结果集驱动大结果集，在索引优化的基础上能进一步减少嵌套循环的次数
• 如果难以判断哪个是大表，哪个是小表，可以使用inner join连接，MySQL会自动选择小表去驱动大表

避免使用JOIN关联太多的表

• 对于MySQL来讲，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置
• 在MySQL中，对于同一个SQL多关联一个表，就会多分配一个关联缓存，如果一个SQL中关联的表越多，所占用的内存也就越大(容易造成服务器内存溢出的情况，会影响到服务器数据库性能的稳定性)

排序优化

利用索引扫描作排序

MySQL中两种生成有序结果的方式：

1. 对结果集进行排序的操作
2. 按照索引顺序扫描得出的结果自然是有序的

在设计索引的时候，尽可能使用同一个索引既满足排序又用于查找行

select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;
//当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果进行排序

UNION优化

• MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引
• 最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化
• 此除非确实需要去重，一定要使用union all，如果不加all关键字，MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高

慢日志查询

• 出现慢查询通常的排查手段是先使用慢查询日志功能，查询出比较慢的 SQL 语句，然后再通过 Explain 来查询 SQL 语句的执行计划，最后分析并定位出问题的根源，再进行处理
• 慢查询日志指的是在 MySQL 中可以通过配置来开启慢查询日志的记录功能，超过long_query_time值的 SQL 将会被记录在日志中
• 在开启慢日志功能之后，会对 MySQL 的性能造成一定的影响，因此在生产环境中要慎用此功能

设计优化

1. 尽量避免使用NULL

   NULL在MySQL中不好处理，存储需要额外空间，运算也需要特殊的运算符，含有NULL的列很难进行查询优化

   应当指定列为not null，用0、空串或其他特殊的值代替空值，比如定义为int not null default 0

2. 最小数据长度

   越小的数据类型长度通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快

3. 使用最简单数据类型

   简单的数据类型操作代价更低，比如：能使用 int 类型就不要使用 varchar 类型，因为 int 类型比 varchar 类型的查询效率更高

4. 尽量少定义 text 类型

   text 类型的查询效率很低，如果必须要使用 text 定义字段，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率

5. 适当分表、分库策略

   • 分表是指当一张表中的字段更多时，可以尝试将一张大表拆分为多张子表，把使用比较高频的主信息放入主表中，其他的放入子表，这样我们大部分查询只需要查询字段更少的主表就可以完成了，从而有效的提高了查询的效率

   • 分库是指将一个数据库分为多个数据库。比如我们把一个数据库拆分为了多个数据库，一个主数据库用于写入和修改数据，其他的用于同步主数据并提供给客户端查询，这样就把一个库的读和写的压力，分摊给了多个库，从而提高了数据库整体的运行效率

常见类型选择

1. 整数类型宽度设置

   MySQL可以为整数类型指定宽度，例如int(11)，实际上并没有意义，它并不会限制值的范围，对于存储和计算来说，int(1)和int(20)是相同的

2. VARCHAR和CHAR类型

   • char类型是定长的，而varchar存储可变字符串，比定长更省空间，但是varchar需要额外1或2个字节记录字符串长度，更新时也容易产生碎片

   • 需要结合使用场景来选择：如果字符串列最大长度比平均长度大很多，或者列的更新很少，选择varchar较合适；如果要存很短的字符串，或者字符串值长度都相同，比如MD5值，或者列数据经常变更，选择使用char类型

3. DATETIME和TIMESTAMP类型

   datetime的范围更大，能表示从1001到9999年，timestamp只能表示从1970年到2038年。datetime与时区无关，timestamp显示值依赖于时区。在大多数场景下，这两种类型都能良好地工作，但是建议使用timestamp，因为datetime占用8个字节，timestamp只占用了4个字节，timestamp空间效率更高

4. BLOB和TEXT类型

   • blob和text都是为存储很大数据而设计的字符串数据类型，分别采用二进制和字符方式存储

   • 在实际使用中，要慎用这两种类型，它们的查询效率很低，如果字段必须要使用这两种类型，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率

范式化

当数据有较好范式化时，修改的数据更少，而且范式化的表通常要小，可以有更多的数据缓存在内存中，所以执行操作会更快

缺点则是查询时需要更多的关联

• 第一范式：字段不可分割，数据库默认支持

• 第二范式：消除对主键的部分依赖，可以在表中加上一个与业务逻辑无关的字段作为主键，比如用自增id

• 第三范式：消除对主键的传递依赖，可以将表拆分，减少数据冗余

硬件优化

MySQL 对硬件的要求主要体现在三个方面：磁盘、网络和内存

1. 磁盘

   • 磁盘应该尽量使用有高性能读写能力的磁盘，比如固态硬盘，这样就可以减少 I/O 运行的时间，从而提高了 MySQL 整体的运行效率

   • 磁盘也可以尽量使用多个小磁盘而不是一个大磁盘，因为磁盘的转速是固定的，有多个小磁盘就相当于拥有多个并行运行的磁盘一样

2. 网络

   保证网络带宽的通畅（低延迟）以及够大的网络带宽是 MySQL 正常运行的基本条件，如果条件允许的话也可以设置多个网卡，以提高网络高峰期 MySQL 服务器的运行效率

3. 内存

   MySQL 服务器的内存越大，那么存储和缓存的信息也就越多，而内存的性能是非常高的，从而提高了整个 MySQL 的运行效率