mysql 优化

使用 explain 分析 select 查询语句

explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。

select_type

常用的有 SIMPLE 简单查询，UNION 联合查询，SUBQUERY 子查询等。

table

要查询的表

possible_keys

The possible indexes to choose

可选择的索引

key

The index actually chosen

实际使用的索引

rows

Estimate of rows to be examined

扫描的行数

type

索引查询类型，经常用到的索引查询类型：

const：使用主键或者唯一索引进行查询的时候只有一行匹配 ref：使用非唯一索引 range：使用主键、单个字段的辅助索引、多个字段的辅助索引的最后一个字段进行范围查询 index：和all的区别是扫描的是索引树 all：扫描全表：

system

触发条件：表只有一行，这是一个 const type 的特殊情况

const

触发条件：在使用主键或者唯一索引进行查询的时候只有一行匹配。

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;
复制代码

eq_ref

触发条件：在进行联接查询的，使用主键或者唯一索引并且只匹配到一行记录的时候

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;
复制代码

ref

触发条件：使用非唯一索引

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;
复制代码

range

触发条件：只有在使用主键、单个字段的辅助索引、多个字段的辅助索引的最后一个字段进行范围查询才是 range

SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
复制代码

index

The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:

触发条件：

只扫描索引树

1）查询的字段是索引的一部分，覆盖索引。 2）使用主键进行排序

all

触发条件：全表扫描，不走索引

优化数据访问

减少请求的数据量

• 只返回必要的列：最好不要使用 SELECT * 语句。
• 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
• 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询。

重构查询方式

切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);
复制代码

rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0
复制代码

分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

• 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。

• 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。

• 减少锁竞争；

• 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。

• 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

  SELECT * FROM tag JOIN tag_post ON tag_post.tag_id=tag.id JOIN post ON tag_post.post_id=post.id WHERE tag.tag='mysql'; 复制代码

  SELECT * FROM tag WHERE tag='mysql'; SELECT * FROM tag_post WHERE tag_id=1234; SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904); 复制代码

事务

事务是指满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

ACID

事务最基本的莫过于 ACID 四个特性了，这四个特性分别是：

• Atomicity：原子性
• Consistency：一致性
• Isolation：隔离性
• Durability：持久性

原子性

事务被视为不可分割的最小单元，事务的所有操作要么全部成功，要么全部失败回滚。

一致性

数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的。

隔离性

一个事务所做的修改在最终提交以前，对其他事务是不可见的。

持久性

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢。