Mysql 如果有多个可选条件怎么加索引_MySQL|mysql-索引

太过爱你忘了你带给我的痛 2023-10-01 08:38 17阅读 0赞

## 1、索引是什么 ##

### 1.1索引简介 ###

索引是表的目录，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，以及快速定位查询数据。对于索引，会保存在额外的文件中。

### 1.2主要注意的地方： ###

(1).索引可以提高查询速度，会减慢写入速度，索引的缺点时创建和维护索引需要耗费时间。

(2).索引并不是越多越好，索引固然可以提高相应的select的效率，但同时也降低了insert及update的效率，因为insert或update是有可能会重建索引。

### 1.3什么样的字段适合创建索引 ###

(1).**表的主键、外键必须有索引；外键是唯一的，而且经常会用来查询**。

(2).**经常与其他表进行连接的表，在连接字段上应该建立索引**。

(3).数据量超过300的表应该有索引。

(4).重要的SQL或调用频率高的SQL，比如经常出现在where子句中的字段，order by,group by, distinct的字段都要添加索引。

(5).**经常用到排序的列上，因为索引已经排序**。

(6).**经常用在范围内搜索的列上创建索引**，因为索引已经排序了，其指定的范围是连续的。

### 1.4什么场景不适合创建索引 ###

1.**对于那些在查询中很少使用或者参考的列不应该创建索引**，这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

2.**对于那 些只有很少数据值的列也不应该增加索引**。因为本来结果集合就是相当于全表查询了，所以没有必要。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比 例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

3.**对于那些定义为text, image和bit数据类型的列不应该增加索引**。这是因为，这些列的数据量要么相当大，要么取值很少。

4、**当修改性能远远大于检索性能时，不应该创建索引**。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因 此，当修改性能远远大于检索性能时，不应该创建索引。

5.**不会出现在where条件中的字段不该建立索引**。

6.**如果列均匀分布在 1 和 100 之间，却只是查询中where key\_part1 > 1 and key\_part1 < 90不应该增加索引**

### 1.5索引使用以及设计规范 ###

1、越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂

2、尽量避免null：应该指定列为not null， **含有空值的列很难进行查询优化**，因为它们使得索引、索引的统计信息以及比较运算更加复杂，**也可能导致复合索引无效**

**3、主键选择策略**

1.  每个表必须显示指定主键；
2.  主键尽量为一个字段，且为数字类型,避免使用字符串；
3.  主键尽量保持增长趋势，建议使用id的生成器；

4)主键尽量杜绝联合索引

**4、每个表的索引个数尽量少于5个，避免创建重复冗余索引；每个组合索引尽量避免超过3个字段，索引不是越多越好，谨慎添加索引，综合考虑数据分布和数据更新**

**5、重要的SQL或调用频率高的SQL**

1.  update/select/delete的where条件列字段都要添加索引；
2.  order by , group by, distinct的字段都要添加索引

**6、避免出现index merge(单索引or的查询)；合理利用covering index**

**7、组合索引创建时，把区分度(选择性)高的字段放在前面；根据SQL的特性，调整组合索引的顺序**

**8、对于varchar字段加索引，建议使用前缀索引，从而减小索引大小**

## 2、索引的创建和删除 ##

### 2.1创建数据表时创建索引的基本语法结构： ###

create table 表名(
    
    字段名1 数据类型 [约束条件...]
    
    字段名2 数据类型 [约束条件...]
    
    ...
    
    [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名] (被用作索引的字段名[(长度)] [ASC|DESC])
    
    );

\#\[\]中的内容可加可不加，看具体情况； |:表示’或者’ 字段名就是表的列名

### 2.2直接创建索引的基本语法结构: ###

create [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名] on 表名 (被用作索引的字段名[(长度)] [ASC|DESC])
    
    );

### 2.3修改表结构的方式添加索引的基本语法: ###

alter table 表名 add 索引格式;

### 2.4删除索引: ###

drop index 索引名 on 表名;

### 2.5以上出现的索引属性名的含义： ###

UNIQUE：(unique)，可选参数，表示索引为唯一索引。

FULLTEXT：(fulltext) ，可选参数，表示索引为全文索引。

SPATIAL：(spatial) ，可选参数，表示索引为空间索引。

INDEX | KEY：(index | key)， 必选参数，用于指定字段为索引的，用户在选择时，只需要二选一即可。

