MYSQL8数据索引优化

OPTIMIZE TABLE重组表数据索引

MySQL 8 中的 OPTIMIZE TABLE 命令是用于优化表格的指令，会重组表格并为其创建索引，从而提高查询和数据读取的效率。

执行后会重组表数据和索引的物理存储，减少对存储空间使用和提升访问表时io效率。 optimize table后，表的变化和存储引擎也有关。 对于MyISAM表，optimize table操作执行以下工作： 1.如果表含有删除的列、分列的列，optimize table会修复表。

-- 重组表数据索引
-- 示例优化
OPTIMIZE Table 库名.表名;

使用数据索引优化

索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

数据准备

创建tb_seller数据表；

-- 数据准备--创建tb_seller表
CREATE TABLE 'tb_seller'(
    'sellerid' VARCHAR(100),
    'name' VARCHAR(100),
    'nickname' VARCHAR(50),
    'password' VARCHAR(60),
    'status' VARCHAR(1),
    'address' VARCHAR(100),
    'createtime' datetime,
    PRIMARY KEY(sellerid)
);

创建组合索引。

-- 创建组合索引
create index idex_seller_name_sta_addr on tb_seller(name,status,address);

全值匹配

-- 全值匹配，和字段匹配成功即可，和字段无关
explain SELECT * FROM tb_seller WHERE name='小米科技' AND status='1' AND address='北京市';

-- 和字段匹配成功即可，和字段无关
explain SELECT * FROM tb_seller WHERE address='北京市' AND name='小米科技' AND status='1';

避免索引失效–最左前缀法则

最左前缀原则指的是：查询从索引的最左前列开始，并且不跳过索引中的列。
符合最左前缀法则，索引生效，执行效率高。
如果索引了多列，要遵守最左前缀法则。

-- 最左前缀法则
-- 如果索引了多列，要遵守最左前缀法则。
-- 最左前缀法则指的是查询从索引的最左前列开始，并且不跳过索引中的列。
explain SELECT * FROM tb_seller WHERE name='小米科技'; -- key_len值：403

explain SELECT * FROM tb_seller WHERE name='小米科技' AND status='1'; -- key_len值：410

explain SELECT * FROM tb_seller WHERE status='1' AND name='小米科技' ; -- key_len值：410

违背最左前缀法则，索引失效

-- 违背最左前缀法则，索引失效
explain SELECT * FROM tb_seller WHERE status='1';
-- key_len值：null

符合最左前缀法则，但出现跳跃某一列，只有最左索引生效

-- 如果符合最左前缀法则，但是出现跳跃某一列，只有最左列索引生效
explain SELECT * FROM tb_seller WHERE name='小米科技' AND address='北京市';
-- key_len值：403

避免索引失效–其他匹配原则

下列情况下：索引生效，执行效率高。

-- 范围查询右边的列，不能使用索引
-- 根据前面的两个字段，status查询是走索引的，但是最后一个条件address没有用到索引
explain SELECT * FROM tb_seller WHERE name='小米科技' AND status>'1' AND address='北京市';

-- 不要载索引列上进行运算操作，索引将失效
explain SELECT * FROM tb_seller WHERE substring(name,3,2)='科技';

-- 字符串不加单引号，造成索引失效
-- status的数据类型是字符串，传入的是int
explain SELECT * FROM tb_seller WHERE name='小米科技' AND status=1;

-- 尽量使用覆盖索引,减少select * 
-- 尽量使用索引覆盖(从索引树上就能查询到所有数据)
-- selec1 * 需要从原表及磁盘上读取数据
explain SELECT * FROM tb_seller WHERE name='小米科技' AND address='北京市';  -- key_len值403

-- 如果查询列，超出索引列，也会降低性能

/*
Extra:
    using index: 使用覆盖索引时就会出现
    using where: 载查找使用索引的情况下，需要回去查询所需的数据
    using index condition: 查找使用了索引，但是需要回表查询数据
    using index; usring where :查找使用了索引，需要的数据都在索引列中能找到，所以不需要回表查数据
*/

or分隔开的条件，索引失效

-- 用or分隔开的条件，那么涉及的索引都不会被用到。
explain SELECT * FROM tb_seller WHERE name='黑马程序员' OR  createtime='2088-01-01 12:00:00';
explain SELECT * FROM tb_seller WHERE name='黑马程序员' OR  address='西安市';
explain SELECT * FROM tb_seller WHERE name='黑马程序员' OR  status='1';

以%开头的模糊查询，索引失效

-- 以%开头的LIKE模糊查询，索引失效；
explain SELECT * FROM tb_seller WHERE name LIKE '科技%';  -- 索引生效

explain SELECT * FROM tb_seller WHERE name LIKE '%科技';  -- 索引失效

explain SELECT * FROM tb_seller WHERE name LIKE '%科技%';  -- 索引失效

如果索引比全表更慢，则自动不使用索引

如果MySQL评估使用索引比全表更慢，则不使用索引。
这种情况是由数据本身的特点来决定的。

is null, is not null 有时索引失效；

in 走索引，not in 索引失效；

使用单列所列和复合索引，防止索引失效

能提高使用索引的机率。

-- 单列索引和符合索引，尽量使用复合索引
-- 创建索引
CREATE index idex_seller_name_sta_addr ON tb_seller(name,status,address);
/*
等价于：
    name
    name + status
    name + status + address
*/

-- 为常用的3个列创建单列索引
CREATE index index_name ON tb_seller(name);
CREATE index index_status ON tb_seller(status);
CREATE index index_address ON tb_seller(address);


-- 如果一张表有多个单列索引，及时where种都是用了这些索引列，则只有一个最优索引生效
show index FROM tb_seller;

-- 不论使用哪个字段，
explain SELECT * FROM tb_seller WHERE name='小米科技' AND status='1' AND address='西安市';