mysql 索引

索引概述
索引类型
索引存储结构
索引原则
如何使用索引
索引优化

索引概述

所有MySQL列类型可以被索引。对相关列使用索引是提高SELECT操作性能的最佳途径。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节。大多数存储引擎有更高的限制。

在MySQL 5.1中,对于MyISAMInnoDB表,前缀可以达到1000字节长。请注意前缀的限制应以字节为单位进行测量,而CREATE TABLE语句中的前缀长度解释为字符数。当为使用多字节字符集的列指定前缀长度时一定要加以考虑。

还可以创建FULLTEXT索引。该索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引,并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行,不支持局部(前缀)索引。也可以为空间列类型创建索引。只有MyISAM存储引擎支持空间类型。空间索引使用R-树。默认情况MEMORY(HEAP)存储引擎使用hash索引,但也支持B-树索引

索引类型

  1. 普通索引: 创建到任何数据类型中
  2. 唯一索引:限制索引值必须唯一
  3. 全文索引:仅限创建到char\varchar\text类型字段,提高文本查询速度。MyISAM引擎支持
  4. 单列索引:给表中单个字段创建索引
  5. 多列索引:给多个字段创建索引
  6. 空间索引:使用spatial参数创建,提供系统获取控件数据的效率
  7. 主键索引

普通索引

这是这基本的索引,它没有任何限制,MyISAM 中默认的 B-tree 类型的索引。
应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。
只要有可能,就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。

# 直接创建索引
CREATE INDEX index_name ON table(column(length))
# 修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
# 创建表的时候同时创建索引
CREATE TABLE table_name ( \*,INDEX index_name title(length))
# 删除索引
DROP INDEX index_name ON table

唯一索引

与普通索引一致,不同的是索引值必须唯一,允许有空值。
这么做的好处:一是简化了MySQL对这个索引的管理工作,这个索引也因此而变得更有效率;二是MySQL会在有新记录插入数据表时,自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了;如果是,MySQL将拒绝插入那条新记录。
也就是说,唯一索引可以保证数据记录的唯一性。事实上,在许多场合,人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复。

# 直接创建索引
CREATE UNIQUE INDEX index_name ON table(column(length))
# 修改表结构的方式添加索引
ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (column(length))
# 创建表的时候同时创建索引
CREATE TABLE table_name ( \*,UNIQUE index_name title(length))

主键索引

主键索引与唯一索引的唯一区别是:前者在定义时使用的关键字是PRIMARY而不是UNIQUE

单列索引和多列索引(复合索引)

单列索引就是常用的一个列字段的索引,常见的索引。
多列索引就是含有多个列字段的索引,对于多列索引:Mysql会从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。
例如索引是key index(a,b,c),可以支持a| a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效。
修改表 alter table table_name add index_name(col1,col2,col3)
ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))
建立这样的组合索引,其实是相当于分别建立了下面两组组合索引:
–title,time
–title

全文索引

FULLTEXT 索引仅 MyISAM 引擎支持
他们可以从CHARVARCHARTEXT列中作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLECREATE INDEX被添加
不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法
注解:InnoDB引擎在MySQL5.6及以上版本才支持全文索引。

# 直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)
# 修改表结构的方式添加索引
ALTER TABLE article ADD FULLTEXT index_content(content)
# 创建表的时候同时创建索引
CREATE TABLE table_name ( \*,FULLTEXT (content))

索引存储结构

  • b-tree 当人们在谈论索引的时候,如果没有指定类型,那么多半说的是B-Tree索引,它使用B-Tree数据结构来存储数据。(InnoDB使用的是B+Tree)。B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针,存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点,这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值,要么该记录不存在。注:关于B-树和B+树的区别 mysql_index_01.png

  • hash 哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希吗(hash code),哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。 在MySQL中,只有Memory引擎显式支持哈希索引InnoDB引擎有一个特殊的功能叫做“自适应哈希”,当InnodeDB注意到某些索引值被使用的非常频繁时,它会在内存中基于B-Tree索引之上在创建一个哈希索引,这样就让B-Tree索引也具有哈希索引的一些优点,比如快速的哈希查找。

  • 空间数据索引(R-Tree) MyISAM表支持空间索引,可以用做地理数据存储。和B-Tree索引不同,这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时,可以有效地使用任意维度来组合查询。必须使用mysql的GIS相关函数如MBRCONTAINS()等来维护数据。mysql的GIS支持并不完善,所以大部分人都不会使用这个特性。开源数据库中对GIS的解决方案做的比较好的是PostgreSQLpostGIS

  • 全文索引(FULL TEXT INDEX) 全文索引是一种特殊类型的索引,他查找的是文本中的关键词,而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样。他有许多需要注意的细节,如停用词、词干和复数、布尔搜索等。全文索引更类似与搜索引擎做的事情,而不是简单的where条件匹配。 在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突,全文索引适用于MATCH AGAINST操作,而不是普通的where条件操作。

