所在的位置: mysql >> mysql介绍 >> MySQL数据库性能优化

MySQL数据库性能优化

北京酒渣鼻专业医院 http://baidianfeng.39.net/a_xcyy/210624/9102614.html

1、表设计

1.1、数据库表最大程度遵守三范式

第一范式:数据库表中的字段都是单一属性的,不可再分;

第二范式:在第一范式基础上,除了主键以外的其它列都依赖于该主键;

第三范式:如果满足第二范式,并且除了主键以外的其它列都不传递依赖于主键列。

1.2、数据表和字段设计的原则

字段的命名要有意义;

字段选择的一般原则是保小不保大,能占用字节少的字段就不用大字段;

尽可能的使用varchar/nvarchar代替char/nchar

尽量不使用blob,还有将图片以二进制存到数据库

不用使用无法加索引的类型作为关键字段,比如text;

表中组合主键的字段个数越少越好,适当情况使用代理主键。

没有冗余的数据库未必是最好的数据库,适当的时候需降低范式标准;

1.3、根据应用场合选择表的存储引擎

数据表选择合适的引擎

MyISAM特点

数据存储方式简单,使用B+Tree进行索引使用三个文件定义一个表:.MYI、.MYD、.frm;

少碎片、支持大文件、能够进行索引压缩;

访问速度飞快,是所有MySQL文件引擎中速度最快的;

不支持一些数据库特性,比如事务、外键约束等;

表级锁,性能稍差,更适合读取多的操作(查询和更新操作并行时,查询操作需等待更新操作结束);

表数据容量有限,一般建议单表数据量介于50w–w;

增删查改以后要使用myisamchk检查优化表

InnoDB特点

使用TableSpace的方式来进行数据存储(ibdata1,ib_logfile0);

支持事务、外键约束等数据库特性;

行级锁,读写性能都非常优秀。(在默认“可重复读”事务隔离下,查询和更新操作并行时,查询操作不需等待);

能够承载大数据量的存储和访问;

拥有自己独立的缓冲池,能够缓存数据和索引;

在关闭自动提交的情况下,与MyISAM引擎速度差异不大

1.4、数据表的反范式化设计

降低范式标准至第二范式

ticketorderdetail中字段inputTime、inputBy等完全可以通过orderId外键关联ticketorder表来获取到相应信息。这么设计:存在一定的冗余,主要目的是部分场景时,可以提前筛选或直接取值,而不用join链接。

2、常用优化方法

1、查询语句中不要使用*

2、尽量减少子查询,使用关联查询(leftjoin,rightjoin,innerjoin)替代

3、减少使用IN或者NOTIN,使用exists,notexists或者关联查询语句替代

4、or的查询尽量用union或者unionall代替(在确认没有重复数据或者不用剔除重复数据时,unionall会更好)

5、表关联时,关联前尽量过滤数据量,可以减少关联后的集合量级,提高查询速度

6、空间换取时间。增加中间表进行优化(这个主要是在统计报表的场景,后台开定时任务将数据先统计好,尽量不要在查询的时候去统计)

7、那些可以过滤掉最大数量记录的条件必须写在WHERE子句的最前面,(oracle则是末尾)

8、大量数据的更新操作,要尽量使用批量处理,而不能foreach一条一条处理。

9、分页查询优化。不需要查询全部数据时,用分页查询返回。

10、分页查询优化的优化。在数据量非常大的情况下,分页查询时,limit的效率将会非常低。此时应该考虑优化查询条件。(在Mongodb特别明显)

11、结合执行计划explain。

3、索引的创建和使用

3.1、什么是索引?

3.2、创建索引的好处

帮助用户提高查询速度;

利用索引的唯一性来控制记录的唯一性;

可以加速表与表之间的连接;

降低查询中分组和排序的时间

3.3、创建索引的坏处

存储索引占用磁盘空间;

执行数据修改操作(INSERT、UPDATE、DELETE)产生索引维护

索引创建的总体原则

在了解表的具体应用场景基础上建立索引;

为所有主键和外键列建立索引;

对出现在WHERE子句、JOIN子句、ORDERBY或GROUPBY子句中的列考虑建立索引;

对需要确保唯一性的列考虑建立索引;

对于WHERE子句中用AND连接并频繁使用的列使用组合索引,最频繁的列放在最左边;

数据更新频繁的列不宜建立索引;

数据量较小的表也不宜建立索引

3.4、Explain中type类型

3.5.1、索引的正确使用(一)

去除查询条件左端的任何标量函数或计算

3.5.2、索引的正确使用(二)

确保宿主变量定义与列数据类型匹配

3.5.3、索引的正确使用(三)

查询条件中使用like时避免宿主变量以‘%’开头

3.5.4、索引的正确使用(四)

避免使用“or”,采用其它方式重写

3.5.5、索引的正确使用(五)

使用组合索引时,应注意“最左前缀”基本原则

最左前缀:就是最左优先,我们创建了lname、fname和age的多列索引,相当于创建了lname单列索引,(lname,fname)的组合索引以及(lname,fname,age)组合索引;

SELECT`uid`FROMpeopleWHERE`fname`=‘Zhiqun’AND`age`=26上述查询语句因违法“最左前缀”原则,系统通常会扫描整表以匹配数据!

4、EF框架查询优化

4.1、避免使用Select*

一些必填的字符串,应该在实体类上加上[Required]

4.2、表关联优化




转载请注明:http://www.aierlanlan.com/rzfs/1519.html

  • 上一篇文章:
  •   
  • 下一篇文章: 没有了