在MYSQL表上添加复合索引 [英] Adding composite indexes on MYSQL table

问题描述

我有一张这样的桌子

CREATE TABLE IF NOT EXISTS `billing_success` (
`bill_id` int(11) NOT NULL AUTO_INCREMENT,
`msisdn` char(10) NOT NULL,
`circle` varchar(2) NOT NULL,
`amount` int(11) NOT NULL,
`reference_id` varchar(100) NOT NULL,
`source` varchar(100) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`bill_id`),
KEY `msisdn` (`msisdn`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8573316 ;

，我想添加复合索引以优化查询.这是我用来生成报告的表，最近它变得非常缓慢.这些是我用来生成报告的查询

and I want to add composite indexes to optimize queries. This is the table from where I generate reports, lately its becoming very slow. These are my queries to generate reports

1.SELECT msisdn,amount,circle FROM billing_total_success WHERE (source='XX' OR source='Y' OR source='STR') AND (time like '$date%')

2.SELECT msisdn,amount,circle FROM billing_total_success WHERE source <> 'RNH' AND source <> 'STR' AND source <> 'XAS' AND source <> 'RTR' AND (time like '$date%')

3.SELECT msisdn,amount,circle FROM billing_total_success WHERE (source='STR' OR source='RER' OR source='ASD') AND time BETWEEN  '$date1' AND '$date2'

4.SELECT msisdn,amount,circle FROM billing_total_success WHERE (source='RNH') AND time BETWEEN  '$date1' AND '$date2'

请告诉我应该在哪里为这些查询添加索引以进行优化，以及如何在给定查询的情况下添加适当的索引.

Please tell me where all should I add indexes for these queries to be optimized and how to add proper index given the query.

推荐答案

A.您可能需要在timestamp字段上建立索引.

A. You probably want an index on the timestamp field.

然而，这并不一定是故事的结局.如果您的查询是在时间戳索引上进行范围扫描，那么在InnoDB中将其作为二级索引不一定是个好主意.

However this isn't necessarily the end of the story; if your queries are range-scanning on the timestamp index, then having it as a secondary index is not necessarily a good idea in InnoDB.

B.将时间戳记作为主键的第一部分

B. Make timestamp the first part of the primary key

这是违反直觉的，但是由于InnoDB群集在主键上，因此具有时间戳的主键的第一部分将使二级索引范围扫描变为主键范围扫描，通常情况下会更好.次要索引范围扫描需要检索范围中的每一行；主键扫描已已检索到它.

This is counter-intuitive, but as InnoDB clusters on the primary key, having the timestamp the first part of the primary key will make the secondary index range scan into a primary key range scan, which is generally better. A secondary index range scan needs to retrieve each row in the range; a primary key scan has retrieved it already.

C.按时间分区

如果由于表变得太大而导致查询速度变慢(表需要占用大量的I/O内存)，但是您始终查询的时间范围较小，请考虑使用每日或每小时分区.

If your queries are slow because the table is becoming too big for memory (IO reads are required often), but you are always querying a small(ish) time range, consider having daily or hourly partitions.

当然，如果您的用户位于不同的时区，则每日分区的效果会差很多，因为他们的日子与分区的日子重叠.

Of course daily partitions work a lot less well if your users are in different time zones, as their days overlap with the partitions' days.

分区需要脚本对表进行持续维护(创建新分区；删除旧分区).您需要提供并测试这些脚本，因此这显然是开发人员的开销.操作还需要监视这些脚本，因此它们也有操作开销.

Partitioning requires scripts to do ongoing maintenance on the tables (creating new partitions; dropping old ones). You need to provide and test these scripts so this is clearly an overhead for the developer. Operations also need to monitor these scripts, so they have operational overhead too.

这篇关于在MYSQL表上添加复合索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！