面试篇三：数据库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; #类型转换没有发生在索引列上,索引的用途没有被改变

面试篇三：数据库MySQL、Oracle

相关推荐