^-^
MySQL索引
  1. 索引介绍
  2. 索引使用规范
  3. 索引管理
  4. 查询没有走索引的情况分析
  5. mysqlslap测试工具
    1. 常用参数
    2. 结果分析

索引介绍

　　索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

优缺点

优点：索引大大提高了查询速度

缺点：占用空间，降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。

索引类型

基于InnoDB存储引擎的索引类型包括单列索引和组合索引，基于MyISAM

单列索引

单列索引即一个索引只包含单个列，单列索引包括普通索引、唯一索引、主键索引

  • Key（普通索引）：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值

  • Unique（唯一索引）：索引列的值必须唯一，但允许有空值。

  • Primary Key（主键索引）：也称为聚集索引，主键索引是一种特殊的唯一索引，不允许有空值。

组合索引

组合索引指在表的多个字段组合上创建的索引，使用组合索引时遵循最左前缀集合

全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建

空间索引

空间索引（SPATIAL）是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL

索引使用规范

  1. 最左前缀匹配原则

    1
    查询条件中，MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

  2. 尽量选择区分度高的列作为索引

    1
    区分度就是字段不重复的比例，区分度越高，扫描的记录数越少，唯一键的区分度是1，优先选择唯一性索引可以更快速地确定某条记录。而一些状态、性别字段可能区分度很低。如果非要使用重复值较多的列作为查询条件，可以考虑联合其他列做组合索引，区分度高的列放在前面。也可以选择拆分表。

  3. 为经常查询的条件建立索引

    1
    比如查询语句经常需要使用`WHERE`，`ORDER BY`，`GROUP BY`，`JOIN ON`等操作，为它们查询的条件建立索引是非常有效的一种优化手段。

  4. 如果字段很长，可以考虑建立前缀索引

    1
    前缀索引的目的主要是减少字符串作为索引占用的空间，提高查询速度，组合索引中也可以使用前缀索引。

  5. 限制索引的数目

    1
    2
    3
    4
    5
    6
    索引的数目并不是越多越好，当我们为一个表创建了过多的索引，可能会存在以下几个常见的问题:
            (1)索引数目越多，MySQL优化器的负担会加重，有可能会影响到优化器的选择;
            (2)修改表时，对索引的重构和更新很麻烦，这意味着索引数目越多会增加更多的耗时;
            (3)当然，每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大;

    所以尽量地扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要扩展原来的索引即可。

  6. 定期删除不需要的索引

    1
    数据的使用方式被改变后，原有的一些索引可能不在需要，应该定期找出这些索引，并将它们删除，从而减少索引对更新操作的影响。

  7. 少在更新频繁的字段上创建索引

    1
    维护索引是需要一定成本的，在修改表时，对索引的重构和更新很麻烦，如果我们为修改表的某个字段过于频繁建立了索引，这意味着会频繁的更新索引信息，导致较多的IOPS的递增。

  8. 在业务不繁忙期间添加索引

    1
    不仅仅用在建立索引，凡是对生产表做修改操作时，都应该尽量选择在低峰期做维护操作，这样尽管做了不当操作导致数据库宕机，也可以将成本降到最低。

索引管理

获取查询信息

Explain是MySQL中的一个关键词，用于获取有关查询执行计划的信息。通过在SQL语句前面加上EXPLAIN关键词，MySQL会解释并展示关于如何执行查询的详细信息，包括执行顺序、使用的索引、表的连接类型等。。这对于优化查询性能非常有用。

使用EXPLAIN的语法如下：

1
EXPLAIN SELECT * FROM table_name WHERE condition;

输出的列包括：

  • id: 查询的唯一标识符
  • select_type: 查询类型，如简单查询、子查询等
  • table: 涉及的表
  • type: 表示连接类型，如ALL、index、range等
  • possible_keys: 可能用到的索引
  • key: 实际使用的索引
  • key_len: 使用的索引长度
  • ref: 连接使用的列
  • rows: 表示MySQL认为查询需要检索的行数
  • Extra: 额外的信息，如使用的临时表、使用了文件排序等

