Sql优化

myslq优化应该主要从以下四个方面进行：

1.mysql表的设计

2.sql语句的优化

3.数据库参数的配置

4.适当的操作系统和硬件的配置

 

一数据表的设计要合理

1.我们的表设计要符合三范式模式，有时我们需要适当的逆范式

（三范式：第一，对原子性的要求，也就是要起不可分解（关系型方式就自动符合第一范式，比如学生表：字段有ID,NAME,EMALI,ADDRESS）。）

（第二范式（只有满足第一范式的基础上，才能考虑第二范式）：对记录的唯一约束性。只要表的记录满足唯一性就满足第二范式。通常的手法是建立一个主键。）

（第三范式：对字段冗余性的约束，即任何字段不可以由其他字段派生出来，要求字段没有冗余。）（EX.第一个学生表中出现了班级，二第二个表中也出现了班级就出现了冗余，可以通过外键来派生解决冗余）。

但也是也可以不满足三范式，而用适当的逆范式来满足要求，所有规范是死的，技术是活的。

二sql语句的优化

1.通过show status命令了解各种sql的执行频率

2.定位执行效率比较低的SQL语句（重点是select语句，其中selcet语句是最重点的，因为一般查询操作时应用到最多的）；

3.通过explain分析低效率的SQL语句执行情况

4.确定问题并采取相应的优惠措施

sql语句分为两类：

1.ddl语句（数据定义语句）（create alter drop）

2.dml语句（数据库操作语句）（insert delete update)

4.select

4dtl语句（数据事务语言）（commit  rollback  savepoint)

5dcl语句（数据控制语句）（grant revoke[权限控制指令]

 

show status like 'Com%'可以查询所有以Com打头的指令 也就是select语句

show status like 'Connections'查询一共有多少次连接；

show status like 'uptime‘可以查看服务器工作的时间（是以秒来为单位的）；

show status like 'Slow_queries' 慢查询的次数（默认是10秒钟 ）；也就是查询sql语句的相应时间在10s钟以上的次数。一般情况下在三秒钟以内没有响应的话 体验式非常差的！做个是mysql优化的重点。

show  varibles like 'long_query_time'

set long_query_time=2 就可以改变慢查询的时间为2；

××××××××××××××××××××××××××××××××××××××××××××

重点：在一个数据存储量非常大的数据库中，（而且有很多的查询语句）怎么去找到一个引起慢查询的语句，然后进行修改了，就要启用mysql的本事自带的功能了，mysql本事支持把慢查询语句记录到日志中，然后供程序员分析，但是注意默认情况下，不启用的。

步骤：

（在mysql较低的版本里面可以通过查看My.ini文件的database root查看data存放在哪个目录下面，高版本的一般就存放在data目录下面）

1.要这样启动我们的mssql。

进入到mysql的安装目录，bin目录下mysqld文件可以启用

可以通过cmd方式启用数据库 cd:C:\Program Files (x86)\phpStudy\MySQL\

然后xx>bin\mysqld.ex. -slow query-log 

然后我们就可以看到慢查询的日志记录；

/××××××××××××××××××××解决select语句慢的方法××××××××××××××××××××××/

说起索引是提高数据库性能最好的办法，索引是物美价廉的办法。不用加内存，不用改程序，不用调用sql。（但是一般情况下增加索引后×MYI文件会变的很大，就说牺牲空间来增效率）。但是索引变快的同时会给，删除，添加，更新变慢。索引要酌情处理！

索引的代价：

1.占用磁盘空间；

2.对DML语句的影响；

比如建立主键索引：alert table emp add primary key(empno )   //就是给emp表的empno字段增加了索引。

我们可以用一个指令explain分析工具，可以在我们不执行前对一个sql语句进行分析（一个非常重要的指令），可以预测你的sql语句的执行效率，和是否要进行索引！

explain会返回的语句：id

select_type:SIMPLE

table:emp;

type:const

;;;

rows

extra;

其中主要注意到:

select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型

rows：用来多少条记录查询到我们需要的数据。

selct_type:查询的类型，如果是索引的话就是简单查询SIMPLE表示只查询一列，要是显示ALL就是全部查询，效果会非常的差！

？？？？？？/但是并不是在某个字段上面增加索引，在其他字段上面的查询都会变快，也就说只要在你添加索引的列上面查询才能变快！

索引添加到合适列上面：

1：笔记频繁作为查询字段的；

2：唯一性太差的字段不适合单独创建索引，即便是频繁作为查询条件（比如性别：只有男：女）；

3：更新频繁的字段不适合创建索引；（比如QQ的隐身，在线）

4：不会出现在WHERE子句中字段不该创建索引

/××××××××××××××××××××××××××××××××××××××××××××/

