Mysql如何适当的添加索引介绍
时间:2019-05-30 17:34 来源:linux.it.net.cn 作者:IT
这里先简单介绍一下索引：
添加索引是为了提高数据库查询性能，索引是最物美价廉的东西了，不用加内存，不用改程序，不用调sql，只要执行个正确的create index ,查询的速度就可能提高百倍千倍，这可是有诱惑力的，可是天下没有没费的午餐，查询的速度的提高是以牺牲insert update delete的速度为代价的。而且索引大小一般是数据的三分之一 ，再加上索引要加载进内存的，如果全部字段都加索引会以牺牲内存为代价的，所以才要设当的添加索引。
这里简单介绍一下mysql中常用索引：
在添加索引之前最好先查看一下该表中已存在哪些索引：show index from 表名；
1、主键索引
注意： 主键索引一张表中只能有一个，但是可以添加多个索引 比如：唯一索引、普通索引、全文索引。
主键索引：一般在建表的时候就添加了 比如：id 一般是主键索引加自动递增。
建表后添加主键索引 ：alter table table_name add primary key (column name);
主键索引的特点：不能为空且唯一。
2、普通索引
创建普通索引： alter table table_name add index 索引名(column1,column2);
3、唯一索引
创建唯一索引：ALTER TABLE `table_name` ADD UNIQUE (`column`)；
唯一索引与主键索引的区别：
唯一索引：可以有多个null 但数据内容不能重复
主键索引：不能为null，且内容只能唯一。
两个区别就在于主键索引不能为null 唯一索引可以有多个null 其余都一样。
4、全文索引
全文索引只有MyISAM有效(mysql5.6之后InnoDB也支持了全文索引)[5.7不支持MyISAM]
全文索引主要针对文本文件，比如文章、标题。
在创建表时创建全文索引：
CREATE TABLE `article` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
` content` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM（5.6之后InnoDB也支持全文索引 、5.7不支持MyISAM引擎） DEFAULT CHARSET=utf8;
在现有表中创建全文索引：
ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content);
创建完全文索引之后使用也有要注意的地方：
众所周知在数据库中进行模糊查询是使用like关键字进行查询的，例如：
SELECT * FROM article WHERE content LIKE ‘%查询字符串%';
那么，我们在使用全文索引也这样使用吗？当然不是，我们必须使用特有的语法才能使用全文索引进行查询，例如，我们想要在article表的title和content列中全文检索指定的查询字符串，我们可以如下编写SQL语句：
SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串');
强烈注意：MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx（斯芬克斯）/Coreseek技术来处理中文。
注：目前，使用MySql自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySql全文索引所能找到的词默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。
如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。
删除索引sql语句：alter table table_name drop index 索引名;
通过上面的简单介绍后，那么应该在哪些字段上添加索引呢？
1、 频繁查询的字段，应该创建索引。
2、更新非常频繁的字段，不应该创建索引。
3、唯一性太差的字段，比如 gender字段，就不应该创建索引。
4、不会出现在where条件之后的字段，不应该创建索引。
满足一下条件，应该创建索引：
1、频繁要查询的字段，经常出现在where条件后面的字段，应该创建索引。
2、更新不频繁的字段，可以创建索引。
索引使用的注意事项
1.对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
比如我们对title,content 添加了复合索引
select * from table_name where title = 'test';会用到索引
select * from table_name where content = 'test';不会用到索引
2.对于使用like的查询，查询如果是 ‘%a'不会使用到索引 ,而 like 'a%'就会用到索引。最前面不能使用%和_这样的变化值
3.如果条件中有or，即使其中有条件带索引也不会使用。
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。
查看索引的使用情况：show status like‘Handler_read%';
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高，说明查询低效。
(责任编辑：IT)
| 这里先简单介绍一下索引： 添加索引是为了提高数据库查询性能，索引是最物美价廉的东西了，不用加内存，不用改程序，不用调sql，只要执行个正确的create index ,查询的速度就可能提高百倍千倍，这可是有诱惑力的，可是天下没有没费的午餐，查询的速度的提高是以牺牲insert update delete的速度为代价的。而且索引大小一般是数据的三分之一 ，再加上索引要加载进内存的，如果全部字段都加索引会以牺牲内存为代价的，所以才要设当的添加索引。 这里简单介绍一下mysql中常用索引： 在添加索引之前最好先查看一下该表中已存在哪些索引：show index from 表名； 1、主键索引 注意： 主键索引一张表中只能有一个，但是可以添加多个索引 比如：唯一索引、普通索引、全文索引。 主键索引：一般在建表的时候就添加了 比如：id 一般是主键索引加自动递增。 建表后添加主键索引 ：alter table table_name add primary key (column name); 主键索引的特点：不能为空且唯一。 2、普通索引 创建普通索引： alter table table_name add index 索引名(column1,column2); 3、唯一索引 创建唯一索引：ALTER TABLE `table_name` ADD UNIQUE (`column`)； 唯一索引与主键索引的区别： 唯一索引：可以有多个null 但数据内容不能重复 主键索引：不能为null，且内容只能唯一。 两个区别就在于主键索引不能为null 唯一索引可以有多个null 其余都一样。 4、全文索引 全文索引只有MyISAM有效(mysql5.6之后InnoDB也支持了全文索引)[5.7不支持MyISAM] 全文索引主要针对文本文件，比如文章、标题。 在创建表时创建全文索引： CREATE TABLE `article` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, ` content` text, PRIMARY KEY (`id`), FULLTEXT KEY `title` (`title`,`content`) ) ENGINE=MyISAM（5.6之后InnoDB也支持全文索引 、5.7不支持MyISAM引擎） DEFAULT CHARSET=utf8; 在现有表中创建全文索引： ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content); 创建完全文索引之后使用也有要注意的地方： 众所周知在数据库中进行模糊查询是使用like关键字进行查询的，例如： SELECT * FROM article WHERE content LIKE ‘%查询字符串%'; 那么，我们在使用全文索引也这样使用吗？当然不是，我们必须使用特有的语法才能使用全文索引进行查询，例如，我们想要在article表的title和content列中全文检索指定的查询字符串，我们可以如下编写SQL语句： SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串'); 强烈注意：MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx（斯芬克斯）/Coreseek技术来处理中文。 注：目前，使用MySql自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySql全文索引所能找到的词默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。 如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。 删除索引sql语句：alter table table_name drop index 索引名; 通过上面的简单介绍后，那么应该在哪些字段上添加索引呢？ 1、 频繁查询的字段，应该创建索引。 2、更新非常频繁的字段，不应该创建索引。 3、唯一性太差的字段，比如 gender字段，就不应该创建索引。 4、不会出现在where条件之后的字段，不应该创建索引。 满足一下条件，应该创建索引： 1、频繁要查询的字段，经常出现在where条件后面的字段，应该创建索引。 2、更新不频繁的字段，可以创建索引。 索引使用的注意事项 1.对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。 比如我们对title,content 添加了复合索引 select * from table_name where title = 'test';会用到索引 select * from table_name where content = 'test';不会用到索引 2.对于使用like的查询，查询如果是 ‘%a'不会使用到索引 ,而 like 'a%'就会用到索引。最前面不能使用%和_这样的变化值 3.如果条件中有or，即使其中有条件带索引也不会使用。 4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。 查看索引的使用情况：show status like‘Handler_read%'; handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。 handler_read_rnd_next:这个值越高，说明查询低效。 (责任编辑：IT) |