mysql 多列索引优化

浏览数：37 / 时间：2015年06月12日

Mysql所有的列都可以使用索引，。对相关列使用索引是提高SELECT操作性能的最佳途径。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少256字节。在索引中使用col_name(length)语法，可以创建一个只使用char和archar列的第一个length个字符的索引，按这种方式只索引列的前缀可以索引文件小的多。MyISAm和INNODb存储引擎还支持对blob和text列的索引，但是必须指定索引长度。fulltext索引用于全文搜索不支持局部索引。

多列索引：

Mysql可以创建多列索引，一个索引支持15个列，多列索引对于多条件查询很有帮助。多列索引可以看做是通过连接索引列的值而创建的值的排序的数组。

Mysql按这样的方式使用多列索引：当在WHERE子句为索引的第一个列指定已知的数量时查询很快，即使没有指定其他列的值。

CREATE INDEX suoyin ON Table （col1,col2）

suoyin索引是对于col1和col2的索引，索引可以用于col1,或者是col1,col2在已知范围指定值的查询。

select * FROM test where col1=‘where‘;

select * from test where col1=‘where1‘ AND col2="where2";

select * from test where col1=‘where1‘ AND (col2=‘where1‘ OR col2=‘where3‘);

select * from test where col1=‘where1‘ AND col2 >=‘where1‘ AND col2 < ‘where3‘;

但是多列索引不用与下边的查询：

select * from test WHERE col2 = ‘where‘;

select * from test where col1=‘where1‘ OR col2 = ‘where3‘;

mysql 如何使用索引：

索引用于快速找出在某一个列中有一特定值的行。不使用索引，mysql必须从第一条记录开始然后读完整个表直到找出相关的行，表越大，花费的时间越大。如果表中查询的列有索引，Mysql不必查看所有的数据，快速达到一个位置去搜索数据。大多数mysql索引在B树中存储。。

索引用于下面的操作

快速找出匹配一个WHERE子句的行。

删除时。

执行联接查询从其它表检索时

对具体的有索引的列找出max,min。

对一个可用关键字进行排序分组。

当使用

SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果表有一个多列索引，优化器可以使用最左面的索引前缀来找出行。例如，如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)和(col1,col2,col3)上的搜索进行了索引
如果列不构成索引最左面的前缀，mysql不能使用局部索引。index(col1,col2,col3)

select * from table where col1=val1;

select * from table where col1=val1 AND col2=val2;

select * froom table where col2=val2;不使用索引

select * from table where col2=val2 and col3=val3; 不实用索引

SELECT * FROM Table WHERE col1=val1 OR col2=val2; 不实用索引。

也可以在表达式通过=、>、>=、<、<=或者BETWEEN操作符使用B-树索引进行列比较。如果LIKE的参数是一个不以通配符开头的常量字符串，索引也可以用于LIKE比较。

like ‘partten%‘;使用索引

like ‘parrtn%sf%‘; 使用索引

like ‘%parrtn‘;不实用索引，因为以%开头使用不了索引值的前缀。

like other_col 不使用索引因为不是一个常量值。

如果列是被索引的，那么 col_name is NULL 的搜索也会使用索引。

任何不跨越 WHERE子句中的所有 AND级的索引不用于优化查询，也就是为了能够使用索引，必须在每个AND组中使用所有索引的前缀。