mysql索引

索引

mysql官方对于索引的定义为：索引是帮助mysql高效获取数据的结构，

提取句子主干，就可以得到索引的本质， 索引是数据结构

索引的分类

• 主键索引（PRIMARY KEY）

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

• 唯一索引 （UNIQUE KEY）

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

• 常规索引 （KEYINDEX）

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

• 全文索引 (FullText)

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

基础语法：

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

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

SELECT * FROM class

-- 增加一个全文索引 （索引名） 列名
ALTER TABLE school.class ADD FULLTEXT INDEX `caption`(`caption`)

-- EXPLAIN 分析sql执行的情况

EXPLAIN	SELECT * FROM class

EXPLAIN SELECT * FROM class WHERE MATCH(`caption`)AGAINST('三')

测试索引

DROP FUNCTION IF EXISTS mock_data;
-- 写函数之前必须要写，标志:$$
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
-- 注意returns，否则报错。
BEGIN
DECLARE num INT DEFAULT 1000000;
-- num 作为截止数字，定义为百万，
DECLARE i INT DEFAULT 0;
WHILE i < num DO
   INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), CONCAT('100',i,'@qq.com'), CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
   SET i = i + 1;
END WHILE;
RETURN i;
END;

SELECT mock_data();

SELECT * FROM `app_user`

-- id _表名 _ 字段名
-- CREATE INDEX  索引名 on 表名(字段)

-- 创建索引之前:
SELECT * FROM `app_user` WHERE`name`='用户99996'    1.325 sec
SELECT * FROM `app_user` WHERE`name`='用户999960'   1.194 sec
EXPLAIN SELECT * FROM `app_user` WHERE`name`='用户999960'  -- rows=992293

CREATE INDEX id__app_user_name ON app_user(`name`)

-- 创建索引之后：
SELECT * FROM `app_user` WHERE`name`='用户99996'    0.127 sec
SELECT * FROM `app_user` WHERE`name`='用户999960'   0.090 sec

EXPLAIN SELECT * FROM `app_user` WHERE`name`='用户999960'   -- rows =1

-- 索引在数据量小的时候，差距不多， 在数据量很多的时候，差距很明显

索引原则

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