Mysql索引相关知识分享(公司内部)_MySQL

3.1索引可以改善查询，但会减慢更新，索引不是越多越好，最好不超过字段数的20%（在数据增、删、改比较频繁的表中，索引数量不应超过5个。

3.2离散程度越小，不适合加索引，例如：不要给性别建索引

test.status取值范围：0-9，在status列建索引

mysql> select sql_no_cache * from test where status = 3 order by id limit 80000, 2;

+--------+----------------+---------------------+------------+--------+

| id | time1 | time2 | time3 | status |

+--------+----------------+---------------------+------------+--------+

| 795783 | 20110825150959 | 2011-08-25 15:09:00 | 1314256140 | 3 |

| 795789 | 20120829052359 | 2012-08-29 05:23:00 | 1346188980 | 3 |

+--------+----------------+---------------------+------------+--------+

2 rows in set (1.26 sec)

删除status索引后

mysql> select sql_no_cache * from test where status = 3 order by id limit 80000, 2;

+--------+----------------+---------------------+------------+--------+

| id | time1 | time2 | time3 | status |

+--------+----------------+---------------------+------------+--------+

| 795783 | 20110825150959 | 2011-08-25 15:09:00 | 1314256140 | 3 |

| 795789 | 20120829052359 | 2012-08-29 05:23:00 | 1346188980 | 3 |

+--------+----------------+---------------------+------------+--------+

2 rows in set (0.37 sec)

3.3.避免在空值(Null)很多的字段上建立索引，大量空值会降低索引效率

3.4.避免在数据值分布不均的字段上建立索引，个别数据值占总数据量的百分率明显比其它数据值占总数据量的百分率高，表明该字段数据值分布不均，容易引起数据库选择错误索引，生成错误的查询执行计划。

3.5.在数据量较少且访问频率不高的情况下，如只有一百行记录以下的表不需要建立索引。因为在数据量少的情况下，使用全表扫描效果比走索引更好。

3.6.字符字段必须建前缀索引

单字母区分度：26

4个字母区分度：26*26*26*26=456976

6个字母区分度：26*26*26*26*26*26=308915776

