查询（四）索引

痛定思痛。 2022-05-26 07:35 175阅读 0赞

一、背景：

SQL查询，如果表中的数据达到百万级甚至是千万级的话，查询可能会变得很慢，数据库性能优化一般有以下方法：

1） 分布式集群：部署大量数据库，每个数据库默认只记录部分数据，每隔一段时间，这些数据库的数据会同步一次。

2） 拆分表：将一张表的数据分时分段记录在不同的数据表中；  
3） 软件优化：优化SQL、索引。  
分布式数据库的设计及同步算法等知识点属于比较难一点的知识点了。优化SQL属于SQL的写法，这里先看索引，索引通俗来讲就相当于书的目录，当我们根据条件查询的时候，没有索引，便需要全表扫描。

二、mysql的索引类型（按照作用分）：

按照作用分，mysql的索引有5种：主键索引、普通索引（单列索引）、唯一索引、全文索引、聚合索引（多列索引）。

1、普通索引：是最基本的索引，它没有任何限制。它有以下几种创建方式：

（1）直接创建索引：

CREATE INDEX index_name ON table(column(length))

（2）修改表结构添加：

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

（3） 创建表时候添加：

CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) CHARACTER NOT NULL ,
        `content` text CHARACTER NULL ,
        `time` int(10) NULL DEFAULT NULL ,
        PRIMARY KEY (`id`),
        INDEX index_name (title(length))
    )

（4）删除索引：

DROP INDEX index_name ON table

2、唯一索引：与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

（1）创建表时添加：

CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) CHARACTER NOT NULL ,
        `content` text CHARACTER NULL ,
        `time` int(10) NULL DEFAULT NULL ,
        UNIQUE indexName (title(length))
    );

（2）直接创建：

CREATE UNIQUE INDEX indexName ON table(column(length))

（3）修改表结构添加：

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

3、主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) NOT NULL ,
        PRIMARY KEY (`id`)
    );

4、组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合，

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

5、全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

（1）创建表时候添加：

（2）修改表时候添加：

ALTER TABLE article ADD FULLTEXT index_content(content)

（3）直接创建：

CREATE FULLTEXT INDEX index_content ON article(content)

三、mysql的索引类型（按照类型分）：

1. FULLTEXT  
即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH  
由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询

3. BTREE  
BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是**MySQL里默认和最常用的索引类型，上述的普通索引、组合索引用的基本都是BTREE。**

4. RTREE  
RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种，相对于BTREE，RTREE的优势在于范围查找。

四、注意事项：索引并不是越多越好，索引会提高查询的效率，但是会降低插入（更新）操作的效率。

五、索引失效：首先未使用索引列作为查询条件索引是肯定会生效的，还有其他的情况，索引列做为了查询条件也失效了：

ALTER TABLE staffs ADD INDEX idx\_staffs\_nameAgePos(NAME, age, pos);

1、组合索引失效：如果索引了多列，要遵守最左前缀法则，Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但必须包含最左侧的一个。例如索引是index (a,b,c)，可以支持a | a,b| a,b,c |a,c这几种组合进行查找，但不支持 b|b,c|c进行查找 。如

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3J1YnVsYWk_size_16_color_FFFFFF_t_70_pic_center][]

注：

组合索引最好全值匹配(查询条件的顺序和索引的顺序一致，这种方式最好，能够充分发挥索引的作用，当然`使用and连接的查询提交也可以不与索引的顺序一致，mysql会自动优化，如index(a,b,c)，where a= and b= and c= 与where b= and c= and a=，...效果是一样的 `

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3J1YnVsYWk_size_16_color_FFFFFF_t_70_pic_center 1][]

2、列类型是字符串，查询条件未加引号；

3、使用like时通配符在前会导致索引失效，通配符在后面时效率不受影响：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3J1YnVsYWk_size_16_color_FFFFFF_t_70_pic_center 2][]

