Mysql数据库优化方案, 查看sql语句执行过程, 索引优化看这一篇就够了

在大中型的项目中, 随着业务的扩张, 用户量的增长, 对于我们项目的性能已提供越来越高的要求, 那么在提升项目性能的时候, SQL优化显得尤为重要.

我们将从以下4个方面对数据库优化方案进行阐述:

1. SQL性能检测

这里可能会有疑问, 我们今天探讨的是SQL优化, 为什么要提到性能检测呢? 原因就是我们想要进行优化, 要知道优化哪条SQL, 优化哪个索引, 所以SQL性能检测必不可少.
所以我们首先要介绍检测工具.

1.1 查看SQL执行频率

查看数据库增删改频次

• 命令: show status like 'Com____'

• 结果:
  

• 上图红色框框代表的是当前数据库这一列增删改查的频次,执行一次, 对应的Value值+1.
  通过这条命令, 我们可以知道当前数据库是以查询为主还是更新为主. 如果是查询为主, 就重点查询; 如果增删改多就重点优化写入操作.

查看影响的行数

• 命令: show status like 'Innodb_rows_%'
  
  代表存储引擎影响的行数, 也是分析当前数据库是查询为主还是增删改为主.

1.2 explain分析执行计划(常用的工具)

通过以上命令show status查询到了效率低的sql后, 可以通过explain命令查看整条select语句的执行过程.

• 命令: explain select * from tb_item where id = 2
• 结果:
  
• 查询结果这些字段是什么意思呢?
  

1. explain 之 id
   id不是主键, 指的的是表结构的查询顺序,id越大,越先被执行, id这个指标参考意义不大, 了解即可.
2. explain 之 select_type
   标识这条sql语句是什么类型的, 参考意义也不大.
   
3. explain 之 type
   这是很重要的一个指标,显示的是访问类型, 也是有取值范围的.
   
   性能从上到下依次降低.all是最差的.
   一般我们至少要达到range级别, 最好是ref.
4. explain 之 key 很重要的指标
   实际用到的索引
5. explain 之 key_len 很重要的指标
   索引长度
6. explain 之 rows 很重要的指标
   该条sql语句执行时扫描的行数.

1.3 show profile分析SQL

show profile可以查看所有sql语句的执行效率(所用时间). 前提是这个命令需要被打开, 严格的说也就是打开这个命令后执行的所有sql语句, 它都能记录下执行时间, 并展示出来.
可以通过这个命令分析哪些sql语句执行效率低. 耗时长, 就更有针对性的优化这条sql.

• 查看当前Mysql是否支持profiling, 如果是YES就代表支持
  select @@have_profiling
  
• 查看profiling转态, 是打开还是关闭, 0代表关闭, 1代表打开
  select @@profiling
  
• 打开profiling set profiling=1
• 然后执行很多sql语句
• 最后执行 show profiles
  
  就可以找到耗时较长的sql语句了.

1.4 慢查询日志(常用的工具)

慢查询日志记录了所有执行时间超过参数 long_query_time的sql语句的日志, long_query_time默认为10秒(可以通过配置文件设置), 慢查询日志默认是关闭的, 使用的时候需要修改配置文件打开.

• 修改Mysql配置文件, 设置慢查询 vi /usr/my.conf, slow_query_log=1代表开启慢查询
• 在配置文件后面添加如下参数.
  
  保存后要重启Mysql.service mysql restart
• 日志保存在 /var/lib/mysql/目录下, 有个slow_query.log文件,
• 查看日志, 如果查询语句超过2秒, 日志中就会记录下来
  cd /var/lib/mysql
tail -f slow_query.log
  

通过慢查询日志, 就能抓取到哪些sql语句执行慢, 这样就能有针对性的进行优化了.

2. 索引优化

说的数据库优化, 其实最主要的优化查询, 也就是提高查询效率. 而优化查询, 最主要的是优化索引.

2.1 索引是什么

Mysql官方对索引的定义: 索引(Index)是帮助Mysql高效获取数据的数据结构(有序). 在数据之外, 数据库系统还维护了满足特定查找算法的数据结构, 这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法, 这种数据结构就是索引.
如下面的示意图所示:

