MySQL性能管理及架构设计（二）：数据库结构优化、高可用架构设计、数据库索引优化（收藏一波）

朱雀 2021-01-29 10:39 515阅读 0赞

## 一、数据库结构优化（非常重要） ##

### 1.1 数据库结构优化目的 ###

1、减少数据冗余：（数据冗余是指在数据库中存在相同的数据，或者某些数据可以由其他数据计算得到），注意，尽量减少不代表完全避免数据冗余；

2、尽量避免数据维护中出现更新，插入和删除异常：

![ab6acad5e802e23013e8e28b28a66fdc.png][]

总结：要避免异常，需要对数据库结构进行范式化设计。

3、节约数据存储空间。

4、提高查询效率。

### 1.2 数据库结构设计步骤 ###

1、需求分析：全面了解产品设计的存储需求、数据处理需求、数据安全性与完整性；

2、逻辑设计（重要）：设计数据的逻辑存储结构。数据实体之间的逻辑关系，解决数据冗余和数据维护异常。数据范式可以帮助我们设计；

3、物理设计：表结构设计，存储引擎与列的数据类型；

4、维护优化：索引优化、存储结构优化。

### 1.3 数据库范式设计与反范式化 ###

[传送门：数据库逻辑设计之三大范式通俗理解，一看就懂，书上说的太晦涩][Link 2]

### 1.4 物理设计 ###

![a8984ad825eea2bc48ead94894279785.png][]

![4ffe6600e89b22addaa76053709184c3.png][]

![949918c37758048315d430a041c71aea.png][]

## 二、高可用架构设计 ##

![7a27e2729113b99f432882bb7f6b2d91.png][]

![77b485ba371a25871cd1a6abf7caac5c.png][]

### 2.1 读写分离 ###

![27d045e519c91ea189ea10e09d425822.png][]

## 三、数据库索引优化（非常重要） ##

### 3.1 两种主要数据结构：B-tree和Hash ###

### 3.1.1 B-tree结构 ###

![3f74647f85f2f54461041c90f79aa7e1.png][]

### B-tree索引的限制： ###

![9ac02bb5e19f19e5cbd34c561c845259.png][]

### 3.1.2 Hash结构 ###

![f6ddd4eaa5b9a30bb759d193d247b4f5.png][]

### Hash索引的限制： ###

*  Hash索引必须进行二次查找
 *  Hash索引无法用于排序
 *  Hash索引不支持部分索引查找也不支持范围查找
 *  Hash索引中Hash码的计算可能存在Hash冲突，不适合重复值很高的列，如性别，身份证比较合适。

### 3.1.3 MySQL常见索引和各种索引区别 ###

PRIMARY KEY（主键索引） ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE (`column`) INDEX(普通索引) ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) FULLTEXT(全文索引) ALTER TABLE `table_name` ADD FULLTEXT ( `column` ) 组合索引 ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

1.  普通索引：最基本的索引，没有任何限制
2.  唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
3.  主键索引：它 是一种特殊的唯一索引，不允许有空值。
4.  全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
5.  组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

### 3.2 使用索引好处和索引缺陷 ###

### 3.2.1 为什么要使用索引 ###

1、索引大大减少了存储引擎需要扫描的数据量；

2、索引可以帮助我们进行排序以避免使用临时表；

3、索引可以把随机I/O变为顺序I/O。

### 3.2.2 索引不是越多越好 ###

1、索引会增加写操作的成本；

2、太多的索引会增加查询优化器的选择时间。

> 索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，而有500页是目录，它当然效率低，目录是要占纸张的,而索引是要占磁盘空间的。

### 3.3 索引优化策略 ###

### 3.3.1 索引列上不能使用表达式和函数 ###

![091db99f89d041b0acd0dea2709627f0.png][]

### 3.3.2 前缀索引和索引列的选择性 ###

> Innodb索引列最大宽度为667个字节(utf-8 差不多255个字符),MyIsam索引类宽度最大为1000个字节，于是出现前缀索引，索引的选择性。

