mysql alter 原理_理解MySQL索引原理和实现

索引含义

系统根据某种算法，将已有的数据(未来可能新增的数据)，单独建立一个文件：文件能够实现快速的匹配数据，并且能够快速的找到对应表中的记录

索引的意义

1.提升查询数据的效率

2.约束数据的有效性(唯一性等)

增加索引的前提条件：索引本身会产生索引文件(有时候有可能比数据文件还大)会非常耗费磁盘空间

如果某个字段需要作为查询条件经常使用，那么可以使用索引。

如果某个字段需要进行数据的有效性约束，也可能使用索引(主键，唯一键)

MySQL中索引的语法

创建索引

注意：创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行

索引的创建可以在CREATE TABLE语句中进行，也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。

删除索引可以利用ALTER TABLE或DROP INDEX语句来实现。

(1)使用ALTER TABLE语句创建索引。

语法如下：

alter table table_name add index index_name (column_list) ;

alter table table_name add unique (column_list) ;

alter table table_name add primary key (column_list) ;

其中包括普通索引、UNIQUE索引和PRIMARY KEY索引3种创建索引的格式，

table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。

另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。

(2)使用CREATE INDEX语句对表增加索引。

能够增加普通索引和UNIQUE索引两种,不能增加PRIMARY KEY索引。其格式如下：

create index index_name on table_name (column_list) ;

create unique index index_name on table_name (column_list) ;

(3)在CREATE TABLE语句中进行

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

INDEX [indexName] (username(length))

);

(4)删除索引。

删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。其格式如下：

drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

其中，在前面的两条语句中，都删除了table_name中的索引index_name。

而在最后一条语句中，只在删除PRIMARY KEY索引中使用，

因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。

如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，

则MySQL将删除第一个UNIQUE索引。

如果从表中删除某列，则索引会受影响。

对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。

如果删除组成索引的所有列，则整个索引将被删除。

显示索引信息

SHOW INDEX FROM table_name;

索引的分类

(假设在下表)

主键索引

即主索引，根据主键建立索引，不允许重复，不允许空值

alter table a add index key_index(a_id);

唯一索引

用来建立索引的列的值必须是唯一的，允许空值

alter table a add unique name_index(a_name);

普通索引

用表中的普通列构建的索引，没有任何限制

alter table a add index name2_index(a_name);

全文索引

用大文本对象的列构建的索引

在MySQL5.6以下，只有MyISAM表支持全文检索。在MySQL5.6以上Innodb引擎表也提供支持全文检索

首先需要增加一个文本对象

alter table a add(a_tx text);

增加索引alter table a add fulltext index text_index(a_tx);

插入数据到表如下

注意：我们还需要修改ft_min_word_len因为默认为4，mysql fulltext只会对4个字或者4个字以上的关键字进行建立索引操作。而假设搜索的'a','aa','aaa'关键字长度都小于4。

SHOW VARIABLES LIKE 'ft%';

ft_boolean_syntax:全文索引分词关键字，不能更改，为内置变量

ft_max_word_len:最大分词长度,一般情况下不需要更改

ft_min_word_len:最小分词长度，一般修改为1

ft_query_expansion_limit:不明觉厉的一个参数，基本上不需要设置

ft_stopword_file:全文停止词

修改完毕后需要重建表的索引

REPAIR TABLE tbl_name QUICK; //tbl_name是数据表名

如果出现The storage engine for the table doesn't support repair好像是只有MyISAM才能直接修复

使用全文索引的格式： MATCH (columnName) AGAINST ('string')

eg:

SELECT * FROM a WHERE MATCH(a_tx) AGAINST('爱');

当查询多列数据时：

建议在此多列数据上创建一个联合的全文索引，否则使用不了索引的。

SELECT * FROM student WHERE MATCH(name,address) AGAINST('聪 广东')

总结一下要点

FULLTEXT全文索引的几个关键点

1.表的存储引擎需要是MyISAM,听说MYSQL5.6也支持全文索引了;

2.字段类型：char、varchar和text;

3. MySQL全文索引查询关键词最小长度限制;

=> ft_min_word_len,默认是4,建议改为1,不然against()对应的关键字就只能是4个以上的字符,查不了单个字符,也查不了单个汉字.

=> my.ini配置文件中添加

[mysqld]

ft_min_word_len = 1

