MySQL 索引优化3

Posted on

  • 《阿里巴巴开发手册》：页面搜索严禁左模糊或全模糊，如果需要可以用搜索引擎解决。

    • 说明：索引文件具有 B+Tree 最左前缀匹配特性，如果左边的值未确定，则无法使用此索引，将导致全表扫描。

  • 索引选择性陷阱 在索引匹配的情况下，如果索引的区分度不够，当索引覆盖范围的数据过多时，MySQL查询优化器会根据索引分布(百分比)”优化”算法，采用全表扫描的方式代替使用索引，导致性能问题。

    • 在模糊查询时，如果没有用”最佳左前缀”，会直接导致全表扫描
    • 在模糊查询时，即使使用了”最佳左前缀”，但是区分度不足，导致数据过多，最终优化为全表扫描。 如：用 1 位前缀模糊查询手机号，当用户极少时可以命中索引；当用户较多时，全表扫描
    • 索引字段本身备选项较少，区分度不足，即使命中也会数量太多，索引 MySQL 会自动优化为全表扫描 例如：性别 = 男、is_delete = 0、月薪<10 万

  • 索引分布大概在 25%时可能会引发索引选择性陷阱导致全表扫描，但是这个数字并不精确，最终以 explain 为准

  • 方案：

    • 可以在 MySQL 查询语句中用forece index(fieldName)强制使用索引
    • 增加缓存，提高全表扫描速度(费用高) innodb_buffer_pool_size=16G、innodb_buffer_pool_instances=2
    • 可以用 ElasticSearch 这种搜索引擎(分片多线程检索)解决这种情况下的检索性能问题，但是同时带来了数据不一致和架构复杂性的问题，需要权衡考虑