mysql索引使用基础

1.创建&删除

MySQL可以通过CREATE、ALTER、DDL三种方式创建一个索引。

在MySQL中，使用CREATE INDEX语句可以创建索引。具体语法如下：

CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);
 
其中，indexName是索引的名称，tableName是要在其上创建索引的表名，columnName是要创建索引的列名，length是可选参数，用于指定索引的长度，ASC表示升序排序，DESC表示降序排序。

例如，如果要在名为students的表上为name列创建一个名为index_name的索引，可以使用以下语句：

CREATE INDEX index_name ON students (name);
 
如果要在名为employees的表上为age列创建一个名为index_age的索引，并按照年龄降序排序，可以使用以下语句：

CREATE INDEX index_age ON employees (age DESC);

 

在MySQL中，使用ALTER TABLE语句可以添加索引。具体语法如下：
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);

其中，tableName是要在其上添加索引的表名，indexName是索引的名称，columnName是要创建索引的列名，length是可选参数，用于指定索引的长度，ASC表示升序排序，DESC表示降序排序。

例如，如果要在名为students的表上为name列创建一个名为index_name的索引，可以使用以下语句：
ALTER TABLE students ADD INDEX index_name(name);

    
如果要在名为employees的表上为age列创建一个名为index_age的索引，并按照年龄降序排序，可以使用以下语句：
ALTER TABLE employees ADD INDEX index_age(age DESC);

 

在MySQL中，使用CREATE TABLE语句可以创建表。具体语法如下：
CREATE TABLE tableName(
  columnName1 dataType NOT NULL,
  columnName2 dataType,
  ....,
  INDEX indexName (columnName(length))
);

    
其中，tableName是要创建的表名，columnName是列名，dataType是数据类型，NOT NULL表示该列不能为空，indexName是索引的名称，columnName(length)是要创建索引的列名和长度。

例如，如果要创建一个名为students的表，包含id、name和age三个列，并为name列创建一个名为index_name的索引，可以使用以下语句：
CREATE TABLE students(
  id INT(8) NOT NULL,
  name VARCHAR(50),
  age INT(3),
  INDEX index_name (name)
);

 

mysql中没有修改索引语句，只能删除然后再重新添加

在MySQL中，使用DROP INDEX语句可以删除索引。具体语法如下：
DROP INDEX indexName ON tableName;
  
其中，indexName是要删除的索引名称，tableName是要在其上删除索引的表名。
例如，如果要从名为students的表中删除名为index_name的索引，可以使用以下语句：
DROP INDEX index_name ON students;

　　

2.索引本质

数据库索引的本质是一种数据结构，用于提高数据库查询的性能。

索引的工作原理基于为数据库表中的一列或多列创建一个额外的数据结构，这个数据结构允许快速地定位到存储数据的位置，从而加快查询速度。具体来说，索引通常包含两列：索引字段和指向原记录

指针。索引表按照索引字段的值进行排序，这样在执行查询时，数据库管理系统(DBMS)可以采用高效的查找算法（如二分查找）来快速定位到所需的记录。

以下是索引的具体优点和缺点：

优势：

  1. 提高检索速度：索引可以显著加快数据的查询速度，尤其是当表中数据量较大时更为明显。通过索引，数据库能够快速定位到所需数据，避免全表扫描。
  2. 保证数据唯一性：唯一索引确保了数据库表中每一行数据的唯一性，这对于维护数据的完整性非常重要。
  3. 加速连接操作：在涉及多表连接的查询中，有索引的列可以加速表与表之间的连接操作。
  4. 减少排序和分组时间：在使用ORDER BY或GROUP BY子句进行数据检索时，索引可以减少查询中的排序和分组时间。
  5. 优化性能：正确创建和使用索引是实现高性能查询的基础，有助于提高整个系统的性能。

