(四)2.2 mysql 数据库对象优化之--优化索引

   

   索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(), MIN()和ORDERBY这些命令的时候，性能提高更为明显。

   那该对哪些字段建立索引呢？一般说来，索引应建立在那些将用于JOIN, WHERE判断和ORDER BY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况，例如customerinfo中的 “province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTER TABLE或CREATE INDEX在以后创建索引

1）. 普通索引

   普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。

2）. 唯一索引 

   保证记录中的数据唯一，不会出现重复的记录，提高访问速度。

   普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个”员工个人资料”数据表里可能出现两次或更多次。 如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是 MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建 唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

3）. 主索引

   与唯一索引区别是关键字不同：PRIMARY而不是UNIQUE。

   在前面已经反复多次强调过：必须为主键字段创建一个索引，这个 索引就是所谓的”主索引”。主索引与唯一索引的唯一区别是：前者在 定义时使用的关键字是PRIMARY而不是UNIQUE。

 4）. 外键索引

   索引时会加上外部约束条件

   如果为某个外键字段定义了一个外键约束条件，MySQL就会定义 一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

 5）. 复合索引

   多个字段的复合索引，索引可用不可用和顺序相关。

   索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索 引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果 查询操作只需要用到columnA数据列上的一个索引，就可以使用复合索 引INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引 中排列在前的数据列组合。比如说，INDEX(A, B, C)可以当做A或(A, B) 的索引来使用，但不能当做B、C或(B, C)的索引来使用。

建立索引注意事项

上述索引类型总结

   唯一索引会保证数据的唯一性，不会出现重复的记录，从而提高访问速度。主索引与唯一索引的区别就是关键字不同，一个是PRIMARY另一个是UNIQUE。外键索引不常用，复合索引一定要注意索引字段的顺序。（这里的主索引和外键索引，我理解是在建立表的主键和外键的时候会自动添加上索引）

  1 、建立合适的索引。

   在建立一个表的时候我们要判断是否需要建立索引，所以在建立索引的时候一定要慎重，因为一是索引会增加数据库的存储空间，二是在插入和修改数据的时候会花费较多的时间来维护索引，如果滥用索引，会影响数据库的响应时间。

   2、索引优化

   Sql优化以及索引优化

   如何使用索引

   索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是 提高SELECT 操作性能的最佳途径。

   查询要使用索引最主要的条件是查询条件中需要使用索引关键字， 如果是多列索引，那么只有查询条件使用了多列关键字最左边的前缀时 （前缀索引），才可以使用索引，否则 将不能使用索引。

   下列情况下， Mysql 不会使用已有的索引：      

   1、如果 mysql 估计使用索引比全表扫描更慢，则不使用索引。例 如：如果 key_part 1均匀分布在 1 和 100 之间，下列查询中使用索引就不是很好：         SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90    

   2、如果使用 heap 表并且 where 条件中不用＝索引列，其他 > 、 < 、 >= 、 <= 均不使 用索引（MyISAM和innodb表使用索引）；

   3、使用or分割的条件，如果or前的条件中的列有索引，后面的列中没有索引，那么涉及到的索引都不会使用。       

   4、如果创建复合索引，如果条件中使用的列不是索引列的第一部分；（不是前缀索引）       

   5、如果 like 是以％开始；       

   6、对 where 后边条件为字符串的一定要加引号，字符串如果为数字 mysql 会自动转 为字符串，但是不使用索引。

