简介
在提高数据库性能的方法中，通过索引来提高查询速度是非常便捷、高效的手段。
测试代码
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`);索引的使用规则
1）比较频繁的作为查询条件的字段应该创建索引；
2）唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
3）更新非常频繁的字段不适合创建索引；
4）不会出现在where子句中的字段不该创建索引。
扫一扫
5455
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
