如果是千万数量级的订单数据，一个优质的查询会有非常明显的提速。索引的作用在这里尤其关键。

索引通俗来讲就相当于书的目录，当我们根据条件查询的时候，没有索引，便需要全表扫描，数据量少还可以，一旦数据量超过百万甚至千万，一条查询sql执行往往需要几十秒甚至更多，5秒以上就已经让人难以忍受了。

提升查询速度的方向一是提升硬件(内存、cpu、硬盘)，二是在软件上优化（加索引、优化sql；优化sql不在本文阐述范围之内）。

能在软件上解决的，就不在硬件上解决，毕竟硬件提升代码昂贵，性价比太低。代价小且行之有效的解决方法就是合理的加索引。

索引使用得当，能使查询速度提升上万倍，效果惊人。

索引的类型：

mysql的索引有5种：主键索引、普通索引、唯一索引、全文索引、聚合索引（多列索引）。

唯一索引和全文索引用的很少，我们主要关注主键索引、普通索引和聚合索引。

1）主键索引：主键索引是加在主键上的索引，设置主键（primary key）的时候，mysql会自动创建主键索引；

2）普通索引：创建在非主键列上的索引；

3）聚合索引：创建在多列上的索引

索引的语法：

查看某张表的索引：SHOW INDEX FROM 表名；

创建普通索引：ALTER TABLE 表名 ADD INDEX  索引名 (加索引的列)

创建聚合索引：ALTER TABLE 表名 ADD INDEX 索引名 (加索引的列1,加索引的列2)

删除某张表的索引：DROP INDEX 索引名 ON 表名;

EXPLAIN分析SQL语句的效率：

EXPLAIN列的解释

table                    显示这一行的数据是关于哪张表的

type                     这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

possible_keys     显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key                      实际使用的索引。如果为NULL，则没有使用索引。

key_len               使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref                       显示索引的哪一列被使用了，如果可能的话，是一个常数

rows                    MYSQL认为必须检查的用来返回请求数据的行数

Extra                   关于MYSQL如何解析查询的额外信息。

Extra字段值含义：

Distinct                   一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists               MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

Range checked for each Record（index map:#）      没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort          看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using index            列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

Using temporary    看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Where used           使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）

type字段值含义：

const       表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待

eq_ref     连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

ref           这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

range      这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

index       这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）

ALL         这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

两大引擎MyISAM、InnoDB分析：

背景：

数据记录：10024725行

表索引：  主键、A、AB、ABC

相同点：

1.都是B+树的底层实现。

2.WHERE条件都符合索引最左匹配原则。

不同点：

1.MyISAM的存储文件有三个，frm、MYD、MYI 文件；InnoDB的存储文件就两个，frm、ibd文件。总文件大小InnoDB引擎占用空间更小。

2.InnoDB的存储文件本身就是索引构成，建立新索引的时间比MyISAM快。

3.MyISAM比InnoDB查询速度快，插入速度也快。

4.主键区间查询，InnoDB查询更快。字符串区间查询，MyISAM相对更快。

5.有A、AB、ABC索引的情况下，A OR B 查询，InnoDB查询性能比MyISAM慢。不建议使用OR 条件进行查询。

6.InnoDB表没有命中到 A、B、C 索引最左原则时，BC组合查询命中了索引，但还是完全扫描，比全表扫描快些。MyISAM是全表扫描。

软件层面的优化一是合理加索引；二是优化执行慢的sql。此二者相辅相成，缺一不可，如果加了索引，还是查询很慢，这时候就要考虑是sql的问题了，优化sql。实际生产中的sql往往比较复杂，如果数据量过了百万，加了索引后效果还是不理想，使用集群、垂直或水平拆分。

优化要注意的一些事（重点）

1. 索引其实就是一种归类方式，当某一个字段属性都不能归类，建立索引后是没什么效果的，或归类就二种（0和1），且各自都数据对半分，建立索引后的效果也不怎么强。

2. 主键的索引是不一样的，要区别理解。

3. 当时间存储为时间戳保存的可以建立前缀索引。

4. 在什么是字段上建立索引，需要根据查询条件而定，不要一上来就建立索引，浪费内存还有可能用不到。

5. 大字段（blob）不要建立索引，查询也不会走索引。

6. 常用建立索引的地方：

1）主键的聚集索引
2）外键索引
3）类别只有0和1就不要建索引了，没有意义，对性能没有提升，还影响写入性能
4）用模糊其实是可以走前缀索引

7. 唯一索引一定要小心使用，它带有唯一约束，由于前期需求不明等情况下，可能造成我们对于唯一列的误判。

8. 由于我们建立索引并想让索引能达到最高性能，这个时候我们应当充分考虑该列是否适合建立索引，可以根据列的区分度来判断，区分度太低的情况下可以不考虑建立索引，区分度越高效率越高。

