mysql查询优化
博客专区 > 七爹 的博客 > 博客详情
mysql查询优化
七爹 发表于2年前
mysql查询优化
  • 发表于 2年前
  • 阅读 20
  • 收藏 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越大,后者越优   
 利用主键索引排序

标签: mysq 查询 优化
共有 人打赏支持
粉丝 2
博文 6
码字总数 4080
×
七爹
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: