前提
在项目上线过程中发现表中没有加索引,造成服务器cpu迅速飙升,影响了系统的线上环境。趁着这个计划温故一下索引的使用方式。
使用索引好处
使用索引会提升速度主要原因是利用btree方式对数据进行维护,检索的时候使用索引的次数就是log2N次数
索引种类
主键索引
把一张表某个列设为主键的时候,则该列则就是主键索引。
操作方式
-- 方式一(创建的时候建立主键索引)
create TABLE test1(
id int UNSIGNED PRIMARY key auto_increment,
name VARCHAR(32) not null DEFAULT""
);
-- 方式二(创建之后修改主键索引)
CREATE TABLE test2 (
id INT,
NAME VARCHAR (32) NOT NULL DEFAULT ""
);
alter TABLE test2 add PRIMARY KEY(id);
唯一索引
当表某列被指定为unique约束时,这一列就是一个唯一索引
-- 方式一(创建的时候添加唯一索引)
CREATE TABLE test3(id int PRIMARY key auto_increment,name VARCHAR(32) UNIQUE);
-- 方式二(创建之后修改添加唯一索引)
CREATE TABLE test4(id int PRIMARY key auto_increment,name VARCHAR(32))
CREATE UNIQUE INDEX name on test4(name);
create UNIQUE index 索引名 on 表名(列名);
普通索引
-- 方式一 普通索引
CREATE TABLE test5(id int UNSIGNED,name VARCHAR(32));
create index 索引名 on 表名(列);
全文索引
这个主要针对在长文本中查询关键信息等内容
-- -- 方式一 创建全文
CREATE TABLE `test7` (
`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 DEFAULT CHARSET=utf8;
-- 方式二 先建表后创建索引
CREATE TABLE `test8` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`content` text,
PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8;
-- 给现有的test8表的title和content字段创建全文索引
-- 索引名称为fulltext_article
ALTER TABLE article
ADD FULLTEXT INDEX fulltext_article (title, content)
小编通过这篇文章主要讲解一下全文索引
如何使用全文索引?
错误用法:
select * from test8 where content like ‘%mysql%’;
可以利用执行计划分析一下 explain select * from test8 where content like ‘%mysql%’;就会知道没有用到执行计划了。
正确的用法是:
select * from test8 where match(title,body) against('database');
注意
全文索引在MySQL5.6.4之前Innodb是不支持全文索引的,只有myisam支持全文索引,而且仅支持英文字符的全文索引,但是可以通过使用sphinx或者coreseek帮助实现对中文的索引。在mysql5.6.4以及之后的版本中innodb也开始支持全文索引,所以使用全文索引的时候同样需要注意mysql的版本问题。
如何选择创建索引的字段
1、where中经常使用的条件
2、唯一性比较好的字段,即该字段不是唯一的几个值
3、字段内容不是频繁变化的
索引使用
查询要使用索引最重要的条件是查询条件中需要使用索引
特殊情况
对于创建了多列索引的,只有查询条件使用了最左边的列,索引一般就会被使用;
对于like查询,如果查询条件是'%aaa'则不会使用索引,但是‘aaa%'就会使用索引
以下情况将不会使用索引
如果条件中有or,即使有条件带索引也不会使用;
对于多列索引,不是使用的第一部分,则不会使用索引
like查询是以%开头的
如果列索引是字符串,那么条件中一定要使用引号引用起来,否则不使用索引
MySQL估计使用全文扫描比使用索引快,则也不会使用索引。
索引的代价
1、由于维护了btree,会有磁盘的占用情况
2、在对数据进行增删改的时候同样会去维护btree,造成速度降低。
总结
关于索引的问题在线上遇见好几次了,这次好好的总结一下,重点是吸收!