9. 写入比较频繁的时候，不能开启MySQL的查询缓存，因为在每一次写入的时候不光要写入磁盘还的更新缓存中的数据。

10. 建索引的目的：

1）加快查询速度，使用索引后查询有迹可循。
2）减少I/O操作，通过索引的路径来检索数据，不是在磁盘中随机检索。
3）消除磁盘排序，索引是排序的，走完索引就排序完成。

11. 其实建索引的原理就是将磁盘I/O操作的最小化，不在磁盘中排序，而是在内存中排好序，通过排序的规则去指定磁盘读取就行，也不需要在磁盘上随机读取。

12. 由于磁盘整理磁盘碎片，所有有的时候我们也可以通过建立聚集索引来减少这一类的问题。

13. 当一个表中有100万数据，而经常用到的数据只有40万或40万以下，是不用考虑建立索引的，没什么性能提升。

14. 什么时候不适合建立索引：

1）频繁更新的字段不适合建立索引
2）where条件中用不到的字段不适合建立索引，都用不到建立索引没有意义还浪费空间
3）表数据可以确定比较少的不需要建索引
4）数据重复且发布比较均匀的的字段不适合建索引（唯一性太差的字段不适合建立索引），例如性别，真假值
5）参与列计算的列不适合建索引，如：

select * from table where amount+100>1000 /*这样是不走索引的，可以改造为如下*/

select * from table where amount>1000-100。

15. 使用count统计数据量的时候建议使用count(*)而不是count(列)，因为count(*)MySQL是做了优化的。

16. 二次SQL查询区别不大的时候，不能按照二次执行的时间来判断优化结果，没准第一次查询后又保存缓存数据，导致第二次查询速度比第二次快，很多时候我们看到的都是假象。

17. 什么时候开MySQL的查询缓存，交易系统（写多、读少）、SQL优化测试，建议关闭查询缓存，论坛文章类系统（写少、读多），建议开启查询缓存。

18. Explain 执行计划只能解释SELECT操作。

19. 查询优化可以考虑让查询走索引，走索引能提升查询速度，索引覆盖是最快的，如下就是让分页走覆盖索引提高查询速度。

20. 子查询比join快，虽然规律不绝对，但对大表多数有效

21. 复杂SQL语句优化的思路：

1）首先考虑在一个表中能不能取到有关的信息，尽量少关联表
2）关联条件争取都走主键或外键查询条件，能走到对应的索引
3）争取在满足业务上走小集合数据查找
4）INNER JOIN 和子查询哪个更快，场景不一致速度也不同

22. where条件多条件一定要按照小结果集排大结果集前面

23. 尽量避免大事务操作，提高系统并发能力，有时无法避免，改用定时器延迟处理。

24. 什么情况不走索引：

25. 如果MySQL估计使用全表扫描要比使用索引快，则不使用索引。

26. 使用UNION ALL 替换OR多条件查询并集。

27. 在大数据表删除也是一个问题，避免删除过程数据库奔溃，可以考虑分配删除，一次删1000条，删完后等一会继续删除

28. 大数据表优化：

1）建立汇总表
2）建立流水表
3）分库分表

29. 建立汇总表，首先不用考虑分库分表，使用定时器定时去汇总。

30. 分表，可以按水平或垂直切分。垂直分表其实就是将经常使用的数据和很少使用的数据进行垂直的切分，切分到不同的库，提高单库的数据容量，如：前3个月之前的交易记录就可以放另一个库中。

31. 建立流水表，数据冗余，有这个表记录流水变更就不用去写复杂SQL计算流水。

32. 分库，多数据库相同库结构，分发处理并发能力，但同时带来了数据同步问题，也可以使用分库做主备分离

32. SQL优化顺序：

1）尽量少作计算。
2）尽量少 join。
3）尽量少排序。
4）尽量避免 select *。
5）尽量用 join 代替子查询。
6）尽量少 or。
7）尽量用 union all 代替 union。
8）尽量早过滤。
9）避免类型转换。
10）优先优化高并发的 SQL，而不是执行频率低某些“大”SQL。
11）从全局出发优化，而不是片面调整。
12）尽可能对每一条运行在数据库中的SQL进行 Explain。

33. 如下是30条大数据表优化要点：

1）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

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 ‘李%’若要提高效率，可以考虑全文检索。

7）如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num

8）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*2

9）应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)=’abc’ ，name以abc开头的id 应改为: select id from t where name like ‘abc%’

10）不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11）在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12）不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： create table #t(…)

13）很多时候用 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)

14）并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15）索引并不是越多越好，索引固然可 以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

16）应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17）尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18）尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19）任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20）尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21）避免频繁创建和删除临时表，以减少系统表资源的消耗。

22）临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23）在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24）如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25）尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26）使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27）与临时表一样，游标并不是不可使 用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28）在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。

29）尽量避免大事务操作，提高系统并发能力。

30）尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

转载请注明：26点的博客 » MySQL上的查询优化