`

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

mysql创建索引mysql索引优化mysql索引创建删除mysql exists蕃薯耀 
阅读更多

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

 

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

©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;

 

 

 

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

©Copyright 蕃薯耀 2020-11-23

http://fanshuyao.iteye.com/

1
顶
1
踩
分享到：
| Linux以rpm方式安装Mysql，Centos7通过 ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    MySQL创建索引，查看以及删除

    创建索引虽然能提升查询速度，但也会占用额外的存储空间，并可能降低插入、更新和删除操作的性能。因此，合理设计和使用索引至关重要。以下是一些优化策略： 1. 选择合适的索引类型：根据查询需求选择适当的索引...

    MYSQL创建索引全过程

    代码复制粘贴 改下里面的参数 就可以用 而且参数 也有说明 简单易懂

    mysql中创建各种索引的语句整理.pdf

    Mysql中创建各种索引的语句整理 代码 添加PRIMARY KEY（主键索引) 添加UNIQUE(唯一索引) 添加INDEX(普通索引) 添加FULLTEXT(全文索引) 添加多列索引 ） mysql>ALTER TABLE `...

    mysql的索引优化

    ### MySQL的索引优化 ...通过上述内容可以看出，合理使用和优化MySQL索引对于提升数据库性能至关重要。开发者应该根据具体的应用场景来选择合适的索引策略，并定期评估和调整索引设置，以确保系统的高效运行。

    MySQL数据库：创建索引.pptx

    创建索引 数据索引 课程目标 理解 —— 创建索引的语法； 掌握 —— 在已有表上创建索引的方法； 掌握 —— 在修改表时添加索引的方法； 掌握 —— 在创建表时创建索引的方法。 创建索引 使用CREATE INDEX语句创建...

    MySQL索引优化课件

    MySQL索引优化是数据库性能提升的关键技术之一，尤其在处理大量数据时，高效索引能够显著加快查询速度，降低服务器负载。本课件主要聚焦于MySQL数据库的索引原理、优化策略以及相关存储过程和触发器的应用。 首先，...

    Mysql的索引及优化策略

    Mysql的索引及优化策略，个人感觉还不错

    04-VIP-Mysql索引优化实战一.pdf

    MySQL索引优化是数据库性能提升的关键环节，本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先，创建了一个名为`employees`的员工记录表，其中包含`id`（主键）、`name`、`age`、`position`和`hire_...

    MySQL索引分析和优化.pdf

    ### MySQL索引分析和优化 #### 一、索引的重要性及原理 索引在数据库管理中扮演着极其重要的角色，特别是在提高数据检索速度方面。**MySQL索引**本质上是用来加快数据检索过程的一种数据结构，类似于书籍中的目录...

    尚硅谷mysql高级：索引、优化

    一、MySQL索引 1. 索引类型：MySQL支持多种索引类型，包括B-Tree（默认索引类型）、Hash、Full-text（全文索引）以及R-tree（空间数据索引）。B-Tree索引适用于范围查询，而Hash索引适用于等值查询，Full-text索引...

    MySQL数据库：使用NAVICAT工具创建和管理索引.pptx

    在MySQL中，索引的管理和创建是数据库性能优化的重要环节。NAVICAT是一款强大的数据库管理工具，支持多种数据库系统，包括MySQL，提供了一个直观的图形用户界面，使得数据库操作变得更为简单。 **创建索引** 1. **...

    MySQL 创建索引(Create Index)的方法和语法结构及例子

    CREATE INDEX Syntax CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON ... HASH | RTREE} 代码如下: — 创建无索引的表格 create table testNoPK ( id int not null, name varchar(10) ); — 创建

    MySQL查询优化技术_索引.pdf

    MySQL查询优化技术_索引

    mysql查询优化之索引优化

    MySQL查询优化是数据库管理中的关键环节，特别是在大数据量的场景下，索引优化能显著提升查询性能。本文将深入探讨“mysql查询优化之索引优化”这一主题。 首先，了解索引的基本概念至关重要。索引是数据库为了快速...

    mysql添加索引.pdf

    总结来说，正确地添加和管理MySQL索引是提高数据库性能的关键，但同时也需要注意平衡索引带来的好处和潜在的问题。理解索引的工作原理、选择合适的索引类型以及定期维护和调整，是每个数据库管理员和开发人员必备的...

    Mysql数据库索引创建、索引删除、索引失效场景详解

    除了创建索引，索引管理还包括删除。在MySQL中，可以使用`DROP INDEX`语句来删除索引。但是，需要注意的是，如果索引与主键关联，必须先删除主键，再删除主键索引。 索引的失效场景包括：更新或删除索引列，使用不...

    MySQL索引 聚集索引

    MySQL索引 聚集索引 如果你想了解MySQL索引查询优化，你首先应该对MySQL数据组织结构、B-Tree索引、聚集索引，次要索引有一定的了解，才能够更好地理解MySQL查询优化行为。这里主要探讨MySQL InnoDB的聚集索引。

    mysql索引优化分享

    关于mysql索引一些优化介绍与创建原则，还有对order by排序的算法的介绍等等

    mysql之Linux安装，重点是索引的优化笔记

    ### MySQL之Linux安装与索引优化笔记 #### 一、MySQL简介及Linux版安装 **1. MySQL概述** MySQL是一种关系型数据库管理系统(RDBMS)，由瑞典MySQL AB公司开发，目前属于Oracle公司。它是一种开源软件，因其性能...

Magicbox
Global site tag (gtag.js) - Google Analytics