mysql索引使增删变慢_mysql优化（四）–索引

http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

一、 四种索引类型：

主键索引，唯一索引，全文索引，普通索引

二、  为什么建立索引比较快

一般的数据表是按照行来存储的，字段多、有长字段的表的记录就会长，就要占用更多的空间来存储，而索引是建立在一个或少数几个字段上的特殊数据结构，一个索引项的存储开销和表的记录相比是很小的。

所谓查询速度更快，其实发生在计算机内部的动作有三大步骤，即输入、处理和输出，完成整套动作之后，你才能体会到快慢，而在这三个动作中，输入通常就是从硬盘上装载数据到内存，它耗时最甚，那么读取索引和读取表的全部行的数据量差异就大大影响最终性能。另外，索引是排好序的，能够实施二分查找算法，比其行扫描(读取表的所有记录，逐行判断是否满足条件)这种顺序查找算法来说，效率提升也以数量级计。

三、 各种索引的增删查改

3.1 增加

3.1.1 主键索引添加

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

create table aaa

(id int unsigned primary key auto_increment ,

name varchar(32) not null defaul ‘’);

这是id 列就是主键索引.

如果你创建表时，没有指定主键索引，也可以在创建表后，在添加, 指令:

alter table 表名 add primary key (列名);

举例:

create table bbb (id int , name varchar(32) not null default ‘’);

alter table bbb add primary key (id);

3.1.2 普通索引

一般来说，普通索引的创建，是先创建表，然后在创建普通索引

比如:

create table ccc(

id int unsigned,

name varchar(32)

)

create index 索引名 on 表 (列1,列名2);

3.1.3 全文索引

全文索引，主要是针对文件，文本的检索, 比如文章, 全文索引针对MyISAM有用.

创建 ：

CREATE TABLE articles (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT,

FULLTEXT (title,body)

)engine=myisam charset utf8;

如何使用全文索引:

错误用法:

select * from articles where body like ‘%mysql%’; 【不会使用到全文索引】

mysql> explain select * from articles where body like ‘mysql%’\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: articles

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 6

Extra: Using where

1 row in set (0.00 sec)

证明:

explain  select * from articles where body like ‘%mysql%’

正确的用法是:

select * from articles where match(title,body) against(‘database’); 【可以】

mysql> explain select * from articles where match(title,body) against ( ‘mysql’)\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: articles

type: fulltext

possible_keys: title

key: title

key_len: 0

ref: NULL

rows: 1

Extra: Using where

1 row in set (0.00 sec)

☞ 说明:

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

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

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

全文索引：停止词, 因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.比如(a，b，mysql，the)

3.1.4 唯一索引

①当表的某列被指定为unique约束时，这列就是一个唯一索引

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

这时, name 列就是一个唯一索引.

unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复，

但是不能存有重复的空字符串’’

主键字段，不能为NULL,也不能重复.

②在创建表后，再去创建唯一索引

create table eee(id int primary key auto_increment, name varchar(32));

create unique index 索引名  on 表名 (列表..);

3.2 索引查询

desc 表名 【该方法的缺点是：　不能够显示索引名.】

show index(es) from 表名

show keys from 表名

3.3 索引删除

alter table 表名 drop index 索引名;

如果删除主键索引。

alter table 表名 drop primary key

3.4 修改索引

先删除索引再重新创建索引

四、索引的代价

占用磁盘空间、使得dml 增删改操作变慢。索引主要用于查操作变快。

五、哪些列上适合加索引

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

5.2 唯一性太差的字段不适合创建索引

5.3 更新非常频繁的字段不适合创建索引

5.4 不会出现在where字句中的字段不适合创建索引

总结: 满足以下条件的字段，才应该创建索引.

a: 肯定在where条件经常使用 b: 该字段的内容不是唯一的几个值(sex) c: 字段内容不是频繁变化.

六、查看索引的使用情况

如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明表索引不正确或写入的查询没有利用索引。

语法：    mysql> show status like ‘Handler_read%';

七、使用索引的几个注意点

7.1  对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。如果只使用右边的列，索引不会被使用。

7.2 对于使用like的查询，查询如果是 ‘%aaa’ 不会使用到索引。‘aaa%’ 会使用到索引。在like查询时，关键的 ‘关键字’ , 最前面，不能使用 % 或者 _这样的字符.， 如果一定要前面有变化的值，则考虑使用 全文索引->sphinx.

7.3  如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引, 我们建议大家尽量避免使用or 关键字

7.4  如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’), 也就是，如果列是字符串类型，就一定要用 ‘’ 把他包括起来.

7.5  如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

八、 如何选择mysql的存储引擎

8.1 在开发中，我们经常使用的存储引擎 myisam / innodb/ memory

myisam 存储: 如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表，回复表.

INNODB 存储: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.

8.2 MyISAM 和 INNODB的区别

事务安全(MyISAM不支持事务，INNODB支持事务)

查询和添加速度(MyISAM批量插入速度快)

支持全文索引(MyISAM支持全文索引，INNODB不支持全文索引)

锁机制(MyISAM时表锁，innodb是行锁)

外键 MyISAM 不支持外键， INNODB支持外键. (在PHP开发中，通常不设置外键，通常是在程序中保证数据的一致)

Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快. (如果mysql重启的话，数据就不存在了)

如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理，因为在myisam引擎中，如果删除数据，空间不会被回收，索引你会发现虽然数据删除很多，但是空间占用依然很厉害，索引需要使用optimize语句进行空间的回收整理。