存储架构优化的那些事(三)

2021-09-01 本文已影响0人勤_

mysql.jpeg

概述

上文我们具体的深入Mysql SQL优化的相关方案，本文将深入Mysql 索引优化的方案。

初始化脚本

通过如下脚本，在mysql数据库中新建对应的实验表和数据。

CREATE TABLE `user_info` (
  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `age`  INT(11)              DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
)
  ENGINE = INNODB
  DEFAULT CHARSET = utf8;

INSERT INTO user_info (NAME, age) VALUES ('xys', 20);
INSERT INTO user_info (NAME, age) VALUES ('a', 21);
INSERT INTO user_info (NAME, age) VALUES ('b', 23);
INSERT INTO user_info (NAME, age) VALUES ('c', 50);
INSERT INTO user_info (NAME, age) VALUES ('d', 15);
INSERT INTO user_info (NAME, age) VALUES ('e', 20);
INSERT INTO user_info (NAME, age) VALUES ('f', 21);
INSERT INTO user_info (NAME, age) VALUES ('g', 23);
INSERT INTO user_info (NAME, age) VALUES ('h', 50);
INSERT INTO user_info (NAME, age) VALUES ('i', 15);

CREATE TABLE `order_info` (
  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT(20)           DEFAULT NULL,
  `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  `productor`    VARCHAR(30)          DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
)
  ENGINE = INNODB
  DEFAULT CHARSET = utf8;

INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p199', 'WHH99');

-- 验证分区 
CREATE TABLE user_temp (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(20),
  PRIMARY KEY (id)
)ENGINE = INNODB PARTITION BY KEY (id) PARTITIONS 3;

INSERT INTO user_temp VALUES(1,"hello") ;
INSERT INTO user_temp VALUES(2,"world") ;
INSERT INTO user_temp VALUES(3,"nice") ;

DROP PROCEDURE IF EXISTS batchInsert; 

-- 批量插入数据
DELIMITER //
CREATE PROCEDURE batchInsert()
BEGIN
    DECLARE num INT; 
    SET num=20;
    WHILE num<=100000 DO
        INSERT INTO user_info (id, NAME, age) VALUES (num ,CONCAT('xys',num) , 20);
        INSERT INTO order_info (user_id, product_name, productor) VALUES (num, CONCAT('p',num) , CONCAT('WHH',num));
        SET num=num+1;
    END WHILE;
END
// 
DELIMITER ; #恢复;表示结束
CALL batchInsert;

SELECT COUNT(1) FROM `user_info`;
SELECT COUNT(1) FROM `order_info`;

-- user_info增加一列
ALTER TABLE user_info ADD COLUMN `oid` BIGINT(20);
UPDATE user_info u , order_info o SET u.oid = o.user_id WHERE o.user_id = u.id ;

ALTER TABLE order_info ADD COLUMN `description` VARCHAR(255) ;

-- Index Prefixes
CREATE TABLE test(blob_col BLOB);
SHOW VARIABLES LIKE 'InnoDB_large_prefix';
SET GLOBAL InnoDB_large_prefix=off;
ALTER TABLE test ADD INDEX idx_blob_col(blob_col(7600));

-- 
CREATE TABLE t1 (
  i1 INT NOT NULL DEFAULT 0,
  i2 INT NOT NULL DEFAULT 0,
  d DATE DEFAULT NULL,
  PRIMARY KEY (i1, i2),
  INDEX k_d (d)
) ENGINE = INNODB;

INSERT INTO t1 VALUES
(1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
(1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
(1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
(2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
(2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
(3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
(3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
(3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
(4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
(4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
(5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
(5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
(5, 5, '2002-01-01');

优化索引

总体方向：

提高 SELECT 操作性能的最佳方法是为查询中测试的一个或多个列创建索引。索引条目的作用类似于指向表行的指针，允许查询快速确定 WHERE 子句中哪些行与条件匹配，并检索这些行的其他列值。

尽管为查询中使用的每个可能列创建索引很诱人，但是不必要的索引会浪费 MySQL 的空间和时间来决定使用哪些索引。索引还会增加插入、更新和删除的成本，因为每个索引都必须更新。

索引用于快速查找具有特定列值的行。如果没有索引，MySQL 必须从第一行开始，然后通读整个表以找到相关的行。如果表中有相关列的索引，MySQL 可以快速确定要在数据文件中间查找的位置，而无需查看所有数据。这比按顺序读取每一行要快得多。

大多数 MySQL 索引(primarykey、 UNIQUE、 INDEX 和 FULLTEXT)都存储在 b 树中。

细分方向：

Primary Key Optimization

表的主键表示在最重要的查询中使用的列或列集。它有一个关联的索引，用于快速查询性能。使用 InnoDB 存储引擎，表数据被物理地组织起来，以便根据主键列进行超快速的查找和排序。

如果您的表很大很重要，但是没有明显的列或列集用作主键，那么您可以创建一个单独的列，使用自动递增值作为主键。当您使用外键联接表时，这些惟一 id 可以作为指向其他表中相应行的指针。
Foreign Key Optimization

如果一个表有许多列，并且您查询许多不同的列组合，那么将较少使用的数据拆分为单独的表，每个表有几列，并通过从主表复制数字 ID 列将它们与主表关联起来，这会对性能有提升。

这样，每个小表都可以有一个主键用于快速查找其数据，您可以使用联接操作查询所需的列集。根据数据的分布情况，查询可能执行较少的 i/o 并占用较少的缓存内存。(为了最大化性能，查询尝试从磁盘读取尽可能少的数据块; 只有几列的表可以在每个数据块中容纳更多的行。)
Column Indexes

最常见的索引类型涉及一个列，将来自该列的值的副本存储在数据结构中，从而可以快速查找具有相应列值的行。B 树数据结构允许索引快速查找 WHERE 子句中与 = 、 > 、≤、 BETWEEN、 IN 等操作符对应的特定值、一组值或值范围。

每个存储引擎定义了每个表的最大索引数和最大索引长度。所有存储引擎对每个表支持16个索引，总索引长度为256字节。大多数存储引擎都有更高的限制。
- Index Prefixes：使用字符串列的索引规范中的 col _ name (n)语法，您可以创建仅使用该列的前 n 个字符的索引。以这种方式仅索引列值的前缀可以使索引文件大大缩小。索引 BLOB 或 TEXT 列时，必须为索引指定前缀长度。前缀最长可达1000字节(InnoDB 表为767字节，除非您设置了 InnoDB _ large _ prefix)。
```
-- Index Prefixes
CREATE TABLE test(blob_col BLOB);
SHOW VARIABLES LIKE 'InnoDB_large_prefix';
SET GLOBAL InnoDB_large_prefix=off;
ALTER TABLE test ADD INDEX idx_blob_col(blob_col(7600));
```
  image-20210901092645730.png
- FULLTEXT Indexes：FULLTEXT 索引用于全文搜索。只有 InnoDB 和 MyISAM 存储引擎支持 FULLTEXT 索引，并且只支持 CHAR、 VARCHAR 和 TEXT 列。索引始终在整个列上进行，并且不支持列前缀索引。
Multiple-Column Indexes

MySQL 可以创建复合索引(即多列上的索引)。一个索引最多可以由16列组成。对于某些数据类型，您可以索引列的前缀。

如果表具有多列索引，则优化器可以使用索引最左边的任何前缀查找行。例如，如果在(col1、 col2、 col3)上有三列索引，那么在(col1)、(col1、 col2)和(col1、 col2、 col3)上有索引搜索功能。
```
-- Multiple-Column Indexes
EXPLAIN SELECT * FROM order_info WHERE product_name LIKE 'x%' AND user_id!=1 AND productor LIKE 'p%'; -- 最左的前缀原则验证，user_id用不到索引，导致多列索引失效
```
image-20210901093855389.png

InnoDB and MyISAM Index Statistics Collection MyISAM Index Statistics Collection

存储引擎收集关于表的统计信息，供优化器使用。

-- InnoDB and MyISAM Index Statistics Collection  MyISAM Index Statistics Collection
SHOW INDEX FROM order_info;
SHOW INDEX FROM user_info; -- 
SHOW VARIABLES LIKE 'InnoDB_stats_method';
SHOW VARIABLES LIKE 'innodb_stats_persistent'; -- 配置持久化（Persistent）统计信息参数
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';  -- 配置自动触发更新统计信息参数.表修改时，确认变化的数据是否超过10%，超过自动收集统计信息如果禁用了innodb_stats_auto_recalc，请在对索引列进行实质性更改后，通过为每个适用的表发出ANALYZE TABLE语句来确保统计信息的准确性。在表上添加索引或者添加删除索引中的列时，将自动计算索引统计信息并将其添加到innodb_index_stats表，不受innodb_stats_auto_recalc的值影响。
SHOW VARIABLES LIKE 'innodb_stats_persistent_sample_pages'; -- 配置持久化统计信息采样的页数.统计信息不够准确，优化器选择次优计划时需要增大。ANALYZE TABLE太慢时，要减小。
SELECT * FROM mysql.innodb_table_stats WHERE database_name LIKE '%sample%' AND table_name = 'order_info'; -- 查询统计表
SELECT * FROM mysql.innodb_index_stats WHERE database_name LIKE '%sample%' AND table_name = 'order_info'; -- 查询统计表 innodb_table_stats和innodb_index_stats表是普通表，可以手动更新。如果手动更新统计信息，请发出FLUSH TABLE tbl_name命令以使MySQL重新加载更新的统计信息。
ANALYZE TABLE order_info;

Use of Index Extensions

InnoDB 通过附加主键列自动扩展每个辅助索引。在确定如何以及是否使用索引时，优化器将考虑扩展辅助索引的主键列。这可以导致更高效的查询执行计划和更好的性能。优化器可以使用扩展的二级索引来进行 ref、 range 和 index_merge 索引访问、松散索引扫描访问、连接和排序优化以及 MIN ()/MAX ()优化。
```
-- Use of Index Extensions
SET optimizer_switch = 'use_index_extensions=off';
EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
SHOW STATUS LIKE 'handler_read%';
SET optimizer_switch = 'use_index_extensions=on';
EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
SHOW STATUS LIKE 'handler_read%';
```
image-20210901105757329.png

参考

Optimization and Indexes

存储架构优化的那些事(三)

概述

初始化脚本

优化索引

参考

猜你喜欢

热点阅读