手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

MySQL索引语法+使用场景

[日期:2017-11-04] 来源:cnblogs.com/orchidbaby  作者:卡卡真的很卡 [字体: ]

MySQL索引语法

建表时添加索引

建表同时建立单索引

CREATE TABLE t_user1(id INT ,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
INDEX (userName) #关键字INDEX
);

建表同时建立唯一索引(可以是单或多)

CREATE TABLE t_user2(id INT ,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
UNIQUE INDEX index_userName(userName) #关键字UNIQUEINDEX
);

建表同时建立联合索引

CREATE TABLE t_user3(id INT ,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
INDEX index_userName_password(userName,PASSWORD)
);

给已存在表添加索引

  • 单列索引
    CREATE INDEX index_userName ON t_user(userName);
  • 唯一索引
    CREATE UNIQUE INDEX index_userName ON t_user(userName);
  • 联合索引
    CREATE INDEX index_userName_password ON t_user(userName,PASSWORD);

另一种写法与上面相似 😱 但是有区别

  • 单列索引
    ALTER TABLE t_user ADD INDEX index_userName(userName);

  • 唯一索引
    ALTER TABLE t_user ADD UNIQUE INDEX index_userName(userName);
  • 联合索引

两种区别:
1、CREATE INDEX必须提供索引名,对于ALTER TABLE,将会自动创建,如果你不提供;
2、CREATE INDEX一个语句一次只能建立一个索引,ALTER TABLE可以在一个语句建立多个,如:
ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);
3、只有ALTER TABLE 才能创建主键,ADD INDEX 不能;

ALTER TABLE t_user ADD INDEX index_userName_password(userName,PASSWORD);

删除索引

DROP INDEX index_userName ON t_user;
DROP INDEX index_userName_password ON t_user;

各种索引的合理使用

  1. 首先要确定优化的目标,在什么样的业务场景下,表的大小等等。如果表比较小的话,可能都不需要加索引。
  2. 哪些字段可以建索引? 一般都where、order by 或者 group by 后面的字段。
  3. 记录修改的时候需要维护索引,所以会有开销,要衡量建索引之后的得与失(空间+维护换时间)。
  4. 比如学生表,可以认为name的重复度比较小,而age的重复度比较大,对于单列索引来说,比较适合建在重读度低的列上。
  5. 对于select from students where name='张三’and age=18;该中情况下:
    A. name 和 age 各自单独建立索引:
    一般来说mysql会选择其中一个索引,name的可能性比较大,因为mysq会统计每个索引上的重复度,选用低重复度的字段。所以不使用age,否则增加太多成本。
    B. name和age的联合索引:
    这种索引的切合度最好。但是相对单索引来说,维护的成本大,索引数据占用的存储空间也要更大。
    可是!有必要使用联合索引吗?一般没必要:学校有10000个学生,叫谢春花的会超过5个吗?5个找一个比建立联合索引花销小的多。
  6. 什么情况下使用联合索引比较好呢? 举一个例子,大学修课,需要创建一个关系对应表,有2个字段,student_id 和 teacher_id,想要查询某个老师和某个学生是否存在师生关系。
    一个学生会选50老师,一个老师会带200个学生
    如果只为student_id建立索引的情况下,经过索引会选出50条记录,然后在内存中where一下,去除其余的老师。
    相反如果只为teacher_id建立索引,经过索引会选出200条记录,然后在内存中where一下,去除其余的学生。
    两种情况都不是最优的,因为使用索引后范围依然很大,这个时候使用联合索引最合适,通过索引直接找到对应记录,差不多提高了一倍效率。

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-11/148247.htm

linux
相关资讯       MySQL索引  MySQL索引语法 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款