文档章节

mysql查询优化

七爹
 七爹
发布于 2015/05/30 18:28
字数 2390
阅读 25
收藏 0
点赞 0
评论 0

通过以下几方面来优化

一、通过查询缓存提高查询速度
二、索引优化
三、使用临时表
四、优化数据类型
五、sql语句优化

一、通过查询缓存提高查询速度

查询缓存可以看做是SQL文本和查询结果的映射。如果第二次查询的SQL和第一次查询的SQL完全相同,第二次查询就直接从查询缓存中取结果。

数据库查询缓存类型设置:query_cache_type,有0、1、2三个取值。0则不使用查询缓存。1表示始终使用查询缓存。2表示按需使用查询缓存。临时关闭、开启缓存关键字添加进入sql,SQL_NO_CACHE、SQL_CALHE 。

缓存命中的规则:1.注意两个sql必须是完全相同,即使多一个空格或者大小写不同都认为不同,且开启了查询缓存。
2.查询缓存不会存储有不确定结果的查询。因此,任何一个包含不确定函数(比如NOW()或CURRENT_DATE())的查询不会被缓存。同样地,CURRENT_USER()或CONNECTION_ID()这些由不同用户执行,将会产生不同的结果的查询也不会被缓存。事实上,查询缓存不会缓存引用了用户自定义函数、存储函数、用户自定义变量、临时表、mysql 数据库中的表或者任何一个有列级权限的表的查询。

二、索引优化

  1. 索引是做什么的?

索引用于快速找出在某个列中有一特定值的行。不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。 表越大,花费的时间越多。如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据。

    2.索引好复杂,我该怎么理解索引,有没一个更形象点的例子?

有,想象一下,你面前有本词典,数据就是书的正文内容,你就是那个cpu,而索引,则是书的目录。

    3.索引越多越好?

大多数情况下索引能大幅度提高查询效率,但:数据的变更(增删改)都需要维护索引,因此更多的索引意味着更多的维护成本更多的索引意味着也需要更多的空间 (一本100页的书,却有50页目录?),过小的表,建索引可能会更慢  (读个2页的宣传手册,你还先去找目录?)

    4.索引的字段类型问题

text类型,也可建索引(需指定长度);
myisam存储引擎索引键长度综合不能超过1000字节;
用来筛选的值尽量保持和索引列同样的数据类型。

    5.like 不能用索引?

尽量减少like,但不是绝对不可用,”xxxx%” 是可以用到索引的,
想象一下,你在看一本成语词典,目录是按成语拼音顺序建立,查询需求是,你想找以 “一”字开头的成语(”一%“),和你想找包含一字的成语(“%一%”)
除了like,以下操作符也可用到索引:
 <,<=,=,>,>=,BETWEEN,IN
    <>,not in ,!=则不行

    6.什么样的字段不适合建索引?

一般来说,列的值唯一性太小(如性别,类型什么的),不适合建索引(怎样叫太小?一半说来,同值的数据超过表的百分之15,那就没必要建索引了)
太长的列,可以选择只建立部分索引,(如:只取前十位做索引)
更新非常频繁的数据不适宜建索引(怎样叫非常?意会)
    7.一次查询能用多个索引吗?

不能
    8.多列查询该如何建索引?

一次查询只能用到一个索引,所以 首先枪毙 a,b各建索引方案
a还是b? 谁的区分度更高(同值的最少),建谁!
当然,联合索引也是个不错的方案,ab,还是ba,则同上,区分度高者,在前
    9.联合索引的问题?

where a = “xxx” 可以使用 AB 联合索引 where b = “xxx” 则不可 (再想象一下,这是书的目录?)
所以,大多数情况下,有AB索引了,就可以不用在去建一个A索引了
    10.哪些常见情况不能用索引?

like “%xxx”
not in , !=
对列进行函数运算的情况(如 where md5(password) = “xxxx”)
WHERE index=1 OR A=10
存了数值的字符串类型字段(如手机号),查询时记得不要丢掉值的引号,否则无法用到该字段相关索引,反之则没关系也即
 select * from test where mobile = 13711112222;
可是无法用到mobile字段的索引的(如果mobile是char 或 varchar类型的话)

    11.NULL 的问题

NULL会导致索引形同虚设,所以在设计表结构时应避免NULL 的存在(用其他方式表达你想表达的NULL,比如 -1?除非代码中需要使用NULL)
    12.如何查看索引信息,如何分析是否正确用到索引?

show index from tablename; explain select ……;

关于explain关键字,有兴趣的话可以自己去研究

    三、使用临时表

当我们查询的结果集中的数据比较多时,可以通过SQL_BUFFER_RESULT选项强制将结果集放到临时表中,这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了),并且可以长时间地为客户端提供大记录集。

  1. 使用临时表注意点

    1.在同一个query语句中,你只能查找一次临时表。例如:下面的就不可用
     mysql> SELECT * FROM temp_table, temp_table AS t2;
      ERROR 1137: Can't reopen table: 'temp_table'
    2.临时表的数据当断开了连接就会被自动清空,用完临时表,记得清掉,释放存储空间
    3.你应该测试临时表看看它们是否真的比对大量数据库运行查询快。如果数据很好地索引,临时表可能一点不快。

四、优化数据类型

  1. 1.应该尽可能多的使用数值操作,而不是字符串操作。
    2.如果”小“类型够用,就不要选用”大“类型。
    3.尽可能的把数据声明为not null,这样一方面可以更快的完成对数据列的处理,另一方面我们也不用总是简查值是否为空,还可以减小存储空间
    4.尽量避免对很大的BLOB或TEXT值进行检索
    5.把BLOB或TEXT数据列剥离到单独一个数据表里,条件是移出 该列后可将表转换为定长行格式。这样会减少主表中的碎片,而且能利用定长行的性能优势

