MySQL性能优化

 

 

 

u      mysql的优化

1.      数据库(表)设计合理

我们的表设计要符合3NF   3范式(规范的模式) , 有时我们需要适当的逆范式

2.      sql语句的优化(索引，常用小技巧.)

3.      数据的配置(缓存设大)

4.      适当硬件配置和操作系统 (读写分离.)

u       数据的3NF

1NF :就是具有原子性，不可分割.(只要使用的是关系性数据库，就自动符合)

2NF: 在满足1NF 的基础上，我们考虑是否满足2NF: 只要表的记录满足唯一性,也是说,你的同一张表，不可能出现完全相同的记录, 一般说我们在 表中设计一个主键即可.

3NF: 在满足2NF 的基础上，我们考虑是否满足3NF：即我们的字段信息可以通过关联的关系，派生即可.(通常我们通过外键来处理)

适当的逆范式,比如相册的点击次数与相片的点击次数统计。

u      sql语句的优化

sql语句分类：

ddl (数据定义语言) [create alter drop]

dml(数据操作语言)[insert delete upate ]

select（查询语句，用得最多）

dtl(数据事务语句) [commit rollback savepoint]

dcl(数据控制语句) [grant  revoke]

u      show status命令

该命令可以显示你的mysql数据库的当前状态.我们主要关心的是 “com”开头的指令

show status like ‘Com%’  <=> show session  status like ‘Com%’  //显示当前控制台的情况

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

显示连接数据库次数

show status like  'Connections';

这里我们优化的重点是在 慢查询. (在默认情况下是10秒 ) mysql5.5.19

显示查看慢查询的情况

show variables like ‘long_query_time’

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

启动步骤:

1） 要这样启动mysql

进入到 mysql安装目录

2）启动 xx>bin\mysqld.exe –slow-query-log 

l        数据索引

1） 可以加快快查询速度，但以牺牲删除、修改、添加的性能为代价。

2） 索引信息存在*.myi文件中

工具 explain可以对 sql语句进行分析,可以预测你的sql执行的效率，基本用法是：

explain sql语句\G

//根据返回的信息，我们可知,该sql语句是否使用索引，从多少记录中取出,可以看到排序的方式。

l        在什么列上添加索引比较合适

1） 在经常查询的列上加索引.

2） 列的数据，内容就只有少数几个值,不太适合加索引.

3） 内容频繁变化，不合适加索引

l        索引的种类

1） 主键索引 (把某列设为主键，则就是主键索引)

2） 唯一索引(unique) （即该列具有唯一性，同时又是索引）

3） index （普通索引）

4） 全文索引(FULLTEXT)

5） 复合索引(多列和在一起)

l        如何创建索引

如果创建unique / 普通/fulltext 索引

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

2） alter table 表名 add index 索引名 (列名...)

//如果要添加主键索引

alter table 表名 add primary key (列...)

删除索引

a) drop index 索引名 on 表名

b) alter table 表名 drop index index_name;

c) alter table 表名 drop primary key

显示索引

       showindex(es) from 表名

       showkeys from 表名

       desc表名

如何查询某表的索引

show indexes from 表名

l        使用索引的注意事项

查询要使用索引最重要的条件是查询条件中需要使用索引。

下列几种情况下有可能使用到索引：
1）对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
2）对于使用like的查询，查询如果是  ‘%bbb’ 不会使用到索引，‘bbb%’ 会使用到索引。

下列的表将不使用索引：
1 ）如果条件中有or，即使其中有条件带索引也不会使用。
2 ）对于多列索引，不是使用的第一部分，则不会使用索引。
3 ）like查询是以%开头
4 ）如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。
5 ）如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

l        如何检测你的索引是否有效

mysql>show status like ‘handler_read%’;

Handler_read_key 越大越少

Handler_read_rnd_next 越小越好

l        MyISAM 和 Innodb区别是什么

1） MyISAM 不支持外键, Innodb支持

2） MyISAM 不支持事务,不支持外键.

3） 对数据信息的存储处理方式不同.（如果存储引擎是MyISAM的，则创建一张表，对于三个文件..,如果是Innodb则只有一张文件 *.frm,数据存放到ibdata1）

对于 MyISAM 数据库，需要定时清理，命令：optimize table 表名

l        常见的sql优化手法

1）使用order by null  禁用排序

比如 select * from dept group by enameorder by null

2） 在精度要求高的应用中，建议使用定点数(decimal)来存储数值，以保证结果的准确性

l        表的水平划分

将一个表划分为多个表

l        垂直分割表

根据字段的大小分成多个表

l        读写分离（amoeba服务器，mysql集群）