MySQL数据库优化总结

一个：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";

   显示server工作了的时间：

   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字节）。