mysql创建索引操作

首先我们创建表：

<span style="font-size:18px;">CREATE TABLE mytable(
idserial INT PRIMARY KEY,
category_id INT  ,
user_id INT   ,
ADDDATE INT 
);</span>

写个存储过程插入1000条数据：

<span style="font-size:18px;">DELIMITER $
CREATE PROCEDURE pro_insert()
BEGIN 
	DECLARE i INT DEFAULT 1;
	DECLARE m INT DEFAULT 1000;
	WHILE i<m DO 
	INSERT INTO mytable(idserial,category_id,user_id,ADDDATE) VALUES(i,i,i,i);
	SET i=i+1;
	END WHILE;
</span>

删除存储过程：

<span style="font-size:18px;">DROP PROCEDURE pro_insert;
</span>

<span style="font-size:18px;">查询category_id=1</span>

<span style="font-size:18px;">SELECT * FROM mytable WHERE category_id=1;</span>

<span style="font-size:18px;">创建索引</span>

<span style="font-size:18px;">CREATE INDEX mytable_categoryid ON mytable (category_id);
</span>

<span style="font-size:18px;">删除索引</span>

<span style="font-size:18px;">DROP INDEX  mytable_categoryid ON mytable;
</span>

<span style="font-size:18px;">查询and条件</span>

<span style="font-size:18px;">SELECT * FROM mytable WHERE category_id=111 AND user_id=111;
</span>

<span style="font-size:18px;">创建多重索引</span>

<span style="font-size:18px;"><span style="font-weight: 700; color: rgb(51, 51, 51); font-family: arial, 宋体, sans-serif; font-size: 14px; line-height: 24px; text-indent: 28px;">CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);</span>
</span>

<span style="font-size:18px;"><span style="font-weight: 700; color: rgb(51, 51, 51); font-family: arial, 宋体, sans-serif; font-size: 14px; line-height: 24px; text-indent: 28px;">
</span></span>

<span style="font-size:18px;"><span style="font-weight: 700; color: rgb(51, 51, 51); font-family: arial, 宋体, sans-serif; font-size: 14px; line-height: 24px; text-indent: 28px;">order by 后面创建多重索引</span></span>

<span style="font-size:18px;"><span style="font-weight: 700; color: rgb(51, 51, 51); font-family: arial, 宋体, sans-serif; font-size: 14px; line-height: 24px; text-indent: 28px;">SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY ADDDATE DESC;
CREATE INDEX mytable_categoryid_userid_adddate ON mytable (category_id,user_id,ADDDATE);
</span></span>

<span style="font-size:18px;"><span style="font-weight: 700; color: rgb(51, 51, 51); font-family: arial, 宋体, sans-serif; font-size: 14px; line-height: 24px; text-indent: 28px;">
</span></span>