7、MySQL索引相关

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

参考博客：https://blog.csdn.net/weixin_42470983/article/details/104480373

7.1、索引的分类

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

• 主键索引（PRIMARY KEY）

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

• 唯一索引（UNIQUE KEY）

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

• 常规索引（KEY/INDEX）

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

• 全文索引（FullText）

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

基础语法：

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


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


-- 增加一个索引 (索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);


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


EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘');  -- 全文索引

7.2、测试索引

创建用户表

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用户表'

批量插入数据：

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


CREATE FUNCTION mock_date111()
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),'276439029@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-10000000)+10000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
      SET i = i+1;
   END WHILE;
   RETURN i;
END;


SELECT mock_date();

出现如下所示错误信息提示：

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

经查询资料得出解决方案：

先执行SQL代码：

SET GLOBAL log_bin_trust_function_creators = 1;

然后再执行上述批量添加数据代码即可成功！

测试索引：

SELECT * FROM app_user WHERE `name`='用户9999';        -- 总耗时: 2.210 sec
SELECT * FROM app_user WHERE `name`='用户9999';        -- 总耗时: 2.072 sec
SELECT * FROM app_user WHERE `name`='用户9999';        -- 总耗时: 2.094 sec
SELECT * FROM app_user WHERE `name`='用户9999';        -- 总耗时: 2.051 sec

EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';        -- 1987565

SELECT * FROM student

-- id_表名_字段名
-- CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);    -- 总耗时: 10.972 sec

SELECT * FROM app_user WHERE `name`='用户9999';        -- 总耗时: 0.005 sec
SELECT * FROM app_user WHERE `name`='用户9999';        -- 总耗时: 0.002 sec
SELECT * FROM app_user WHERE `name`='用户9999';        -- 总耗时: 0.002 sec

EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';        -- 1

添加索引前：

添加索引后：

索引在小数据量的时候，用户不大，但是在大数据的时候，区别十分明显~~

7.3、索引原则

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

索引的数据结构：

Hash类型的索引
Btree：InnoDB的默认数据结构~

阅读文章：http://blog.codinglabs.org/articles/theory-of-mysql-index.html