mysql数据库-3-2-查询优化-慢sql原因分析，慢sql优化，千万级别数据如何查询
慢查询日志
将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件，通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。
慢查询日志参数:
long_query_time ： 设定慢查询的阀值，超出设定值的SQL即被记录到慢查询日志，缺省值为10s
slow_query_log ： 指定是否开启慢查询日志
log_slow_queries ： 指定是否开启慢查询日志(该参数已经被slow_query_log取代，做兼容性保留)
slow_query_log_file ： 指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
log_queries_not_using_indexes: 如果值设置为ON，则会记录所有没有利用索引的查询.
查看 MySQL慢日志信息
#.查询慢日志配置信息 :
show variables like '%query%';
#.修改配置信息
set global slow_query_log = on;
查看不使用索引参数状态:
# 显示参数
show variables like '%log_queries_not_using_indexes';
# 开启状态
set global log_queries_not_using_indexes = on;
查看慢日志显示的方式
#查看慢日志记录的方式
show variables like '%log_output%';
#设置慢日志在文件和表中同时记录
set global log_output='FILE,TABLE';
测试慢查询日志
#查询时间超过10秒就会记录到慢查询日志中
select sleep(3) FROM user ;
#查看表中的日志
select * from mysql.slow_log;
数据库优化方案
- 避免全表扫描，首先应考虑在 where 及 orderby 涉及的列上建立索引。
- 避免在 where 子句中对字段进行 null 值判断，导致引擎放弃使用索引而进行全表扫描
- 避免在 where 子句中使用 != 或>操作符，引擎将放弃使用索引而进行全表扫描。
- 避免在 where 子句中使用or 来连接条件
- 慎用in 和 not， 可以用 exists 代替 in
- 慎用 like 'XXX%'，要提高效率，可以全文检索。
- 应尽量避免在 where 子句中对字段进行表达式操作，如：
select id from t where num/2=100
应改为select id from t where num=100*2 - 避免在where子句中对字段进行函数操作
select id from t where substring(name,1,3)='abc'
改为:
select id from t where name like 'abc%' - 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，
并且应尽可能的让字段顺序与索引顺序相一致。(索引的最左前缀原则) - 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，
如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。 - 索引不是越多越好，索引可以提高select 的效率，同时也降低 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引。
- 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”
- 避免频繁创建和删除临时表，以减少系统表资源的消耗。
- 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；
如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。 - 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
数据库中的数据在未进行分库分表的情况下，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大，相应地，数据操作，增删改查的开销也会越来越大 - 读写分离。通过数据库配置设置， mysql复制时，产生了多个数据副本（备库），为减少服务器压力，备库用于处理读操作，主库可同时处理读写。
备库的复制是异步的，无法实时同步，读写分离的主要难点也在于备库上的脏数据。通常如果使用备库进行读，一般对数据的实时性要求不能太高。 - 分库、分表。
- 利用缓存存储经常被查询的数据。利用redis、memcache
去重
子查询
千万级别的表分页查询非常慢，怎么办？
一、问题复现
在实际的软件系统开发过程中，随着使用的用户群体越来越多，表数据也会随着时间的推移，单表的数据量会越来越大。
以订单表为例，假如每天的订单量在 4 万左右，那么一个月的订单量就是 120 多万，一年就是 1400 多万，随着年数的增加和单日下单量的增加，订单表的数据量会越来越庞大，订单数据的查询不会像最初那样简单快速，如果查询关键字段没有走索引，会直接影响到用户体验，甚至会影响到服务是否能正常运行！
下面我以某个电商系统的客户表为例，数据库是 Mysql，数据体量在 100 万以上，详细介绍分页查询下，不同阶段的查询效率情况（订单表的情况也是类似的，只不过它的数据体量比客户表更大）。
下面我们一起来测试一下，每次查询客户表时最多返回 100 条数据，不同的起始下，数据库查询性能的差异。
当起点位置在 0 的时候，仅耗时：18 ms
当起点位置在 1000 的时候，仅耗时：23 ms
当起点位置在 10000 的时候，仅耗时：54 ms
当起点位置在 100000 的时候，仅耗时：268 ms
当起点位置在 500000 的时候，仅耗时：1.16 s
当起点位置在 1000000 的时候，仅耗时：2.35 s
可以非常清晰的看出，随着起点位置越大，分页查询效率成倍的下降，当起点位置在 1000000 以上的时候，对于百万级数据体量的单表，查询耗时基本上以秒为单位。
而事实上，一般查询耗时超过 1 秒的 SQL 都被称为慢 SQL，有的公司运维组要求的可能更加严格，比如小编我所在的公司，如果 SQL 的执行耗时超过 0.2s，也被称为慢 SQL，必须在限定的时间内尽快优化，不然可能会影响服务的正常运行和用户体验。
对于千万级的单表数据查询，小编我刚刚也使用了一下分页查询，起点位置在 10000000，也截图给大家看看，查询耗时结果：39 秒！
没有接触过这么大数据体量的同学，可能多少对这种查询结果会感到吃惊，事实上，这还只是数据库层面的耗时，还没有算后端服务的处理链路时间，以及返回给前端的数据渲染时间，以百万级的单表查询为例，如果数据库查询耗时 1 秒，再经过后端的数据封装处理，前端的数据渲染处理，以及网络传输时间，没有异常的情况下，差不多在 3～4 秒之间，可能有些同学对这个请求时长数值还不太敏感。
据互联网软件用户体验报告，当平均请求耗时在1秒之内，用户体验是最佳的，此时的软件也是用户留存度最高的；2 秒之内，还勉强过的去，用户能接受；当超过 3 秒，体验会稍差；超过 5 秒，基本上会卸载当前软件。
有的公司为了提升用户体验，会严格控制请求时长，当请求时长超过 3 秒，自动放弃请求，从而倒逼技术优化调整 SQL 语句查询逻辑，甚至调整后端整体架构，比如引入缓存中间件 redis，搜索引擎 elasticSearch 等等。
继续回到我们本文所需要探讨的问题，当单表数据量到达百万级的时候，查询效率急剧下降，如何优化提升呢？
二、解决方案
下面我们一起来看看具体的解决办法。
2.1、方案一：查询的时候，只返回主键 ID
我们继续回到上文给大家介绍的客户表查询，将select *改成select id，简化返回的字段，我们再来观察一下查询耗时。
当起点位置在 100000 的时候，仅耗时：73 ms
当起点位置在 500000 的时候，仅耗时：274 ms
当起点位置在 1000000 的时候，仅耗时：471 ms
可以很清晰的看到，通过简化返回的字段，可以很显著的成倍提升查询效率。
实际的操作思路就是先通过分页查询满足条件的主键 ID，然后通过主键 ID 查询部分数据，可以显著提升查询效果。
-- 先分页查询满足条件的主键ID
select id from bizuser order by id limit 100000,10;
-- 再通过分页查询返回的ID，批量查询数据
select * from bizuser where id in (1,2,3,4,.....);
2.2、方案二：查询的时候，通过主键 ID 过滤
这种方案有一个要求就是主键ID，必须是数字类型，实践的思路就是取上一次查询结果的 ID 最大值，作为过滤条件，而且排序字段必须是主键 ID，不然分页排序顺序会错乱。
查询 100000～1000100 区间段的数据，仅耗时：18 ms
查询 500000～5000100 区间段的数据，仅耗时：18 ms
查询 1000000～1000100 区间段的数据，仅耗时：18 ms
可以很清晰的看到，带上主键 ID 作为过滤条件，查询性能非常的稳定，基本上在20 ms内可以返回。
这种方案还是非常可行的，如果当前业务对排序要求不多，可以采用这种方案，性能也非常杠！
但是如果当前业务对排序有要求，比如通过客户最后修改时间、客户最后下单时间、客户最后下单金额等字段来排序，那么上面介绍的【方案一】，比【方案二】查询效率更高！
2.3、方案三：采用 elasticSearch 作为搜索引擎
当数据量越来越大的时候，尤其是出现分库分表的数据库，以上通过主键 ID 进行过滤查询，效果可能会不尽人意，例如订单数据的查询，这个时候比较好的解决办法就是将订单数据存储到 elasticSearch 中，通过 elasticSearch 实现快速分页和搜索，效果提升也是非常明显。
关于 elasticSearch 的玩法，之前有给大家介绍过具体的实践，这里不在过多撰书。
三、小结
不知道大家有没有发现，上文中介绍的表主键 ID 都是数值类型的，之所以采用数字类型作为主键，是因为数字类型的字段能很好的进行排序。
但如果当前表的主键 ID 是字符串类型，比如 uuid 这种，就没办法实现这种排序特性，而且搜索性能也非常差，因此不建议大家采用 uuid 作为主键ID，具体的数值类型主键 ID 的生成方案有很多种，比如自增、雪花算法等等，都能很好的满足我们的需求。
本文主要围绕大表分页查询性能问题，以及对应的解决方案做了简单的介绍，
