mysql 索引大全

创建一个主键索引
alter table 表名 add primary key(列名)

这时通过创建索引，速度提高了
小建议：如果时间紧急，但没有什么好方案。那就添加索引。查那个表就添加那个。

那些列上适合添加索引，

较频繁的作为查询条件字段应该创建索引
select * from emp where empno=1
select * from emp where sex=’男’
更新非常频繁的字段不适合创建索引
select * from emp where logincount=1
不会出现在where字句中字段不该创建索引
索引的创建，修改，删除，查询->如何使用索引

索引注意四类(primary,unique,index,fulltext)

主键索引

创建主键索引
1.在创建表的时候，指定某列为主键，那么这列就是一个主键索引
create table aaa(id int unsigned primary key auto_increment,name varchar(255));
2.在创建表后，然后指定某列或者某几列为主键索引
create table bbb
(id int unsigned,name varchar(255) not null default ”);
ALTER TABLE bbb ADD PRIMARY KEY(id);

主键索引最多只能有一个
主键索引快是因为它不可能重复，找到一个就行了
普通索引就因为可能重复

创建唯一索引

1.在创建表的时候，直接指定某列，或者某几列为唯一索引，当你把某个字段设置为unique的时候，该列就自然是唯一索引

create table ccc 
(id int unsigned primary key, 
emial varchar(64) unique, 
name varchar(255) not null default ”);

2.创建表后，在指定某列唯一索引
create table ddd
(id int unsigned primary key,
emial varchar(64)
);
//两个方法
1.create unique index uni_email ON ddd (emial);
2.alter table ddd add unique index uni_email(emial)

创建普通索引

1.一般是创建表后，再使用指令来创建索引

create table eee 
(id int unsigned primary key, 
email varchar(64), 
name varchar(255) not null default ”);

//两个方法
1.create index ind_email ON eee (email);
2.alter table eee add index ind_email(email)

说明一下主键索引和唯一索引的区别
1.主键索引不能为空，也不能重复
2.唯一索引可以为空，多个NULL也可以，但是如果有值,就不能重复

全文索引

一句话：主要用处是快速检索文章的关键字，在默认情况下，mysql提供的全文索引支持对英文文章的检索->如果文章中含有中文，则需要学习sphinx(coreseek)

我们先体验一下mysql的自带的fulltext

CREATE TABLE articles ( 
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 
title VARCHAR(200), 
body TEXT, 
FULLTEXT (title,body) 
);

INSERT INTO articles (title,body) VALUES 
(‘MySQL Tutorial’,’DBMS stands for DataBase …’), 
(‘How To Use MySQL Well’,’After you went through a …’), 
(‘Optimizing MySQL’,’In this tutorial we will show …’), 
(‘1001 MySQL Tricks’,’1. Never run mysqld as root. 2. …’), 
(‘MySQL vs. YourSQL’,’In the following database comparison …’), 
(‘MySQL Security’,’When configured properly, MySQL …’);

—-如何使用

SELECT * FROM articles WHERE title like ‘%MySQL%’；X错的

–这样使用

SELECT * FROM articles WHERE MATCH (title,body) AGAINST (‘database’);

提出一个关于全文索引的匹配度的概念，

select MATCH (title,body) AGAINST (‘database’) from articles; 

这里写图片描述

看出，全文索引是以匹配度的高低来决定是否把这条记录检索出来，全文索引对常用词是不会建立索引，我们把这些常用词称为停止词

修改索引：

删除后重新创建 

删除索引

drop index uni_email(索引名) on ddd(表名);

如果删除的是唯一索引，普通索引，全文索引
drop index 索引名 ON 表名
如果删除主键索引
alter table eee(表名) drop primary key;

查询索引：
show index from 表名；
show indexes from 表名
show keys from 表名
desc 表名

如何正确的使用索引

创建一个多列索引来讲解这些技巧

向dept表中插入记录

delimiter $$

create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into dept values ((start+i) ,rand_string(10),rand_string(8));
until i = max_num
end repeat;
commit;
end $$

delimiter ;
call insert_dept(100,10);

创建主键索引

alter table dept add primary key(deptno);

创建多列索引

alter table dept add index mul_index(dname,loc);

介绍一款测试sql语句执行情况的命令 explain

explain 检测索引
explain 加查询语句。能分析出有没有使用索引

这个工具，有助于我们分析sql语句的效率

索引的技巧

• 技巧1：对于创建多列索引，只要查询条件使用了最左的列，索引一般就会被使用
• 技巧2：对于使用Like的查询，查询如果是 ‘%aaa’不会使用索引 ‘aaa%’hu会使用到索引，查询内容的左边，使用%就不会使用到索引

下列的语句将不使用索引：

• 1，如果条件中有or,即使其中有条件带索引也不会使用索引，只有全部条件都带索引，才会使用索引
• 2.对于所列索引，不是使用的第一部分，则不会使用索引
• 3.Like查询是以%开头的，
• 4.如果字段类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引，(添加时，字符串必须”)
• 5.如果mysql估计使用全表扫毛要比使用索引快，则不使用索引(当表只有一条记录的时候)

如何衡量你的索引的效率是否高？

show status like 'Handler_read%';
Handler_read_key 高，证明好
Handler_read_rnd_next 高，证明效率低