搜索引擎优化试题(Mysql查询优化之前，我先说一个常见的面试题)

　　搜索引擎优化试题(Mysql查询优化之前，我先说一个常见的面试题)

　　今天在说Mysql查询优化之前，先说一个常见的面试题，并带着题深入讨论。这会让大家有更深入的了解。

　　1、Mysql数据库中一个表的数据超过1000万条。如何快速找出第 9 百万项后的 100 条数据？

　　怎么查，谁能告诉我答案？有没有人在想，你不是一句话就搞定了吗？

　　select * from table limit 9000000,100;

　　那就试试吧，执行这条SQL看看

　　你看见了吗？我查了100条数据，用了7.063s。这能算是一个快速查询吗？这个速度估计没人能接受！基于这个问题，我今天要讲的就是大数据的快速查询。

　　首先，我演示了大数据分页查询。我的测试表中有超过1000万条数据。然后我使用 limit 进行分页测试： select * from test limit 0,100;

　　耗时：0.005s

　　从测试限制 1000,100 中选择 *；

　　耗时：0.006s

　　从测试限制 10000,100 中选择 *；

　　耗时：0.013s

　　从测试限制 100000,100 中选择 *；

　　耗时：0.104s

　　从测试限制 500000,100 中选择 *；

　　耗时：0.395s

　　从测试限制 1000000,100 中选择 *；

　　耗时：0.823s

　　从测试限制 5000000,100 中选择 *；

　　耗时：3.909s

　　select * from test limit 10000000,100;

　　耗时：10.761s

　　我们发现分页查询越晚，查询越慢的现象。这也让我们得出一个结论：

　　1、limit语句的查询时间与起始记录的位置成正比。

　　2、mysql的limit语句很方便，但是不适合直接用于记录很多的表。

　　大数据量限制的分页性能优化

　　说到查询优化，我们首先想到的肯定是索引的使用。如果使用索引查询的语句中的条件只收录该索引列，那么这种情况下查询速度会非常快。因为索引搜索有相应的优化算法，而且数据在查询索引上，不需要去寻找相关的数据地址，节省了很多时间。另外Mysql里面有相关的索引缓存，并发度高的时候用缓存比较好。

　　我的测试表使用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（索引顺序访问方法）的缩写，是一种存储记录和文件的标准方法。它不是事务安全的并且不支持外键。如果进行大量的选择，插入 MyISAM 更合适。InnoDB：支持事务安全的引擎，支持外键、行锁、事务是他最大的特点。如果更新和插入比较多，建议使用InnoDB，特别是对于多个并发和高 QPS 的情况。注意：MySQL 5.5之前的版本默认搜索引擎为MyISAM，MySQL 5.5之后的版本默认搜索引擎更改为InnoDB。MyISAM 和 InnoDB 的区别：

　　1. InnoDB 支持事务，但 MyISAM 不支持。对于InnoDB，每种SQL语言默认封装为一个事务，自动提交，会影响速度，所以最好在begin和commit之间放多个SQL语言，形成一个事务；

　　2. InnoDB 支持外键，但 MyISAM 不支持。

　　3. InnoDB是一个聚集索引，使用B+Tree作为索引结构。数据文件绑定到（主键）索引（表数据文件本身就是B+Tree组织的索引结构）。它必须有一个主键。主键索引非常有效。MyISAM 是非聚集索引，同样使用 B+Tree 作为索引结构。索引和数据文件是分开的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

　　4、InnoDB不保存表中具体的行数，执行select count(*) from table时需要进行全表扫描。MyISAM用一个变量来保存整个表的行数，执行上述语句时只需要读取该变量，非常快。

　　5、Innodb不支持全文索引，而MyISAM支持全文索引。MyISAM 具有更高的查询效率；InnoDB 7 以后将支持全文索引。

　　6、InnoDB支持表级锁和行级锁（默认），而MyISAM支持表级锁。；

　　7、InnoDB表必须有主键（如果不是用户指定的，用户会自行查找或产生主键），但Myisam可能没有。

　　8、innodb存储文件有frm和ibd，而Myisam有frm、MYD、MYI。

　　innodb：frm是表定义文件，ibd是数据文件。

　　myisam：frm是表定义文件，myd是数据文件，myi是索引文件。

　　3.你会怎么做优化Mysql复杂查询语句？

　　说到复杂的SQL优化，最常见的原因是多表关联导致大量复杂的SQL语句。那么我们如何优化这条SQL呢？实际优化也是有套路的，只要按照套路去执行。复杂SQL优化方案：

　　1.使用EXPLAIN关键词检查SQL。EXPLAIN 可以帮助您分析查询语句或表结构的性能瓶颈。EXPLAIN的查询结果还会告诉你你的索引主键是怎么用的，你的数据表是怎么搜索排序的，有没有全表扫描等；

　　2、查询条件尽量使用索引字段。如果一个表有多个条件，尽量使用复合索引查询。使用复合索引时，要注意字段的顺序。

　　3、多表关联尽量使用join，减少子查询的使用。如果表的关联字段可以使用主键，就使用主键，即尽量使用索引字段。如果关联字段不是索引字段，可以根据情况考虑增加索引。

　　4.分页批量查询尽量使用limit，不要一下子搞定。

　　5、绝对避免使用select *，尽量选择具体需要的字段，减少不必要字段的查询；

　　6. 尝试转换或联合所有。

　　7. 尽量避免使用is null 或is not null。

　　8.注意使用like，前模糊和全模糊不会被索引。

　　9、Where后的查询字段尽量少使用函数，因为函数会导致索引失败。

　　10. 避免使用不等式 (!=)，因为它不会使用索引。

　　11、用exists代替in，notexists代替notin，效率会更好；

　　12. 避免使用 HAVING 子句。HAVING 只会在检索到所有记录后过滤结果集。这个处理需要排序、合计等操作。如果可以通过 WHERE 子句限制记录数，就可以减少这种开销。

　　13. 不要按 RAND() 排序

　　接下来，我将继续总结一些面试问题，与大家分享。如果您觉得内容不错，请关注本公众号。我会时不时推一些干货给你。