上图的索引视图是一个简单的示意, 索引数据结构并非都是二叉树, 后面会详细介绍~

左边是数据表, 一共有两列7条记录, 最左边的数据记录的是物理地址. 为了加快Col2这一列的查找, 可以维护一个右边所示的二叉查找树, 每个节点分别包换索引键值和一个指向对应数据记录物理地址的指针, 这样就能通过二叉树快速查找到相应的数据.
比如: 我想找22,图1中是从上往下一个一个找, 要找5次才能找到; 而图2中找3次就可以了, 22比34小, 找到5, 22又比5大, 最后定位到了22.

一般来说索引本身也很大, 不可能全部存储在内存中, 因此索引往往以索引文件的形式存储在磁盘上. 索引是数据库用来提高性能最常用的工具.

通俗的说: 索引类似于字典的目录, 如果没有目录(索引), 那么我们要找到指定的字需要从第一个字开始查找到最后一个字才有结果, 可能要把字典中所有的字看一遍才能找到想要的结果; 而目录(索引)则能够让我们快速的定位到这个字的位置, 从而找到我们要的结果.

2.2 索引的优缺点

• 优点:
  1. 类似于书籍的目录索引, 提高数据检索的效率, 降低数据库的IO成本.
  2. 通过索引列对数据进行排序, 降低数据排序的成本, 降低CPU的消耗.
• 缺点:
  1. 实际上索引也是一张表, 该表中保存了主键与索引字段, 并指向实体类的记录, 所以索引列也是要占用空间的.
  2. 虽然索引大大提高了查询效率, 同时却也降低更新表的速度, 如对表进行添加, 修改, 删除操作. 因为更新表的时候, Mysql不仅要保存数据, 还要保存一下索引文件每次更新添加了索引列的字段, 都会调整因为更新所带来的键值变化后的索引信息.

2.3 索引的结构

索引是在Mysql的存储引擎(InnoDB, MyIsam)层中实现的, 而不是在服务层实现的. 所以每种存储引擎的索引都不一定完全相同, 也不是所有的存储引擎都支持所有的索引类型的, Mysql目前提供了以下4种索引:

• B+Tree 索引: 最常见的索引类型, 大部分索引都支持B+树索引.
• Hash 索引: 只有Memory引擎支持, 使用场景简单.
• R-Tree索引(空间索引): 空间索引是MyISAM引擎的一个特殊索引类型, 主要地理空间数据, 使用也很少.
• Full-text(全文索引): 全文索引也是MyISAM的一个特殊索引类型, 主要用于全文索引, InnoDB从Mysql5.6版本开始支持全文索引.
  

2.3.1 BTree结构

B+Tree是在BTree基础上进行演变的, 所以我们先来看看BTree, BTree又叫多路平衡搜索树, 一颗m叉BTree特性如下:

• 树中每个节点最多包含m个孩子.
• 除根节点与叶子节点外, 每个节点至少有[ceil(m/2)] 个孩子(ceil函数指向上取整).
• 若根节点不是叶子节点, 则至少有两个孩子.
• 每个非叶子节点由n个Key和n+1个指针组成, 其中 [ceil(m/2) -1 ] <= n <= m-1.

以5叉BTree为例, key的数量: 公式推导 [ceil(m/2) -1 ] <= n <= m-1. 所以 2 <= n <= 4, 中间节点分裂父节点,两边节点分裂.

2.3.2 B+Tree 结构

B+Tree为BTree的变种, B+Tree与BTree的区别:

• B+Tree的叶子节点保存所有的key信息, 依key大小顺序排列.
• B+Tree叶子节点元素维护了一个单项链表.
• 所有的非叶子节点都可以看作是key的索引部分.
  
  由于B+Tree只有叶子节点保存key信息, 查询任何key都要从root走的叶子. 所以B+Tree查询效率更稳定.

2.3.3 Mysql中的B+Tree

MySql索引数据结构对经典的B+Tree进行了优化, 在原B+Tree的基础上, 增加了一个指向相邻叶子节点的链表指针, 就形成了带有顺序指针的B+Tree, 提高区间访问的性能.

