MySql 索引 (7)

MySQL官方对索引的定义为：索引(index)是帮助MySQL高效获取数据的数据结构。

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

7.1 索引的分类

在一个表中，主键索引是唯一的；唯一索引可以有多个

• 主键索引(PRIMARY Key)

  • 唯一的标示，主键不可以重复，只能有一个！

• 唯一索引(UNIQUE Key)

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

• 常规索引(Key/Index)

  • 默认的 Key/Index 关键字来进行设置

• 全文索引(FullTEXT)

  • 在特定的数据库引擎下才有 MyISAM

  • 快速定位数据

 

基础语法：

-- 索引的使用
-- 1.在创建表的时候给字段添加索引
-- 2.创建完毕，添加索引
​
-- 显示所有的索引信息
SHOW INDEX from student;

1. 添加PRIMARY KEY(主键索引)

   ALTER TABLE `table_name` ADD PRIMARY KEY(`colum`)

2. 添加UNIQUE（唯一索引）

   ALTER TABLE `table_name` ADD UNIQUE KEY(`colum`)

3. 添加INDEX（普通索引）

　　　　ALTER TABLE `table_name` ADD INDEX index_name (`colum`)

4. 添加FULLTEXT（全文索引）

　　　　ALTER TABLE `table_name` ADD FULLTEXT (`colum`)

5. 添加多列索引

　　　　ALTER TABLE `table_name` ADD INDEX index_name (`colum1`,`colum2`,`colum3`)

-- EXPLAIN 分析sql的执行情况
EXPLAIN SELECT * from student; -- 非全文索引
EXPLAIN SELECT * from student where MATCH(`studentName`) AGAINST('刘');  -- 全文索引

7.2 测试索引

-- 创建数据表
create table `app_user`(
  `id` int(11) not null auto_increment commit '学号',
  `name` varchar(255) not null commit '名字',
  `email` varchar(255) not null commit '邮箱',
  `phone` varchar(255) not null commit '电话号',
  `gender` TINYINT(4) not null commit '性别',
  `password` varchar(100) not null commit '性别',
  `age` TINYINT(4) not null commit '金钱',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  primary key (`id`)
)ENGING=INNODB DEFAULT CHARSET=utf8;
​
-- 插入100w 数据  55.177sec
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
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 (COUNT('用户',i),'123@qq.com',COUNT('18',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 WHERE `name` = '用户9999'; -- 0.993 sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.783 sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.933 sec
​
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; 
SELECT * FROM student;
​
-- id_表名_字段名
-- create index 索引名 on 表(字段)
create index id_app_user_name on `app_user`(`name`)
​
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.001 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; -- rows 1
​

索引在小数据量的时候不明显，在一定数据时，区别很明显！

7.3 索引原则

• 索引不是越多越好

• 不要对进程变动的数据加索引

• 小数据量的表不需要加索引

• 索引一般加在常用查询的字段上

 

索引的数据结构

hash 类型的索引

Btree ：InnoDB的默认数据结构

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