`
lvwenwen
  • 浏览: 926343 次
  • 性别: Icon_minigender_1
  • 来自: 魔都
社区版块
存档分类
最新评论

mysql优化

阅读更多
数据库优化工作

8.7
 mysql的优化
1. 数据库(表)设计合理
我们的表设计要符合3NF   3范式(规范的模式) , 有时我们需要适当的逆范式
2. sql语句的优化(索引,常用小技巧.)
3. 数据的配置(缓存设大)
4. 适当硬件配置和操作系统 (读写分离.)

 数据的3NF
1NF :就是具有原子性,不可分割.(只要使用的是关系性数据库,就自动符合)
2NF: 在满足1NF 的基础上,我们考虑是否满足2NF: 只要表的记录满足唯一性,也是说,你的同一张表,不可能出现完全相同的记录, 一般说我们在 表中设计一个主键即可.
3NF: 在满足2NF 的基础上,我们考虑是否满足3NF:即我们的字段信息可以通过关联的关系,派生即可.(通常我们通过外键来处理)
逆范式: 为什么需呀逆范式:
(相册的功能对应数据库的设计)

第一范式:确保每列的原子性.
    如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式.
    例如:顾客表(姓名、编号、地址、……)其中"地址"列还可以细分为国家、省、市、区等。


第二范式:在第一范式的基础上更进一层,目标是确保表中的每列都和主键相关.
    如果一个关系满足第一范式,并且除了主键以外的其它列,都依赖于该主键,则满足第二范式.
    例如:订单表(订单编号、产品编号、定购日期、价格、……),"订单编号"为主键,"产品编号"和主键列没有直接的关系,即"产品编号"列不依赖于主键列,应删除该列。

第三范式:在第二范式的基础上更进一层,目标是确保每列都和主键列直接相关,而不是间接相关.
    如果一个关系满足第二范式,并且除了主键以外的其它列都不依赖于主键列,则满足第三范式.
    为了理解第三范式,需要根据Armstrong公里之一定义传递依赖。假设A、B和C是关系R的三个属性,如果A-〉B且B-〉C,则从这些函数依赖中,可以得出A-〉C,如上所述,依赖A-〉C是传递依赖。
    例如:订单表(订单编号,定购日期,顾客编号,顾客姓名,……),初看该表没有问题,满足第二范式,每列都和主键列"订单编号"相关,再细看你会发现"顾客姓名"和"顾客编号"相关,"顾客编号"和"订单编号"又相关,最后经过传递依赖,"顾客姓名"也和"订单编号"相关。为了满足第三范式,应去掉"顾客姓名"列,放入客户表中。
适当的逆范式.
sql语句的优化
面试题:sql语句有几类
ddl(数据定义语言)[create alter drop]
dml(数据操作语言)[insert,delete update]
select
dtl(数据事物语句) [commit rollback savepoint]
dcl(数据控制语句) [gant revoke]

mysql可以自定义函数(max)

四种索引(性价比高,不需要加内存,不用改程序)
1.主键索引,唯一索引,普通索引,全文索引,符合索引(多列上的索引)

加索引(alter table emp add primary key (empno))
分割水平:qq1,qq2,qq3,insert into qq1 values(); (qq id%100);
垂直分割:id,fenshu,timu(大---->变成一张表),miaosu(大--->变成一张表)
读写分离原理:读写分离目的是:给大型网站缓解查询压力,在服务器端跑了
(amoeba服务器,他可以判断语句是dml(写)还是读的操作),如果是写的数据就会找master mysql,
把这个数据更新到主数据库中去,主数据会把它的数据同步到他的slave数据库下面(由mysql proxy完成);
如果是查询(select)语句他,amoeba会根据当前的负载情况,挑选出其中 一个server把数据返回
对于一个以数据为中心的应用,数据库的好坏直接影响到程序的性能,因此数据库性能至关重要。一般来说,要保证数据库的效率,要做好以下四个方面的工作:
① 数据库设计
② sql语句优化
③ 数据库参数配置
④ 恰当的硬件资源和操作系统
这个顺序也表现了这四个工作对性能影响的大小

 mysql的优化
1. 数据库(表)设计合理
我们的表设计要符合3NF   3范式(规范的模式) , 有时我们需要适当的逆范式
2. sql语句的优化(索引,常用小技巧.)
3. 数据的配置(缓存设大)
4. 适当硬件配置和操作系统 (读写分离.)




 数据的3NF

1NF :就是具有原子性,不可分割.(只要使用的是关系性数据库,就自动符合)
2NF: 在满足1NF 的基础上,我们考虑是否满足2NF: 只要表的记录满足唯一性,也是说,你的同一张表,不可能出现完全相同的记录, 一般说我们在 表中设计一个主键即可.
3NF: 在满足2NF 的基础上,我们考虑是否满足3NF:即我们的字段信息可以通过关联的关系,派生即可.(通常我们通过外键来处理)


逆范式: 为什么需呀逆范式:
(相册的功能对应数据库的设计)
适当的逆范式.


不适当的逆范式


 sql语句的优化
面试题 :sql语句有几类
ddl (数据定义语言) [create alter drop]
dml(数据操作语言)[insert delete upate ]
select
dtl(数据事务语句) [commit rollback savepoint]
dcl(数据控制语句) [grant  revoke]

 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’

为了教学,我们搞一个海量表(mysql存储过程)


目的,就是看看怎样处理,在海量表中,查询的速度很快!


select * from emp where empno=123456;


需求:如何在一个项目中,找到慢查询的select , mysql数据库支持把慢查询语句,记录到日志中,程序员分析. (但是注意,默认情况下不启动.)
步骤:
1. 要这样启动mysql

进入到 mysql安装目录


2.  启动 xx>bin\mysqld.exe –slow-query-log   这点注意

测试 ,比如我们把
select * from emp where empno=34678 ;
用了1.5秒,我现在优化.

快速体验: 在emp表的 empno建立索引.
alter table emp add primary key(empno);
//删除主键索引
alter table emp drop primary key

然后,再查速度变快.


 索引的原理



介绍一款非常重要工具 explain, 这个分析工具可以对 sql语句进行分析,可以预测你的sql执行的效率.
他的基本用法是:
explain sql语句\G
//根据返回的信息,我们可知,该sql语句是否使用索引,从多少记录中取出,可以看到排序的方式.




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

① 在经常查询的列上加索引.
② 列的数据,内容就只有少数几个值,不太适合加索引.
③ 内容频繁变化,不合适加索引


 索引的种类
① 主键索引 (把某列设为主键,则就是主键索引)
② 唯一索引(unique) (即该列具有唯一性,同时又是索引)
③ index (普通索引)
④ 全文索引(FULLTEXT)
select * from article where content like ‘%李连杰%’;
hello, i am a boy
 你好,我是一个男孩  =>中文 sphinx

⑤ 复合索引(多列和在一起)
create index myind on 表名 (列1,列2);

 如何创建索引

如果创建unique / 普通/fulltext 索引
1. create [unique|FULLTEXT] index 索引名 on 表名 (列名...)
2. alter table 表名 add index 索引名 (列名...)
//如果要添加主键索引
alter table 表名 add primary key (列...)


删除索引
1. drop index 索引名 on 表名
2. alter table 表名 drop index index_name;
3. alter table 表名 drop primary key

显示索引
show index(es) from 表名
show keys from 表名
desc 表名




如何查询某表的索引
show indexes from 表名

 使用索引的注意事项
查询要使用索引最重要的条件是查询条件中需要使用索引。
下列几种情况下有可能使用到索引:
1,对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用。
2,对于使用like的查询,查询如果是  ‘%aaa’ 不会使用到索引
‘aaa%’ 会使用到索引。
下列的表将不使用索引:
1,如果条件中有or,即使其中有条件带索引也不会使用。
2,对于多列索引,不是使用的第一部分,则不会使用索引。
3,like查询是以%开头
4,如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引。
5,如果mysql估计使用全表扫描要比使用索引快,则不使用索引。

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



结论: Handler_read_key 越大越少
Handler_read_rnd_next 越小越好

fdisk
find

 MyISAM 和 Innodb区别是什么
1. MyISAM 不支持外键, Innodb支持
2. MyISAM 不支持事务,不支持外键.
3. 对数据信息的存储处理方式不同.(如果存储引擎是MyISAM的,则创建一张表,对于三个文件..,如果是Innodb则只有一张文件 *.frm,数据存放到ibdata1)

对于 MyISAM 数据库,需要定时清理
optimize table 表名



 常见的sql优化手法
1. 使用order by null  禁用排序
比如 select * from dept group by ename order by null

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

1000000.32 万
create table sal(t1 float(10,2));
create table sal2(t1 decimal(10,2));

问?在php中 ,int 如果是一个有符号数,最大值. int- 4*8=32   2 31 -1



 表的水平划分


 垂直分割表


如果你的数据库的存储引擎是MyISAM的,则当创建一个表,后三个文件. *.frm 记录表结构. *.myd 数据  *.myi 这个是索引.

mysql5.5.19的版本,他的数据库文件,默认放在 (看 my.ini文件中的配置.)




 读写分离
 

uml 课程.(uml 架构.)=>效果,做一个项目后再说.


分享到:
评论

相关推荐

    mysql优化配置大全

    mysql慢可能是配置不对,阅读一下这个可能对你有帮助 ...对于Discuz!... 下面我们了解一下MySQL优化的一些基础,MySQL的优化我分为两个部分,一是服务器物理硬件的优化,二是MySQL自身(my.cnf)的优化。

    php之mysql优化

    mysql优化

    mysql优化及基础面试题

    mysql优化及基础面试题。 什么是慢查询 慢查询日志,顾名思义,就是查询慢的日志,是指 mysql 记录所有执行超过 long_query_time 参数设定的时间阈值的 SQL 语句的日志。该日志能为 SQL 语句的优化带来很好的帮助...

    mysql 优化工具.rar

    为什么要开发这个MySQL 优化工具(Why)   “一键优化”功能,可以优化本地/远程需要优化的机器,将繁琐的优化工作“傻瓜”式操作 根据您的业务需求Step By Step优化的MySQL服务器参数,起到指引的作用,简化用户...

    MySql优化.rar

    MySql优化

    MySQL优化大揭秘.rar

    MySQL优化

    MYSQL优化-一篇很好的优化文章

    21. MYSQL扩展/优化-提供更快的速度 22. MYSQL何时使用索引 23. MYSQL何时不使用索引 24. 学会使用EXPLAIN 25. 学会使用SHOW PROCESSLIST 26. 如何知晓MYSQL解决一条查询 27. MYSQL非常不错 28. MYSQL应避免...

    我的mysql 优化日记

    我的mysql优化日记 我的mysql优化日记 我的mysql优化日记 我的mysql优化日记

    mysql优化笔记+资料

    详细介绍了mysql的优化方法,资料包里包含word文档,ppt和图片

    mysql优化十大技巧

    mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql...

    mysql优化-sql高级

    mysql优化从以下几个方面介绍 mysql的架构 索引优化分析 查询截取分析 mysql锁机制 主从复制

    大型门户网站核心技术-Mysql优化

    教程名称:大型门户网站核心技术-Mysql优化 课程目录:【】Mysql优化 资料【】Mysql优化01关键技术【】Mysql优化02表的设计【】Mysql优化03慢查询(一)【】Mysql优化04慢查询(二)【】Mysql优化05慢查询(三)【】Mysql...

    Mysql优化方法介绍.ppt

    Mysql优化方法介绍.ppt

    MySql 优化.doc

    MySql优化——MySql 优化.doc

    windows平台mysql优化配置

    windows平台mysql优化配置

    MySql优化.pdf

    MySql优化.pdf

    深入浅出的mysql优化大全

    深入浅出的mysql优化大全!网易数据库专家写的

    2h拿下MySQL优化.pdf

    经典案例实例分析,通过演示教你掌握基本的mysql优化

    非常实用的mysql优化规则

    非常实用的mysql优化规则,问题:,若是能够恰当的使用sql规则,便能极大提高系统的效率。那么如何实现sql语句和表的优化呢?

Global site tag (gtag.js) - Google Analytics