面试篇三:数据库MySQL、Oracle

康慧欣 2020-05-02

  • 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; #类型转换没有发生在索引列上,索引的用途没有被改变

相关推荐