mysql创建索引，mysql索引优化，mysql索引创建删除

电玩女神 2023-09-24 10:04 245阅读 0赞

================================

?Copyright 蕃薯耀 2020-11-23

http://fanshuyao.iteye.com/

一、mysql创建索引

ALTER TABLE 表名 ADD \[UNIQUE | FULLTEXT | SPATIAL\] INDEX | KEY \[索引名\] (字段名1 \[(长度)\] \[ASC | DESC\]) \[USING 索引方法\]；

-- 或

CREATE \[UNIQUE | FULLTEXT | SPATIAL\] INDEX 索引名 ON 表名(字段名) \[USING 索引方法\]；

二、mysql查看索引

show index from 表名;

三、mysql删除索引

DROP INDEX 索引名 ON 表名

-- 或

ALTER TABLE 表名 DROP INDEX 索引名

四、索引优化

1、学生表

CREATE TABLE student(

id INT PRIMARY KEY AUTO\_INCREMENT,

stu\_no VARCHAR(30) NOT NULL,

\`name\` VARCHAR(30),

age INT ,

mobile VARCHAR(11),

\`status\` VARCHAR(1) DEFAULT '1' NOT NULL ,

remark VARCHAR(100)

);

SELECT COUNT(\*) FROM student;

SELECT \* FROM student;

索引

EXPLAIN SELECT \* FROM student l WHERE l.\`name\`='学生11';

EXPLAIN SELECT \* FROM student l WHERE l.\`stu\_no\`='1605782472487';

EXPLAIN SELECT \* FROM student l WHERE l.\`stu\_no\`='1605782472487' AND l.\`status\`='1';

EXPLAIN SELECT \* FROM student l WHERE l.\`name\`='学生1' AND l.\`stu\_no\`='1605782472487';

EXPLAIN SELECT l.id,l.\`stu\_no\`,l.\`name\` FROM student l WHERE l.\`name\`='学生1';

EXPLAIN SELECT l.id,l.\`stu\_no\`,l.\`name\` FROM student l WHERE l.\`name\`='学生1' ORDER BY l.\`name\`;

-- 使用id排序，出现Using filesort

EXPLAIN SELECT l.id,l.\`stu\_no\`,l.\`name\` FROM student l WHERE l.\`name\`='学生1' ORDER BY l.\`id\`;

EXPLAIN SELECT \* FROM student l WHERE l.\`name\`='学生1' AND l.\`mobile\`='13400000001' AND l.\`age\`='21';

-- 打乱顺序，正常使用索引

EXPLAIN SELECT \* FROM student l WHERE l.\`age\`='21' AND l.\`name\`='学生1' AND l.\`mobile\`='13400000001';

-- 没用到索引

-- type：ALL

EXPLAIN SELECT l.id,l.\`stu\_no\`,l.\`name\` FROM student l WHERE l.\`name\` LIKE '学生1%';

-- 用到索引

-- type：range，注意这里是range，不是index，即非全索引查找，是范围查找

-- Using where; Using index

-- 因为这里查询的字段只有name，name是建了索引的，右边%，索引还是生效，然后直接从索引取数据，而不是检索表，索引比表快

EXPLAIN SELECT l.\`name\` FROM student l WHERE l.\`name\` LIKE '学生1%';

-- 没用到索引

-- type：ALL

EXPLAIN SELECT l.id,l.\`stu\_no\`,l.\`name\` FROM student l WHERE l.\`name\` LIKE '%学生1';

-- 用到索引

-- type：index

-- Using where; Using index

-- 因为这里查询的字段只有name，name是建了索引的，左边%，索引还是生效，然后直接从索引取数据，而不是检索表，索引比表快

EXPLAIN SELECT l.\`name\` FROM student l WHERE l.\`name\` LIKE '%学生1';

-- 没用到索引

-- type：ALL

EXPLAIN SELECT l.id,l.\`stu\_no\`,l.\`name\` FROM student l WHERE l.\`name\` LIKE '%学生%';

-- 用到索引

-- type：index

-- Using where; Using index

-- 因为这里查询的字段只有name，name是建了索引的，左右两边%，索引还是生效，然后直接从索引取数据，而不是检索表，索引比表快

EXPLAIN SELECT l.\`name\`,l.\`mobile\` FROM student l WHERE l.\`name\` LIKE '%学生%';

-- 创建索引

CREATE UNIQUE INDEX ind\_stu\_stuNO ON student(stu\_no);

CREATE INDEX ind\_stu\_name\_mobile ON student(\`name\`,mobile);

-- 删除索引

DROP INDEX ind\_stu\_stuNO ON student;

DROP INDEX ind\_stu\_name\_mobile ON student;

-- 查询表的索引

SHOW INDEX FROM student;

2、课程表

CREATE TABLE course(

id INT PRIMARY KEY AUTO\_INCREMENT,

course\_no VARCHAR(30) UNIQUE NOT NULL ,

\`name\` VARCHAR(30),

\`status\` VARCHAR(1) DEFAULT '1' NOT NULL ,

create\_time TIMESTAMP DEFAULT NOW() NOT NULL,-- 只有TIMESTAMP可以设置时间的默认值

remark VARCHAR(100)

);

INSERT INTO course(course\_no,\`name\`) VALUES('1001', '语文');

INSERT INTO course(course\_no,\`name\`) VALUES('1002', '数学');

INSERT INTO course(course\_no,\`name\`) VALUES('1003', '英语');

INSERT INTO course(course\_no,\`name\`) VALUES('1004', '物理');

INSERT INTO course(course\_no,\`name\`) VALUES('1005', '化学');

INSERT INTO course(course\_no,\`name\`) VALUES('1006', '生物');

INSERT INTO course(course\_no,\`name\`) VALUES('1007', '历史');

INSERT INTO course(course\_no,\`name\`) VALUES('1008', '地理');

INSERT INTO course(course\_no,\`name\`) VALUES('1009', '自然科学');

INSERT INTO course(course\_no,\`name\`) VALUES('1010', '体育');

INSERT INTO course(course\_no,\`name\`) VALUES('1011', '哲学');

INSERT INTO course(course\_no,\`name\`) VALUES('1012', '考古');

INSERT INTO course(course\_no,\`name\`) VALUES('1013', '高等数学');

INSERT INTO course(course\_no,\`name\`) VALUES('1014', '大学英语');

INSERT INTO course(course\_no,\`name\`) VALUES('1015', '艺术');

SELECT \* FROM course;

-- 查询表的索引

SHOW INDEX FROM course;

3、学生课程关系表

CREATE TABLE stu\_course(

id INT PRIMARY KEY AUTO\_INCREMENT,

stu\_id INT NOT NULL ,

course\_id INT NOT NULL ,

create\_time TIMESTAMP DEFAULT NOW() NOT NULL-- 只有TIMESTAMP可以设置时间的默认值

);

SELECT \* FROM stu\_course;

SELECT COUNT(1) FROM stu\_course;

加索引前后对比

-- s ref 1

-- sc ALL 150646

-- c eq\_ref 1

EXPLAIN

SELECT s.\*,c.\`name\` FROM student s

LEFT JOIN stu\_course sc ON sc.\`stu\_id\`=s.\`id\`

LEFT JOIN course c ON c.\`id\`=sc.\`course\_id\`

WHERE s.\`name\`='学生1'

;

-- 创建索引

ALTER TABLE stu\_course ADD INDEX stu\_course\_stuId(stu\_id);

-- 创建索引后

-- s ref 1

-- sc ref 753

-- c eq\_ref 1

EXPLAIN

SELECT s.\*,c.\`name\` FROM student s

LEFT JOIN stu\_course sc ON sc.\`stu\_id\`=s.\`id\`

LEFT JOIN course c ON c.\`id\`=sc.\`course\_id\`

WHERE s.\`name\`='学生1'

;

小表驱动大表

-- 加索引

ALTER TABLE stu\_course ADD INDEX stu\_course\_courseId(course\_id);

-- 小表驱动大表

-- c ALL 15

-- sc ref 753

EXPLAIN

SELECT c.\*,sc.\`stu\_id\` FROM course c

LEFT JOIN stu\_course sc ON sc.\`course\_id\`=c.\`id\`

;

-- 大表驱动小表

-- sc ALL 150646

-- c eq\_ref 1

EXPLAIN

SELECT c.\*,sc.\`stu\_id\` FROM course c

RIGHT JOIN stu\_course sc ON sc.\`course\_id\`=c.\`id\`

;

-- 总结：尽量使用小表驱动大表

in和EXISTS

-- in和EXISTS

-- EXISTS查询语法

SELECT select\_list FROM a\_table WHERE \[NOT\] EXISTS(subquery);

-- in查询

-- 1 sc ALL 150646

-- 2 c unique\_subquery 1

EXPLAIN

SELECT \* FROM stu\_course sc WHERE sc.\`course\_id\` IN (SELECT id FROM course c);

-- sc ALL 150646

-- c unique\_subquery 1

EXPLAIN

SELECT \* FROM stu\_course sc WHERE sc.\`course\_id\` IN (SELECT id FROM course c WHERE c.\`name\`='数学');

-- EXISTS查询

-- 1 sc ALL 150646 Using where

-- 2 c eq\_ref 1 Using index

EXPLAIN

SELECT \* FROM stu\_course sc WHERE EXISTS (SELECT 1 FROM course c WHERE c.\`id\`=sc.\`course\_id\`);

-- sc ALL 150646 Using where

-- c eq\_ref 1 Using where

EXPLAIN

SELECT \* FROM stu\_course sc WHERE EXISTS (SELECT 1 FROM course c WHERE c.\`name\`='数学' AND c.\`id\`=sc.\`course\_id\`);

-- in和EXISTS总结：

-- EXISTS：可以理解为：将外查询(左边)表的每一行，代入内查询(右边)作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。

-- 1、通常情况下采用exists要比in效率高，因为IN不走索引。

-- 2、左边为小表时(in是大表)，用in性能好。左边为大表时(EXISTS是小表)，用EXISTS性能好。

4、字典表

-- 字典表

-- drop table dict;

CREATE TABLE dict(

id INT PRIMARY KEY AUTO\_INCREMENT,

\`type\` VARCHAR(30) NOT NULL,

\`name\` VARCHAR(30),

\`value\` VARCHAR(50),

create\_time TIMESTAMP DEFAULT NOW() NOT NULL-- 只有TIMESTAMP可以设置时间的默认值

);

INSERT INTO dict(\`type\`,\`name\`,\`value\`) VALUES('status','有效', '1');

INSERT INTO dict(\`type\`,\`name\`,\`value\`) VALUES('status','无效', '0');

SELECT \* FROM dict;

================================

http://fanshuyao.iteye.com/

mysql创建索引，mysql索引优化，mysql索引创建删除

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Mysql创建索引和删除索引语句

相关 mysql创建索引和删除索引

相关 mysql创建索引，mysql索引优化，mysql索引创建删除

相关 MySQL创建索引和删除索引

相关 mysql 创建索引命令_mysql 索引-【创建索引命令】

相关 mysql创建索引

相关 MySQL数据库索引：删除、查看、创建索引

相关 mysql 创建索引和删除索引

相关 MySQL创建索引、重建索引、查询索引、删除索引

相关 mysql 添加索引创建索引