本文属于大纲，不叙述细节。涉及优化概念、库表设计、索引知识、sql语句优化、索引优化汇总、事务、锁等涵盖所有mysql知识点。

计算机/软件设计还属于知道不知道范畴，不属于懂不懂范畴，计算机软件相关专业（编程基础）的同学都能通过各种方式找到并知道每个章节的细节，并应用。

目录

优化概念

优化，已经不仅仅是性能上的优化，是一个让更好的概念。涵盖功能、性能、安全、友好上的调整。

库表设计

范式

范式是设计高效优雅库表的一个理论基础。

数据库设计课本都会讲到范式，范式是理想化的理论基础，基于这个理论基础，结合项目实际情况，又提出反范式。

第一范式：不重复列、不重复行、列不可再分

第二范式：有主键，数据不冗余

第三范式：非主键与主键直接关系，不应存在传递依赖

这里只介绍常谈起的三大范式，bcnf、4nf等范式略过。

事物具有两面性，范式带来好处的同时，也会有一些不足：

  • 范式越高、库表越不灵活
  • 范式越高、库表性能越低

反范式：适当数据冗余，适当唯一关联

范式：牺牲时间效率，精简空间

反范式：牺牲空间，换取时间效率

范式与反范式都不极端化，只要合适合理就可以用，实践形成理论，理论指导实践。这里不叙述。

引擎

mysql提供了myisam、innodb、memory等内置引擎或引擎插件，目前大部门项目默认使用innodb。

可以了解为什么现在少用myisam，当然还是会有一些场景会用到，比如日志备份；内存级引擎也会其需要使用的场景。

innodb优势：

  • 事务、备份、崩溃恢复
  • 表锁与行锁
  • 外键约束
  • 数据与索引在同一个文件
  • 表空间更大
  • 扩展性好、并发高、数据安全

innodb需要完善：

  • 全文索引
  • count(*)
  • 插入/更新数据相对慢

由于现代业务的复杂度，需要库表支持事务和锁，innodb当然是更适合引擎。另外由于聚簇索引innodb比myisam快，不要迷信 myisam比innodb快。

日志型应用：myisam 开销低、插入快

订单处理：innodb

后续章节我们默认使用innodb引擎。

更多细节这里不叙述。

字符集与校对规则

字符集很多，常用的不多：

ASCII字符集：基于罗马字母表的一套字符集，它采用1个字节的低7位表示字符，高位始终为0。

LATIN1字符集：相对于ASCII字符集做了扩展，仍然使用一个字节表示字符，但启用了高位，扩展了字符集的表示范围。

GBK字符集：支持中文，字符有一字节编码和两字节编码方式。

UTF8字符集：Unicode字符集的一种，是计算机科学领域里的一项业界标准，支持了所有国家的文字字符，utf8采用1-4个字节表示字符。

移动互联网时代，出现各种特殊符号、颜文字、emoji表情等，utf8mb4可以很好才存储这些符号并不出现乱码的提供给客户端。

这里不叙述什么是字符集，也不叙述mysql的客户端、连接、库表、操作使用的字符集设置。

一般建议采用utf8或utf8mb4。

校对规则是在字符集内用于字符比较和排序的一套规则，比如有的规则区分大小写，有的则无视。

校对规则特征：

　　- 两个不同的字符集不能有相同的校对规则；

　　- 每个字符集有一个默认校对规则；

　　- 存在校对规则命名约定：以其相关的字符集名开始，中间包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束。

比如常用的utf8_general_ci、utf8mb4_general_ci

建议：utf8mb4字符集、utf8mb4_general_ci核对规则

主键

主键其实不一定是要字段id，int或bigint，如果没有设置主键，mysql会自动生成一个6字节的隐藏主键，用于聚簇索引，当然最好是设置主键，毕竟业务逻辑直接使用主键查询是最快的。

因为部分开发不知道是否需要或怎么设置合适的主键，建议默认创建id主键，int(11)或bigint，自增、非空。