MySql中的B+Tree索引结构示意图:

2.4 索引分类

• 单值索引: 即一个索引只包含单个列, 一个表可以有多个单列索引.
• 唯一索引: 索引列的值必须唯一, 但允许有空值.
• 复合索引(常用): 即一个索引包含多个列.
• 主键索引
• 全文索引

2.5 索引语法(创建索引, 查看索引等语法)

索引在创建表的时间, 就可以同事创建, 也可以创建表后随时增加新的索引.

2.5.1 查看索引

show index from 表名

结果:

Key_name: 索引名称
Column_name: 在哪一列加的索引
index_type: 索引类型, 这里B+Tree显示不出来, 显示的是BTree, 但类型是B+Tree.

2.5.2 创建索引

create index idx_表名_哪一列 on 表名(哪一列)

2.5.3 删除索引

drop index 索引名称 on 表名

2.6 索引设计原则

索引的设计需要遵循一些已有的原则, 这样便于提升索引的使用效率, 更高效的使用索引.

• 对查询频次较高, 且数据量比较大的表, 建立索引.
• 索引字段的选择, 最佳候选列应当从where子句的条件中提取, 如果where子句中的组合比较多, 那么应当挑选最常用, 过滤效果最好的列的组合.
• 如果where后有多个条件经常被用到, 建议建立符合 索引, 复合索引需要遵循最左前缀法则, N个列组合而成的复合索引, 相当于创建了N个索引.
  复合索引命名规则 index_表名_列名1_列名2_列明3
  比如:create index idx_seller_name_sta_addr on tb_seller(name, status, address)
  复合索引需要遵循最左前缀法则, N个列组合而成的复合索引, 相当于创建了N个索引. 最左前缀法则后面会详细介绍~
• 使用唯一索引, 区分度越高, 使用索引的效率越高.
• 索引并非越多越好, 如果该表赠,删,改操作较多, 慎重选择建立索引, 过多索引会降低表维护效率.
• 使用短索引, 提高索引访问时的I/O效率, 因此也相应提升了Mysql查询效率.
• 多表连接的字段上需要建立索引，这样可以极大提高表连接的效率.
• 排序字段上, 因为排序效率低, 添加索引能提高查询效率.

2.7 索引的使用原则

索引是优化查询最主要的手段, 索引在建立的时候有对应的设计原则, 上面提到过的最左前缀原则我们这里详细介绍下. 在使用索引的时候我们也要注意一些原则避免索引失效.

复合索引命名规则: index_表名_列名1_列名2_列明3
比如: create index idx_seller_name_sta_addr on tb_seller(name, status, address)

复合索引创建好了, 剩下的就是查询的时候使用了, 这里重点介绍如何避免索引失效

2.7.1 遵循最左前缀法则

如果在查询的时候, 使用了复合索引, 要遵循最左前缀法则, 也就是查询从索引的最左列开始, 并且不能跳过索引中的列.

如果不包含最左边的索引列, 则其他索引不生效;

如果包含了最左边的索引列, 但是跳过了一列直接索引了复合索引的第三列, 则第三列的索引不生效.

• 索引生效案例

  select * from tb_seller where name = "小米科技" and status = "1" and address = "北京市";
  select * from tb_seller where name = "小米科技";
  select * from tb_seller where name = "小米科技" and status = "1";
  

• 索引失效案例

1. 没有遵循最左前缀法则, 查询时没有查name这一列, 而name这一列在复合索引最左边, 所以索引会失效.

   select * from tb_seller where status = "1" and address = "北京市";
   

2. 查询时有name这一列, 但是跳过了status, 直接查了address, 所以address索引也会失效.

   select * from tb_seller where name = "小米科技" and address = "北京市";
   

   如何查看索引是否生效, 可以通过上个章节的explain + sql语句, 来查看key 和 key_len判断索引是否生效.
   
3. 最左前缀法则和你查询的时候最左边那列放到位置没关系, 只要查询的sql语句中有就行, 不一定查询的时候放到最左边.

2.7.2 范围查询右边的列, 不能使用索引, 否则右边的索引也会失效.

