`
sangeshitou
  • 浏览: 27571 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

MYSQL INNODB 索引相关

    博客分类：
  • DB
DBINNODBINDEX 
阅读更多

陆陆续续看了一些关于mysql innodb 引擎的文档，但是还是不能回答我自己如何根据查询语言建立索引的问题，所以必须做一些小实验才能解开上述疑惑。

首先，总结下一些文档的内容:

 

  1. innodb用的是b+ tree，PK索引树的叶节点是数据文件也就是record，而辅助索引（也就是非PK字段的索引的叶节点则是指向PK索引树的指针），从而达到加快查询的目的。
  2. 索引并非一张表必然建立的，如果数据量较小的话，建议还是不采取建立索引，等查询速度变慢，再建立索引。

 

本文章的主要目的在于：

mysql innodb如何使用索引，即一条sql语句如何使用索引。

 

测试环境

 

  1. OS：Linux debian 2.6.32-5-amd64 #1 SMP Wed May 18 23:13:22 UTC 2011 x86_64 GNU/Linux 虚拟机
  2. mysqladmin  Ver 8.42 Distrib 5.1.49, for debian-linux-gnu on x86_64

建立基础表结构

建立基础表

 

create table test ( col_pk int  primary key,col_index_1 int,col_index_compound_1 int,col_index_coumpount_2 int)engine=innodb,character set=gbk;
 

查看主键索引

 

 show indexes from test;

 写道

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| test | 0 | PRIMARY | 1 | col_pk | A | 0 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

 

我们如下查询：

 

explain select * from test where col_pk = 1;

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
 

 

从上面可以看出innoDB选择的常量表进行查询，因为表中没有数据所以查询为不可能执行

然后我们插入数据：

 

insert into test values(1,1,1,1);
insert into test values(2,1,1,1);

--再次查询
 explain select * from test where col_pk = 1;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

 

插入数据后我们可以看出，这个查询会使用索引col_pk

 

 

接着我们在test建立索引col_index_1和（col_index_compound_1和col_index_compound_2）的索引

 

create index index_col_index_1 on test(col_index_1);
create index index_col_index_compound_12 on test(col_index_compound_1,col_index_coumpount_2);
mysql> show indexes from test;
+-------+------------+-----------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name                    | Seq_in_index | Column_name           | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-----------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+
| test  |          0 | PRIMARY                     |            1 | col_pk                | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| test  |          1 | index_col_index_1           |            1 | col_index_1           | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |
| test  |          1 | index_col_index_compound_12 |            1 | col_index_compound_1  | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |
| test  |          1 | index_col_index_compound_12 |            2 | col_index_coumpount_2 | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+-----------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)
 

可以从上面看出，我们已经拥有了3个索引，然后进行我们的查询

 

mysql> explain select * from test where col_pk = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from test where col_pk = 1 and col_index_1=1;
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys             | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | const | PRIMARY,index_col_index_1 | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

 从上面两个对比中发现只有有pk作为select的对象则，一定会使用pk索引

 

mysql> explain select * from test where col_index_1=1 order by col_pk;
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | index_col_index_1 | index_col_index_1 | 5       | const |    1 | Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)


mysql> explain select * from test order by col_pk;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | test  | index | NULL          | PRIMARY | 4       | NULL |    2 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

 上述例子则说明where 的过滤条件会优于ordery by

 

 

mysql> explain select * from test where col_pk = 1 or col_index_1=1;
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys             | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | PRIMARY,index_col_index_1 | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from test where col_index_1=1 or col_pk =1;
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys             | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | PRIMARY,index_col_index_1 | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

 上述例子说明两个问题:

 

  1. where条件的前后顺序不影响索引的选择
  2. or关键字会导致扫描所有记录
mysql> explain select * from test where col_index_compound_1=1 and col_pk =1;
+----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys                       | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | const | PRIMARY,index_col_index_compound_12 | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)



mysql> explain select * from test where col_index_1=1 and col_pk =1;
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys             | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | const | PRIMARY,index_col_index_1 | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
 以上例子说明，and的情况下，主键优先
mysql> explain select * from test where col_index_compound_1=2 and col_index_1 =1;
+----+-------------+-------+------+-----------------------------------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys                                 | key               | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------------------------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | index_col_index_1,index_col_index_compound_12 | index_col_index_1 | 5       | const |    1 | Using where |
+----+-------------+-------+------+-----------------------------------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)


mysql> explain select * from test where col_index_compound_1=2 and col_index_1 =1 and col_index_coumpount_2=1;
+----+-------------+-------+------+-----------------------------------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys                                 | key               | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------------------------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | index_col_index_1,index_col_index_compound_12 | index_col_index_1 | 5       | const |    1 | Using where |
+----+-------------+-------+------+-----------------------------------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
 上述例子说明，当联合索引和单索引没有交集的时候，单个索引会优先考虑

建立新索引 index_col_index_1_and_compound_1 create index index_col_index_1_and_compound_1 on test(col_index_1,col_index_compound_1); 

mysql> explain select * from test where col_index_1=1 and col_index_compound_1=1;
+----+-------------+-------+------+--------------------------------------------------------------------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys                                                                  | key               | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+--------------------------------------------------------------------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | index_col_index_1,index_col_index_compound_12,index_col_index_1_and_compound_1 | index_col_index_1 | 5       | const |    1 | Using where |
+----+-------------+-------+------+--------------------------------------------------------------------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)


mysql> explain select * from test where col_index_compound_1=2 and col_index_1 =1;
+----+-------------+-------+------+--------------------------------------------------------------------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys                                                                  | key               | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+--------------------------------------------------------------------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | index_col_index_1,index_col_index_compound_12,index_col_index_1_and_compound_1 | index_col_index_1 | 5       | const |    1 | Using where |
+----+-------------+-------+------+--------------------------------------------------------------------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

  从上述看出，单索引还是会优先选择的

 

总结：

  1. 主键是最优选择
  2. 单索引会优先选择
分享到：
| javascript 简单的单元测试代码
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    MySQL Innodb 索引原理详解

    在深入探讨MySQL Innodb索引之前，我们先了解几种基本的树形数据结构，包括二叉搜索树、B树、B+树以及B*树。 ##### 1.1 搜索二叉树（Binary Search Tree） 搜索二叉树是一种特殊的二叉树，每个节点至多有两个子...

    关于InnoDB的索引大小

    在MySQL数据库系统中，InnoDB存储引擎是默认的...总之，理解和优化InnoDB索引大小是提高MySQL数据库性能的关键步骤之一。通过合理设计索引，不仅可以节省存储空间，还能显著提升查询效率，从而优化整体系统的运行效能。

    辛星笔记之InnoDB索引

    ### 辛星笔记之InnoDB索引 #### 第一节：算法基础 ##### 二分查找法 二分查找法(Binary Search)，又称折半查找法，是一种高效的查找算法，适用于有序数组。它的工作原理是首先将目标值与数组中间位置的元素进行...

    mysql，innodb索引介绍

    MySQL数据库的InnoDB存储引擎广泛使用BTree索引来优化查询性能。BTree索引是一种常见的索引类型，尤其适用于范围查询和排序操作。在MySQL中，InnoDB存储引擎的索引分为聚簇索引（主键索引）和非聚簇索引（二级索引）...

    Mysql InnoDB引擎的索引与存储结构详解

    MySQL数据库中，InnoDB存储引擎的索引与存储结构是数据库性能优化的关键，了解这些机制对于数据库管理员和开发人员至关重要。InnoDB是MySQL中支持事务处理的存储引擎，它具有ACID（原子性、一致性、隔离性、持久性）...

    MySQL体系结构及原理(innodb)图文完美解析

    ### MySQL体系结构及原理(innodb)图文完美解析 #### 宏观认识 在深入探讨MySQL的体系结构及其核心组件InnoDB之前，我们先来理解几个基础概念。 1. **MySQL简介** MySQL是一种开源的关系型数据库管理系统(RDBMS)...

    MySQL InnoDB 查询优化实现分析

    ### MySQL InnoDB 查询优化实现分析 #### 一、目的与背景 本文旨在深入探讨 MySQL + InnoDB 存储引擎在实现查询优化时所采取的方法及其内部机制。通过具体实例和详细的技术解析，揭示 InnoDB 如何高效处理各种查询...

    MySQL技术内幕 InnoDB存储引擎.pdf

    最近在学习MySQL技术内幕 InnoDB存储引擎 第2版，整理了一些文档分享出来，同时也方便以后查看。若有不当之处，烦请批评指正。 1. MySQL体系结构和存储引擎 2. InnoDB存储引擎 2.1 InnoDB体系结构 2.2 ...

    Mysql 的InnoDB引擎相关读书笔记

    Mysql 的InnoDB引擎的相关笔记 1.0.MySQL架构到innoDB架构.md 1.1.0.InnoDB——简介.md 1.1.1.InnoDB——关键特性.md 1.2.0.InnoDB内存结构——缓冲池.md 1.2.1.InnoDB内存结构——log buffer.md 1.2.2.InnoDB内存...

    MySQL内核：InnoDB存储引擎 卷1.pdf.zip

    深入学习《MySQL内核：InnoDB存储引擎 卷1》，读者可以了解到InnoDB的内部工作机制，如如何处理B+树索引、事务的提交与回滚、锁的实现以及内存管理等内容，这对于优化数据库性能、解决并发问题、设计高效的数据模型...

    MySql Innodb 引擎特性详解

    ### MySQL Innodb 引擎特性详解 #### 一、MySQL Innodb 引擎概述 MySQL是一种广泛使用的开源关系型数据库管理系统(RDBMS)，它...同时，通过对InnoDB的相关参数进行合理调整，可以在很大程度上提高系统的整体性能。

    MySQL 和 InnoDB 性能

    ### MySQL与InnoDB性能分析 #### MySQL架构概览 MySQL是一种关系型数据库管理系统，由瑞典MySQL AB公司开发，目前由Oracle公司维护。MySQL的核心组成部分包括服务器端、存储引擎以及一系列支持服务。 - **服务器...

    MySQL内核：InnoDB存储引擎 卷1.pdf

    《MySQL内核：InnoDB存储引擎 卷1》由资深MySQL专家，机工畅销图书作者亲自执笔，在以往出版的两本InnoDB介绍性图书的基础之上，更深入地介绍InnoDB存储引擎的内核，例如latch、B+树索引、事务、锁等，从源代码的...

    MySQL索引 聚集索引

    MySQL索引 聚集索引 如果你想了解MySQL索引查询优化，你首先应该对MySQL数据组织结构、B-Tree索引、聚集索引，次要索引有一定的了解，才能够更好地理解MySQL查询优化行为。这里主要探讨MySQL InnoDB的聚集索引。

    MySQL InnoDB 二级索引的排序示例详解

    MySQL InnoDB 存储引擎的二级索引是其特性之一，用于提高数据查询效率。在InnoDB表中，除了主键索引（Primary Key），还可以创建一个或多个辅助索引（Secondary Indexes）。这些索引的叶子节点并不直接存储行数据，...

Magicbox
Global site tag (gtag.js) - Google Analytics