建议：前面说到了使用主键查询最快，这是因为innodb采用B+树索引，主键默认是聚簇索引，改索引树的叶子结点除了id外，包含id对应记录的所有数据。假设是3阶索引树，只需要3次IO操作即可得到id对应所有记录数据。

这里暂时不叙述联合主键等细节问题。

外键

InnoDB是mysql唯一支持外键的内置存储引擎。

使用外键是有成本的。比如每次修改数据时，都要在另一张表多执行一次查询操作。

InnoDB外键强制使用索引，如果外键的选择性很低，则会导致一个非常大且选择性低的索引。

外键的主要目的是保证两个相关表的数据一致，不过外键维护操作时逐行进行的，所以这样的更新会比批量删除和更新要慢。

如果只是使用外键做约束，那通常在应用逻辑里实现该约束会更好。

外键会带来很大的额外消耗。

建议：外键是一个确保系统完整性的额外特性，但如果设计的是一个高性能的系统，那么外键就显示臃肿。

字段数量

  • 太多的列：极限是几千个字段；
  • 太多关联：极限是12个表以内做关联，EAV（实体-属性-值）在mysql下不能靠谱的工作；
  • 全能的枚举：避免频繁增加枚举值，每增加一次，就需要alter table一次，对与大表来说是高消耗且不安全；

数据类型

数据类型，我们秉持简单最好的原则。

简单数据类型的操作需要更少的cpu周期及其他资源。

  • int/bigint

整数类型通常是标识列最好的选择，因为很快且可以使用auto_increment；

整型会比字符串操作代价更低，因为字符集及校对规则是字符比整型会更为复杂。

  • varchar

字符串列的最大长度比平均长度大很多，列的更新少，碎片不是问题的情况下适用；使用了utf-8复杂的字符集。

  • char

适合存储很短的字符串，或者接近同一个长度；对于经常变更的数据，char也比varchar更好，不容易产生碎片。

  • float

cpu直接支持原生浮点数运算，所以浮点运算明显更快。

  • decimal

decimal用于存储财务数据，如果数据量比较大，可以考虑int或bigint代替decimal，避免浮点计算不精确及decimal精确计算代价高的问题。

  • blob/text

仅有的区别是：blob类型存储的是二进制数据，没有排序规则和字符集，而text有。

  • 随机字符串

随机字符串不适合用于标识列，其会导致写入慢、缓存会有很多刷新和不命中。

  • 怎么选择数据类型

选择存储类型时，考虑数据库如何计算和比较

  • 优先顺序

类型优先顺序：数字 => 字符串 => 时间

更多类型这里不叙述，大家可以自由发挥原则性，并考虑数据库如何计算和比较，就能得出适用什么类型。

字段长度

经常会看到int(11)，这里的11到底是什么含义？稀里糊涂的。

int的宽度对大多数应用是没有意义的，不会限制范围，只是部分mysql交互工具用来显示个数。

默认值

为什么要有默认值？

mysql默认采用严格模式，在严格模式下，会对数据的格式、长度、类型进行校验，如果不指定默认值，又设置字段非空时，mysql将会报错。

建议：设置非空及给对应类型的默认值，可避免严格模式下的异常及应用逻辑层的代码不严谨导致的异常。

非空

建议NOT NUll即非空。

NULL使得索引和值比较更为复杂。

尽量不使用NULL：虽然是视情况而定，但如果没有把握，就使用其他默认空值，避免让事情变得更糟糕；

unsigned无符号

无符号数值从0到2的n次方-1，比如tinyint是1个字节8位，从0到127（2的7次方-1），int是4个字节32位，从0到（2的31次方-1）。

有符号时从(-2的n次方+1)~(2的n次方-1)，比如-127到127

自增

自增主键的作用？保存机制？修改机制？

作用：让主键索引尽量地保持递增顺序插入，避免页分裂，使索引更紧凑。

保存机制：不同的存储引擎不一样。

InnoDB 引擎的自增值，先是保存在了内存里，到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，放在了redolog里。

索引

  • 主键默认是聚簇索引
  • 在设计库表的时候，考虑业务逻辑可能常用常查询的索引（非聚簇索引）

更多索引详见以下索引章节。