• 索引生效案例

  select * from tb_seller where name = "小米科技" and status = "1" and address = "北京市";
  select * from tb_seller where name = "小米科技" and status >= "1" and address = "北京市";
  

• 索引失效案例

  select * from tb_seller where name = "小米科技" and status > "1" and address = "北京市";
  

  address索引失效, 因为status是大于号, 范围查询.

2.7.3 不要在索引上使用运算, 否则索引也会失效.

比如在索引上使用切割函数, 就会使索引失效.

select * from tb_seller where substring(name, 3, 2) = "科技";

2.7.4 字符串不加引号, 造成索引失效.

如果索引列是字符串类型的整数, 条件查询的时候不加引号会造成索引失效. Mysql内置的优化会有隐式转换.

• 索引失效案例

select * from tb_seller where name = "小米科技" and status = 1

2.7.5 尽量使用覆盖索引, 避免select *, 这样能提高查询效率.

如果索引列完全包含查询列, 那么查询的时候把要查的列写出来, 不使用select *

select sellerid, name, status from tb_seller where name = "小米科技" and staus = "1" and address = "西安市"; 

2.7.6 or关键字连接

用or分割开的条件, 如果or前面的列有索引, or后面的列没有索引, 那么查询的时候索引会失效, 如果一定要用or查询, 可以考虑下or连接的条件列都加索引, 这样就不会失效了.

• 索引失效案例:

select * from tb_seller where name = "小米科技" or createTiem = "2018-01-01 00:00:00";

2.7.7 like模糊查询

• 在使用like模糊查询时, 如果like%也就是%加在后面索引不会失效, 如果%lik或％like%也就是%加在前面, 索引会失效.
• 如果查询的列占整张表的绝大多数, 那么就会全表扫描, 不会走索引. 所以在查询的时候走不走索引不能百分百肯定.
  也就是说如果查找一个稀有数据, 如果建立了索引, 就会走索引. 如果查询的数据表达绝大多数都符合条件, 就全表扫描, 不走索引.

2.7.8 is null 和 is not null

• is null和is not null有时索引失效, 有时索引不失效.
  比如name列, 表中的这一列都是有值的
• 如果查询is null这就是稀有数据, 就会走索引.
• 如果查询is not null, 表里的这列都是is not null都是有值的, 所以就不走索引, 全表扫描.

2.7.9 尽量使用复合索引. 因为一个复合索引包含多个单列索引.

3. 常见SQL语句优化

3.1 优化insert语句

• 批量列插入数据要比单个列插入数据效率高

1. 效率低插入数据
   
2. 优化后效率高插入数据
   

• 主键顺序插入效率高,

1. 效率低插入数据
   
2. 优化后效率高插入数据
   

3.2 优化order by语句

• 在使用order by语句时,尽量使用覆盖索引, 也就是select 后面要查有索引的列, 不要使用select *

1. 如果是select * 使用的是using filesort
2. 如果是覆盖索引, 比如select id, age from emp order by age asc; 这样就是用using index

• 如果一条sql语句中对多个列进行排序, 在业务允许情况下, 尽量同时用升序或同时用降序.

3.3 优化group by语句

• 在我们对某一个字段进行分组的时候, Mysql默认就进行了排序, 但是排序并不是我们业务所需的, 额外的排序会降低效率.
• 所以在用的时候可以禁止排序, 优化后

select age, count(*) from emp group by age order by null;

3.4 优化嵌套(子)查询

尽量避免子查询, 可以将子查询优化为join多表连接查询.

3.5 优化分页查询

类似select * from tb_item limit 49000,10;约往后的页数, 查询效率越低. 因为分页查询相当于把49000+10条数据都查出来, 然后丢弃前49000条. 有两种思路进行优化

• 把id进行排序, 然后作为子表进行查询

select * from tb_item t, (select id from tb_item order by id limit 49000,10) d where t.id = d.id;

• 针对主键自增且id必须连续的情况下,

select * from tb_item where id > 49000 limit 10;

4. 数据库结构优化

4.1 遵循数据库设计三范式

