Linux——Mysql索引和事务-一，Mysql索引介绍

时间：2025-05-14 21:52:44

1，索引概述

MySQL 索引是一种用于提高数据库查询效率的数据结构，

  • 就像书籍的目录一样，能帮助数据库快速定位到所需的数据，而不必全表扫描。例如，在一个包含大量用户信息的表中，如果经常要根据用户姓名来查询记录，那么为 “姓名” 字段建立索引后，查询速度会显著提升。
  • 原理：索引通常是基于 B 树（B - Tree）或哈希表等数据结构来实现的。以 B 树为例，它将数据按照一定的规则组织成树形结构，节点中的数据是有序排列的。当进行查询时，数据库可以通过比较查询值与节点中的数据，快速决定是在当前节点的左子树还是右子树继续查找，从而大大减少了查找的范围和时间。

1，索引的优点

(1)，提高查询速度

  • 索引可以显著加快数据检索速度，特别是对大表查询
  • 类似于书籍的目录，可以快速定位到所需数据

(2)，加速表连接

  • 在多表连接操作时，索引能极大提高连接效率

(3)，保证数据唯一性

  • 唯一索引可以确保列中数据的唯一性

(4)，优化排序和分组

  • 对索引列进行ORDER BY或GROUP BY操作时效率更高

(5)，减少服务器扫描1数据量

  • 数据库引擎可以跳过不必要的数据行

2，索引的缺点

(1)：占用存储空间

  • 索引需要额外的磁盘空间存储
  • 对于大型表，索引可能占用相当可观的存储

(2)：减低写入性能

  • 插入、更新和删除操作需要同时维护索引
  • 每次数据修改都可能需要更新多个索引

(3)：维护成本

  • 索引需要定期维护以保持高效
  • 随着数据变化，索引可能变得碎片化

(4)：优化器选择问题

  • 有时查询优化器可能选择不理想的索引
  • 需要DBA监控和调整索引使用

(5)：设计复杂性

  • 需要合理设计索引策略，过多或不当的索引反而会降低性能

2，索引作用

3，索引分类

在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引,可以令 MySQL 的查询和运行更加高效。索引是快速搜索的关键。MySQL 索引的建立对于 MySQL 的高效运行是非常重要的。

     从物理存储的角度来划分，索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索更快。
从逻辑的角度来划分，索引分为普通索引、唯一索引、主键索引、组合索引和全文索引。

  • 普通索引

普通索引是最基本的索引，它没有任何限制，也是大多数情况下用到的索引

##准备创建索引的库和表
create database auth;             ##创建auth库
use auth;                        
create table users (id int(10),user_name char(20),user_pass char(50));    ##创建表

##直接创建索引：语法:create index 索引名 on 表名 索引的值;
create index aaa on users(user_name(20));

##修改表结构添加索引
alter table users(表名) add index bbb(索引名) （user_pass(50) "表中的值");


user_name(20)其中 20 是可选项。如果忽略 20 的值，则使用整个列的值作为索引。如果指定使用列前的 20个字符来创建索引,就是使用列的一部分来创建索引，这样有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限255个字节(MyISAM和 InnoDB 表的最大上限为 1000 个字节)，如果索引列的长度超过了这个上限,就只能用列的前缀进行索引。另外，BLOB或TEXT类型的列也必须使用前缀索引。

 

  • 唯一索引

唯一索引与普通索引类似，不同的就是:唯一索引的索引列的值必须唯一，但允许有空值(注意和主键不同)。如果是组合索引，则列值的组合必须唯一。唯一索引创建方法和普通索引类似。

##创建唯一索引
create unique index bbb on users(id);
mysql> create unique index bbb on users(id);
 
mysql> show index from users\G             ##查看users表的索引
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: bbb
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.01 sec)

##修改表结构的时候添加唯一索引：
alter table users add unique ccc(user_name);

 

  • 主键索引

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

创建主键语法：

create table 表名 （列名 数据类型 primary key,）;

##创建主键索引，表名为students
mysql> CREATE TABLE students (
    ->     student_id INT PRIMARY KEY,
    ->     name VARCHAR(50),
    ->     age INT
    -> );
Query OK, 0 rows affected (0.03 sec)


mysql> show index from students\G      #查看表中的主键信息
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: student_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

 

  • 组合索引

平时用的 SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL 的效率，就要考虑建立组合索引。在组合索引的创建中，有两种场景，即为单列索引和多列索引。

语法：

   CREATE TABLE 表名 (
    列1 数据类型,
    列2 数据类型,
    ...,
    INDEX 索引名 (列1, 列2, ...)
);

 

mysql> CREATE TABLE orders (
    ->     order_id INT,
    ->     customer_id INT,
    ->     order_date DATE,
    ->     INDEX idx_customer_order (customer_id, order_date)
    -> );