通过查看EXPLAIN的输出信息，可以识别查询中的性能问题，并进行优化，例如添加合适的索引、重构查询语句等。

查看索引

1
2
3
4
5
# 如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上FROM db_name
SHOW INDEX FROM table_name [FROM db_name]

# 或者db_name.table_name
SHOW INDEX FROM [db_name.]table_name

创建索引

索引可以在创建表的时候一同创建，也可以在创建表之后在创建索引

  • 创建表的时候创建索引

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    # 创建普通索引
    CREATE TABLE test.`user2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(50) DEFAULT NULL,
      `sex` varchar(5) DEFAULT NULL,
      `score_num` int(11) NOT NULL UNIQUE,
      PRIMARY KEY (`id`),
      index idx1(username)		# index 索引名(字段名)
    );

    # 创建唯一索引
    CREATE TABLE test.`user2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(50) DEFAULT NULL,
      `sex` varchar(5) DEFAULT NULL,
      `address` varchar(100) DEFAULT NULL,
      `score_num` int(11) NOT NULL UNIQUE,
      PRIMARY KEY (`id`),
      unique index idx1(username)		# unique index 索引名(字段名)
    );

    # 创建组合索引
    CREATE TABLE test.`user2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(50) DEFAULT NULL,
      `sex` varchar(5) DEFAULT NULL,
      `address` varchar(100) DEFAULT NULL,
      `score_num` int(11) NOT NULL UNIQUE,
      PRIMARY KEY (`id`),
      index idx1(id,score_num,username)		# index 索引名(字段名组合，以逗号分隔不同字段)
    );

    # 创建全文索引，可以用于全文搜索，并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行，不支持前缀索引
    CREATE TABLE test.`user2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(50) DEFAULT NULL,
      `sex` varchar(5) DEFAULT NULL,
      `address` varchar(100) DEFAULT NULL,
      `score_num` int(11) NOT NULL UNIQUE,
      PRIMARY KEY (`id`),
      fulltext index idx1(username)		# fulltext index 索引名(字段名)
    );

    # 创建空间索引
    create table test.test(
        position geometry not null,
        spatial index idx1(position)		# spatial index 索引名(字段名)
    );

    # 创建前缀索引
    CREATE TABLE test.t1 (
      col1 VARCHAR(10),
      col2 VARCHAR(20),
      INDEX (col1, col2(10))		# index (字段名(索引长度))
    );

  • 创建表之后创建索引

    1
    2
    3
    4
    5
    # 使用`create`关键字创建索引
    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 数据表名 (字段名[索引长度],...) [ASC|DESC]

    # 使用`alter`关键字创建索引
    ALTER TABLE 数据表名 ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (字段名[索引长度],...) [ASC|DESC]

修改索引名称

1
ALTER TABLE 数据表名 RENAME [INDEX|KEY] 旧索引名 TO 新索引名;

删除索引

添加AUTO_INCREMENT约束字段的唯一索引不能被删除

1
2
3
4
5
# 使用`drop`关键字删除索引
DROP INDEX 索引名 ON 数据表名;

# 使用`alter`关键字删除索引
ALTER TABLE 数据表名 DROP INDEX 索引名;

