欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL索引优缺点及使用讲解

程序员文章站 2022-07-05 23:06:05
索引 1. 什么是索引 创建在表上,是对数据库表中一列或多列的值进行排序的结构。 用于快速查询数据库表中的特定记录,可提高查询速度。 不同的存储引擎定...

索引


1. 什么是索引

创建在表上,是对数据库表中一列或多列的值进行排序的结构。
用于快速查询数据库表中的特定记录,可提高查询速度。

    不同的存储引擎定义了每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持 
    16个索引,总索引长度至少256字节。 
2.分类

2.1 聚簇索引和非聚簇索引

聚簇索引

数据的物理存放顺序和索引顺序是一致的
唯一性
聚簇索引的叶结点就是数据结点
主键默认设为聚簇索引;
InnoDB引擎按照聚簇索引存储数据。

非聚簇索引
索引顺序与数据物理排列顺序无关
一 个表对应的非聚簇索引可以有多条,根据不同列的约束可以建立不同要求的非聚簇索引;
非聚簇索引的叶结点是索引结点,有一个指针指向对应的数据块;
MyISAM使用的是非聚簇索引。

2.2 存储类型
索引有两种存储类型:B型(BTREE)树索引和哈希(HASH)索引。

InnoDB和MyISAM存储引擎支持BTREE索引。
HASH索引

特征如下:

    只用于使用=或<=>操作符的等式比较。
    优化器不能使用HASH索引来加速ORDER BY操作。
    MySQL不能确定在两个值之间大约有多少行。如果将一个MyISAM表改为HASH索引的MEMORY 
    表,会影响一些查询的执行效率。
    只能使用整个关键字来搜索一行。

BTREE索引

当时>、<、>=、<=、BETWEEN 、!= 或者 <> , 或者 LIKE‘pattern' 操纵符时,都可以使
用相关列上的索引。

下列范围查询适用于BTREE索引和HASH索引:

SELECT* FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);

下列范围查询适用于BTREE索引:

SELECT* FROM t1 WHERE key_col > 1 AND key_col < 10;

SELECT* FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'lisa' 
                              AND simon';
3.优点和缺点

优点
- 提高检索速度;
- 对于有依赖关系的子表和父表之间的联合查询时,可提高查询速度;
- 使用分组和排序子句进行数据查询时,显著节省查询中分组和排序的时间。

缺点
- 创建和维护索引需要耗费时间,耗费时间的数量随着数据量的增加而增加;
- 索引需要占用物理空间;
- 增加、删除和修改数据时,要动态的维护索引,造成数据的维护速度降低了。

4. 索引分类

1)普通索引:不附加任何限制条件,可创建在任何数据类型中,其值是否唯一和非空由字段本 身的完整性约束条件决定。

2)唯一性索引:使用UNIQUE参数设置索引。索引的值是唯一的

3)全文索引:使用FULLTEXT参数设置索引。只能创建在CHAR、VARCHAR或TEXT类型字段上。查询数据量较大的字符串类型的字段时,使用全文索引可提高查询速度。

4)单列索引:在表中的单个字段上创建索引。只根据该字段进行索引(索引对应一个字段)。单列索引可以是普通索引或者唯一性索引或者全文索引。

5)多列索引:在表的多个字段上创建一个索引。可通过几个字段进行查询。
只有查询条件使用多列关键字最左边的前缀时,才可以使用索引,否则将不能使用索引。

6)空间索引:使用SPATIAL参数设置。
只能建立在空间数据类型上,可提高系统获取空间数据的效率。
只有MyISAM存储引擎支持空间检索,索引字段不为空


5. 索引的设计原则

1)选择唯一性索引。索引的列的基数越大,索引效果越好。

2)为经常需要排序、分组和联合操作的字段建立索引(避免排序操作,浪费时间)

3)为常作为查询条件的字段建立索引(提高查询速度)

4)限制索引的数目,不要过度索引(每个索引都需要占用磁盘空间,会降低写操作的性能。)

5)尽量使用短索引。如果对字符串进行索引,应该指定一个前缀长度。

6)尽量使用左前缀来索引(字段值很长,使用值的前缀索引)

7)删除不再使用或者很少使用的索引(减少索引对更新操作的影响)

