一:MySQL数据库优化考量标准
1.数据库设计(表设计合理)三范式(规范的模式)
三范式包括:
第一范式:1NF是对属性的原子性的约束,要求属性具有原子性,不可再分解。(只要是关系型数据库都满足)
第二范式:2NF是记录的唯一性约束,要求记录有唯一标识,即实体的唯一性。(设立主键)
第三范式:3NF是对字段冗余性约束,即任何字段不能由其他字段派生出来,要求字段没有冗余。(通过表外键)
逆范式(适当的冗余):提高查询效率。PS:冗余应当放在记录尽量少的表上,避免造成空间浪费。.
2.sql语句优化
3.数据库参数的配置(缓存大小)
4.恰当的硬件资源和操作系统
二:sql语句优化步骤
1.通过show status命令了解各种sql的执行效率
show status命令可以显示你的MySQL数据库的当前状态。关心"Com_"打头的数据语句。
显示当前控制台的MySQL情况:
show status like "Com%" ; <=> show session status like "Com%";
显示数据库从启动到此时的情况:
show global status like "Com%";
显示连接数据库的次数:
show status like "Connections";
显示服务器工作了的时间:
show status like "Uptime";
显示慢查询的次数(默认是10s):
show status like "slow_queries";
显示慢查询时间:
show variables like "long_query_time"
设置慢查询时间(2s):
set long_query_time=2;
2.定位到执行效率较低的sql语句
MySQL数据库支持将慢查询语句记录到日志中,供程序猿分析(默认情况下不启动日志功能)。启动:进入到MySQL的安装目录bin下
mysqld.exe --slow-query-log. //以日志功能方式启功MySQL
3.通过explain 分析低效率的sql语句的执行情况
explain一款非常重要的工具。这个分析工具可以对sql语句进行分析可以预测sql的执行效率。
4.确定问题并提供相应的优化措施
三:sql语句的几种类型
ddl(数据库定义语言):create,alter,drop
dml(数据库操作语言):insert,delete,update
select
dtl(数据库事务语言):commit,rollback,savepoint
dcl(数据库控制语言):grant,revoke
四:数据库存储引擎MyISAM和InnoDB比较
1.MyISAM既不支持外键也不支持事务。InnoDB都支持。
2.如果存储引擎是MyISAM的,则当创建一个表后有三个文件:
*.frm(记录表结构),*.myd(记录数据),*.myI(记录索引)
InnoDB只对应一个文件*.frm。数据存储在ibdata1文件中。
3.对于MyISAM存储引擎数据库要定时清理:
执行命令:optimize table 表名;
五:sql语句优化之添加索引
索引的原理:对于MyISAM存储引擎,索引是添加在.myI文件中。数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。再次查询速度将明显变快,但是牺牲了删除,修改,添加表数据的代价。
索引的类型:
1.主键索引。主键自动的为主索引(类型为Primary)主键自带索引
2.唯一索引(UNIQUE)。唯一性同时又是索引
3.普通索引(Index)
4.全文索引(FULLTEXT)。只有MyISAM存储引擎支持
5.复合索引(多列和在一起)。create index 索引名 on 表名 (列1,列2);
创建索引:
1.create [UNIQUE / FULLTEXT] index 索引名 on 表名 (列名...);
2.alter table 表名 add index 索引名 (列名...);
3.如果添加主键索引:alter table 表名 add primary key(列名);
删除索引:
1.drop index 索引名 on 表名;
2.alter table 表名 drop index 索引名;
3.如果删除主键索引:alter table 表名 drop primary key;
查看某表的所有索引:
1.show indexes from 表名;
2.show keys from 表名;
3.desc 表名;
!!!PS!!!下列表查询将不使用索引:
1.如果查询条件中有or,即使其中有索引也不会使用(or指令要少用)。
2.对于使用多列索引的表,只有最左边的列才能使用到索引,其余列则不会使用索引。
3.对于使用like查询,查询如果是"%aaa"不会使用到索引,"aaa%"则会使用到索引。
4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不会使用索引。
5.如果MySQL估计使用全表扫描要比使用索引快,则不使用索引。
查看索引的使用情况:
show status like "Handler_read%";
注意结果:Handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。Handler_read_rnd_next:这个值越高,说明查询低效。
六:explain对sql语句的分析
例如:explain select * from emp where empno=2000\G;
会产生以下信息:
select_type:表示查询的类型。
table:对哪张表查询。
type:表示表的连接类型。
possible_keys:表示查询时,可能使用的索引类型。
key:表示实际使用的索引类型。
key_len:索引的字段长度。
rows:扫描的行数。
Extra:执行情况的描述和说明。
Type的三种类型:
1.ALL:完整的表扫描,通常不好。eg:explain select * from emp\G;
2.system:表仅有一行,这是const连接类型的特例。
3.const:表最多有一个匹配行。
Extra分析:
Notables:不存在表。
Using filesort:当Query中包含ORDER BY操作,而且无法利用索引进行排序。
Using temporary:某些操作必须使用临时表,常见GROUP BY,ORDER BY。
Using where:不用读取表中所有信息,仅通过索引就能获取所需信息。
七:常见sql优化
1.大批量插入数据:
对于MyISAM存储引擎:
alter table 表名 disable keys; //避免建立大量索引
loading data;
alter table 表名 enable keys;
对于InnoDB存储引擎:
1.将要导入的数据按照主键排序
2.set unique_checks=0; 关闭唯一性校验
3.set autocommit=0; 关闭自动提交
2.优化group by语句
默认情况,MySQL对所有的group by列进行排序,这与在查询中指定order by列类似。如果在查询中包括group by但用户想要避免查询结果的消耗,则可以使用order by null禁止排序
eg: select * from dept group by ename order by null;
3.如果在含有or的查询语句中利用索引,则or之间的每个条件列都必须用到索引,如果没有索引,则应该考虑增加索引。
4.在精度要求高的应用中,建议使用定点数(decimal)来存储数值,不使用浮点数(float),以保证结果的准确性。
5.对于MyISAM的存储引擎的数据库,如果经常做删除和修改记录的操作,要定时执行optimize table 表名;对表进行碎片整理。
6.日期类型要根据实际需要选择最小存储的类型(timestamp:4个字节,datetime:8个字节)。