劣势：

  1. 占用空间：每个索引都会占用一定的物理空间，如果建立聚簇索引，所需的空间会更大。
  2. 维护成本：创建和维护索引需要耗费时间，这种时间随着数据量的增加而增加。
  3. 降低数据维护速度：在进行INSERT、UPDATE、DELETE等操作时，由于索引需要动态维护，这些操作的速度会相应降低。
  4. 查询优化器选择：有时候，即使存在索引，查询优化器也可能会选择不使用索引，特别是在评估索引带来的性能提升不足以弥补其维护成本时。

总的来说，索引虽然在空间和更新操作上有所牺牲，但换来的是查询效率的大幅提升。在设计数据库时，合理使用索引是优化性能的重要手段之一。

3.索引区分

从表字段的层次来看，索引又可以分为单列索引和多列索引，这两个称呼也比较好理解，单列索引是指索引是基于一个字段建立的，多列索引则是指由多个字段组合建立的索引。

3.1.单列索引

在数据库中，单列索引可以根据其约束条件和用途分为几种类型，每种类型都有其特定的特点。以下是常见的单列索引类型及其特点：

  1. 普通索引（Regular Index）：

    • 特点：允许列中有重复值和空值。
    • 目的：加速对指定列的查询。

  2. 唯一索引（Unique Index）：

    • 特点：不允许列中有重复值，但可以有空值。
    • 目的：加速查询，并保证列中值的唯一性。

  3. 主键索引（Primary Key Index）：

    • 特点：不允许列中有重复值或空值。
    • 目的：加速查询，并强制表的每一行都唯一。

  4. 全文索引（Fulltext Index）：

    • 特点：用于全文搜索，支持基于文本内容的查询，通常用于VARCHAR或TEXT类型的列。
    • 目的：加速全文搜索。

创建索引的SQL语句示例（以MySQL为例）：

-- 创建普通索引
CREATE INDEX index_name ON table_name(column_name);

-- 创建唯一索引
CREATE UNIQUE INDEX unique_index_name ON table_name(column_name);

-- 创建主键索引
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

-- 创建全文索引（仅适用于支持全文索引的存储引擎，如InnoDB）
ALTER TABLE table_name
ADD FULLTEXT INDEX fulltext_index_name(column_name);

 

创建索引的SQL代码示例：

-- 假设我们有一个名为 `users` 的表，包含 `id`, `username`, `email` 列

-- 创建普通索引
CREATE INDEX idx_users_username ON users(username);

-- 创建唯一索引，以确保每个用户有唯一的电子邮件地址
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 设置 `id` 为主键，自动创建主键索引
ALTER TABLE users
ADD PRIMARY KEY (id);

-- 如果表支持全文搜索，可以创建一个全文索引来优化对 `content` 列的搜索
CREATE FULLTEXT INDEX ft_users_content ON users(content);

 

3.2.多列索引

多列索引是一种在多个列上创建的索引，它允许根据多个列的值进行查询。多列索引可以提供更高效的查询性能，特别是在需要对多个列进行筛选或排序的情况下。

以下是常见的多列索引类型及其特点：

  1. 联合索引（Composite Index）：

    • 特点：基于多个列的值创建一个索引。
    • 目的：加速涉及多个列的查询操作。

  2. 覆盖索引（Covering Index）：

    • 特点：包含查询所需的所有数据的索引。
    • 目的：避免额外的数据访问，提高查询性能。

  3. 前缀索引（Prefix Index）：

    • 特点：只包含指定列的前N个字符的索引。
    • 目的：减少索引的大小，提高查询性能。

  4. 全文索引（Fulltext Index）：

    • 特点：用于全文搜索，支持基于文本内容的查询，通常用于VARCHAR或TEXT类型的列。
    • 目的：加速全文搜索。

创建多列索引的SQL语句示例（以MySQL为例）：

-- 创建联合索引
CREATE INDEX index_name ON table_name(column1, column2);

-- 创建覆盖索引
CREATE INDEX index_name ON table_name(column1, column2, ..., columnN);

-- 创建前缀索引
CREATE INDEX index_name ON table_name(column_name(N));

