MYSQL建立索引{资料}

1.建立索引的时机：若表中的某字段出现在select、过滤、排序条件中，为该字段建立索引是值得的。
2.对于like '%xxx'的模糊查询，普通的索引是无法满足的，需要建立全文索引。
3.对于有多个条件的，比如: "...where a=xxx and b=yyy","...where a=xxx order by b","...where a=xxx group by b"。需要使用组合索引。但是组合索引只能在SQL语句中满足"最左前缀"的条件下使用。且组合索引有一些副作用，如索引尺寸可能比数据本身大，因为组合索引的组合条目多。所以在实际应用中，要量身定做，使用慢查询分析工具分析。
4.开启索引缓存，直接在内存中查找索引，不用再磁盘中。
5.建立索引是有代价的，当update、delete语句执行时，会使得索引更新，将耗掉更多的时间。可以使用mysqlreport报告，了解select、update、delete、insert、replace各语句所占的百分比。

【6索引尽量做３个以下列】

 

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

 

concat(first_name, last_name)　--相当于相加

 

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

 

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

 

http://www.cnblogs.com/kingfly/archive/2013/02/20/2918561.html

 

在数据库中，对性能影响最大的几个策略包括数据库的锁策略、缓存策略、索引策略、存储策略、执行计划优化策略。
索引策略决定数据库快速定位数据的效率，存储策略决定数据持久化的效率。
MySQL中两大主要存储引擎MyISAM和InnoDB采用了不同的索引和存储策略，本文将分析它们的异同和性能。

MySQL主要提供2种方式的索引：B-Tree（包括B+Tree）索引，Hash索引。
B树索引具有范围查找和前缀查找的能力，对于N节点的B树，检索一条记录的复杂度为O(LogN)。
哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。
显然，如果值的差异性大，并且以等于查找为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。

这是一个B+树的结构，InnoDB的索引都采取了这种形式，它在B-树的基础上为每个叶子节点加了一个指针指向下一个叶子节点，这样可以快速的进行范围查找。

对于InnoDB，使用了一种改进的B+树索引，称为聚集索引（Clustered Index），它的不同之处在于索引上不仅有索引值的信息，还有整个索引值所在行的信息，免去了一次通过索引值上的位置去取整行的操作。

 

定长的行比不定长的行效率高！把定长数据和不定长数据分开存储，很多时候可以提高效率。

InnoDB引擎按主键顺序插入记录是非常必要的，否则性能将会面临很大风险。

http://blog.csdn.net/habout632/article/details/7611111

 

 drop index personname on person 

create index personname on person (person(10))  

 

3. 使用短索引。如果对字符串列进行索引，应该指定前缀长度，只要有可能就应该这样做。例如：如果有一个CHAR(200)的列，如果在前10个或20个字符内，多数值是唯一的。那么就不要对整个列进行索引。对前10个或者20个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘IO较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此,MySQL也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。  

 

4.利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引起几个索引的作用，因为可利用索引中最左的列集来匹配行。这样的列集称最左前缀。

 

InnoDB表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效减少索引的磁盘占用，提高索引的缓存效果。

 

唯一索引。        

它与前面的"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

.主键索引        

它是一种特殊的唯一索引，不允许有空值。一个表只能有一个主键。

 

建立这样的组合索引，其实是相当于分别建立了        

vc_Name,vc_City,i_Age        

vc_Name,vc_City        

vc_Name     

这样的三个组合索引！为什么没有vc_City,i_Age等这样的组合索引呢？这是因为mysql组合索引"最左前缀"的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个T-SQL会用到：     

SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="郑州"    

SELECT * FROM myIndex WHREE vc_Name="erquan"

而下面几个则不会用到：     

SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="郑州"    

SELECT * FROM myIndex WHREE vc_City="郑州"

 

只有某些时候的LIKE才需建立索引？是的。因为在以通配符 % 和 _ 开头作查询时，MySQL不会使用索引，如     

SELECT * FROM myIndex WHERE vc_Name like'erquan%'

会使用索引，而     

SELECT * FROM myIndex WHEREt vc_Name like'%erquan'

就不会使用索引了。