Mysql索引

Mysql索引

一、什么是索引

官方定义：索引（Index）是帮助Mysql高效获取数据的数据结构，提取句子主干，就可以得到索引的本质：索引就是数据结构

二、索引的分类

• 主键索引（primary key）
  • 唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引（unique key）
  • 避免重复的列出现，可以重复，多个列都可以标识为唯一索引
• 常规索引 （key/index）
  • 默认的，可以使用（key/index）来设置
• 全文索引（FullText）
  • 在特定的数据库引擎下才有用，MyISAM
  • 快速定位

三、索引的使用

1、在创建表的时候给字段添加索引；

2、创建完毕后，增加索引；

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

-- 添加一个全文索引
create index 索引名 on 表名（字段名）
alter table 表名 add fulltext index 索引名（列名）

--EXPLAIN 分析sql执行的状况
explain select * from 表名 			--常规索引
explain select * from student  where MATCH(pname) AGAINST('匿')

-- -拟百万条数据测试
-- 创建表
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) not null COMMENT '用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
`phone` VARCHAR(20) not null 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用户表'
-- 插入百万条数据
DELIMITER $$  -- 写函数之前必须要写，标志
-- set global log_bin_trust_function_creators=TRUE;
create function mock_data()
returns int 
DETERMINISTIC
begin
	declare num int default 1000000;
	declare i int default 0;
	while i<num do
		-- rand()函数在0~1之间产生随机数，floor（）函数取整
		insert into `app_user` (`name`,`email`,`phone`,`gender`,`password`,`age`)
		values(concat("用户",i),concat(floor(rand()*100000000),"@qq.com")
		,floor(rand()*1000000000),floor(rand()*2),floor(rand()*1000000),floor(rand()*100));
    set i = i+1;                             
	  end while;
    return i;
end;
select mock_data() --  插入数据

-- 普通查询(用时0.84s，rows 995253)
select * from app_user where `name`='用户2333'
explain select * from app_user where `name`='用户2333'

-- 索引查询(用时0.037s,rows 1）
create index id_app_user_name on app_user(`name`);
select * from app_user where `name`='用户2333'
explain select * from app_user where `name`='用户2333'

-- 总结：索引在数据量小的时候区别不大，但是数据量大的时候区别很大；

四、索引原则

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

索引的数据结构

• 二叉树

  • 二叉树是一种比顺序结构更加高效地查找目标元素的结构

• 红黑树

  • 红黑树也叫平衡二叉树，它不仅继承了二叉树的优点，而且解决了上面二叉树遇到的自增整形索引的问题

• Hash 类型

  • 对数据进行Hash（散列）运算，主流的Hash算法有MD5、SHA256等等，然后将哈希结果作为文件指针可以从索引文件中获得数据的文件指针，再到数据文件中获取到数据
  • 无法解决范围查询（Range）的场景，比如 select count(id) from sus_user where id >10；

• B-Tree ：Innodb的默认数据结构

  • BTree的结构可以弥补红黑树的缺点，解决数据量过大时整棵树的深度过长的问题。相同数量的数据只需要更少的层，相同深度的树可以存储更多的数据，查找的效率自然会更高。
  • 查询单条数据是非常快的。但如果范围查的话，BTree结构每次都要从根节点查询一遍，效率会有所降低