-- 创建全文索引（仅适用于支持全文索引的存储引擎，如InnoDB）
ALTER TABLE table_name
ADD FULLTEXT INDEX fulltext_index_name(column_name);

 

创建多列索引的SQL代码示例：

-- 假设我们有一个名为 `orders` 的表，包含 `customer_id`, `order_date`, `total_amount` 列

-- 创建联合索引，以便根据客户ID和订单日期进行查询
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- 创建覆盖索引，以便根据客户ID、订单日期和总金额进行查询，而无需额外访问表数据
CREATE INDEX idx_orders_covering ON orders(customer_id, order_date, total_amount);

-- 创建前缀索引，以便根据客户ID的前5个字符进行查询
CREATE INDEX idx_orders_customer_prefix ON orders(customer_id(5));

-- 如果表支持全文搜索，可以创建一个全文索引来优化对 `description` 列的搜索
CREATE FULLTEXT INDEX ft_orders_description ON orders(description);

请注意，创建索引时需要考虑表中数据的特点和查询模式。不当的索引可能会导致性能问题，例如，频繁更新的列不适合创建索引，因为它会导致索引维护的成本增加。此外，索引虽然可以提高查询速度，但也会占用额外的存储空间，并在插入、更新和删除操作时增加开销。因此，在创建索引前应进行仔细的规划和考虑。

 

4.索引注意点

