MySQL数据库优化_索引

时间：2021-02-07 15:20:36

1.添加索引后减少查询需要的行数，提高查询性能

　　(1) 建表

CREATE TABLE `site_user` (

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID号',

  `phone` varchar(15) NOT NULL COMMENT '手机号码',

  `token` varchar(50) DEFAULT NULL COMMENT '最近一次登录生成的令牌，用于登录验证',

  `status` tinyint(1) NOT NULL DEFAULT '' COMMENT '用户活动状态 0：正常，1：锁定',

  `login_status` tinyint(1) NOT NULL DEFAULT '' COMMENT '当前登录状态 1：在线，2：离线',

  `last_login_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最近登录时间',

  `channel` tinyint(1) NOT NULL DEFAULT '' COMMENT '注册来源 1：公众号，2：小程序，3：外部公众号，4：支付宝，5：Web',

  `remark` varchar(255) DEFAULT NULL COMMENT '备注',

  `signin_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',

  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

  `mallcode` varchar(11) DEFAULT NULL COMMENT '万象城code',

  PRIMARY KEY (`id`),

  KEY `index_phone` (`phone`)

) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8 COMMENT='微站点用户表';

　　(2)插入数据

INSERT INTO `highperformance_mysql`.`site_user` (`id`, `phone`, `token`, `status`, `login_status`, `last_login_time`, `channel`, `remark`, `signin_time`, `update_time`, `mallcode`) VALUES ('', '', 'a3664fbfed754a2aa371cd2a3f3419f6', '', '', '2018-08-14 17:02:06', '', NULL, '2018-07-20 15:34:32', '2018-08-14 15:07:17', '0202A003');

INSERT INTO `highperformance_mysql`.`site_user` (`id`, `phone`, `token`, `status`, `login_status`, `last_login_time`, `channel`, `remark`, `signin_time`, `update_time`, `mallcode`) VALUES ('', '', '7b6223251c9d4798ad4a56f19873cd82', '', '', '2018-08-15 09:32:49', '', NULL, '2018-07-30 09:47:06', '2018-08-15 09:32:48', '0202A003');

INSERT INTO `highperformance_mysql`.`site_user` (`id`, `phone`, `token`, `status`, `login_status`, `last_login_time`, `channel`, `remark`, `signin_time`, `update_time`, `mallcode`) VALUES ('', '', 'ff4b2c4c331c47c0b320d1dbaa7abf49', '', '', '2018-08-13 18:30:11', '', NULL, '2018-08-02 16:06:46', '2018-08-13 18:30:10', '0202A003');

INSERT INTO `highperformance_mysql`.`site_user` (`id`, `phone`, `token`, `status`, `login_status`, `last_login_time`, `channel`, `remark`, `signin_time`, `update_time`, `mallcode`) VALUES ('', '', 'c425ac1dbe354a43b56186a54cf624ac', '', '', '2018-08-07 17:48:40', '', NULL, '2018-08-07 16:21:17', '2018-08-07 16:23:59', '0202A003');

INSERT INTO `highperformance_mysql`.`site_user` (`id`, `phone`, `token`, `status`, `login_status`, `last_login_time`, `channel`, `remark`, `signin_time`, `update_time`, `mallcode`) VALUES ('', '', '7a7fd66631bd486f8f9f255d206796af', '', '', '2018-07-13 16:48:47', '', NULL, '2018-08-07 16:24:49', '2018-07-13 16:48:46', '0202A003');

INSERT INTO `highperformance_mysql`.`site_user` (`id`, `phone`, `token`, `status`, `login_status`, `last_login_time`, `channel`, `remark`, `signin_time`, `update_time`, `mallcode`) VALUES ('', '', '8e3c01fd6435471db7f28508713923c1', '', '', '2018-08-11 10:50:32', '', NULL, '2018-08-11 10:50:23', NULL, NULL);

　　(3)执行查询

EXPLAIN SELECT * FROM site_user WHERE token = 'a3664fbfed754a2aa371cd2a3f3419f6'

　　(4)分析查询结果，这次查询，要获取1行数据，但是要访问6行数据，执行的是全表扫描，如果表数据量变大的话，需要访问的数量会剧增，性能不高

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | site_user | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)　　　　　(ALL就是全表扫描)

　　(5)添加索引

ALTER TABLE site_user ADD INDEX index_token (`token` )

　　(6)再次执行查询

EXPLAIN SELECT * FROM site_user WHERE token = 'a3664fbfed754a2aa371cd2a3f3419f6'

　　(7)分析查询结果，这次查询，要获取1行数据，但是要访问1行数据

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-----------------------+

| id | select_type | table     | type | possible_keys | key         | key_len | ref   | rows | Extra                 |

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-----------------------+

|  1 | SIMPLE      | site_user | ref  | index_token   | index_token | 153     | const |    | Using index condition |

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-----------------------+

1 row in set (0.00 sec)

相关文章