索引实践和调优(3)
今儿啊,洲际哥给大家带来两个8.0的新特性
Ⅰ、不可见索引
schema_unused_indexes表,这个表有三个列分别是object_schema、object_name、index_name
对于这些索引,通常来说,就是删掉,但是直接删除有问题啊,可能现在没用到,过段时间要用啊
但在MySQL 8.0中我们可以先将索引设置为invisible,优化器就不会走这个索引,跑一段时间观察对业务有没有影响,如果没有影响再把这个索引删掉
(root@localhost) [sys]> select * from schema_unused_indexes limit 3;
+---------------+-------------+---------------------+
| object_schema | object_name | index_name |
+---------------+-------------+---------------------+
| dbt3 | customer | i_c_nationkey |
| dbt3 | lineitem | i_l_shipdate |
| dbt3 | lineitem | i_l_suppkey_partkey |
+---------------+-------------+---------------------+
3 rows in set (0.00 sec)
alter table xxx alter index_name invisible/visible;
Ⅱ、降序索引
(root@localhost) [dbt3]> explain select * from orders where o_custkey = 1 order by o_orderDate,o_orderStatus;
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | orders | NULL | ref | i_o_custkey | i_o_custkey | 5 | const | 6 | 100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
(root@localhost) [dbt3]> alter table orders add index idx_a_b_c(o_custkey,o_orderDate,o_orderStatus);
Query OK, 0 rows affected (6.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [dbt3]> explain select * from orders where o_custkey = 1 order by o_orderDate,o_orderStatus;
+----+-------------+--------+------------+------+-----------------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | orders | NULL | ref | i_o_custkey,idx_a_b_c | idx_a_b_c | 5 | const | 6 | 100.00 | Using index condition |
+----+-------------+--------+------------+------+-----------------------+-----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.06 sec)
(root@localhost) [dbt3]> explain select * from orders where o_custkey = 1 order by o_orderDate DESC,o_orderStatus;
+----+-------------+--------+------------+------+-----------------------+-------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-------------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | orders | NULL | ref | i_o_custkey,idx_a_b_c | i_o_custkey | 5 | const | 6 | 100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+-----------------------+-------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
综上所述：到5.7为止,所有的排序必须是一个方向(A,B,C)，因为索引默认都是(A asc,B asc,C asc)这种，如果DESC就得重新排序，即方向不一致就行不通
但是线上我们经常会碰到这种方向不一致的场景,怎么办呢?
方案一：8.0 新特性
alter table orders add index idx_cust_data_status (o_custkey,o_orderDate DESC,o_orderStatus);
其实在5.7中也可以这样执行,但是会把desc忽略掉,没用
方案二：虚拟列
目前大家基本上还用的5.7,我们这边用下面这种函数索引(虚拟列)的方法来实现一下需求
alter tablename add column as (function(column)) virtual/stored;
virtual 每次访问这个列需要一次额外计算,不占任何存储空间,只是一个计算得到的列,但是可以在上面增加一个索引,这个索引是占空间的,stored 在磁盘上占据空间
通常用virtual,不用stored
用函数表达式创建一个虚拟列,datediff是一个日期差,化降序为升序
(root@localhost) [dbt3]> alter table orders add column o_orderdate2 int as (datediff('2099-01-01',o_orderdate)) virtual;
Query OK, 0 rows affected (0.94 sec)
Records: 0 Duplicates: 0 Warnings: 0
在这个虚拟列上加一个索引
(root@localhost) [dbt3]> alter table orders add index idx_cust_date_status (o_custkey,o_orderdate2,o_orderstatus);
Query OK, 0 rows affected (8.99 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [dbt3]> explain select * from orders where o_custkey = 1 order by o_orderDate2,o_orderStatus;
+----+-------------+--------+------------+------+--------------------------------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+--------------------------------------------+----------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | orders | NULL | ref | i_o_custkey,idx_a_b_c,idx_cust_date_status | idx_cust_date_status | 5 | const | 6 | 100.00 | Using where |
+----+-------------+--------+------------+------+--------------------------------------------+----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
这个做法只有5.7版本才能用,因为虚拟列和函数索引都是5.7才支持的
Ⅲ、索引倾斜
这个知识点先了解一下即可
背景
订单status上是否需要建索引,三种状态 0 1 2
倾斜：大部分状态是已完成,查询条件大部分时候是未完成状态,而这种未完成的状态又是很少量的,
如果一共1000w,未完成的只有10w,这样去取是符合B+ tree的条件,从大量数据取小部分数据,这时候本身是有意义的,但是如果去做一些比较复杂的查询,数据库的优化器可能会出错,因为他不知道索引是倾斜的,导致它在选择上有问题
5.7之前,优化器不能感知索引倾斜,如果优化器的执行计划出现小偏差,去看下他使用的哪个索引是否存在倾斜,如果存在那也没办法,后面8.0可能会改进
(root@localhost) [dbt3]> explain select * from lineitem where l_orderkey=1;
+----+-------------+----------+------------+------+--------------------------------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------------------------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | lineitem | NULL | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4 | const | 6 | 100.00 | NULL |
+----+-------------+----------+------------+------+--------------------------------------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.11 sec)
优化器选择了pk
我们也可以强制走索引,但不是很建议这么做
(root@localhost) [dbt3]> explain select * from lineitem force index(i_l_orderkey) where l_orderkey=1;
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | lineitem | NULL | ref | i_l_orderkey | i_l_orderkey | 4 | const | 6 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
