索引优化

查询执行顺序

FROM <_table>	-- 两个表的笛卡尔积 vt1
		-- 如果多于两个表就计算和 vt3 的笛卡尔积，重复
ON <join_condition> -- 筛选出满足条件的行 vt2
<join_type> JOIN <_table>	-- 把主表被过滤的行添加回来 vt3
				-- 次表无匹配的部分补 NULL
WHERE <where_condition> -- 在数据从磁盘进入内存前判断
GROUP BY <group_by_list>	-- 结果集中每个组只有一行
				-- 开始允许使用别名
HAVING <having_condition> -- 全部载入内存，在内存内部判断
SELECT DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

EXPLAIN 字段

type

system：表只有一行记录（等于系统表），是 const 类型的特例。
const：表示索引了一次，只匹配一行数据。如将主键置于 WHERE 列表中，MySQL 就能将该查询转化为一个常量。
eq_ref：读取本表和关联表组合成的一行，查出来只有一条记录。
ref：返回本表和关联表某个值匹配的所有行，查出来有多条记录。
range：只检索给定范围的行，一般是在 WHERE 语句中出现了 BETWEEN 、< >、in 等的查询。只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。
index：Full Index Scan，全索引扫描，遍历索引树。
ALL：Full Table Scan，没有用到索引，全表扫描。

key_len

索引字段的最大可能长度，其根据表定义计算而得，并非实际使用长度。

char 和 varchar 类型计算:
varchr(N) 变长字段允许 NULL = N * ( utf8=3,gbk=2,latin1=1 )+2+1(NULL)
char(N) 固定字段允许 NULL = N * ( utf8=3,gbk=2,latin1=1 )+1(NULL)

数值类型计算：
TINYINT 允许 NULL = 1+1(NULL)
SMALLINT 允许 NULL = 2+1(NULL)
INT 允许 NULL = 4+1(NULL)

日期时间类型计算：（针对mysql5.5及之前版本）
DATETIME 允许 NULL = 8 + 1(NULL)
TIMESTAMP 允许 NULL = 4+1(NULL)

Extra

Using filesort：无法利用索引完成的排序操作。对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
Using temporary：排序时使用了临时表保存中间结果，常见于 ORDER BY 和 GROUP BY。
Using index：同时出现 Using where ，表示索引被用来执行索引键值的查找；如果没有同时出现 Using where ，表明索引用来读取数据而非执行查找动作，使用了索引覆盖。

JOIN 注意点

以小的结果集驱动大的结果集。
优先优化 NestedLoop 的内层。

保证 JOIN 语句中次表的条件字段已经被索引。

CREATE INDEX idx_book_card ON book(card);

SELECT * FROM book LEFT JOIN class 
ON book.card=class.card; -- index + ALL

CREATE INDEX idx_class_card ON class(card); -- ALL + ref

当无法保证建立索引且内存资源充足，酌情设置 Join Buffer 。

索引失效

查询应该从复合索引的最左前列开始，并且不跳过索引中的字段（跳过则为部分索引）。
在索引列上进行计算会使索引失效。

范围之后的索引会失效：

CREATE INDEX idx_article_ccv ON article(category_id,comments,views);

SELECT id,author_id FROM article 
WHERE category_id = 1 AND comments > 1 
ORDER BY views DESC LIMIT 1; -- Using filesort

CREATE INDEX idx_article_cv ON article(category_id,views);

尽量使用覆盖索引，用什么字段就查询什么字段。
使用 != <> IS NULL IS NOT NULL 时无法使用索引。
LIKE 子句的百分应该加右边（此时 type = range，之后的会失效），但覆盖索引下加两边都行。
字符串要加单引号，防止数字/字符发生类型转换，索引也会失效。
使用 OR 连接时索引失效。

查询优化

IN 和 EXISTS

EXISTS：将主查询的数据放到子查询中做条件验证，根据验证结果决定主查询的数据结果是否得以保留。实际执行时会忽略 SELECT 列表。可以用条件表达式，子查询或者 JOIN 替代，具体问题具体分析。

-- IN 适合 B 表比 A 表数据小的情况
SELECT * FROM `A` WHERE `id` IN (SELECT `id` FROM `B`)
-- EXISTS 适合 B 表比 A 表数据大的情况
SELECT * FROM `A` WHERE EXISTS (SELECT 1 FROM `B` WHERE `B`.id = `A`.id);

