我有一个Mysql 5.6表,其中有7000万行,但几周后它将增长到1亿多行或更多。
我有一台专用机器,有一个不起眼的500GB磁盘和4GB RAM,innodb_buffer_pool_size
设置为2GB。
数据库使用99%进行选择,使用1%进行插入(每月一次)。
最重要的列是descripcion_detallada_producto
varchar(300)
,90%的时间都是在这里进行选择。
我的桌子是:
CREATE TABLE `t1` (
`N_orden` bigint(20) NOT NULL DEFAULT '0',
`Fecha` varchar(15) COLLATE latin1_spanish_ci DEFAULT NULL,
`Ncm` int(11) NOT NULL,
`Origen` int(11) NOT NULL,
`Adquisicion` int(11) NOT NULL,
`Medida_Estadistica` int(11) NOT NULL,
`Unidad_Comercializacion` varchar(30) COLLATE latin1_spanish_ci DEFAULT NULL,
`Descripcion_Detallada_Producto` varchar(300) COLLATE latin1_spanish_ci DEFAULT NULL,
`Cantidad_Estadistica` double DEFAULT NULL,
`Peso_Liquido_Kg` double DEFAULT NULL,
`Valor_Fob` double DEFAULT NULL,
`Valor_Frete` double DEFAULT NULL,
`Valor_Seguro` double DEFAULT NULL,
`Valor_Unidad` double DEFAULT NULL,
`Cantidad` double DEFAULT NULL,
`Valor_Total` double DEFAULT NULL,
PRIMARY KEY (`N_orden`),
KEY `Ncm` (`Ncm`),
KEY `Origen` (`Origen`),
KEY `Adquisicion` (`Adquisicion`),
KEY `Medida_Estadistica` (`Medida_Estadistica`),
KEY `Descripcion_Detallada_Producto` (`Descripcion_Detallada_Producto`),
CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`Ncm`) REFERENCES `ncm` (`Ncm`),
CONSTRAINT `t1_ibfk_2` FOREIGN KEY (`Origen`) REFERENCES `paises` (`Codigo_Pais`),
CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`Adquisicion`) REFERENCES `paises` (`Codigo_Pais`),
CONSTRAINT `t1_ibfk_4` FOREIGN KEY (`Medida_Estadistica`) REFERENCES `medida_estadistica` (`Codigo_Medida_Estadistica`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
我的问题是:今天SELECT query using LIKE '%whatever%'
通常需要5到7分钟,有时甚至更长。根据我的理解,varchar索引只在使用"anything%"时使用,但我需要有可能使用左右通配符搜索字符串,而无需每次搜索等待约7分钟。我该怎么做?
解决问题的正确方法是查看针对表运行的所有查询及其相对频率。你只给了我们一部分。你甚至没有说它与哪个字段有关。由于你说"最重要的列是description_detalada_producto varchar(300),它是90%的时候选择的目标",我认为你只需要优化
WHERE descripcion_detallada_producto LIKE '%wathever%'
正如Vatev已经说过的,您可能应该使用全文搜索——它在语义(和语法)上与LIKE谓词不同。此外,您应该将description_detalada_producto属性拆分为它自己的关系,以减少从磁盘向内存中读取大量行的缓冲区刷新效果。
如果要搜索文本列中任何位置的完整单词,则应考虑使用全文索引,这显然与通配符搜索不同。如果你不确定如何搜索全文索引,你可以随时获得帮助。
按照以下方式进行搜索不会使用任何索引。相反,它会扫描你的表数据的所有行,你会受到磁盘读取(以及任何相关的磁盘碎片,这通常不是问题,因为我们通常不扫描表):
SELECT * FROM t1
WHERE Descripcion_Detallada_Producto LIKE `%whatever%'
以下查询将扫描Descripcion_Detallada_Producto
上的索引,该索引将充当"覆盖"索引(请注意,select中的列会有所不同):
SELECT N_orden FROM t1
WHERE Descripcion_Detallada_Producto LIKE `%whatever%'
扫描索引而不是实际的表数据的优点是,在扫描时读取的数据量最小化,理想情况下,使用大的innodb_buffer_pool_size
,该索引将在内存中,这将避免磁盘寻道。
一旦获得N_orden
值,就可以从表数据中检索各个记录。
附加信息
考虑减小列的大小(对于N_orden
,bigint为unsigned int)并减小Descripcion_Detallada_Producto
的大小。尽管VARCHAR只使用了表数据中的实际字节(加上长度),但每个索引条目实际上都使用了最大值,因此即使减少索引中的VARCHAR列大小也会提高索引扫描速度。
此外,如果您有类别,请将搜索限制为选定的类别,并在类别+描述上创建多列索引。通过将搜索限制在特定类别,以下内容只需扫描类别和描述的多列索引的一部分:
SELECT N_orden FROM t1
WHERE Category = 1
AND Descripcion_Detallada_Producto LIKE `%whatever%'
最后,考虑删除通配符前缀。让用户至少键入型号的开头。