《高性能MySQL》摘选

深藏阁楼爱情的钟 2022-01-26 05:03 225阅读 0赞

# MySQL 优化技巧 #

## 数据类型优化 ##

*  尽可能使用存储数据的最小数据类型
 *  选择简单的数据类型（整型比字符操作代价更低）
 *  避免NULL值，查询包含NULL的列，MySQL难以优化，使得索引、索引统计更复杂。

#### 整数类型 ####

有两种类型的数字：整数和实数。如果存储整数，可以使用这几种整数类型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。 分别使用 8,6, 24, 32, 64位存储空间。  
*整数类型有UNSIGNED表示不允许负值*

#### 实数类型 ####

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。  
DECIMAL类型用于存储精确的小数,允许最多65个数字,在计算中DECIMAL会转换为DOUBLE类型

#### 字符串类型 ####

VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型 更节省空间，因为它仅使用必要的空间，*VARCHAR需要使用1 或2个额外字节记录字符串的长度：如果列的最大长度小于或 等于255 字节，则只使用1个字节表示，否则使用2个字节*  
CHAR类型是定长的：MySQL总是根据定义的字符串 长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格。

> 与 CHAR 和 VARCHAR 类似的类型还有 BINARY 和 VARBINARY, 它们存储的是二进制字符串。  
> BLOB和 TEXT都是为存储很大的数据而设计的字符串数据类型， 分别采用二进制和字符方式存储。

#### 时间和日期类型 ####

DATETIME类型能保存大范围的值， 从1001年到9999年， 精度为秒。 它把日期和时间封装到格式为YYYY MMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。  
TIMESTAMP类型保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME 小得多：只能表示从1970年到2038年。MySQL提供了FROM\_ UNIXTIME()函数把Unix时间戳转换为日期，井提供了UNIX\_TIMESTAMP()函数把日期转换为Unix时间戳。

> 除了特殊行为之外，通常也应该尽量使用TIMESTAMP, 因为它比DATETIME 空间效率更高。

## 索引优化 ##

#### 索引类型 ####

###### ***B-Tree索引*** ######

使用B-Tree数据结构来存储数据.B-tree  
B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。  
**可以使用B-Tree索引的查询类型**  
B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

*  全值匹配：全值匹配指的是和索引中的所有列进行匹配
 *  匹配最左前缀：只使用索引的第一列
 *  匹配列前缀：以只匹配某一列的值的开头部分。
 *  匹配范围值
 *  精确匹配某一列并范围匹配另外一列：即第一列全匹配，第二列范围匹配。
 *  只访问索引的查询：B-Tree通常可以支持 “只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。

> 如果不是按照索引的最左列开始查找，则无法使用索引。  
> 不能跳过索引中的列。  
> 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

##### ***哈希索引*** #####

哈希索引(hashindex)基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hashcode), 哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时哈希表中保存指向每个数据行的指针。  
如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

*  哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的 影响并不明显。
 *  哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
 *  哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
 *  哈希索引只支持等值比较查询，包括＝、IN()、<=、>=。也不支持任何范围查询
 *  访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
 *  如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

> InnoDB引擎有一个特殊的功能叫做 “自适应哈希索引(adaptivehash index)"。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。 这是一个完全自动的、 内部的行为，用户无法控制或者配置，不过如果有 必要，完全可以关闭该功能。

##### 空间数据索引 #####

这类索引无须前缀查询。 空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。

##### 全文索引 #####

是一种种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。  
全文搜索和其他几类索引的匹配方式完全不一样，它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情， 而不是简单的WHERE条件匹配。  
在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突， 全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。

#### 高性能索引策略 ####

1.  索引字段策略  
    如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列” 是指索引列不能是表达式的一部分，也不能是函数的参数。  
    简单来说就是 索引列不能使用函数、计算、表达式，会导致索引无效。
2.  前缀索引  
    有时候需要索引很长的字符列， 这会让索引变得大且慢。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。  
    要选择足够长的的前缀保证较高的准确性。  
    前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和 GROUP BY,也无法使用前缀索引做覆盖扫描。
3.  多列索引  
    通常不要为每一列建立索引，要按照正确的顺序建立多列索引。

*  当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
 *  当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存、 排序和合并操作上。可以尝试将查询改写成UNION的方式往往更好。  
    如果在EXPLA IN中看到有索引合井，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer\_switch来关闭索引合并功能。也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。

1.  索引列顺序  
    正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。（仅适用于B-Tree索引，哈希或者其他类型的索引并不会按顺序存储数据）
2.  覆盖索引  
    如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引” 。  
    覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。