mysql> show index from ordes\G        ##查看创建的组合索引
ERROR 1146 (42S02): Table 'auth.ordes' doesn't exist
mysql> show index from orders\G
*************************** 1. row ***************************
        Table: orders
   Non_unique: 1
     Key_name: idx_customer_order
 Seq_in_index: 1
  Column_name: customer_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: orders
   Non_unique: 1
     Key_name: idx_customer_order
 Seq_in_index: 2
  Column_name: order_date
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
2 rows in set (0.01 sec)

 

  • 全文索引

对于较大的数据集，将资料输入一个没有FULLTEXT 索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT 索引的速度更快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间、非常消耗硬盘空间的做法。

语法：

CREATE TABLE 表名 (
    列1 数据类型,
    列2 数据类型,
    ...,
    FULLTEXT INDEX 索引名 (列名) [WITH PARSER ngram]
) ENGINE=InnoDB;

 

##创建全文索引
mysql> CREATE TABLE articles (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     title VARCHAR(200),
    ->     content TEXT,
    ->     FULLTEXT INDEX ft_idx_title_content (title, content)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)


##查看全文索引
mysql> show index from articles\G
*************************** 1. row ***************************
        Table: articles
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
....../省略部分内容

4，查看索引

MySQL 数据表索引已经创建好了，那么如何才能查看刚刚创建的索引?或者怎么去查看表内已经存在的索引?有以下两种查看当前索引的方式。

##查看某个表的索引

show index from 表名;     

show keys from 表名

mysql> show index from users\G             ##查看users表的索引
*************************** 1. row ***************************
        Table: users                 ##表的名称
   Non_unique: 0                     ##如果索引不能包括重复词，则为0;如果可以，则为1。
     Key_name: bbb                   ##索引的名称
 Seq_in_index: 1                     ##索引中的列序号，从 1开始。
  Column_name: id                    ##列名称
    Collation: A                     ##列以什么方式存储在索引中。在 MySQL 中，有值'A’(升序)或 NULL(无分类)。
  Cardinality: 0                     ##索引中唯一值数目的估计值。通过运行 ANALYZE TABLE 或myisamchk-a 可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
     Sub_part: NULL                  ##如果列只是被部分地编入索引，则为被编入索引的字符的数目。
如果整列被编入索引，则为 NULL。
       Packed: NULL                  ##如果列含有 NULL，则含有YES。如果没有，则该列含有 NO。
   Index_type: BTREE                 ##用过的索引方法(BTREE,FULLTEXT, HASH,RTREE)。
      Comment:                       ##备注。

5，删除索引

索引在创建之后,是会占用一定的磁盘空间的,因此表内如果有不再使用的索引从数据库性能方面考虑，最好是删除无用索引。索引的删除有如下两种方法。

drop index 索引名 on 表名;

alter table 表名 drop index 索引名;

6，索引的应用场景

  • 快速查找：在大型数据库表中，通过索引可以快速定位到满足特定条件的数据行。例如，在一个包含数百万条记录的用户表中，根据用户 ID 或用户名进行查询时，索引可以大大减少查询时间。
  • 多条件查询：当查询涉及多个条件时，索引可以帮助数据库快速定位到满足所有条件的数据。例如，在一个订单表中，查询特定日期范围内、特定客户的订单，通过对订单日期和客户 ID 建立索引，可以快速找到符合条件的订单记录。
  • 快速排序：索引可以按照特定的列进行排序，从而加快数据的排序速度。例如，在一个产品表中，按照价格对产品进行排序，如果价格列上有索引，数据库可以直接使用索引来快速获取排序后的结果，而无需对整个表进行排序操作。
  • 分组排序：在进行分组查询时，索引也可以帮助提高排序效率。例如，在一个销售表中，按照地区对销售数据进行分组，并对每个地区的销售额进行排序。通过对地区列和销售额列建立索引，可以快速完成分组和排序操作。

7，不适合使用索引的场景

  • 数据量小的表：当表中的数据量较少时，全表扫描的成本很低，使用索引可能会增加额外的开销，而不会带来明显的性能提升。例如，一个只有几十条记录的表，直接全表扫描查找数据可能比通过索引查找更快，因为索引的维护和查找本身也需要消耗一定的资源。
  • 频繁更新的表：对于频繁进行插入、更新和删除操作的表，索引的维护成本较高。每次数据发生变化时，都需要更新相应的索引，这会增加数据库的负担，降低数据更新的性能。例如，在一个实时交易系统中，交易记录表可能会频繁地插入新记录，如果为该表的每个列都建立索引，那么每次插入新交易记录时，都需要更新多个索引，这会大大影响系统的插入性能。

相关文章