查询没有走索引的情况分析

  1. 没有查询条件或查询条件没有做索引

    1
    2
    如果sql语句没有添加查询条件，就会进行全表扫描。数据量比较大的表不应该存在全表扫描这种需求的，应该尽量避免全表扫描
    如果查询条件没有做索引，可以考虑为其建立索引，如果其区分度较低，可以考虑联合主键做组合索引

  2. 查询结果集占原表比例较大

    1
    查询的结果集，超过了总行数的15%-30%时，优化器觉得就没有必要走索引了，这个百分比跟数据库的预读能力及其相关参数设置有关

  3. 索引失效

    1
    索引有自我维护的能力，对于表内容变化比较频繁的情况下，有可能会出现统计信息不准确(数据过旧)，从而导致索引失效。通常的解决方案就是删除该索引后重建索引即可解决。

  4. 查询条件中使用了运算

    1
    查询条件中使用了算术运算或函数运算，比如`SELECT * FROM oldboyedu.student WHERE id-1=9998;`，不会使用索引

  5. 查询语句使用了隐式转换

    1
    2
    开发经常犯的错误，比如test表中的mobile_number字段是char类型，并且为此字段建立了索引，当使用查询语句`SELECT * FROM test WHERE mobile_number='110';`时，会正常使用索引。
    而使用查询语句`SELECT * FROM test WHERE mobile_number=110;`时，默认认为110是int类型数据，需要将其转换为char类型再进行查询，这种情况是不走索引的。

  6. 模糊查询不走索引

    1
    当查询语句使用了like关键字，结合`%`进行模糊查询，这种情况是不走索引的

  7. 其他特殊查询条件

    1
    !=,	>,	<,	not in 这些条件也可能不使用索引

mysqlslap测试工具

mysqlslap是官方提供的压力测试工具。通过模拟多个并发客户端并发访问MySQL来执行压力测试，同时提供了较详细的SQL执行数据性能报告，并且能很好的对比多个存储引擎（MyISAM，InnoDB等）在相同环境下的相同并发压力下的性能差别。mysqlslap 官方介绍

常用参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
--host=hostname,	-h [hostname]		# MySQL服务器的主机名
--port=port_num,	-P [port]           # 数据库端口
--socket=path,	-S [path]		# 连接服务器的socket
--user=user_name,	-u [username]		# 登录MySQL服务的用户名
--password=password,	-p [password]		# 登录密码
--create-schema='数据库名'		# 指定数据库
--query='sql语句'			# 要执行的sql语句,也可以直接指定一个sql脚本的路径，比如 --query=/tmp/test.sql
--create='sql语句' 		# 创建表所需的SQL,也可以直接指定一个sql脚本的路径，比如 --query=/tmp/test.sql
--concurrency=N,	-c N 		# 表示并发量，也就是模拟多少个客户端同时执行query。可指定多个值，以逗号分隔
--iterations=N,		-i N 		# 测试执行的迭代次数，代表要在不同的并发环境中，各自运行测试多少次；多次运行以便让结果更加准确
--number-of-queries=N
# 总的测试查询次数(并发客户数×每客户查询次数)，可以通过--concurrency、--number-of-queries等选项的值查看每次测试的结果，通过反复测试、优化得出mysql server的最大并发数。如果mysqlslap工具输出结果为Segmentation fault (core dumped)基本表示超出mysql server的负载。
	

--engine=engine_name,	-e engine_name 	# 代表要测试的引擎，可以有多个，用分隔符隔开。例如：--engines=myisam,innodb,memory。
--auto-generate-sql,	-a		# 自动生成MySQL测试语句
--auto-generate-sql-add-autoincrement	# 对生成的表自动添加自增的列

--auto-generate-sql-load-type=type
# 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read (扫描数据表), write (写入数据表), key (读主键列), update (更新主键列), or mixed (一半写入，一般查询). 默认值是mixed.

--debug-info		# 额外输出CPU以及内存的相关信息
--number-int-cols		# 创建测试表的int型字段数量
--number-char-cols		# 创建测试表的chat型字段数量

结果分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Average number of …
运行所有语句的平均秒数

Minimum number of …
运行所有语句的最小秒数

Maximum number of …
运行所有语句的最大秒数

Number of clients …
客户端数量

Average number of queries per client
每个客户端运行查询的平均数
2024-03-17 该篇文章被 邓胖胖 打上标签: MySQL 归为分类: 学习笔记