MySQL 索引

索引介绍在MySQL中,索引是高效获取数据的最重要的数据结构,通常在表数据越来越多情况下获取数据的效率开始下降,而索引或者叫做键可以有效提升效率。理解索引工作的方式最好的办法就是把索引比喻成书的目录,当需要查看特定的章节时通过查看目录的方式往往要比查看整个书的内容要有效很多。当索引包含多个字段时,索引字段的顺序就非常重要,因为MySQL是从左开始匹配使用索引,意味着如果没有最左边字段时,语句是用不...

MySQL 索引

索引介绍

在MySQL中,索引是高效获取数据的最重要的数据结构,通常在表数据越来越多情况下获取数据的效率开始下降,而索引或者叫做键可以有效提升效率。

理解索引工作的方式最好的办法就是把索引比喻成书的目录,当需要查看特定的章节时通过查看目录的方式往往要比查看整个书的内容要有效很多。

索引包含多个字段时,索引字段的顺序就非常重要,因为MySQL是从左开始匹配使用索引,意味着如果没有最左边字段时,语句是用不了索引。

使用索引的优势在于:
大大减少服务器需要扫描的数据量
帮助服务器避免排序和临时表
可以将随机IO变成顺序IO

索引在带来上述优势的同时,也有缺点在;
在创建索引和维护索引时会耗费时间,而且随着数据量的增加而增加
索引文件会占用物理空间
当对表的数据进行Insert,update,delete操作时,索引也要动态维护,这就降低了DML的执行效率

B-Tree索引

B-tree索引
顾名思义,B-tree索引使用B-tree的数据结构存储数据,不同的存储引擎以不同的方式使用B-Tree索引,比如MyISAM使用前缀压缩技术使得索引空间更小,而InnoDB则按照原数据格式存储,且MyISAM索引在索引中记录了对应数据的物理位置,而InnoDB则在索引中记录了对应的主键数值

下图展示InnoDB的B-tree索引结构

B-Tree索引驱使存储引擎不再通过全表扫描获取数据,而是从索引的根节点开始查找,在根节点和中间节点都存放了指向下层节点的指针,通过比较节点页的值和要查找值可以找到合适的指针进入下层子节点,直到最下层的叶子节点最终的结果就是要么找到对应的值,要么找不到对应的值。整个B-tree树的深度和表的大小直接相关。

B-Tree对索引列是顺序组织存储的,所以也很适合查找范围数据。

如下图

使用B-Tree索引适用于全键值、键值范围或者键前缀查找

  1. 全键值匹配:和索引中的所有列都进行匹配,比如查找姓名为zhang san,出生于1982-1-1的人
  2. 匹配最左前缀:和索引中的最左边的列进行匹配,比如查找所有姓为zhang的人
  3. 匹配列前缀:匹配索引最左边列的开头部分,比如查找所有以z开头的姓名的人
  4. 匹配范围值:匹配索引列的范围区域值,比如查找姓在li和wang之间的人
  5. 精确匹配左边列并范围匹配右边的列:比如查找所有姓为Zhang,且名字以K开头的人
  6. 只访问索引的查询:查询结果完全可以通过索引获得,也叫做覆盖索引,比如查找所有姓为zhang的人的姓名

创建测试数据

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));insert into people values('zhang','san','1982-1-1','m');insert into people values('li','si','1985-3-2','m');insert into people values('wang','wu','1988-6-15','f');

  

B-Tree索引也有一定的限制:

如果查询条件不是按照索引最左列开始查找,则无法使用索引,比如如果查找名字为san的人、查找某个特定生日的人、查看姓氏以某字母结尾的人都无法使用此索引

如果查询条件跳过了索引的中间列,则查询使用索引仅使用最左边的列,比如查找姓为zhang且在某个特定日期出生的人

如果查询的某列有范围查找,则其右边的列无法使用索引优化查找,如查询姓为zhang,名字以s开头且生日为1982-1-1的人,则查询只能使用前两列。

哈希索引

