mysql字段简索引_MySQL系列之索引

一.什么情况下应不建或少建索引

1.表记录太少

如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，

一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的

情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。

2.经常插入、删除、修改的表

对一些经常处理的业务表应在查询允许的情况下尽量减少索引，如 zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等业务表。

3.数据重复且分布平均的表字段

假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为 50%，那么对这种表A字段建索引

一般不会提高数据库的查询速度。

1．选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。

为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2．为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，

可以有效地避免排序操作。

3．为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4．限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5．尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。

例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

6．尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7．删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，

将它们删除，从而减少索引对更新操作的影响。

8 . 最左前缀匹配原则，非常重要的原则。

mysql会一直向右匹配直到遇到范围查询(>、 3 and d = 4

如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

9 .=和in可以乱序。

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

10 . 尽量选择区分度高的列作为索引。

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，

唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就 是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，

这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条 记录

11 .索引列不能参与计算，保持列“干净”。

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，

但进行检索时，需要把所有元素都应用函数才能比较，显然成本 太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

12 .尽量的扩展索引，不要新建索引。

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。

我们要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。

二.MySQL中什么情况下索引会失效

1. is null is not null值判断；

2. != , <> 操作符；

3.NOT IN和操作都不会使用索引将进行全表扫描。

4. 在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算；select id from t where num/2=100 ；

5、order by 和 limit 结合使用，如果where 字段，order by字段都是索引，那么有limit索引会使用order by字段所在的索引。

没有limit会使用where 条件的索引。遇到此类状况可以考虑用子查询将order by 和 limit 分开。这种情况主要发生在你用了多个索引，那么你需要注意了。

它可能不执行你希望的走索引。(我觉得mysql会自动计算索引)

6、DATE_FORMAT()格式化时间，格式化后的时间再去比较，可能会导致索引失效。

7、子查询中order by的索引会失效，同时可能导致子查询中的where条件索引都不能用。

8、字符集的使用导致不走索引，有时你会发现用一个SQL 条件值不同可能会有天的差别(我之前遇到的 两个不同的ID号，一个查询80s,一个不到1s)

9、在where查询中使用了or关键字, myisam表能用到索引， innodb不行;(用UNION替换OR,可以使用索引)

10、全表扫描快于索引扫描(数据量小时)

11.SELECT * FROM `table` WHERE `reverse_email` LIKE REVERSE('%.com'); -- 走索引

注：以上如果你使用REVERSE(email) = REVERSE('%.com')，一样得不到你想要的结果，因为你在索引列email列上使用了函数，MySQL不会使用该列索引

12.同样的，索引列上使用正则表达式也不会走索引。

三.对千万级MySQL数据库建立索引的事项及提高性能的手段

一、注意事项：

首先，应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据，在建立索引的时候势必也会占用大量表空间。

因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。

其次，在对建立索引的时候要对表进行加锁，因此应当注意操作在业务空闲的时候进行。

二、性能调整方面：

首当其冲的考虑因素便是磁盘I/O。物理上，应当尽量把索引与数据分散到不同的磁盘上(不考虑阵列的情况)。逻辑上，数据表空间与索引表空间分开。

这是在建索引时应当遵守的基本准则。

其次，我们知道，在建立索引的时候要对表进行全表的扫描工作，因此，应当考虑调大初始化参数 db_file_multiblock_read_count的值。一般设置为32或更大。

再次，建立索引除了要进行全表扫描外同时还要对数据进行大量的排序操作，因此，应当调整排序区的大小。

9i之前，可以在session级别上加大sort_area_size的大小，比如设置为100m或者更大。

9i以后，如果初始化参数workarea_size_policy的值为TRUE，则排序区从pga_aggregate_target里自动分配获得。

最后，建立索引的时候，可以加上nologging选项。以减少在建立索引过程中产生的大量 redo，从而提高执行的速度。