MySQL中处理大数据的优化查询速度方法
在实际项目中，当MySQL表的数据量达到100万时，普通SQL查询的效率呈线性下降，如果where中有很多查询条件，查询速度是无法容忍的。想想看，如果我们在淘宝上查询订单的详细信息，如果查询时间高达几十秒，那么任何用户都会因为如此高的查询延迟而发疯。因此，如何提高SQL查询的效率是非常重要的。
查询速度慢的原因
1.没有索引或未使用索引（这是查询速度慢的最常见问题，也是程序设计中的一个缺陷）
2.I/O吞吐量小，形成瓶颈效应。
3.查询未优化，因为未创建计算列。
4.内存不足
5.网络速度慢
6.查询的数据量太大（可以使用多个查询，也可以使用其他方法来减少数据量）
7.锁或死锁（这是查询速度慢的最常见问题，也是程序设计中的一个缺陷）
8、 sp_uuolock、sp_uuowho、活动用户查看，因为他们读写竞争资源。
9.返回了不必要的行和列
10.查询语句不好，未优化
30种SQL查询优化方法：
1.尽量避免在where子句=或运算符中使用，否则引擎将放弃使用索引并执行完整表扫描。
2.尽量避免判断where子句中字段的空值，否则引擎将放弃使用索引并扫描整个表，例如：
1 | select id from t where num = 10 or num = 20; |
可以这样查询：
1 2 3 | select id from t where num = 10union allselect id from t where num = 20; |
5、下面的查询也将导致全表扫描：（不能前置百分号）
1 | select id from t where name like '%abc%'; |
若要提高效率，可以考虑全文检索。
6、in 和 not in 也要慎用，否则会导致全表扫描，如：
1 | select id from t where num in(1, 2, 3); |
对于连续的数值，能用 between 就不要用 in 了：
1 2 3 4 5 6 | select id from t where num between 1 and 3;select xx,phone FROM send a JOIN ( select '13891030091' phone union select '13992085916' ………… UNION SELECT '13619100234' ) b on a.Phone=b.phone--替代下面 很多数据隔开的时候in('13891030091','13992085916','13619100234'…………) |
7、如果在 where 子句中使用参数，也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
1 | select id from t where num = @num; |
可以改为强制查询使用索引：
1 | select id from t with(index(索引名)) where num = @num; |
8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
1 | select id from t where num/2 = 100; |
应改为：
1 | select id from t where num = 100 * 2; |
9、应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
1 2 | select id from t where substring(name, 1, 3) = ’abc’–name; //以abc开头的idselect id from t where datediff(day,createdate,’2005-11-30′) = 0–’2005-11-30′; //生成的id |
应改为:
1 2 | select id from t where name like ‘abc%’select id from t where createdate >= ’2005-11-30′ and createdate < ’2005-12-1′; |
10、不要在 where 子句中的 “=” 左边进行函数，算术运算或者其他表达式运算，否则系统将可能无法正确使用索引。
11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
12、不要些一些没有意义的查询，如需要生成一个空表结构：
1 | select col1,col2 into #t from t where 1=0; |
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
1 | create table #t(…) |
13、很多时候用 exists 代替 in 是一个好的选择：
1 | select num from a where num in(select num from b) |
用下面的语句替换：
1 | select num from a where exists(select 1 from b where num=a.num); |
14.并非所有索引都对查询有效。SQL根据表中的数据优化查询。当重复索引列中的大量数据时，SQL查询可能不会使用索引。例如，如果表中有sex字段，男性和女性字段，几乎各占一半，那么即使索引是基于sex构建的，它也不会对查询效率产生影响。
15.指数不是越多越好。索引可以提高相应选择的效率，但也会降低插入和更新的效率。由于索引可能在插入或更新期间重建，因此需要根据具体情况仔细考虑如何构建索引。表的索引号不应超过6。如果太多，考虑是否有必要在一些不常用的列上建立索引。
16.应尽可能避免更新聚集索引数据列，因为聚集索引数据列的顺序是表记录的物理存储顺序。一旦该列的值发生变化，将导致调整整个表记录的顺序，这将消耗大量资源。如果应用系统需要频繁更新聚集索引数据列，则需要考虑是否应将索引构建为聚集索引。
17.尽量使用数字字段。如果只包含数字信息的字段不被设计为字符，将降低查询和连接的性能，并增加存储开销。这是因为在处理查询和连接时，引擎将逐个比较字符串中的每个字符。对于数字类型，仅进行一次比较就足够了。
18.尽量使用varchar/nvarchar而不是char/nchar，因为首先，可变长度字段的存储空间小，可以节省存储空间。其次，对于查询，在相对较小的字段中搜索效率明显较高。
19.不要在任何地方使用select*from t，用特定字段列表替换*并且不要返回任何不能使用的字段。
20.尽量使用表变量，而不是临时表。如果表变量包含大量数据，请注意索引非常有限（仅主键索引）。
21.避免频繁创建和删除临时表，以减少系统表资源的消耗。
22.临时桌子不能使用。正确地使用它们可以使一些常规更有效。例如，当需要重复引用大表或公共表中的数据集时。但是，对于一次性事件，最好使用导出表。
23.创建临时表时，如果一次插入了大量数据，可以使用select into代替create table，避免造成大量日志，提高速度；如果数据量较小，为了简化系统表的资源，请先创建表，然后插入。
24.如果使用临时表，必须显式删除存储过程末尾的所有临时表，先截断表，然后删除表，以避免长时间锁定系统表。
25.尽量避免使用游标，因为游标的效率很低。如果游标操作的数据超过10000行，则应考虑重写。
26.在使用基于光标的方法或临时表方法之前，应首先找到基于集合的解决方案来解决问题。基于集合的方法通常更有效。
27.与临时表一样，游标也不是不可用的。对于小数据集使用fast时，前向游标通常优于其他逐行方法，尤其是在必须引用多个表才能获得所需数据的情况下。结果集中包含总计的例程通常比使用游标执行得更快。如果开发时间允许，可以尝试基于游标和基于集合的方法，看看哪种方法效果更好。
28.在所有存储过程和触发器的开头设置NOCOUNT on，在结尾设置NOCOUNT off。在uproc消息中执行存储过程和触发器的每个语句后，不需要向客户机发送done。
29.尽量避免向客户返回大量数据。如果数据量太大，考虑相应的需求是否合理。
30.尽量避免大型事务操作，提高系统的并发性。
如对本文有疑问，请提交到交流论坛，广大热心网友会为你解答！！ 点击进入论坛
