MySQL调优


阅读数: 次   

MySQL调优

MySQL优化主要分为以下四大方面：

  1. 设计：存储引擎、字段类型、范式与逆范式
  2. 功能：索引、缓存、分区分表
  3. 架构：主从复制、读写分离、负载均衡、集群
  4. 合理SQL：测试、经验

索引

索引的出现是为了改善查询性能的，是帮助MySQL高效获取数据的数据结构。索引是在存储引擎层实现的，所以没有统一的索引标准，不同存储引擎的索引工作方式不同，也不是所有的引擎都支持所有的索引类型。

索引分类

  1. 主键索引

主键是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引，并且一个表只能有一个主键。

  1. 普通索引

MySQL 允许对两个或两个以上的列上创建索引，我们习惯上称之为联合索引(又叫复合索引，或者组合索引)。实际上，单列索引可以看成联合索引元素数为1的特例。

  1. 唯一索引

唯一索引列的值必须唯一，但允许有空值。如果是联合索引，则列值的组合必须唯一。

  1. 全文索引

全文索引是一种特殊类型的索引，通过建立倒排索引，快速匹配文档的方式。它查找的是文本中的关键字，而不是直接比较索引中的值。

正排索引：以文档id作为关键字

倒排表以字或词为关键词进行索引，表中关键字所对应的记录表项记录了出现这个字或词的所有文档。由属性值来确定记录的位置，比如说通过关键词检索文档。

倒排索引跟B+树一样，也是一种数据结构。一般利用关联数组，在辅助表中存储单词与文档中所在位置的映射。

注：此处有一个问题，为什么不使用like+%，因为这种查询方式不能够使用索引，因此检索效率极低。

全文索引跟其它几种索引类型的匹配方式完全不一样。它有许多需要注意的细节，比如停用词、词干和复数、布尔搜索等。另外，只有字段类型为 char、varchar 和 text 的字段才能设置全文索引。

创建全文索引：

① 在创建表时创建全文索引

