数据库及SQL优化

SQL的生命周期

        1. 应用服务器与数据库服务器建立一个连接

        2. 数据库进程拿到请求 sql

        3. 解析并生成执行计划，执行

        4. 读取数据到内存并进行逻辑处理

        5. 通过步骤一的连接，发送结果到客户端

        6. 关掉连接，释放资源

优化方式

        1、优化SQL

        2、优化表结构和索引

        3、加缓存（如：redis）

        4、分库分表

        5、主从复制、读写分离

        6、使用集群

优化SQL

1、定位SQL语句的性能问题

MySQL 提供了 explain命令来查看语句的执行计划，使用方式：在执行的SQL前加上 explain 即可。

执行带有EXPLAIN的查询后，你将得到一个结果集，其中包含了关于查询执行计划的详细信息。
结果集中的每一行都代表查询中的一个步骤或操作，并包含以下列（某些列可能因MySQL版本和配置而异）：

id: 查询的标识符。
select_type: 查询的类型（例如SIMPLE, SUBQUERY, DERIVED等）。
table: 查询涉及的表。
type: 访问类型，这是性能调优的关键指标之一。常见的访问类型包括ALL（全表扫描）、index（索引全扫描）、range（索引范围扫描）、ref（非唯一索引查找）、eq_ref（唯一索引查找）、const（常量引用）等。
possible_keys: 可能被使用的索引。
key: 实际使用的索引。
key_len: 使用的索引的长度。在不使用索引的情况下，值为NULL。
ref: 显示哪些列或常量被用作索引查找的条件。
rows: 估计要检查的行数。
Extra: 额外的信息，例如“Using where”表示使用了WHERE子句来过滤结果，“Using index”表示只使用了索引，而没有读取实际的表数据等。

2、优化SQL

1）尽量减少查询字段，使用索引，使用limit

2）UNION ALL不去重，效率高于UNION

3）避免全表扫描：不对字段进行null值判断，如is not null，避免使用 != 或 <>、or、in、Like

表结构优化

1）加索引、主键，注意：索引不是越多越好

2）分表分区，使用partition 关键字根据某个字段进行分区；拆分表字段

主从复制、读写分离

        将主数据库中的DDL 和 DML 操作通过二进制日志（ BINLOG ）传输到从数据库上，然后将这

些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

MySQL记录binlog的三种模式

1、基于SQL语句的复制

        每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一 条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致 master-slave 中的数据不一致( 如sleep()函数）

2、基于行 的复制

        不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样；缺点是会产生大量的日志

3、混合模式复制

        以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于 STATEMENT 模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式

Mysql的锁机制

1、乐观锁：加个version字段，每次更新

2、悲观锁：类似于java中的synchronized，执行完释放锁，其他链接才可进行操作（FOR UPDATE）

3、共享锁、排它锁（悲观锁的实现）：共享锁（读锁）：只能读，不能写（LOCK IN SHARE MODE）；排它锁（写锁）：当前事务读写完成之前其他事务只能读，不能写（FOR UPDATE）

4、表级锁：没有索引的表进行查询时会锁定整个表（LOCK TABLES、UNLOCK TABLES）

5、行级锁：通过索引给某一行加锁