面试篇三：数据库MySQL、Oracle
- MySQL
- MySQL分页查询
SELECT * FROM admin_company LIMIT 0,10; SELECT * FROM admin_company ORDER BY id LIMIT 0,10;
limit后参数代表什么意思？
LIMIT[位置偏移量,]行数，第一个参数是从哪个序号加上，初始是0，第二个参数是查询多少行记录。
还有没有其他方式？
- MySQL怎么查看执行计划
用explain，如 SELECT * FROM admin_company
https://www.cnblogs.com/scorpio-cat/p/12661216.html
MySQL索引
索引分类：主键索引、唯一索引、普通索引、全文索引、组合索引。
MySQL默认会对主键和唯一键列创建索引。
MySQL在创建表时就会创建一主键索引。如果主键存在，则存主键；如果主键不存在，但唯一键存在；如果主键和唯一健都不存在，则存6个字节的rowid。
索引的优点：减少服务器需要扫描的数据量、帮助服务器避免排序和临时表、将随机io变成顺序io。
索引匹配方式：
（1）全值匹配：where条件为=。
（2）最左前缀匹配：组合索引时，SQL必须包括组合索引指定的第一列；如果不存在，则不会走索引。
（3）列前缀匹配：使用like模糊查询，like ‘123%‘。
（4）范围值匹配：between and，> , <，但是索引中，如果包括了范围判断，则会导致后面的查询条件不走索引，只有前面的列和该范围列走索引。
（5）精确匹配某一列并范围匹配另一列。
（6）只访问索引的查询。
https://www.cnblogs.com/scorpio-cat/p/12661379.html
- Oracle
- Oracle分页查询
SELECT * FROM
(SELECT ROWNUM rn ,* FROM admin_company WHERE rn <= 10)
WHERE rn > 0;没有ORDER BY，两层查询；如果要排序使用上面两层查询，会出现什么问题？查询结果有问题。
ROWNUM伪列产生的序号是按照数据被查询出来的顺序添加上去的，第一条是1,第二条是2,依次加1。
在ORACLE中使用rownum伪列分页时，需要多加一层查询，以保证rownum序号的连续性。
SELECT * FROM
(SELECT ROWNUM rn , c.* FROM
(SELECT * FROM admin_company ORDER BY companyno) c WHERE rn <= 10
)
WHERE rn > 0;当将一条语句交给查询优化器处理时：
如果排序列上有索引，则借助索引去查询数据，这样，读取出来的数据和rownum产生的序号是一种正常的对应关系。
如果排序列上没有索引，则使用全表扫描的方式，依次从表中读取数据，读取完成后，最后进行排序，可能产生的rownum序号不连续。
正是由于排序列上不一定有索引，所以在ORACLE中使用rownum伪列分页时，需要多加一层查询，以保证rownum序号的连续性。
- Oracle怎么查询执行计划
（1）通过使用工具PLSQL Developer中的Explain Plan Window窗口查看SQL执行计划。快捷键为F5。
（2）通过explain plan for explain plan for select *admin_company; 。
索引
作用：提高查询速度、确保数据的唯一性、可以加速表和表之间的连接，实现表和表之间的参照完整性、使用分组和排序子句进行数据检索时，可以减少分组和排序的时间、全文检索字段进行搜素优化。
分类：主键索引（PRIMAY KEY）、唯一索引（UNIQUE）、常规索引（INDEX）、全文索引（FULLTEXT）。
主键索引的几种创建方式：确保数据记录的唯一性，主键索引只能有一个。（以下为MYSQL示例）
CREATE TABLE mytable ( ID INT(11) AUTO_INCREMENT PRIMARY KEY, username VARCHAR (16) NOT NULL #或 PRIMARY KEY(`ID`) ) ;
唯一索引的几种创建方式：避免同一个表中某数据列中的值重复，唯一索引可有多个。（以下为MYSQL示例）
（1）创建索引： CREATE UNIQUE INDEX indexName ON mytable(username(length));
（2）修改表结构： ALTER table mytable ADD UNIQUE [indexName] (username(length));
（3）创建表时指定：
CREATE TABLE mytable ( ID INT NOT NULL, username VARCHAR (16) NOT NULL, UNIQUE [ indexName ] (username (LENGTH)) # 或者username VARCHAR(16) NOT NULL UNIQUE ) ;
常规索引的几种创建方式：快速定位特定数据，应加在查询条件的字段，不易添加太多常规索引，影响数据的插入，删除和修改操作，使用KEY或INDEX关键字设置。（以下为MYSQL示例）
（1）创建表时添加：
CREATE TABLE mytable ( ID INT NOT NULL, userno VARCHAR (16) NOT NULL, username VARCHAR (16) NOT NULL, loginname VARCHAR (16) NOT NULL, INDEX `index1` (userno, username), KEY `index2` (userno, loginname) ) ;
（2）创建后追加： ALTER TABLE `mytable` ADD INDEX `ind` (`userno`,`username`);
全文索引的几种创建方式：快速定位特定数据，只能用于MyISAM类型的数据表，只能用于CHAR ,VARCHAR,TEXT数据列类型。（以下为MYSQL示例）
（1）创建表时添加：
CREATE TABLE mytable( username VARCHAR (16) NOT NULL, FULLTEXT(`username`) )ENGINE=MYISAM;
（2）创建后追加： ALTER TABLE mytable ADD FULLTEXT(`username`);
SQL怎么优化执行效率更高、SQL优化经验
（1）SELECT子句中避免使用‘*’：Oracle在解析的过程中, 会将‘*’依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
（2）使用表的别名(Alias)： 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
（3）用IN来替换OR、用UNION替换OR (适用于索引列)、用EXISTS替代IN、用NOT EXISTS替代NOT IN。
（4）如果不需要去重，用UNION-ALL 替换UNION：UNION 将对结果集合进行合并和排序,这个操作会使用到SORT_AREA_SIZE这块内存，UNION ALL 将重复输出两个结果集合中相同记录，排序也不是必要的，效率就会因此得到提高。
（5）优化GROUP BY：提高GROUP BY 语句的效率，可以通过将不需要的记录在GROUP BY 之前过滤掉。
（6）使用DECODE函数来减少处理时间。【TODO】
（7）用Where子句替换HAVING子句：HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序,总计等操作。
（8）合理使用索引：
①避免在索引列上使用NOT，当Oracle“遇到”NOT,他就会停止使用索引转而执行全表扫描。
②避免在索引列使用 !=、||、+，WHERE子句中，优化器将不使用索引而使用全表扫描。
③避免在索引列上使用计算。WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。
④避免在索引中使用任何可以为空的列，Oracle将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。对于复合索引，如果每个列都为 空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。
⑤总是使用索引的第一个列：如果索引是建立在多个列上，只有在它的第一个列(leading column)被where子句引用时，优化器才会选择使用该索引。这也是一条简单而重要的规则，当仅引用索引的第二个列时，优化器使用了全表扫描而忽略 了索引。
⑥避免对WHERE子句的列名使用函数（避免改变索引列的类型）：当比较不同数据类型的数据时，如：
SELECT * FROM EMP WHERE EMPNO = TO_NUMBER(‘123‘); #类型转换没有发生在索引列上,索引的用途没有被改
SELECT * FROM EMP WHERETO_NUMBER(EMP_TYPE)=123; #类型转换没有发生在索引列上,索引的用途没有被改变