MySQL优化思路

数据库
10212 words

MySQL的存储引擎

  • MyISAM：MyISAM是一种非事务型的存储引擎，MyISAM是一种非事务型的存储引擎，MyISAM是一种非事务型的存储引擎。
  • InnoDB：InnoDB是一种事务型的存储引擎，InnoDB是一种事务型的存储引擎，InnoDB是一种事务型的存储引擎。

Main advantages of InnoDB over MyISAM:

  • InnoDB supports transactions, MyISAM does not.
  • InnoDB supports row-level locking, MyISAM only supports table-level locking. (finer-grained locking = better concurrency)
  • InnoDB supports foreign keys, MyISAM does not. (BUT usually we do not recommend using foreign keys.)

Innodb存储引擎

  • InnoDB存储引擎的优点是：InnoDB存储引擎的优点有支持事务、支持行级锁、支持外键、支持崩溃修复、支持MVCC等。
  • InnoDB存储引擎的缺点是：InnoDB存储引擎的缺点有不支持全文索引。

InnoDB存储引擎适用于OLTP场景。

OLTP：Online Transaction Processing，即在线事务处理，是指在事务处理的过程中，用户可以同时进行查询和更新操作，而且查询和更新操作可以交叉进行。

MyISAM存储引擎

  • MyISAM存储引擎的优点是：MyISAM存储引擎的优点有支持全文索引、支持压缩、支持空间函数、支持空间函数、支持空间函数。
  • MyISAM存储引擎的缺点是：MyISAM存储引擎的缺点有不支持事务、不支持行级锁、不支持外键、不支持崩溃修复、不支持MVCC等。

OLAP: Online Analytical Processing，即在线分析处理，是指在事务处理的过程中，用户只能进行查询操作，而不能进行更新操作。

MySQL索引

  • 索引的原理：索引是一种数据结构，可以加快数据的检索速度
  • 索引的类型：索引的类型有B+Tree索引、Hash索引、R-Tree索引、FullText索引、Spatial索引。
  • 索引的优点是：索引可以加快数据的检索速度。
  • 索引的缺点是：索引会占用磁盘空间，索引会占用磁盘空间，索引会占用磁盘空间。
  • 索引的使用注意事项：索引的使用注意事项有索引列不能参与计算、索引列不能参与函数运算、索引列不能参与表达式运算。

Clustered Index

  • Clustered Index索引的原理：Clustered Index索引是一种多叉树，每个节点包含多个key，每个key对应一个value，Clustered Index索引的叶子节点包含了所有的key，Clustered Index索引的叶子节点包含了所有的value，Clustered Index索引的叶子节点是有序的。

Non-Cluster Index

TODO

索引失效的情况

create payment table:

create table payment_tab {
    `id` bigint unsigned auto_increment,
    `payment_id` bigint unsigned  not null,
    `user_id` bigint unsigned not null,
    `create_time` int unsigned  not null,
    `update_time` int unsigned  not null default 0,
    `amount` bigint  not null default 0,
    `payment_status` tinyint  not null default 0,
    `ref` varchar(64)  default NULL,
    `payment_type` tinyint not null default 0,
    primary key (`id`), -- 主键
    `idx_user_id` (`user_id`),
    `uk_payment_id` (`payment_id`), -- 唯一索引
    `idx_create_time` (`create_time`), -- 普通索引
    `idx_update_time` (`update_time`), -- 普通索引
    `idx_ref_payment_type` (`ref`, `payment_type`) -- 复合索引
} ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;;
  1. NULL值 - NULL值不参与索引
    • explain select * from payment_tab where ref is not null;
  2. OR运算符: OR运算符左右两边的条件必须都能使用索引
    • explain select * from payment_tab where ref = "a" or payment_status = 0
  3. !=、<>、NOT IN、NOT EXISTS、 between运算符
    • explain select * from payment_tab where ref != "a"
    • explain select * from payment_tab where ref <> "a"
    • explain select * from payment_tab where ref not in ("a", "b", "c")
    • explain select * from payment_tab where ref not exists (select 1 from payment where ref = "a")
    • explain select * from payment_tab where ref between "a" and "b"
  4. LIKE运算符的前缀模糊匹配
    • 当like前缀没有%，后缀有%时，索引有效;
    • explain select * from payment_tab where ref like "%xyz"
  5. 在索引列上使用mysql的内置函数，索引失效
    • explain select * from payment_tab where ref = UPPER("abc")
    • explain select * from payment_tab where SUBSTR(ref,1,3) = '100';
    • explain select DISTINCT(ref) from payement
  6. HAVING子句：HAVING子句不能使用索引
    • explain select ref, count(*) from payment_tab group by ref having count(*) > 1
  7. ORDER BY子句：在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引
  8. 复合索引使用不正确：
    • idx_col1_col2, but order by col1 desc, col2 asc
    • 复合索引未用左列字段，即不是使用第一列索引，索引失效
  9. 类型转换导致索引失效
    • explain select * from payment_tab where convert(id, char) = '666';
    • 类型隐式转换: explain select * from payment_tab where ref = 1002;
  10. 对索引列运算（如，+、-、*、/），索引失效。
    • explain select * from payment_tab where id + 1 = 666
  11. 两列做比较: 如果两个列数据都有索引，但在查询条件中对两列数据进行了对比操作，则会导致索引失效。
    • explain select * from payment_tab where update_time > create_time
  12. DBMS发现全表扫描比走索引效率更高，因此就放弃了走索引。 也就是说，当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描。 某些场景下即便强制SQL语句走索引，也同样会失效。 类似的问题，在进行范围查询（比如>、< 、>=、<=、in等条件）时往往会出现上述情况，而上面提到的临界值根据场景不同也会有所不同。

