mysql获取当前时间毫秒_MySQL：数据库结构优化、高可用架构设计、数据库索引优化...

一、SQL查询优化（重要）

1.1 获取有性能问题SQL的三种方式

1. 通过用户反馈获取存在性能问题的SQL；
2. 通过慢查日志获取存在性能问题的SQL；
3. 实时获取存在性能问题的SQL；

1.1.2 慢查日志分析工具

相关配置参数：

slow_query_log 

常用工具：mysqldumpslow和pt-query-digest

pt

1.1.3 实时获取有性能问题的SQL（推荐）

SELECT 

查询当前服务器执行超过60s的SQL，可以通过脚本周期性的来执行这条SQL，就能查出有问题的SQL。

1.2 SQL的解析预处理及生成执行计划（重要）

1.2.1 查询过程描述（重点！！！）

通过上图可以清晰的了解到MySql查询执行的大致过程：

1. 发送SQL语句。
2. 查询缓存，如果命中缓存直接返回结果。
3. SQL解析，预处理，再由优化器生成对应的查询执行计划。
4. 执行查询，调用存储引擎API获取数据。
5. 返回结果。

1.2.2 查询缓存对性能的影响（建议关闭缓存）

第一阶段：
相关配置参数：

query_cache_type 

缓存查找是利用对大小写敏感的哈希查找来实现的，Hash查找只能进行全值查找（sql完全一致），如果缓存命中，检查用户权限，如果权限允许，直接返回，查询不被解析，也不会生成查询计划。

在一个读写比较频繁的系统中，建议关闭缓存，因为缓存更新会加锁。将query_cache_type设置为off,query_cache_size设置为0。

1.2.3 第二阶段：MySQL依照执行计划和存储引擎进行交互

这个阶段包括了多个子过程：

一条查询可以有多种查询方式，查询优化器会对每一种查询方式的（存储引擎）统计信息进行比较，找到成本最低的查询方式，这也就是索引不能太多的原因。

1.3 会造成MySQL生成错误的执行计划的原因

1、统计信息不准确
2、成本估算与实际的执行计划成本不同

3、给出的最优执行计划与估计的不同

4、MySQL不考虑并发查询
5、会基于固定规则生成执行计划
6、MySQL不考虑不受其控制的成本，如存储过程，用户自定义函数

1.4 MySQL优化器可优化的SQL类型

查询优化器：对查询进行优化并查询mysql认为的成本最低的执行计划。为了生成最优的执行计划，查询优化器会对一些查询进行改写

可以优化的sql类型

1、重新定义表的关联顺序；

2、将外连接转换为内连接；

3、使用等价变换规则；

4、优化count(),min(),max()；

5、将一个表达式转换为常数；
6、子查询优化；

7、提前终止查询，如发现一个不成立条件(如where id = -1)，立即返回一个空结果；

8、对in()条件进行优化；

1.5 查询处理各个阶段所需要的时间

1.5.1 使用profile(目前已经不推荐使用了)

set 

1.5.2 performance_schema是5.5引入的一个性能分析引擎（5.5版本时期开销比较大）

启动监控和历史记录表：use performance_schema

update 

1.6 特定SQL的查询优化

1.6.1 大表的数据修改

1.6.2 大表的结构修改

对表中的列的字段类型进行修改

改变字段的宽度时还是会锁表

无法解决主从数据库延迟的问题

1. 利用主从复制，先对从服务器进入修改，然后主从切换
2. （推荐）

添加一个新表（修改后的结构），老表数据导入新表，老表建立触发器，修改数据同步到新表， 老表加一个排它锁（重命名）， 新表重命名， 删除老表。

修改语句这个样子：

alter 

利用工具修改：

1.6.3 优化not in 和 <> 查询

子查询改写为关联查询：

SELECT customer_id,first_name,last_name,email 
FROM customer
WHERE customer_id
NOT IN (SELECT customer_id FROM payment) 

改写后：

SELECT a.customer_id,a.first_name,a.last_name,a.email 
FROM customer a
LEFT JOIN payment b ON a.customer_id = b.customer_id
WHERE b.customer_id IS NULL

二、分库分表

2.1 分库分表的几种方式

分担读负载 可通过 一主多从，升级硬件来解决。

2.1.1 把一个实例中的多个数据库拆分到不同实例（集群）

拆分简单,不允许跨库。但并不能减少写负载。

2.1.2 把一个库中的表分离到不同的数据库中

该方式只能在一定时间内减少写压力。

以上两种方式只能暂时解决读写性能问题。

2.1.3 数据库分片

对一个库中的相关表进行水平拆分到不同实例的数据库中

2.1.3.1 如何选择分区键

1. 分区键要能尽可能避免跨分区查询的发生
2. 分区键要尽可能使各个分区中的数据平均

2.1.3.2 分片中如何生成全局唯一ID

使用auto_increment_increment和auto_increment_offset参数
使用全局节点来生成ID
在Redis等缓存服务器中创建全局ID（推荐）

完！

---

原作者：唐成勇
原文链接：MySQL性能管理及架构设计（三）：SQL查询优化、分库分表 - 完结篇
原出处：思否segmentfault