MySQL索引与索引优化

说起提高数据库性能,索引是最物美价廉的东西了。不用加内存,不用改程序,不用调sql,只要执行个正确的’create index’,查询速度就可能提高百倍千倍,这可真有诱惑力。可是天下没有免费的午餐,查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的I/O。

MySQL的索引种类

Mysql索引包含:普通索引、主键索引、唯一索引、全文索引,为了适应不同的查询、数据结构,提高查询速度,选择合适的索引方式。

1.普通索引

这是最基本的索引,它没有任何限制。

普通索引的创建方式:

一般来说,普通索引的创建,是先创建表,然后在创建普通索引,代码如下

create table ccc(

id int unsigned,

name varchar(32)

)

create index ccc_index on ccc (id,name);

修改表结构创建索引:

ALTER mytable ADD INDEX [indexName] ON (username(length))

创建表是指定索引:

CREATE TABLE mytable(
  ID INT NOT NULL,
  username VARCHAR(16) NOT NULL,
  INDEX [indexName] (username(length))
); 

删除索引:

DROP INDEX [indexName] ON ccc;

注:上述实例代码中的 length,如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。  

2.主键索引

主键没有着明确的概念定义,其是索引的一种,并且是唯一性索引的一种,且必须定义为“PRIMARY KEY”,主键不能重复,一个表只能有一个主键。

主键字段,不能为NULL,也不能重复;一般是在建表的时候同时创建主键索引;

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

创建表时指定主键索引,实例

create table aaa
(id int unsigned primary key auto_increment ,
name varchar(32) not null defaul ‘’);

建表完成后创建索引:

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

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;

全文索引的使用方式,实例:match()  against()

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

注:

  1. 在mysql中fulltext 索引只针对 myisam存储引擎生效;
  2. mysql自己提供的fulltext针对英文生效,对中文无效->sphinx (coreseek) 技术处理中文;
  3. 使用方法是 match(字段名..) against(‘关键字’);
  4. 全文索引一个 叫 停止词,  因为在一个文本中,创建索引是一个无穷大的数,因此,对一些常用词和字符,就不会创建,这些词,称为停止词。

以下全文停止词部分展示,其他可以查阅MySQL手册

4.唯一索引

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

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

创建表时声明唯一索引,实例:

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

在创建表后,再去创建唯一索引,实例:

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

create unique index eee_index  on eee(name);

MySQL索引的sql操作

1.查询索引

show index[es] from tableName;
#或者
show keys from tableName;

2.删除索引

alter table tableName drop index indexName; 

3.修改索引

先删除,再重新创建.

MySQL索引的的优点和不足

不足:

I.磁盘占用;

例如MyISAM在磁盘上存储成3个文件,其中文件名和表名都相同,但是扩展名分别为:

  • .frm(存储表定义)
  • MYD(MYData,存储数据)
  • MYI(MYIndex,存储索引) 占用一定的磁盘空间,随着数据的增加索引文件也会增加

II.对dml(update delete insert)语句的效率影响;

数据的变化,需要同时维护索引

优点:

查询速度变快,但是为什么变快?

简单理解:没有索引的情况,要进行全表查询,加上索引后利用BTree方式进行数据检索,要检索的数据量大大减少,索引检索速度要快的多。

可以参考MySQL索引的实现方式,进行理解,参考文章

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

MySQL索引的应用

适合索引的情况:

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

使用索引的注意事项:

#实例说明:在dept表中创建符合索引
# dname 左边的列,loc就是右边的列
alter table dept add index my_ind (dname,loc); 

说明,如果我们的表中有复合索引(索引作用在多列上), 此时我们注意:

  • 对于创建的多列索引,只要查询条件使用了最左边的列,索引一般才会被使用。
explain select * from dept where loc=‘aaa‘\G  #就不会使用到索引

#explain 可以帮助我们在不真正执行某个sql语句时,就执行mysql怎样执行,这样利用我们去分析sql指令.

explain select * from dept where dname=‘aaa‘\G  #就会使用到索引
  • 如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引, 我们建议大家尽量避免使用or 关键字;
  • select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45
    #上述条件出现的条件字段必须建立索引
  • 对于使用like的查询,查询如果是  ‘%aaa’ 不会使用到索引,利用‘aaa%就可以’;
  • explain select * from dept where dname like ‘%aaa‘\G
    #不能使用索引,即,在like查询时,关键的 ‘关键字’ , 最前面,不能使用 % 或者 _这样的字符., 如果一定要前面有变化的值,则考虑使用 全文索引->sphinx.
  • 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’),也就是,如果列是字符串类型,就一定要用 ‘’ 把他包括起来;
  • 如果mysql估计使用全表扫描要比使用索引快,则不使用索引。

explain工具介绍

explain 可以帮助我们在不真正执行某个sql语句时,就执行mysql怎样执行,这样利用我们去分析sql指令.

•Possible_key :该查询可以利用的索引,如果没有任何索引可以使用null

•Key                : Mysql 从 Possible_key 中所选择使用索引

•Key_len         :被选中使用索引的索引长度

•Rows             :显示MySQL认为它执行查询时必须检查的行数

