北大青鸟设计培训:mysql数据库的优化方法? |
您所在的位置:网站首页 › sql优化知乎 › 北大青鸟设计培训:mysql数据库的优化方法? |
我们都知道,服务器数据库的开发一般都是通过java或者是PHP语言来编程实现的,而为了提高我们数据库的运行速度和效率,数据库优化也成为了我们每日的工作重点,今天,昌平IT培训http://www.kmbdqn.cn/就一起来了解一下mysql服务器数据库的优化方法。 为什么要了解索引真实案例案例一:大学有段时间学习爬虫,爬取了知乎300w用户答题数据,存储到mysql数据中。 那时不了解索引,一条简单的“根据用户名搜索全部回答的sql“需要执行半分钟左右,完全满足不了正常的使用。 案例二:近线上应用的数据库频频出现多条慢sql风险提示,而工作以来,对数据库优化方面所知甚少。 例如一个用户数据页面需要执行很多次数据库查询,性能很慢,通过增加超时时间勉强可以访问,但是性能上需要优化。 索引的优点合适的索引,可以大大减小mysql服务器扫描的数据量,避免内存排序和临时表,提高应用程序的查询性能。 索引的类型mysql数据中有多种索引类型,primarykey,unique,normal,但底层存储的数据结构都是BTREE有些存储引擎还提供hash索引,全文索引。 BTREE是常见的优化要面对的索引结构,都是基于BTREE的讨论。 B-TREE查询数据简单暴力的方式是遍历所有记录如果数据不重复,就可以通过组织成一颗排序二叉树,通过二分查找算法来查询,大大提高查询性能。 而BTREE是一种更强大的排序树,支持多个分支,高度更低,数据的插入、删除、更新更快。 现代数据库的索引文件和文件系统的文件块都被组织成BTREE。 btree的每个节点都包含有key,data和只想子节点指针。 btree有度的概念d=1。 假设btree的度为d,则每个内部节点可以有n=[d+1,2d+1)个key,n+1个子节点指针。 树的大高度为h=Logb[(N+1)/2]。 索引和文件系统中,B-TREE的节点常设计成接近一个内存页大小(也是磁盘扇区大小),且树的度非常大。 这样磁盘I/O的次数,就等于树的高度h。 假设b=100,一百万个节点的树,h将只有3层。 即,只有3次磁盘I/O就可以查找完毕,性能非常高。 索引查询建立索引后,合适的查询语句才能大发挥索引的优势。 另外,由于查询优化器可以解析客户端的sql语句,会调整sql的查询语句的条件顺序去匹配合适的索引。 用法一复制代码 代码如下: SELECT `keyword_rank`.* FROM `keyword_rank` WHERE (advertiserid='59') LIMIT 2 OFFSET 1
比如这个SQL ,limit后面跟的是2条数据,offset后面是从第1条开始读取。 用法二 复制代码 代码如下: SELECT `keyword_rank`.* FROM `keyword_rank` WHERE (advertiserid='59') LIMIT 2,1
而这个SQL,limit后面是从第2条开始读,读取1条信息。 这两个千万别搞混哦。 用法三 复制代码 代码如下: select * from tablename 条件语句 limit 100,-1
从第100条后开始-最后一条的记录 用法四 复制代码 代码如下: select * from tablename 条件语句 limit 15
相当于limit 0,15 .查询结果取前15条数据用法五 mysql低版本不支持limit offset limit offset 在mysql 4.0以上的版本中都可以正常运行,在旧版本的mysql 3.23中无效 limit m offset n 等价于 limit m,n limit 的优化 mysql的limit给分页带来了极大的方便,但数据量一大的时候,limit的性能就急剧下降 来源:一亩三分地博客 MYSQL的优化是非常重要的。其他最常用也最需要优化的就是limit。mysql的limit给分页带来了极大的方便,但数据量一大的时候,limit的性能就急剧下降。 同样是取10条数据 复制代码 代码如下: select * from yanxue8_visit limit 10000,10 select * from yanxue8_visit limit 0,10
就不是一个数量级别的。 网上也很多关于limit的五条优化准则,都是翻译自mysql手册,虽然正确但不实用。今天发现一篇文章写了些关于limit优化的,很不错。 文中不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据,明显要好于直接使用limit。这里我具体使用数据分两种情况进行测试。(测试环境win2033+p4双核 (3GHZ) +4G内存 mysql 5.0.19) 1、offset比较小的时候。 复制代码 代码如下: select * from yanxue8_visit limit 10,10
多次运行,时间保持在0.0004-0.0005之间 复制代码 代码如下: Select * From yanxue8_visit Where vid >=( Select vid From yanxue8_visit Order By vid limit 10,1 ) limit 10 多次运行,时间保持在0.0005-0.0006之间,主要是0.0006 结论:偏移offset较小的时候,直接使用limit较优。这个显然是子查询的原因。 2、offset大的时候。 复制代码 代码如下: select * from yanxue8_visit limit 10000,10
多次运行,时间保持在0.0187左右 复制代码 代码如下: Select * From yanxue8_visit Where vid >=( Select vid From yanxue8_visit Order By vid limit 10000,1 ) limit 10 多次运行,时间保持在0.0061左右,只有前者的1/3。可以预计offset越大,后者越优。 以后要注意改正自己的limit语句,优化一下mysql了 欢迎分享,转载请注明来源:内存溢出 原文地址:https://outofmemory.cn/zaji/7313266.html |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |