Mysql索引优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 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

image.png

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

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

  • 组合索引
  • 多个单字段索引冲突

组合索引

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

image.png

当我们勾选上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语句的生成,禁止发生列运算类型转换的发生。
  • 长字符串可以使用前缀索引,只对字符串的前面一定字符长度建立索引。
  • 组合索引的顺序合理优化(会有新文章介绍)
  • 当多个单字段索引发生冲突时,强制使用某个索引。

本文原创作者 Siam博客 原文地址:https://www.siammm.cn/archives/100

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
2月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
2月前
|
存储 关系型数据库 MySQL
MySQL数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
85 4
|
4月前
|
存储 关系型数据库 MySQL
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
|
3月前
|
SQL 缓存 关系型数据库
MySQL 慢查询是怎样优化的
本文深入解析了MySQL查询速度变慢的原因及优化策略,涵盖查询缓存、执行流程、SQL优化、执行计划分析(如EXPLAIN)、查询状态查看等内容,帮助开发者快速定位并解决慢查询问题。
126 0
|
16天前
|
缓存 关系型数据库 MySQL
降低MySQL高CPU使用率的优化策略。
通过上述方法不断地迭代改进,在实际操作中需要根据具体场景做出相对合理判断。每一步改进都需谨慎评估其变动可能导致其他方面问题,在做任何变动前建议先在测试环境验证其效果后再部署到生产环境中去。
55 6
|
6月前
|
关系型数据库 MySQL 数据库
Mysql的索引
MYSQL索引主要有 : 单列索引 , 组合索引和空间索引 , 用的比较多的就是单列索引和组合索引 , 空间索引我这边没有用到过 单列索引 : 在MYSQL数据库表的某一列上面创建的索引叫单列索引 , 单列索引又分为 ● 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。 ● 唯一索引:索引列中的值必须是唯一的,但是允许为空值 ● 主键索引:是一种特殊的唯一索引,不允许有空值 ● 全文索引: 只有在MyISAM引擎、InnoDB(5.6以后)上才能使⽤用,而且只能在CHAR,VARCHAR,TEXT类型字段上使⽤用全⽂文索引。
|
2月前
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
|
7月前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
2月前
|
存储 SQL 关系型数据库
MySQL 动态分区管理:自动化与优化实践
本文介绍了如何利用 MySQL 的存储过程与事件调度器实现动态分区管理,自动化应对数据增长,提升查询性能与数据管理效率,并详细解析了分区创建、冲突避免及实际应用中的关键注意事项。
105 0
|
3月前
|
存储 关系型数据库 MySQL
MySQL覆盖索引解释
总之,覆盖索引就像是图书馆中那些使得搜索变得极为迅速和简单的工具,一旦正确使用,就会让你的数据库查询飞快而轻便。让数据检索就像是读者在图书目录中以最快速度找到所需信息一样简便。这样的效率和速度,让覆盖索引成为数据库优化师傅们手中的尚方宝剑,既能够提升性能,又能够保持系统的整洁高效。
99 9

推荐镜像

更多