创建高性能索引笔记

时间:2022-12-11 21:19:11

可以使用B-Tree索引的查询类型

  • 全值匹配 :和索引中的所有列进行匹配
  • 匹配最左前缀 :可只是用索引的第一列
  • 匹配列前缀 :也可以只匹配某一列的值的开头部分
  • 匹配范围值 :查找在某个范围的
  • 精确匹配某一列并范围匹配另外一列 :即第一列全批评,第二列范围匹配

B-Tree索引的一些限制

如果不是按照索引的最左列开始查询,则无法使用索引

不能跳过索引中的列,只有索引中的1、3列缺失了第二列,则只能使用索引的第一列

如果查询中有某个列的范围查询,则其右边的所有列无法使用索引进行优化查询

高性能索引的策略

 

  • 独立的列

        ”独立的列“ 是指索引列不能是表达式的一部分,也不能是函数的参数

  • 前缀索引和索引的选择性

        有时需要索引很长的字符列,这会让索引变得大且慢。一个策略是模拟哈希索引,通常还可以索引开始的部分字符,这里需要选择合适的前缀长度,使前缀的选择性达到完整列的选择性,计算方式

SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo;

+----------------------------------------+
|COUNT(DISTINCT city)/COUNT(*) |
+----------------------------------------+
| 0.0312 |
+----------------------------------------+

SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7,
FROM sakila.city_demo;

+--------------------------------------------------+
|sel3 |sel4 |sel5 |sel6 |sel7 |
|0.0239 |0.0293 |0.0305 |0.0309 |0.0310 |
+-------------------------------------------------+

查询显示达到7的时候再曾经长度,提升的效果已经很小了

  • 多列索引
  • 选择合适的索引列顺序
  • 聚簇索引,innodb的数据存储方式