MySQL中索引的使用

索引的定义

在MySQL中，索引是一个排序的列表，在这个索引中存在这个索引的值和这个索引所在行的物理地址，使用索引可以加快查询速度是因为，使用索引后可以不用扫描全表来定位某行的位置，而是通过索引列表直接跳转到该行的物理地址访问相关的数据。

MySQL中索引的语法

建表的时候添加索引：

 CREATE TABLE table(
    ID INT NOT NULL,
    username VARCHAR(16)  NOT NULL,
    INDEX [indexname] (username(length))
);

 在建表以后添加索引：

ALERT TABLE table ADD [UNIQUE] INDEX index_name(column_name);

CREATE INDEX index_name ON table(column_name);

 在建立索引的时候需要占用磁盘空间，而且需要对表加锁。

根据索引查询：

具体查询：
SELECT * FROM table_name WHERE column_1=column_2;(为column_1建立了索引)
 
或者模糊查询
SELECT * FROM table_name WHERE column_1 LIKE '%三'
SELECT * FROM table_name WHERE column_1 LIKE '三%'
SELECT * FROM table_name WHERE column_1 LIKE '%三%'
 
SELECT * FROM table_name WHERE column_1 LIKE '_好_'
 
如果要表示在字符串中既有A又有B，那么查询语句为：
SELECT * FROM table_name WHERE column_1 LIKE '%A%' AND column_1 LIKE '%B%';
 
SELECT * FROM table_name WHERE column_1 LIKE '[张李王]三';  //表示column_1中有匹配张三、李三、王三的都可以
SELECT * FROM table_name WHERE column_1 LIKE '[^张李王]三';  //表示column_1中有匹配除了张三、李三、王三的其他三都可以
 
//在模糊查询中，%表示任意0个或多个字符；_表示任意单个字符（有且仅有），通常用来限制字符串长度;[]表示其中的某一个字符；[^]表示除了其中的字符的所有字符
 
或者在全文索引中模糊查询
SELECT * FROM table_name WHERE MATCH(content) AGAINST('word1','word2',...);

删除索引：

DROP INDEX index ON table;

ALERT TABLE table DROP INDEX index;

索引的优缺点：

  • 优势：可以快速检索，减少IO次数，加快检索速度；根据索引排序和分组，可以加快排序和分组；
  • 劣势：索引本身也是表，会占用磁盘空间，一般来说，索引表的大小是数据表的1.5倍，而且，索引的创建和维护都需要时间成本；构建索引会降低数据表的修改速度，因为在修改的过程中需要同时修改索引表。

索引的分类：主键索引，唯一索引，普通索引（全文索引，组合索引）

  • 主键索引：既根据主键pk_column（length）创建索引，不允许空值，不允许重复 
    ALERT TABLE table ADD PRIMARY KEY pk_index('col');
  • 唯一索引：用来建立索引的列的值必须是惟一的，允许空值； 
    ALERT TABLE table ADD UNIQUE pk_index('col');
  • 普通索引：用表中的普通列构建的索引，没有任何限制； 
    ALERT TABLE table ADD INDEX pk_index('col');
  • 组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值 
    ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；
    *遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。*在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引 
    ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

索引的实现原理：MySQL支持多种数据引擎，所以对索引的支持也各不相同，比如有B+Tree， BTree, Hash索引， 全文索引

  • 哈希索引：只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。全文索引：
  • FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加：
//创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
id INT(10) PRIMARY KEY,
name VARCHAR(10) NOT NULL,
my_text TEXT,
FULLTEXT(my_text)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
//创建表以后，在需要的时候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX ft_index(column_name);
全文索引的查询也有自己特殊的语法，而不能使用LIKE %查询字符串%的模糊查询语法

SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');

 

注意：

*对于较大的数据集，把数据添加到一个没有FULLTEXT索引的表，然后添加FULLTEXT索引的速度比把数据添加到一个已经有FULLTEXT索引的表快。

*5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本之后InnoDB存储引擎开始支持全文索引

*在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。

*在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节，此外，如果检索的字符包括停止词，那么停止词会被忽略。


原文链接：https://blog.csdn.net/tongdanping/article/details/79878302

posted @ 2019-09-30 14:55  shemlothae  阅读(220)  评论(0)  编辑  收藏  举报