下面我将详细讲解如何根据三条原则为MySQL表添加索引:
什么是MySQL索引
在进行MySQL查询时,如果MySQL没有为表添加索引,那么查询语句执行时就会进行全表扫描,这会导致查询效率极低。而添加索引可以让MySQL在进行查询时只搜索索引列,从而提高查询效率。因此,我们可以将索引看作是对数据库中某一列或多列的引用,可以提高查询速度。
三条原则
1. 最左前缀匹配原则
在 MySQL 中,使用索引时必须从最左前缀列开始匹配,如果不是最左前缀列匹配,那么该索引将不可用。
比如,我们有一个表users,包含三列:id、name、email,我们想要通过name和email两列进行查询,因此我们需要添加一个联合索引。但必须遵守最左前缀匹配原则,如果索引是(name, email),则查询"WHERE email='123@qq.com'"是可用索引的,而查询"WHERE name='Tom'"不是可用索引的,因为name列不是最左前缀列。
2. 索引列上不能有函数 or 运算符
如果查询语句中的索引列上使用了函数或运算符,MySQL 将不会使用该索引。
比如,我们有一个表orders,包含列price,我们想要查询 price0.8 的结果小于 100 的所有行,如果添加索引(price),MySQL 将不会使用该索引,而进行全表扫描查找,因为 price0.8 不是索引列price上的值。
3. 小范围列先
如果存在两个列作为条件进行查询,那么可以采用最小范围的列作为索引列。
比如,我们有一个表product,包含两列:id和name,我们想要查询id=100,并且name like '%test%'的所有行。如果添加索引(id, name),MySQL 将会使用该索引,因为id是小范围的列,查询结果的范围比name列更小,因此应该使用id作为索引列。
示例说明
示例1:
假设我们有一个表book,包含五列:id、name、author、price、publish_time。现在我们需要通过name和author两列进行查询,因此需要添加一个联合索引。我们可以通过以下命令添加:
ALTER TABLE `book` ADD INDEX `name_author` (`name` ASC, `author` ASC);
示例2:
假设我们有一个表order,包含五列:id、user_id、status、total_price、create_time。我们需要查找create_time是2019年8月份,且status是‘已发货’的所有订单。那么我们需要添加联合索引并且按照以下方式添加:
ALTER TABLE `order` ADD INDEX `multi_idx` (`status` ASC, `create_time` ASC);
在添加该索引后,我们进行查询时,MySQL 会先使用status列过滤出相应的记录,再使用create_time过滤,从而提高查询效率。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:简单了解添加mysql索引的3条原则 - Python技术站