一般，在查询数据条数约占总条数五分之一以下时能够使用到索引，但超过五分之一时，则使用全表扫描了。

B+Tree索引

  • B+Tree索引的原理：B+Tree索引是一种多叉树，每个节点包含多个key，每个key对应一个value，B+Tree索引的叶子节点包含了所有的key，B+Tree索引的叶子节点包含了所有的value，B+Tree索引的叶子节点是有序的。
  • B+Tree索引的缺点是：B+Tree索引的叶子节点包含了所有的value。
  • B+Tree索引的使用场景是：B+Tree索引的使用场景有WHERE、ORDER BY、GROUP BY、JOIN、UNION、EXISTS、IN、LIKE等。
  • B+Tree索引的使用原则是：B+Tree索引的使用原则有最左前缀匹配、覆盖索引、索引下推、索引合并、索引失效等。

Hash索引

  • Hash索引的原理：Hash索引是一种一对一的映射，Hash索引的叶子节点包含了所有的key，Hash索引的叶子节点包含了所有的value，Hash索引的叶子节点是无序的。

MySQL主从复制

主从复制的原理：主库将binlog写入磁盘，从库通过IO线程读取主库的binlog，并将binlog写入relay log，从库通过SQL线程读取relay log，并将relay log解析成SQL语句，然后执行SQL语句，从而实现主从复制。

主从复制可以分为：

  • 主从同步：当用户写数据主服务器必须和从服务器同步了才告诉用户写入成功，等待时间比较长。
  • 主从异步：只要用户访问写数据主服务器，立即返回给用户。
  • 主从半同步：当用户访问写数据主服务器写入并同步其中一个从服务器就返回给用户成功。

主从复制工作过程

  • 从库生成两个线程，一个 I/O 线程，一个 SQL 线程;
  • I/O 线程去请求主库的 binlog，并将得到的 binlog 日志写到 relay log(中继日志) 文件中;
  • 主库会生成一个 log dump 线程，用来给从库 I/O 线程传 binlog;
  • SQL 线程会读取 relay log 文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致;

主从复制的优点和缺点

  • 主从复制的优点是：读写分离，提高读的性能，提高可用性，提高安全性，提高扩展性，提高灵活性。
  • 主从复制的缺点是：主从延迟，主库宕机，从库无法切换到主库；从库宕机，主库无法切换到从库；主从复制的数据不一致，

主从复制的实现

  • 基于MySQL的主从复制：MySQL的主从复制是基于binlog的，binlog是MySQL的二进制日志，记录了数据库的变更，binlog是MySQL的一个功能，可以通过show variables like 'log_bin'来查看是否开启了binlog，binlog的格式可以通过show variables like 'binlog_format'来查看，binlog的位置可以通过show master status来查看。

  • 基于MySQL的GTID的主从复制：GTID是MySQL5.6版本引入的，用于解决主从复制的主从延迟问题，GTID是全局唯一的，可以保证主从复制的数据一致性，从库可以通过GTID来确定自己的复制位置，从而避免主从延迟。