索引原则

  1. 搜索的索引列,不一定是所要选择的列
    换句话说,最适合索引的列是出现在WHERE子句中的列,或连接子句中指定的列,而不是出现在SELECT关键字后的选择列表中的列。

  2. 使用惟一索引
    考虑某列中值的分布。对于惟一值的列,索引的效果最好,而具有多个重复值的列,其索引效果最差。例如,存放年龄的列具有不同值,很容易区分 各行。而用来记录性别的列,只含有“M”和“F”,则对此列进行索引没有多大用处(不管搜索哪个值,都会得出大约一半的行)。

  3. 使用短索引 如果对串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,如果有一个CHAR(200)列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前10 个或20个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘I/O较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL也可以在内存中容纳更多的值。这增加 了找到行而不用读取索引中较多块的可能性。(当然,应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的,因为这个索引中不会有许多不 同的值。)

  4. 利用最左前缀 在创建一个n 列的索引时,实际是创建了MySQL可利用的n 个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同,索引一个列的前缀是利用该的前n 个字符作为索引值。)

  5. 不要过度索引
    不要以为索引“越多越好”,什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能,这一点我们前面已经介绍过。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。如果有一个索引很少利用或从不使用,那么会不必要地减缓表的修改速度。此外,MySQL在生成一个执行计划时,要考虑各个索引,这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多,也可能会使 MySQL选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引,应该考虑所要增加的索引是否是现有多列索引的最左 索引。如果是,则就不要费力去增加这个索引了,因为已经有了。

  6. 考虑在列上进行的比较类型 索引可用于“ <”、“ < = ”、“ = ”、“ > =”、“ > ”和BETWEEN 运算。在模式具有一个直接量前缀时,索引也用于LIKE 运算。如果只将某个列用于其他类型的运算时(如STRCMP( )),对其进行索引没有价值。

如何使用索引

索引用于快速找出在某个列中有一特定值的。不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大,花费的时间越多。如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据。如果一个表有1000行,这比顺序读取至少快100倍。注意如果你需要访问大部分行,顺序读取要快得多,因为此时我们避免磁盘搜索。

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树,并且MEMORY表还支持hash索引

存储引擎数据结构
MyISAMb+tree
INNODBb+tree
memory/heaphash,btree

索引优化

  1. 应尽量避免在where子句中对字段进行null值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null
  2. 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num=0
  3. 应尽量避免在where子句中使用!=<>操作符,否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行
  4. 应尽量避免在where子句中使用or来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num=10 or num=20
    可以这样查询:
select id from t where num=10
union all
select id from t where num=20
  1. innot in也要慎用,因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如:select id from t where num in(1,2,3)
  2. 对于连续的数值,能用between就不要用in了:select id from t where num between 1 and 3
  3. 尽量避免在索引过的字符数据中,使用非打头字母搜索。这也使得引擎无法利用索引。
    见如下例子:
SELECT * FROM T1 WHERE NAME LIKE ‘%L%’
SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’
SELECT * FROM T1 WHERE NAME LIKE ‘L%’

即使NAME字段建有索引,前两个查询依然无法利用索引完成加快操作,引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作
8. 应尽量避免在where子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描
9. 应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描
10. 不要在where子句中的“=”左边进行函数算术运算其他表达式运算,否则系统将可能无法正确使用索引 11. 使用字段选择性高的的字段来建立索引 show index from table 查看表上的索引。

Table表的名称
Non_unique如果索引不能包括重复词,则为0。如果可以,则为1。
Key_name索引的名称
Seq_in_index索引中的列序列号,从1开始
Column_name列名称。
Collation列以什么方式存储在索引中。
在MySQL中,有值‘A’(升序)或NULL(无分类)。
Cardinality索引中唯一值的数目的估计值。
通过运行ANALYZE TABLE或myisamchk -a可以更新。
基数根据被存储为整数的统计数据来计数,
所以即使对于小型表,该值也没有必要是精确的。
基数越大,当进行联合时,MySQL使用该索引的机会就越大
Sub_part如果列只是被部分地编入索引,则为被编入索引的字符的数目
。如果整列被编入索引,则为NULL。
Packed指示关键字如何被压缩。如果没有被压缩,则为NULL。
Null如果列含有NULL,则含有YES。如果没有,则该列含有NO。
Index_type用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