排序

尽量最佳左前缀，如果不在索引列上，FileSort 有两种算法：

双路排序算法：MySQL4.1 之前默认。读取行指针和 ORDER BY 列在 buffer 中进行排序，然后按照排序好的列表读取对应的数据输出。
单路排序算法：从磁盘读取查询需要的所有列，按照 ORDER BY 列在 buffer 中进行排序，然后扫描排序后的列表进行输出，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO，但会使用更多的空间。

单路排序算法效率好过双路排序。但如果 SortBuffer 缓冲区太小，导致从磁盘中读取所有的列不能完全保存在 SortBuffer 中，性能反而不如后者。

增大 sort_buffer_size 参数的设置。
增大 max_length_for_sort_data 参数的设置。query 字段大小总和小于其数值时将使用单路排序。过大易超出 sort_buffer_size 导致性能下降。

GORUP BY 实质先排序后分组，优化排序。

慢查询

long_query_time 的默认值为10。

-- 重启失效
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;
SHOW VARIABLES LIKE '%slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time%';
SHOW GLOBAL STATUS LIKE '%Slow_queries%';

# my.cnf
[mysqld]
slow_query_log=ON
slow_query_log_file=../slow.log
long_query_time=2

mysqldumpslow 工具

-s ORDER  what to sort by (al, at, ar, c, l, r, t), 'at' is default
		al: average lock time # 平均锁定时间
		ar: average rows sent # 平均返回记录数
		at: average query time # 平均查询时间
		c: count # 访问次数
		l: lock time # 锁定时间
		r: rows sent # 返回记录
		t: query time # 查询时间
-t NUM  just show the top n queries
-g PATTERN  grep: only consider stmts that include this string

# 返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 ../slow.log
# 访问次数最多的10个SQL
mysqldumpslow -s c -t 10 ../slow.log
# 按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" ../slow.log

SHOW PROFILE

1 2	SET profiling=ON; SHOW VARIABLES LIKE 'profiling';

SHOW PROFILE
	ALL: 显示所有的开销信息。
	BLOCK IO: 显示块IO相关开销（通用）。
	CONTEXT SWITCHES: 上下文切换相关开销。
	CPU: 显示CPU相关开销信息（通用）。
	IPC: 显示发送和接收相关开销信息。
	MEMORY: 显示内存相关开销信息。
	PAGE FAULTS: 显示页面错误相关开销信息。
	SOURCE: 显示和Source_function。
	SWAPS: 显示交换次数相关开销的信息。

SHOW PROFILES;
SHOW PROFILE cpu, block io FOR QUERY <Query_ID>;

锁

表锁

SHOW OPEN TABLES;
LOCK TABLE `readtable` READ, `writetable` WRITE;
UNLOCK TABLES;
SHOW STATUS LIKE 'table%';
-- Table_locks_immediate: 可以立即获取锁的查询次数。
-- Table_locks_waited: 出现表级锁定争用而发生等待的次数。

MyISAM 执行 SELECT 之前，给涉及到的所有表加读锁，在执行增删改之前，给涉及的表加写锁。

MySQL 表级锁两种模式：

表共享读锁（Table Read Lock）。不会阻塞其他线程対同一表的读操作，但是会阻塞其他线程対同一表的写操作
表独占写锁（Table Write Lock）。阻塞其他线程対同一表的读和写操作。

行锁

行锁是通过索引实现的，没有索引和索引失效会使行锁变表锁。

SELECT ... FOR UPDATE 锁定某一行后其他写操作会被阻塞，直到锁定的行被 COMMIT。

SHOW STATUS LIKE 'innodb_row_lock%';
-- Innodb_row_lock_current_waits: 当前正在等待锁定的数量。
-- Innodb_row_lock_time: 从系统启动到现在锁定总时间长度（重要）。
-- Innodb_row_lock_time_avg: 每次等待所花的平均时间（重要）。
-- Innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花的时间。
-- Innodb_row_lock_waits: 系统启动后到现在总共等待的次数（重要）。

间隙锁

当使用范围条件检索数据并请求共享或者排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做 “间隙(GAP)”。InnoDB 也会对这个 “间隙” 加锁。
造成在锁定的时候无法插入锁定键值范围内的任何数据。