Mysql常见四种索引的使用

提到mysql优化，索引优化是必不可少的。其中一种优化方式 ——索引优化，添加合适的索引能够让项目的并发能力和抗压能力得到明显的提升。

我们知道项目性能的瓶颈主要是在"查(select)"语句，要提升"查"这一性能，mysql索引是必不可少的。接下来总结一下mysql常见的四种索引

一. 四种索引(主键索引/普通索引/全文索引/唯一索引)

1.索引的添加

1.1主键索引的添加

当一张表，把某个列设为主键的时候，则该列就是主键索引

    create table a(
    id int primary key auto_increment,
    name varchar(20) not null default ''
    );
    //这里id就是表的主键

如果当创建表时没有指定主键索引，也可以在创建表之后添加：

alter table table_name add primary key (column name);

修改表自增主键的起始值

ALTER TABLE bbs_addr AUTO_INCREMENT =500;

1.2普通索引

普通索引一般是在建表后再添加的，
create index 索引名 on table_name(column1,column2);

alter table table_name add index 索引名(column1,column2);

1.3全文索引

首先，全文索引主要针对文本文件，比如文章，标题，全文索引只有MyISAM有效(mysql5.6之后InnoDB也支持了全文索引)

    create table c(
    id int primary key auto_increment ,
    title varchar(20),
    content text,
    fulltext(title,content)
    )engine=myisam charset utf8;
     
    insert into c(title,content) 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 c where content like "%mysql%";
这里并不会使用全文索引，可以用explain进行查看。正确用法：

select *  from c where match(title,content) against ('MYSQL');

备注:

1.  在mysql中fulltext 索引只针对 myisam生效

2.  mysql自己提供的fulltext针对英文生效->sphinx(coreseek)技术处理中文

3.  使用方法是 match(字段名..) against(‘关键字’)

1.4唯一索引

create table d(id int primary key auto_increment , name varchar(32) unique)

d表中name就是唯一索引，唯一索引可以有多个null,不能是重复的内容

相比主键索引，主键字段不能为null，也不能重复

添加唯一索引

create [unique|fulltext|spatial]index in_id on test4(id[(长度)][ASC|DESC])

ALTER TABLE bbs_addr ADD [unique|fulltext|spatial] index unique_name(name[(长度)][ASC|DESC])

2. 查询索引

show indexes from table_name;

show keys from table_name;

3.删除索引

alter table table_name drop index 索引名;

drop index 索引名称 on tbl_name

二. 索引的机制

2.1 为什么我们添加完索引后查询速度为变快？

    传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，mysql需要将表的数据从头到尾遍历一遍

    在我们添加完索引之后，mysql一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历(折半查找大幅查询效率)，找到相应的键从而获取数据

2.2 索引的代价

    1. 创建索引是为产生索引文件的，占用磁盘空间

    2. 索引文件是一个二叉树类型的文件，可想而知我们的dml操作同样也会对索引文件进行修改，所以性能会下降

2.3 在哪些column上使用索引？

    1 .较频繁的作为查询条件字段应该创建索引

    2. 唯一性太差的字段不适合创建索引，尽管频繁作为查询条件，例如gender性别字段

    3. 更新非常频繁的字段不适合作为索引

    4.不会出现在where子句中的字段不该创建索引

总结: 满足以下条件的字段，才应该创建索引.
a: 肯定在where条经常使用 b: 该字段的内容不是唯一的几个值 c: 字段内容不是频繁变化

三.索引使用注意事项

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:这个值越高，说明查询低效。