=> 设置 ft_min_word_len 之后,要重启MySQL服务,然后执行 SHOW VARIABLES LIKE 'ft%';查看 ft_min_word_len设置成功没有;

=> 重新设置配置后,已经设置的索引需要重新设置生成索引,不然有可能报错,

我在update某些记录的时候就报错了: Incorrect key file for table './webm/temp.MYI';try to repair it.

后来, 我执行了 mysql> repair table 表名; 就好了;

5. match(索引名),match()的参数是索引名,不是字段名;

=> MATCH(title, content)里的参数必须和FULLTEXT(title, content)里的参数一模一样。

6. match(singername,songname),可以同时在多个索引名里面查找关键字;

7. 如果一个关键词在50%的数据出现,那么这个词会被当做无效词,可以使用against('关键字'IN BOOLEAN MODE)绕过无效设定;

8. 如果搜索多个词,请用空格或者逗号隔开,如下

=> SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('a x');

=> SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('a,x');

=> AGAINST('关键字1 关键字2'),使用逗号或空格隔开多个关键字,使用的是or规则.

9. 每次更新表都会重构索引,索引使用了全文索引会拖慢insert和update;

10. 搜索语法规则;

=> + 一定要有(不含有该关键词的数据条均被忽略)。

=> - 不可以有(排除指定关键词，含有该关键词的均被忽略)。

=> " " 用双引号将一段句子包起来表示要完全相符，不可拆字。

高能警报

MySQL不支持中文全文索引，原因很简单：与英文不同，中文的文字是连着一起写的，中间没有MySQL能找到分词的地方，截至目前MySQL5.6版本是如此，但是有变通的办法，就是将整句的中文分词，并按urlencode、区位码、base64、拼音等进行编码使之以“字母+数字”的方式存储于数据库中。

为啥mysql全文检索只能检索关键字是全部，因为中文啊

换成英文

就好咯

关于解决中文检索问题升级mysql应该可以解决

升完级就可以创建

mysql> alter table a add fulltext index text_index(a_tx) WITH PARSER ngram;支持中文检索的索引了 是不是很棒帮哒

mysql> alter table a add fulltext index text_index(a_tx) WITH PARSER ngram;

Query OK, 0 rows affected (0.30 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from a;

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

| id | name | a_tx |

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

| 1 | 菜菜最厉害 | NULL |

| 2 | 小明 | 我爱中国 |

| 3 | 小王 | 我喜欢星星 |

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

3 rows in set (0.00 sec)

mysql> select * from a where match(a_tx) against('中国');

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

| id | name | a_tx |

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

| 2 | 小明 | 我爱中国 |

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

1 row in set (0.01 sec)

关于naram插件的检索最小长度默认是2

show variables like 'ngram%'; 修改同样再配置文件中修改

mysql> show variables like 'ngram%';

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

| Variable_name | Value |

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

| ngram_token_size | 2 |

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

全文检索语法

搜寻语法：

+：一定要有。

-：不可以有，但这个'不可以有'指的是在符合的条件里不可以有指定的字符串，所以不能只写'-yoursql'这样是查不到任何东西的，必须搭配其他语法使用。

：(什么都没)预设用法，表示可有可无，有的话排比较前面，没有的排后面。

>：提高该字的相关性。

( )：条件可以巢状。

+aaa +(>bbb

~：将其相关性由正转负，表示拥有该字会降低相关性，但不像「-」将之排除，只是排在较后面。

*：万用字，不像其他语法放在前面，这个要接在字符串后面。

" "：用双引号将一段句子包起来表示要完全相符，不可拆字。

组合索引

用多个列组合构建的索引，这多个列中的值不允许有空值

alter table a add index more_index(name,a_tx(4));

因为这里a_tx是text类型，所以需要使用前多少字符；

在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，

在允许的情况下，可以只取col1和col2的前几个字符作为索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2(3))；

表示使用col1的前4个字符和col2的前3个字符作为索引

explain select * from a where name='小王' AND a_tx='我爱中国';

explain的分析

实例

mysql建立组合索引(联合索引)有最左前缀的原则，即最左优先，如：

如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；

如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；

关于最左前缀的使用，有下面两条说明：

注意

最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、

比如a = 1 and b = 2 and c > 3 and d = 4

如果建立(a,b,c,d)顺序的索引，d是用不到索引的，(因为c>3是范围查询)

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

=和in可以乱序，

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，

mysql的查询优化器会帮你优化成索引可以识别的形式

索引的实现原理