MySQl-索引

Neo丶

已于 2023-03-20 19:48:12 修改

阅读量779

点赞数 1

文章标签： java mysql 学习

于 2022-10-03 13:26:36 首次发布

本文链接：https://blog.csdn.net/Ego_Ekko/article/details/127138007

版权

简介

在提高数据库性能的方法中，通过索引来提高查询速度是非常便捷、高效的手段。

测试代码

DROP DATABASE tmp;
-- 创建数据库
CREATE DATABASE tmp;
-- 部门表
CREATE TABLE dept(
		deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
		dname VARCHAR(20) NOT NULL DEFAULT ' ',
		loc VARCHAR(13) NOT NULL DEFAULT ' ');
		
-- 雇员表
CREATE TABLE emp(
		empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
		ename VARCHAR(32) NOT NULL DEFAULT ' ',
		job VARCHAR(9) NOT NULL DEFAULT ' ',
		mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
		hiredate DATE NOT NULL,
		sal DECIMAL(7,2) NOT NULL,
		comm DECIMAL(7,2) NOT NULL,
		deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0);

-- 工资级别表
CREATE TABLE salgrade(
		grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
		losal DECIMAL(17,2) NOT NULL,
		hisal DECIMAL(17,2) NOT NULL);
	
-- 测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

DELIMITER $$

-- 创建函数，随即返回指定个数的字符串
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255)		-- 返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT ' ';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
	-- count函数：连接函数mysql函数
	SET return_str = CONCAT (return_str, SUBSTRING(chars_str, FLOOR(1 + RAND()*52), 1));
	SET i = i + 1;
	END WHILE;
RETURN return_str;
END $$

-- 定义函数，返回一个随机的部门号
CREATE FUNCTION rand_num()
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR (10 + RAND() *500);
RETURN i;
END $$

-- 创建一个存储过程，可以添加雇员
CREATE PROCEDURE insert_emp (IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;	-- 默认不提交sql语句
REPEAT
SET i = i + 1;
-- 通过函数随机产生字符串和部门编号，然后加入到emp表中
INSERT INTO emp VALUES ((START + i ), rand_string(6), 'SALESMAN', 0001, CURDATE(), 2000, 400, rand_num());
UNTIL i = max_num
END REPEAT;
-- commit整体提交给所有sql语句，提高效率
	COMMIT;
END $$

-- 添加 8000000数据
CALL insert_emp (100001,8000000) $$

-- 命令结束符，再重新设置为;
DELIMITER;

-- 没有创建索引时，我们查询一条记录
SELECT * 
		FROM emp
		WHERE empno = 1234567;
		
-- 使用索引进行优化
-- 在创建索引前，emp.ibd 文件大小536576kb
-- 在创建索引后，emp.ibd 文件大小667648kb【索引本身也会占用存储空间】
CREATE INDEX  empno_index ON emp(empno);

SELECT * 
		FROM emp
		WHERE empno = 1234568;
-- 创建索引后，查询时只对创建索引的的列有效

索引机制

未建立索引时，查询操作是对全表目标所在的列数据进行比较查询(一查到底)；建立索引后首先会将目标所在列形成一个索引的数据结构，然后进行比较查询。如果对表进行update、delete、insert操作，会对索引进行维护，对速度有影响。

索引代价

1）磁盘占用；2）对dml（update、delete、insert）语句的效率影响。

索引类型

1）主键索引，主键自动的为主索引（类型primary key）；

CREATE TABLE t1(
        id INT PRIMARY KEY,    -- 主键同时也是索引，称为主键索引
        `name` VARCHAR(32));

2）唯一索引（UNIQUE）；

CREATE TABLE t2(
        id INT UNIWUE,    -- id是唯一的同时也是索引，称为UNIQUE索引
        `name` VARCHAR(32));

3）普通索引（INDEX）；

4）全文索引（FULLTEXT）【适用于MyISAM】；

开发中考虑使用：全文搜索Solr 和 ElasticSearch（ES）,一般不使用mysql自带的全文索引。

索引使用

1）添加索引

CREATE [UNIQUE] INDEX index_name ON table_name (col_name [(length)] [ASC|DESC], ...);

-- 或者

ALTER TABLE table_name ADD INDEX [index_name] (index_col_name,...)

# 创建索引
CREATE TABLE t8(
		id INT,
		`name` VARCHAR(32));

-- 查询表是否有索引
SHOW INDEXES FROM t8;

-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t8 (id);

-- 添加普通索引
CREATE INDEX name_index ON t8 (`name`);
-- 或者
ALTER TABLE t8 ADD INDEX name_index (`name`);

-- 如果列的值是不会重复的则优先考虑使用unique索引，否则使用普通索引

2）添加主键索引

ALTER TABLE table_name ADD PRIMARY KEY(目标列名, ...);

-- 添加主键索引
-- 1.在创建列时添加 PRIMARY KEY；
-- 2.创建表后添加主键索引
ALTER TABLE t8 ADD PRIMARY KEY (`name`);

3）删除索引

DROP INDEX index_name ON table_name;

-- 或者

ALTER TABLE table_name drop index index_name;

-- 查询索引
SHOW INDEXES FROM t8;

-- 删除索引
DROP INDEX name_index ON t8;
-- 或者
ALTER TABLE t8 DROP INDEX id_index;

4）删除主键索引

ALTER TABLE table_name DROP PRIMARY KEY;

-- 删除主键索引（不需要指定列，因为主键索引时指定的）
ALTER TABLE t8 DROP PRIMARY KEY;

-- 查询索引
SHOW INDEXES FROM t8;

5)修改索引（把之前添加的索引删除后，再添加新的索引）

-- 创建新表
CREATE TABLE index_test(
	id INT,
	`name` VARCHAR(32),
	age INT);

CREATE INDEX id_index ON index_test(id);
SHOW INDEXES FROM index_test;

DROP INDEX id_index ON index_test;
SHOW INDEXES FROM index_test;

CREATE INDEX newID_index ON index_test(id);
SHOW INDEXES FROM index_test;

6）查询索引

-- 查询索引
-- 方式一：
SHOW INDEX FROM index_test;
-- 方式二：
SHOW INDEXES FROM index_test;
-- 方式三：
SHOW KEYS FROM index_test;
-- 方式四：
DESC index_test;

联系代码

-- 创建一张订单表order（id号，商品名，订购人，数量）。要求id号为主键，请使用3种
-- 方式来创建主键
CREATE TABLE `order1`(
		id INT PRIMARY KEY,
		`nama` VARCHAR(32),
		people VARCHAR(32),
		num INT );

CREATE TABLE `order2`(
		id INT,
		`nama` VARCHAR(32),
		people VARCHAR(32),
		num INT, 
		PRIMARY KEY (id));
		
CREATE TABLE `order3`(
		id INT,
		`nama` VARCHAR(32),
		people VARCHAR(32),
		num INT);
ALTER TABLE `order` ADD PRIMARY KEY(id);

-- 创建一张特价菜谱表munu(id号，菜谱名，厨师，点餐人身份证号，价格)要求id号为主键，
-- 点餐人身份证号唯一（unique），使用两种方式来创建unique
CREATE TABLE menu1(
		id INT PRIMARY KEY,
		`caipu` VARCHAR(32),
		`chushi` VARCHAR(32),
		card_id VARCHAR(18) UNIQUE,
		price DOUBLE);
		
CREATE TABLE menu2(
		id INT PRIMARY KEY,
		`caipu` VARCHAR(32),
		`chushi` VARCHAR(32),
		card_id VARCHAR(18),
		price DOUBLE);
CREATE UNIQUE INDEX index_card_id ON menu2(card_id);

-- 创建一张运动员表sportman（id号，名字，特长）要求id号为主键，名字为普通索引，
-- 使用2种方式创建索引
CREATE TABLE sportman1(
		id INT PRIMARY KEY,
		`name` VARCHAR(32),
		`techang` VARCHAR(32));
CREATE INDEX name_index ON sportman1(`name`);

CREATE TABLE sportman2(
		id INT PRIMARY KEY,
		`name` VARCHAR(32),
		`techang` VARCHAR(32));
ALTER TABLE sportman2 ADD INDEX name_index (`name`);