mysql索引入门总结

这是一篇适合初学者看的索引入门博客，耐心看完，会学会的，参考总结出来的！！！！！！！！！！！

一. 索引是什么？

    -  索引是表的目录，类似于字典中的目录，用于快速定位查询数据，索引会保存在额外的文件中。
    -  索引可以提高查询速度，会减慢写入速度，索引的缺点市创建和维护索引需要耗费时间。
    -  索引也不是越多越好，索引虽然可以提高查询效率，但同时页降低了insert和update效率，因为在insert或update时有可能会重建索引。
    -  所以怎样建索引需要慎重考虑，视情况而定。一个表的索引数最好不要超过5个，若太多则应考虑一些不常用的列上建索引是否有必要。

    2.1 什么样的字段适合创建索引？

 

        2.1.1 表的主键，外键必须有索引;外键是唯一的，而且经常会用来查询

        2.1.2 经常与其他表进行连接的表，在连接字段上应该建立索引;

        2.1.3 数据量超过300的表应该有索引;

        2.1.4 重要的SQL或调用频率高的SQL，比如经常出现在where字句中的字段，order by ,group by , distinct的字段都要添加索引

        2.1.5 经常用到排序的列上，因为索引已经排序。

        2.1.6 经常用到范围内搜索的列上创建索引，因为索引已经排序了，其指定的范围时连续的

    3.1 什么场景不适合创建索引

        3.1.1 对于一些查询很少使用的或者参考的列不应该创建索引
                 如果列很少使用时，有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
        3.1.2 对于哪些只有很少数据值的列也不应该增加索引
                因为本来结果集合就是相当于全表查询了，所以没有必要。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比 例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显                  加快检索速度。
        3.1.3 text,image,bit数据类型不应设为索引
                 这是因为，这些列的数据量要么相当大，要么取值很少。
        3.1.4 当修改性能远远大于检索性能时，不应该创建索引。
                 这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因 此，当修改性能远远大于检索性能时，不应该创建索引。
        3.1.5 不会出现在where条件中的字段不该建索引

    4.1 索引的使用以及设计规范

        4.1.1 越小的数据类型通常更好：越小的数据类型通常在磁盘，内存和cpu缓存中都需要更少的空间，处理起来更快。简单的数据类型更好：整型数据比字符处理开销更小，因为字符串的标胶更复杂

        4.1.2 尽量避免null：应该指定为not null ，含有空值的列很难进行查询优化，因为他们是的索引，索引的统计信息以及比较运算更加复杂，也可能导致复合索引无效

        4.1.3 主键选择策略

            - 每个表必须显示指定主键;

            - 主键尽量为一个字段，且为数据类型，避免使用字符串;

            - 主键尽量保持增长趋势，建议使用id的生成器

            - 主键杜绝使用联合索引

        4.1.4 每个表的索引尽量少于5个，避免创建重复冗余索引，每个组合索引避免超过三个字段，索引不是越多越好，谨慎添加索引，综合考虑数据分析和数据更新

        4.1.5 重要的SQL或调用频率的SQL

            - update/select/delete的where条件列字段都要添加索引

            - order by /group by distinct 的字段都要添加索引

        4.1.6 避免出现 index merge （单索引 or 的查询）;合理利用covering index

        4.1.7 组合索引创建时，把区分度（选择性高）的字段放在前面;根据SQL的特性，调用组合索引的顺序

        4.1.8 对于varchar字段加索引，建议使用前缀索引，从而减小索引大小

二. 索引分类

    2.1 普通索引

        - 仅加速查询 最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。
        - 例：

CREATE INDEX  employee_no_index ON  ums_account(employee_no); 

- SHOW INDEX FROM `ums_account`

 

    2.2 唯一索引

        - 与普通索引类似，不同的是：加速查询+列值唯一（可以有null）
        - 例：

CREATE UNIQUE INDEX  username  ON ums_account(username)

 

    2.3 全文索引

        - 全文索引（full-text）仅可以适用于MyISAM引擎的数据表;作用于char，varchar，text数据类型的列。

    2.4 组合索引

      - 将几列作为一条索引进行检索，使用最左匹配原则

三. 索引的创建和删除

 

•     注意：对于创建索引时如果blob和text类型，必须指定length

    3.1 创建表时同时创建索引

 CREATE TABLE amy_index_test (
        id   BIGINT(20)  NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT  '主键',
        NAME  VARCHAR(32) NOT NULL COMMENT '姓名',
        email VARCHAR(64) NOT NULL COMMENT  '邮箱',
        message TEXT DEFAULT  NULL COMMENT '个人信息',
        INDEX index_name (NAME) COMMENT '索引name'
        ) COMMENT  = '索引测试表';
        
SHOW INDEX FROM `amy_index_test`

    3.2 在存在的表上创建索引（三种方式）

 1- CREATE INDEX NAME ON `amy_index_test`(NAME)
 2- CREATE INDEX message ON `amy_index_test`(message(200));
 3- ALTER TABLE `amy_index_test` ADD INDEX emp_name (NAME);

    3.3 删除索引（2种方式）

 - alter table amy_index_test  drop index  emp_name ;
 - drop index name ON `amy_index_test`;

    3.4 查看索引

• 这个时候我们会发现其实主键id也会是一个索引

  - SHOW INDEX FROM `ums_account`  

  
四.联合索引和单索引

  -大神博客：https://blog.csdn.net/Abysscarry/article/details/80792876

五.使用索引

  - 如果以错误的方式使用，则即使建立索引也会不奏效。

    5.1 使用 like 的查询

 - 对于使用 like 的查询，只有%号不在第一个字符,索引才可能会被使用

    5.2 column_name is null

-  如果列是索引，使用 column_name is null 将使用索引

    5.3 列是字符型，，传入的是数字，则匹配不上'' 不会使用索引   

六.索引命名规范

  【强制】 主键索引名为 pk_表名_字段名；唯一索引名为 uk_表名_字段名；普通索引名则为 idx_表名_字段名。

       

  ##### 年纪大了，前一天学的内容，第二天就能忘！！！！！！！！！！！！！