mysql创建索引操作

来源：互联网发布：大班mg红异端淘宝编辑：程序博客网时间：2024/06/05 23:56

首先我们创建表：

<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 mytableWHERE category_id=1 AND user_id=2ORDER 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>

0 0