mysql索引

最新推荐文章于 2024-06-17 15:31:11 发布

weixin_44224345

最新推荐文章于 2024-06-17 15:31:11 发布

阅读量95

点赞数

分类专栏： mysql索引文章标签： mysql索引

本文链接：https://blog.csdn.net/weixin_44224345/article/details/86466889

版权

mysql索引专栏收录该内容

1 篇文章 0 订阅

订阅专栏

2.1.2：索引

（1）什么是索引?

索引，我们可以通俗的理解为就像是书的目录，字典，车次表等等。通过不断的缩小想要获取的数据的范围最终得到我们想要获得的数据。

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。在数据库关系图中，可以在选定表的"索引/键"属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。

（2）索引干什么用的？有什么作用?

创建索引可以大大提高系统的性能。第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因；第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

1.索引的作用

(1)快速取数据;

(2)保证数据记录的唯一性;

(3)实现表与表之间的参照完整性;

(4)在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间

2.索引的优，缺点

优点：

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：

1.索引需要占物理空间。

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

（3）索引的分类

1．普通索引

在创建普通索引时，不添加任何限制条件。这类的索引可以创建在任何的数据类型中。这类索引的唯一作用就是增加查询的速度，所以一般为那些最常出现在查询条件中的字段添加索引。

2．唯一索引

使用unique参数可以设置索引为唯一索引。在创建索引时，就规定该索引的值必须是唯一的。即不允许出现相同的值。通过唯一索引，可以更快速的确定某条记录。主键索引就是一种特殊的唯一索引，但是主键索引不允许值为空，唯一索引值可以为空。

3．主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

4．全文索引

使用fulltext参数可以设置索引为全文索引，全文索引只能创建在char，varchar，text类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。但只有MyISAM存储引擎支持全文检索。

5．复合索引

多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

6．空间索引

使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

（4）索引的操作

查看索引

show index in table_name；

普通索引（简单索引）

CREATE INDEX index_name ON table_name (column_name)

唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column_name)

主键索引

在创建表时，添加主键，便自动创建朱建索引；

    CREATE TABLE user(
        id int unsigned not null auto_increment,
        name varchar(50) not null,
        email varchar(40) not null,
        primary key (id)
    );

复合索引

CREATE INDEX index_name ON table_name (column_name, column_name，….) 

在创建表时创建复合索引

    CREATE TABLE test (
        id INT NOT NULL,
        last_name CHAR(30) NOT NULL,
        first_name CHAR(30) NOT NULL,
        PRIMARY KEY (id),
        INDEX name (last_name,first_name)
   );

全文索引

CREATE FULLTEXT INDEX index_name ON table_name(column_name)

删除索引

drop index index_name on table_name ;

（5）索引失效

https://blog.csdn.net/wuseyukui/article/details/72312574

2.1.3：性能分析

介绍
- 使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的。分析你的查询语句或是表结构的性能瓶颈；
使用方法：
- explain + sql 语句；
- 分析：
- - id
  - - select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序；
    - id相同，执行顺序由上至下；
    - id不同如果子查询，id的序号会递增，id值越大优先级约高，越先被执行；
    - id相同不同，同时存在；（id大的先执行，然后相同的顺序执行）（derived）;
  - select_type
  - - 查询的类型；
    - 1，simple：简单的select查询，查询中不包含子查询或者union；
    - 2，primary：查询中若包含任何复杂的子部分，最外层查询则标记为；（最后加载的）
    - 3，subquery：在select或者where列表中包含了子查询；
    - 4，derived：在from列表中包含的子查询被标记问derived（衍生），mysql会递归执行这些子查询，把结果放在临时表里；
    - 5，union：若第二个select出现在union之后，则被标记为nunion；
    - 6，union result：从union表获取结果的select ；
  - table :显示这一行的数据是关于哪张表的；
  - type：
  - - 访问类型
    - 从最好到最差依次是：
    - - system>const>eq_ref>ref>range>index>ALL；
    - 1，system：表只有一行记录（等同于系统表），平时不会出现，这个也可以忽略不计；
    - 2，const ：表示通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将该查询转换为一个常量；
    - 3，eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描；
    - 4，ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体；
    - 5，range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现了between，<，>，in 等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
    - 6，index：全索引扫描，index和ALL的区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小，但index是从索引中读取的，而all是从硬盘中读取的；
    - 7，ALL：全表扫描；最差的结果；
  - possible_keys：显示可能应用在这张表中的索引，一个或者多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定别查询实际使用；
  - key：实际使用了那个索引；如果为null，则没有使用索引；查询中入使用了覆盖索引，则该索引仅出现在key列表中。
  - key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
  - ref：显示索引的哪一列被使用了，如果可能的话，是一个常数（const），哪些列或常量被用于查找索引列上的值；
  - rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数；
  - Extra：包含不适合在其他列中显示但十分重要的额外信息；
  - - 1：using filesort，（九死一生）文件内排序，就是我们自己创建的索引没有被使用到，而是自己内部重新排序，（出现这个应该尽快优化）；
    - 2：using temporary：（死定了）使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询 group by。
    - 3：using index：表示相应的select操作中送了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现using where ，表明索引被用来执行索引键值的查找，如果没有同时出现using where ，表明索引来读取数据而非执行查找动作；
    - - 覆盖索引：就是select的数据列只用从索引中就能够取到，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说，查询列要背所建的索引覆盖；
    - 4：using where ：表示使用到了where ；
    - 5：using join buffer ：使用到了连接缓存；
    - 6：impossible where ： where子句的值总是false，不能用来获取任何元素；
    - 7：select tables optimized away ：在没有group by子句的情况下，基于索引优化min/max操作或者对于myISAM存储引擎优化count(*)操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即完成优化。
    - 8：distinct ：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作；
作用
- 表的读取顺序；
- 数据读取操作的操作类型；
- 那些索引可以使用；
- 那些索引被实际使用；
- 表之间的引用；
- 每张表有多少行别优化器查询；