MySQL优化第三版

mysql架构：

1 客户端
2 服务器层
3 存储引擎层
mysql第二层架构：
大多数MySQL核心服务在这一层，包括查询解析，分析，优化，缓存，所有内置函数，视图等
第三层：
存储引擎，负责数据的存储和提取
存储引擎API包含几十个底层函数，用于执行操作，但存储引擎不会解析SQL，不同存储引擎之间也不会相互通信，只是简单地相应上层服务器的请求。

连接管理

每个客户端连接都会在服务器进程中拥有一个线程，该线程只能轮流在某个CPU运行，服务器会缓存线程，不需要为每一个创建销毁线程

优化执行

mysql会解析查询，并创建内部数据结构（解析树），然后对其优化，包括重写查询，决定表的读取顺序，选择合适的索引等。
用户可以提示hint优化器，影响它的决策过程

并发控制

只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。
读写锁：
共享锁（读锁），排他锁（写锁）
读锁互相不阻塞，写锁会阻塞其它写锁和读锁
锁粒度：
对修改的数据片进行精确的锁定，锁定的数据量越少，系统的并发程度越高。但加锁需要消耗资源。
所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡。一般是在表上施加行级锁
两种重要的锁策略：
表锁：
开销最小的策略，锁定整张表。其中mysql本身也会使用表锁，例如，alter table会使用表锁，这会忽略存储引擎的锁机制
行级锁：
行级锁可以最大程度支持并发处理。行级锁只在存储引擎层实现，服务层没有实现，它完全不了解存储引擎中的锁实现。

隔离级别

sql标准中定义了4种隔离级别，较低级别的隔离通常可以执行更高的并发
读未提交；
即使没有提交，对其它事务也是可见的。也就是可以读取未提交的数据，这也就是脏读。实际基本不会用这种级别。
提交读：
大多数数据库系统默认隔离级别(mysql不是)，提交之前，对于其它事务不可见，解决脏读，但存在不可重复读
可重复读：
mysql默认隔离级别，保证同一事务多次读取同样记录的结果是一致的，理论上不能解决幻读的问题，通过多版本并发控制解决幻读问题
可串行化：
最高隔离级别，强制事务串行执行，可能导致超时，锁竞争，一般很少使用，除非非常需要数据一致性，可以接受没有并发的情况。

死锁

两个事务在同一资源上互相占用，并请求对方占用的资源，就会死锁。
数据库系统实现了各种死锁检测和死锁超时机制。InnoDB目前处理死锁的方式是将持有最少行级排他锁的事务回滚

多版本并发控制MVCC

大多数据库实现了MVCC，可以认为MVCC是行级锁的一个变种，MVCC的实现是通过保存数据在某个时间点的快照来实现。
不管需要执行多少时间，每个事务看到的数据是一致的，根据事务开始时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

show profile分析sql

set profiling=on; #打开
select * from ad_record limit 716581,50 #执行sql
Show profiles; #查看qid
show profile for query qid; #分析，注意qid

详细信息
set @query_id = 160
SELECT
    state,
    sum(duration) AS total_r,
    ROUND(
        100 * sum(duration) / (
            SELECT
                sum(duration)
            FROM
                information_schema.PROFILING
            WHERE
                QUERY_ID = @query_id
        ),
        2
    ) AS pct_r,
    count(*) AS calls,
    SUM(duration) / count(*) AS "r/call"
FROM
    information_schema.PROFILING
WHERE
    QUERY_ID = @query_id
GROUP BY
    STATE
ORDER BY
    total_r DESC

优化数据类型

简单数据类型更好，例如整型比字符型代价更低
用整型存储ip，inet_auto(),inet_ntoa()
列没有特殊需求就设置成not null