mysql优化

Mysql数据库的优化技术


对mysql优化时一个综合性的技术，主要包括 


a: 表的设计合理化(符合3NF)


b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]


c: 分表技术(水平分割、垂直分割)


d: 读写[写: update/delete/add]分离


e: 存储过程 [模块化编程，可以提高速度]


f: 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ] Linux /etc/my.cnf 


g: mysql服务器硬件升级


h: 定时的去清除不需要的数据,定时进行碎片整理(MyISAM) 


重点：定位慢查询，mysql索引优化，mysql备份


3NF 
1NF 关系型数据库自动满足
2NF 表中记录唯一（通常设置一个主键实现）
3NF 没有数据冗余，若表中字段有一对多的关系，考虑分表




反3NF : 但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。 


//常用命令
show status


常用的:
show status like ‘uptime’ ;  //mysql服务工作时间
show stauts like ‘com_select’ ；//select 查询次数
 
show [session|global] status like  （session表示只针对当前窗口，global从mysql 服务启动到现在）


show status like ‘connections’; // 试图连接MySQL服务器的次数


//定位慢查询


set global log_slow_queries=on;//mysql中启用慢查询日志


show variables like "%slow%";//查看是否开启，文件日志保存路径 


show variables like ‘long_query_time’; //查看慢查询时间设置值
set long_query_time=0.1; //重新设置为0.1s


show status like ‘slow_queries’; //显示慢查询次数


测试时,可以看到在日志中查看mysql慢sql语句.




优化问题. 


explain 查看mysql指令实际的执行情况
 




添加索引 


四种索引(主键索引/唯一索引/全文索引/普通索引)


数据库表3个文件，（数据表结构，数据，索引）
1. 添加


1.1主键索引添加


当创建表，将某个字段设为主键的时候，该字段，就是主键索引。


alter table 表名 add primary key (列名);//可后添加






1.2普通索引
一般表创建后，再创建
create index  xx  on表 (列1,列名2);
 
1.3 全文索引 


主要是针对对varchar,text的检索,全文索引针对MyISAM有用.(中文使用sphinx)


停止词如一些常用词和字符，不会创建
CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;


ALTER TABLE articles  ADD FULLTEXT INDEX fulltext_article(title,body);//可以后创建


select * from articles where match(title,body) against(‘关键字’); //用法


1.4唯一索引
①当表的某列被指定为unique约束时，这列就是一个唯一索引
create table ddd(id int primary key auto_increment , name varchar(32) unique);




unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复.


主键字段，不能为NULL,也不能重复.


②在创建表后，再去创建唯一索引


create unique index 索引名  on 表名 (列表..);




2. 查询索引 


show index(es) from 表名\G


show keys from 表名\G 


3. 删除


alter table 表名 drop index 索引名; 




4. 修改   //先删除，再重新创建.




索引创建注意


满足以下条件的字段，才应该创建索引.


Where条件下经常使用，该字段内容多样，不频繁变化 


索引使用注意
//使用索引


alter table dept add index my_ind (dname,loc); //  dname 左边的列,loc就是右边的列


explain select * from dept where loc='aaa'\G 


//复合索引，查询条件时，使用最左边的列。


//like查询，请使用 ‘aaa%’,不要使用‘%aaa’或 ‘_aaa’(考虑sphinx)


//or 查询，所有条件的字段带索引，才使用索引，（避免使用or）


select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45


//字段类型为字符串，查询时一定要用引号引起来，(添加时,字符串必须’’)


//不经常出现，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。








//如何查看索引使用的情况:


show status like ‘Handler_read%’;


handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。


//sql语句的小技巧


Group by分组查询后，还会进行内部排序，order by null可以防止排序.
 


//可使用左外连接代替子查询，因为使用join，MySQL不需要在内存中创建临时表。


select * from dept, emp where dept.deptno=emp.deptno; [简单处理方式]


select * from dept left join emp on dept.deptno=emp.deptno;  [左外连接，更ok!]




//选择mysql的存储引擎


在开发中，我们经常使用的存储引擎 myisam / innodb/ memory


myisam 存储: 如果表对事务要求不高，同时是以查询和添加为主的，考虑使用,比如 bbs 中的 发帖表，回复表.


INNODB 存储: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.


//如果数据库的存储引擎是myisam, 删除数据后，数据文件大小没有变化，记住要定时进行碎片整理


// mysql命令行，对myisam进行整理，linux下 /etc/my.cnf 找到datadir路径查看清理效果


handler_read_rnd_next:这个值越高，说明查询低效。