Mysql优化、索引

1，创建MySql索引 
对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加 
索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下 
降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。 
2，复合索引 
比如有一条语句是这样的：select * from users where area=’beijing’ and age=22; 
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效 
率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀 特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。 
3，索引不会包含有NULL值的列 
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。 
4，使用短索引 
对串列进行MySql索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。 
5，排序的索引问题 
mysql查询只使用一个MySql索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。 
6，like语句操作 
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。 
7，不要在列上进行运算 
select * from users where YEAR(adddate) 
8，不使用NOT IN和操作 
NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id>3 or id

mysql索引的不足之处
过多的使用索引将会造成滥用。因此索引也会有它的缺点：
◆虽然mysql索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
◆建立mysql索引会占用磁盘空间的索 引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
索引只是提高效率的一个因素，如果你的 MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
使用mysql索引的注意事项
使用mysql索引时，有以下一 些技巧和注意事项：
◆索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
◆使用短索引
对串列进 行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进 行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
◆索引列排序
MySQL查询只使用一个索引，因此如果 where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
◆like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
◆不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’;
◆不使用NOT IN和<>操作
如何查看MySQL索引
mysql> show index from tblname;
mysql> show keys from tblname;
? Table
表的名称。
? Non_unique
如果MySQL索引不能包括重复词，则为0。如果可以，则为1。
? Key_name
索引的名称。
? Seq_in_index
索引中的列序列号，从1开始。
? Column_name
列名称。
? Collation
列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
? Cardinality
MySQL索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
? Sub_part
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
? Packed
指示关键字如何被压缩。如果没有被压缩，则为NULL。
? Null
如果列含有NULL，则含有YES。如果没有，则该列含有NO。
? Index_type
用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
? Comment

关于建立索引的几个准则：
1、合理的建立索引能够加速数据读取效率，不合理的建立索引反而会拖慢数据库的响应速度。
2、索引越多，更新数据的速度越慢。
3、尽量在采用MyIsam作为引擎的时候使用索引（因为MySQL以BTree存储索引），而不是InnoDB。但MyISAM不支持 Transcation。
4、当你的程序和数据库结构/SQL语句已经优化到无法优化的程度，而程序瓶颈并不能顺利解决，那就是应该考虑使用诸如memcached这样的分 布式缓存系统的时候了。
5、习惯和强迫自己用EXPLAIN来分析你SQL语句的性能。
一个很容易犯的错误：
不要在选择的栏位上放置索引，这是无意义的。应该在条件选择的语句上合理的放置索引，比如where，order by。
例子：
SELECT id,title,content,cat_id FROM article WHERE cat_id = 1;
上面这个语句，你在id/title/content上放置索引是毫无意义的，对这个语句没有任何优化作用。但是如果你在外键cat_id上放置一 个索引，那作用就相当大了。
几个常用ORDER BY语句的MySQL优化：
1、ORDER BY + LIMIT组合的索引优化。如果一个SQL语句形如：
SELECT [column1],[column2],.... FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];
这个SQL语句优化比较简单，在[sort]这个栏位上建立索引即可。
2、WHERE + ORDER BY + LIMIT组合的索引优化，形如：
SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort] LIMIT[offset],[LIMIT];
这个MySQL语句，如果你仍然采用第一个例子中建立索引的方法，虽然可以用到索引，但是效率不高。更高效的方法是建立一个联合索引 (columnX,sort)
3、WHERE + IN + ORDER BY + LIMIT组合的索引优化，形如：
SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX] IN ([value1],[value2],...) ORDER BY[sort] LIMIT [offset],[LIMIT];
这个语句如果你采用第二个例子中建立索引的方法，会得不到预期的效果（仅在[sort]上是using index，WHERE那里是using where;using filesort），理由是这里对应columnX的值对应多个。
这个MySQL语句怎么优化呢？我暂时没有想到什么好的办法，看到网上有便宜提供的办法，那就是将这个语句用UNION分拆，然后建立第二个例子中的索引：
SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX]=[value1] ORDER BY [sort] LIMIT[offset],[LIMIT] 
UNION 
SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX]=[value2] ORDER BY [sort] LIMIT[offset],[LIMIT] 
UNION 
……
但经验证,这个方法根本行不通,效率反而更低,测试时对于大部分应用强制指定使用排序索引效果更好点
4、不要再WHERE和ORDER BY的栏位上应用表达式(函数)，比如：
SELECT * FROM [table] ORDER BY YEAR(date) LIMIT 0,30;
5、WHERE+ORDER BY多个栏位+LIMIT，比如
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
对于这个语句，大家可能是加一个这样的索引(x,y,uid)。但实际上更好的效果是(uid,x,y)。这是由MySQL处理排序的机制造成的。
以上例子你在实际项目中应用的时候，不要忘记在添加索引后，用EXPLAIN看看效果。