【索引专题】索引优化相关

共以下 4 个回答

• 終达·斯维达尼亚 永久会员 2024年2月9日 上午3:22

  1、字段加索引，你是否在自己的项目中用过呢？你觉得什么样的字段适合加索引？

  订单系统包含订单号和支付状态这两个字段，由于经常需要通过订单号去查询订单，因此可以给订单号加一个普通索引，但是因为经常使用订单号仅仅是查询支付状态，因此可以给订单号和支付状态加一个联合索引，这样可以省去回表的操作，提高效率。适合加索引的字段首先要索引区分度好、增删改查频繁以及字段的长度不能太长。

  2、Mysql怎么创建索引

  • 对于普通索引和联合索引，都是 CREATE INDEX 索引名 ON 表名(列名, …) 。
  • 对于唯一索引，多了一个 UNIQUE 关键字，CREATE UNIQUE INDEX。
  • 如果想删除索引的话，是 DROP INDEX 索引名 ON 表名。

  3、那你觉得，字段加了索引，查找的时候一定会走索引吗？

  这是不一定的，加了索引不走索引的情况有

  • 在书写sql语句的时候查询条件写成了函数语句，这时sql将不会走索引而是全表扫描
  • 使用LIKE 查询并且把通配符 % 放在开头
  • 使用联合索引但没有遵循最左匹配原则等等

  4、刚才你的索引失效的例子，都是因为人为没有写好 sql 导致的，那如果排除人为的情况，sql 正确书写，那就一定会走索引吗？

  在sql内部优化的时候会判断是否走索引会更快，如果判断走索引不会更快则也会走全表扫描，但是在判断的时候是对部分数据的抽样检查，因此可能会出现判断偏差导致查询时走全表扫描，此时可以通过force指令强行让查询走索引

  5、如果我想要强制走某个索引，能实现吗？可以怎么做？

  可以通过force指令强制走制定的索引，比如在两张表之间进行联合查询的时候，根据sql语句书写的不同，可能会走A表的索引也可能会走B表的索引，而如果走两张表的索引复杂度差距过大，则可能会走更加耗时的索引，因此除了在人为书写sql语句时要更加留意以外可以用force指令强制走速度快的索引

  6、如何一条 sql 执行的很慢，我们可以怎么来排查原因？

  • 查询语句执行慢一般分两种情况，首先是查询语句在平时的时候都比较快，此时突然出现卡顿的现象，也就是临时性地突然变慢，这可能是由于sql内部redo log的缓存已经写完，强制将数据写入磁盘，此时将会阻塞主进程，再等待片刻后就会恢复原状。或者说是查询语句在查询被锁住的行，等锁释放即可，这种情况可以用 show processlist 命令来查看语句当前的状态。
  • 第二种情况是查询语句一直都很慢，此时可以先用explain命令排查查询语句有没有走索引，检查是否存在索引、是否是根据指定的索引来查找、sql语句的书写是否不规范从而导致全表扫描的事情发生。其次查看数据库的规模，如果数据库的数据过多，则可能需要对原库进行分库分表。最后可以排查网络原因，因为上线的数据库大多都是集群模式的，网络不通畅通畅会导致主库和从库的数据同步出现延迟从而阻塞主线程。

  7、刚才说到了模糊匹配失效，为什么使用模糊匹配会失效，你能给我解释一下底层原理吗？

  这种情况一般发生在使用 LIKE 关键字时，将通配符 % 放在了开头，导致索引用不上，只能进行全表扫描，效率很低。原理是 InnoDB 索引底层是 B+ 树，对于字符串类型来说，每个叶子节点的数据都按照字符串的首字母排序，如果首字母相同，再比较第二个字母，以此类推。我们再进行模糊查询的时候，如果把 % 放在开头，会导致最左的 N 个字母是不确定的，无法根据索引的有序性定位到某个索引，只能进行全表扫描。

  0 赞同 0 条回复

  登录以回复
