mysql优化-合理利用索引

1.索引的简介

正确合理的利用索引是提升数据库查询性能的方式之一，索引就想目录一样，能帮我们快速的定位数据，mysql索引种类有B树索引，hash索引，全文索引，空间索引等。一般我们只重点关注B树索引。
一般从几个方面评价索引的优劣，
1星索引：可以通过索引扫描数据
2星索引：在1星的基础上，可以使用覆盖索引，无需再回表查询
3星索引：在二星的基础上，可以利用索引完成排序。
mysql索引的原理：点击查看，我们这里不再复述。
https://blog.csdn.net/lucky_ly/category_7563195.html

2.索引的创建

先看索引的sql定义语句如下，可以在表定义时，创建也可以在后来手动添加。

2.1 创建表时指定索引

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition
  | {INDEX|KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
  | CHECK (expr)

示例如下：

CREATE TABLE `film` (
  `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `release_year` year(4) DEFAULT NULL,
  `language_id` tinyint(3) unsigned NOT NULL,
  `original_language_id` tinyint(3) unsigned DEFAULT NULL,
  `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
  `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
  `length` smallint(5) unsigned DEFAULT NULL,
  `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
  `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
  `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`film_id`),
  KEY `idx_title` (`title`),
  KEY `idx_fk_language_id` (`language_id`),
  KEY `idx_fk_original_language_id` (`original_language_id`),
  KEY `idx_film_length` (`length`),
  CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

key和index都可以定义索引，index_type可选btree和hash索引，默认是Btree索引，

2.2 直接创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

示例：

create index idx_film_length on film(length desc);

一般索引名以idx开头，下划线分割，然后解表名，和列名。

2.3 查看表的索引信息：

show index from 表名

3. 索引优化

1 innodb会为主键自动创建聚簇索引，主键最好选择自增长id，或递增的，有序的区分度较高的键。否则可能在插入数据时，会导致索引频繁的裂变，如果键值有序的，数据可以直接追加在后头，如果键无序，例如使用uuid当主键，要会先找到正确索引位置，在插入数据，中间会导致数据的移动，严重影响性能。而且会产生大量的碎片。
2. 在逻辑上符合的话，尽量创建唯一索引。
3. 表的连接列和一些区分度较高的where条件列可以创建索引。
4. 索引列不能用是表达式的一部分，不能是函数的参数，
5. like模糊匹配时，开头不能是%，例如, select * from film where title like %toutiao%
6. 不要为盲目的为多个列创建单个索引，当有多个列时，可以创建多列索引，列的区分度尽量由高到低，多列索引的遵循最左匹配原则。
例如有学生表如下

CREATE TABLE `student` (
 `s_id` int(11) NOT NULL AUTO_INCREMENT,
 `sno` int(11) DEFAULT NULL,
 `sname` varchar(50) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 `sex` char(1) DEFAULT NULL,
 `remark` varchar(500) DEFAULT NULL,
 PRIMARY KEY (`s_id`),
 UNIQUE KEY `uk_sno` (`sno`)
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

如果我们需要根据sname,age,sex条件去查询，我们可以在此基础上创建一个多列索引。

create index idx_student_sname_age_sex on student(sname,age,sex);

遵循最左匹配原则
1.使用左侧第一列可以走索引

2.使用左侧第一列和左侧第二列也可以使用索引

3.使用第二列或第三列，即时在数据区分度很高的情况下，也只能走全表扫描

7.当为字符类型的字段建立索引时，如果区分度合适的情况下，可以创建前缀索引，这样可以减少索引占用的空间，但是创建了前缀索引，就无法使用覆盖索引了，使用时需要权衡利弊。
前缀索引的创建方式
如下为sname创建一个前缀索引,保留前缀5个字符，

create index idx_student_sname on student(sname(5));

如何知道多少个保留多少个字符，区分度才较为合适呢，我们比较该字段去重之后占总数据的百分比和保留n个字符前缀占总数据的百分比，
如下，保留到10字符时，比值和原来相等，基本就可以了。
对于text,blob数据类型，如果使用b-tree索引的话，必须指定前缀索引，对于唯一索引来所，前缀索引可能会引起报错，因为保留前缀可能无法区别唯一性。

8.利用索引字段的有序性，避免排序。这可以极大的提高性能。
对于多个排序字段排序时，如果要在这些字段上有组合索引也要遵循最左匹配原则，
例如，我们有一张学生表，有（birth_date,age,sex）组合索引

select * from student where birth_date = '2020-01-05' 
order by age,sex

前导列出现在where条件，也可以利用索引排序。
9.避免多个索引列的范围查询，mysql无法同时利用2个索引列进行范围查询。

4.总结

正确的使用索引是提高数据库的性能的方式之一，但是是否决定走索引还是走全表，还是由优化器决定的，现在的主流数据库都是使用基于成本的优化器，理解数据库的原理，才能写出最优的sql。