认识达内从这里开始

认真做教育专心促就业

Mysql数据库索引的介绍与使用方针

发布：烟台达内科技
来源：互联网
时间：2017-11-03 15:14

    概述

    索引是什么

    索引可以简单地理解为“排好序的快速查找数据结构”

    一般来说索引本身业很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

    我们平时所说的索引，没有特别指明，都是B树（多路搜索树）结构组织的索引。其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引等

    索引的优势

    类似大学图书馆数目索引，提高数据检索的效率，降低数据库的IO成本

    通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗

    索引的劣势

    实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，索引索引列也是要占空间的

    虽然buoying大大提高了初旬速度，同时却会降低更新表的速度（INSERT,UPDATE,DELETE）

    索引只是提高效率的一个因素，如果你的MYSQL有大数据的表，就需要话时间研究简历最优秀的索引，或优化查询。

    一丶索引的分类

    单值索引

    即一个索引只包含单个列，一个表可以有多个单列索引，最多不要超过五个

    唯一索引

    索引列的值必须唯一，但允许有空值

    复合索引

    即一个索引包多个列

    二丶基本语法

    创建

    CREATE [UNIQUE] INDEX idnex_name ON tbl_name（column_name（length））；

    ALTER tbl_name ADD [UNIQUE] INDEX[index_name] ON （column_name（length））；

    删除

    DROP INDEX [indexName] ON tbl_name;

    查看

    SHOW INDEX FROM tbl_name;

    使用ALTER命令

    4.1 ALTER TABLE tbl_name ADD PRIMARY KEY（columu_name）；该语句添加爱一个主键，这意味着索引值必须是唯一的，且不能为NULL.

    4.2 ALTER TABLE tbl_name ADD UNIQUE index_name（olumu_name）；这条语句创建的索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

    4.3 ALTER TABLE tbl_name ADD INDEX index_name（olumu_name）；添加普通索引，索引值可出现多次。

    4.4 ALTER TABLE tbl_name ADD FULLTEXT index_name（olumu_name）；该语句指定了索引为FULLTEXT,用于全文索引。

    三丶哪些情况需要创建索引

    主键自动建立唯一索引

    品份作为查询条件的字段应该创建索引

    查询中与其他表关联的字段，外键关系建立索引

    频繁更新的字段不适合创建索引

    Where条件里不用不到的字段不创建索引

    单键/组合索引的选择问题（在高并发下倾向创建组合索引）

    查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

    查询中统计或者分组字段

    四丶哪些情况不需要创建索引

    1.表的记录太少

    2.经常增删改的表

    3.数据重复且分布平均的，某个数据列包含许多重复内容的，建立索引就没有太大的实际效果。

    1丶选择索引的数据类型

    MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：

    （1）越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

    （2）简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。

    （3）尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL.在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

    2、选择主键类型

    选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。

    （1）整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT.

    （2）字符串：尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。

    Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

    PRIMARY KEY（主键索引） ALTER TABLE `table_name` ADD PRIMARY KEY （ `col` ）

    UNIQUE（唯一索引） ALTER TABLE `table_name` ADD UNIQUE （`col`）

    INDEX（普通索引） ALTER TABLE `table_name` ADD INDEX index_name （`col`）

    FULLTEXT（全文索引） ALTER TABLE `table_name` ADD FULLTEXT （ `col` ）

    组合索引 ALTER TABLE `table_name` ADD INDEX index_name （`col1`, `col2`, `col3` ）

    Mysql各种索引区别：

    普通索引：最基本的索引，没有任何限制

    唯一索引：与“普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。

    主键索引：它是一种特殊的唯一索引，不允许有空值。

    全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。

    组合索引：为了更多的提高mysql效率可建立组合索引，遵循“最左前缀”原则。创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。

组合索引最左字段用in是可以用到索引的，最好explain一下select.

更多烟台达内科技相关资讯，请扫描下方二维码

烟台达内科技