A. 如何提高上百万级记录MySQL数据库查询速度
关于mysql处理百万级以上的数据时如何提高其查询速度的方法
最近一段时间由于工作需要，开始关注针对Mysql数据库的select查询语句的相关优化方法。
由于在参与的实际项目中发现当mysql表的数据量达到百万级时，普通SQL查询效率呈直线下降，而且如果where中的查询条件较多时，其查询速度简直无法容忍。曾经测试对一个包含400多万条记录（有索引）的表执行一条条件查询，其查询时间竟然高达40几秒，相信这么高的查询延时，任何用户都会抓狂。因此如何提高sql语句查询效率，显得十分重要。以下是网上流传比较广泛的30种SQL查询语句优化方法：
1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
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、下面的查询也将导致全表扫描：(不能前置百分号)
select id from t where name like ‘%c%’
若要提高效率，可以考虑全文检索。
6、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
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 datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id
应改为:
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、不要写一些没有意义的查询，如需要生成一个空表结构：
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、尽量避免大事务操作，提高系统并发能力。
B. MySQL大数据量分页查询方法及其优化
使用子查询优化大数据量分页查询
这种方式的做法是先定位偏移位置的id，然后再往后查询，适用于id递增的情况。
使用id限定优化大数据量分页查询
使用这种方式需要先假设数据表的id是连续递增的，我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使用 id between and 来查询：
当然了，也可以使用in的方式来进行查询，这种方式经常用在多表关联的情况下，使用其他表查询的id集合来进行查询：
但是使用这种in查询方式的时候要注意的是，某些MySQL版本并不支持在in子句中使用limit子句。
参考 sql优化之大数据量分页查询（mysql） - yanggb - 博客园 (cnblogs.com)
C. mysql百万数据分页查询4秒，求教怎么优化
很多应用往往只展示最新或最热门的几条记录，但为了旧记录仍然可访问，所以就需要个分页的导航栏。然而，如何通过MySQL更好的实现分页，始终是比较令人头疼的问题。虽然没有拿来就能用的解决办法，但了解数据库的底层或多或少有助于优化分页查询。
我们先从一个常用但性能很差的查询来看一看。
SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15
这个查询耗时0.00sec。So，这个查询有什么问题呢？实际上，这个查询语句和参数都没有问题，因为它用到了下面表的主键，而且只读取15条记录。
CREATE TABLE city (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
city varchar(128) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
真正的问题在于offset(分页偏移量)很大的时候，像下面这样：
SELECT *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
上面的查询在有2M行记录时需要0.22sec，通过EXPLAIN查看SQL的执行计划可以发现该SQL检索了100015行，但最后只需要15行。大的分页偏移量会增加使用的数据，MySQL会将大量最终不会使用的数据加载到内存中。就算我们假设大部分网站的用户只访问前几页数据，但少量的大的分页偏移量的请求也会对整个系统造成危害。Facebook意识到了这一点，但Facebook并没有为了每秒可以处理更多的请求而去优化数据库，而是将重心放在将请求响应时间的方差变小。
对于分页请求，还有一个信息也很重要，就是总共的记录数。我们可以通过下面的查询很容易的获取总的记录数。
SELECT COUNT(*)
FROM city;
然而，上面的SQL在采用InnoDB为存储引擎时需要耗费9.28sec。一个不正确的优化是采用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能够在分页查询时事先准备好符合条件的记录数，随后只要执行一句 select FOUND_ROWS(); 就能获得总记录数。但是在大多数情况下，查询语句简短并不意味着性能的提高。不幸的是，这种分页查询方式在许多主流框架中都有用到，下面看看这个语句的查询性能。
SELECT SQL_CALC_FOUND_ROWS *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
这个语句耗时20.02sec，是上一个的两倍。事实证明使用 SQL_CALC_FOUND_ROWS 做分页是很糟糕的想法。
下面来看看到底如何优化。文章分为两部分，第一部分是如何获取记录的总数目，第二部分是获取真正的记录。
高效的计算行数
如果采用的引擎是MyISAM，可以直接执行COUNT(*)去获取行数即可。相似的，在堆表中也会将行数存储到表的元信息中。但如果引擎是InnoDB情况就会复杂一些，因为InnoDB不保存表的具体行数。
我们可以将行数缓存起来，然后可以通过一个守护进程定期更新或者用户的某些操作导致缓存失效时，执行下面的语句：
SELECT COUNT(*)
FROM city
USE INDEX(PRIMARY);
获取记录
下面进入这篇文章最重要的部分，获取分页要展示的记录。上面已经说过了，大的偏移量会影响性能，所以我们要重写查询语句。为了演示，我们创建一个新的表“news”，按照时事性排序(最新发布的在最前面)，实现一个高性能的分页。为了简单，我们就假设最新发布的新闻的Id也是最大的。
CREATE TABLE news(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(128) NOT NULL
) ENGINE=InnoDB;
一个比较高效的方式是基于用户展示的最后一个新闻Id。查询下一页的语句如下，需要传入当前页面展示的最后一个Id。
SELECT *
FROM news WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage
查询上一页的语句类似，只不过需要传入当前页的第一个Id，并且要逆序。
SELECT *
FROM news WHERE id > $last_id
ORDER BY id ASC
LIMIT $perpage
上面的查询方式适合实现简易的分页，即不显示具体的页数导航，只显示“上一页”和“下一页”，例如博客中页脚显示“上一页”，“下一页”的按钮。但如果要实现真正的页面导航还是很难的,下面看看另一种方式。
SELECT id
FROM (
SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt
FROM news
JOIN (SELECT @cnt:= 0)T
WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage * $buttons
)C
WHERE cnt = 0;
通过上面的语句可以为每一个分页的按钮计算出一个offset对应的id。这种方法还有一个好处。假设，网站上正在发布一片新的文章，那么所有文章的位置都会往后移一位，所以如果用户在发布文章时换页，那么他会看见一篇文章两次。如果固定了每个按钮的offset Id，这个问题就迎刃而解了。Mark Callaghan发表过一篇类似的博客，利用了组合索引和两个位置变量，但是基本思想是一致的。
如果表中的记录很少被删除、修改，还可以将记录对应的页码存储到表中，并在该列上创建合适的索引。采用这种方式，当新增一个记录的时候，需要执行下面的查询重新生成对应的页号。
SET p:= 0;
UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;
当然，也可以新增一个专用于分页的表，可以用个后台程序来维护。
UPDATE pagination T
JOIN (
SELECT id, CEIL((p:= p + 1) / $perpage) page
FROM news
ORDER BY id
)C
ON C.id = T.id
SET T.page = C.page;
现在想获取任意一页的元素就很简单了：
SELECT *
FROM news A
JOIN pagination B ON A.id=B.ID
WHERE page=$offset;
还有另外一种与上种方法比较相似的方法来做分页，这种方式比较试用于数据集相对小，并且没有可用的索引的情况下—比如处理搜索结果时。在一个普通的服务器上执行下面的查询，当有2M条记录时，要耗费2sec左右。这种方式比较简单，创建一个用来存储所有Id的临时表即可(这也是最耗费性能的地方)。
CREATE TEMPORARY TABLE _tmp (KEY SORT(random))
SELECT id, FLOOR(RAND() * 0x8000000) random
FROM city;
ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;
接下来就可以向下面一样执行分页查询了。
SELECT *
FROM _tmp
WHERE OFFSET >= $offset
ORDER BY OFFSET
LIMIT $perpage;
简单来说，对于分页的优化就是。。。避免数据量大时扫描过多的记录。
D. mysql数据量上十万条后，查询慢导致服务器卡有什么解决办法
几方面：
硬件，软件，以及语言
硬件，是不是抗不住，
软件，mysql是不是没有设置好，数据库设计方面等，
语言，语句写法。
下面是一些优化技巧。
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.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
E. 澶勭悊涓婄櫨涓囨潯鐨勬暟鎹搴撳備綍鎻愰珮澶勭悊鏌ヨ㈤熷害_MySQL
1銆佸规煡璇㈣繘琛屼紭鍖栵紝搴斿敖閲忛伩鍏嶅叏琛ㄦ壂鎻忥紝棣栧厛搴旇冭檻鍦 where 鍙 order by 娑夊強鐨勫垪涓婂缓绔嬬储寮曘
2銆佸簲灏介噺閬垮厤鍦 where 瀛愬彞涓瀵瑰瓧娈佃繘琛 null 鍊煎垽鏂锛屽惁鍒欏皢瀵艰嚧寮曟搸鏀惧純浣跨敤绱㈠紩鑰岃繘琛屽叏琛ㄦ壂鎻忥紝濡傦細
select id from t where num is null
鍙浠ュ湪num涓婅剧疆榛樿ゅ0锛岀‘淇濊〃涓璶um鍒楁病鏈塶ull鍊硷紝鐒跺悗杩欐牱鏌ヨ锛
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銆乮n 鍜 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 瀛愬彞涓浣跨敤鍙傛暟锛屼篃浼氬艰嚧鍏ㄨ〃鎵鎻忋傚洜涓篠QL鍙鏈夊湪杩愯屾椂鎵嶄細瑙ｆ瀽灞閮ㄥ彉閲忥紝浣嗕紭鍖栫▼搴忎笉鑳藉皢璁块棶璁″垝鐨勯夋嫨鎺ㄨ繜鍒拌繍琛屾椂锛涘畠蹇呴』鍦ㄧ紪璇戞椂杩涜岄夋嫨銆傜劧鑰岋紝濡傛灉鍦ㄧ紪璇戞椂寤虹珛璁块棶璁″垝锛屽彉閲忕殑鍊艰繕鏄鏈鐭ョ殑锛屽洜鑰屾棤娉曚綔涓虹储寮曢夋嫨鐨勮緭鍏ラ」銆傚備笅闈㈣鍙ュ皢杩涜屽叏琛ㄦ壂鎻忥細
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銆佸簲灏介噺閬垮厤鍦╳here瀛愬彞涓瀵瑰瓧娈佃繘琛屽嚱鏁版搷浣滐紝杩欏皢瀵艰嚧寮曟搸鏀惧純浣跨敤绱㈠紩鑰岃繘琛屽叏琛ㄦ壂鎻忋傚傦細
select id from t where substring(name,1,3)='abc'--name浠abc寮澶寸殑id
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’鐢熸垚鐨刬d
搴旀敼涓:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate
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銆佸苟涓嶆槸鎵鏈夌储寮曞规煡璇㈤兘鏈夋晥锛孲QL鏄鏍规嵁琛ㄤ腑鏁版嵁鏉ヨ繘琛屾煡璇浼樺寲鐨勶紝褰撶储寮曞垪鏈夊ぇ閲忔暟鎹閲嶅嶆椂锛孲QL鏌ヨ㈠彲鑳戒笉浼氬幓鍒╃敤绱㈠紩锛屽備竴琛ㄤ腑鏈夊瓧娈祍ex锛宮ale銆乫emale鍑犱箮鍚勪竴鍗婏紝閭ｄ箞鍗充娇鍦╯ex涓婂缓浜嗙储寮曚篃瀵规煡璇㈡晥鐜囪捣涓嶄簡浣滅敤銆
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 锛屼互鎻愰珮閫熷害锛涘傛灉鏁版嵁閲忎笉澶э紝涓轰簡缂撳拰绯荤粺琛ㄧ殑璧勬簮锛屽簲鍏坈reate table锛岀劧鍚巌nsert銆
24銆佸傛灉浣跨敤鍒颁簡涓存椂琛锛屽湪瀛樺偍杩囩▼鐨勬渶鍚庡姟蹇呭皢鎵鏈夌殑涓存椂琛ㄦ樉寮忓垹闄わ紝鍏 truncate table 锛岀劧鍚 drop table 锛岃繖鏍峰彲浠ラ伩鍏嶇郴缁熻〃鐨勮緝闀挎椂闂撮攣瀹氥
25銆佸敖閲忛伩鍏嶄娇鐢ㄦ父鏍囷紝鍥犱负娓告爣鐨勬晥鐜囪緝宸锛屽傛灉娓告爣鎿嶄綔鐨勬暟鎹瓒呰繃1涓囪岋紝閭ｄ箞灏卞簲璇ヨ冭檻鏀瑰啓銆
26銆佷娇鐢ㄥ熀浜庢父鏍囩殑鏂规硶鎴栦复鏃惰〃鏂规硶涔嬪墠锛屽簲鍏堝绘壘鍩轰簬闆嗙殑瑙ｅ喅鏂规堟潵瑙ｅ喅闂棰橈紝鍩轰簬闆嗙殑鏂规硶閫氬父鏇存湁鏁堛
27銆佷笌涓存椂琛ㄤ竴鏍凤紝娓告爣骞朵笉鏄涓嶅彲浣跨敤銆傚瑰皬鍨嬫暟鎹闆嗕娇鐢 FAST_FORWARD 娓告爣閫氬父瑕佷紭浜庡叾浠栭愯屽勭悊鏂规硶锛屽挨鍏舵槸鍦ㄥ繀椤诲紩鐢ㄥ嚑涓琛ㄦ墠鑳借幏寰楁墍闇鐨勬暟鎹鏃躲傚湪缁撴灉闆嗕腑鍖呮嫭“鍚堣”鐨勪緥绋嬮氬父瑕佹瘮浣跨敤娓告爣鎵ц岀殑閫熷害蹇銆傚傛灉寮鍙戞椂闂村厑璁革紝鍩轰簬娓告爣鐨勬柟娉曞拰鍩轰簬闆嗙殑鏂规硶閮藉彲浠ュ皾璇曚竴涓嬶紝鐪嬪摢涓绉嶆柟娉曠殑鏁堟灉鏇村ソ銆
28銆佸湪鎵鏈夌殑瀛樺偍杩囩▼鍜岃Е鍙戝櫒鐨勫紑濮嬪勮剧疆 SET NOCOUNT ON 锛屽湪缁撴潫鏃惰剧疆 SET NOCOUNT OFF 銆傛棤闇鍦ㄦ墽琛屽瓨鍌ㄨ繃绋嬪拰瑙﹀彂鍣ㄧ殑姣忎釜璇鍙ュ悗鍚戝㈡埛绔鍙戦 DONE_IN_PROC 娑堟伅銆
29銆佸敖閲忛伩鍏嶅ぇ浜嬪姟鎿嶄綔锛屾彁楂樼郴缁熷苟鍙戣兘鍔涖
30銆佸敖閲忛伩鍏嶅悜瀹㈡埛绔杩斿洖澶ф暟鎹閲忥紝鑻ユ暟鎹閲忚繃澶э紝搴旇ヨ冭檻鐩稿簲闇姹傛槸鍚﹀悎鐞嗐
F. mysql 大数据量查询如何优化，没办法去掉<>和like
其实你这个需要程序和数据库有一致的设计。可考虑分区。
通过电话前缀来分区，以下只是一个形式，不推荐用中文命名，
这样，只要用户不填写电话，那么前三字符就是xxx，自动会放入 ZFU区。
CREATE TABLE Customer
(
ID INT NOT NULL,
Mobile_PerNO CHAR(3) DEFAULT 'xxx',
Mobile VARCHAR(30)
)
PARTITION BY LIST(Mobile_PerNO)
PARTITION 联通186 VALUES IN (133),
PARTITION 移动139 VALUES IN (139),
PARTITION 电信133 VALUES IN (133),
PARTITION ZFU VALUES IN (xxx)
);
其次你过于依赖数据库而成形的程序，用点不客气的话说，那就是耦合极高的设计。
你其实完全可以在注册时，写入验证，一个手机号就能注册一次。左右打掉空格，这样手机上就能建立唯一索引。 使用LIKE ‘133%’ 至少性能上有一定的飞跃。
至于你的第二个SQL， 在时间和发送号码上可以建立索引，然后条件上写入时间。
也可以使用hash方式按照年季度分区。
理论上 性能提升百倍还是一点问题都没有的。
分区资料 看官方文档。