学习博客：【MySQL】索引

1. 分类

• 主键索引

  唯一标识，主键不可重复，只能有一个列作为主键

• 唯一索引

  避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引

• 常规索引

  默认的，key、index关键字设置

• 全文索引

  在特定数据库引擎下才有，MyISAM

  快速定位数据

-- 显示所有索引信息
SHOW INDEX FROM tb_newbee_mall_goods_info

-- 增加一个全文索引
ALTER TABLE newbee.tb_newbee_mall_goods_info ADD FULLTEXT INDEX `goods_name`(`goods_name`)

-- 分析sql执行状况
EXPLAIN SELECT * FROM tb_newbee_mall_goods_info 

EXPLAIN SELECT * FROM tb_newbee_mall_goods_info WHERE MATCH(goods_name) AGAINST('无印')

2. 测试索引

-- 插入100万数据测试索引
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 DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'


-- 创建函数时报错解决办法
SET GLOBAL log_bin_trust_function_creators=TRUE


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`) VALUES (CONCAT('用户',i),CONCAT(FLOOR(RAND()*((999999999-100000000)+100000000)),'@qq.com'),CONCAT('13',FLOOR(RAND()*((999999999-100000000)+1000000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
      SET i=i+1;
   END WHILE;
   RETURN i;
END;

DROP FUNCTION mock_data();
SHOW CREATE FUNCTION mock_data()
SELECT mock_data()

EXPLAIN SELECT * FROM app_user WHERE `name`='用户3263'

-- 添加索引
-- id_表名_字段名
CREATE INDEX id_app_user_name ON app_user(`name`)

3. 索引原则

• 不是越多越好
• 不要对进程变动数据加索引
• 小数据量的表不需要加索引
• 一般加在常用来查询的字段

索引的数据结构

Hash类型的索引

Btree：InnoDB的默认数据结构

MySQL索引背后的数据结构及算法原理