查看索引使用情况

   如果索引正在工作， 

   Handler_read_key 的值将很高，这个值 代表了一个行被索引值读的次数，很低的值表明增加索引得到 的性能改善不高，因为索引并不经常使 用。

   Handler_read_rnd_next 的值高则意味着查询运行低效，并且 应该建立索引补救。这个值的含义是在数据文件中读下一行的 请求数。如果你正进行大量的表扫描， 该值较高。通常说明表索引不正确或写入的查询没有利用索引。 

 

       语法：mysql> show status like 'Handler_read%';  

   1). 应尽量避免在 where 子句中对字段进行 null 值判断否则将导致引擎放弃使用索引而进行全表扫描，如：

        select id from t where num is null

   NULL对于大多数数据库都需要特殊处理，MySQL也不例外，它需要更多的代码，更多的检查和特殊的索引逻辑，有些开发人员完全没有意识到，创建表 时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值， 如0，-1作为默认值。

   不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列 就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。 任何在 where子句中使用is null或is not null的语句优化器是不允许使用索引的。

   此例可以在num上设置默认值0，确保表中num列没有null值，然后这样 查询：

         select id  from t where num=0

   

   2). 应尽量避免在 where 子句中使用!=或<>操作符

   否则将引擎放弃使用索引而进行全表扫描。MySQL只有对以下操作符才使用索引：<，<=，=，>，>=， BETWEEN，IN，以及某些时候的LIKE。  

   可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或 者_）开头的情形。

   例如: SELECT id FROM  t WHERE col LIKE 'Mich%'; #  这个查询将使用索 引，      

    SELECT id FROM  t WHERE col  LIKE '%ike';   #这个查询不会使用索 引。

 

   3). 应尽量避免在 where 子句中使用 or 来连接条件

        否则将导致引擎放弃使用索引而进行全表扫描，如：

        select id from t where num=10 or num=20

        可以 使用UNION合并查询： select id from t where num=10 union all select id from t where num=20

    在某些情况下，or条件可以避免全表扫描的。

                1 .where 语句里面如果带有or条件, myisam表能用到索引， innodb不行。                

               2 .必须所有的or条件都必须是独立索引

 

 

   4) .in 和 not in 也要慎用，否则会导致全表扫描，如：

   select id from t where num in(1,2,3)

   对于连续的数值，能用 between 就不要用 in 了： Select id from t where num between 1 and 3

 

   5).下面的查询也将导致全表扫描：

   select id from t where name like '%abc%' 或者

   select id from t where name like '%abc' 或者

   若要提高效率，可以考虑全文检索。

   而select id from t where name like 'abc%' 才用到索引

   6). 如果在 where 子句中使用参数，也会导致全表扫描。

   因为SQL只有在运行时才会解析局部变量，但优化程序不能 将访问计划的选择推 迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无 法作为索引选择的输入项。如下面语句将进行全表扫描：

       select id from t where num=@num

   可以改为强制查询使用索引： select id from t with(index(索引名)) where num=@num

   7). 应尽量避免在 where 子句中对字段进行表达式操作，不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，

   

   这将导致引擎放弃使用索引而进行全表扫描。如：

   select id from t where num/2=100 应改为:  select id from t where num=100*2

   8). 应尽量避免在where子句中对字段进行函数操作，将导致引擎放弃使用索引而进行全表扫描。如：

      select id from t where substring(name,1,3)='abc'     --name

      select id from t where datediff(day,createdate,'2005-11-30')=0   --‘2005-11-30’  

      生成的id 应改为:

      select id from t where name like 'abc%'

      select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

  9). 索引字段不是复合索引的前缀索引

   例如 在使用索引字段作为条件时，如果该索引是复合索引，那么必须 使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该 索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

   

   

   10). 很多时候用 exists 代替 in 是一个好的选择：

       select num from a where num in(select num from b)

       用下面的语句替换：

       select num from a where exists(select 1 from b where num=a.num)  

   11). 并不是所有索引对查询都有效

   SQL是根据表中数据来进行查询优化的，当索引列有大 量数据重复时，SQL查询可能不会去利用索引，如一表中有 字段sex，male、female几乎各一半，那么即使在sex上建 了索引也对查询效率起不了作用。

   12). 索引并不是越多越好

   索引固然可以提高相应的 select 的效率，但同时也降 低了 insert 及 update 的效率，因为 insert 或 update 时 有可能会重建索引，所以怎样建索引需要慎重考虑，视具体 情况而定。一个表的索引数最好不要超过6个，若太多则应 考虑一些不常使用到的列上建的索引是否有必要。

   

   

  13).应尽可能的避免更新 clustered 索引(聚集索引)数据列

   因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一 旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。 若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应 将该索引建为 clustered 索引。  

   14).尽量使用数字型字段

   若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连 接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐 个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

   

   

   15).尽可能的使用 varchar/nvarchar 代替 char/nchar 

   因为首先变长字段存储空间小，可以节省存储空间，其次对于查询 来说，在一个相对较小的字段内搜索效率显然要高些。  

   

   16) 在某些情况中， MySQL可以使用一个索引来满足 ORDER BY子句，而不需要 额外的排序。

   where条件和order by使用相同的索引，并且order by 的顺序 和索引顺序相 同，并且order by的字段都是升序或者都是降序。

   例如：下列sql 可以使用索引。    

   SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;    

   SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;    

   SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;    

但是以下情况不使用索引：      

   SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC ； --order by 的字段混合 ASC 和 DESC    

   SELECT * FROM t1 WHERE key2=constant ORDER BY key1 ；-- 用于 查询行的关键字与 ORDER BY 中所使用的不相同      

   SELECT * FROM t1 ORDER BY key1, key2 ；-- 对不同的关键字使用 ORDER BY  

     

   

   

索引字段用于排序

   

   MySQL的弱点之一是它的排序。虽然MySQL可以在1秒中查询大约15,000 条记录，但由于MySQL在查询时最多只能使用一个索引。因此，如果WHERE 条件已经占用了索引，那么在排序中就不使用索引了，这将大大降低查询的速度。 

   我们可以看看如下的SQL语句:      

   SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;      

   在以上的SQL的WHERE子句中已经使用了NAME字段上的索引，因此，在 对SALE_DATE进行排序时将不再使用索引。为了解决这个问题，我们可以对 SALES表建立复合索引:      

   ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME,SALE_DATE)      

   这样再使用上述的SELECT语句进行查询时速度就会大副提升。但要注意，在使用这个方法时，要确保WHERE子句中没有排序字段，在上例中就是不能用 SALE_DATE进行查询，否则虽然排序快了，但是SALE_DATE字段上没有单独的 索引，因此查询又会慢下来。   

部分来自网络...