一、SQL的执行顺序
顺序：FROM——ON——JOIN——WHERE——GROUP BY——SUM、COUNT——HAVING——SELECT——DISTINCT——ORDER BY——LIMIT
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
对于联合索引来说，要遵守最左前缀法则
举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。
注意范围查询语句
对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5.in和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
6.下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)='abc'--name以abc开头的id
应改为:
select id from t where name like 'abc%'
9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
11.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)
12.很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句？
原SQL语句：
select colname … from A表 where a.id not in (select b.id from B表)
高效的SQL语句：
select colname … from A表 Left join B表 on where a.id = b.id where b.id is null
取出的结果集如下图表示，A表不在B表中的数据：
13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
16.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，
其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
17.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
18.避免频繁创建和删除临时表，以减少系统表资源的消耗。
19.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，
以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
21.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
22.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
23.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
24.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。
在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
25.尽量避免大事务操作，提高系统并发能力。
26.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
27.关于JOIN优化
LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。
注意：
1）MySQL中没有full join，可以用以下方式来解决：
select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B;
2）尽量使用inner join，避免left join：
参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。
3）合理利用索引：
被驱动表的索引字段作为on的限制字段。
4）利用小表去驱动大表：
从原理图能够直观的看出如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。
二、执行计划——EXPLAIN
执行计划，是SQL在数据库中执行时的表现情况,通常用于SQL性能分析,优化等场景。在MySQL使用 explain 关键字来查看SQL的执行计划。
基本的语法：EXPLAIN(select * from table)
在常规SQL语句前面加上EXPLAIN即可
参数解释：
1、id：数字越大越先执行，一样大则从上往下执行，如果为NULL则表示是结果集，不需要用来查询。
2、select_type：
simple：不需要union的操作或者是不包含子查询的简单select语句。
primary：需要union操作或者含有子查询的select语句。
union：连接两个select查询，第一个查询是dervied派生表，第二个及后面的表select_type都是union。
dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响。
union result：包含union的结果集。
subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery。
dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响。
derived：from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select。
3、table
表名，如果是用了别名，则显示别名
4、type
依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。
system：表中只有一行数据或者是空表。
const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。
eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref。
ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。
fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。
ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。
unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值。
index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引。
index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。
5、possible_keys：查询可能使用到的索引。
6、key：查询真正使用到的索引。
7、key_len：用于处理查询的索引长度。
8、ref：常数等值查询显示const，连接查询则显示表的关联字段。
9、rows：执行计划中估算的扫描行数，不是精确值。
10、filtered：表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例。
11、extra：该字段信息较多，这里就不一一叙述了。
在实际的使用过程中我们需要重点去关注type、key、key_len、rows、extra这几个参数，type要努力优化到range级别，all要尽量少的出现，在查询的过程中要尽量使用索引，提高效率，在extra里面出现Using filesort, Using temporary是不太好的，要去优化提高性能。
mysql的四种隔离等级
READ UNCOMMITED(未提交读)
在RERAD UNCOMMITED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也成为脏读（Dirty Read）。这个级别会导致很多问题，从性能上说READ UNCOMMITED 不会比其他的级别好太多，但缺乏其他级别的好多好处，除非有非常必要的理由，在实际的应用中一般很少使用READ UNCOMMITED.
READ COMMITED (提交读)
大多数数据库系统的默认隔离级别都是READ COMMITED （但是MYSQL不是）。READ COMMITED 满足前面提到的隔离性的简单定义：一个事务开始时，只能看到已经提交的事务所做的修改。换句话说，一个事务从开始到提交之前，所做的任何修改对其他事务都 是不可见的。这个级别有时候也叫做不可重复的（nonerepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。
REPEATABLE READ (可重复读)
REPEATABLE READ (可重复读) 解决了脏读问题。该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。但是，理论上，可重复读隔离级别还是无法解决另一个幻读 （PhantomRead）的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读 取该范围的记录时，会产生幻行（Phantom Row）。InnoDB和XtraDB 存储引擎通过多版并发控制（MVCC ,Multivesion Concurrency Control ）解决了幻读问题。
可重复读是Mysql 默认的事务隔离级别。
SERIALIZABLE(可串行化)
SERIALIZABLE是最高的隔离级别。它通过强制事务串行，避免了前面说的幻读问题。简单的来说，SERIALIZABLE会在读的每一行数据上 都加上锁，所以可能导致大量的超时和锁征用问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况，才可考 虑用该级别。
简单理解
定义
(1)READ UNCOMMITED（未提交可读）-- 事务1修改了数据A后未提交，事务2可以读到被事务1修改的数据A，但是事务1可能最终不提交，那么事务2读到的就是错误的数据(脏数据)
(2)READ COMMITED（提交了可读）-- 事务1读取了数据A后未提交，事务2修改了数据A且提交,然后事务1再去读数据A会发现前后两次读结果不同，这就是不可重复读（因为重复读结果不一样）
(3)REAPATABLE READ（可重复读）-- 事务1读取了数据A后未提交，事务2修改了数据A且提交，然后事务1再去读数据A会发现前后两次读结果相同，这就是可重复读 -- MYSQL默认级别
(4)SERIALIZABLE（可串行化）-- 每一行数据加锁，导致所有事务都必须串行执行，但是代价很大
问题
脏读:事务内修改的数据在未提交时对外可见，(2)(3)(4)解决该问题
不可重复读:事务内相同数据的读结果不一致，(3)(4)解决该问题
幻读:事务1读取了范围A-范围B的数据且未提交，事务2在A-B之间插入新的一行数据后提交，事务1再去读取会发现多了一行数据，就像幻觉一样,只有(4)与MVCC多版本并发控制解决
隔离级别 脏读可能性 不可重复读可能性 幻读可能性 加锁读
READ UNCOMMITED YES YES YES NO
READ COMMITED NO YES YES NO
REPEATABLE READ NO NO YES NO
SERIALIZABLE NO NO NO YES
网友评论