4、查询条件中使用or会使索引失效，要想是索引生效，需要将or中的每个列都加上索引；

5、对索引列进行计算、函数、(自动or手动)类型转换会导致索引失效，如where substr(a, 1, 3) = ‘hhh’、where a = a+1、DATE\_FORMAT函数等；

6、mysql 在使用不等于(!= )的时候无法使用索引，会导致全表扫描；

7、使用in查询，当in()括号里面只有一个时，索引有效；否则无效。如表zt\_test现有数据和索引如下：

![20200707151659106.png][]![20200707151706507.png][]

![20200707151622386.png][]

现在查询：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k_size_16_color_FFFFFF_t_70][]

当in的条件多于1个时，

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k_size_16_color_FFFFFF_t_70 1][]

8、出现在Order by 后的索引列都是用于排序的，不会用于查找，索引无效；group by 基本上都需要进行排序，会有临时表产生

小结：假设index(a,b,c)：

![20200416141256690.png][]

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>sql</td> 
   <td>有没有用到索引，有的话用到了哪些</td> 
  </tr> 
  <tr> 
   <td>where c=1 and b=1 and a='aaa'&nbsp;</td> 
   <td>&nbsp;用到了a,b,c</td> 
  </tr> 
  <tr> 
   <td>where a = 'aaa' &nbsp;and c=1</td> 
   <td>&nbsp;用到了a，没有用到c</td> 
  </tr> 
  <tr> 
   <td>where a like '%aaa%' and b=1 and c=1&nbsp;</td> 
   <td>&nbsp;没有用到</td> 
  </tr> 
  <tr> 
   <td>where a like 'aaa%' and b=1 and c=1&nbsp;</td> 
   <td>用到</td> 
  </tr> 
  <tr> 
   <td>where a ='aaa' and b like '1%' and c=1</td> 
   <td>用到了a和b， c在模糊查询的通配符之后，断了</td> 
  </tr> 
  <tr> 
   <td>where a = 111 and b =1 and c=1&nbsp;</td> 
   <td>没有用到</td> 
  </tr> 
  <tr> 
   <td>where a = 'aaa' or b =1 or c=1 &nbsp;</td> 
   <td>&nbsp;没有用到</td> 
  </tr> 
  <tr> 
   <td>where a!='aaa' and b =1 and c=1</td> 
   <td>&nbsp;没有用到</td> 
  </tr> 
  <tr> 
   <td>where (a+'aa')!='aaa'&nbsp;</td> 
   <td>&nbsp;没有用到</td> 
  </tr> 
  <tr> 
   <td>ORDER BY a&nbsp;</td> 
   <td>&nbsp;没有用到</td> 
  </tr> 
  <tr> 
   <td>GROUP &nbsp;BY a&nbsp;</td> 
   <td>用到</td> 
  </tr> 
 </tbody> 
</table>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3J1YnVsYWk_size_16_color_FFFFFF_t_70_pic_center]: /images/20220526/b916f40cd6194c839dac5741928dcf73.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3J1YnVsYWk_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20220526/7f1b41a1f55f46cb8ab0094491267c17.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3J1YnVsYWk_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20220526/46ff784dbedf4bc99eac34273899e1c6.png
[20200707151659106.png]: https://img-blog.csdnimg.cn/20200707151659106.png
[20200707151706507.png]: /images/20220526/2d4184dedb4d4c25aae9bef4f7546cec.png
[20200707151622386.png]: /images/20220526/e1c75c6d6c904471bf335abf0f81aa2e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k_size_16_color_FFFFFF_t_70]: /images/20220526/e7e99b61789742dd9b75d09df6f4594c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k_size_16_color_FFFFFF_t_70 1]: /images/20220526/c0b828b0009e4af7918c4a28a8aca6a1.png
[20200416141256690.png]: /images/20220526/cb784b0b7ea04aa599ccb0c097276ffd.png