全文索引

在标准的mysql数据库中，只有myisam支持全文索引，InnoDB在mysql5.6中也只是实验性质地支持全文索引。

即使是myisam支持了全文索引，也仍然存在很多性能、数据崩溃等问题。

建议：使用第三方解决方案，如sphinx，lucene，solr等，或者等mysql5.6正式发布后，直接使用InnoDB的全文索引。

alter table

其原理：大部分修改表的结构，是用新的结构创建一个表，从旧表中查出所有数据，并插入新表。

所以大部分alter table将会导致mysql中断，线上操作大表结构时要谨慎，可能要花费几十分钟、几小时甚至按天算，需要考虑一个周全的方案（网上很多）

建议：如果是修改字段属性，建议使用alter column，这个速度将会很快。

库表设计小结

  • 尽可能的保持任何东西小而简单总是好的。
  • 尽量避免过度设计，导致复杂查询的scheam设计。
  • 使用小而简单的合适数据类型
  • 除非实际情况需要，否则应该尽可能地避免使用null值。
  • 尽量使用相同的数据类型存储相似或相关的值。
  • 注意可变长字符串，在排序或临时表时可能导致悲观的按最大长度分配内存。
  • 尽量使用整型定义标识列
  • 避免使用已废弃特性，如浮点数的精度，整数的显示宽度
  • alter table是危险且让人痛苦的操作，大部分情况下会锁表并且重建整张表。

索引知识

索引类型

B-Tree B+Tree

Hash index

全文索引

索引的特点

  • a、索引大大减少了服务器需要扫描的数据量
  • b、索引可以帮助服务器避免排序和临时表
  • c、索引可以将随机I/O变为顺序I/O

B+索引树

常见B-Tree索引按照顺序存储数据，所以mysql可以用来做order by 和group by操作。

因为有序，且B-Tree也会将相关的列值都存储在一起，因为索引中存储了实际的列值，所以部分查询只使用索引就可完成查询

索引是最好的解决方案吗

  • 非常小的表，简单全表扫描更高效
  • 中到大型表，索引非常之有效
  • 特大型表，建议采用分区。建立索引和使用索引的代价将随之增长

聚簇索引

mysql内建存储引擎暂时还不支持自定义聚簇索引，在mysql内建引擎上，主键一般都是聚簇索引

覆盖索引

覆盖索引，顾名思义，如果一个索引包含了所有需要查询的字段的值，我们就称作为覆盖索引。一般是非聚簇索引。

如果查询只需要扫描索引而无须扫描表，将极大的减少数据的访问量。

对于IO密集型范围查询会比随机从磁盘读取每一行数据的IO要少的多。

如何判断是否使用了覆盖索引：

同explain的extra列，我们可以看到"using index"的信息，表示查询直接从索引中获取数据，而不用去表数据中查询。

explain解析结果中的extra列和type列两者是完全不同的含义：

extra列的using index表示：使用覆盖索引查询取值。

type列的index表示：使用索引扫描排序。

mysql不能在索引中执行like操作。

重复冗余索引

重复索引没有意义，且加重mysql优化器的检查

冗余索引，经常犯的错是：主键作为联合索引中的二级索引，联合索引的最左索引又单独创建索引

联合索引与最左前缀

关于最左前缀，在排序中的使用，假设索引idx_abc（a,b,c）,在sql语句中：

select * from table where a=1 order by b,c

由于索引中的第一列a是常量1，是可以满足最左前缀，且order by字句中，都是正向排序，满足索引扫描来做排序。

如果第一列a是范围查询，就需要在order by子句中包含a或ab或abc的正向排序，才能满足索引扫描做排序。

