mysql索引

四种索引 :   主键索引 唯一索引 全文索引 普通索引
1、添加索引
      1.1 、主键索引的添加
                当一张表中，把某个列设为主键的时候，则该列就是主键索引
                如果你创建表时，没有指定主键索引，也可以在创建表后添加索引
               语句：alter table 表名 add primary key (列名);
      1.2、 普通索引
                 一般来说，普通索引的创建，是先创建表，然后再创建索引
                语句：create index 索引名 on 表名 (列名);
       1.3 全文索引
                全文索引：主要是针对文件，文本的索引，比如文章
                全文索引针对MyISAM有用
                 如何使用全文索引
                错误用法：
                                 select * from articles where body like '%mysql%';
                 这种方法是不会用到全文索引的
                正确用法：
                              // title,body是全文索引，匹配database的句子
                            select * from articles where match(title,body) against('database');
    说明：
    1.在MySQL中fulltext索引只针对myISAM生效
    2.针对英文生效，对中文需要sphinx(coreseek)技术处理
    3.使用方法是match(字段名) against('关键字')
    4.全文索引有一个停止词概念：
        因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，
        就不会创建，这些词，称为 停止词。
1.4 唯一索引
    当表的某列被指定为unique约束，这列就是一个唯一索引
    唯一索引的列可以为null，并且可以有多个
    在创建表后，再去创建唯一索引

    创建语法：create unique index 索引名 on 表名 (列名);

    ALTER TABLE `t_user` ADD unique(`username`);

mysql主键索引和唯一索引
1.主键一定是唯一性索引，唯一性索引并不一定就是主键；
2.一个表中可以有多个唯一性索引，但只能有一个主键；
3.主键列不允许空值，而唯一性索引列允许空值。

    2、查询
    ① desc 表名 该方法缺点：不能够显示索引的名字
    ② show index(es) from 表名
    ③ show keys from 表名
3.删除
    语法：alter table 表名 drop index 索引名;
    主键索引删除：alter table 表名 drop primary key;
4.修改
    先删除，再重新创建
索引注意事项：  索引占用磁盘空间对dml(insert/update/delete)语句效率有影响

在哪些列上适合添加索引？
    较频繁的作为查询条件字段创建索引
    例如 select * from emp where empno=1;
    唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
    例如 select * from emp where sex='男';
    更新非常频繁的字段不适合创建索引
    例如 select * from emp where logincount=1;
    不会出现在where子句中字段不该创建索引
总结：满足以下条件的字段，才能创建索引
a.肯定在where条件中经常使用的
b.该字段的内容不是唯一的几个值
c.字段内容变化不能太频繁
使用索引的注意事项
alter table dept add index myind (dname,loc); // dname就是左边的列，loc是右边的列
下列情况有可能使用到索引
a.对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用
explain select * from dept where dname='aaa';
b.对于使用like的查询，查询条件如果是'%aaa'则不会使用到索引，'aaa%'会使用到索引
下列情况不会使用索引
a.如果条件中有or，即使其中有条件带索引也不会使用
换言之，就是要求使用的所有字段都创建索引，建议：尽量避免使用or关键字
b.对于多列索引，不是使用的第一部分，则不会使用索引
explain select * from dept where loc='aaa';// 多列索引时，loc为右边列，索引不会使用到
c.like查询是以%开头
如果一定要使用，则使用全文索引去查询
d.如果列类型是字符串，那一定要在条件中将数据使用引号引起来，否则不使用索引
e.如果MySQL估计使用全表扫描要比使用索引块，则不使用索引
查看索引的使用情况
show status like 'Handler_read%';
handler_read_key:这个值越高越好，代表使用索引查询到的次数
handler_read_rnd_next:这个值越高，说明查询低效

SQL语句的小技巧
① 优化 group by 语句
默认情况下，MySQL对所有的group by col1,col2 进行排序，这与在查询中指定 order by col1,col2 类似
如果查询中包括 group by 但用户想尽量避免排序结果的消耗，则可以使用 order by null 禁止排序
② 有些情况下，可以使用连接来替代子查询
    因为使用 join MySQL不需要在内存中创建临时表
③ 如果想要在含有 or 的查询语句中利用索引，则 or 之间的每个条件列都必须用到索引，
    如果没有索引，则应该考虑增加索引。
如何选择MySQL的存储引擎
1.myISAM:
    如果表对事务要求不高，同时是以查询和添加为主的。
    比如 BBS中的发帖表，回复表
2.InnoDB:
    对事务要求高，保存的数据都是重要数据
    比如 订单表，账户表
3.Memory:
    数据变化频繁，不需要入库同时又经常查询和修改
myISAM 与 InnoDB 主要区别
1.myisam 批量插入速度快，InnoDB慢，myisam插入数据时不排序
2.InnoDB支持事务
3.myisam支持全文索引
4.锁机制，myisam是表锁，InnoDB是行锁
5.myisam不支持外键，InnoDB支持外键
外键
classes表
create table classes(
        id int unsigned not null auto_increment primary key,
        name varchar(64) not null
    )engine=innoDB;
insert into classes values (1,'aaa');
stu表
create table student(
        id int unsigned not null auto_increment primary key,
        name varchar(64) not null,
        classid int unsigned not null,
        foreign key (classid) references classes(id) /* 外键 */
    )engine=innoDB;
当设置了外键的时候，企图添加一个外键没有的数据，会报错，无法插入数据
insert into student values (1,'hello',1); 这个是正确的
insert into student values (1,'hello',2); 当classes表中id=2不存在时，这个是错误的

这可能是MySQL在InnoDB中设置了foreign key关联，造成无法更新或删除数据。可以通过设置FOREIGN_KEY_CHECKS变量来避免这种情况。
SET FOREIGN_KEY_CHECKS = 0; 
删除完成后设置 
SET FOREIGN_KEY_CHECKS = 1;
其他： 
关闭唯一性校验 
set unique_checks=0; 
set unique_checks=1;