前言
直奔正文
到店达之后一开始接到了大量的导数的数据需求。还记得当时有一个需求是从数据库中拉每周的周报数据（财务）。那个SQL写的非常的大而且开始的时候执行的速度很慢，差点就逼近了赤狐系统的临界执行时长。
因为一开始没想去用程序处理，也只是想把需求先搞出来。后面数据慢慢稳定。于是想把整个查询进行一些优化。
以下就是对于MySQL查询性能优化的一些心得（无查询缓存情况下）。
正文
先大致了解一下MySQL的架构设计和查询执行路径。
MySQL的逻辑架构分为三层。最上层大多数是基于网络的客户端。第二层是MySQL的核心，MySQL的核心服务功能都在此层，包括查询解析、分析、优化、缓存以及所有的内置函数。第三层则是存储引擎，引擎的职责是负责MySQL中数据的存储和提取。各个引擎各有所长。服务器通过API与存储引擎进行通信。
查询的生命周期大致可以按照顺序来看：从客户端–>服务器–>服务器解析–>生成执行计划–>执行–>返回结果。其中执行阶段可以认为是最重要的阶段了。
查询慢可能的原因：
1.表数据量过大,没有合理的切分数据表。
2.建表时并没有建立高效的索引
3.SQL语句有提高的空间(避免返回不需要的数据)
解决办法：
1.如果一个表的基础数据量就已经够大了并且伴随较大的增量数据，可以考虑分表（按时间分等策略…）
2.索引，建立高性能的索引策略。（下面会细讲）
3.优化自己的SQL语句
4.基础数据预处理（中间表）
5.如果数据体量比较大，MySQL处理起来比较费力，可以考虑一些分布式计算框架。
抛开数据量一上来就建索引就是耍流氓，因为MySQL自身维护它也是需要消耗资源的，小表全表扫描简单粗暴又高效，超大型的表也不适合，索引的代价太高可用考虑分区技术。中到大型表，嘿嘿嘿，就是你了。在innodb引擎使用的是B+tree索引,基于B-tree对索引列是顺序组织存储的缘故，所以很适合查找范围数据。
索引
定义：索引是对数据库表中一个或多个列（例如，employee 表的姓名 (name) 列）的值进行排序的结构。
目的：加快数据检索速度。
分类：聚簇索引（又称只为聚集索引，一般一张表只有一个聚集索引，并且一般为主键索引）、非聚簇索引（普通字段加索引）。聚簇索引 是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。关于非聚集索引中的联合索引，其遵从最左匹配原则。
数据结构的选择
哈希表：key-value存储，较为适合单值查找。
数组：其具有连续性和随机访问特性。能进行高效的等值查询和范围查询，问题是数据库不单单只有读，在新数据写入、更新数据后这种数据结构往往会造成批量移动数组中的其他元素。
B+树： 多叉树，B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。 B+ 树元素自底向上插入。
这个主要跟B+树这种数据结构有关。其有以下特点：（下诉为一个m = 3阶的B树，阶:一个节点的子节点数目的最大值）
1、根节点至少有2个子节点。
2、每个中间节点那都包含至少ceil(m / 2) 个孩子，最多有m个孩子。 （孩子范围为：[2, 3]）
3、每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m。 ( k: [2, 3])
4、所有的叶子结点都位于同一层，并且连成链表。
5、每个节点中的元素从小到大排列，非叶子节点为其孩子节点的最小或最大值。
创建高性能的索引：
1.查询中列不是独立的
独立的列指的是索引列不能是表达式的一部分，也不能是函数的参数。
select stu.name from stu where stu.id + 1 = 5.即使你在stu(前提：stu表中的id不是主键)的id建了索引，并没有什么用。
2.前缀索引
有的时候需要索引很长的字符，这会导致索引变得大且慢，可以考虑索引开始的部分字符，此举可大大节约索引空间，从而提高索引效率，但也会导致另一索引选择性降低。（索引选择性 = 不重复的索引值/表的记录总数）
如何确定前缀索引的长度？
1
2
3
4
5
select count(1) as total,colu_name
from table_name t
group by colu_name
order by total desc
limit 10;
可以得到某个列最频繁出现的前10个值，记录下来这10个值得范围作为标准。
1
2
3
4
5
select count(1) as total,left(colu_name,n) as pref
from table_name t
group by pref
order by total desc
limit 10;
通过不断的调整上面n的值(前缀索引的长度)得到的范围值是接近标准中的最小的数值。 打个比方，当n=7时得到的范围值就已经很接近标准了，但是n=8的时候比7更接近但时范围差距很小，那么n=7较为合适。 创建前缀索引：
1
2
alter table table_name add key (colu_name(best_index_length))
//best_index_length最佳前缀索引长度
3.多列索引
如果在用explain查看SQL的执行计划中有发现有索引合并（type值为index_merge）的时候应该好好检查一下查询和表的结构。
a、当表中的多个单独的索引列做相交操作时(通常有多个AND条件)，通常需要一个包含所有相关列的所列索引而不是多个独立的单列索引。
4.选择合适的索引顺序
通常做法：将选择性高的列放到索引最前列
5.聚簇索引
6.删除未使用的索引和冗余的索引
这些索引是累赘，可以通过一些工具帮忙定位后删除。
一般做法
1.业务出发，不要建不必要的索引。
2.看where子句哪些列出现比较频繁。给那些频繁的列（主要）、有更多不同值（次要）的列建上索引。(次要做法的目的是高效的过滤掉不要的行)
3.使用explain查看执行计划，补充遗漏的索引。
优化sql
1
2
3
4
5
6
select t.id,max(s.score) as 'this_is_my_best_student_score'
from teacher t,student s
where t.id = s.tid
group by t.id
having t.name like '%王%' //'%keyword%'这种是不会走索引
order by t.id desc
首先得知道整个SQL的执行顺序：
from -> where -> group by ->having ->select -> order by -> limit
from过程中如果使用的是自然连接那么会导致产生一张笛卡尔积的虚拟表，这样做是很蠢的。推荐使用外连接(会减少虚拟表的数据量)。
where 对虚拟表中的数据进行过滤，能放在这里的过滤别放having中进行过滤
group by
having 主要就是聚合函数结果进行过滤
select 只要自己要的数据！不需要的字段一个不要。
order by
limit
此外上面这个sql中的like可以替成一些方法：locate、position、instr等
查询性能优化大致策略：
1、确认用用程序是否存在检索大量超过需要的数据（每次用select *的时候都需要用怀疑的眼光审视）。
2、确认MySQL服务层是否分析了大量超过需要的数据。
3、重构查询方式。优化有问题的查询时，目标时获取一个更优获取数据的方式而不只是说取到了我们所需要的数据集。
4、尽可能的使用简单查询而不是复杂查询。对于MySQL来言网络通信、查询解析和优化不再是一件代价很高的事情了。
5、切分查询。分治思想的体现，比如说删除旧数据，一次性删除大量数据可能导致锁住很多数据、占满整个事物日志、耗尽资源、阻塞很多小的但是重要的查询。
可以考虑将一个大的DELETE语句切分为多个较小的语句。
6、分解关联查询。很多高性能的应用都会对关联查询进行分解。对每一个表进行一次单表查询，然后在应用程序中进行关联。
基础数据预处理
针对业务上的一些统计数据，可以将数据按日统计好在放入一张中间表中，之后将相关的定时任务脚本部署好，每日进行统计数据的增量更新。此举会明显加快系统的相应时间。增量更新与全量更新相结合更能保证数据的可靠性。
改变架构
改变数据库架构，采用分片架构设计。
形式：分库、分表。
建议采用分库。因为分表依然公用一个数据库文件，仍然有磁盘IO的竞争。分库能够很容易的将数据迁移到不同的数据库实例上，甚至是数据库机器上，扩展性更好。当然分库之后也会带来数据库连接数更多的问题。
手段：范围法、哈希法（使用的多）。
范围法一般以业务主键为划分依据，将数据水平切分到多个数据库实例上。
哈希法同样是业务主键为划分依据，通过某些哈希算法将数据水平切分到多个数据库实例上。
结果：
多个实例之间本身不直接产生联系，不像主从见有binlog同步。
多个实例数据库结构，也完全相同。
多个实例存储的数据之间没有交集，所有实例间数据并集构成全局数据。
解决的问题：
线性提升数据库写性能，需要注意的是，分组架构是不能线性提升数据库写性能的。
降低单库数据容量
分片解决的是“数据库数据量大”问题，常实施的架构设计。
MyISAM 和 InnoDB的区别
后记
这应该是我所知道的关于SQL查询优化的所有技能了。
MySQL是有查询优化器帮忙优化，但是正如卓哥（店达架构师）说的一样：一定要有写高性能的sql语句的意识。
再补充几点：
1、不要想把所有的事情都交给MySQL交给SQL。SQL语句整个执行是一个黑盒。我们没有办法去debug看看具体的数据流。
一个较为恰当的做法就是粗加工基础数据，再把这些数据用程序来进行相关的计算。这样即便是有问题，我们也可以轻松的定位问题。
2、个人心得：SQL书写的准确性不是难在语法，而是业务上的。我们需要对用到的每张表有所认知。知道每张表的细粒度。
也就是这样，我们才会对各种连接查询之后的那个虚拟的表的数据结构有一个清楚的认识。也就是这样才能保证我们的数据是正确的。
转载请注明出处!
