MySql的优化

MySql的优化

 

u 考虑的方面

1. 数据库(表)的设计->3NF (三范式)

2. sql语句优化 (select)

3. 数据库配置 Mysql -> my.ini (缓存、最大连接数，字符集，默认数据库存储引擎)

4. 服务器配置

 

关系型数据库(目前主流)：Mysql、Oracle、sql server db2, informix  

非关系型数据库: 面向集合，面向对象

nosql数据库: mogodb 

 

u 数据库(表)的设计->3NF (三范式)

1. 我们数据表的标准是以满足 几范式来衡量, 我们 PHP网站表，要求满足3NF

2. 1NF 指的是一条记录要满足原子性，不可以分割(只有你使用数据库是关系型数据，则自动满足)

 

3. 2NF 就是记录的唯一性(我们是通过主键来控制  primary key  unique)

 

主键不能重复，也不能为空

 

unique 的特点是， 可以为空，但是不能重复(如果有具体值，不能重复，如果是NULL，可以重复) 

 

4. 3NF 就是数据字段不能冗余

示意图:

 

u 反3NF , 有某些情况下，我们的数据表设计要适当的反三范式

 

 

小 标准: 如果我们增加了一个字段，但是效率提高了，则可以增加这个看似冗余的字段.

 

u sql语句的优化

1. 怎样写sql(select ) 语句更好，更快. 

 

常见 sql 语句有几种 ( dml 语句 数据操作语句 (insert ,update ,delete) , ddl 语句( 数据定义语句 data definition language [create drop alter ]   ), dcl (数据库控制语句 grand revoke ), dtl( 数据事务语句 commit rollback savepoint  ) / select )

 

 

2. 怎么定位慢查询

 

 

u show status 指令

 

查看当前有多少个连接 

      show status like  ‘connections ‘;

  show status like ‘uptime’;

    show status like ‘slow_queries’;

查看执行了多少次 insert select update delete

  show status like ‘com_select’;  //其它依次类推

☞ 如果查询当前对话,  show session status ….

   如果要查询从数据库启动到现在  show global status …

 

现在我们一起来创建一张海量表， 这里我们讨论如何优化大表

这几张表是表示 部门信息

 

我们创建了函数和过程，来生成我们的的海量表:这里请参考 sql.txt脚本.

 

这里可能不同学员有各种情况

1. 一种过程或是函数创建不了.

2. 函数和过程都ok ,但是call错误(x)

 

安装一个5.5.15 ,前，把原来的 data目录下的文件拷贝一份.

<?php

 

$mysql_connect(“localhost”,”root”,”root”);

 

因为我们有了大表，所以我们可以来测试如何定位慢查询.

这里清楚

1. mysql 数据库默认慢查询是 10s -> 一会我把它修改成1秒

2. mysql数据库默认不记录慢查询，因此需要我们启动时，就设置.

3. 如果有慢查询出现，默认放在C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/

 

 

a. 关闭mysql

b. binmysqld.exe –safe-mode –slow-query-log

 

使用 select * from emp e,dept d where e.empno=123451  and e.deptno=d.deptno order by e.empno ； 很慢， 超过10s 就会把这个 sql记录到 日志文件.

 

注意：在实际开发中，我们建议大家把默认的慢查询的时间 10s 改成 2秒.

 

查询慢查询时间

show variables like ‘long_query_time’;

修改慢查询时间

set long_query_time=2

 

u sql语句的优化-使用explain 工具

该工具可以在不真正执行 sql 语句，就可以告诉程序员，效率如何?

 

创建索引

快速入门

create index 索引名 on 表名 (字段名)

create index inx_empno on emp (empno);

 

提示： 创建索引后，该表对应的 emp.myi 这个文件会变得大. 

 

当我们创建索引后，select 快了，但是 dml 语句变慢

 

 

用户登录时的代码要小心

select * from users where name=“” and password=“”;

//严谨写法

   select password from users where name=“hsp”;

   //取出数据库对应密码与用户输入的密码比对，如果相同说明ok

   if($pasd==$userpassw){

  //ok

}else{退出;}

 

u 哪些字段不宜加索引

1. 在经常查询的字段上加索引, 不经常查询的就不加索引

2. 唯一性差的字段不要加索引.

3. 频繁变化的字段不要索引.

 

u 索引的分类:

① 主键索引

当一个字段设为主键后，就自动是主键索引,主键索引只能有一个.

alter table 表名 addd priamry key (字段名)

 

删除主键索引

alter table 表名  drop primary key;

 

② 普通索引

create index 索引名 on 表名( 字段名,..)

 

alter table 表名 drop index 索引名

 

③ 唯一索引

当一个字段设为unique后，就是唯一索引.

创建

create table aaa ( id int priamry key, name varchar(20) unique);

create unique index 索引名 on 表名 （字段）

 

删除

alter table 表名 drop index 索引名

 

 

④ 全文索引

全文索引，是在MyISAM表生效. 他主要是针对内容检索.

create fulltext index 索引名 on 表名 (字段名) 

如果我们是中文文章，fulltext 作用不大= >sphinx 来解决 coreseek

 

删除索引

alter table 表名 drop index 索引名

 

 

如何查询表的索引情况

 

show index from 表名;

show indexes form 表名

show keys from 表名

 

u 修改索引

删除，在添加.

 

u 使用索引讨论

1. 如果是多列索引，只有使用到左边的字段，才会使用索引

2. like 语句 ‘%aa’ 是不会用索引

3. or 要小心使用.

4. 如果你在字符串字段添加索引，则需要在查询时，要引号.

 

 

u 优化小技巧

1. group by 的优化：

如果只需要分组，但是不希望排序，则是可以使用 order by null

explaine select * from dept group by dname; =>这时显示 extra: using filesort 说明会进行排序

explaine select * from dept group by dname order by null =>这时不含有显示 extra: using filesort 说明不会进行排序

 

2. 同学们考虑使用 left join 来替代普通多表联合查询

 

u MyISAM 和  InnoDB的选择

1. 如果你的网站以 读和写为主，同时对事务性要求不高，建议使用MyISAM

2. 如果你的网站对事务性高，比如(转账,付款…)这个表建议使用InnoDB

 

u 选择适当的数据类型

 

账号余额  float  / decimal 我吗要使用精度高的  decimal 

 

u 如果你的项目中使用的MyISAM 存储引擎，则需要你定义优化该表

 

optimize table 表名

 

 

水平分割:

 

 

垂直分割:

 

mysql数据库的配置:

端口: 3306  可以修改

存储引擎: InnoDB->MyISAM

max_connection 100可以调成 1000

 

u 读写分离