Binlog的三种格式

binlog中记载着对用户数据库所做的所有修改类操作，例如delete，update，insert等等。 binlog一般情况下分为三种格式，分别是row格式、statement格式、mixed格式，下面就这三种格式给出一些解释:

  1. Row格式
  • 记录每一行的变化；此格式不记录SQL语句上下文相关信息，仅保存哪条记录被修改。
  • 优点：binlog中可以不记录执行的SQL语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以Row格式的日志内容会非常清楚的记录下每一行数据修改的细节。
  • 缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如一条update语句或者一条alter语句，修改多条记录，则binlog中每一条修改都会有记录，每条记录都发生改变，那么该表每一条记录都会记录到日志中，这样造成binlog日志量会很大。
  1. Statement格式
  • 记录每一条语句的变化；该格式下每一条会修改数据的SQL都会记录在binlog中。
  • 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。它相比row模式能节约很多性能与日志量，具体节约的多少取决于应用的SQL情况。正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，考虑到整表删除等一些大量数据操作，ROW格式会产生大量日志，所以总体来讲statement模式会稍微好一些。
  • 缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。
  1. Mixed格式
  • 混合模式，记录每一条语句的变化，但是对于INSERT、UPDATE、DELETE语句，记录每一行的变化
  • 该格式是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，当statement无法完成主从复制的操作时(设计一些函数时)，则采用Row格式保存binlog。
  • MySQL会根据执行的每一条具体的SQL语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。
  • 新版本的MySQL中队Row模式也被做了优化，并不是所有的修改都会以Row模式来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

主从复制的数据不一致的原因

  • 主从复制是异步的，主库和从库的binlog不一定是同步的，主库和从库的binlog的顺序和数量不一定是同步的，导致主库和从库的binlog的内容不一定是同步的。

数据丢失

当主库宕机后，数据可能丢失。 解决方法：使用半同步复制，可以解决数据丢失的问题。

一个值得注意的重要事情是，如果在一定时间范围内没有副本确认事务，MySQL将恢复到标准的异步复制模式。这时事务并不会失败。这也说明，半同步复制不是一种防止数据丢失的方法，而是可以让你拥有更具弹性的故障切换的更大工具集的一部分。

MySQL的事务

MySQL的事务: 事务就是一组SQL语句，作为一个工作单元以原子方式进行处理。如果数据库引擎能够成功地对数据库应用整组语句，那么就执行该组语句。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么整组语句都不执行。也就是说，作为事务的一组语句，要么全部执行成功，要么全部执行失败。

MySQL ACID

  • 原子性（Atomicity）：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  • 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
  • 隔离性（Isolation）：一个事务的执行不能被其他事务干扰，多个并发事务之间要相互隔离。一个事务所做的修改在最终提交以前，对其他事务是不可见的。
  • 持久性（Durability）：已提交的事务对数据库中的数据的改变是永久性的。此时即使系统崩溃，数据也不会丢失。持久性是一个有点模糊的概念，实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障，而有些则未必。而且不可能有100%的持久性保障

MySQL事物隔离级别

MySQL可以通过执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。

事务隔离级别：MySQL的事务隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE，默认是REPEATABLE READ

  • READ UNCOMMITTED：读未提交：在事务中可以查看其他事务中还没有提交的修改。可能会导致脏读、幻读或不可重复读
  • READ COMMITTED：读已提交：一个事务可以看到其他事务在它开始之后提交的修改，但在该事务提交之前，其所做的任何修改对其他事务都是不可见的。可以阻止脏读，但是幻读或不可重复读仍有可能发生
  • REPEATABLE READ：可重复读：在同一个事务中多次读取相同行数据的结果是一样的。可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE：串行化，最高级别，所有事务依次执行，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。

所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(phantom row)。InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)解决了幻读的问题。

read uncommitted

READ UNCOMMITTED允许某个事务看到其他事务并没有提交的数据。可能会导致脏读、不可重复读、幻读。 原理：READ UNCOMMITTED不会采用任何锁。

read committed

