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 TABLEmytable (
IDINT(11) AUTO_INCREMENT PRIMARY KEY,
usernameVARCHAR (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 TABLEmytable (
IDINT NOT NULL,
usernameVARCHAR (16) NOT NULL,UNIQUE [indexName](username (LENGTH))
# 或者usernameVARCHAR(16) NOT NULL UNIQUE) ;
常规索引的几种创建方式:快速定位特定数据,应加在查询条件的字段,不易添加太多常规索引,影响数据的插入,删除和修改操作,使用KEY或INDEX关键字设置。(以下为MYSQL示例)
(1)创建表时添加:
CREATE TABLEmytable (
IDINT NOT NULL,
usernoVARCHAR (16) NOT NULL,
usernameVARCHAR (16) NOT NULL,
loginnameVARCHAR (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 TABLEmytable(
usernameVARCHAR (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; #类型转换没有发生在索引列上,索引的用途没有被改变