MySQL笔记： 索引

-- 查询薪水记录总数
SELECT COUNT(*) FROM salaries;

-- 查询员工薪水记录按emp_no排序，从低到高,前10个,查看查询时间 0.00 sec
SELECT * FROM salaries ORDER BY emp_no DESC LIMIT 10; -- 查询前10个
SELECT * FROM salaries ORDER BY emp_no LIMIT 0,10; -- 从第一条数据开始,10个
 
-- 查询员工薪水记录按薪水排序,高到底,前10个,查看查询时间 01:965 sec  -> 0.063
SELECT * FROM salaries ORDER BY salary DESC LIMIT 10;
SELECT * FROM salaries ORDER BY salary DESC LIMIT 10,100;

-- 目的是为了加速薪水相关查询，为salaries 的salary加上索引
-- 方式1
ALTER TABLE salaries ADD INDEX index_sa(salary); -- 用了大约18s时间建立索引，索引表也会占用磁盘空间
-- 修改    表格 salaries表 添加 索引  索引名字(salary字段)
-- 方式2
CREATE INDEX index_sa ON salaries(salary);
-- 创建 索引 索引的名字 在 salaries表上的salary字段上

-- (0 row(s) affected) 
-- Execution Time : 00:00:17:956
-- Transfer Time  : 00:00:01:092
-- Total Time     : 00:00:19:048

-- 删除索引
-- 方式1:
ALTER TABLE salaries DROP INDEX index_sa;
-- 方式2:
DROP INDEX index_sa ON salaries;

-- 索引的优点:
-- 1.加快查询速度

-- 缺点:
-- 1. 维护索引需要耗费数据库资源(时间和空间)
-- 2. 对数据进行增删改的时候，因为要维护索引，速度也会影响。(修改salary字段，索引表也会跟着改)

-- 分析查询语句
EXPLAIN SELECT * FROM salaries ORDER BY emp_no DESC LIMIT 10;

EXPLAIN SELECT * FROM salaries ORDER BY salary DESC LIMIT 10; \G

-- 查看表格
SHOW CREATE TABLE salries;


-- 创建表时就添加索引
-- 1. 普通索引
CREATE TABLE index1 (
    id INT,
    NAME VARCHAR(20),
    sex BOOLEAN,
    INDEX(id)
    );
SHOW CREATE TABLE index1;

-- 对比:
CREATE TABLE index1_(
    id INT PRIMARY KEY,
    NAME VARCHAR(20),
    sex BOOLEAN
    );
SHOW CREATE TABLE index1_;

-- 2. 唯一索引 等同unique
DROP TABLE index2;
CREATE TABLE index2(
    id INT,
    NAME VARCHAR(20),
    sex BOOLEAN,
    UNIQUE INDEX index2_id(id ASC)
    );
SHOW CREATE TABLE index2;

-- 对比
CREATE TABLE index2_(
    id INT UNIQUE,
    NAME VARCHAR(20),
    sex BOOLEAN
    );
SHOW CREATE TABLE index2_;

-- 3. 全文索引 需要MyISAM
CREATE TABLE index3(
    id INT,
    info VARCHAR(20),
    FULLTEXT INDEX index3_info(info)
) ENGINE = MYISAM;

-- 4. 单列索引
CREATE TABLE index4(
    id INT,
    fullname VARCHAR(30),
    INDEX index4_fn(fullname(10))
    );
SHOW CREATE TABLE index4;

-- 5. 双列索引
CREATE TABLE index5(
    id INT,
    fullname VARCHAR(30),
    sex CHAR(4),
    INDEX index5_fs(fullname,sex)
    );
SHOW CREATE TABLE index5;

-- 6. 空间索引
CREATE TABLE index6(
    id INT,
    SPACE GEOMETRY NOT NULL,
    SPATIAL INDEX index6_sp(SPACE)
    ) ENGINE = MYISAM;
SHOW CREATE TABLE index6;    


-- 练习:
-- 给employees的名字加索引,比较查询速度
SELECT * FROM employees WHERE first_name = 'Georgi';