2019-04-24  223 views 发表评论

mysql索引总结

 标签:

概述:

索引(index)是数据库技术中的一个重要的概念和技术,也是MySQL的一个数据库对象。

对数据库中得数据进行检索时,系统对表得数据主要又两种搜索扫描方式:(1)全表扫描、检索;(2)利用数据表上建立得索引进行扫描。

全表扫描是将表中所有记录从头至尾逐行读取,与查询条件进行对比,返回满足条件得记录,这种方式因为要读取所有表中相关得记录,所以需要进行大量得磁盘读写操作,当表中数据量巨大时,查询效率会大大降低。

索引扫描是通过搜索索引值,再根据索引值与记录得关系直接访问数据表中的记录行,这种方式在数据量巨大时能有效加快数据检索得速度。

索引分类

根据用途主要分为普通索引,唯一索引,主键索引,聚簇索引,全文索引。

(1)普通索引

普通索引(index)是最基本的索引类型,索引列值可取空值或者重复值,创建索引时的关键字时index,key。

(2)唯一索引

唯一索引(unique)与普通索引基本相同,区别仅在于索引列值不能重复,必须时唯一的,但可以时空值,创建唯一索引使用关键字unique。

(3)主键索引

主键索引(primary key)在MySQL中建立主键是系统会自动创建主键索引,它是一种唯一性索引,但与唯一索引不同的是主键索引的列值不能为空值,一般在创建表的时候指定主键,也可以通过修改表的方式添加主键,每个表只能有一个主键;MySQL中创建表时,凡是被定义为主键的系统会自动建立相应字段的索引。创建主键索引时使用关键字primary key。

(4)聚簇索引

聚簇索引的索引顺序就是数据存储的物理存储顺序,这样能保证索引值相近的元组所存储的物理位置也相近,对于非聚簇索引,索引顺序与数据的物理排序无关;一个表只能有一个聚簇索引,MySQL存储引擎中支持聚簇索引的是solidDB和innoDB。

(5)全文索引

全文索引(fulltext)只能创建在数据类型为varchar和text的列上。建立全文索引后,能够在建立全文索引的列上进行全文查找。全文索引只能在MyISAM存储引擎的表中创建

在索引的实际使用中,索引可以建立在单一列上,成为单列索引。也可以建立在多个列上,称为组合索引。

(1)单列索引

单列索引就是一个索引只包含表中的一个列。一个表上可以建立多个单列索引。如:学生表中建立了学号的索引,还可以建立姓名、所属班级的索引等。

(2)组合索引

组合索引也称复合索引或多列索引,组合索引是指在表的多个列上创建一个索引。如:在班级表中所属院系和年级两列上创建一个索引,即为组合索引。其含义是先按所属院系排序,若是院系相同,则按照年级排序。这也就是最左侧缀法则。

实操

一. 查看索引

语法格式:show {index|indexs|keys} {from|in} tbl_name  [{from|in} db_name]

eg:show index from db_school.tb_name;

解析:

table:索引所在的表名。

Non_unique:该索引是否不是唯一索引,不是则值为1,是则为0。

Key_name:索引的名称,在创建索引的语句中使用primary key关键字,在没有明确给出索引名得情况下系统会知道一个名称:“primary”。

Column_name:建立索引得列名称。

Collation:以何种顺序(升序或降序)索引。如果是升序,该列值显示A;如过该列值显示NULL,表示无分类。

二. 创建索引

1.create table (创建数据表时创建)

#普通索引:

create table student1(

    studentNo char(10) not null,

    studentName varchar(20) not null,

    sex char(3) not null,

    INDEX(studentName)

);

#唯一索引:

create table student2(

    studentNo char(10) not null UNIQUE,

    studentName varchar(20) not null,

    sex char(3) not null

);

#主键索引

CREATE TABLE `score2` (

  `studentNo` char(10) NOT NULL,

  `courseNo` char(5) NOT NULL,

  `score` float DEFAULT NULL,

  PRIMARY KEY (`studentNo`,`courseNo`),

  KEY `FK_score2` (`courseNo`)

);

2.create index (在已存在表上创建)

#语法

CREATE [UNIQUE] INDEX index_name on tbl_name (col_name[(length)] [ASC | DESC], ...);

    

#普通索引

create index index_stu on tb_student(studentNo);

create index index_course on tb_course(courseName(3) desc); ---按课程名courseName字段值前三个字符建立降序排序,mysql中值如果是中文则按照汉语拼音对应的字母顺序排序

create index index_book on tb_book(classNo,bookName desc); ---组合索引

3.alter table (修改表的方式创建)

#语法

CREATE TABLE tbl_name ADD [UNIQUE | FULLTEXT] [INDEX | KEY] [index_name] (col_name[length][ASC|DESC], ...);

create table tb_student add index idx_studentName(studentName);

三. 删除索引

1.drop index

#语法

DROP INDEX index_name ON tb_name;

drop index idx_studentName on tb_student;

2.alter table

#语法

ALTER TABLE tbl_name DROP INDEX index_name;

alter table tb_student drop index idx_studentName; ---使用drop constraint子句能删除表中的主键或外键约束,同时删除主键和外键的索引

MySQL索引方式

1.hash

哈希索引只能做等值查找,无论多大的Hash表,查找复杂度都是O(1)。

2.btree

具有范围查找和前缀查找的能力,对于有N节点的B树,检索一条记录的复杂度为O(LogN)。相当于二分查找。

使用索引注意事项

1.合理创建索引,索引虽能数据检索效率,但过多创建会降低系统性能,降低更新速度,占据不必要的存储空间。

2.数据量较小时最好不要创建索引。

3.使用组合索引时,严格遵循最左侧前缀法则,即先按照第一列(最左字段)进行排序,当第一列的值相同的情况下对第二列排序,以此类推。

4.创建索引的对象最好是经常使用、有较多不同值得字段,避免在不同值较少得字段上创建;如:‘sex’ 字段得值只有两个:男或女,在此类字段上创建索引不仅不会提高查询效率,反而会降低更新速度。

5.为了提高索引得效率,若使用CHAR或VARCHAR列的得字符多很多,则可视具体情况选取字段前N个字符进行索引,即对索引列得前缀建立索引,这样可节约存储空间。

6.在where子句中尽量避免讲索引作为字段作为表达式得一部分。在使用LIKE是,避免在开头使用通配符,如:“LIKE %aaa%”,这样会使索引失效,但是“LIKE aaa%”子句可以使用索引。

7.如果列的类型是字符串,则必须使用引号,否则不会使用索引。如:phone=18866669999不会使用索引,phone=‘18866669999’则会使用索引。

8.如果语句条件中有OR,且其中有一个列没有索引,则所有条件有索引得列都不会使用索引,除非OR条件中每个列都有索引

给我留言

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: