67677新澳门手机版 > 数据库 > sql 2000/2005几种分页方法
sql 2000/2005几种分页方法
2020-03-14 20:53

通过SQL 查询分析器,显示比较:我的结论是:分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用

sql优化,sql优化的几种方法

数据库三范式

通俗地理解三个范式,对于数据库设计大有好处。在数据库设计中,为了更好地应用三个范式,就必须通俗地理解三个范式(通俗地理解是够用的理解,并不是最科学最准确的理解):

第一范式:1NF是对属性的原子性约束,要求属性具有原子性,不可再分解;

第二范式:2NF是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性;

第三范式:3NF是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余。

没有冗余的数据库设计可以做到。但是,没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降低范式标准,适当保留冗余数据。具体做法是: 在概念数据模型设计时遵守第三范式,降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段,允许冗余。

sql语句类型:

    ddl(数据定义语言):create ,alter , drop

    dml(数据操作语言):insert , delete ,update

    select

    dtl(数据事务语言):rollback ,commit ,savepoint

    dcl(数据控制语句):grant , revoke

show status命令

    该命令可以显示你的mysql数据库的当前状态,我们主要关心的是"Com"开头的指令

    show status like 'Com%' <=> show session status like 'Com%'//显示当前控制台的状况

    show global status like 'Com%';//显示数据库从启动到查询的次数

    重点关注 Com_select , Com_insert,Com_delete,Com_update,

    show session status like 'Com_select';

    显示尝试连接数据库次数    show status like 'Connections';

    服务器工作时长:show status like 'Uptime';(单位为妙)

    慢查询的次数:show status like 'Slow_queries';(默认是10  show variables like 'long_query_time';)

压力测试脚本

创建表DEPT     /*部门表*/