4.1.建立索引时需要遵守以下原则

  1. 选择唯一性索引：唯一性索引可以更快速地通过该索引来确定某条记录，因为唯一性索引的值是唯一的。
  2. 为经常需要排序、分组和联合操作的字段建立索引：如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
  3. 最左前缀匹配原则：在使用SQL语句时，如果WHERE部分的条件不符合最左匹配原则，可能导致索引失效，或者不能完全发挥建立的索引的功效。
  4. =和in可以乱序：比如a = 1 and b = 2 and c = 3建立 (a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。
  5. 尽量选择区分度高的列作为索引：区分度的公式是：count (distinct col)/count (*)。区分度越高，索引的效果越好。
  6. 不要建立过多的索引：因为索引本身会占用存储空间。
  7. 考虑字段值长度较短的字段建立索引：如果字段值太长，会降低索引的效率。

4.2.联合索引的最左前缀原则

联合索引的最左前缀原则是指在使用联合索引进行查询时，查询条件需要遵循索引中列的顺序，从左到右进行匹配。

最左前缀原则是数据库优化中的一个重要概念，它要求在使用联合索引时，查询条件必须包含索引列的最左端开始的一个或多个连续的列。这个原则确保了数据库能够有效地使用索引来加速查询。以下是

一些关键点：

  • 查询条件的顺序：查询条件中的列必须按照联合索引中列的顺序出现。例如，如果有一个联合索引(a, b, c)，那么查询条件中必须首先包含列a，然后是列b，最后是列c。
  • 部分使用索引：如果查询条件只涉及到联合索引中的部分列，那么只有这些列的索引会被利用。例如，如果查询条件只包含列a和列b，那么只有这两部分的索引会被用于查询。
  • 范围查询的影响：当遇到范围查询（如大于、小于、BETWEEN等）时，最左前缀原则会停止匹配。这意味着，如果范围查询出现在联合索引的某个列上，那么该列右侧的所有列都无法使用索引。
  • 调整索引顺序：根据业务需求和查询模式，可以调整联合索引中列的顺序，以优化查询性能。例如，如果查询经常涉及列a、列b和列d，但很少涉及列c，则可以考虑创建(a, b, d, c)的联合索引。

以下是一个具体的示例来说明最左前缀原则的应用：

假设有一个表users，其包含id, username, email等字段，并且在这个表上创建了一个联合索引idx_username_email（包含username和email两个字段）。

CREATE INDEX idx_username_email ON users(username, email);

根据最左前缀原则，以下几种查询方式是符合最左前缀原则的：

 

  1.使用username和email进行查询，能够完全利用索引：

SELECT * FROM users WHERE username = '张三' AND email = 'zhangsan@example.com';

 

  2.只使用username进行查询，能够部分利用索引：

SELECT * FROM users WHERE username = '张三';

  3.使用username进行范围查询，只能利用到第一列的索引：

SELECT * FROM users WHERE username LIKE '张%';

 

而以下查询方式则不符合最左前缀原则，可能导致索引失效：

  1.只使用email进行查询，没有利用到最左侧的username：

SELECT * FROM users WHERE email = 'zhangsan@example.com';

  2.使用username进行范围查询后，又使用了email进行等于查询，但因为范围查询的存在，email的索引将不会生效：

SELECT * FROM users WHERE username > '张三' AND email = 'zhangsan@example.com';

 

4.3.索引失效

在MySQL中，即使为表的字段创建了索引，也可能由于查询语句的写法或数据的特性导致索引失效。以下是一些可能导致索引失效的情况，以及相应的SQL示例：

  1. 使用OR操作符： 如果查询条件中使用了OR连接不同字段，即使部分条件有索引，索引也可能不会使用。

    SELECT * FROM users WHERE last_name = 'Smith' OR age = 30;

    如果last_name有索引，而age没有，last_name上的索引可能不会使用。

  2. 不符合最左前缀原则： 对于联合索引，查询条件必须包含联合索引的最左边列，否则索引可能失效。

    SELECT * FROM users WHERE age = 30;

    如果有一个联合索引(last_name, age)，这个查询就不会使用索引。

  3. LIKE以通配符开头： 如果使用LIKE操作符且模式以通配符'%'开头，如LIKE '%abc'，索引通常不会生效。

    SELECT * FROM users WHERE last_name LIKE '%abc';

    即使last_name上有索引，这种查询也不会使用索引。

  4. 类型转换： 如果查询条件中对索引列进行了隐式的类型转换，可能会导致索引失效。

    SELECT * FROM users WHERE age = '30';

    如果age是整数类型，这里的字符串'30'需要进行类型转换，可能会导致索引失效。

  5. 索引列进行计算或使用函数： 如果在查询条件中对索引列使用了函数或进行了计算，索引可能会失效。

    SELECT * FROM users WHERE YEAR(birthdate) = 1990;

    如果birthdate字段有索引，使用YEAR函数可能会导致索引失效。

  6. 全表扫描更快时： 在某些情况下，即使有索引，MySQL优化器可能会选择全表扫描，因为它认为这样更快，尤其是在数据量较小的时候。

  7. 频繁更新的字段： 经常更新的字段不适合作为索引，因为每次更新都会导致索引重新构建，这会消耗大量的资源。

  8. 索引选择性差： 如果一个索引唯一性很差，即很多行都具有相同的索引值，那么使用索引的效果将不明显，因为MySQL需要在这些具有相同索引值的行中进行进一步的查找。

  9. 使用了不等于操作符： 使用不等于（<>）操作符时，索引效果通常不如等值比较。

    SELECT * FROM users WHERE age <> 30;

    即使age字段有索引，这种查询也可能不会使用索引。

  10. 复合索引未用左列字段： 在使用复合索引时，如果没有使用到最左侧的列，那么整个复合索引都可能不会被使用。

    SELECT * FROM users WHERE age = 30;

    如果有一个复合索引(last_name, age)，这个查询就不会使用索引。

 

4.3.索引正确使用姿势

在MySQL中，正确使用索引可以显著提高查询效率。以下是一些正确使用索引的SQL示例和最佳实践：

  1. 创建和使用主键索引： 主键索引是唯一的，它确保了数据的唯一性，并且MySQL会自动为主键创建索引。

    CREATE TABLE users ( id INT AUTO_INCREMENT, username VARCHAR(50), PRIMARY KEY (id) );

  2. 创建和使用唯一索引： 唯一索引保证列中的每一个值都是唯一的，这有助于加快查询速度。

    CREATE UNIQUE INDEX idx_username ON users (username);

  3. 创建和使用普通索引： 普通索引（非唯一索引）可以帮助加速查找操作。

    CREATE INDEX idx_email ON users (email);

  4. 使用复合索引： 复合索引可以在多个列上创建，有助于多列条件的快速查询。

    CREATE INDEX idx_name_age ON users (last_name, age);

  5. 避免使用OR操作符： 如果查询条件中使用了OR连接不同字段，即使部分条件有索引，索引也可能不会使用。应尽量避免使用OR，或者将OR条件拆分成多个查询。

  6. 避免在索引列上进行计算： 不要在索引列上使用函数或进行计算，这会导致索引失效。

  7. 使用LIMIT限制结果集： 使用LIMIT可以减少返回的数据量，这样即使没有索引，也能减少查询时间。

  8. 选择合适的索引类型： 根据存储引擎的不同，选择最合适的索引类型。例如，InnoDB存储引擎使用B+Tree索引数据结构。

  9. 分析查询语句： 使用EXPLAIN命令来分析查询语句，确保索引被正确使用。

  10. 避免全表扫描： 尽量减少全表扫描的情况，因为全表扫描会忽略索引，导致查询效率降低。

 

4.4.提高查询效率的重要优化策略

MySQL提高查询效率的重要优化策略包括库表结构优化、索引优化和查询优化。以下是一些具体的优化措施：

  1. 库表结构优化：
    • 避免使用SELECT *，只查询需要的列，减少数据传输量。
    • 为字段选择合适的数据类型，以减少存储空间并提高处理速度。
    • 合理设计表结构，避免过度复杂的关联关系，以提高查询效率。
  2. 索引优化：
    • 创建合适的索引，例如B树索引或哈希索引，以提高查找速度。
    • 理解索引的工作原理，避免在大量重复值的列上创建索引，因为这样会导致索引效率降低。
    • 使用复合索引时，遵循最左前缀原则，即查询条件应包含复合索引的最左侧列。
  3. 查询优化：
    • 使用EXPLAIN分析查询语句，了解MySQL是如何处理语句的，从而进行查询优化器的优化。
    • 避免在WHERE子句中使用函数或进行计算，因为这会阻止使用索引。
    • 对于大数据集，考虑分页查询优化，但要注意LIMIT语句可能导致的性能问题。
    • 合理使用连接和子查询，避免不必要的数据关联和复杂的查询结构。
    • 在适当的情况下，考虑使用索引覆盖，这样可以直接从索引中获取所需数据，而无需访问数据表的行。

      • 索引覆盖指的是查询所需的所有列都包含在索引中，因此不需要回表查询数据行。这通常通过创建包含所有需要查询字段的复合索引来实现。例如：

        • 　　　 
          创建一个包含name和age字段的复合索引。
SQL查询使用这两个字段作为过滤条件。
CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(20), age INT, INDEX name_age (name, age) ) ENGINE=InnoDB; 

          SELECT id, name FROM user WHERE name = 'shenjian' AND age = 30;

在这个例子中，如果EXPLAIN的结果中Extra字段显示为Using index，则表示触发了索引覆盖
    • 利用索引下推技术，尽早使用索引进行数据筛选，减少不必要的数据读取。
      • 索引下推是MySQL 5.6引入的一个特性，它允许在查询执行过程中，将过滤条件下推到存储引擎层，减少“回表查询”的次数。这意味着，即使某些记录不满足查询条件，也可以在索引层被排除，而不是在所有记录都被读取之后。例如： 
        • 假设有一个包含name、age和sex字段的表。
SQL查询使用了这三个字段作为过滤条件。
SELECT * FROM tuser WHERE name = 'zou' AND age = 10 AND sex = 1;
这个查询可以通过索引下推来优化，因为即使某些记录不满足所有过滤条件，也可以在索引层被排除。

           

 

posted @ 2024-05-07 11:12  楚景然  阅读(38)  评论(0)  编辑  收藏  举报