MySQL的索引类型和实现原理

索引类型

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

1. FULLTEXT
即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH
由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

HASH索引的缺点：

• 因为Hash索引比较的是经过Hash计算的值，所以只能进行等式比较，不能用于范围查询
• 每次都要全表扫描
• 由于哈希值是按照顺序排列的，但是哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法利用Hash索引来加速任何排序操作
• 不能用部分索引键来搜索，因为组合索引在计算哈希值的时候是一起计算的。
• 当哈希值大量重复且数据量非常大时，其检索效率并没有Btree索引高的。

3. BTREE
B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型，除了 Archive 存储引擎之外的其他所有的存储引擎都支持 B-Tree 索引。
BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

Btree索引的存储结构在Innodb和MyISAM中有很大区别。

• MyISAM中数据文件和索引文件是分开的。当我们根据Btree索引去搜索的时候，若key存在，在data域找到其地址，然后根据地址去表中查找数据记录。
• Innodb的索引文件就是数据文件。叶子节点存储的并不是表的地址，而是数据。

4. RTREE
RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找

索引种类

• 普通索引：仅加速查询
• 唯一索引：加速查询 + 列值唯一（可以有null）
• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减。如index_name(‘col1’,‘col2’,‘col3’)，相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的
• 全文索引：对文本的内容进行分词，进行搜索

索引操作

创建索引

<直接创建索引>

-- 创建普通索引
CREATE INDEX index_name ON 'table_name'(col_name);
-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON 'table_name'(col_name);
-- 创建普通组合索引
CREATE INDEX index_name ON 'table_name'(col_name_1,col_name_2);
-- 创建唯一组合索引
CREATE UNIQUE INDEX index_name ON 'table_name'(col_name_1,col_name_2);
--创建全文索引
ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

<通过修改表结构创建索引>

ALTER TABLE table_name ADD INDEX index_name(col_name);

刪除索引

//直接删除索引
DROP INDEX index_name ON table_name;
//修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;

查看索引

#查看:
show index from \`表名\`;
#或
show keys from \`表名\`;

查看查询语句使用索引的情况

//explain 加查询语句
explain SELECT * FROM table_name WHERE column_1='123';

索引机制

1.为什么我们添加完索引后查询速度为变快？

• 传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，mysql需要将表的数据从头到尾遍历一遍
• 在我们添加完索引之后，mysql一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历(折半查找大幅查询效率)，找到相应的键从而获取数据

2.索引的代价

• 创建索引是为产生索引文件的，占用磁盘空间
• 索引文件是一个二叉树类型的文件，可想而知我们的dml操作同样也会对索引文件进行修改，所以性能会下降

3.在哪些column上使用索引？

• 较频繁的作为查询条件字段应该创建索引
• 唯一性太差的字段不适合创建索引，尽管频繁作为查询条件，例如gender性别字段
• 更新非常频繁的字段不适合作为索引
• 不会出现在where子句中的字段不该创建索引

慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，MySQLl数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

查看慢日志参数

 --查询配置命令
 show variables like '%query%';
 --当前配置参数
 binlog_rows_query_log_events    OFF ft_query_expansion_limit    20   have_query_cache    YES
 --时间限制，超过此时间，则记录
 long_query_time    10.000000 query_alloc_block_size    8192 query_cache_limit    1048576   query_cache_min_res_unit    4096    query_cache_size    1048576   query_cache_type    OFFquery_cache_wlock_invalidate    OFFquery_prealloc_size    8192
 --是否开启慢日志记录
 slow_query_log    OFF
 --日志文件
 slow_query_log_file    usr/local/var/mysql/data/Jack-slow.log

修改当前配置

 set global 变量名 = 值;
 --例如，修改时间限制为20 
 set global slong_query_time = 20;

ps.也可以直接打开慢日志配置文件进行修改，但必须重启服务才能生效

查看MySQL慢日志

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log