索引选择性=索引列唯一值/表记录数 选择性较低索引 可能带来的性能问题 选择性越高索引检索价值越高,消耗系统资源越少; 选择性越低索引检索价值越低,消耗系统资源越多; 查询条件含有多个字段时,不要在选择性很低字段上创建索引 可通过创建组合索引来增强低字段选择性和避免选择性很低字段创建索引带来副作用; 尽量减少possible_keys,正确索引会提高sql查询速度,过多索引会增加优化器选择索引的代价,不要滥用索引; 选择合适的索引列顺序 在一个多列的B-Tree索引中,索引列的顺序意味着索引首先按照最左列进行排序,其次是第二列,等等。

所以,索引可以按照升序或者降序进行扫描,以满足精确符合列顺序的ORDER BY,GROUP BYDISTINCT等子句的查询要求。 对于如何选择索引的列的顺序有一个经验法则:将选择性最高的列放到索引最前列。当不需要考虑排序和分组时,将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找,可以最快的过滤出需要的数据行。但有时我们需要根据那些运行频率最高的查询来调整索引列的顺序。 计算出列的选择性:
select count(distinct twitter_id)/count() as twitter_count , count(distinct aid)/count() as aid_count from campaign_goods_info;
使用覆盖索引 如果索引包含满足查询的所有数据,就称为覆盖索引。覆盖索引是一种非常强大的工具,能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点:
(1) 索引项通常比记录要小,所以MySQL访问更少的数据;
(2) 索引都按值的大小顺序存储,相对于随机访问记录,需要更少的I/O;
(3) 大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。
(4) 覆盖索引对于InnoDB表尤其有用,因为InnoDB使用聚集索引组织数据,如果二级索引中包含查询所需的数据,就不再需要在聚集索引中查找了。
覆盖索引不能是任何索引,只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同,并不是所有存储引擎都支持覆盖索引(Memory就不支持)。 例:explain select aid, goods_id from campaign_goods_info;

优化LIMIT分页

在系统中需要进行分页操作的时候,我们通常会用LIMIT加上偏移量的方法来实现,同时加上合适的ORDER BY子句。如果有对应的索引,通常效率会不错,否则,MySQL需要做大量的文件排序操作。 例如可能是LIMIT 1000,20这样的查询,这是MySQL需要扫描10020条记录然后只返回最后20条,前面的10000条记录都将被抛弃,这样做的代价非常高。

  • 优化方法一: 尽可能的使用覆盖索引,而不是查询所有的列。

  • 优化方法二: LIMIT和OFFSET的问题,其实就是OFFSET的问题,它会导致MYSQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置,那么下次就可以直接从该书签记录的位置开始扫描,这样就可以避免使用OFFSET。 该技术的好处是,无论翻页到多么后面,其性能都会很好!

Mysql不会使用已有的索引

  1. 如果mysql估计使用索引比全表扫描更慢,则不使用索引。
    例如:如果key_part1均匀分布在1和100之间,下列查询中使用索引就不是很好:
    SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
  2. 如果使用heap表并且where条件中不用索引列,其他><>=<=均不使用索引;
  3. 如果不是索引列的第一部分;
  4. 如果like是以开始;
  5. where后边条件为字符串的一定要加引号,字符串如果为数字mysql会自动转为字符串,但是不使用索引。

索引并不是时时都会生效的,比如以下几种情况,将导致索引失效:
不等号!=函数like '%xxx'范围查询(>、<、between、like)

  1. 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
    注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
  2. 对于多列索引,不是使用的第一部分,则不会使用索引
  3. like查询是以%开头
  4. 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
  5. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
    此外,查看索引的使用情况
    show status like ‘Handler_read%';
  • 大家可以注意:

handler_read_key:这个值越高越好,越高表示使用索引查询到的次数
handler_read_rnd_next:这个值越高,说明查询低效

  • qq_43638135
    妲己再美究为妃: 博主没有想过自己接一些私活干吗?我现在还没毕业,但是我也确实听说外挂市场自动化游戏脚本市场挺火热的,并且报酬也很丰厚,但是具体的我也不是很清楚,求解答。 (1个月前 #47楼) 查看回复(2) 举报 回复
    22