对于列的值较长，比如BLOB、TEXT、VARCHAR，就必须建立前缀索引，即将值的前一部分作为索引。这样既可以节约空间，又可以提高查询效率。但无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。

语法： ALTER TABLE table\_name ADD KEY(column\_name(prefix\_length))

![bb6fa1277449d3640aad4313a32c19b0.png][]

如何选择索引列的顺序：

1、经常会被使用到的列优先（选择性差的列不适合，如性别，查询优化器可能会认为全表扫描性能更好）；

2、选择性高的列优先；

3、宽度小的列优先（一页中存储的索引越多，降低I/O，查找越快）；

### 3.3.3 组合/联合索引策略 ###

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

### 3.3.4 覆盖索引策略 ###

跟组合索引有点类似，如果索引包含所有满足查询需要的数据的索引则成为覆盖索引(Covering Index)，也就是平时所说的不需要回表操作。即索引的叶子节点上面包含了他们索引的数据(hash索引不可以)。

判断标准：使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询。

### 优点: ###

1、可以优化缓存,减少磁盘IO操作； 2、可以减少随机IO,变随机IO操作变为顺序IO操作； 3、可以避免对InnoDB主键索引的二次查询； 4、可以避免MyISAM表进行系统调用；

### 无法使用覆盖索引的情况： ###

1、存储引擎不支持覆盖索引； 2、查询中使用了太多的列（如SELECT \* ）； 3、使用了双%号的like查询（底层API所限制）；

### 3.3.5 SQL索引优化总结口诀（套路重点） ###

> 全值匹配我最爱，最左前缀要遵守； 带头大哥不能死，中间兄弟不能断； 索引列上不计算，范围之后全失效； LIKE百分写最右，覆盖索引不写 \*； 不等空值还有or，索引失效要少用； 字符单引不可丢，SQL高级也不难 ；

### 3.4 使用索引来优化查询 ###

### 3.4.1 利用索引排序 ###

1、group by 实质是先排序后分组，遵照索引的最佳左前缀。；

2、索引中所有列的方向(升序、降序)和Order By子句完全一致；

3、当无法使用索引列，增大max\_length\_for\_sort\_data参数的设置+增大sort\_buffer\_size参数的设置；

4、如果最左列使用了范围，则排序会失效；

5、where 高于having，能写在where限定的条件就不要去having去限定了

### 3.5 索引的维护和优化 ###

### 3.5.1 删除重复索引 ###

![d625584af9d52a40a165471e829f985c.png][]

注：主键约束相当于(唯一约束 + 非空约束)

一张表中最多有一个主键约束,如果设置多个主键,就会出现如下提示：Multiple primary key defined!!!

### 3.5.2 删除冗余索引 ###

![f4861e88cb85489bcbe9c3f703ab43ea.png][]

检查工具：pt-duplicate-key-checker

[ab6acad5e802e23013e8e28b28a66fdc.png]: /images/1611916703999.png
[Link 2]: /article/details/1355100714523250689
[a8984ad825eea2bc48ead94894279785.png]: /images/1611916682324.png
[4ffe6600e89b22addaa76053709184c3.png]: /images/1611916660739.png
[949918c37758048315d430a041c71aea.png]: /images/1611916641148.png
[7a27e2729113b99f432882bb7f6b2d91.png]: /images/1611916619561.png
[77b485ba371a25871cd1a6abf7caac5c.png]: /images/1611916598741.png
[27d045e519c91ea189ea10e09d425822.png]: /images/1611916578351.png
[3f74647f85f2f54461041c90f79aa7e1.png]: /images/1611916545871.png
[9ac02bb5e19f19e5cbd34c561c845259.png]: /images/1611916517147.png
[f6ddd4eaa5b9a30bb759d193d247b4f5.png]: /images/1611916496084.png
[091db99f89d041b0acd0dea2709627f0.png]: /images/1611916471028.png
[bb6fa1277449d3640aad4313a32c19b0.png]: /images/1611916443991.png
[d625584af9d52a40a165471e829f985c.png]: /images/1611916412334.png
[f4861e88cb85489bcbe9c3f703ab43ea.png]: /images/1611916390316.png