写点什么

浅析 mysql 性能优化

作者:乌龟哥哥
  • 2022 年 4 月 20 日
  • 本文字数:2573 字

    阅读完需:约 8 分钟

浅析mysql性能优化

写在前面

在我们日常使用数据库的时候,肯定避免不了对数据库的优化。那么对数据库的优化又少了不索引的知识。


是的,建立索引能极大地提高查询的效率。那么你知道吗,如果合理建立索引,可以更大地榨出数据库的性能——也就等同于进一步提高查询效率。


写下这篇文章就是为了记录一下对索引的优化,合理建立索引。

什么是索引

MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构。


我们可以简单理解为:快速查找排好序的一种数据结构。Mysql 索引主要有两种结构:B+Tree 索引和 Hash 索引。


索引就相当于字典前的目录,如果这个目录划分规划更好,那么我们找到想要的数据就会更方便,也就提高了查询的效率。


索引的类型有:


  • normal:表示普通索引

  • unique:表示唯一的,不允许有重复值的索引

  • full textl: 表示全文搜索的索引,用于搜索很长一篇文章的时候,效果最好。

建立索引的场景

索引不是越多越好,因为每次更新、插入数据,就需要对索引文件进行变动,会减低该类型操作的执行效率。


如果建立索引的字段太多,影响就会很大。


所以我们只在合理的字段上建立索引


  • 在经常用来当查询条件(where,on,group by,order by)的字段上建立索引。

  • 在数据的维度比较大的字段上建立索引。

  • 对数据较小的列使用索引,可以使索引文件更小,同时内存中也可以装载更多的索引键。

  • 为较长的字符串使用前缀索引。比如数据的长度大部分是150个字节,我们只建立前100个字节的索引。


常见的可以用于建立索引的字段场景:


① 用户 id 在订单表中的用户 id 字段上建立索引,根据用户 id 筛选订单,则会很快查询出用户的订单。用户一般是在自己的后台查看订单,所以表中的其他用户数据与他无关,如果没有建立索引,每次查询都是全表扫描,则会很慢。(我们每个人在淘宝平台上占的订单比例都不到 0.0001)


② 商品名同上,如果不建立索引,我们在淘宝输入框搜索键盘,怎么快速查询出键盘商品?


③ 日期常用的活跃数据,一般都是是最近产生的,很少人没事去翻半年前的订单数据吧?


合适的还有很多很多场景,需要各位小伙伴自己好好思量。

索引不生效的场景

并不是建立了索引就在查询语句中生效。


  • 当语句中带有 or 的时候,索引会失效

  • 当索引的字段使用 like 查询,并且使用了前通配比如%Siam,索引失效。后通配会生效Siam%

  • 当索引的字段是字符类型,但是储存的值是数字,比如 user_name:’123456’,在查询语句中要 user_name = ‘123456’而不能 user_name = 123456 否则发生类型转换,索引失效,其他类型的字段 比如日期等 也同理

  • 当使用的条件语句,预计结果数量超过全表数据的一定比例时,会转为全表扫描(mysql 一般是 30%左右)这就是为什么在建立索引的时候要选择维度(区别度)比较高的列,性别这种字段不适合建立索引。

  • 语句中出现列数据运算才判断的,比如 where age – 10 > 0 每一行都要运算之后才知道是否大于 0 所以就是全表扫描,如果 age > 10 则可使用索引。使用函数转换列数据也一样原理。

  • 组合索引时使用的条件语句。


上面举例的一些场景都是比较容易理解的。组合索引还没涉及,往下继续介绍。

mysql 中,多个索引同时使用?

先来看一条语句


select * from test where user_name = ``'siam_007'` `and` `create_time = 1563280050
复制代码


假设在这个表中,user_name 字段和 create_time 都是建立了索引的。也没有发生数据类型转换等情况。


问题:该语句有几个索引生效?


是 2 个吗?并不是这样子的。


