image.png
前言
在上篇文章中MySQL系统学习（03）：深入浅出索引
一篇文章中主要就MySQL的InnoDB引擎对应的B+树结构的索引实现、原理做了说明。这篇文章在就索引相关内容做一些补充。
索引概念补充
MySQL索引的建立是为了提高MySQL的检索性能的。
从索引字段组成角度来看，索引可以分为单列索引与组合索引两种。
从InnoDB引擎的索引数据结构来看，可以分为主键索引和非主键索引两种。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。同时，建立索引会占用磁盘空间的索引文件。
MySQL索引分类
MySQL目前索引主要包含这几种：全文索引（FULLTEXT）、哈希索引（HASH）、B树（BTREE）、R树（RTREE）四种。
FULLTEXT
即为全文索引，目前只有MYISAM引擎支持。它可以在CREATE TABLE、ALTER TABLE、CREATE INDEX时使用，目前只有CHAR、VARCHAR、TXT列上创建全文索引。
HASH
由于HASH的唯一性（几乎100%唯一），并且是类似键值对的形式，所以很适合作为索引。
HASH索引可以一次性定位，不需要像树形索引逐层查找，所以是极其高效的。但是这种高效是由限制的，只有“=”、“in”这个的检索条件下才高效，而当遇到范围检索、组合检索、排序等情况的事情效率确会很差。
BTREE
BTREE是一种将索引值按一定的算法，存入一个树形的数据结构中。每次查询都是从数的入口开始，依次遍历node。是MySQL里默认的索引结构。InnoDB引擎使用的是B+树，这个我们在上篇文章中有说明原因，极其工作原理的。
RTREE
RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
相对于BTREE，RTREE的优势在于范围查找。
索引种类
主键索引：加速查询+列值唯一（不可为null）+表中只有一个
普通索引：仅加速查询
唯一索引：加速查询+列值唯一（可为null）
组合索引：多列值组成一个索引，专门用于组合查询，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索
索引合并：使用多个单列索引组合搜索
覆盖索引：select的数据列只从索引中就能够取得，不行读取整行数据。可以理解为我们要查询的列被
索引操作
1.建表的时候创建索引
CREATE TABLE `table_name` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`col_name1` char(20) DEFAULT NULL COMMENT 'col_name1',
`col_name2` char(20) DEFAULT NULL COMMENT 'col_name2',
`col_name3` char(20) DEFAULT NULL COMMENT 'col_name3',
-- 在建表的时候就创建索引（这个做法较多）
PRIMARY KEY (`id`), -- 创建主键索引
unique key `uk_col_name1_col_name2` (`col_name1`, `col_name2`), -- 普通唯一索引
unique key `idx_col_name1_col_name2` (`col_name1`, `col_name2`), -- 组合唯一索引
key `idx_col_name1` (`col_name1`), -- 普通索引
key `idx_col_name1_col_names` (`col_name1`, `col_name2`) -- 普通组合索引
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='测试表';
2.通过CREATE语句创建索引
-- 创建普通索引
CREATE INDEX idx_index_name ON table_name (col_name);
-- 创建唯一索引
CREATE UNIQUE INDEX uk_index_name ON table_name(col_name);
-- 创建普通组合索引
CREATE INDEX idx_index_name ON table_name(col_name1,col_name2,col_name3);
-- 创建唯一组合索引
CREATE UNIQUE INDEX uk_index_name ON table_name (col_name1, col_name2);
3.通过修改表结构创建索引
ALTER TABLE table_name ADD INDEX idx_col_name1(col_name1);
4.删除索引
DROP index uk_index_name ON table_name;
5.查看索引
show index from table_name;
创建索引的时机
一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。比如：
SELECT t.Name FROM mytable_t LEFT JOIN mytable_m ON t.Name=m.username WHERE m.age=20 AND m.city='北京' ;
此时就需要对city和age建立索引，由于mytable_m表的userame也出现在了JOIN子句中，也有对它建立索引的必要。
注意事项
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量用 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复多）不适合建索引，例：性别不适合
慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。
默认情况下，MySQLl数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
关于慢查询日志的分析方法，后面单独抽一篇文章记录。
个人博客网站：RelaxHeart网-Tec博客
网友评论