3.  冗余和重复索引  
    重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。  
    如果创建了索引(A, B)再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。因此索引(A,B)也可以当作索引(A)来使用（这种冗余只是对B-Tree索引来说的）。但是如果再创建索引，(B,A)，则不是冗余索引，索引(B)  
    也不是，因为B不是索引(A, B)的最左前缀列。

#### 查询性能优化 ####

##### 查询执行的步骤 #####

1.  客户端发送一条查询给服务器。
2.  服务器先检查查询缓存， 如果命中了缓存， 则立刻返回存储在缓存中的结果。 否则进入下一阶段。
3.  服务器端进行SQL解析、 预处理， 再由优化器生成对应的执行计划。
4.  MySQL根据优化器生成

##### 查询状态 #####

查看当前的状态,执行SQL SHOW FULL PROCESSLIST

*  Sleep: 线程正在等待客户端发送新的请求
 *  Query: 线程正在执行查询 或者正在将结果发送给客户端
 *  Locked: 在MySQL服务器层，该线程正在等待表锁
 *  Analyzing and statistics: 线程正在收集存储引擎的统计信息，井生成查询的执行计划
 *  Sorting result: 线程正在对结果集进行排序
 *  Sending data: 线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据

> 查询缓存: 在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据

##### 优化数据访问 #####

1.  查询不需要的记录  
    查询结果行中不需要的记录行不要查询出来，使用LIMIT进行限制.
2.  多表关联时返回全部列  
    "SELECT \* " 查询全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的IO、内存和CPU的消耗。这样做有时候还能避免某些列被修改带来的问题。有时候查询返回超过需要的数据可以简化开发，提高代码可用性，从中进行取舍。
3.  重复查询相同的数据  
    当出现这种情况使用缓存解决。

##### 是否在扫描额外的记录 #####

衡量查询开销的三个指标：响应时间、扫描的行数、返回的行数。

##### 重构查询的方式 #####

1.  将一个复杂查询拆分成多个简单查询
2.  对关联查询进行分解，然后结果在应用程序中进行关联

*  高效利用缓存
 *  减少查询对锁的竞争
 *  更容易对数据库拆分，高可用、可拓展。
 *  程序中使用Hash关联，某些场景效率相比Mysql的嵌套关联效率更高

## 索引使用 ##

MySQL QUERY CACHE

查询缓存可以将查询语句结构和查询结果缓存起来。如果查询是同样结构的SQL,可以直接从缓存中读取结果。表中任何数据或是结构的改变，缓存就会失效。

缓存内容为 SELECT 的结果集, 缓存使用完整的 SQL 字符串做 KEY, 并区分大小写，空格等。即两个 SQL 必须完全一致才会缓存命中。

MySQL QUERY CACHE 是对大小写敏感的，因为Query Cache在内存中是以HASH结构来进行映射。

对于频繁更新的表，缓存会频繁失效给服务器造成很大的开销

开启缓存: 到MySQL安装目录下找到my.ini文件修改，linux下是my.cnf。 文件目录C:\\ProgramData\\MySQL\\MySQL Server

查看缓存 show status like ‘%qcache%’;

*  Qcache\_free\_blocks: 表示查询缓存中目前还有多少剩余的blocks。值较大，说明查询缓存中的内存碎片过多。  
    减少碎片: 合适的 query\_cache\_min\_res\_unit 可以减少碎片，这个参数最合适的大小和应用程序查询结果的平均大小直接相关，可以通过内存实际消耗(query\_cache\_size - Qcache\_free\_memory)除以 Qcache\_queries\_in\_cache 计算平均缓存大小。
 *  Qcache\_free\_memory: 查询缓存的剩余内存大小。
 *  Qcache\_hits: 表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
 *  Qcache\_inserts: 表示多少次未命中然后插入。
 *  Qcache\_lowmem\_prunes: 该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，可以适当的调整缓存大小。
 *  Qcache\_not\_cached: 表示因为 query\_cache\_type 的设置而没有被缓存的查询数量。
 *  Qcache\_queries\_in\_cache: 当前缓存中缓存的查询数量。
 *  Qcache\_total\_blocks:当前缓存的block数量。

查询缓存状态 SHOW VARIABLES LIKE ‘%query\_cache%’;

*  have\_query\_cache 是否支持缓存
 *  query\_cache\_limit 允许缓存的单条查询结果集的最大容量
 *  query\_cache\_min\_res\_unit 分配内存块时的最小单位大小
 *  query\_cache\_size 缓存使用的总内存空间大小
 *  query\_cache\_type 缓存类型
    
     *  OFF(0) 未开启
     *  NO(1) 开启
     *  DEMAND(2) 只有当SELECT语句中使用了SQL\_CACHE才开启  
        使用方法: SELECT SQL\_CACHE \* FROM my\_table WHERE condition;

