mysql innodb 主键 索引_MySQL Index--InnoDB引擎的主键索引

查看表主键信息

## 查看表主键信息SELECTt.TABLE_NAME,

t.CONSTRAINT_TYPE,

c.COLUMN_NAME,

c.ORDINAL_POSITIONFROMINFORMATION_SCHEMA.TABLE_CONSTRAINTSASt,

INFORMATION_SCHEMA.KEY_COLUMN_USAGEAScWHEREt.TABLE_NAME=c.TABLE_NAMEAND t.CONSTRAINT_TYPE = 'PRIMARY KEY'

AND t.TABLE_NAME=''

AND t.TABLE_SCHEMA='';

查看无主键表

## 查看无主键表SELECTtable_schema, table_name,TABLE_ROWSFROMinformation_schema.tablesWHERE (table_schema, table_name) NOT IN(SELECT DISTINCTtable_schema, table_nameFROMinformation_schema.columnsWHERE COLUMN_KEY = 'PRI')AND table_schema NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema');

无主键表

在Innodb存储引擎中，每张表都会有主键，数据按照主键顺序组织存放，该类表成为索引组织表 Index Ogranized Table

如果表定义时没有显示定义主键，则会按照以下方式选择或创建主键：

a) 先判断表中是否有"非空的唯一索引"，如果有1) 如果仅有一条"非空唯一索引"，则该索引为主键2) 如果有多条"非空唯一索引"，根据索引索引的先后顺序，选择第一个定义的非空唯一索引为主键。

b) 如果表中无"非空唯一索引"，则自动创建一个6字节大小的指针作为主键。

如果主键索引只有一个索引键，那么可以使用_rowid来显示主键，如：

## 删除测试表

DROP TABLE IF EXISTS tb2001;

## 创建测试表

CREATE TABLE `tb2001` (

`id`int(11) NOT NULL,

`c1`int(11) DEFAULT NULL,

UNIQUE uni_id (id),

INDEX idx_c1(c1)

) ENGINE= InnoDB CHARSET =utf8;

## 插入测试数据

INSERT INTO tb2001 (id, c1)

SELECT1, 1;

INSERT INTO tb2001 (id, c1)

SELECT2, 2;

INSERT INTO tb2001 (id, c1)

SELECT4, 4;

## 查看数据和_rowid

SELECT*, _rowid

FROM tb2001;+----+------+--------+

| id | c1 | _rowid |

+----+------+--------+

| 1 | 1 | 1 |

| 2 | 2 | 2 |

| 4 | 4 | 4 |

+----+------+--------+

可以发现，上面的_rowid与id的值相同，因为id列是表中第一个唯一且NOT NULL的索引。

强制主键索引

由于主键索引只能为PRIMARY：

SHOW INDEXES IN tb2001 \G*************************** 1. row ***************************Table: tb2001

Non_unique:0Key_name: PRIMARY

Seq_in_index:1Column_name: id

Collation: A

Cardinality:2Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

因此在强制走主键索引FORCE INDEX(PRIMARY)时，使用：

SELECT *FROM tb2001 FORCE INDEX(PRIMARY)

WHERE C1=2;

非聚集索引中的聚集索引键

在MySQL 5.6.9版本前，Innodb的非聚集索引中包含聚集索引的索引键，但只起到通过非聚集索引定位记录的作用，但在MySQL 5.6.9之后版本中，优化器会考虑非聚集索引中包含的聚集索引键来提升查询性能，并提供优化器选项use_index_extensions来开启或关闭该特性。

假设有表TB1(ID,C1,C2), ID为主键聚集索引，然后在列C1建立索引IDX_C1(C1)：

在MySQL 5.6版本前，索引类似于IDX_C1(C1) INCLUDE(ID)；

在MySQL 5.6版本中，索引类似于IDX_C1(C1,ID);

无论是MySQL 5.5还是MySQL 5.6版本中，非聚集索引上的数据都是先按照非聚集索引键在按照聚集索引键进行排序，即在非聚集索引键上值相同的记录会按照聚集索引进行排序。

对于查询:

SELECT *FROM TB1

WHERE C1='ABC'AND ID>10AND ID<1000

在MySQL 5.5版本中，需要按照索引IDX_C1扫描所有C1='ABC'的记录，再根据ID进行过滤。

在MySQL 5.6版本中，进行按照索引IDX_C1就可以先定位C1='ABC' AND ID>10的第一条记录，再顺序扫描至C1='ABC' AND ID<1000的记录，有效减少扫描的数据量。

对于查询:

SELECT *FROM TB1

WHERE C1='ABC'ORDER BY ID

LIMIT1

如果满足C1='ABC'的记录数较多，那么查询在MySQL 5.5版本就会性能极差，而在MySQL 5.6版本中变得性能极好，MySQL 5.5版本可以将IDX_C1(C1)优化为 IDX(C1,ID)。

在MySQL 5.5.14版本进行测试，准备测试数据：

## 删除测试表

DROP TABLE IF EXISTS tb2001;

## 创建测试表

CREATE TABLE `tb2001` (

`id`int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`c1`int(11) DEFAULT NULL,

`c2`int(11) DEFAULT NULL,

INDEX idx_c1(c1),

INDEX idx_c2(c2,id)

) ENGINE= InnoDB CHARSET =utf8;

## 插入测试数据(10万左右)

## 如果information_schema.columns数据较少，可以重复多次

INSERT INTO tb2001 (c1,c2)

SELECT1,1 from information_schema.columns;

测试1：

## 测试SQL 1SELECT c1,id

FROM tb2001 FORCE INDEX(idx_c1)

WHERE C1=1 and id<2;

## 执行时间超过20MS

## 对应执行计划为：*************************** 1. row ***************************id:1select_type: SIMPLE

table: tb2001

type:refpossible_keys: idx_c1

key: idx_c1

key_len:5

ref: constrows:44196Extra: Usingwhere; Using index

## 对应PROFILING数据为：+----------------------+----------+----------+------------+--------------+---------------+-------+

| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |

+----------------------+----------+----------+------------+--------------+---------------+-------+

| starting | 0.000039 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| Opening tables | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| System lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| init | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| optimizing | 0.000009 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| statistics | 0.000041 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| preparing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| executing | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| Sending data | 0.020529 | 0.019997 | 0.000000 | 0 | 0 | 0 |

| end | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| closing tables | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| freeing items | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| logging slow query | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| cleaning up | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | 0 |

+----------------------+----------+----------+------------+--------------+---------------+-------+

测试2：

## 测试SQL:

SELECT c2,id

FROM tb2001 FORCE INDEX(idx_c2)

WHERE C2=1 and id<2;

##执行时间2ms

## 对应执行计划为：*************************** 1. row ***************************id:1select_type: SIMPLE

table: tb2001

type: range

possible_keys: idx_c2

key: idx_c2

key_len:9

ref: NULL

rows:1Extra: Usingwhere; Using index

## 对应PROFILING数据为:+----------------------+----------+----------+------------+--------------+---------------+-------+

| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |

+----------------------+----------+----------+------------+--------------+---------------+-------+

| starting | 0.000042 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| Opening tables | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| System lock | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| init | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| optimizing | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| statistics | 0.000033 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| preparing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| executing | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| query end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| closing tables | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| freeing items | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| logging slow query | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| cleaning up | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | 0 |

+----------------------+----------+----------+------------+--------------+---------------+-------+