• 一张表只说一件事,也就是一张表只能有一个主题
• 列不可拆分, 也就是一个列属性只能对应一个属性值, 不可以再拆分.
• 不能有传递依赖, 某一列的值不能通过求和,求平均等运算得到. 当然也得看这个值用户是不是经常用到了,如果经常用到就要反第三范式: 也就是把这列设计出来

4.2 优化表结构

• 尽量将表字段定义为NOT NULL约束，这时由于在MySQL中含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得很复杂。
• 对于只包含特定类型的字段，可以使用enum、set 等数据类型。
• 数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型。例如IP地址可以使用int类型。
• 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED。但对整数类型指定宽度，比如INT(11)，没有任何用，因为指定的类型标识范围已经确定。
• VARCHAR的长度只分配真正需要的空间
• 尽量使用TIMESTAMP而非DATETIME，但TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。
• 单表不要有太多字段，建议在20以内
• 合理的加入冗余字段可以提高查询速度。

4.3 表拆分

4.3.1 垂直拆分

垂直拆分按照字段进行拆分，其实就是把组成一行的多个列分开放到不同的表中，这些表具有不同的结构，拆分后的表具有更少的列。
举个例子:

• 例如用户表中的一些字段可能经常访问，可以把这些字段放进一张表里。另外一些不经常使用的信息就可以放进另外一张表里。
• 我们电商项目中spu表分为spu和spu_deitail表, 商品详情下面的规格表分为规格组表和规格参数表.

缺点: 插入的时候使用事务，也可以保证两表的数据一致。缺点也很明显，由于拆分出来的两张表存在一对一的关系，需要使用冗余字段，而且需要join操作。
但是我们可以在使用的时候可以分别取两次，这样的来说既可以避免join操作，又可以提高效率。

4.3.2 水平拆分

水平拆分按照行进行拆分，常见的就是分库分表。以用户表为例，可以取用户ID，然后对ID取10的余数，将用户均匀的分配进这 0-9这10个表中。查找的时候也按照这种规则，又快又方便。

举个例子: 有些表业务关联比较强，那么可以使用按时间划分的。例如每天的数据量很大，需要每天新建一张表。这种业务类型就是需要高速插入，但是对于查询的效率不太关心。表越大，插入数据所需要索引维护的时间也就越长。

4.4 表分区

分区适用于例如日志记录，查询少。一般用于后台的数据报表分析。对于这些数据汇总需求，需要很多日志表去做数据聚合，我们能够容忍1s到2s的延迟，只要数据准确能够满足需求就可以。

MySQL主要支持4种模式的分区：range分区、list预定义列表分区，hash 分区，key键值分区。

录入使用key键值分区：

    CREATE TABLE `test2` (
      `id` int(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
      `name` varchar(100) DEFAULT NULL COMMENT '名称',
      `state` int(1) DEFAULT NULL COMMENT '状态',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    PARTITION BY KEY (id)
    PARTITIONS 10;

通过：docker exec -it ‘mysql容器id‘ /bin/bash 进入容器
进入到mysql数据目录： cd /var/lib/mysql/data/
查看列表：ls -l

4.5 读写分离

大型网站会有大量的并发访问，如果还是传统的数据存储方案，只是靠一台服务器处理，如此多的数据库连接、读写操作，数据库必然会崩溃，数据丢失的话，后果更是不堪设想。这时候，我们需要考虑如何降低单台服务器的使用压力，提升整个数据库服务的承载能力。

我们发现一般情况对数据库而言都是“读多写少”，也就说对数据库读取数据的压力比较大，这样分析可以采用数据库集群的方案。其中一个是主库，负责写入数据，我们称为写库；其它都是从库，负责读取数据，我们称为读库。这样可以缓解一台服务器的访问压力。

MySql自带主从复制功能，我们可以使用主从复制的主库作为写库，从库和主库进行数据同步，那么可以使用多个从库作为读库，已完成读写分离的效果。

4.6 数据库集群

如果访问量非常大，虽然使用读写分离能够缓解压力，但是一旦写操作一台服务器都不能承受了，这个时候我们就需要考虑使用多台服务器实现写操作。

例如可以使用MyCat搭建MySql集群，对ID求3的余数，这样可以把数据分别存放到3台不同的服务器上，由MyCat负责维护集群节点的使用。