mysql

mysql索引入门

Mysql索引入门

  1. 什么是索引

    索引用于快速找出在某个列中有一特定值的行

  2. 索引类型

    大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引

  3. 索引越多越好？

    • 数据的变更（增删改）都需要维护索引，因此更多的索引意味着更多的维护成本
    • 更多的索引意味着也需要更多的空间 （一本100页的书，却有50页目录？）
    • 过小的表，建索引可能会更慢
    • 频繁写入影响性能

  4. 使用索引

    1
    2
    3
    like 'xxx%'   -- 可以用索引
    Like '%xxx%'  -- 不能用索引
    like '%xxx'   -- 不能用索引
    1
    2
    <，<=，=，>，>=，BETWEEN，IN 	-- 可以用索引
    <>，not in ，！=则不行  -- 不能用索引
    1
    2
    3
    SELECT `sname` FROM `t_stu` WHERE `age`=20; 	-- 会使用索引
    SELECT `sname` FROM `t_stu` WHERE `age`+10=30; 	-- 不会使用索引！！因为所有索引列参与了计算
    SELECT `sname` FROM `t_stu` WHERE `age`=30-10;	-- 会使用索引
    1
    2
    SELECT `sname` FROM `stu` WHERE concat(`sname`,'abc') ='Jaskeyabc'; -- 不会使用索引,因为使用了函数运算,原理与上面相同
    SELECT `sname` FROM `stu` WHERE `sname` =concat('Jaskey','abc'); -- 会使用索引
    1
    2
    3
    4
    5
    select * from dept where dname='jaskey' or loc='bj' or deptno=45 --如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引
    可以通过union来替换or
    select * from dept where dname='jaskey' union
    select * from dept where loc='bj' union
    select * from dept where deptno=45

  5. 什么样的字段适合建索引

    • 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
    • 较频繁的作为查询条件的字段应该创建索引

  6. 什么时候用复合索引

    where a = x and b =xx 是建立索引a，索引b，索引ab，索引ba？

    看区分度

  7. 最左前缀原则

    复合索引（a,b,c），可以用到索引的情况是

    where a=x and b=x and c=x

    where a=x and b=x

    where a=x

    其余where条件都不能用到索引

  8. order by 索引

    单独使用order by是不能用索引的，需要配合limit

    where+orderby时需要加复合索引，比如where a=x order by b,c需要加（a,b,c）索引

  9. 当有多个索引，选择哪个

    mysql 会自动选择返回集合小的索引

  10. 不要过早优化，除非仔细分析

    explain工具

  11. Case Study 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
     CREATE TABLEt_cms_comment` (

      id bigint(20) NOT NULL AUTO_INCREMENT,

      content text,

      create_time timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

      platform_id int(11) DEFAULT NULL,

      update_time timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),

      target_type varchar(30) DEFAULT NULL,

      target_id bigint(20) DEFAULT NULL,

      content_type varchar(30) DEFAULT NULL,

      interaction_type varchar(30) DEFAULT NULL,

      user_id bigint(20) DEFAULT NULL,

      variety varchar(255) DEFAULT NULL,

      variety_id bigint(20) DEFAULT NULL,

      create_user bigint(20) DEFAULT NULL,

      deleted int(11) DEFAULT NULL,

      update_user bigint(20) DEFAULT NULL,

      audit_state varchar(30) DEFAULT NULL,

      author_type varchar(30) DEFAULT NULL,

      is_top bit(1) DEFAULT NULL,

      top_priority bigint(20) DEFAULT NULL,

      audit_user bigint(20) DEFAULT NULL,

      audit_time timestamp NULL DEFAULT NULL,

      re_audit_user bigint(20) DEFAULT NULL,

      PRIMARY KEY (id)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    select * from t_cms_comment where create_time < ‘2017-11-09 10:00:00’ and variety_id=5 and variety=’studio’ order by top_priority desc ,create_time desc, id desc limit 200 — 怎么加索引？

    • where create_time < ‘2017-11-09 10:00:00’ and variety_id=5 and variety=’studio’
    • order by : top_priority desc ,create_time desc, id desc
    • where + order by 复合索引，如果不行，优先考虑对sort建立索引，因为sort比较耗性能
    • 可能索引：(top_priority+create_time+id), (create_time+varierty_id),(variety_id)