CREATE TABLE `dept` (
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `dname` varchar(20) NOT NULL DEFAULT '',
  `loc` varchar(13) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

创建表EMP    /*员工表*/

CREATE TABLE `emp` (
  `empno` int(11) NOT NULL DEFAULT '0',
  `ename` varchar(20) NOT NULL DEFAULT '""',
  `job` varchar(20) NOT NULL DEFAULT '“”',
  `mgr` int(11) NOT NULL DEFAULT '0',
  `hiredate` date NOT NULL,
  `sal` decimal(10,0) NOT NULL,
  `comm` decimal(10,0) NOT NULL,
  `deptno` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

#工资级别表

create table salgrade(
    grade mediumint unsigned not null default 0,
    losal decimal(17,2) not null,
    hisal decimal(17,2) not null
)engine=MyISAM default charset=utf8;

insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400);
insert into salgrade values(3,1401,2000);
insert into salgrade values(4,2001,3000);
insert into salgrade values(5,3001,9999);

//随机产生字符串
delimiter $$    //新定义一个命令结束符
drop function rand_string$$
create function rand_string(n int)
returns varchar(255)
begin
    declare chars_str varchar(100) default 'abcdefghijkmnopqrstuvwxyzABCDEFGHIJKMNOPQRSTUVWXYZ';
    declare return_str varchar(255) default '';
    declare i int default 0;
while i<n do
    set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
    set i = i+1;
end while;
return return_str;
end$$
select rand_string(6);
//随机产生部门编号
create function rand_num()
returns int(5)
begin
    declare return_num int(5) default 0;
    set return_num = floor(10+rand()*500);
return return_num;
end$$
select rand_num();
//向emp表中插入记录(海量数据)
create procedure insert_emp(in start int(10),in max_num int(10))
begin
    declare i int default 0;
    set autocommit = 0;
    repeat
    set i = i + 1;
    insert into emp values((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
    until i = max_num
    end repeat;
    commit;
end$$
delimiter ;//将命令结束符改回为分号
//调用存储过程
call insert_emp(100001,180000); //创建十八万员工数据

delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))
begin
    declare i int default 0;
    set autocommit = 0;
    repeat
    set i = i + 1;
    insert into dept values((start+i),rand_string(10),rand_string(8));
    until i = max_num
    end repeat;
    commit;
    end$$
delimiter ;
call insert_dept(100,10);  //创建十个部门

如何在一个项目中,找到慢查询的select,mysql数据库支持把慢查询语句记录到日志中(默认情况下不启动)

1.启动mysql服务

    进入到mysql安装目录

2.启动xx>binmysqld.exe --slow-query-log

修改慢查询时间set long_query_time=0.5;

show status like 'slow%';

建立索引是最优的sql优化方式

(1)能够提高查询效率,但是是以降低插入,删除,更新效率为代价的,因为索引增加了大量的I/O

(2)会占用磁盘空间

explain ,可以对sql进行分析,预测sql的执行效率

explain select * from emp where empno = 123456G;(G纵向展示)

重点关注以下几个字段:

    type: 

        all  -->全表查询,要避免这样的情况

        system -->表仅有一行,这是const联接类型的一个特例

        const -->表最多有一行匹配

    possible_keys : 可能用到的索引

    key : 实际用到的索引

    rows : 从多少行数据中检索出的结果

     extra: (主要针对排序)

        no tables : query语句中使用from dual 或不含任何from子句

        using filesort : 当query语句需要排序(order by) ,且无法利用索引完成排序

        using temporary : 某些操作必须使用临时表,常见group by , order by

        using where : 不用读取表中的信息,仅通过索引就可以获取到数据

索引分四种:

    主键索引(primary) : 把某列设为索引,则就是主键索引

    唯一索引(unique)  : 该列具有唯一性,同时又是索引

    普通索引(index) : 普通索引

     全文索引(fulltext)    : //

     综合使用  --》复合索引

在哪些列上添加索引比较合适:

(1)较为频繁的作为查询条件字段应创建索引 select * from emp where emp.empno = '1';

(2)唯一性太差的字段不适合创建索引,即使频繁作为过滤条件 select * from class where gender = '男';

(3)更新字段非常频繁的字段不适合创建索引

(4)不会出现在where子句中字段不适合创建索引

 

alter table emp add primary key(emp);//添加主键索引

create [unique[FULLTEXT]] index 索引名 on 表名(列名...)

alter table 表名 add index [索引名]

 

alter table emp drop primary key;//删除主键索引

drop index 索引名 on 表名;

alter table emp drop index 索引名;

 

如何查询某表的所有索引:

    show indexes from tablename;

    show keys from tablename;

索引的使用(特殊情况):

(1)    对于创建的多列索引,只要查询条件使用了最左边的列,索引一般会被使用。该说法是针对 alter table dept add index myindex (dname,loc);在这种情况下,索引会建在dname上,用loc去查询的时候是没有索引的;

(2)    对于使用like的查询,%aaa是不用索引的,aaa%和aa%a都会用到索引;

(3)    如果条件中有or,则不会用到索引

(4)    如果加了索引的列类型是字符串,那一定要在条件中用引号引用起来才会用到索引,否则不用。

(5)    如果mysql的算法算出使用全表扫描会更快的话,那么就不使用索引

如何检测创建索引的是否有效:

    show status like 'handler_read_key';这个值越高,说明使用索引查询到的次数越多,索引有效

    show status like 'handler_read_rnd_next';这个值越高,说明查询效率越低

数据库三范式 通俗地理解三个范式,对于数据库设计大有好处。在数据库设计中,为了更好地应用三个范式,...

如果没有主键,可以用临时表,也可以用方案三做,但是效率会低。建议优化的时候,加上主键和索引,查询效率会提高。

通过SQL 查询分析器,显示比较:我的结论是:分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用

1.如果有一个自动增长的id字段,则:

定义二个变量:Page,PageCount

Select top PageCount * From [tb_code] Where id=(select min(id) from (select top (Page-1)*PageCount+1 id from [tb_code] order by id desc) as t) order by id desc

原理,根据ID计算出(Page-1)页的最小值,然后用TOP关键字及可解决问题。

2.SELECT TOP 10 id,username From [tb_code] where id not in ( SELECT TOP 20000 id FROM tb_code ORDER BY username)

优点:此方法可以根据表中的任一个字段排序,在一个表中有几百万条记录时,仍有很高的效率,缺点是在大数据量的情况下效率略逊于第一种

3.SELECT TOP 10 id,username From (SELECT TOP page*pagecount id, username FROM tb_code ORDER BY username) DERIVEDTBL ORDER BY username DESC

优点:此方法可以根据表中的任一个字段排序. 缺点是效率最低