• LilyBoy 永久会员 2023年9月21日 下午12:06

  1、字段加索引，你是否在自己的项目中用过呢？你觉得什么样的字段适合加索引？（PS：从索引区分度，查找频率真，增删频繁角度考虑）
  账户申请表、改期表、操作表
  研发人员开通账号：状态字段，频繁查询需要进行加索引；

  索引区分度大

  频繁查询
  不为null
  用于条件查询

  曾删该不适合创建索引
  联合索引
  避免创建冗余索引

  2、mysql怎么创建索引？（PS：说实话，有些人踢踢而谈，但是还真的不知道怎么给字段加索引）
  create index 索引名 on 表（列名）
  create unique index 索引名 on 表(列名)
  drop index 索引

  3、那你觉得，字段加了索引，查找的时候一定会走索引吗？（PS：可以回答索引失效的几个经典因素）
  like ‘%**’
  or前后列没有加索引
  函数计算
  发生隐式转换

  4、刚才你的索引失效的例子，都是因为人为没有写好 sql 导致的，那如果排除人为的情况，sql 正确书写，那就一定会走索引吗？（PS：走不走索引，是经过优化器权衡预测的，所以这里需要回答系统是如何预测的）
  不一定：
  优化器、基数（索引区分度）、采样统计、比较全表扫描、扫喵行数、出现预测错误

  5、如果我想要强制走某个索引，能实现吗？可以怎么做？
  可以，force index

  6、如何一条 sql 执行的很慢，我们可以怎么来排查原因？
  偶尔慢：写入内存redolog日志刷盘、等待锁释放
  一直慢：没有加索引、索引失效、选错了索引

  7、刚才说到了模糊匹配失效，为什么使用模糊匹配会失效，你能给我解释一下底层原理吗？

  0 赞同 0 条回复

  登录以回复
• 小肸 永久会员 2023年8月10日 下午11:25

  我商城项目中有一个需求：需要从订单表中根据用户的ID取出最近的一条订单信息。我就创建一个联合索引(user_id, create_time)来优化性能。首先user_id和create_time两列中重复数据比较少，区分度高，构建B+索引树可以避免走全表查询加快性能。而且这个需求也挺频繁的，每一个用户都需要查看自己最近购买的订单信息，可以添加一个再来一单的功能，比如美团外卖就有这一个功能。返回的行数据也只有一行，不多。如果是根据user_id返回全部订单信息，就没有必要添加索引，都要走全表扫描。

  alter table 表名 add index idx_索引名 (`索引列1` [asc | desc], `索引列2` [asc | desc])
  alter table 表名 add unique index uk_索引名 (`索引列`)
  create index idx_索引名 on 表名 (`索引列` [asc | desc])
  create unique index uk_索引名 on 表名 (`索引列` [asc | desc])
  

  不一定，有时候sql语句中使用了模糊匹配、函数会导致索引列进行了数据转换，导致索引失效。另外查询大量数据时也可能会导致索引失效走全表扫描。

  也不一定。MySQL是根据优化器的分析决定是否使用索引。MySQL的优化器会预估每个索引大概会扫描多少行数据，然后结合回表的成本、排序的成本来选择它认为性能最好的索引。有时候MySQL采样统计的扫描行数可能不准确，导致使用错误的索引。有时候可以通过修改sql语句去引诱优化器选择正确的索引或者看能不能就使用一个索引，不使用或者删除错误的索引。可以使用 analyze table 表名让MySQL重新统计表的索引信息。

  可以，加上force index(索引名)强制执行。

  如果是突然很慢的话，update语句应该可能是并发量突然增大了，导致锁等待时间变长导致sql语句执行慢。select语句可以看看最近时间短是不是有查询大量数据的sql语句被执行，导致缓冲池的空间不够，需要把脏页刷到磁盘中后才执行select语句。

  如果是一直很慢，如果使用了索引使用explain语句看看是走全表扫描还是索引，看看索引失效没有。如果没使用索引，根据业务功能考虑需不需要创建索引来优化性能。

  原理上B+树上面的叶子节点字符串值的比较是从左往右依次根据一个字符比较大小，是最左匹配原则。如果通配符%或者_放在左边开头，根本无法确定第一个要比较的字符是什么，B+树就无法快速定位。

  0 赞同 0 条回复

  登录以回复
