在实际工作中,我也经常会遇到一些同学提出这样的问题:MySQL并没有按照自己的预想来选择索引,比如创建了索引但是选择了全表扫描,这肯定是MySQL数据库的Bug,或者是索引出错了?当然不是!这主要因为索引中的数据犯了错。
为什么这么说呢?要理解该问题,要理解MySQL数据库中的优化器是怎么执行的,然后才能明白为什么最终优化器没有选择你预想的索引。接下来,我们就来理解MySQL数据库是怎么选择索引的。
MySQL是如何选择索引的上一节,在表orders中,对于字段o_custkey已经创建了相关的3个索引,所以现在表orders的情况如下所示:
CREATETABLE`orders`(`o_orderkey`intNOTNULL,`O_CUSTKEY`intNOTNULL,`O_ORDERSTATUS`char(1)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULL,`O_TOTALPRICE`decimal(15,2)NOTNULL,`O_ORDERDATE`dateNOTNULL,`O_ORDERPRIORITY`char(15)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULL,`O_CLERK`char(15)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULL,`O_SHIPPRIORITY`intNOTNULL,`O_COMMENT`varchar(79)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULL,PRIMARYKEY(`o_orderkey`),KEY`orders_fk1`(`O_CUSTKEY`),KEY`idx_custkey_orderdate`(`O_CUSTKEY`,`O_ORDERDATE`),KEY`idx_custkey_orderdate_totalprice`(`O_CUSTKEY`,`O_ORDERDATE`,`O_TOTALPRICE`),CONSTRAINT`orders_ibfk_1`FOREIGNKEY(`O_CUSTKEY`)REFERENCES`customer`(`c_custkey`)ONDELETERESTRICTONUPDATERESTRICT)ENGINE=InnoDB
在查询字段o_custkey时,理论上可以使用三个相关的索引:ORDERS_FK1、idx_custkey_orderdate、idx_custkey_orderdate_totalprice。那MySQL优化器是怎么从这三个索引中进行选择的呢?
在关系型数据库中,B+树索引只是存储的一种数据结构,具体怎么使用,还要依赖数据库的优化器,优化器决定了具体某一索引的选择,也就是常说的执行计划。
而优化器的选择是基于成本(cost),哪个索引的成本越低,优先使用哪个索引。
MySQL执行过程如上图所示,MySQL数据库由Server层和Engine层组成:
Server层有SQL分析器、SQL优化器、SQL执行器,用于负责SQL语句的具体执行过程;
Engine层负责存储具体的数据,如最常使用的InnoDB存储引擎,还有用于在内存中存储临时结果集的TempTable引擎。
SQL优化器会分析所有可能的执行计划,选择成本最低的执行,这种优化器称之为:CBO(Cost-basedOptimizer,基于成本的优化器)。
而在MySQL中,一条SQL的计算成本计算如下所示:
Cost=ServerCost+EngineCost=CPUCost+IOCost
CPUCost表示计算的开销,比如索引键值比较、记录值的比较、结果集的排序……这些操作都在Server层完成;
IOCost表示引擎层IO的开销,MySQL8.0可以通过区分一张表的数据是否在内存中,分别计算读取内存IO开销以及读取磁盘IO的开销。
数据库mysql下的表server_cost、engine_cost则记录了对于各种成本的计算,如:
表server_cost记录了Server层优化器各种操作的成本,这里面包括了所有的CPUCost,其具体含义如下:
disk_temptable_create_cost:创建磁盘临时表的成本,默认为20。
disk_temptable_row_cost:磁盘临时表中每条记录的成本,默认为0.5。
key_