虽然两个字段的索引都没有问题,如果单独使用也都能生效。


但是:MySQL会从可用的索引中猜测出效率最高的一个索引并使用它


怎么证明?使用explain语句(详解可以在网上其他资料找到)


只要在 sql 语句最前面加上该语法,则会显示查询类型相关的信息。


Explain select * from mysql_index_test where user_name = ``'siam_007'` `and` `create_time = 1563280050
复制代码



我建立了两个索引,都能用,都是本次查询只能使用一个。


基于这个情况,会引申出两个知识点


  • 组合索引

  • 多个单字段索引冲突

组合索引

先来说说组合索引吧,我们在新建索引的时候(可视化软件),是这样子的页面



当我们勾选上 2 个或者 2 个以上 就能把它叫为组合索引


可以看到还有调整顺序的功能


在组合索引中,字段顺序也是极为重要的。


假设我们有这样一个索引:new : a,b,c 字段


在查询时使用where a = 1 and b = 2 and c= 3。那么这个语句肯定是生效的。


如果我们使用where b = 2 and c =3没有 a 条件 而 a 又是在组合索引中最左侧的,那么索引就不会生效。


还是要扫描全部行


where a,c where a,b where b,a,c 这样子使用都是可以生效的。


组合索引要注意字段顺序,是指在创建索引时候的排序,而不是 sql 语句中 where 的顺序,我们使用where b = 2 and a = 1 and c = 3也是 可以生效的


那么组合索引的字段顺序要如何排比较好?


后面会有新的一篇文章会讲这个

多个单字段索引冲突

如果我们在表中,拥有两个索引,比如user_name 用户名, user_phone 用户手机号


现在有这样子的数据量:100W 条数据 user_name=’我是用户名’100 条数据 user_phone=’110′5 条数据 user_name=’我是用户名’ and user_phone=’110′


假设有这样子一条语句:


select * from test where user_name = ``'我是用户名'` `and` `user_phone=``'110'
复制代码


有两个字段都有索引可用,mysql 会选择一个使用。这是属于 mysql 的内部处理判断


正常情况下,如果用 user_phone 索引生效的话,会很快得到结果(先筛选出 100 条 再筛选)


如果 user_name 生效,则要先筛选 100W 条数据,再筛选 user_phone


mysql 的内部判断可能使得 user_name 索引生效,此时效率就会很低了,我们可以强制使用某个索引


phone 是索引名 是新建的时候填的


select * from test index(phone) where user_name = ``'我是用户名'` `and` `user_phone=``'110'
复制代码


此种情况出现的概率比较小,毕竟 mysql 的解释器很复杂,也做了足够多的优化。只有排查慢日志并且分析确定索引冲突的情况才需要强制使用索引。

优化 (总结)

  • 只在经常使用的字段上建立索引,否则会拖慢数据更新和插入的速度。

  • 只在维度高的字段上建立索引,否则会使得数据比例过大,转为全表扫描。

  • 优先对数据量比较小的字段建立索引,可以使索引文件更小,同时内存中也可以装载更多的索引键。

  • 规范 sql 语句的生成,禁止发生列运算类型转换的发生。

  • 长字符串可以使用前缀索引,只对字符串的前面一定字符长度建立索引。

  • 组合索引的顺序合理优化(会有新文章介绍)

  • 当多个单字段索引发生冲突时,强制使用某个索引。

用户头像

乌龟哥哥

关注

正在努力寻找offer的大四小菜鸟 2021.03.16 加入

擅长 Hbuilder、VS Code、MyEclipse、AppServ、PS 等软件的安装与卸载 精通 Html、CSS、JavaScript、jQuery、Java 等单词的拼写 熟悉 Windows、Linux、 等系统的开关机 看–时间过得多快,不说了,去搬砖了

评论

发布
暂无评论
浅析mysql性能优化_4月月更_乌龟哥哥_InfoQ写作社区