explain执行计划效率测试

explain执行计划效率测试

textA

CREATE TABLE `texta` (
  `id` int(10) NOT NULL,
  `text` varchar(100) DEFAULT NULL,
  UNIQUE KEY `Idindex` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

textB

CREATE TABLE `textb` (
  `id` int(10) NOT NULL,
  `text` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `indexid` (`id`) USING BTREE,
  KEY `textIndex` (`text`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- all 在内存中遍历全表或没有索引的行
explain select textA.text from textA;
-- index 查询索引所有数据
explain select * from textB;

-- 唯一性索引，表索引与另外表的主键进行关联，两张表之间每条数据要一一对应，查询的数据在表中是唯一性，不能有重复，
-- 被联合的主键表的type=eq_ref
explain select textA.id,textB.id from textA,textB where textA.id=textB.id;
select textA.id,textB.id from textA,textB where textA.id=textB.id;
explain select textA.text,textB.text from textA join textB on textA.id=textB.id where textA.id=1;
-- range 范围查找
explain select * from textA where textA.id=1 or textA.id = 2;

--
select * from textA join textB where textA.id=1;
-- ref 非唯一索引的索引键查询查出零条或以上数据
explain select textB.text from textB where text='world';
-- const 仅仅能查到一条数据的SQL，用于primary key 或 unique的索引
explain select * from textB where textB.id=1;

-- system 表中只有一行数据