索引

MySQl官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干：就可以得到索引的本质：索引是数据结构。

1、索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引 （PRIMARY KEY）

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

• 唯一索引 （UNIQUE KEY）

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

• 常规索引（KEY/INDEX）

  -默认的，通过index，key关键字来设置

• 全文索引（FullText）

  • 在特定的数据库引擎下才有，MyISAM
  • 快速定位数据

基础语法

-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后，增加索引


-- 显示所有的索引信息
SHOW INDEX FROM account;

-- 增加一个全文索引 （索引名） 列名
ALTER TABLE shop.account ADD FULLTEXT INDEX `name`(`name`);

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM account;  -- 非全文索引

EXPLAIN SELECT * FROM account WHERE MATCH(name) AGAINST('a');

2、测试索引

CREATE TABLE `test_user`(
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) DEFAULT '' COMMENT '用户名',
    email VARCHAR(50) NOT NULL COMMENT '邮箱',
    phone VARCHAR(50) DEFAULT '' COMMENT '手机号',
    gender TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0：男，1：女）',
    passwd 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=utf8 COMMENT '用户表';


-- 插入100万数据
DELIMITER $$ -- 写函数之前必须要写，用作标志

CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
    DECLARE num INT DEFAULT 1000000;
    DECLARE i INT DEFAULT 0;
    
    WHILE i<num DO
        -- 插入语句
        INSERT INTO test_user(`name`,`email`,`phone`,`gender`,`passwd`,`age`)
        VALUES(CONCAT('用户',i),'281463547@qq.com',
        CONCAT('13',FLOOR(RAND()*((999999999-1000000)+1000000))),
        FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
        SET i=i+1;
    END WHILE;
    RETURN i;
END;

SELECT mock_data();

SELECT * FROM test_user WHERE `name` = '用户19998';  -- 查询时间3.622s
SELECT * FROM test_user WHERE `name` = '用户19998';  -- 查询时间2.349s

EXPLAIN SELECT * FROM test_user WHERE `name` = '用户19998'; -- rows 992262

-- id_表名_字段名
-- 语法： CREATE INDEX 索引名 ON 表（字段名）
CREATE INDEX id_test_user_name ON test_user(`name`);  -- 创建用时18.442s

SELECT * FROM test_user WHERE `name` = '用户19998'; -- 创建索引后查询用时0.028s
SELECT * FROM test_user WHERE `name` = '用户19998'; -- 0.026s

EXPLAIN SELECT * FROM test_user WHERE `name` = '用户19998'; -- rows 1

注：索引在小数据量的时候用处不大，但在大数据的时候，区别十分明显

3、索引原则

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

索引的数据结构

Hash类型的索引

BTree：InnoDB的默认数据结构