mysql创建索引操作-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

mysql创建索引操作

简介: 首先我们创建表: CREATE TABLE mytable( idserial INT PRIMARY KEY, category_id INT , user_id INT , ADDDATE INT ); 写...

首先我们创建表:

<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>

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章