Mysql基础：索引

-- =====索引=====

-- 定义：索引（index）是帮助MySQL高效获取数据的数据结构。-- 本质：索引就是数据结构
-- 索引分类：
　　 主键索引（primary key）

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

　　 唯一索引（unique key）

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

　　 常规索引（key/indey）

　　　　　　默认的，index/key关键字来设置

　　 全文索引（fulltext）=

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

 

索引语法

-- =====索引基础语法=====
-- 定义：索引（index）是帮助MySQL高效获取数据的数据结构。
-- 本质：索引就是数据结构

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

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

-- 增加一个全文索引（索引名） 列名
ALTER TABLE school.student add FULLTEXT `studentname`(studentname);

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

EXPLAIN SELECT*FROM student WHERE MATCH(studentname) AGAINST('刘');

 

索引测试，根据数据库生成100万条数据通过select进行对比

 

-- =====测试索引=======

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：女）',
    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用户表'

-- 插入100万数据.
DELIMITER $$
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data ()
RETURNS INT DETERMINISTIC
-- DETERMINISTIC为确定性函数
BEGIN
    DECLARE num INT DEFAULT 1000000;
    DECLARE i INT DEFAULT 0;
    WHILE i<num DO
        INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
        SET i=i+1;
    END WHILE;
    RETURN i;
END;

SELECT mock_data() -- 执行此函数 生成一百万条数据，用了48秒才跑完

-- 不加索引前
SELECT * FROM app_user WHERE `name`='用户9999';-- 0.993sec

-- 加索引  id_表名_字段名
-- 语法：CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);-- 注：在执行创建索引过程中，会给每条数据加索引
SELECT * FROM app_user WHERE `name`='用户9999';-- 0.001s

原因： 不加索引时，要查询表中某个数据时，需要遍历才能找到，因此所找数据越大，需要时间越大
　　　 加索引后，是在创建索引时给每条数据都加了对应索引，因此他的原理就是，当你查某条数据时，它本质仅仅是搜索了一次就可以实现了，因此大大的节省了时间

 

索引原则（大于500万条数据后再考虑加索引）

　　1.索引不是越多越好
　　2.不要对经常变动的数据加索引
　　3.小数据量的表不需要加索引
　　4.索引一般加在常用来查询字段上

推荐看：索引的数据结构相关知识

索引的数据结构（常见）

　　Hash 类型索引

　　Btree -- INNODB默认的索引类型（数据结构）

　　　　

 

posted @ 2020-09-26 10:13  凸然猿  阅读(139)  评论(0)  编辑  收藏  举报