索引的分类：

（如果查询某表的索引：show index from table）

主索引：主键自动加的索引（Primary）

唯一索引：(unique)  (也就该列注意唯一性同时又有索引)

普通索引：（index）

全文索引：（FULLTEXT）：（比如我在一边文章里面查询出现某个关键字的：select  *from atticle where content like '%李连杰%'！）【但是注意在中文，全文索引比较少！可以利用sphinx+中文分词 来实现中文索引；而且只有MYISAM支持】

复合索引：（多列和在一起）；

 

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

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

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

//如果要添加主键索引

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

 

 

删除索引

1.      drop index 索引名 on 表名   普通索引和惟一索引都是这样的 （drop index unique test;是错误的）

2.      alter table 表名 drop index index_name;

3.      alter table 表名 drop primary key

 

显示索引

       showindex(es) from 表名

       showkeys from 表名

       desc表名

索引的使用：

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

1：对于创建的多列索引，只要查询条件使用了最左边的列，索引一般会被使用；

比如表emp 中字段empnane和log字段为index索引，索引名字都为myindex；字段empnane在logc的左边，这个时候如果selec * from empnane where log="XXX"是不会用到索引的，等于白写了！；

2：对于使用like 的查询，如果是“%aaaa”的话，一般索引是用不到的；

下来的情况不会使用索引：

1：如果条件中有or 即使其中的条件带索引也不好使用；select * from dept where dname='AA' or  LOC='XXX';

即使这个语句中的两个字段 也不会使用索引 ，所以or要尽量少使用！

2：如果列类型是字符串，那么需要带上单引号不会使用索引！ 比如select * from temp where name=aaa是不行的；

3:如果你的数据库表使用全部查询，比使用索引还有快的话一般不会使用索引；

那么我们如何查看索引的使用情况呢：

show status like 'Handler_read%'

要是handler_read_key很高 则我们的索引非常高；

而handle_read_rnd_next:非常高的话，则我们的索引效率非常的低；

/××××××××××××××对于MYISAM××××××××××××/

要大批量的导入数据时候；我们一般先禁用索引；

alter table table_name disable keys;

loading data;

alter table table_name enable keys;

对于Innodb

1;将要导入的数据按照主键排序

2：set unique_checks=0;关闭唯一性检验；

3：set autocommit=0;关闭自动提交；

、、、、、、、、、、、、、、、、、、

常见的数据库引擎有MYISAM和Indb

MYISAM则当创建一个数据表会产生三个文件：emp.frm(*frm这个是表结构);emp.MYD（*MYD数据信息）；emp.MYI(*MYI表索引)。

Innodb在data中只会存储×frm也就是表结构，但是它的数据信息存储在ibdata中

1.MYISAM不支持外键，不支持事物语言

2.MYISAM比Innodb要处理速度快；
3.因为MYISAM在删除的时候空间上不会释放的，所以使用MYISAM的空间会越来越大（所以我们一般要执行指令：

optimize table table_name;

可以优化我们的MYISAM数据库；来清除空间）

自己复制自己的语句：insert into tt select * from tt;

---------常见的sql优化手法

1.使用order by nul  比如我要进行分组，而分组的时候一般会进行自动的排序；所以为了检索效率 我们可以禁用排序：selce * from temp group by columname oder by null;

(group by默认会进行排序)

2尽量多的使用join 来代替子查询；join不会穿件临时表

3.如果想要在or查询语句中使用索引的话，则应该考虑每一个条件都使用索引；

4.在精度要求高的应用中，建议使用定点数来（decimal而不采用float因为float会消失小数点后一个数据）存储数据；以保证结果的正确性；

5.日期类型要根据实际情况选择能够满足应用的最小存储的早期类型；timestamp容易推算出以前和以后的时间；

比如要推算几天内发的帖子；

1.对表的水平划分：（有几千万的数据放在一个表中就会效率很慢，那么就要进行分表）；

比如有一千万的数据要从中取出来，我可以分成100个数据表，并按编号命名；要进行查询的时候，我们可以先按规则判读出数据存在于哪张表，然后进行查询对应的表；

（例如添加的时候我们按id取模插入对应的表；我们查询的时候也可先取模）。

2.对表的垂直划分：虽然表的数据不是很多，但是字段非常长，比如小说的内容；占取很大的内容字段长度；

3.数据库表的字段存储原则上存大不存小

对于数据库参数的设置：

最重要的数据库参数就是内存，我们主要用的是innodb引擎

innodb_additional_men_pool_size=64M;

innodeb_buffer_pool_siez=1G

对于myisam数据可，可以调整key_buffer_size;        

还可以实现读写分离；将数据库的压力分散；