Mysql索引

Mysql索引

​ 索引(index)是帮助Mysql高效获取数据的数据结构。即索引是数据结构。同一个表的索引总数为16个。由于算法一般只能应用于特定的数据结构，所以索引的底层数据结构和算法有很多种，各有优势。

​ 二叉搜索树

B-Tree索引

​ 现在大多数数据库系统及文件系统都使用的是BTree和其变种B+Tree(每一个叶子节点都指向下一叶子节点的指针，方便叶子节点的范围遍历)

B-Tree通常意味着所有的值都是按顺序存储好的，并且每一个叶子到根的距离相同，很适合查找范围数据。

primary key 主键索引 一张表只能由一个主键
unique key 唯一索引
key 或 index 常规索引
fulltext 全文索引

​ 普通的mysql性能不够好，特别是多表查询、子查询等，sql语句欠佳。

​ 解析过程一般是先解析from再解析select，即解析过程与编写过程不一致。

​ sql优化，主要就是优化索引，索引，就相当于书的目录。

索引的弊端

构建索引底层的数据结构是需要一定内存的，所以索引在很多场景并不适用。

索引的使用场景

• 数据很多(上百万以上)
• 更新不频繁(重新需要构建数据结构，消耗大量资源)
• 同列重复数据不多
• 用于常查询的字段

并且索引虽然会提高查询效率，但会减小增删改的效率。

优点

• 提高查询效率(降低IO使用率)
• 降低CPU使用率(已经排好序了)

​ 索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。并且只为最经常查询和最经常排序的数据列建立索引。

• 主键索引
• 唯一索引
• 常规索引 默认的(index/key)
• 全文索引 特定的数据库才有

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

Alter table student add fulltext index 'studentName'('studentName') 索引名(列名)

create index 索引名 on 表(列名)
Create index student_id on student('name')

分析sql执行的情况
Explain Select * from student; 

索引的使用
Explain select * from student where matcch(studentName) against ('张')

插入100万条数据用于测试

Delimiter $$ --函数的标志
Create function data()
returns int 
begin
 	declare num int default 1000000;
 	declare i int default 0;
 	while i<num DO
 	insert into ... value (concat('用户',i))--插入数据 并且拼接字符串 rand生成随机数
 	set i = i+1;
 	end while;
end;

在一百万条数据下查询同一条数据 使用索引前后效果对比

使用前 1.098 sec
使用后 0.001 sec

资料来源——http://blog.codinglabs.org/articles/theory-of-mysql-index.html