• 秊 永久会员 2023年7月31日 下午8:50

  1、字段加索引，你是否在自己的项目中用过呢？你觉得什么样的字段适合加索引？（PS：从索引区分度，查找频率真，增删频繁角度考虑）

  使用的很多，比如支付模块的支付信息表，包含有订单号和支付状态两个字段。由于经常需要通过订单号去查它的支付状态，可以单独给订单号创建一个普通索引，但考虑到每次只是由订单号查支付状态这一个字段，可以创建一个订单号和支付状态的联合索引，也就是覆盖索引，这样就省去了使用普通索引的回表操作，提高查询效率。

  对于是否需要给字段加索引，需要考虑几个因素。第一个是要考虑索引的区分度，尽量选择区分度高的字段作为索引。第二个是需要考虑这个字段的查询频率。对于频繁增删改的字段，可以创建索引来加快查询速度。第三个是要考虑字段的长度，过长会占用空间，过短可能会导致索引区分度降低。比如身份证号只取前 6 位这种情况。

  2、mysql怎么创建索引？（PS：说实话，有些人踢踢而谈，但是还真的不知道怎么给字段加索引）

  对于普通索引和联合索引，都是 CREATE INDEX 索引名 ON 表名(列名, ...) 。

  对于唯一索引，多了一个 UNIQUE 关键字，CREATE UNIQUE INDEX。

  如果想删除索引的话，是 DROP INDEX 索引名 ON 表名。

  3、那你觉得，字段加了索引，查找的时候一定会走索引吗？（PS：可以回答索引失效的几个经典因素）

  不一定会走索引（，即使走索引也不一定会走最佳的索引）。可能会因为人为没有写好 SQL 导致索引失效。比如在 SQL 中使用函数，LIKE 查询并且把通配符 % 放在开头，使用联合索引但没有遵循最左匹配原则等等，都是会导致索引失效的。

  4、刚才你的索引失效的例子，都是因为人为没有写好 sql 导致的，那如果排除人为的情况，sql 正确书写，那就一定会走索引吗？（PS：走不走索引，是经过优化器权衡预测的，所以这里需要回答系统是如何预测的）

  也不一定，因为走不走索引，走哪个索引都是由优化器权衡预测的。优化器会选择一个最优的执行方案，用最小的代价去执行语句。其中扫描行数是影响执行代价的因素之一，它是由 MySQL 进行采样统计得到的估算值，有时候误差可能很大，导致优化器认为执行的代价也很大，进而就不会走最佳的索引。除了扫描行数，是否使用到了临时表，是否排序等因素也会被考虑在内。

  对于只是扫描行数估计值不准确的情况，可以使用 analyze table 语句重新统计索引信息，就可以解决。其它情况就需要考虑别的办法，让系统走我们认为对的索引，比如使用 force index 强行选择一个索引，或者根据实际情况新建一个更合适的索引，删除走错的索引等等。

  5、如果我想要强制走某个索引，能实现吗？可以怎么做？

  可以，最简单的办法就是使用 force index 语句，这个建立在我们确定某个索引一定是最佳的，但系统会走错这种情况。缺点是变更的及时性差，等到出问题时再加上 force index，再测试和发布，整个过程不够敏捷。

  其次就是在数据库内部解决，考虑根据情况修改语句，引导 MySQL 使用我们期望的索引。

  还有就是新建一个更合适的索引，来提供给优化器做选择，或删除走错的索引。

  6、如何一条 sql 执行的很慢，我们可以怎么来排查原因？

  一条 SQL 执行的很慢，需要考虑是每次执行都很慢，还是偶尔很慢。所以需要分两种情况讨论。

  对于偶尔很慢的情况，我觉得这条 SQL 语句本身是没有什么问题的，而是其他原因导致的。比如 redo log 被写满的情况下，MySQL 只能暂停正在执行的操作，通过 redo log 将最新的数据同步到磁盘中，这个时候就会导致平时正常的 SQL 语句突然执行的很慢。还有一种情况是这条语句涉及的表或者数据行被上锁了，那只能等其它语句释放锁后再执行，这种情况可以用 show processlist 命令来查看语句当前的状态。

  对于一直都这么慢的情况，大概率是 SQL 写的有问题，比如语句中用了索引，但实际却没有走索引，或者走错了索引。（扯 3、4、5）

  7、刚才说到了模糊匹配失效，为什么使用模糊匹配会失效，你能给我解释一下底层原理吗？

  这种情况一般发生在使用 LIKE 关键字时，将通配符 % 放在了开头，导致索引用不上，只能进行全表扫描，效率很低。

  原理是 InnoDB 索引底层是 B+ 树，对于字符串类型来说，每个叶子节点的数据都按照字符串的首字母排序，如果首字母相同，再比较第二个字母，以此类推。我们再进行模糊查询的时候，如果把 % 放在开头，会导致最左的 N 个字母是不确定的，无法根据索引的有序性定位到某个索引，只能进行全表扫描。

  0 赞同 0 条回复

  登录以回复