READ COMMIT允许某个事务看到其他事务已经提交的数据。可能会导致不可重复读和幻读。 原理：数据的读是不加锁的，但是数据的写入、修改、删除加锁，避免了脏读。

repeatable read

可以重复读取，但有幻读。

原理：数据的读、写都会加锁，当前事务如果占据了锁，其他事务必须等待本次事务提交完成释放锁后才能对相同的数据行进行操作。读取的数据行不可写，但是可以往表中新增数据。 在MySQL中，其他事务新增的数据，看不到，不会产生幻读。采用多版本并发控制（MVCC）机制解决幻读问题。

serializable

可读，不可写。SERIALIZABLE 级别在InnoDB中和REPEATABLE READ采用相同的实现。像java中的锁，写数据必须等待另一个事务结束。

其中，Read Uncommitted 和 Serializable 比较极端，前一个可以读取未提交的记录，后一个读写冲突，并发性低，所以两者在一般情况下都不建议使用，用的最多是RC和RR。

这里需要注意的是：Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别.

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

MySQL MMVC

  • MySQL的MVCC是基于InnoDB的，MVCC是MySQL的一种多版本并发控制，MVCC是MySQL的一个功能，可以通过show variables like 'innodb_version'来查看是否支持MVCC。
  • MVCC的实现原理是：MVCC是通过在每行记录后面保存两个隐藏的列来实现的，这两个列分别记录了该行记录的创建时间和过期时间，当事务开始时，MVCC会保存一个快照，事务中读取数据时，只能读取快照时间之前创建的数据，这样就可以避免脏读，当事务提交时，会更新过期时间，这样就可以避免不可重复读，当事务提交时，会更新过期时间，这样就可以避免幻读。

MySQL的锁

  • 表锁：Table-Level Lock, 也叫表级锁，是对整张表加锁，只有获取到表锁才能对表进行读写操作。
  • 行锁：Row-Level Lock, 也叫行级锁，是对表中的某一行加锁，只有获取到行锁才能对行进行读写操作。
  • 乐观锁：乐观锁是基于版本号的，乐观锁认为不会发生并发冲突，只有在提交时才会检查是否发生了并发冲突，如果发生了并发冲突，就会回滚事务。
  • 悲观锁：悲观锁是基于锁的，悲观锁认为会发生并发冲突，所以在操作数据之前就会加锁，这样就可以避免并发冲突。

InnoDB 的锁的类型

  • 表锁：TLOCK - 表锁，只有获取到表锁才能对表进行读写操作。
    1. lock table payment write (TLOCK)
    2. unlock tables
  • 共享锁：SLOCK - 共享锁，允许其他事务对数据进行读操作，但是不允许其他事务对数据进行写操作。
    1. select * from payment where id=1 lock in share mode (SLOCK)
  • 排他锁：XLOCK - 排他锁，不允许其他事务对数据进行读操作，也不允许其他事务对数据进行写操作。
    1. select * from payment where id=1 for update (XLOCK)
    2. update payment set amount=100 where id=1
  • 意向共享锁：ISLOCK - 意向共享锁，表示事务想要获取表中某个数据的共享锁。
  • 意向排他锁：IXLOCK - 意向排他锁，表示事务想要获取表中某个数据的排他锁。

InnoDB锁的实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。

InnoDB存储引擎的锁的算法有三种：

  • Record lock：单个行记录上的锁
  • Gap lock：间隙锁，锁定一个范围，不包括记录本身
  • Next-key lock：record+gap 锁定一个范围，包含记录本身

相关知识点：

  1. innodb对于行的查询使用next-key lock
  2. Next-locking keying为了解决Phantom Problem幻读问题
  3. 当查询的索引含有唯一属性时，将next-key lock降级为record key
  4. Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
  5. 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

InnoDB行锁分为3种情形。

  • Record lock：对索引项加锁。是对索引记录本身加锁，避免其他事务修改这个记录。
  • Gap lock：对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的“间隙”加锁。
  • Next-key lock：前两种的组合，对记录及其前面的间隙加锁。是对索引记录之间的空间和索引记录本身加锁，避免其他事务在这个空间插入新的记录或者修改这个记录。

InnoDB这种行锁实现特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样！

  • 在不通过索引条件查询时，InnoDB会锁定表中的所有记录。
  • 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
  • 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

什么时候使用表锁对于InnoDB表