设置缓存变量: set global query\_cache\_size = 600000;  
清空缓存 reset query cache;  
整理碎片 flush query cache  
查询缓存结果集 show status like ‘qcache\_q%’

> 使用MySQL QUERY CACHE时，尽量将query\_cache\_type设置为2。 如果开启全部缓存，所有SQL都会产生缓存，每次执行SQL都会先去缓存中找结果，频繁的I/O会导致效率的降低。应该结合业务找到适合缓存的SQL进行缓存。

> ***文章内容从《高性能MySQL》摘选***

# 补充 #

## 知识点 ##

### 悲观锁和乐观锁 ###

**悲观锁**: 对于数据的处理持悲观态度，总认为会发生并发冲突，获取和修改数据时，别人会修改数据。所以在整个数据处理过程中，需要将数据锁定。

实现: 通常依靠数据库提供的锁机制实现，比如mysql的排他锁，select … for update来实现悲观锁, 这样就通过开启排他锁的方式实现了悲观锁

> 使用悲观锁，需要关闭mysql的自动提交功能，将 set autocommit = 0;

> mysql中的行级锁是基于索引的，如果sql没有走索引，那将使用表级锁把整张表锁住

悲观锁在并发控制上采取的是先上锁然后再处理数据的保守策略，虽然保证了数据处理的安全性，但也降低了效率。

**乐观锁**: 就是对数据的处理持乐观态度，乐观的认为数据一般情况下不会发生冲突，只有提交数据更新时，才会对数据是否冲突进行检测, 如果发现冲突了，则返回错误信息给用户，让用户自已决定如何操作。

乐观锁的实现不依靠数据库提供的锁机制，需要我们自已实现，实现方式一般是记录数据版本，一种是通过版本号，一种是通过时间戳。

实现: 给表加一个版本号或时间戳的字段，读取数据时，将版本号一同读出，数据更新时，将版本号加1。 当我们提交数据更新时，判断当前的版本号与第一次读取出来的版本号是否相等。如果相等，则予以更新，否则认为数据过期，拒绝更新，让用户重新操作。

> 乐观锁是基于程序实现的，所以不存在死锁的情况，适用于读多的应用场景。如果经常发生冲突，上层应用不断的让用户进行重新操作，这反而降低了性能，这种情况下悲观锁就比较适用。

### MyISAM和InnoDB ###

##### MyISAM #####

MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点

1.  不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁
2.  不支持事务
3.  不支持外键
4.  不支持崩溃后的安全恢复
5.  在表有读取查询的同时，支持往表中插入新纪录
6.  支持BLOB和TEXT的前500个字符索引，支持全文索引
7.  支持延迟更新索引，极大提升写入性能
8.  对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用

##### InnoDB #####

InnoDB在MySQL 5.5后成为默认索引，它的特点

1.  支持行锁，采用MVCC来支持高并发
2.  支持事务
3.  支持外键
4.  支持崩溃后的安全恢复
5.  不支持全文索引

> 总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表

### 主从原理 ###

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

**主要用途**

1.  读写分离: 在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
2.  容灾: 数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换
3.  架构扩展: 随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

**MySQL 主从形式**

1.  一主一从
2.  一主多从，提高系统的读性能
3.  多主一从 （从5.7开始支持）: 多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上
4.  双主复制:  
    双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中。
5.  级联复制: 级联复制模式下, 部分slave的数据同步不连接主节点, 而是连接从节点。 因为如果主节点有太多的从节点, 就会损耗一部分性能用于replication, 那么我们可以让3~5个从节点连接主节点, 其它从节点作为二级或者三级与从节点连接, 这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响

##### MySQL 主从复制原理 #####

MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点

**主节点 binary log dump 线程**  
当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成，甚至在发动给从节点之前，锁会被释放。

**从节点I/O线程**  
当从节点上执行`start slave`命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。 I/O线程接收到主节点binlog dump 进程发来的更新之后，保存在本地relay-log中。

**从节点SQL线程**  
SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

要实施复制，首先必须打开Master 端的binary log（bin-log）功能, 整个复制过程实际上就是Slave 从Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。如下图所示：  
![format_png][]

**复制的基本过程**

1.  从节点上的I/O 进程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容
2.  主节点接收到来自从节点的I/O请求后，通过I/O进程的请求信息读取指定日志指定位置之后的日志信息，返回给从节点
3.  从节点的I/O进程接收到内容后，将接收到的日志内容更新到本机的relay log中，并将读取到的binary log文件名和位置保存到master-info 文件中，以便在下一次读取的时候能够清楚的告诉Master下一次读取的文件以及内容
4.  Slave 的 SQL线程检测到relay-log 中新增加了内容后，会将relay-log的内容解析成在祝节点上实际执行过的操作，并在本数据库中执行

