这是给公司培训的资料，主要是讲解数据库可以优化的点，然后主要说明跟开发相关数据表设计、索引添加、数据库参数调优。

MySql数据库优化方案

一、数据库设计优化

1. 根据业务需求选择正确的存储引擎，如果对事务性要求高就用InnoDB（默认），读多写少尽量使用MyISAM。

2. 设置合理的字段类型及字段长度，比如说你这个字段就20多个字段你设置成VARCHAR(255)就是对磁盘空间的浪费。

3. 默认值尽可能的使用 NOT NULL，可以通过设置默认值避免NULL值。

4. 尽量少的使用VARCHAR，TEXT，BLOB这三个字段。

5. 添加适当索引(index) [普通索引、主键索引、唯一索引unique、全文索引]。

6. 不要滥用索引，大表索引，小表不索引。

7. 表的设计合理化（符合3NF）。

二、SQL语句优化

1. 优化利器 – EXPLAIN

   EXPLAIN
   SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'iacl_cmp' 
   UNION SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'iacl_cmp' 
   UNION SELECT COUNT(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'iacl_cmp';
   

   运行结果

主要关注4个指标：

• type：优化目标至少达到range级别，要求是ref级别，如果可以const最好。

  • type 性能从高到低

    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    

  Type类型	说明
  system	表内只有一行数据
  const	常量连接，表最多只有一行匹配，通用用于主键或者唯一索引比较时
  eq_ref	常量连接，表最多只有一行匹配，通用用于主键或者唯一索引比较时
  ref	如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键
  fulltext	全文搜索
  ref_or_null	与ref类似，但包括NULL
  index_merge	表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）
  unique_subquery	在in子查询中，就是value in (select…)把形如select unique_key_column的子查询替换。PS：所以不一定in子句中使用子查询就是低效的！
  index_subquery	同上，但把形如”select non_unique_key_column“的子查询替换
  range	常数值的范围
  index	索引树扫描。a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；d.如单独出现，则是用读索引来代替读行，但不用于查找
  all	全表扫描(full table scan)

• key：是查询使用到的索引，如果此列为空，要么未建立索引，要么索引失效。

• rows：是这条SQL语句扫描的行数，越少越好。

• extra：此列为扩展列，如果出现临时表、文件排序则需要优化。

2. where、= 条件避免类型转换

   • 入参类型在传入前转换成跟字段类型一致
   • 内关联避免也要避免类型不一致做过滤

3. 避免使用 select *

4. like引起索引失效，尽量满足最左匹配原则，建议这样写：

   where display_name like '开始%';
   

5. 不要在where条件中带有函数计算、类型转换

6. 关于in和exist，如果查询的俩个表大小一致则性能差别可忽略，如果子查询表大用exist，否则使用in

7. 预知查询一条数据要加上limit 1，以便查到数据终止扫描

8. 关联查询尽量不要超过 3 个表关联

9. 数据字段更新频繁、重复度高不适合建索引

10. explain中的type至少要达到range，建议为ref

11. 尽量用join代替子查询

12. 尽量用union all代替union

13. 数据优化常用命令

    • 查看Mysql服务器的链接

      show full processlist;
      

    • 查询慢查询次数

       show status like 'slow_queries';
      

    • 查看慢查询设置的时间 ，默认10s

       show variables like 'long_query_time';
      

    • 设置慢查询时间、

      set long_query_time = 1;
      

    • 查看建表语句

      show create table wf_node;
      

    • 查看表的字段

      desc wf_node;
      

    • 查看表索引

      show indexes from wf_node;
      

    • 创建索引

      • 主键索引

        alter table wf_node add primary key(id);
        

      • 唯一索引

        alter table wf_node add UNIQUE (gmt_create);
        

      • 普通索引

        alter table wf_node add index idx_display_name (display_name);
        

      • 复合索引

        ALTER TABLE wf_node ADD INDEX idx_display_name_gmt_create (display_name, gmt_create);
        

    • 删除索引

      drop index idx_display_name on wf_node;
      

    • 重建索引

      OPTIMIZE TABLE wf_node;
      

三、基础设施优化

1. 硬件优化
   • CPU：配置多核心和频率高的cpu,多核心可以执行多个线程
   • 内存：配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.
   • 磁盘：配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.
2. 数据库本身参数配置

   • key_buffer_size：索引缓冲区大小

   • table_open_cache：指定表高速缓存的大小

   • query_cache_size和query_cache_type：前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE

   • sort_buffer_size：排序缓冲区

四、数据库架构优化

1. 增加缓存

   • 给数据库增加缓存系统，把热数据缓存到内存中，如果缓存中有请求的数据就不再去请求MySQL，减少数据库负载。

2. 主从复制与读写分离

   • 在生产环境中，业务系统通常读多写少，可部署一主多从架构，主数据库负责写操作，并做双机热备，多台从数据库做负载均衡，负责读操作

3. 分表

   分表分为垂直拆分和水平拆分：

   • 垂直拆分：把原来的一个很多字段的表拆分多个表，解决表的宽度问题。你可以把不常用的字段单独放到一个表中，也可以把大字段独立放一个表中，或者把关联密切的字段放一个表中。
   • 水平拆分：把原来一个表拆分成多个表，每个表的结构都一样，解决单表数据量大的问题。

4. 分区

   • 分区就是把一张表的数据根据表结构中的字段（如range、list、hash等）分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上，分区后，表面上还是一张表，但数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘I/O读写性能。