在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们选择InnoDB表的理由。 但在个别特殊事务中，也可以考虑使用表级锁。

  • 第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
  • 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁，减少数据库因事务回滚带来的开销。

MySQL死锁例子 - 循环等待

2 Rows Update Dead Lock

死锁是指两个或多个事务相互持有和请求相同资源上的锁，产生了循环依赖。当多个事务试图以不同的顺序锁定资源时会导致死锁。当多个事务锁定相同的资源时，也可能会发生死锁。

  • 事务A：UPDATE payment_tab SET a = a + 1 WHERE id = 1
  • 事务B：UPDATE payment_tab SET a = a - 1 WHERE id = 2
  • 事务A：UPDATE payment_tab SET a = a - 1 WHERE id = 2
  • 事务B：UPDATE payment_tab SET a = a + 1 WHERE id = 1
  • 事务A：COMMIT
  • 事务B：COMMIT

分析：事务A和事务B都在更新id为1和2的记录，事务A先更新id为1的记录，事务B先更新id为2的记录，然后事务A更新id为2的记录，事务B更新id为1的记录，这样就发生了死锁。

InnoDB目前处理死锁的方式是将持有最少行级排他锁的事务回滚。

Single Row Update Dead Lock

  • – Let’s say: payment(id=1,ref=“abc”)
  • SQL 1: update payment_tab set ref=“xyz” where ref = “abc” order by user_id limit 1;
  • SQL 2: update payment_tab set ref=“def” where id=1;

Why does Single Row Update lead to Dead Lock?

Updating single row is not a single step. Before Update, DB need lock the index fisrt.

  • SQL 1 - step A: XLOCK on ref=“abc”, getting pk=1
  • SQL 2 - step B: XLOCK pk=1
  • SQL 1 - step C: XLOCK pk=1 -> waiting for B to release XLock
  • SQL 2 - step D: XLOCK on ref=“abc” -> waiting for A to release XLock

Gap Lock 导致死锁

InnoDB utilizes indexes for locking records, so locking an existing record seems easy – simply lock the index for that record.

If the a record doesn’t exist, and let’s say two requests come at the same time.

Both SELECT ... FOR UPDATE will get gap locks instead of X (record) locks and gap locks only prevent no more records can be inserted in this gap.

Why X lock -> gap lock?

If the record exists, DB will Xlock the index(row). Happy case. If the record not exist, cannot lock an index for a record that doesn’t exist.

If you are using the default isolation level of REPEATABLE READ, InnoDB will also utilize gap locks. As long as you know the id (or even range of ids) to lock, then InnoDB can lock the gap so no other record can be inserted in that gap until we’re done with it.

However, if we have two gap locks on the same gap what will happen? It will result in deadlock and make one transaction rollback.

MySQL如何避免死锁？

  • 降低隔离级别
  • 优化SQL语句，减少锁的粒度
  • 优化表结构，减少锁的粒度

MySQL优化基本思路

首先是如何定位性能瓶颈？

一般使用show命令、慢查询日志、explain、profiling等进行分析。

  • 打印慢查询日志，找出执行慢的查询
  • 当服务器的负载增加时，使用SHOW PROCESSLIST来查看有问题的查询
  • EXPLAIN查看SELECT时索引命中的情况，分析查询语句和表结构

根据分析结果，优化索引和查询，优化表结构；这是后端小伙伴可以着手的部分。然后，还可以找DBA优化MySQL配置和硬件。