**binlog记录格式**  
MySQL 主从复制有三种方式：基于SQL语句的复制（statement-based replication，SBR），基于行的复制（row-based replication，RBR)，混合模式复制（mixed-based replication,MBR)。对应的binlog文件的格式也有三种：STATEMENT, ROW, MIXED

*  Statement-base Replication (SBR): 记录sql语句在bin log中。  
    **优点**是只需要记录会修改数据的sql语句到binlog中，减少了binlog日质量，节约I/O，提高性能  
    **缺点**是在某些情况下，会导致主从节点中数据不一致，比如sleep(),now()等
 *  Row-based Relication(RBR): master将SQL语句分解为基于Row更改的语句并记录在bin log中，也就是只记录哪条数据被修改了。  
    **优点**是不会出现某些特定情况下的存储过程、或者函数、或者trigger的调用或者触发无法被正确复制的问题。  
    **缺点**是会产生大量的日志，尤其是修改table的时候会让日志暴增,同时增加bin log同步时间。也不能通过bin log解析获取执行过的sql语句，只能看到发生的data变更。
 *  Mixed-format Replication(MBR): 是以上两种模式的混合，MySQL会根据执行的SQL语句选择日志保存方式。

##### MySQL 主从复制模式 #####

**异步模式**  
这种模式下，主节点不会主动push bin log到从节点，这样有可能导致failover的情况下，也许从节点没有即时地将最新的bin log同步到本地

**半同步模式**  
这种模式下, 当事务开启后，主节点只需要接收到其中一台从节点的返回信息，就会commit，否则需要等待直到超时时间然后切换成异步模式再提交. 这样做的目的可以使主从数据库的数据延迟缩小，可以提高数据安全性，确保了事务提交后，binlog至少传输到了一个从节点上，不能保证从节点将此事务更新到db中。性能上会有一定的降低，响应时间会变长  
![format_png 1][]

**全同步模式**  
当事务开启后，主节点和从节点全部执行了commit并确认才会向客户端返回成功

### Mysql 优化策略 ###

##### 设计表 #####

1.  表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。
2.  尽量使用INT而非BIGINT，如果非负则加上UNSIGNED（这样数值容量会扩大一倍），当然能使用TINYINT、SMALLINT、MEDIUM\_INT更好。
3.  使用枚举或整数代替字符串类型
4.  尽量使用TIMESTAMP而非DATETIME
5.  单表不要有太多字段，建议在20以内
6.  用整型来存IP

##### 选择合适的数据类型 #####

1.  使用可存下数据的最小的数据类型，整型 < datetime < char,varchar < blob
2.  使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar
3.  尽可能使用not null定义字段
4.  尽量少用text，非用不可最好采用分表处理

##### 选择合适的索引列 #####

1.  查询频繁的列，在where、 group by、 order by、on从句中出现的列
2.  where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
3.  长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
4.  离散度大（不同的值多）的列，放在联合索引前面

##### 索引优化 #####

1.  索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描, 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
2.  值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
3.  字符字段只建前缀索引
4.  字符字段最好不要做主键
5.  不用外键，由程序保证约束
6.  尽量不用UNIQUE，由程序保证约束
7.  使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

> 简言之就是使用合适的数据类型，选择合适的索引

##### sql的编写需要注意优化 #####

1.  使用limit对查询结果的记录进行限定
2.  避免 select \*，将需要查找的字段列出来
3.  使用连接（join）来代替子查询
4.  拆分大的delete或insert语句
5.  可通过开启慢查询日志来找出较慢的SQL
6.  不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
7.  sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
8.  OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
9.  不用函数和触发器，在应用程序实现
10. 避免%xxx式查询
11. 使用同类型进行比较，比如用’123’和’123’比，123和123比
12. 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
13. 对于连续数值，使用BETWEEN不用IN
14. 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

### 分区 ###

MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引

用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化

**分区的好**

1.  可以让单表存储更多的数据
2.  分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。 还可以对一个独立分区进行优化、检查、修复等操作
3.  部分查询能够从查询条件确定只落在少数分区上，速度会很快
4.  分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备
5.  可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
6.  可以备份和恢复单个分区

**分区的限制和缺点**：

1.  一个表最多只能有1024个分区
2.  如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
3.  若表有primary key或unique key，则分区表的分区列必须包含在primary key或unique key列表里
4.  分区表无法使用外键约束
5.  NULL值会使分区过滤无效
6.  所有分区必须使用相同的存储引擎

**分区的类型**：

*  RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区
 *  LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
 *  HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
 *  KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值

> 分区的字段必须是要包含在主键当中

[format_png]: /images/20220126/f8ae73dd462747149a02f0d6c7cc43d7.png
[format_png 1]: /images/20220126/6d4b7d6763bd403284265e6a1cdf7962.png