【小白专用】mysql 添加索引-结合实战项目

mysql的索引有很多种，下面我们来介绍以下。

• 1.添加主键索引： primary key

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

• 2.添加 唯一索引：

ALTER TABLE `table_name` ADD UNIQUE (`column` ) 

• 3.添加 普通索引：

ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 

DROP TABLE IF EXISTS `hysc_credits_order_log`;
CREATE TABLE `hysc_credits_order_log` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`order_type` TINYINT(1) NOT NULL DEFAULT 1,
	`uid` INT(10) NOT NULL,  
	`orderno` VARCHAR(30) NOT NULL,
	`credits` INT(10) NOT NULL,
	`create_time` INT(10) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT='会员订单领取积分日志表';
-- 
-- indexes of hysc_credits_order_log
-- 
ALTER TABLE `hysc_credits_order_log` ADD INDEX uid(uid);
ALTER TABLE `hysc_credits_order_log` ADD INDEX orderno(orderno);
ALTER TABLE `hysc_credits_order_log` ADD INDEX create_time(create_time);


DROP TABLE IF EXISTS `hysc_pay_log`;
CREATE TABLE `hysc_pay_log` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`pay_type` VARCHAR(20) NOT NULL,
	`trade_type` VARCHAR(20) NOT NULL,
	`uid` INT(10) NOT NULL,
	`fans_id` INT(10) NOT NULL,
	`openid` VARCHAR(255) NOT NULL,
	`unionid` VARCHAR(255) NOT NULL,
	`tid` VARCHAR(30) NOT NULL,
	`fee` DECIMAL(10,2) NOT NULL,
	`tag` VARCHAR(2000) NOT NULL,
	`refund_tag` VARCHAR(2000) NOT NULL,
	`status` TINYINT(2) NOT NULL,
	`create_time` INT(10) NOT NULL,
	`pay_time` INT(10) NOT NULL,
	`refund_time` INT(10) NOT NULL,
	`remark` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT='支付日志表';
-- 
-- indexes of hysc_pay_log
-- 
ALTER TABLE `hysc_pay_log` ADD INDEX pay_type(pay_type);
ALTER TABLE `hysc_pay_log` ADD INDEX uid(uid);
ALTER TABLE `hysc_pay_log` ADD INDEX fans_id(fans_id);
ALTER TABLE `hysc_pay_log` ADD INDEX openid(openid);
ALTER TABLE `hysc_pay_log` ADD INDEX tid(tid);
ALTER TABLE `hysc_pay_log` ADD INDEX status(status);

• 5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )