mysql的索引介绍和有无索引性能测试

USE db_01;
-- AUTO_INCREMENT 单独添加
DROP TABLE IF EXISTS person;
CREATE TABLE IF NOT EXISTS `person`(
`id` INT NOT NULL  COMMENT '编号',
`name` VARCHAR(20) NOT NULL DEFAULT '无名氏' COMMENT '姓名',
`age` TINYINT NOT NULL DEFAULT 0 COMMENT '年龄',
`gender` TINYINT NOT NULL DEFAULT 0 COMMENT '性别',
`phone` VARCHAR(11) COMMENT '手机号码',
`password` VARCHAR(100) DEFAULT '123' COMMENT '密码',
`createTime` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updateTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间：系统自动识别更新时间',
PRIMARY KEY (`id`)
)ENGINE =INNODB CHARSET= utf8mb4; -- 注意点：utf8（最多保存3个字符长度） 并不等于utf-8（最多保存4个字符长度） ，mysql 5.5.3+ 版本后可以设置为utf8mb4=utf-8

ALTER TABLE `person` CHANGE `id` `id` INT NOT NULL AUTO_INCREMENT COMMENT '编号'; -- 添加一个属性自增长

ALTER TABLE `person` ADD PRIMARY KEY (`id`);-- 添加一个主键索引


-- ===================================   创建一个自定义函数 mysql8.0以后写法 ==================================
DELIMITER $$
CREATE FUNCTION insertData01(startIndex INT)
RETURNS INT
NO SQL
BEGIN
  DECLARE num INT DEFAULT 100;
 -- DECLARE startIndex INT DEFAULT 0;

  -- 循环插入数据
  WHILE startIndex<num DO
    -- 插入语句
    INSERT INTO person (`name`,`age`,`gender`,`phone`,`password`)VALUES(CONCAT('user',startIndex),FLOOR(RAND()*100),FLOOR(RAND()*2),CONCAT('15',FLOOR(RAND()*999999999)),UUID());
    SET startIndex=startIndex+1;
  END WHILE;
  RETURN startIndex; -- 返回了当前执行了多少行
END;


SELECT insertdata01(5); -- 调用函数（有参数的情况）

-- -------------------------- 添加索引之前的查询速度  -----------------------------
-- 索引分类：主键索引、唯一索引、普通索引、全文索引、组合索引
-- 主键索引：创建主键即创建主键索引成功，不允许重复，不允许空值
--        sql示范：ALTER TABLE `person` ADD PRIMARY KEY (`id`);-- 添加一个主键索引
-- 唯一索引：用来建立索引的列的值必须是唯一的，允许空值
--        sql示范：ALTER TABLE `person` ADD UNIQUE INDEX id_表名_字段名('要添加的索引列');
-- 普通索引：用表中的普通列构建的索引，没有任何限制
--        sql示范：ALTER TABLE `person` ADD INDEX id_表名_字段名('要添加的索引列');
-- 全文索引：用于大文本对象的列构建的索引（例如：对文章的内容查询时）
--        sql示范：ALTER TABLE `person` ADD FULLTEXT INDEX ft_index('col');
-- 组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值
--        sql示范：ALTER TABLE `person` ADD INDEX id_表名_字段名('要添加的索引列1','列2'，'列3',....);
--        (注意点：1.最常用作为检索或排序的列放在最左，依次递减 2.组合索引所包含的列不允许再单独添加索引)

SELECT * FROM person WHERE `name` ='user989898'; -- 执行耗时   : 1.583 sec

EXPLAIN SELECT * FROM person WHERE `name` ='user989898'; -- 查询的行：994249 key:null（代表没有索引） EXPLAIN：查看SQL是否使用索引，查询sql语句前面加上explain即可

CREATE INDEX id_person_name ON `person`(`name`); -- 为person表的name字段添加一个普通索引

SELECT * FROM person WHERE `name` = 'user989898'; -- 执行耗时   : 0.001 sec


EXPLAIN SELECT * FROM person WHERE `name` ='user989898'; -- 查询的行：1 key:id_person_name（说明索引存在） EXPLAIN：查看SQL是否使用索引，查询sql语句前面加上explain即可