\[索引名\]：可选参数，其作用是给创建的索引取新名称。(起到方便使用的目的)

被选定的字段名：必选参数，被用作索引的对应的字段名称，该字段必须被预先定义。

长度：可选参数，其指索引的长度，必须是字符串类型才可以使用。(比如：电话号码)

\[ASC | DESC\]：(asc | desc)，可选参数，ASC 表示升序排列，DESC 表示降序排列。

## 3、索引类型 ##

索引有很多种类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务层。所以，并没有统一的索引标准：不同的存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。

### 3.1普通索引 ###

**创建普通索引，不需要添加 \[UNIQUE | FULLTEXT | SPATIAL \] 等任何参数进行约束。**

*  普通索引 (由关键字KEY或INDEX定义的索引) 的唯一任务是加快对数据的访问速度。
 *  只为那些最经常出现在‘查询条件’(WHERE column = …) 或‘排序条件’(ORDER BYcolumn)中的数据列，来创建索引。
 *  只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个int整数类型的数据列)来创建索引。

建表语句:

create table person(
    
    id int(11) not null auto_increment,
    
    name varchar(20) default null,
    
    primary key (id),
    
    key (name) using btree
    
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

添加索引:

create index name on person(name);

### 3.2唯一索引(主键) ###

**创建唯一索引时，使用 UNIQUE 参数对 INDEX | KEY 进行约束。**

*  与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值(注意和主键不同)。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。
 *  如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。
 *  事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。
 *  主索引：在前面已经反复多次强调过！必须为主键字段创建一个索引，这个索引就是所谓的"主索引"。
 *  主索引 与 唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。

eg:创建的表名为 address 的数据表，并在该表的 id 字段上建立名称为 address\_id 的唯一索引，

建表语句:

create table address(
    
    id int(11) auto_increment primary key not null,
    
    name varchar(50),
    
    address varchar(200),
    
    UNIQUE INDEX address_id (id ASC) # id 字段设为唯一索引，并赋予新名字address_id ,且ASC升序排列
    
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

### 3.3全文索引 ###

**全文索引只能作用在 CHAR、VARCHAR、TEXT、类型的字段上。创建全文索引需要使用 FULLTEXT 参数进行约束。**

*  MySQL从3.23.23版开始支持全文索引和全文检索，fulltext索引仅可用于 MyISAM表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE或CREATE INDEX被添加。
 *  对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
 *  文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE%word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。
 *  这类场合正是全文索引(full-textindex)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用命令添加。
 *  有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法： SELECT \* FROM table\_name
    
     *  WHERE MATCH(column1, column2)
     *  AGAINST(‘word1’, ‘word2’, ‘word3’)
     *  上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。

eg:创建的表名为 cards 的数据表，并在该表的 name 字段上建立名称为 cards\_number 的全文索引

建表语句:

create table cards(
    
    id int(11) auto_increment primary key not null ,
    
    name varchar(20) default null,
    
    number bigint(11),
    
    info varchar(50),
    
    FULLTEXT KEY cards_number (name) # name字段设为全文索引，并赋予新名字cards_number
    
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

### 3.4单列索引 ###

**创建单列索引，即在数据表的单个字段上创建索引。创建该类型索引不需要引入约束参数，用户在建立时只需要指定单列字段名，即可创建单列索引。**

*  多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

eg:创建的表名为 telephone 的数据表，并在该表的 tel 字段上建立名称为 tel\_num 的单列索引

create table telephone(

id int(11) primary key auto_increment not null,
    
    name varchar(10) not null,
    
    tel varchar(50) not null,
    
    index tel_num (tel(20)) # tel字段设为单列索引，并赋予新名字tel_num,由于tel字段是字符串类型，因此可以设定'索引长度20'
    
    );

### 3.5多列索引 ###

**创建多列索引，即在数据表的多个字段上创建索引。与上述单列索引类似，创建该类型索引不需要引入约束参数。**

*  多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

eg:创建的表名为 information 的数据表，并在该表的 name 和 sex 字段上建立名称为 info 的多列索引

create table information(
    
    inf_id int(11) auto_increment primary key not null,
    
    name varchar(20) not null,
    
    sex varchar(5) not null,
    
    birthday varchar(50) not null,
    
    index info (name,sex) # name,sex字段设为多列索引，并赋予新名字info
    
    );

*  **注意：在多列索引中，只有查询条件中使用了这些字段中的第一个字段(即上面示例中的 name 字段)，索引才会被使用。触发多列索引的条件是用户必须使用索引的第一字段，如果没有用到第一字段，则索引不起任何作用，用户想要优化查询速度，可以应用该类索引形式。**

### 3.6空间索引 ###

**创建空间索引，需要添加 SPATIAL 参数进行约束。同样，必须说明的是，只有 MyISAM 类型的表支持该类型 ‘ 空间索引 ’。而且，索引字段必须有非空约束。**

eg:创建的表名为 list 的数据表，并在该表的 goods 字段上建立名称为 listinfo 的空间索引，这里 goods 字段有非空约束，

create table list(
    
    id int(11) primary key auto_increment not null,
    
    goods geometry not null,
    
    SPATIAL INDEX listinfo (goods) # 这里goods字段有非空约束，符合条件 goods字段设为空间索引，并赋予新名字listinfo
    
    )engine=MyISAM;

注意： goods 字段上已经建立名称为 listinfo 的空间索引，其中 goods 字段必须不能为空，且数据类型是 GEOMETRY，该类型是空间数据类型。

空间类型不能用其他类型代替，否则在生成空间素引时会产生错误且不能正常创建该类型索引。

外键索引：如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

另外：INDEX | KEY：(index | key)， 必选参数，用于指定字段为索引的，用户在选择时，只需要二选一即可。

区别：

1). key ：是数据库的物理结构，它包含两层意义：一是约束(偏重于约束和规范数据库的结构完整性)、二是索引(辅助查询用的)。包括primary key, unique key, foreign key 等。primary key 有两个作用，一是约束作用(constraint)，用来规范一个存储主键和唯一性，但同时也在此key上建立了一个index；unique key 也有两个作用，一是约束作用(constraint)，规范数据的唯一性，但同时也在这个key上建立了一个index；foreign key也有两个作用，一是约束作用(constraint)，规范数据的引用完整性，但同时也在这个key上建立了一个index；

2). index：是数据库的物理结构，它包含一层意义：它只是索引(辅助查询用的)，它创建时会在另外的表空间(mysql中的innodb表空间)以一个类似目录的结构存储。因此，索引只是索引，它不会去约束索引的字段的行为(那是key要做的事情)。

### 3.7聚簇索引 ###

1.聚簇索引并不是一种单独索引，而是一种数据储存方式。InnoDB 的聚簇索引实际上在同一结构中保存了B-Tree 索引和数据行。

2.当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。“聚簇”表示数据行和相邻的键值紧凑的储存在一起。

3.对应InnoDB 来说如果表没有定义主键，会选择一个唯一的非空索引代替。如果没有这样的索引InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一页面中的记录。

4.聚簇索引的优势：

(1)可以把相关数据保存在一起。(2)数据访问更快。数据和索引保存在同一个 B-Tree 。(3)使用覆盖索引扫描的查询可以直接使用页节点的主键值

5.聚簇索引的缺点：

(1)聚簇索引最大的提高了I/O密集型应用的性能，但如果数据全部都放到内存中，则数据的顺序就没有那么重要了，聚簇索引也就没什么优势了。(2)插入速度严重依赖插入顺序。按照主键插入的方式是InnoDB 速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载后最好使用OPTIMIZE TABLE 命令重新组织一2下表(3)更新聚簇索引列的代价很高。因为会强制InnoDB 将每个被更新的行移动到新的位置

6.二级索引

主键索引的叶子节点存的是整行数据，在InnoDB 里，主键索引也被称为聚簇索引非主键索引的叶子节点内容是主键的值。在InnoDB 里。非主键索引也被称为二级索引。如：select\* from order where user\_id=3; user\_id是普通索引。则会先搜索user\_id 的索引树，得到id=5,再到id 索引树搜索一次，这个过程就是 “回表”。也就是说非主键索引需要查询2次

### 3.8覆盖索引 ###

1.mysql 可以使用索引直接来获取列的数据，这样就可以不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？如果一个索引包含(覆盖)所有要查询的字段的值，那么就称为“覆盖索引”

2.覆盖索引可以提高查询的性能，不需要回表，好处是：

(1)索引条目通常小于数据行，如果只需读取索引，那么mysql 就会减少访问量(2)索引是按照列值顺序存储的，索引I/O 密集型的范围查询会比随机从磁盘读取每一行数据的I/O 要少得多(3)一些存储引擎如MyISAM 在内存只缓存索引，数据则依赖操作系统来缓存，因此要访问数据需要一次系统调用，这可能导致严重的性能问题，尤其是那些系统调用占了数据访问中最大开销的场景(4)InnoDB 的聚簇索引，覆盖索引对InnoDB 表的特别有用。InnoDB 的二级索引在叶子节点保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

1.  select id from order where user\_id between 1 and 3这时候只需要查ID 的值，而ID 已经在user\_id 索引树上，因此可以直接提供查询结果，不需要回表。

select \* from order where user\_id between 1 and 3一旦用了select \*，就会有其他列需要读取，这时在读完index以后还需要去读data才会返回结果。

这两种处理方式性能差异非常大，特别是返回行数比较多，并且读数据需要 I/O 的时候，可能会有几十上百倍的差异。因此建议根据需要用select \*

## 4、索引原理 ##

### 4.1索引实现的数据结构 ###

Mysql对于不同的存储引擎，索引的实现实现方式是不同的。主流的存储引擎：MyISAM和InnoDB，两种存储引擎都使用B+Tree(B-Tree的变种)作为索引结构，但是在实现方式上，却有很大的不同。下面是两种BTree数据结构：

B-tree结构：

![120615245bf00511a413809952b12fb5.png][]

B-Tree无论是叶子结点还是非叶子结点，都含有key和一个指向数据的指针，只要找到某个节点后，就可以根据指针找到磁盘地址从而找到数据。

B+tree结构:

![31e75da375b80a8c233d118f0082cf5f.png][]

B+Tree所有叶子结点才有指向数据的指针。非叶子结点就是纯索引数据和主键。每个叶子结点都有指向下一个叶子结点的链接。

**小结：非叶子结点存放在内存中，也叫内结点，因此，在有限的内存中， B-Tree中每个数据的指针会带来额外的内存占用，减少了放入内存的非叶子结点数；B+Tree则尽可能多地将非叶子结点放入内存中**

### 4.2MySQL中索引实现 ###

由于B+Tree数据结构的优势，目前mysql基本都采用B+Tree方式实现索引，下面列出了两个最常用的存储引擎的索引实现：

1、MyISAM：如下图，叶子结点的data域存放的是数据的地址：

![4189931a385d9d03c07f77d9a4907d41.png][]

上图表中共三列数据，col1为主键，表示MyISAM表的主索引示意图，在MyISAM中，主索引和辅助索引(除主键以外的其它索引)在结构上没有任何区别，只是主索引的key是唯一的，辅助索引的key可以重复。

2、InnoDB：对比MyISAM，InnoDB的主键索引与辅助索引存储方式是不同的：

主键索引：主键索引的叶子结点存放的是key值和数据，叶子结点载入内存时，数据一起载入，找到叶子结点的key，就找到了数据。

![9f2c482c2565b341fab381c00f307f3e.png][]

辅助索引：辅助索引的叶子结点存放的是key值和对应的记录的主键值，使用辅助索引查询，首先检索辅助索引获取主键，然后用主键在主索引中检索获取记录。

![856360ee476d24a3299d596a9af2f4d4.png][]

**小结：MyISAM索引叶子节点存放的是数据的地址，主键索引与辅助索引除了值得唯一性在结构上完全一样。InnoDB索引叶子节点存放的内容因索引类型不同而不同，主键索引叶子节点存放的是数据本身，辅助索引叶子节点上存放的是主键值。**

[120615245bf00511a413809952b12fb5.png]: https://img-blog.csdnimg.cn/img_convert/120615245bf00511a413809952b12fb5.png
[31e75da375b80a8c233d118f0082cf5f.png]: https://img-blog.csdnimg.cn/img_convert/31e75da375b80a8c233d118f0082cf5f.png
[4189931a385d9d03c07f77d9a4907d41.png]: https://img-blog.csdnimg.cn/img_convert/4189931a385d9d03c07f77d9a4907d41.png
[9f2c482c2565b341fab381c00f307f3e.png]: https://img-blog.csdnimg.cn/img_convert/9f2c482c2565b341fab381c00f307f3e.png
[856360ee476d24a3299d596a9af2f4d4.png]: https://img-blog.csdnimg.cn/img_convert/856360ee476d24a3299d596a9af2f4d4.png