8)对于InnoDB存储引擎的表,记录默认会按照一定的顺序保存。
InnoDB表的普通索引会保存主键的键值,主键应选择较短的数据类型,减少索引的磁盘占用,提高索引的缓存效果。

9)辅助索引,叶子结点存放着索引字段的值及对应的主键值

10)对查询where条件中区分度高的字段加索引;对查询分组和排序分组加索引。

11)一下情况无法使用到索引:like通配符在最左,not in, !=, <>, 队列做函数运算隐式数据类型转换,OR子句

12)FORCE INDEX强制加索引


6. 创建索引

创建表示可直接创建索引

1)创建普通索引

    创建一个表名为index1的表,在表中的id字段上建立索引。                      
CREATE TABLE index1(id  INT , name  VARCHAR(20),INDEX(id) );

2)创建唯一性索引

CREATE TABLE index2 (id  INT UNIQUE, name VARCHAR(20),   
                      UNIQUE INDEX index2_id(id  ASC)  );

3)创建全文索引(只有MyISAM存储引擎支持全文索引)

创建一个表名为index3的表,在表中的info字段上建立名为index3_info的全文索引。 
CREATE  TABLE index3 (id  INT, info  VARCHAR(20),        
                      FULLTEXT INDEX index3_info(info) )ENGINE=MyISAM; 

4)创建单列索引(单个字段)

创建一个表名为index4的表,表中的subject字段上建立名为index4_st的单列索引。 
CREATE  TABLE  index4(id  INT, subject VARCHAR(30),
                        INDEX index4_st(subject(10)) );

5)创建多列索引(多个字段,只有使用了第一个字段时才会触发索引

创建表名为index5的表,在表中的name和sex字段上建立名为index5_ns的多列索引。 
CREATE  TABLE  index5(id  INT,  name  VARCHAR(20), sex  CHAR(4), 
                        INDEX  index5_ns(name, sex)  );

6)创建空间索引

在index6表中的space字段上建立名为index6_sp的空间索引。            
    CREATE  TABLE  index6(id  INT,space GEOMETRY NOT NULL,
                           SPATIAL INDEX index6_sp(space) )ENGINE=MyISAM;
7. 在已存在的表上建立索引
CREATE UNIQUE INDEX index_id ON index(course_id);

1)创建普通索引

 CREATE INDEX index7_id ON  example0(id);   
 //id字段上建立索引

2)唯一性索引

 CREATE UNIQUE  INDEX index8_id  ON  index8(course_id); 
 //索引为index8_id

3)全文索引

CREATE  FULLTEXT INDEX index9_info  ON  index9(info);  

4)单列索引

  CREATE INDEX index10_addr  ON  index10(address(4));
  //查询address前4个字符

5)创建多列索引

CREATE INDEX index11_na  ON index11(name,address);  
//必须有name字段

6)空间索引

CREATE SPATIAL INDEX  index12_line  ON  index12(line);
8. 用ALTER TABLE 语句创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT |SPATIAL] INDEX
                    索引名(属性名 [(长度)] [ASC|DESC]);

1)创建普通索引

    在s1表中name字段上建立index_name的索引。如下:
ALTER TABLE s1 ADD INDEX index_name(name(20)); 

2)唯一性索引

在s2表中course_id字段上,建立index_id的唯一性索引。如下:
ALTER TABLE s2 ADD UNIQUE INDEX index_id(course_id);

3)全文索引

    在s3表中的info字段上建立名为index_info的全文索引。如下:
ALTER TABLE s3 ADD FULLTEXT INDEX index_info(info);  

4)单列索引

    在s4表中的address字段上建立名为index_addr的单列索引。
ALTER TABLE s4 ADD INDEX index_addr(address(4));

5)创建多列索引

    在s5表中的name和address字段上建立名为index_na索引
ALTER TABLE s5 ADD INDEX index_na(name,address);

6)空间索引

    在s6表中的line字段上建立名为index_line的多列索引。
ALTER TABLE s6 ADD SPATIAL INDEX index_line(line);

9. 删除索引
DROP INDEX 索引名 ON 表名;
查看索引使用情况
show status like 'Handler_read%';   

Handler_read_rnd_next的值较高意味着查询运行抵消。

11. 索引优化
定期分析表和检查表
ANALYZE table tbl_name;     //表分析
CHECK table tbl_name;       //表检查
定期优化表
OPTIMIZE table tbl_name;