MYSQL建立索引个数测试

mysql测试版本：5.7
1，测试单个索引建立个数

DROP TABLE IF EXISTS CYH_INDEX_TEST;
CREATE TABLE CYH_INDEX_TEST (
  A1 CHAR(1) NOT NULL,
  A2 CHAR(1) NOT NULL,
  A3 CHAR(1) NOT NULL,
  A4 CHAR(1) NOT NULL,
  A5 CHAR(1) NOT NULL,
  A6 CHAR(1) NOT NULL,
  A7 CHAR(1) NOT NULL,
  A8 CHAR(1) NOT NULL,
  A9 CHAR(1) NOT NULL,
  A10 CHAR(1) NOT NULL,
  A11 CHAR(1) NOT NULL,
  A12 CHAR(1) NOT NULL,
  A13 CHAR(1) NOT NULL,
  A14 CHAR(1) NOT NULL,
  A15 CHAR(1) NOT NULL,
  A16 CHAR(1) NOT NULL,
  A17 CHAR(1) NOT NULL,
  A18 CHAR(1) NOT NULL,
  A19 CHAR(1) NOT NULL,
  PRIMARY KEY (A1),
  INDEX `IDX_A2`(`A2`),
  INDEX `IDX_A3`(`A3`),
  INDEX `IDX_A4`(`A4`),
  INDEX `IDX_A5`(`A5`),
  INDEX `IDX_A6`(`A6`),
  INDEX `IDX_A7`(`A7`),
  INDEX `IDX_A8`(`A8`),
  INDEX `IDX_A9`(`A9`),
  INDEX `IDX_10`(`A10`),
  INDEX `IDX_11`(`A11`),
  INDEX `IDX_12`(`A12`),
  INDEX `IDX_13`(`A13`),
  INDEX `IDX_14`(`A14`),
  INDEX `IDX_15`(`A15`),
  INDEX `IDX_16`(`A16`),
  INDEX `IDX_17`(`A17`),
  INDEX `IDX_18`(`A18`)
)

建立17个索引，没有16只能在16列上建立索引的限制，执行结果如下：

受影响的行: 0
时间: 0.049s

2，联合索引列个数限制
脚本

DROP TABLE IF EXISTS CYH_INDEX_TEST;
CREATE TABLE CYH_INDEX_TEST (
  A1 CHAR(1) NOT NULL,
  A2 CHAR(1) NOT NULL,
  A3 CHAR(1) NOT NULL,
  A4 CHAR(1) NOT NULL,
  A5 CHAR(1) NOT NULL,
  A6 CHAR(1) NOT NULL,
  A7 CHAR(1) NOT NULL,
  A8 CHAR(1) NOT NULL,
  A9 CHAR(1) NOT NULL,
  A10 CHAR(1) NOT NULL,
  A11 CHAR(1) NOT NULL,
  A12 CHAR(1) NOT NULL,
  A13 CHAR(1) NOT NULL,
  A14 CHAR(1) NOT NULL,
  A15 CHAR(1) NOT NULL,
  A16 CHAR(1) NOT NULL,
  A17 CHAR(1) NOT NULL,
  A18 CHAR(1) NOT NULL,
  A19 CHAR(1) NOT NULL,
  PRIMARY KEY (A1),
  INDEX `IDX_A`(`A2`,`A3`,`A4`,`A5`,`A6`,
  `A7`,`A8`,`A9`,`A10`,`A11`,`A12`,`A13`,`A14`,`A15`,`A16`,`A17`,`A18`)
)

列个数为17个，执行结果如下：

[Err] 1070 - Too many key parts specified; max 16 parts allowed

总结:mysql联合索引列个数上线为16个，单独在列上建立索引没有16个上限限制