MySql索引
[TOC]
神马是MySql索引
数据库索引是一种数据结构,目的是提高表的操作速度。要创建的索引,应当认为哪列将用于使SQL查询,创建对这些列的一个或多个索引。实际上,索引也是表,其中保存主键或索引字段的指针并指向每个记录到实际的表的类型。
索引一方面可以提高数据的检索速度,不过另一方面过多的索引会降低表的gen更新速度,(因为在更新表的同时也会更新索引。)
索引的基本用法
创建与修改普通索引
1234567891011CREATE INDEX indexName ON mytable(username(length));//创建表的时候直接指定CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,INDEX [indexName] (username(length)));//修改索引ALTER mytable ADD INDEX [indexName] ON (username(length))//删除索引DROP INDEX [indexName] ON mytable;创建与修改复合索引
复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则,即在查询条件中使用了复合索引的第一个字段,索引才会被使用。因此,在复合索引中索引列的顺序至关重要。
123create index index_name on tbl_name(index_col_name,...);//修改索引alter table tbl_name add index index_name on (index_col_name,...);创建与修改唯一索引
唯一索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
123456789CREATE UNIQUE INDEX indexName ON mytable(username(length))//创建表的时候直接指定CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,UNIQUE [indexName] (username(length)));//修改索引ALTER table mytable ADD UNIQUE [indexName] (username(length))创建与修改主键索引
主键索引值必须是唯一的,且不能为NULL。(默认创建主键都会为该列创建索引)
123ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)//删除索引ALTER TABLE testalter_tbl DROP PRIMARY KEY;创建与修改全文索引
它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。
12345678CREATE TABLE article (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),content TEXT,FULLTEXT (title, content) --在title和content列上创建全文索引);//修改索引ALTER TABLE article ADD FULLTEXT INDEX fulltext_article (title, content)如何使用全文索引
12//在article表的title和content列中全文检索指定的查询字符串SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')MySQL自带的全文索引只能对英文进行全文检索,目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索,我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。
使用索引的注意事项
不要在列上进行运算或者函数
在索引列上进行运算或使用函数会导致索引失效
1select * from user where YEAR(birthday)<1990 and code="">不要为多个列创建索引
为多个列创建独立的索引,大部分并不能提高MySQL的查询性能。因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。
索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
范围查询对多列查询的影响
如果查询中的某个列有范围查询,则其右边所有列都无法使用索引优化查找。
1where age>10 and age<90 and name="lianggzone" >这是因为age是范围查询,导致多列索引t_index(age,name),无法用到name索引。