MySQL查询优化之 index 索引的分类和使用

迷南。 2021-09-21 11:04 377阅读 0赞

### 索引的分类 ###

*  主键索引 (PRIMARY KEY)
    
     *  唯一的标识符, 主键不可重复, 只能有一列作为主键
 *  唯一索引 (Unique KEY)
    
     *  避免重复的列出现, 唯一索引可以重复, 多个列都可以标识为唯一索引
 *  常规索引 (KEY/INDEX)
    
     *  默认的, index 和 key 关键字可以设置常规索引
 *  全文索引 (FullText)
    
     *  快速定位数据, 在 MyISAM 引擎下才有
     *  只能用于CHAR , VARCHAR , TEXT数据列类型, 适合大型数据集

> 基础语法

/* # 方法一：创建表时 CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) ); */
    
    -- 方法二：CREATE在已存在的表上创建索引
    -- CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ;
    /*常规索引*/
    CREATE INDEX id_app_user_name ON app_user(`name`);
    
    
    -- 方法三：ALTER TABLE在已存在的表上创建索引
    -- ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX
    -- 索引名 (字段名[(长度)] [ASC |DESC]) ;
    /*增加全文索引*/
    ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname`(`StudentName`);
                               
    -- 删除索引：DROP INDEX 索引名 ON 表名字;
    DROP INDEX `id_app_user_name` ON app_user
    
    -- 删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
    
    
    #显示索引信息: SHOW INDEX FROM student;
    
    
    /*EXPLAIN : 分析SQL语句执行性能*/
    EXPLAIN SELECT * FROM student WHERE studentno='1000';
    
    /*使用全文索引*/
    -- 全文搜索通过 MATCH() 函数完成。
    -- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
    EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
    
    /* 开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况 MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引； MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引; 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。 测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。 */

### 测试索引 ###

-- 建表
    -- 默认时间 DEFAULT CURRENT_TIMESTAMP
    CREATE TABLE `app_user` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
    `email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
    `phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
    `gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0:男；1：女）',
    `password` VARCHAR(100) NOT NULL COMMENT '密码',
    `age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
    `create_time` DATETIME,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
    
    
    -- 创建 插入一百万条数据 的函数
    DELIMITER $$ -- 写函数之前的默认操作
    CREATE FUNCTION mock_data()
    RETURNS INT
    BEGIN
      DECLARE num INT DEFAULT 1000000;
      DECLARE i INT DEFAULT 0;
      WHILE i < num DO
        -- 插入数据
    	INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`,`create_time`)
    	VALUES(
    	  CONCAT('用户', i),
    	  '896352417@qq.com',
    	  CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),
    	  FLOOR(RAND()*2),
    	  UUID(),
    	  FLOOR(RAND()*100),
    	  NOW()
    	);
    	SET i = i+1;
      END WHILE;
      RETURN i;
    END;
    
    -- 调用函数
    SELECT mock_data();
    
    
    SELECT * FROM app_user WHERE `name`='用户9999' -- 耗时 0.686 sec
    SELECT * FROM app_user WHERE `name`='用户9999' -- 耗时 0.711 sec
    EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' -- 查询了1000258行数据
    
    -- 一般索引命名: id_表名_字段名
    -- 创建索引: CREATE INDEX 索引名 ON 表(字段)
    CREATE INDEX id_app_user_name ON app_user(`name`);
    
    -- 测试发现快了许多
    SELECT * FROM app_user WHERE `name`='用户9999' -- 耗时 0 sec
    EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' -- 查询了一行数据

### 索引准则 ###

*  索引不是越多越好
 *  不要对经常变动的数据加索引
 *  小数据量的表建议不要加索引
 *  索引一般应加在查找条件的字段

> 索引的数据结构

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
    hash类型的索引：查询单条快，范围查询慢
    btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
    
    -- 不同的存储引擎支持的索引类型也不一样
    InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
    MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
    Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
    NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
    Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；