mysql 索引学习的一些总结
一、使用索引的注意事项
1、索引不会包含有NULL值的列 (数据库设计时不要让字段的默认值为NULL )。
2、使用短索引:对串列进行索引,如果可能应该指定一个前缀长度,前10个或20个字符内 ;使用短索引可以大量节省磁盘空间,也有可能会使查询更快;较小的索引涉及的磁盘I/O较少,较短的值比较起来更快;对于较短的键值,索引高速缓存中块能容纳更多的键值,因此MySql也可以内存中容纳更多的值。这增加了为了读到行而不用读取索引中较多块的可能性。
3、 索引列排序 : MySQL查询只使用一个索引因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
4、利用最左前缀:在创建一个n列索引时,实际是创建了MySql可利用的n个索引,多列索引可起几个索引的作用,因为可利用索引最左边的列集来匹配。
假设现在我们有一个firstname、lastname、age列上的多列索引,我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时,MySQL将使用fname_lname_age索引:
firstname,lastname,age
firstname,lastname
firstname
5、不要过度索引:过度索引会占用过多的磁盘空间,并降低写操作性能。在修改表的内容时,必须对索引进行更新,有时有可能需要进行重构,因此,过多的索引,更新所花的时间越长。索引太多也可能会导致MySql找不到所要使用的最好索引。
6、考虑在列上进行比较的类型:索引可用于“<","<=","=",">",">="">"和BETWEEN运算,在模试里有一个直接量前缀时,也可用行like运算。如果只将某列用于其他运算时(如STRCMP()),对其进行索引没有价值。
7、 like语句操作 : like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
不要在列上进行运算 select * from users where YEAR(adddate)<2007; 将在每个行上进行运算,这将导致索引失效而进行全表扫描 可以改写为: select * from users where adddate<‘2007-01-01’。
不使用NOT IN和<>操作。
8、 使你的数据尽可能小:
尽可能地使用最有效(最小)的数据类型。MySQL有很多节省磁盘空间和内存的专业化类型。
尽可能使用较小的整数类型使表更小。例如,MEDIUMINT经常比INT好一些,因为MEDIUMINT列使用的空间要少25%。
如果可能,声明列为NOT NULL。它使任何事情更快而且每列可以节省一位。注意如果在应用程序中确实需要NULL,应该毫无疑问使用它,只是避免 默认地在所有列上有它。
对于MyISAM表,如果没有任何变长列(VARCHAR、TEXT或BLOB列),使用固定尺寸的记录格式。这比较快但是不幸地可能会浪费一些空间。即使你已经用CREATE选项让 VARCHAR列ROW_FORMAT=fixed,也可以提示想使用固定长度的行。