1
2
3
4
5
6
7
create table fulltext_test (
    id int(11) NOT NULL AUTO_INCREMENT,
    content text NOT NULL,
    tag varchar(255),
    PRIMARY KEY (id),
    FULLTEXT KEY content_tag_fulltext(content,tag)  // 创建联合全文索引列
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

② 在已存在的表上创建全文索引

1
2
create fulltext index content_tag_fulltext
    on fulltext_test(content,tag);

③ 通过SQL语句创建全文索引

1
2
alter table fulltext_test
    add fulltext index content_tag_fulltext(content,tag);

全文索引使用match和against关键字来进行检索

1
2
select * from fulltext_test 
    where match(content,tag) against('xxx xxx');
  1. 空间索引

空间索引用于地理数据存储，无需前缀查询，而是会从所有维度来索引数据。

创建索引

  1. 使用 CREATE INDEX 语句（不能用这个创建主键索引）

    1
    create<索引名> on <表名>（<列名>[<长度>][ASC|DESC]

  2. 使用 CREATE TABLE语句（在创建表的同时指定某列或者某几列为某种索引类型）

    1
    2
    3
    4
    5
    6
    7
    create table fulltext_test (
        id int(11) NOT NULL AUTO_INCREMENT,
        content text NOT NULL,
        tag varchar(255),
        PRIMARY KEY (id),
        FULLTEXT KEY content_tag_fulltext(content,tag)  // 创建联合全文索引列,content_tag_fulltext为索引别名
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

  3. 使用 ALTER TABLE语句

    1
    ALTER TABLE <表名> ADD INDEX [<索引名>][<索引类型>](<列名>,...)

删除索引

1
DROP INDEX [indexName] ON mytable;
1
ALTER TABLE testalter_tbl DROP INDEX (c);

MyISAM索引 和 InnoDB

索引 实现

  1. MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址。下图是 MyISAM 索引的原理图：

这里设表一共有三列，假设我们以 Col1 为主键，则上图是一个 MyISAM 表的主索引(Primary key)示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中，主索引和辅助索引(Secondary key)在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引，则此索引的结构如下图所示：

img

同样也是一颗 B+Tree，data 域保存数据记录的地址。因此，MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。

MyISAM 的索引方式也叫做“非聚集”的，之所以这么称呼是为了与 InnoDB 的聚集索引区分。

  1. 虽然 InnoDB 也使用 B+Tree 作为索引结构，但具体实现方式却与 MyISAM 截然不同。

第一个重大区别是 InnoDB 的数据文件本身就是索引文件。从上文知道，MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。

img

上图是 InnoDB 主索引(同时也是数据文件)的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键(MyISAM 可以没有)，如果没有显式指定，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域。例如，下图为定义在 Col3 上的一个辅助索引：

img

如何选择

  1. InnoDB支持事务，MyISAM不支持
  2. InnoDB支持外键，MyISAM不支持
  3. MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
  4. InnoDB不支持FULLTEXT类型的索引
  5. InnoDB中不保存表的行数，如select count( * ) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count( * )语句包含where条件时MyISAM也需要扫描整个表
  6. InnoDB支持行锁
  7. 清空整个表时，InnoDB是一行一行的删除，效率非常慢；MyISAM则会重建表

如果应用程序一定要使用事务，毫无疑问你要选择INNODB引擎。但要注意，INNODB的行级锁是有条件的。在where条件没有使用主键时，照样会锁全表。比如DELETE FROM mytable这样的删除语句。但是如果应用程序对查询性能要求较高，就要使用MYISAM了。MYISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MYISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。

索引失效

  1. where语句中包含or时（使用or时并不是一定会索引失效，需要看or左右两边的查询列是否命中相同的索引）

    1
    2
    3
    4
    %如果user_id列有索引，这条语句就是可以命中索引的
    select * from `user` where user_id = 1 or user_id = 2
    %而无论age列是否有索引，都是无法命中索引的
    select * from `user` where user_id = 1 or age = 20;

  2. where语句中索引列使用了负向查询，可能会导致索引失效（但也不一定会失效，这要看Mysql优化器的判断）

    负向查询包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE

  3. 索引字段可以为null，使用 is null 或者 is not null 时，可能会导致索引失效

    单个索引其实使用is null或者is not null时，是可以命中索引的，但使用or进行连接时，就会索引失效了，这应该属于第一种索引失效的情况；但是有null值的字段会使索引比较更加复杂，使数据库的处理性能降低很多。

  4. 在索引列上使用内置函数，一定会导致索引失效

    比如下面语句中索引列login_time上使用了函数，会索引失效：

    1
    select * from `user` where DATE_ADD(login_time, INTERVAL 1 DAY) = 7;

    4.1 隐式类型转换导致的索引失效

    比如下面语句中索引列user_id为varchar类型，不会命中索引

    1
    select * from `user` where user_id = 12;

    这是因为MySQL做了隐式类型转换，调用函数将user_id做了转换。

    1
    select * from `user` where CAST(user_id AS signed int) = 12;

    4.2 隐式字符编码转换导致的索引失效

    当两个表之间做关联查询时，如果两个表中关联的字段字符编码不一致的话，MySQL可能会调用CONVERT函数，将不同的字符编码进行隐式转换从而达到统一。作用到关联的字段时，就会导致索引失效。

    比如下面这个语句，其中d.tradeid字符编码为utf8，而l.tradeid的字符编码为utf8mb4。因为utf8mb4是utf8的超集，所以MySQL在做转换时会用CONVERT将utf8转为utf8mb4。简单来看就是CONVERT作用到了d.tradeid上，因此索引失效。

    1
    select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

  5. 对索引列进行运算，一定会导致索引失效

运算如+，-，*，/等，如下：

1
select * from `user` where age - 1 = 10;

优化的话，要把运算放在值上，或者在应用程序中直接算好，比如：

1
select * from `user` where age = 10 - 1;

  1. like通配符可能会导致索引失效

    like查询以%开头时，会导致索引失效。解决办法有两种：

    将%移到后面，如：

    1
    select * from `user` where `name` like '李%';

    利用覆盖索引(覆盖索引指select的数据列只用从索引中就能够取得，不必在数据库中读取)来命中索引。

    1
    select name from `user` where `name` like '%李%';

  2. 联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效

  3. MySQL优化器的最终选择，不走索引

    上面有提到，即使完全符合索引生效的场景，考虑到实际数据量等原因，最终是否使用索引还要看MySQL优化器的判断。当然你也可以在sql语句中写明强制走某个索引。

mysql一般在哪些字段上建立索引

  1. 表的主键和外键建立索引

    当对父表进行更新时，如果在子表中的外键没有使用外键，会在更新的过程中将整个子表锁定，而实际上并不需要锁定整个子表，而仅仅需要锁定子表中的几条记录。这样就会大大影响数据库访问的并发性，并且有可能造成死锁的情况。

    因此只有在外键不需要更新或表数据不需要删除时才不需要建立索引。因为一旦发生了上述两种情况，就会导致子表的全表查询

  2. 在order by和group by后建立索引

  3. 数据量超过300的表应该建立索引

  4. 经常与其他表进行连接的表的字段，应该在该字段上建立索引。

  5. 经常出现在where子句中的字段建立索引

  6. 不应该在字段比较长的字段上建立索引，因为会消耗大量的空间

  7. 对于经常频繁进行修改和插入的表应该少建立索引，因为在修改和插入之后，数据库会去维护索引，会消耗资源

MySQL优化

MySQL优化主要分为以下四大方面：

  1. 设计：存储引擎、字段类型、范式与逆范式
  2. 功能：索引、缓存、分区分表
  3. 架构：主从复制、读写分离、负载均衡、集群
  4. 合理SQL：测试、经验

设计

存储引擎

MySQL中主要的存储引擎有InnoDB（5.7版本中默认）、MyISAM、MEMORY、CSV等

  1. InnoDB的关键属性包括：

1）ACID事务特性支持，包括commit，rollback以及crash恢复的能力；

2）行级别锁以及多版本并发控制MVCC；

3）利用主键的聚簇索引（clustered index）在底层存储数据，提升对主键查询的IO性能；

4）支持外键功能，管理数据的完整性。

5）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度

  1. MyISAM

1）不支持事务

2）锁粒度是表级的

3）通过key_buffer_size缓存索引，大大提高访问性能，减少产品IO。只会缓存索引，不缓存数据。

4）读取速度快，占用资源少

5）不支持外键约束，但支持全文索引

字段类型

字段类型应该要满足需求，尽量要满足以下需求。

尽可能小（占用存储空间少）、尽可能定长（占用存储空间固定）、尽可能使用整数

MySQL支持的字段类型大致分为：

  1. 数值类型

    1）预估存储的数据的取值范围，选取最合适的数据类型，例如枚举类用TINYINT

    2）当确定字段只有正数的时候，unsigned一定要带上，一方面是向其他开发者说明业务要求，另一方面是能够利用好存储空间。

    注：int(10)其中的10只是显示宽度()，对存储并无影响

  2. 时间日期类型

    绝大多数的业务场景还是使用 YYYY-MM-DD HH:MM:SS 的格式来进行存储

    datetime和timestamp我们该如何选择？

    timestamp只需要4个字节的存储空间，相比于datetime有很多的优势，如果考虑到存储空间大小时，同等情况下，选择timestamp会更省空间。

    但是timestamp只能存储1970到2038年的时间

  3. 字符串类型

    varchar 可变长，更加有效的利用空间

    char 一般用来存储定长字符串 像MD5后的密码

范式与反范式

三大范式

  1. 第一范式：列具有原子性，列不可再分

    例如地址，可以拆成省、市、区甚至更细分，并不具有原子性

  2. 第二范式：在满足第一范式的前提下，非主键列都依赖于主键

    要求表中的每列都与主键相关，但不能只与主键的某部分相关（针对联合主键）

    考虑一个订单明细表：【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName）。
    因为我们知道在一个订单中可以订购多种产品，所以单单一个 OrderID 是不足以成为主键的，主键应该是（OrderID，ProductID）。显而易见 Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。
    可以把【OrderDetail】表拆分为【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName）来消除原订单表中UnitPrice，ProductName多次重复的情况。

  3. 第三范式：非主键列都直接依赖主键，不存在传递依赖。即不能存在：非主键列A依赖于非主键B，非主键B依赖于主键的情况

    考虑一个订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID）。
    其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主键列都完全依赖于主键（OrderID），所以符合 2NF。不过问题是 CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。
    通过拆分【Order】为【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）从而达到 3NF。

  4. 反范式：违反范式规则的数据库设计理念

范式的优点与缺点

优点：

​ ① 范式化的更新操作通常比反范式化要快，只需要修改较少数据

​ ② 当数据较好的范式化时，就只有很少或没有重复数据，所以只需要修改更少的数据

​ ③ 范式化的表通常更小，可以更好的存放在内存中，所以执行操作会更快

​ ④ 很少有多余的数据意味着检索列表数据时更少需要distinct或者group by语句

缺点：

​ ① 通常表需要关联，稍微复杂一些的查询语句在符合范式的表上都可能需要至少一次的关联，可能会更多。这不但代价昂贵，也可能是一些索引策略无效。

​ ② 在执行数据库语句时，使得需要在一个索引中又排序又过滤。

反范式的优点与缺点

优点：

​ ① 反范式化的表因为所有的数据都在一张表中，可以很好的避免关联

​ ② 对大部分查询最差的情况，即使表没有使用到索引，当数据比内存大时这可能比关联要快的多，因为这样避免了随机IO，一般全表查询是顺序IO。

缺点：

​ 表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失。

规范化越高，那么产生的关系就越多，关系过多的直接结果就是导致表之间的连接操作越频繁，而表之间的连接操作是性能较低的操作，直接影响到査询的速度，所以，对于査询较多的应用，就需要根据实际情况运用逆规范化对数据进行设计，通过逆规范化来提高査询的性能。

反规范的好处是降低连接操作的需求、降低外键和索引的数目，还可能减少表的数目，相应带来的问题是可能出现数据的完整性问题。加快查询速度，但会降低修改速度。因此，决定做反规范时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点，好的索引和其他方法经常能够解决性能问题，而不必采用反规范这种方法。

功能

索引

  1. 选择在什么样的字段上建立索引

  2. 索引失效的条件

  3. 索引优化

    ① 建立聚集索引，聚集索引可以极大的提高查询速度

    ② 常查询数据建立索引或者组合索引

    ③ 最左前缀原则

    ④ 较长的数据列建立前缀索引

    ⑤ 不要建立无意义的索引

缓存

​ 缓存之所以有效，主要是因为程序运行时对内存和外存的访问呈现局部性特征，局部特征性为空间局部性和时间局部性两方面。时间局部性是指刚刚访问过的数据近期可能会再次被访问，空间局部性是指，某个位置被访问后，其相邻的位置的数据很可能被访问到，而MySQL的缓存机制就是把刚刚访问的数据以及未来即将访问到的数据保存到缓存中，甚至是高速缓存中，从而提高IO效率。

按照缓存读写功能的不同，MySQL将缓存分为Buffer缓存和Cache缓存。

Buffer缓存。由于硬盘的写入速度过慢，或者频繁的I/O，对于硬盘来说是极大的效率浪费。那么可以等到缓存中储存一定量的数据之后，一次性的写入到硬盘中。Buffer 缓存主要用于写数据，提升I/O性能。

Cache 缓存。 Cache 缓存一般是一些访问频繁但是变更较少的数据，如果Cache缓存已经存储满，则启用LRU算法，进行数据淘汰。淘汰掉最远未使用的数据，从而开辟新的存储空间。不过对于特大型的网站，依靠这种策略很难缓解高频率的读请求，一般会把访问非常频繁的数据静态化，直接由nginx返还给用户。程序和数据库I/O设备交互的越少，则效率越高。

分区分表

分表的类型

  1. 垂直分表

    对原始表的列进行拆分，根据一定的拆分规则，把一张表的列，拆分成多张表。例如将原始表中不常用的列拆分到一张表，将一些存储大数据量的字符拆到一张表，把经常使用的列拆分到一张表。

    垂直分表只是拆分了原始表的列，没有减少表的行，原来是多少行，还是多少行。

  2. 水平分表

    对行进行拆分，根据一定的拆分规则，把原先在一张表中的数据，分表存储到多张表中。如根据id取模，hash值，按日期月份等

    这样将原本存储在一张表中的数据，被分开存储到不同的表中，大大降低了单表的数据量。

分区的类型

分区的意思是将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件。

  1. 垂直分区

    根据表中的列，进行垂直划分，使某些特定的列数据被集中在某一个分区中。如根据数据列使用频率

  2. 水平分区

    根据表中的行进行分区，把表中满足某种特性的数据，集中保存在某一个分区中。

分区的好处
  • 可以让单表存储更多的数据
  • 分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
  • 部分查询能够从查询条件确定只落在少数分区上，速度会很快
  • 分区表的数据还可以分布在不同的物理设备上，从而搞笑利用多个硬件设备

架构

主从复制

读写分离

集群

合理SQL

测试

经验

服务器的硬盘、CPU、内存、网络都有影响到MySQL的性能。

赏

谢谢你请我吃糖果

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

永远热爱生活的<br><br>一个程序员