mysql索引及优化

2021-02-25 10:24:26 浏览数 (1)

1.mysql索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

2.索引类型

普通索引，唯一索引，主键索引，全文索引。

无论任何类型，都是通过建立关键字与位置的对应关系来实现的。

以上类型的差异：对索引关键字的要求不同。

代码语言：javascript复制
添加修改索引语法：

ALTER TABLE tbl_name ADD INDEX index_name (column_list(length)): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

删除索引语法：
ALTER TABLE tbl_name drop INDEX 索引名;

查看索引
SHOW INDEX FROM tbl_name

3.普通索引（index）

这是最基本的索引，它没有任何限制，比如上文中为title字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

代码语言：javascript复制
–修改表结构的方式添加索引
ALTER TABLE tbl_name ADD INDEX index_name (column_list(length))

例：
CREATE TABLE mytable( 
    ID INT NOT NULL,    
    name VARCHAR(16) NOT NULL,   
    INDEX index_name (name(length))   
);  

ALTER TABLE mytable ADD INDEX index_name ON (name(length))

4.唯一索引（unique index）

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

代码语言：javascript复制
–修改表结构的方式添加索引
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

例：
CREATE TABLE mytable( 
    ID INT NOT NULL,    
    name VARCHAR(16) NOT NULL,   
    UNIQUE unique_name (name(length))   
);  

ALTER TABLE mytable ADD UNIQUE index_name ON (name(length))

5.主键索引（primary key）

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

代码语言：javascript复制
--添加修改索引
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
CREATE TABLE mytable(   
    ID INT NOT NULL,    
    name VARCHAR(16) NOT NULL,   
    PRIMARY KEY(ID)   
);

当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

6.全文索引（fulltext key）

MySQL从3.23.23版开始支持全文索引和全文检索；

代码语言：javascript复制
--修改表结构的方式添加索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
--创建表全文索引
CREATE TABLE mytable(   
    ID INT NOT NULL,    
    name VARCHAR(16) NOT NULL,   
    content TEXT 
    PRIMARY KEY(ID),
    FULLTEXT(content)
);
ALTER TABLE mytable ADD FULLTEXT index_content(content)

使用全文索引常见的错误：

代码语言：javascript复制
select * from mytable where content like "%mysql%";

这里并不会使用全文索引，可以用explain进行查看。正确用法：

代码语言：javascript复制
select * from mytable where match(title,content) against ('MYSQL');

备注：

  • 在mysql中fulltext 索引只针对 myisam生效
  • mysql自己提供的fulltext针对英文生效->sphinx(coreseek)技术处理中文
  • 使用方法是 match(字段名..) against(‘关键字’)

7.组合索引（非mysql索引类型）

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。

例如上表中针对title和time建立一个组合索引：ALTER TABLE mytable ADD INDEX index_name_content (name(50),content(10))。建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：

  • name,content
  • name

为什么没有content这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示：

代码语言：javascript复制
–使用到上面的索引

SELECT * FROM mytable WHREE name='测试' AND content='123345';
SELECT * FROM mytable WHREE name='测试';

–不使用上面的索引
SELECT * FROM mytable WHREE content='123345';

8.执行计划

可以使用explain语句去查看分析结果，而不是真正执行该语句。

其中，

  • select_type
代码语言：javascript复制
simple：表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个。
primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个。
subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响。
derived：from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select。
union：union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union。
dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null。
  • table：表名
  • type
代码语言：javascript复制
依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL。
除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。
system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index
const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描。
eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref。
ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。
fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。
ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。
unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值。
index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能都不如range。
index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。
  • possible_keys：查询可能使用到的索引都会在这里列出来。
  • key：查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。
  • key_len：

用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。

  • ref：如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。
  • rows：这里是执行计划中估算的扫描行数，不是精确值。
  • extra：这个列可以显示的信息非常多，有几十种，常用的有：
代码语言：javascript复制
distinct：在select部分使用了distinc关键字
no tables used：不带from字句的查询或者From dual查询。
使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。
using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中。
using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。
using_union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集
using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集
using sort_union和using sort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。
using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition
using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。
firstmatch(tb_name)：5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个
loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个
filtered：使用explain extended时会出现这个列，5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

9.MySQL索引的优化

  1. 对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。

比如我们对title,content 添加了复合索引 select * from table_name where title = 'test';会用到索引 select * from table_name where content = 'test';不会用到索引

  1. 对于使用like的查询，查询如果是 ‘%a’不会使用到索引 ,而 like 'a%'就会用到索引。最前面不能使用%和_这样的变化值
  2. 如果条件中有or，即使其中有条件带索引也不会使用,对于索引字段推荐使用union替换or。
  3. 索引不会包含有NULL值的列
  4. 使用短索引。如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。
  5. 索引列排序。MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
  6. 不要在列上进行运算。例如：select from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select from users where adddate<’2007-01-01′。
  • 12 小时前发布
全文检索 sql 数据库 云数据库SQLServer

0 人点赞