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. bin\mysqld.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 读写分离