如何创建高性能索引

  • 独立的列：索引列不能是表达式的一部分，也不能是函数的参数。

  • 前缀索引：

  • 索引选择性：不重复的索引值和数据表记录总数（#T）的比值（1/#T ~ 1之间），索引的选择性越高查询效率越高。比如ID唯一索引（比值为1，意思不存在重复值）的选择性是最高的，性能也是最好的。

  • 多列的索引：一个常见的错误就是为每个列创建独立的索引，或者按照错误的顺序创建多列索引

    多列索引在高版本mysql中会自动采用索引合并方案，and时索引相交，or时索引相并，大概率下都不是很好的方案，尽量只使用一个索引。

  • 选择合适的索引列顺序：在一个B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。

    在5.6版本之后mysql将不限制于最左列排序。

  • 如何选择索引列的顺序有一个经验法则：将选择性最高的列放到所以最前列。但又通常没有我们想象的那么重要。这个法则只是用于优化where条件的查询。

  • 如何定义选择性最高？

    通过该条件查询可以快速排除更多的记录或快速定位到部分记录的条件，它的选择性更高。

    比如customer_id=2时，总共只有30条记录满足条件，而staff_id=584的时候，共有7992条记录满足条件，则custome_id的选择性比staff_id的选择性更高，所以再创建索引的时候，按经验法则，创建联合索引时，customer_id会在staff_id之前。

sql语句优化

慢查询基础知识

慢查询日志，就是查询慢的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。

该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能

得到慢查询日志后，分析每一条慢查询sql语句，分析：

  • a、是否向数据库请求了不需要的数据：多余行或多余列（特别是连表查询）
  • b、是否需要取出全部列：难以使用覆盖索引，也浪费io及cpu资源
  • c、重复查询相同的数据：部分开发经常会犯这样的错误，应该缓存或内存重复查询的数据
  • d、是否扫描额外的记录：在确定查询只返回需要的数据以后，接下来要确认查询是否扫描了过多的数据，根据响应时间、扫描行数、返回行数三个指标衡量查询的开销。
  • e、响应时间=服务时间+排队时间
  • f、扫描行数与返回行数比例一般在1:1和10:1之间，有时这个值也可能非常大
  • g、explain中的type反应了访问类型：全表扫描、索引扫描index、范围扫描range、唯一索引查询、常数引用ref等
  • h、如果查询没有找到合适的访问类型，那么通常的方法就是增加一个合适的索引

关于索引、explain，详见后续章节【索引优化】【查询】

写入

关于数据写入

  • 不建议大量数据写入，不得已时，考虑分表，将比较大的一列单独一个表，降低索引树大小、提高索引树查询效率
  • 考虑表是读写频率，合理设置索引，比如高频写，低频读，尽量减少索引，提高写的效率
  • 主从场景，考虑读写分离

删除

删除其实是在查询的基础上再进行删除，一次性删除大量数据，可能会锁住很多数据、占满日志、耗尽资源、阻塞小而重要的查询。

建议：定期定量删除，比如一次性删除10000条数据一般来说是比较高效且影响也最小的做法，定期定量可以分散服务器压力。

查询类sql语句优化

为什么查询语句会慢？

查询时一个任务，它由多个子任务组成，有可能存在不必要的子任务，每个子任务会消耗一定的时间，部分子任务可能执行次数太多。

前面我们已经提到了不少慢查询的基础知识。

本章节，仍然描述优化的一些比较粗的方案，不叙述过多细节，可在有优化方案或方向的基础，进一步在网络上查找相关资料。

原则：简单快速//精确

简单、快速、精确，三者难以完全满足，只能永远满足其二 统计数据，意味着大量存储大量扫描，在mysql层，除了覆盖索引，还不够的话，就要考虑修改应用架构，可以增加汇总表或增加外部缓存系统。

可能很快你就会发现陷入一个熟悉的困境：快速、精确、简单，三者永远只能满足其二，必须舍掉其中一个。

