Mysql数据库千万级数据查询优化⽅案.....
⼀，Mysql数据库中⼀个表⾥有⼀千多万条数据，怎么快速的查出第900万条后的100条数据？
怎么查，谁能告诉我答案？有没有⼈想着，不就⼀条语句搞定嘛
select * from table limit 9000000,100;
matlab中round函数的用法那我们试试，去执⾏下这个SQL看看吧
看见了吗，查了100条数据⽤了7.063s。这能算的上是快速查询吗，估计没⼈能接受了这种速度吧！基于这个问题，我今天就要说说⼤数据时的快速查询了。
⾸先，我演⽰下⼤数据分页查询，我的test表⾥有1000多万条数据，然后使⽤limit进⾏分页测试：select * from test limit 0,100;
数据库课程设计运动会管理系统耗时：0.005s
select * from test limit 1000,100;
耗时：0.006s
django个人博客项目
select * from test limit 10000,100;
耗时：0.013s
select * from test limit 100000,100;
耗时：0.104s
select * from test limit 500000,100;
耗时：0.395s
select * from test limit 1000000,100;
耗时：0.823s
select * from test limit 5000000,100;
耗时：3.909s
select * from test limit 10000000,100;
耗时：10.761s
我们发现⼀个现象，分页查询越靠后查询越慢。这也让我们得出⼀个结论：
mysql面试题sql优化1，limit语句的查询时间与起始记录的位置成正⽐。
2，mysql的limit语句是很⽅便，但是对记录很多的表并不适合直接使⽤。
对⼤数据量limit分页性能优化
说到查询优化，我们⾸先想到的肯定是使⽤索引。利⽤了索引查询的语句中如果条件只包含了那个索引列，那在这种情况下查询速度就很快
了。因为利⽤索引查有相应的优化算法，且数据就在查询索引上⾯，不⽤再去相关的数据地址了，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发⾼的时候利⽤缓存就效果更好了。
我的test表使⽤InnoDB作为存储引擎，id作为⾃增主键，默认为主键索引。那我们现在⽤覆盖索引查询，看看效果如何：
SELECT id FROM test LIMIT 9000000,100;
总耗时4.256s，相对于7.063s少了很多。
现在优化的⽅案有两种，即通过id作为查询条件使⽤⼦查询实现和使⽤join实现；
1，id>=的（⼦查询）形式实现
select * from test where id >= (select id from test limit 9000000,1)limit 0,100
耗时 4.262s;
2，使⽤join的形式；
SELECT * FROM test a JOIN (SELECT id FROM test LIMIT 9000000,100) b ON a.id = b.id
耗时 4.251s；这两种优化查询使⽤时间⽐较接近，其实两者⽤的都是⼀个原理，所以效果也差不多。但个⼈建议最好使⽤join，尽量减少⼦查询的使⽤。注：⽬前是千万级别查询，如果将⾄百万级别，速度会更快，我有亲⾃测试⼀下语句，查询时间0.410s。
SELECT * FROM test a JOIN (SELECT id FROM test LIMIT 1000000,100) b ON a.id = b.id
⼆，你⽤过mysql那些存储引擎，他们都有什么特点和区别？
这是⾼级开发者⾯试时经常被问的问题。实际我们在平时的开发中，经常会遇到的，在⽤SQLyog等⼯具创建表时，就有⼀个引擎项要你去选。如下图：
Mysql的存储引擎有这么多种，实际我们在平时⽤的最多的莫过于InnoDB和MyISAM了。所有如果⾯
试官问道mysql有哪些存储引擎，你只需要告诉这两个常⽤的就⾏。那他们都有什么特点和区别呢？MyISAM：默认表类型，它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的顺序访问⽅法) 的缩写，它是存储记录和⽂件的标准⽅法。不是事务安全的，⽽且不⽀持外键，如果执⾏⼤量的select，insert MyISAM⽐较适合。InnoDB：⽀持事务安全的引擎，⽀持外键、⾏锁、事务是他的最⼤特点。如果有⼤量的update 和insert，建议使⽤InnoDB，特别是针对多个并发和QPS较⾼的情况。注：在MySQL 5.5之前的版本中，默认的搜索引擎是MyISAM，从MySQL 5.5之后的版本中，默认的搜索引擎变更为InnoDB。MyISAM和InnoDB的区别：
1. InnoDB⽀持事务，MyISAM不⽀持。对于InnoDB每⼀条SQL语⾔都默认封装成事务，⾃动提交，这样会影响速度，所以最好把多条
SQL语⾔放在begin和commit之间，组成⼀个事务；
2. InnoDB⽀持外键，⽽MyISAM不⽀持。
3. InnoDB是聚集索引，使⽤B+Tree作为索引结构，数据⽂件是和（主键）索引绑在⼀起的（表数据⽂件本⾝就是按B+Tree组织的⼀个
索引结构），必须要有主键，通过主键索引效率很⾼。MyISAM是⾮聚集索引，也是使⽤B+Tree作为
索引结构，索引和数据⽂件是分离的，索引保存的是数据⽂件的指针。主键索引和辅助索引是独⽴的。
4. InnoDB不保存表的具体⾏数，执⾏select count(*) from table时需要全表扫描。⽽MyISAM⽤⼀个变量保存了整个表的⾏数，执⾏上述
语句时只需要读出该变量即可，速度很快。
免费空间大的网盘
5. Innodb不⽀持全⽂索引，⽽MyISAM⽀持全⽂索引，查询效率上MyISAM要⾼；5.7以后的InnoDB⽀持全⽂索引了。
6. InnoDB⽀持表、⾏级锁(默认)，⽽MyISAM⽀持表级锁。；
定义常量7. InnoDB表必须有主键（⽤户没有指定的话会⾃⼰或⽣产⼀个主键），⽽Myisam可以没有。
8. Innodb存储⽂件有frm、ibd，⽽Myisam是frm、MYD、MYI。
9. Innodb：frm是表定义⽂件，ibd是数据⽂件。
10. Myisam：frm是表定义⽂件，myd是数据⽂件，myi是索引⽂件。
三，Mysql复杂查询语句的优化，你会怎么做？
说到复杂SQL优化，最多的是由于多表关联造成了⼤量的复杂的SQL语句，那我们拿到这种sql到底该怎么优化呢，实际优化也是有套路的，只要按照套路执⾏就⾏。复杂SQL优化⽅案：
1. 使⽤EXPLAIN关键词检查SQL。EXPLAIN可以帮你分析你的查询语句或是表结构的性能瓶颈，就得EXPLAIN 的查询结果还会告诉你
你的索引主键被如何利⽤的，你的数据表是如何被搜索和排序的，是否有全表扫描等；
2. 查询的条件尽量使⽤索引字段，如某⼀个表有多个条件，就尽量使⽤复合索引查询，复合索引使⽤要注意字段的先后顺序。
3. 多表关联尽量⽤join，减少⼦查询的使⽤。表的关联字段如果能⽤主键就⽤主键，也就是尽可能的使⽤索引字段。如果关联字段不是索
引字段可以根据情况考虑添加索引。
4. 尽量使⽤limit进⾏分页批量查询，不要⼀次全部获取。
5. 绝对避免select *的使⽤，尽量select具体需要的字段，减少不必要字段的查询；
6. 尽量将or 转换为 union all。
7. 尽量避免使⽤is null或is not null。
8. 要注意like的使⽤，前模糊和全模糊不会⾛索引。
9. Where后的查询字段尽量减少使⽤函数，因为函数会造成索引失效。
10. 避免使⽤不等于（!=），因为它不会使⽤索引。
11. ⽤exists代替in，not exists代替not in，效率会更好；
12. 避免使⽤HAVING⼦句, HAVING 只会在检索出所有记录之后才对结果集进⾏过滤，这个处理需要排序，总计等操作。如果能通过
WHERE⼦句限制记录的数⽬，那就能减少这⽅⾯的开销。
13. 千万不要 ORDER BY RAND()
接下来会继续总结⼀些⾯试中的问题共享给⼤家，如果觉得内容不错请关注我，我会不定期的推送⼀些⼲货给⼤家。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。