MySQL查询优化

  1. 为搜索字段建索引，合理使用 （第一条，因为它最关键）

    • 在 WHERE、GROUP BY 和 ORDER BY 的列上加上索引
    • 尽量保证索引简单，避免在同一列上加多个索引
    • 有时MySQL会选择错误的索引，可考虑使用USE INDEX
    • 查询字段存在索引中，考虑Cover Index

  2. 当只要一行数据时使用 LIMIT 1

  3. 避免 SELECT *

    • 从数据库里读出越多的数据，那么查询就会变得越慢
    • 数据多会增加网络传输的负载

  4. 利用MySQL的查询缓存

    • 使用像NOW()和RAND()或是其它类似的SQL函数，都不会开启查询缓存
    • 保持查询条件次序一致
      第一次查select col from tab where a = 'x' and b = 'y'
      下次查是select col from tab where b = 'y' and a = 'x'，就无法利用查询缓存了

  5. WHERE 子句

    • 避免对于null的判断，否则会导致全表扫描
    • 避免使用 != 或 <> 操作符，否则会造成后面的索引字段失效
    • 不使用 % 前缀模糊查询
      • 例如like “%name” 或者like “%name%”，这种查询会导致索引失效
      • 但是可以使用like “name%”
    • 避免进行表达式操作
      • select uid from user where age*2=36;会导致索引失效
    • 使用同类型进行比较，比如用 ‘123’ 和 ‘123’ 比，123 和 123 比

  6. LIMIT M,N节制使用

    • 随着表数据量的增加，使用LIMIT分页会越来越慢
    • 可以采用记录上次查询结果ID的方法实现翻页

  7. IN和OR

    • OR 改写成 IN：OR的效率是O(n)级别，IN 的效率是log(n)级别
    • IN包含的值不应过多，如果较多，产生的消耗也是比较大的，IN 的个数建议控制在 200 以内

  8. COUNT()

    • 采用InnoDB时避免在整个表上使用count(*)，它可能会导致整个表hang住，因为count操作太耗时了
    • InnoDB是没有保存rows count的，因为在不同transaction中，看到的行可能不一样
    • InnoDB通过扫描索引来计数，count全表时，需要遍历全部索引；当索引不再内存中时，还需要从磁盘读取，会更慢更消耗资源
    • 如果只需要一个大概的行数，可以用SHOW TABLE STATUS
    • MyISAM引擎不一样，维护了count值，SELECT COUNT(*)能马上返回

  9. JOIN

    • 尽可能避免复杂的join和子查询
    • 建议减少对大表的 join 查询，InnoDB 引擎会产生行锁；MyISAM 引擎会产生表锁，会导致其他写操作被阻塞
    • 尽量使用inner join，避免left join；没有其他过滤条件时，MySQL默认会自动选择小表作为驱动表

设计优化

  1. 每张表都设置一个ID做为其主键

    • 推荐使用UNSIGNED INT，设置上AUTO_INCREMENT标志

  2. 把IP地址存成 UNSIGNED INT

    • 用VARCHAR(15)字段的字符串来存IP不划算
    • 如果用整形来存放只需要4个字节，并且可以有定长的字段

  3. 固定长度的表会更快

    • 固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快
    • 有VARCHAR，TEXT，BLOB字字段；这个表就不是“固定长度静态表”了

  4. 越小的列会越快

    • 对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈
    • 数据变得紧凑会，在一个内存页可以读取更多行数据，可以减少了对硬盘的访问

  5. 尽可能的使用 NOT NULL

    • NULL无法利用索引，且需要额外的空间
    • NULL在业务逻辑层需要额外的处理

  6. 选择正确的存储引擎

    • OLTP业界用InnoDB比较多，InnoDB支持行锁
    • MyISAM使用Cluster Index, 不支持行锁，适合于一些需要大量查询的应用

  7. 大表水平分拆

    • 降低每个表的行数，加快查询
    • 比如可以按 payment_tab.user_id % 1000 分1000个表

  8. 多字段表垂直分拆

    • 把数据库中的表按列变成几张表
    • 降低表的复杂度和字段的数目

  9. 不适合建索引的情况

    • 区分度不高的字段，不适合建索引；消耗资源，对性能提升不大
    • 乱序的字段不适合作为索引，如MD5, UUID；否则，在插入数据时，更新索引需在B+树中大量移动结点，导致较多硬盘IO
    • 更新非常频繁的字段不适合创建索引；原因同上

  10. 适度冗余，让Query尽量减少Join

  11. 不推荐使用外键 - Foreign Key

    • MySQL外键的实现比较简单和粗糙，性能不佳
    • 引入外键后，不方便进行分表

  12. 尽量把数字定义成unsigned

  13. 尽量不使用ENUM

其他优化

  1. 重启MySQL时，记得预热数据库，确保将数据加载到内存，提高查询效率
  2. 使用Redis等分布式缓存或本地缓存，减少对数据库的访问
  3. 考虑持久连接或连接池，而不是多次建立连接，以减少资源的消耗
  4. 进行分表时，可以考虑双写，以平稳过渡，防止数据丢失
本文由 络壳 原创或整理，转载请注明出处