•extra   :查询细节信息

No tables

Query语句中使用FROM DUAL 或不含任何FROM子句

Using filesort

当Query中包含 ORDER BY 操作,而且无法利用索引完成排序,

Impossible WHERE noticed after reading const tables: MYSQL Query Optimizer

通过收集统计信息不可能存在结果

Using temporary

某些操作必须使用临时表,常见 GROUP BY  ; ORDER BY

Using where

不用读取表中所有信息,仅通过索引就可以获取所需数据;

如何查看索引使用的情况

show status like ‘Handler_read%’;

大家可以注意:
handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高,说明查询低效。

时间: 05-31

MySQL索引与索引优化的相关文章

mysql 多列索引优化

Mysql所有的列都可以使用索引,.对相关列使用索引是提高SELECT操作性能的最佳途径.根据存储引擎定义每个表的最大索引数和最大索引长度.所有存储引擎支持每个表至少16个索引,总索引长度至少256字节.在索引中使用col_name(length)语法,可以创建一个只使用char和archar列的第一个length个字符的索引,按这种方式只索引列的前缀可以索引文件小的多.MyISAm和INNODb存储引擎还支持对blob和text列的索引,但是必须指定索引长度.fulltext索引用于全文搜索不

MySQL高级 之 索引失效与优化详解

案例所用的表结构.索引.与数据如下:   索引失效与优化 1.全值匹配我最爱 2.最佳左前缀法则(带头索引不能死,中间索引不能断) 如果索引了多个列,要遵守最佳左前缀法则.指的是查询从索引的最左前列开始 并且 不跳过索引中的列. 正确的示例参考上图. 错误的示例: 带头索引死:  中间索引断(带头索引生效,其他索引失效):  3.不要在索引上做任何操作(计算.函数.自动/手动类型转换),不然会导致索引失效而转向全表扫描 4.mysql存储引擎不能继续使用索引中范围条件(bettween.<.>

MYSQL索引分析和优化设计方案

一.什么是索引? 索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存.如果没有索引,执行查询时 MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录.表里面的记录数量越多,这个操作的代价就越高.如果作为搜索条件的列上已 经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置.如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍. 假设我们创建了一个名为people的表: CREATE TABLE people (

MySQL索引类型及优化

索引是快速搜索的关键.MySQL索引的建立对于MySQL的高效运行是很重要的.下面介绍几种常见的MySQL索引类型. 在数据库表中,对字段建立索引可以大大提高查询速度.假如我们创建了一个 mytable表: CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL ); 我们随机向里面插入了10000条记录,其中有一条:5555, admin. 在查找username="admin"的记录 SELECT *

mysql如何查看索引使用情况以及优化

mysql中支持hash和btree索引.innodb和myisam只支持btree索引,而memory和heap存储引擎可以支持hash和btree索引 我们可以通过下面语句查询当前索引使用情况: show status like '%Handler_read%'; +-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Handler_read_first | 0

(转)Mysql 索引原理及优化

本文内容主要来源于互联网上主流文章,只是按照个人理解稍作整合,后面附有参考链接. 一.摘要 本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题.特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等.为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引,至于哈希索引和全文索引本文暂不讨论. 二.常见的查询算法及数据结构 为什么这里要讲查询算

mysql如何查看索引使用情况以及优化 - guols0612

mysql中支持hash和btree索引.innodb和myisam只支持btree索引,而memory和heap存储引擎可以支持hash和btree索引 我们可以通过下面语句查询当前索引使用情况: show status like '%Handler_read%'; +-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Handler_read_first | 0

MySQL索引原理及优化

一.各种数据结构介绍 这一小节结合哈希表.完全平衡二叉树.B树以及B+树的优缺点来介绍为什么选择B+树. 假如有这么一张表(表名:sanguo): (1)Hash索引 对name字段建立哈希索引: 根据name字段值进行hash计算,定位到数组的下标,因为字段值所对应的数组下标是哈希算法随机算出来的,所以可能出现哈希冲突.其中每一个节点存储的是name字段值及对应的行数据地址,那么对于这样一个索引结构,现在来执行下面的sql语句:  select * from sanguo where name

MySQL索引优化(索引单表优化案例)

1.单表查询优化 建表SQL CREATE TABLE IF NOT EXISTS `article` ( `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `author_id` INT(10) UNSIGNED NOT NULL, `category_id` INT(10) UNSIGNED NOT NULL, `views` INT(10) UNSIGNED NOT NULL, `comments` INT(10) UNS

mysql索引原理及优化(二)

索引原理分析:数据结构 索引是最常见的慢查询优化方式其是一种优化查询的数据结构,MySql中的索引是用B+树实现,而B+树就是一种数据结构,可以优化查询速度,可以利用索引快速查找数据,优化查询. 可以提高查询速度的数据结构:哈希表.完全平衡二叉树.B树.B+树等等. 哈希:select* from sanguo where name>'周瑜     哈希表的特点是可以快速的精确查询,但是不支持范围查询.完全平衡二叉树:对于数据量大情况,它相比于哈希或者B树.B+树需要查找次数更多.B树:比完全平