数据库索引

数据库索引

mysql官方对索引的定义：索引（index）是帮助mysql高效获取数据的数据结构。

索引 的分类

• 主键索引（primary key） 一个表只能有一个主键索引，一般表内的id字段设置为主键
• 唯一索引（unique key） 一个表可以有多个唯一索引，唯一索引的列数据不可重复，例如身份证号可以设置为唯一索引
• 常规索引（key/index） 这是最基本的索引类型，而且它没有唯一性之类的限制
• 全文索引（fulltext）MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。

测试

-- ====================测试索引================================
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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

SET GLOBAL log_bin_trust_function_creators=TRUE;

-- 插入100万数据.
DELIMITER $$
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data3()
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),'19224305@qq.com','123456789',FLOOR(RAND()*2),FLOOR(RAND()*888888),FLOOR(RAND()*100));
        SET i=i+1;
    END WHILE;
    RETURN i;
END;
SELECT mock_data3(); -- 执行此函数 生成一百万条数据

SELECT * FROM `app_user` WHERE `name`='用户999999';
SELECT * FROM `app_user` WHERE `id`=999999;

EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户999999';  -- 总耗时: 0.992 sec 查询了99w行数据
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999';  -- 总耗时: 0.995 sec 查询了99w行数据

-- 插入索引
INSERT INTO `app_user` ADD FULLTEXT TO `name`;

-- 创建索引
CREATE INDEX `id_app_user_name` ON `app_user`(`name`);

-- 分析SQL查询的详细情况
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户999999'; -- 总耗时: 0.002 sec 仅查询了一行数据

语法

-- 显示所有的索引信息
show index from tablename

-- 增加一个索引
-- 方法一 创建表的时候创建
create table `tablename`(
    `id` int(8) not null,
    `name` varchar(20) not null,
    primary key(`id`), --主键索引
    key|index `name`(`name`) -- 普通索引 key 和index 都可以
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 方法二 增加一个索引
alter table `tablename` add index `索引名`(`字段名`);
-- 方法三 创建索引
CREATE INDEX `id_app_user_name` ON `app_user`(`name`);

-- explain 分析SQL执行的情况
EXPLAIN SELECT * FROM `tablename`
EXPLAIN SELECT * FROM `tablename` WHERE `字段`=值;

索引在小数据量的时候用处不大，但是在大数据的时候，效率提高很明显