五、sql语句优化

  1. 当确定查询结果只有一个或一条记录时,在sql里加上limit 1,mysql就会在找到第一条记录后停止搜索,不再继续查找下一条符合记录的数据。
    2.避免使用select *,需要什么字段取什么。
    3.尽量少 join
    4.尽量少排序
    5.尽量少 or:很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果

    6.尽量用 union all 代替 union:union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的 CPU 运算,加大资源消耗及延迟。所以当我们可以确认不  可能出现重复结果集或者不在乎重复结果集的时候,尽量使用 union all 而不是 union
     7.尽量早过滤:比如我们在多个表进行分页数据查询的时候,我们最好是能够在一个表上先过滤好数据分好页,然后再用分好页的结果集与另外的表 Join,这样可以尽可能多的减少不必要的 IO 操作,大大节省 IO 操作所消耗的时间。
     8.避免类型转换:指 where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换
     9.优先优化高并发的 SQL,而不是执行频率低某些“大”SQL
     10.尽可能对每一条运行在数据库中的SQL进行 explain:进行大量的 explain 操作收集执行计划,并判断是否需要进行优化

    *永远别要用复杂的sql语句来显示你的聪明,对于多表关联,复杂的sql尽量拆分,保证准确性才是王道。

    2.limit分页查询优化

        1.offset比较小的时候:
 select * from yanxue8_visit limit 10,10
 Select * From yanxue8_visit Where vid >=(Select vid From yanxue8_visit Order By vid limit 10,1  ) limit 10
 测试结论:偏移offset较小的时候,直接使用limit较优。这个显然是子查询的原因。

        2.offset大的时候
 select * from yanxue8_visit limit 10000,10  
 Select * From yanxue8_visit Where vid >=(Select vid From yanxue8_visit Order By vid limit 10000,1  ) limit 10  .
 测试结论:offset越大,后者越优   
 利用主键索引排序

© 著作权归作者所有

共有 人打赏支持
七爹
粉丝 1
博文 6
码字总数 4080
作品 0
宁波
程序员

暂无相关文章

istio 文档

https://istio.io/docs/concepts/ https://istio.io/docs/concepts/traffic-management/handling-failures/ https://istio.io/docs/concepts/traffic-management/rules-configuration/......

xiaomin0322 ⋅ 19分钟前 ⋅ 0

编程语言的作用及与操作系统和硬件的关系

一、编程语言的作用及与操作系统和硬件的关系 作用:编程语言是计算机语言,是一种程序员与计算机之间沟通的介质,通过编程语言可以使得计算机能够根据人的指令一步一步去工作,完成某种特定...

slagga ⋅ 29分钟前 ⋅ 0

runtime实现按钮点击事件

也不能说是实现吧,,,就是有点类似于RAC里边的写法,不用给btn添加另外的点击事件,就那个add...select...这样子很不友好,来看下代码: [self.btn handleControlEvent:UIControlEventTou...

RainOrz ⋅ 29分钟前 ⋅ 0

Windows系统运维转linux系统运维的经历

开篇之前,首先介绍一下我的背景把:我是一个三线城市的甲方运维。最近,在《Linux就该这么学》书籍的影响下和朋友小A(Linux运维已经三年了,工资也比我的高很多)的影响下,决定转行。最近...

linux-tao ⋅ 30分钟前 ⋅ 0

zip压缩工具,tar打包工具

zip压缩工具 zip打包工具跟前面说到的gzip,bz2,xz 工具最大的不一样是zip可以压缩目录。如果没有安装,需要使用yum install -y zip 来安装。安装完之后就可以直接使用了,跟之前提到的压缩...

李超小牛子 ⋅ 38分钟前 ⋅ 0

使用npm发布自己的npm组件包

一、注册npm账号 官网:https://www.npmjs.com/signup 注册之后需要进行邮箱验证,否则后面进行组件包发布时候会提示403错误,让进行邮箱核准。 二、本地新建一个文件夹,cd进入后使用npm i...

灰白发 ⋅ 40分钟前 ⋅ 0

010. 深入JVM学习—垃圾收集策略概览

1. 新生代可用GC策略 1. 串行GC(Serial Copying) 算法:复制(Copying)清理算法; 操作步骤: 扫描年轻代中所有存活的对象; 使用Minor GC进行垃圾回收,同时将存活对象保存到“S0”或“S...

影狼 ⋅ 40分钟前 ⋅ 0

JVM性能调优实践——JVM篇

在遇到实际性能问题时,除了关注系统性能指标。还要结合应用程序的系统的日志、堆栈信息、GClog、threaddump等数据进行问题分析和定位。关于性能指标分析可以参考前一篇JVM性能调优实践——性...

Java小铺 ⋅ 41分钟前 ⋅ 0

误关了gitlab sign-in 功能的恢复记录

本想关sign-up的,误点了sign-in 退出后登录界面提示: No authentication methods configured 一脸懵逼.. 百度后众多方案说修改application_settings 的 signin_enabled字段; 实际上新版本字段...

铂金蛋蛋 ⋅ 42分钟前 ⋅ 0

登录后,后续请求接口没有带登录cookie可能原因

1.XMLHttpRequest.withCredentials没设置好,参考https://developer.mozilla.org/zh-CN/docs/Web/API/XMLHttpRequest/withCredentials...

LM_Mike ⋅ 43分钟前 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部