查询一些基础知识

  • 查询执行基础

    • a、查询缓存
    • b、sql解析、预处理、优化器优化
    • c、执行计划
    • d、调用存储引擎api执行查询
    • e、返回mysql服务器
    • f、返回客户端

  • 查询执行引擎

    mysql的存储引擎接口只有几十个，但功能却非常丰富；像搭积木一样。接口叠加使用可以达到某一个功能实现。

  • mysql客户端与服务器通信协议

    半双工，即只有客户端或服务器一方才可以发送或推送消息给对方

  • 查询状态

    通过show all processlist命令可以查看每个mysql连接或说线程，任何时刻都有一个状态。

  • mysql引擎能处理的优化类型

    • a、重新定义关联表的顺序

    • b、将外连接转换为内连接

    • c、使用等价变换规则

    • d、优化count min max等聚合函数

    • e、预估并转化为常数表达

    • f、覆盖索引扫描

    • g、子查询优化

    • h、提前终止查询 limit 或不成立的条件

    • i、in不等同于or的时间复杂度，in在mysql中，会先排序后进行二分查找法，是一个O(logn)的复杂度，而or是一个O(n)的复杂度

    • j、关联查询优化器 ，mysql优化器会智能判断join表的顺序

    • k、排序优化，排序是一个高成本的操作，从性能角度，应尽可能避免排序或避免大数据量的排序

      当不能使用索引进行排序时，mysql会在内存或硬盘进行排序（统一称为filesort）using filesort

    • l、limit会在排序之后应用

    • m、关联查询的排序，如果order by的列都来自第一个表，则会using filesort对第一个表进行排序，再关联其他表数据，最后应用limit；

      如果order by的列来自多张表，则会得到所有关联数据后，再通过using temporary; using filesort排序，最后应用limit。

  • mysql三种方式应用where条件（从好到坏）

    • a、在索引中使用where条件过滤不匹配的记录。这是在存储引擎层完成的。
    • b、使用索引覆盖扫描（using index），直接从索引中过滤不需要的记录并返回命中的结果。在mysql服务器层完成。
    • c、从数据表中返回数据，然后过滤不满足条件的记录（using where）。在mysql的服务器层完成。

  • 重构复杂的查询

    • a、一个复杂的查询还是多个简单的查询。看实际情况，前提是目前mysql连接与断开开销越来越小，网络带宽越来越大，mysql每秒内存扫描百万级数据，运行多个小查询不是大问题。但仍然看实际情况。
    • b、切分查询。大查询分而治之，比如一次性删除大量数据，可能会锁住很多数据、占满日志、耗尽资源、阻塞小而重要的查询。可以考虑定期定量删除。（一次性删除10000条数据一般来说是比较高效且影响也最小的做法），定期定量可以分散服务器压力。
    • c、分解关联查询。比如常见的文章-标签 关系三张表的join关联查询，可以分解为先查询出标签id，通过标签id查询出所有文章id，通过in查询所有文章id对应文章信息。一方面可以利用应用程序中的缓存机制缓存简单查询数据，提高缓存效率及命中率；查询分解后，可以减少锁的竞争；在应用层做关联，更容易对数据库进行拆分达到高性能及可扩展；in按照id顺序查询；应用层做关联，减少冗余记录的查询；哈希关联在应用层中实现，而不是使用mysql的嵌套循环关联。

关联子查询优化为in或join

select * from xxx where xxx in(select id from xxx where xxx=1);

这样的关联子查询不建议的，mysql的执行不是我们想象的先执行子句，再执行外层。

mysql优化器会将相关的外层表压到子查询中，它认为这样可以更高效地查找到数据行，但经过explain我们会发现mysql对外层表进行全表查询后，然后再执行改写后的子查询。

所以如果是in的关联子查询，建议in后不加子查询，而是直接加一个由逗号分隔的列表，比如（1,2,3），in的列表查询本身是很快的；

或者建议使用inner join

优化union查询

尽量使用union all，否则mysql会给临时表加上dinstinct关键字，会导致对整个临时表进行唯一查询，代价非常高。

注意：mysql总是将结果放入临时表，然后再读出，再返回给客户端。

建议：将where limit order by等子句冗余地写一份到各个子查询中，减少临时表数据及扫描。

union结果集的排序及limit

union结果集的排序及limit，最好在子句中体现，并最后加上一个全局的排序及limit

如：

(select * from xxx order by name desc limit 20) union all (select * from xxx order by name desc limit 20) order by name limit 20;

效果：生成的临时表最多40条数据，提高查询及排序性能。但数据不一定准确。

优化count()查询

count可以统计行数及统计某个列值的数量（没有给列指定值，将统计不含null值的行数，如果指定值，将统计该列等于该值的行数）

例如：count(type) 与 count(type=1) 第一个统计所有type不为NULL的行数，第二个统计type=1的行数

count(*)并不会像我们猜想的那样扩展成所有的列，它会忽略所有的列而直接统计所有行数。

如果希望知道的是结果集的行数，最好使用count(*)，这样写更有意义，性能也会更好。

对于不需要非常精确的统计，建议使用近似值，避免各种条件及distinct等约束条件，避免文件排序，返回结果几乎相同，但查询效率快很多。

优化关联查询

确保on和using字句中的列上使用索引，一般来说，除非有其他理由，否则需要在关联顺序中的第二个表的相应列上创建索引。

如表A和表B用C列关联时，如果优化器的关联顺序是B、A，那么只要A上有索引，表B不需要索引。

确保group by与order by表达式中只涉及到一个表中的列。

优化group by与distinct

很多场景下mysql都使用同样的办法优化这两种查询，事实上，mysql优化器会在内部处理的时候相互转化这两类查询，人为最为有效的优化办法仍然是索引优化。

而如果是关联查询group by，确保表达式中只设计到一个表的列。

优化limit分页

分页功能通常会使用到limit加上偏移量，同时加上合适的order by字句，如果有对应的索引，通常效率会不错，否则mysql需要大量的文件排序操作。

limit常见的问题是在偏移量非常大的时候，需要查询的记录数很大，但只需要其中10或20条记录，也就是抛弃了前面大部分记录，如limit 1000,20，意味着要查询1020条记录，然后保留最后20条记录。

如果每一页访问频率一样，则查询平均需要访问半个表的数据，要优化这种查询，要么是页面中限制分页数量，要么是优化大偏移量的性能。

建议：优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列表，然后根据需要做一次关联操作再返回所有需要的列。对于大偏移量很大的时候，这样做的效率会提升非常大。

延迟关联优化

延迟关联主要是借用索引覆盖从索引直接获得id等索引列的值，并关联查询对应的其他列的数据。

优化order by

order by是mysql优化中一个很重要的点，叙述起来篇幅会有点长：

详见这篇：mysql利用索引优化order-by

优化join

join也是mysql优化的一个重灾区，需要了解各种join及其原理，再考虑结合实际场景给出较优方案：

详见这篇：mysql的join明明白白

explain

explain extended 可以查看优化器优化后的sql语句 并结合show warnings

explain partition 可以查看使用哪个分区

mysql5.6将支持explain非select语句。

查询缓存

mysql部分新版本已经不再支持查询缓存，说明自带的查询缓存实用性不强。

大部分数据库都可以缓存查询的执行计划，相同类型的sql就可以跳过sql的解析与执行计划生成阶段。

mysql查询缓存，保存查询返回的完整结果。当查询命中该缓存时，mysql立即返回结果，跳过了解析、优化、执行阶段。

当查询中有存在一些不确定的数据时，则不会被缓存。如now count等临时聚集计算。

建议：但大部分情况下，我们还是认为应该默认关闭查询缓存。如果可以的话，可以尝试使用应用级别的缓存。

索引优化汇总

索引优化涉及多方面，特别是查询方面，和以上部分内容可能有重复，但这里仍然汇集下，方面阅读：

mysql索引优化干货

其他高级

事务

首先我们要了解什么是事务，为什么需要事务？

明明白白事务ACID

  • 事务隔离级别

    事务更多的是优先考虑是数据的一致性和安全，mysql有4种事务隔离级别，读未提交、读已提交、可重复读、串行，这4种隔离级别，并行性能依次降低，安全性依次提高

  • 长事务

    意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间。除此之外，长事务还占用锁资源，可能会拖垮库。

    在开发过程中，尽可能的减小事务范围，少用长事务，如果无法避免，保证逻辑日志空间足够用，并且支持动态日志空间增长。监控Innodb_trx表，发现长事务报警。

    怎么定义长事务：比如set autocommit=0的时候，开发者一直在select，update等等操作，但忘了手动commit时，系统只会在断开连接的时候才会自动commit，这个就会产生一个奇怪的长事务。

  • 并发

    消息队列是业务逻辑中存在事务长在大并发场景下性能严重下降后，仍然可保证业务服务继续维持的好方案，比如redis是个不错的消息队列容器。

