长春网站建设net_天元建设集团有限公司汇票信誉_长沙网站优化效果_友情链接交换

MySql INDEX  索引语法

增：

CREATE INDEX index_name ON table_name(column_name);
CREATE INDEX index_name ON table_name(column1, column2, ...);
CREATE UNIQUE INDEX index_name ON table_name(column_name);
CREATE FULLTEXT INDEX index_name ON table_name(column_name);   CHAR,VARCHAR,TEXT

ALTER TABLE table_name ADD INDEX index_name (column_name);

删除：

DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;

查：

SHOW INDEX FROM table_name;
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';

举个栗子：

DROP TABLE IF EXISTS `tablea`;
CREATE TABLE `tablea`  (`id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,`code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `index_code_name`(`name`, `code`) USING BTREE,INDEX `index_code`(`code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;CREATE INDEX index_code_name ON tablea(name, code);
CREATE INDEX index_code ON tablea(code);COMMIT;EXPLAIN SELECT id, code, name from  tablea  where  code = '1' and name = '2';EXPLAIN SELECT id, code, name from  tablea  where  code = '1';EXPLAIN SELECT id, code, name from  tablea  where  name = '1';EXPLAIN SELECT id, code, name, remark from  tablea  where  name = '2';DROP INDEX index_code_name ON tablea;
DROP INDEX index_name ON tablea;
COMMIT;