Mysql索引

最新推荐文章于 2024-06-17 15:31:11 发布

原创最新推荐文章于 2024-06-17 15:31:11 发布 · 167 阅读

0 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

6 篇文章

订阅专栏

mysql索引：

基于B+Tree
最左匹配原则：组合索引的最左边第一个字段排序，在此基础上排序第二个字段。查询时如果第一个字段缺失，会导致索引失效。
索引列：不能参与函数计算。

建表SQL,并导入了800万数据：

CREATE TABLE `User` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `orderNum` varchar(255) DEFAULT NULL,
  `customerNum` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_num` (`orderNum`,`customerNum`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

索引测试：

select * from User where orderNum ='2140d1a9-d8bd-4c74-9dba-56bea193bb7b';          //索引有效
select * from User where orderNum like '2140d1a9-d8bd-4c74-9dba-56bea193bb7b%';     //索引有效
select * from User where orderNum like '%2140d1a9-d8bd-4c74-9dba-56bea193bb7b%';    //索引无效
select * from User where customerNum ='2f828f5b-b5f1-4398-bb78-ae98da80468c';       //索引无效
select * from User where customerNum ='2f828f5b-b5f1-4398-bb78-ae98da80468c' 
and orderNum ='2140d1a9-d8bd-4c74-9dba-56bea193bb7b';                               //索引有效

//1 SIMPLE  User        ALL                 8018734 10  Using where
EXPLAIN select * from User where name = '81ea5e71-5ac8-45f7-a646-c8bd015e4426' 
//1 SIMPLE  User        ALL                 8018734 10  Using where
EXPLAIN select * from User where customerNum ='2f828f5b-b5f1-4398-bb78-ae98da80468c' 
//1 SIMPLE  User        ref uniq_num    uniq_num    768 const   1   100 
EXPLAIN select * from User where orderNum ='2140d1a9-d8bd-4c74-9dba-56bea193bb7b';

插入性能

UserMapperTest: 每千条耗时: 1029====================已有条数0,000
UserMapperTest: 每千条耗时: 156====================已有条数1,000
UserMapperTest: 每千条耗时: 118====================已有条数2,000
UserMapperTest: 每千条耗时: 112====================已有条数3,000
UserMapperTest: 每千条耗时: 121====================已有条数4,000
UserMapperTest: 每千条耗时: 107====================已有条数5,000
UserMapperTest: 每千条耗时: 91====================已有条数6,000
UserMapperTest: 每千条耗时: 129====================已有条数7,000
UserMapperTest: 每千条耗时: 1261====================已有条数3993,000
UserMapperTest: 每千条耗时: 2141====================已有条数3994,000
UserMapperTest: 每千条耗时: 1960====================已有条数3995,000
UserMapperTest: 每千条耗时: 1985====================已有条数3996,000
UserMapperTest: 每千条耗时: 1024====================已有条数3997,000
UserMapperTest: 每千条耗时: 1409====================已有条数3998,000
UserMapperTest: 每千条耗时: 6226====================已有条数6804,000
UserMapperTest: 每千条耗时: 6367====================已有条数6805,000
UserMapperTest: 每千条耗时: 5784====================已有条数6806,000
UserMapperTest: 每千条耗时: 5505====================已有条数6807,000