CREATE TABLE `test1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`a` char(20) NOT NULL DEFAULT '',

`b` varchar(14) NOT NULL DEFAULT '00000000000000',

`c` varchar(14) DEFAULT '00000000000000',

PRIMARY KEY (`id`),

KEY `a` (`a`(6))

) ENGINE=MyISAM AUTO_INCREMENT=12534199 DEFAULT CHARSET=gbk;

mysql> select sql_no_cache count(*) from test1;

+----------+

| count(*) |

+----------+

| 12534198 |

+----------+

1 row in set (0.00 sec)

mysql> select sql_no_cache count(*) from test1 where a = 'tR6cDjx0frXx45yURG1m';

+----------+

| count(*) |

+----------+

| 1 |

+----------+

1 row in set (0.00 sec)

3.7.不在索引列做运算，尽量不用外键（InnoDB）

3.8.唯一索引：在建立索引的字段所有数值都具有唯一性特点的情况下，建立唯一索引(unique index)代替普通索引，唯一索引(unique index)查询效率比普通索引查询效率更高，可以大幅提升查询速度。

4.组合索引

4.1.避免建立两个或以上功能相同索引。例如已经建立字段A、B两个字段的索引，应该避免再建立字段A的单独索引。两个索引之间，对相同的查询都会起到相同的作用。建立两个功能相同的索引，反而会容易引起数据库产生错误的查询计划，降低查询效率。

4.2.选择正确的组合索引字段顺序，最常用的查询字段和选择性、区分度较高的字段，应该作为索引的前导字段使用。

假设存在组合索引it1c1c2(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。

4.3.合适的字段数，组合索引的字段数不适宜较多，较多的组合索引字段数会降低索引查询效率，组合索引字段数应不多于3个，如业务特点需要建立多字段的组合主键例外。

关于一个B-Tree索引的例子：

假设有如下一个表：

CREATE TABLE People (

last_name varchar(50) not null,

first_name varchar(50) not null,

dob date not null,

gender enum('m', 'f') not null,

key(last_name, first_name, dob)

);

其索引包含表中每一行的last_name、first_name和dob列。其结构大致如下：

索引存储的值按索引列中的顺序排列。可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询，当然，如果想使用索引，你必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。

(1)匹配全值(Match the full value)：对索引中的所有列都指定具体的值。例如，上图中索引可以帮助你查找出生于1960-01-01的Cuba Allen。

(2)匹配最左前缀(Match a leftmost prefix)：你可以利用索引查找last name为Allen的人，仅仅使用索引中的第1列。

(3)匹配列前缀(Match a column prefix)：例如，你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列。

(4)匹配值的范围查询(Match a range of values)：可以利用索引查找last name在Allen和Barrymore之间的人，仅仅使用索引中第1列。

(5)匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part)：可以利用索引查找last name为Allen，而first name以字母K开始的人。

(6)仅对索引进行查询(Index-only queries)：如果查询的列都位于索引中，则不需要读取元组的值。

由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行ORDER BY。当然，使用B-tree索引有以下一些限制：

(1)查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。

(2)不能跳过某一索引列。例如，你不能利用索引查找last name为Smith且出生于某一天的人。

(3)存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，则该查询只会使用索引中的前两列，因为LIKE是范围查询。

另一个例子：

CREATE TABLE `friends` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`uid` bigint(20) unsigned NOT NULL DEFAULT '0',

`fuid` bigint(20) unsigned NOT NULL DEFAULT '0',

`fname` varchar(50) NOT NULL DEFAULT '',

`fpicture` varchar(150) NOT NULL DEFAULT '',

`fsex` tinyint(1) NOT NULL DEFAULT '0',

`status` tinyint(1) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `uid_fuid` (`uid`,`fuid`)

) ENGINE=MyISAM DEFAULT CHARSET=gbk;

下一个

5.覆盖索引(Covering Indexes)

如果索引包含满足查询的所有数据，就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点：

(1)索引项通常比记录要小，所以MySQL访问更少的数据；

(2)索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；

(3)大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。

(4)覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

注意：覆盖索引不能是任何索引，只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。

对于索引覆盖查询(index-covered query)，使用EXPLAIN时，可以在Extra一列中看到“Using index”

CREATE TABLE `friends` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`uid` bigint(20) unsigned NOT NULL DEFAULT '0',

`fuid` bigint(20) unsigned NOT NULL DEFAULT '0',

`fname` varchar(50) NOT NULL DEFAULT '',

`fpicture` varchar(150) NOT NULL DEFAULT '',

`fsex` tinyint(1) NOT NULL DEFAULT '0',

`status` tinyint(1) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `uid_fuid` (`uid`,`fuid`)

) ENGINE=MyISAM DEFAULT CHARSET=gbk;

6.排序

MySQL中，有两种方式生成有序结果集：

a. filesort 糟糕

b. Index排序 好

什么时候使用Index排序？

当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序。其它情况都会使用filesort。

什么时候使用filesort？

当MySQL不能使用Index排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。

当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；

否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”。

通过索引优化来实现MySQL的ORDER BY语句优化例子：

1、ORDER BY的索引优化。如果一个SQL语句形如：

SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];

在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。

2、WHERE + ORDER BY的索引优化，形如：

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];

建立一个联合索引(columnX,sort)来实现order by 优化。

注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];

3、WHERE+ 多个字段ORDER BY

SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。

MySQL Order By不能使用索引来优化排序的情况：

1、对不同的索引键做 ORDER BY ：(key1,key2分别建立索引)

SELECT * FROM t1 ORDER BY key1, key2;

2、用于where语句的索引和ORDER BY 的不是同一个：(key1,key2分别建立索引)

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

3、同时使用了 ASC 和 DESC：(key_part1,key_part2建立联合索引)，通过where语句将order by中索引列转为常量，则除外

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

4、如果在WHERE或ORDER BY的栏位上应用表达式(函数)时，则无法利用索引来实现order by的优化

SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;

5、检查的行数过多，且没有使用覆盖索引

6、where语句中使用了条件查询

7.关于group by或distinct

7.1.尽量只对存在索引的字段进行group by或distinct。当group by 不能使用index 时mysql有两种处理方法：临时表和filesort。

7.2.在group by 语句中mysql会自动order，如果不需要可使用order by null来禁止自动的order。

8.关于索引失效

8.1.避免对索引字段计算

8.2.避免使用索引列值是否可为空的索引，如果索引列值可以是空值，在SQL语句中那些要返回NULL值的操作，将不会用到索引。

8.3.相同的索引列不能互相比较，这将会启用全表扫描，如tab1上存在索引idx_col1_col2(col1,col2)，其中col1和col2都是int型。则查询语句SELECT * FROM tab1 WHERE col1>col2;是不会使用索引的。

8.4.避免使用存在潜在的数据类型转换的索引。潜在的数据转换，查询条件中是指由于等式两端的数据类型不一致。例如索引字段使用的是数字类型，而条件等式的另一端数据类型是字符类型，数据库将会对其中一端进行数据类型转换，数据类型的转换会让索引的作用失效，令数据库选择其他的较为低效率的访问路径。

8.5.使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

a)尽量避免负向查询：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE，避免%前模糊查询

b)WHERE条件中的范围查询（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引。

8.6.使用索引列作为条件进行范围查询时，应该避免较大范围取值。