索引介绍

  • 索引就好比一本书的目录,它会让你更快的找到内容
  • 让获取的数据更有目的性,从而提高数据库检索数据的性能。

MySQL数据库中索引的类型

  • BTREE:B+树索引(平时所见大部分索引)
  • HASH:HASH索引
  • FULLTEXT:全文索引
  • RTREE:R树索引

MySQL索引管理

  • 索引建立在表的列上(字段)的。
  • 在where后面的列建立索引才会加快查询速度。 pages<---索引(属性)<----查数据。
  • 索引分类:
    主键索引
    普通索引*****
    唯一索引
  • 添加索引:
    ##在已有表test中添加索引name
    alter table test add index index_name(name); 
    ##等效
    create index index_name on test(name);
  • 通过表查询索引信息(Key 中显示索引类型)
    mysql> desc world.city; #world数据库下的city表
    +-------------+----------+------+-----+---------+----------------+
    | Field       | Type     | Null | Key | Default | Extra          |
    +-------------+----------+------+-----+---------+----------------+
    | ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
    | Name        | char(35) | NO   |     |         |                |
    | CountryCode | char(3)  | NO   | MUL |         |                |
    | District    | char(20) | NO   |     |         |                |
    | Population  | int(11)  | NO   |     | 0       |                |
    +-------------+----------+------+-----+---------+----------------+
    5 rows in set (0.00 sec)
    
    mysql> 

     

  • 直接查询索引(desc city 或 show index from city)

    mysql> desc city;
    +-------------+----------+------+-----+---------+----------------+
    | Field       | Type     | Null | Key | Default | Extra          |
    +-------------+----------+------+-----+---------+----------------+
    | ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
    | Name        | char(35) | NO   |     |         |                |
    | CountryCode | char(3)  | NO   | MUL |         |                |
    | District    | char(20) | NO   |     |         |                |
    | Population  | int(11)  | NO   |     | 0       |                |
    +-------------+----------+------+-----+---------+----------------+
    5 rows in set (0.00 sec)
    
    mysql> show index from city;
    +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | city  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
    | city  |          1 | CountryCode |            1 | CountryCode | A         |         465 |     NULL | NULL   |      | BTREE      |         |               |
    +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    2 rows in set (0.00 sec)
    
    mysql> 

     

  • 用explain查看SQL执行计划中的索引

    mysql> explain select * from city where CountryCode = 'CHN';
    +----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
    | id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                 |
    +----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
    |  1 | SIMPLE      | city  | ref  | CountryCode   | CountryCode | 3       | const |  363 | Using index condition |
    +----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
    1 row in set (0.01 sec)
    
    mysql>

     

MySQL中的约束索引

  • 主键索引:只能有一个主键。
    -- 主键索引:列的内容是唯一值,高中学号。
    -- 表创建的时候至少要有一个主键索引,最好和业务无关。
    ### 建表时,添加主键
    CREATE TABLE `test` (
      `id` int(4) NOT NULL AUTO_INCREMENT,
      `name` char(20) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
    
    ### 建表后,添加主键
    CREATE TABLE `test` (
      `id` int(4) NOT NULL,
      `name` char(20) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
    增加自增主键
    alter table test change id id int(4) primary key not null auto_increment;
    

     

  • 普通索引
    -- 加快查询速度,工作中优化数据库的关键。
    -- 在合适的列上建立索引,让数据查询更高效。
    alter table test add index index_name(name);
    create index index_name on test(name);

    -- 用了索引,查一堆内容。     
    -- 在where条件关键字后面的列建立索引才会加快查询速度。

    select id,name from city where state = 1 order by id group by name;

     

  • 唯一索引
    -- 内容唯一,但不是主键。
    create unique index index_name on city(name);

     

  • 使用字段前缀创建索引及联合索引
    -- 前缀索引:根据字段的前N个字符建立索引

    create index index_name on city(name(8));

    -- 联合索引:多个字段建立一个索引。

    where a=女生 and b=身高165 and c=身材好
    index(a,b,c)
    特点:前缀生效特性。
    a,ab,abc  可以走索引。
    b ac bc c 不走索引。
    原则:把最常用来作为条件查询的列放在前面。
    select * from people where a = 'nv' and b > 165 and tizhong <= 120;

    例1:

    alter table test add sex char(4) not null;
    create index ind_name_sex on test(name,sex);
    explain select id,name from test where name='oldboy'\G
    explain select id,name from test where sex='nv'\G
    explain select id,name from test where name='oldgirl' and sex='nv'\G

    例2:

    ##联合主键是联合索引的特殊形式:
    PRIMARY KEY (`Host`,`User`)
    alter table test add sex char(4) not null;
    create index ind_name_sex on test(name,sex);

    例3:

    ##前缀加联合索引
    create index index_name on test(name(8),sex(2));

     

数据库索引的设计原则
为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引。那么索引设计原则又是怎样的?

  • 选择唯一性索引
    唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。
    例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话,可能存在同名现象,从而降低查询速度。
    主键索引和唯一键索引,在查询中使用是效率最高的。
  • 为经常需要排序、分组和联合操作的字段建立索引
    经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间。如果为其建立索引,可以有效地避免排序操作。
  • 为常作为查询条件的字段建立索引
    如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,为这样的字段建立索引,可以提高整个表的查询速度。
  • 尽量使用前缀来索引
    如果索引字段的值很长,最好使用值的前缀来索引。例如,TEXT和BLOG类型的字段,进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度。
  • 限制索引的数目
    索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。
  • 尽量使用数据量少的索引
    如果索引的值很长,那么查询的速度会受到影响。例如,对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
  • 删除不再使用或者很少使用的索引
    表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。

数据库开发规范

  • 企业SQL优化思路:
    1、把一个大的不使用索引的SQL语句按照功能进行拆分
    2、长的SQL语句无法使用索引,能不能变成2条短的SQL语句让它分别使用上索引。
    3、对SQL语句功能的拆分和修改
    4、减少“烂”SQL     由运维(DBA)和开发交流(确认),共同确定如何改,最终由DBA执行
    5、制定开发流程
  • 不适合走索引的场景:
    1、唯一值少的列上不适合建立索引或者建立索引效率低。例如:性别列
    2、小表可以不建立索引,100条记录。
    3、对于数据仓库,大量全表扫描的情况,建索引反而会慢
  • 建索引流程:
    1、找到慢SQL。
         #show processlist;
         #记录慢查询日志。
    2、explain select句,条件列多。
    3、查看表的唯一值数量:
    select count(distinct user) from mysql.user;
    select count(distinct user,host) from mysql.user;

    4、建立索引(流量低谷)
         #force index
    5、拆开语句(和开发)。
    6、like '%%'不用mysql 

最后修改于 2019-08-07 09:13:49
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付
上一篇