MySQL数据库——SQL优化之索引

一、准备工作——创建person表

-- ----------------------------
-- 创建Person表
-- ----------------------------
DROP TABLE IF EXISTS person;
CREATE TABLE person  (
  PID int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  PNAME varchar(50)  NOT NULL COMMENT '姓名',
  PSEX varchar(10) NULL DEFAULT NULL COMMENT '性别',
	PAGE	int(11)  NOT NULL COMMENT '年龄',
  SAL decimal(7, 2) NULL DEFAULT NULL COMMENT '工资',
  PRIMARY KEY (PID)
) ENGINE = InnoDB AUTO_INCREMENT = 7001 CHARACTER SET = utf8mb4 COMMENT = '人员表';

二、创建索引

语法：
CREATE INDEX 索引名称 on 表名称(列名称);

为Person表的pname建立索引：

CREATE INDEX INDEX_PERSON_PNAME ON PERSON(PNAME);

查看SQL语句是否用了索引

EXPLAIN select * from person where pname= 'Jack';

 

三、在MySQL中为表创建主键的同时默认也创建了一个索引

-- 下面的DQL语句使用了索引。
EXPLAIN
select * from person
where id =1;

四、where pname like 模糊查询用到索引没有

-- 前后模糊中间精确不会使用索引
EXPLAIN
select  * from person where pname like '%e%';
-- 前面模糊后面精确也不会使用索引
EXPLAIN
select  * from person where pname like '%e';
-- 只有前面精确后面模糊才会使用索引，工作中数据量大的表模糊查询尽量不要使用 '%%',也不要使用'%a',他们都不支持索引
EXPLAIN
select  * from person where pname like 'e%';

五、如何创建一个复合索引，例如：我要对年龄和性别两个列创建复合索引

-- 为多个列创建复合索引
CREATE INDEX INDEX_PERSON_AGE_SEX ON PERSON(PAGE,PSEX);
-- 没有创建索执行下面DQL语句引耗时1.427秒，创建索引执行耗时1毫秒
SELECT * FROM PERSON WHERE PAGE =22 AND PSEX='男';
-- 创建的复合索引，但是只对第二个索引列单独进行where条件，不会使用索引
-- 如果创建复合索引，经常使用的列放在前面
EXPLAIN
SELECT * FROM PERSON WHERE  PSEX='男';