反 3NF 实际上一定的冗余是允许的 就是反 3NF 在表的 1对N 情况下,为了提高效率,可能会在 1 这表中设计字段 提速 譬如 相册的浏览量 相册 字段 id name views 相片 字段 id name path views 这里的views字段,在两个表中都有,可以避免显示相册浏览量的时候再去查询计算相册内相片的浏览量总和 以此冗余提高查询效率
SQL优化的一般步骤 ① 通过 show status 命令了解各种SQL的执行频率 ② 定位执行效率较低的SQL语句 (重点select) ③ 通过 explain 分析低效率的SQL语句的执行情况 ④ 确定问题并采取相应的优化措施
SQL语句本身的优化 问题:如果从一个大型项目中,迅速的定位执行速度慢的语句 ① 首先了解MySQL数据库的一些运行状态如何查询 show status 比如想知道当前MySQL运行时间,一共执行了多少次select/update/delete 当前连接 等等 常用的 show status like 'uptime' 当前MySQL运行时间 show status like 'com_select' 当前MySQL执行了多少次查询 show status like 'com_insert' 当前MySQL执行了多少次添加 show status like 'com_update' 当前MySQL执行了多少次更新 show status like 'com_delete' 当前MySQL执行了多少次删除 show status 语法: show [session|global] status like ''; 如果不写 [session|global] 表示默认是 session 指取出当前窗口的执行情况 如果想看所有(mysql启动到现在)的情况 加上 global show global status like 'com_insert'; show status like 'connections';试图连接MySQL的连接数
显示慢查询次数 show status like 'slow_queries';
② 如何去定位慢查询 构建一个大表(400万数据)->存储过程构建
默认情况下,MySQL认为 10秒钟 才是一个慢查询
修改MySQL的慢查询时间 // 显示慢查询时间 show variables like 'long_query_time'; // 修改慢查询时间 set long_query_time=2;// 即修改慢查询时间为2秒
1.2 普通索引 一般来说,普通索引的创建,是先创建表,然后再创建索引 语句:create index 索引名 on 表名 (列名);
1.3 全文索引 全文索引:主要是针对文件,文本的索引,比如文章 全文索引针对MyISAM有用 如何使用全文索引 错误用法: select * from articles where body like '%mysql%'; 这种方法是不会用到全文索引的 正确用法: // title,body是全文索引,匹配database的句子 select * from articles where match(title,body) against('database'); 说明: 1.在MySQL中fulltext索引只针对myISAM生效 2.针对英文生效,对中文需要sphinx(coreseek)技术处理 3.使用方法是match(字段名) against('关键字') 4.全文索引有一个停止词概念: 因为在一个文本中,创建索引是一个无穷大的数,因此,对一些常用词和字符, 就不会创建,这些词,称为 停止词。
1.4 唯一索引 当表的某列被指定为unique约束,这列就是一个唯一索引 唯一索引的列可以为null,并且可以有多个 在创建表后,再去创建唯一索引 创建语法:create unique index 索引名 on 表名 (列名); 2.查询 ① desc 表名 该方法缺点:不能够显示索引的名字 ② show index(es) from 表名 ③ show keys from 表名 3.删除 语法:alter table 表名 drop index 索引名; 主键索引删除:alter table 表名 drop primary key; 4.修改 先删除,再重新创建
在哪些列上适合添加索引? 较频繁的作为查询条件字段创建索引 例如 select * from emp where empno=1; 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件 例如 select * from emp where sex='男'; 更新非常频繁的字段不适合创建索引 例如 select * from emp where logincount=1; 不会出现在where子句中字段不该创建索引
select_type 类型 primary : 子查询中最外层查询 subquery : 子查询内层第一个select,结果不依赖于外部查询 dependent subquery : 子查询内层第一个select,依赖于外部查询 uNIOn : union语句中第二个select开始后面所有select simple : 简单模式 union result : union中合并结果
type 类型 all : 完整的表扫描 通常不好 system : 表仅有一行(=系统表) 这是const联接类型的一个特例 const : 表最多有一个匹配行
extra 类型 no table : query语句中使用 from dual 或不含任何from子句 Using filesort : 当query中包含 order by 操作,而且无法利用索引完成排序 impossible WHERE noticed after reading const tables:Mysql query optimizer 通过收集统计信息不可能存在结果 Using temporary : 某些操作必须使用临时表,常见 group by ,order by Using where : 不用读取表中所有信息,仅通过索引就可以获取所需数据
查看索引的使用情况 show status like 'Handler_read%'; handler_read_key:这个值越高越好,代表使用索引查询到的次数 handler_read_rnd_next:这个值越高,说明查询低效
SQL语句的小技巧 ① 优化 group by 语句 默认情况下,MySQL对所有的group by col1,col2 进行排序,这与在查询中指定 order by col1,col2 类似 如果查询中包括 group by 但用户想尽量避免排序结果的消耗,则可以使用 order by null 禁止排序
② 有些情况下,可以使用连接来替代子查询 因为使用 join MySQL不需要在内存中创建临时表
③ 如果想要在含有 or 的查询语句中利用索引,则 or 之间的每个条件列都必须用到索引, 如果没有索引,则应该考虑增加索引。
外键 classes表 create table classes( id int unsigned not null auto_increment primary key, name varchar(64) not null )engine=innoDB; insert into classes values (1,'aaa');
stu表 create table student( id int unsigned not null auto_increment primary key, name varchar(64) not null, classid int unsigned not null, foreign key (classid) references classes(id) )engine=innoDB; 当设置了外键的时候,企图添加一个外键没有的数据,会报错,无法插入数据 insert into student values (1,'hello',1); 这个是正确的 insert into student values (1,'hello',2); 当classes表中id=2不存在时,这个是错误的
① 在精度要求高的应用中,建议使用定点数来存储数值,以保证数据的准确性。deciaml精度比float高,尽量使用 ② 对于存储引擎是myisam的数据库,如果经常做删除和修改记录的操作, 要定时执行optimize table table_name;功能对表进行碎片整理。 ③ 日期类型要根据实际需要选择能够满足应用的最小存储的早期类型 create table bbs (id int unsigned not null ,con varchar(1024) , pub_time int );
0