MySQL中只有Memory引擎支持哈希索引,但Memory引擎也支持B-Tree索引。哈希索引是基于哈希表实现, 只有精确匹配索引中的所有列的查询才有效。对每一行数据,会将所对应的索引列计算出一个哈希码, 在索引中存放此哈希码和对应数据行的指针。当存在多行数据的哈希码相同时,索引会以链表的方式存放多个记录指针到同一个哈希条目中。也有不同的索引列值却有相同哈希码的情况,叫哈希冲突,当存在哈希冲突时,
存储引擎必须遍历链表中所有行指针,于对应的行数据比较,直到找到所有符合条件的行。

创建一些测试表和数据

CREATE TABLE testhash (fname VARCHAR(50) NOT NULL,lname VARCHAR(50) NOT NULL,KEY USING HASH(fname)) ENGINE=MEMORY;insert into  testhash values("Arjen", "Lentz"), ("Baron",  "Schwartz"), ("Peter",  "Zaitsev"), ("Vadim",  "Tkachenko");mysql> SELECT * FROM testhash; -------- ----------- | fname | lname | -------- ----------- | Arjen | Lentz || Baron | Schwartz || Peter | Zaitsev || Vadim | Tkachenko | -------- ----------- 

  

虽然InnoDB表创建索引时指定hash索引也能创建成功,但底层创建的索引依旧是btree索引

create table TIhash(fname varchar(10),lname varchar(10),key using hash(fname));

  

看看表结构

看看底层用的索引

哈希索引的限制:

  1. 只存储哈希值和行指针,不存储字段值,所以最后的查询会真正查询行数据,无法使用覆盖索引
  2. 索引数据并不是按照索引值顺序存储的,所以排序操作无法使用索引
  3. 由于哈希索引是用索引列的全部内容计算哈希码,所以如果查询只有部分索引字段时是无法使用索引的
  4. 哈希索引只支持等值比较查询,比如=,IN操作等,不支持范围查询

例:如下
explain select * from TIhash where fname=’a’;

explain select * from TIhash where fname>’a’;

explain select * from TIhash where fname in (‘a’,’b’);

全文索引

Fulltext索引是创建在char, varchar, text文本字段上的加速查询和DML操作的索引。它不是直接比较索引中的值,而是查找文本中的关键词,所以全文索引更类似于搜索引擎而不是简单的where条件匹配

Fulltext索引的使用时通用match()…against语句
创建表的适合添加全文索引

CREATE TABLE `article` (`id` int(11) NOT NULL AUTO_INCREMENT ,`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,`time` int(10) NULL DEFAULT NULL ,PRIMARY KEY (`id`),FULLTEXT (content));

  

修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)

直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)

全文搜索的语法: MATCH(col1,col2,…) AGAINST (expr[search_modifier])。其中

MATCH中的内容为已建立FULLTEXT索引并要从中查找数据的列, AGAINST中的expr为要查找的文本内容, search_modifier为可选搜索类型。 search_modifier的可能取值有:

IN NATURAL LANGUAGEMODE、 IN NATURAL LANGUAGE MODE WITH QUERY
EXPANSION、 IN BOOLEAN MODE、 WITH QUERY EXPANSION。 search_modifier
的每个取值代表一种类型的全文搜索,分别为自然语言全文搜索、带查询扩展的自然语
言全文搜索、布尔全文搜索、查询扩展全文搜索(默认使用IN NATURAL LANGUAGE
MODE) 。

插入测试数据

Insert into article values(1,'ac','abcd',1), (2,'bd','bcde',1),(3,'ab','defg',1), (4,'be','degh',1);

  

使用全文索引方法如下

SELECT * FROM tablenameWHERE MATCH(column1, column2) AGAINST(‘xxx’, ‘sss’, ‘ddd’)

  

索引创建

mysql> help create indexName: 'CREATE INDEX'Description:Syntax:CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_option] [algorithm_option | lock_option] ...index_col_name: col_name [(length)] [ASC | DESC]index_option: KEY_BLOCK_SIZE [=] value  | index_type  | WITH PARSER parser_name  | COMMENT 'string'index_type: USING {BTREE | HASH}algorithm_option: ALGORITHM [=] {DEFAULT|INPLACE|COPY}lock_option: LOCK [=] {DEFAULT|NO
源文地址:https://www.guoxiongfei.cn/cntech/2821.html
0