MySQL 索引与索引优化

发布于 2021-01-23 00:12:21   阅读量 56  点赞 0  

 索引可以提高数据库的检索速度,分为 单列索引组合索引

 在创建索引时,需要确保该索引会频繁应用在 SQL 查询语句的条件中(一般用在WHERE子句中)。

 由于索引是以索引文件的形式保存的,故建立索引会占用磁盘空间;此外,当对表进行更新操作时(INSERTUPDATEDELETE),数据库还需同步更新索引文件,故滥用索引会造成效率低下。


一、索引的使用

1. 索引类型

 MySQL 中有四种不同的索引:

  1. 普通索引:最基本的索引,没有任何限制;

  2. 唯一索引:唯一索引可以用来保证数据的唯一性,MySQL 将拒绝插入重复的唯一索引。索引的列值必须唯一,但允许有空值(只能有一个,多个 NULL 被视为重复);

  3. 主键索引:用以唯一表示表中的某一条记录,保证数据的完整性。不能重复,不允许为空

  4. 全文索引:用于解决关键字搜索问题。全文索引处理大量数据时,比like %快很多倍。


2. 查看索引

SHOW INDEX FROM table_name;


3. 创建索引

 有两种方式可以创建索引:CREATE INDEXALTER TABLE,其中CREATE只能用于创建普通索引与唯一索引;而ALTER TABLE还能用于创建主键索引与全文索引。

 在字符型字段上创建索引时(除全文索引),可提供一个 length 用以指定索引的范围,即使用 前缀索引:索引只在当前字段的 length 长度范围上有效,超出此范围的部分无法进行索引。且不同的字符类型也有区别:

  1. CHARVCHAR:length 可选,当不指定时为定义字段时指定的最大长度。可以小于字段实际长度;

  2. TEXTBLOB:length 必须指定,因这两种类型没有长度上限,需明确指定索引的范围。

① CREATE INDEX
CREATE INDEX index_name ON table_name (column1(length) [,column2(length) ...]);
CREATE UNIQUE INDEX index_name ON table_name (column1(length) [,column2(length) ...]);

② ALTER TABLE
ALTER TABLE table_name ADD INDEX [index_name] (column1(length) [,column2(length) ...]);
ALTER TABLE table_name ADD UNIQUE [index_name] (column1(length) [,column2(length) ...]);
ALTER TABLE table_name ADD PRIMARY KEY [index_name] (column1(length) [,column2(length) ...]);
ALTER TABLE table_name ADD FULLTEXT KEY [index_name] (column1(length) [,column2(length) ...]);

添加索引时,可以指定索引名,也可以省略,此时的索引自动为字段名。


4. 创建表时指定索引

CREATE TABLE table_name (
    ...
    INDEX [index_name] (column(length)) [,column2(length) ...],
    // 或 KEY [index_name] (column(length)) [,column2(length) ...],
    UNIQUE KEY [index_name] (column(length)) [,column2(length) ...],
    PRIMARY KEY [index_name] (column(length)) [,column2(length) ...]
);

添加索引时,可以指定索引名,也可以省略,此时的索引自动为字段名。


5. 删除索引

DROP INDEX [index_name] on table_name;

 或

ALTER TABLE table_name DROP index_name;
// 或若要删除主键:
ALTER TABLE table_name DROP PRIMARY KEY;



二、索引优化攻略

1. Explain 优化查询检测

Explain语句可以显示数据库如何使用索引来处理查询与连接表,可以帮助选择更好的索引和写出更高效的查询语句。

 使用方法:在对应 SQL 语句前加上Explain即可

EXPLAIN select * from test_table;


2. 创建索引的技巧

  1. 选择维度高的列创建索引(维度:数据列中不重复值出现的数量越高,维度越高)

  2. 为需要作为查询条件的字段建立索引(出现在WHERE子句中的字段)

  3. 为需要排序(ORDER)、分组(GROUP BY)和联合(ON)操作的字段建立索引;

  4. 对较小的数据列使用索引,使索引文件更小,以装载更多索引键

  5. 对较长字符串使用前缀索引


3. 不走索引的 SQL

① 无查询条件或查询条件没有索引
// 没有查询条件即查询所有记录
SELECT (column_list) FROM table_name;


② 查询的结果占总数据的 15% 以上
SELECT * FROM table_name WHERE column_name > 500;

// 若无需获取所有的查询结果,则可用 limit 做限制,使得查询使用索引
SELECT * FROM table_name WHERE column_name > 500 limit 20;

 在查询之前,MySQL 会对满足条件的记录数做一个评估,若数据量过大,使用索引的效率可能不如全表扫描。如:

如果在全班同学中查找所有男生,且已有按性别建立的索引(低纬度),也会先看男女排序表找到学号,再到花名册表(以学号主键排序)找到姓名及其它字段。 MYSQL认为还不如直接在花名册中全表扫描一遍高效。


③ 查询的条件字段参与了运算
// 在比较运算符左侧有计算,不走索引
SELECT * FROM table_name WHERE id-1=1;

// 在比较运算符右侧有计算,走索引
SELECT * FROM table_name WHERE id=3-1;

 原因:数据库在真正进行查询前无法获知在条件字段的真正条件,故只能走主键索引来进行全表扫描,再对每一行记录进行条件字段上的判断。


④ 字符串与数字比较

 MySQL 中比较字符串字段与数字时,会将字符串类型转换为整形,从首字符开始,直到遇到非数字字符(若首字符为非数字字符,则结果为0)。

// 假定 a 为 char 类型
SELECT * FROM table_name WHERE a = "1";     // 走索引
SELECT * FROM table_name WHERE a = 1;       // 不走索引


⑤ like 查询时进行了前缀通配

 即%在最前面。

SELECT * FROM table_name WHERE name LIKE "john%";   // 走索引
SELECT & FROM table_name WHERE name LIKE "%john%";  // 不走索引

 索引是依据字段类型的排序规则组织的,若进行前缀通配,此时满足条件的字段前缀不确定,无法利用顺序的索引。


⑥ 组合索引的顺序

 见组合索引篇:[组合索引]


Last Modified : 2021-01-27 17:20:30