锁

这里先以悲观锁引入：悲观锁实践

分区

引入分区给查询优化带来了写新思路，同时也带了新的bug。

另外分区存在很多和索引及列不能优化的问题

建议：分区表是一种粗粒度的、简易的索引策略，适用于大数据量的过滤场景

视图

mysql暂不支持物化视图（将视图结果数据存放在一个可以查看表中，并定期从原始表中刷新数据到这个表中）。

mysql也不支持在视图中创建及使用索引。

如果打算通过视图来提升性能，需要做比较详细的测试。 mysql的视图还不是那么的成熟。

建议：对好几个表的复杂查询，使用视图有时候会大大简化问题，但无法将where压到各个具体的表，也不能使用任何索引，需要特别注意这类查询的性能。

外键的约束

InnoDB是mysql唯一支持外键的内置存储引擎。

使用外键是有成本的。比如每次修改数据时，都要在另一张表多执行一次查询操作。

InnoDB外键强制使用索引，如果外键的选择性很低，则会导致一个非常大且选择性低的索引。

外键的主要目的是保证两个相关表的数据一致，不过外键维护操作时逐行进行的，所以这样的更新会比批量删除和更新要慢。

如果只是使用外键做约束，那通常在应用逻辑里实现该约束会更好。

外键会带来很大的额外消耗。

建议：外键是一个确保系统完整性的额外特性，但如果设计的是一个高性能的系统，那么外键就显示臃肿。

存储过程

优点：

  • 离数据最近，节省带宽，减少网络延迟
  • 代码重用，保证行为一致
  • 简化代码的维护与更新
  • 提升安全

缺点：

  • mysql本身没有好的开发和调试工具
  • 存储代码效率不比应用程序高，使用函数有限
  • 存储过程会服务器带来额外压力
  • mysql本身没有什么选项可以控制存储程序的资源消耗
  • 调试是一件很困难的事情

建议：mysql的存储过程并没有别的数据库系统那么的成熟和全面，在某些特定经典场景下，数据库专家可以去实现

触发器

  • mysql触发器实现非常简单，所以功能也有限；
  • mysql只支持基于行的触发，而不是基于sql语句的触发；
  • 触发器掩盖了服务器背后的复杂工作；可能一个简单sql语句触发更新另一张表，这个触发可能让这条sql影响的记录数翻倍；
  • 触发器出现问题很难排查；
  • 触发器可能导致死锁和锁等待
  • 使用触发器有可能不能保证数据一致，因为失败时难以回滚

应用逻辑优化

  • 静态内容：不要使用apache来做静态内容服务；静态内容可以使用nginx或第三方云存储服务实现

  • 连接数：对于大流量的网站，web服务器同一时刻处理上千个连接是常见的。然而只有小部分连接需要实时处理。

  • 缓存：使用缓存服务，避免压力渗透到数据库层

    采用缓存通常可以获得数量级的性能提升。诀窍是找到正确的粒度和缓存过期策略组合。另外也需要决定哪些内容适合缓存，缓存在哪里？

    典型的高负载应用会有很多层的缓存，在每个环节都可能产生缓存。越接近于客户端越节省资源并且效率更高。

    缓存分成两种方式：

    被动缓存：存储和返回缓存结果（有结果或无结果）

    主动缓存：比如访问缓存未命中，通用会到数据库等存储媒介获取相应资源数据后生成请求结果，并缓存结果并返回数据给应用。

    操作开销：

    没有缓存时的开销就是为每个请求生成数据的开销；有缓存时的开销是检查缓存的开销加上缓存不命中的概率乘以生成数据的开销，再加上缓存命中的概率乘以缓存提供的数据的开销。

  • 读写分离

  • 冷热数据

  • 集群

@tsingchan 2020 陆续补充