所有的MySQL列类型能被索引。在相关的列上的使用索引是改进SELECT操作性能的最好方法。
一、前缀索引
对于CHAR和VARCHAR列,你可以索引列的前缀。这更快并且比索引整个列需要较少的磁盘空间。在CREATE TABLE语句中索引列前缀的语法看起来像这样:
KEY index_name (col_name(length))
下面的例子为name列的头10个字符创建一个索引:
mysql> CREATE TABLE test (
name CHAR(200) NOT NULL,
KEY index_name (name(10)));
对于BLOB和TEXT列,你必须索引列的前缀,你不能索引列的全部。
二、多列索引
MySQL能在多个列上创建索引。一个索引可以由最多15个列组成。(在CHAR和VARCHAR列上,你也可以使用列的前缀作为一个索引的部分)。
一个多重列索引可以认为是包含通过合并(concatenate)索引列值创建的值的一个排序数组。
当你为在一个WHERE子句索引的第一列指定已知的数量时,MySQL以这种方式使用多重列索引使得查询非常快速,即使你不为其他列指定值。
索引原则
1.索引越少越好
原因:主要在修改数据时,每个索引都要进行更新,降低写速度。
2.最窄的字段放在键的左边
3.避免file sort排序,临时表和表扫描.
通过索引优化来实现MySQL的ORDER BY语句优化:
1、ORDER BY的索引优化。如果一个SQL语句形如:
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。
2、WHERE + ORDER BY的索引优化,形如:
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
建立一个联合索引(columnX,sort)来实现order by 优化。
注意:如果columnX对应多个值,如下面语句就无法利用索引来实现order by的优化
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];