查询性能优化,有3个方法。
- 如何设计最优的库表结构
- 如何建立最好的索引
- 合理的设计查询
库表结构优化、索引优化、查询优化需要齐头并进,一个不落。
在尝试编写快速的查询之前,需要清楚一点,真正重要是响应时间。
提升性能的两个方法:空间换时间,随机转顺序
方法1:设计最优的库表结构,参考:数据库查询性能优化方法1:设计最优的库表结构
方法3:合理设计查询语句,参考:数据库查询性能优化方法3:合理设计查询语句
本篇为方法2:建立最好的索引
文章目录
- 建立最好的索引
- 索引设计规范
建立最好的索引
MySQL可以使用同一个索引既满足排序,又用于查找行。因此,如果可能,设计索引时应该尽可能地同时满足这两种任务,这样是最好的。
查找行是索引的常规功能,不再赘述。
下面来看使用索引扫描来优化排序。
MySQL 有两种方式可以生成有序的结果:
- 通过order by排序操作
- 按索引顺序扫描,如果EXPLAIN 出来的type列的值为“index”,则说明MySQL 使用了索引扫描来做排序(不要和Extra 列的“Using index,搞混淆了)。
只有当下面3个条件都满足,MySQL 才能够使用索引来对结果做排序,条件还是比较苛刻的
- 索引的列顺序和ORDER BY子句的顺序完全一致
- 所有列的排序方向(倒序或正序)都一样时
- Order by中的字段全部在关联表中的第一张表中
排序本身就是一个成本很高的操作
索引设计规范
- 限制每张表上的索引数量,建议单张表索引不超过5个
- 禁止给表中的每一列都建立单独的索引
- 不使用更新频繁的列做主键,不使用多列主键
- 不使用UUID、MD5、HASH、字符串,作为主键
- 主键建议使用自增ID列
- 常见索引列建议
- select update delete语句的where从句中的列
- 包含在order by、group by、distinct中的字段
- 多表join的关联列
- 联合索引索引列的顺序
- 在一个多列B+ Tree 联合索引中,索引列的顺序意味着索引首先按照最左列进行排序,其次是第二列,等等。所以,索引可以按照升序或者降序进行扫描,以满足精确符合列顺序的ORDER BY、GROUP BY 和DISTINCT 等子句的查询需求。如果不是按照索引最左列开始查找(最左匹配原则),则无法使用索引。
- 当不需要考虑排序和分组时,将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE 条件的查找。
在这种情况下,这样设计的索引确实能够最快地过滤出需要的行,对于在WHERE 子句中只使用了索引部分前缀列的查询来说选择性也更高。
然而,性能不只是依赖于所有索引列的选择性(整体基数),也和查询条件的具体值有关,也就是和值的分布有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序,让这种情况下索引的选择性最高。 - 在满足选择性的前提下,尽量把字段长度小的列放在联合索引的最左侧(每页容纳更多的索引)
- 使用最频繁的列放到联合索引的左侧
- 避免建立冗余索引和重复索引
重复索引
primary key(id),unique index(id),index(id)
mysql的主键就是一个非空的唯一索引
冗余索引
比如上图,联合索引中是按照从左到右来使用的,index(a)就是冗余的
冗余索引增加查询优化器选择的时间
发生此现象的原因可能是为了使用联合索引,而忘记了将原a列上的索引删除
- 对于频繁的查询优先考虑使用覆盖索引,避免回表。
比如查询商品库存这样的sql,我们就要优先考虑是否使用覆盖索引,索引中包括全部字段 - 尽量避免使用外键,而在表与表之间的关联键上建立索引
保证数据的参照完整性,建议在业务端实现 - 前缀索引和索引选择性
有时候需要索引很长的字符列,这会让索引变得大且慢
一般情况下某个列前缀的选择性也是足够高的,足以满足查询性能。对于BLOB、TEXT 或者很长的VARCHAR 类型的列,必须使用前缀索引,因为MySQL 不允许索引这些列的完整长度。
使前缀的选择性接近于完整列的选择性 - 如果计划在列上建索引,就应该尽量避免设计成可为NULL的列。索引NULL列需要额外的空间来保存,所以要占用更多的空间(索引空间占用越少越好,这样一页可以放更多的索引)
参考:MySQL中NULL对索引的影响
null列是可以用到索引的,不管是单列索引还是联合索引,但仅限于
is null
,is not null
是不走索引的
- 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引
- 使用索引时不能跳过索引中的列
- 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列,这样MySQL才有可能使用索引来优化这个过程。
- category这个字段有没有必要创建索引呢?这是mysql中一个比较讨厌的问题,因为其没有oracle中的位图索引bitmap
比如网易云音乐里面的音乐类别,如摇滚、民谣啥的,如歌曲有100万首,类别不超过50个,所以类别不会进行索引
如果说你要对类别进行查询,那应该怎么办呢?
方法1:就是用list分区
方法2:分多张表
但这样做,开发效率就会变低了
某一个类别,比如说流行,其歌曲会非常非常多,而如独立,其歌曲会比较少
以上所说的都是一首歌曲仅仅只有一个category。如果一首歌曲有多个category,处理方法就不同了,类似多对多的关系,与用户和标签的多对多关系类似,这种情况使用关系型数据库就不太合适了,使用es的倒排索引机制更加合适。