数据库三范式
通俗地理解三个范式,对于数据库设计大有好处。在数据库设计中,为了更好地应用三个范式,就必须通俗地理解三个范式(通俗地理解是够用的理解,并不是最科学最准确的理解):
第一范式: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>bin\mysqld.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 = 123456\G;(\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';这个值越高,说明查询效率越低