MySQL索引优缺点及使用讲解
索引
1. 什么是索引
创建在表上，是对数据库表中一列或多列的值进行排序的结构。
用于快速查询数据库表中的特定记录，可提高查询速度。
不同的存储引擎定义了每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持
16个索引，总索引长度至少256字节。
2.分类
2.1 聚簇索引和非聚簇索引
聚簇索引
数据的物理存放顺序和索引顺序是一致的；
唯一性；
聚簇索引的叶结点就是数据结点；
主键默认设为聚簇索引；
InnoDB引擎按照聚簇索引存储数据。
非聚簇索引
索引顺序与数据物理排列顺序无关；
一 个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；
非聚簇索引的叶结点是索引结点，有一个指针指向对应的数据块；
MyISAM使用的是非聚簇索引。
2.2 存储类型
索引有两种存储类型：B型（BTREE）树索引和哈希（HASH）索引。
InnoDB和MyISAM存储引擎支持BTREE索引。HASH索引
特征如下：
只用于使用=或<=>操作符的等式比较。
优化器不能使用HASH索引来加速ORDER BY操作。
MySQL不能确定在两个值之间大约有多少行。如果将一个MyISAM表改为HASH索引的MEMORY
表，会影响一些查询的执行效率。
只能使用整个关键字来搜索一行。
BTREE索引
当时>、<、>=、<=、BETWEEN 、！= 或者 <> , 或者 LIKE‘pattern' 操纵符时，都可以使 用相关列上的索引。
下列范围查询适用于BTREE索引和HASH索引：
SELECT* FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);
下列范围查询适用于BTREE索引：
SELECT* FROM t1 WHERE key_col > 1 AND key_col < 10;
SELECT* FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'lisa'
AND simon';
3.优点和缺点
优点：
- 提高检索速度；
- 对于有依赖关系的子表和父表之间的联合查询时，可提高查询速度；
- 使用分组和排序子句进行数据查询时，显著节省查询中分组和排序的时间。
缺点：
- 创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；
- 索引需要占用物理空间；
- 增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。
4. 索引分类：
1）普通索引：不附加任何限制条件，可创建在任何数据类型中，其值是否唯一和非空由字段本 身的完整性约束条件决定。
2）唯一性索引：使用UNIQUE参数设置索引。索引的值是唯一的。
3）全文索引：使用FULLTEXT参数设置索引。只能创建在CHAR、VARCHAR或TEXT类型字段上。查询数据量较大的字符串类型的字段时，使用全文索引可提高查询速度。
4）单列索引：在表中的单个字段上创建索引。只根据该字段进行索引（索引对应一个字段）。单列索引可以是普通索引或者唯一性索引或者全文索引。
5）多列索引：在表的多个字段上创建一个索引。可通过几个字段进行查询。
只有查询条件使用多列关键字最左边的前缀时，才可以使用索引，否则将不能使用索引。
6）空间索引：使用SPATIAL参数设置。
只能建立在空间数据类型上，可提高系统获取空间数据的效率。
只有MyISAM存储引擎支持空间检索，索引字段不为空。
5. 索引的设计原则
1）选择唯一性索引。索引的列的基数越大，索引效果越好。
2）为经常需要排序、分组和联合操作的字段建立索引（避免排序操作，浪费时间）
3）为常作为查询条件的字段建立索引（提高查询速度）
4）限制索引的数目，不要过度索引（每个索引都需要占用磁盘空间，会降低写操作的性能。）
5）尽量使用短索引。如果对字符串进行索引，应该指定一个前缀长度。
6）尽量使用左前缀来索引（字段值很长，使用值的前缀索引）
7）删除不再使用或者很少使用的索引（减少索引对更新操作的影响）
8）对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存。
InnoDB表的普通索引会保存主键的键值，主键应选择较短的数据类型，减少索引的磁盘占用，提高索引的缓存效果。
9）辅助索引，叶子结点存放着索引字段的值及对应的主键值
10）对查询where条件中区分度高的字段加索引；对查询分组和排序分组加索引。
11）一下情况无法使用到索引：like通配符在最左，not in, !=, <>, 队列做函数运算， 隐式数据类型转换，OR子句。
12）FORCE INDEX强制加索引
6. 创建索引
创建表示可直接创建索引
1）创建普通索引
创建一个表名为index1的表，在表中的id字段上建立索引。
CREATE TABLE index1（id INT , name VARCHAR(20),INDEX(id) ）；
2）创建唯一性索引
CREATE TABLE index2 （id INT UNIQUE， name VARCHAR(20),
UNIQUE INDEX index2_id(id ASC) );
3)创建全文索引(只有MyISAM存储引擎支持全文索引)
创建一个表名为index3的表，在表中的info字段上建立名为index3_info的全文索引。
CREATE TABLE index3 （id INT, info VARCHAR(20),
FULLTEXT INDEX index3_info(info) )ENGINE=MyISAM;
4）创建单列索引（单个字段）
创建一个表名为index4的表，表中的subject字段上建立名为index4_st的单列索引。
CREATE TABLE index4（id INT， subject VARCHAR(30)，
INDEX index4_st(subject(10)) );
5)创建多列索引（多个字段,只有使用了第一个字段时才会触发索引）
创建表名为index5的表，在表中的name和sex字段上建立名为index5_ns的多列索引。
CREATE TABLE index5(id INT, name VARCHAR(20), sex CHAR(4),
INDEX index5_ns(name, sex) );
6）创建空间索引
在index6表中的space字段上建立名为index6_sp的空间索引。
CREATE TABLE index6（id INT，space GEOMETRY NOT NULL,
SPATIAL INDEX index6_sp(space) )ENGINE=MyISAM;
7. 在已存在的表上建立索引
CREATE UNIQUE INDEX index_id ON index(course_id);
1）创建普通索引
CREATE INDEX index7_id ON example0(id); //id字段上建立索引
2）唯一性索引
CREATE UNIQUE INDEX index8_id ON index8(course_id); //索引为index8_id
3)全文索引
CREATE FULLTEXT INDEX index9_info ON index9(info);
4)单列索引
CREATE INDEX index10_addr ON index10(address(4)); //查询address前4个字符
5）创建多列索引
CREATE INDEX index11_na ON index11(name,address); //必须有name字段
6）空间索引
CREATE SPATIAL INDEX index12_line ON index12(line);
8. 用ALTER TABLE 语句创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT |SPATIAL] INDEX
索引名（属性名 [(长度)] [ASC|DESC]);
1）创建普通索引
在s1表中name字段上建立index_name的索引。如下：
ALTER TABLE s1 ADD INDEX index_name(name(20));
2）唯一性索引
在s2表中course_id字段上，建立index_id的唯一性索引。如下：
ALTER TABLE s2 ADD UNIQUE INDEX index_id(course_id);
3)全文索引
在s3表中的info字段上建立名为index_info的全文索引。如下：
ALTER TABLE s3 ADD FULLTEXT INDEX index_info(info);
4)单列索引
在s4表中的address字段上建立名为index_addr的单列索引。
ALTER TABLE s4 ADD INDEX index_addr(address(4));
5）创建多列索引
在s5表中的name和address字段上建立名为index_na索引
ALTER TABLE s5 ADD INDEX index_na(name,address);
6）空间索引
在s6表中的line字段上建立名为index_line的多列索引。
ALTER TABLE s6 ADD SPATIAL INDEX index_line（line);
9. 删除索引
DROP INDEX 索引名 ON 表名;查看索引使用情况
show status like 'Handler_read%';
Handler_read_rnd_next的值较高意味着查询运行抵消。
11. 索引优化
定期分析表和检查表ANALYZE table tbl_name; //表分析 CHECK table tbl_name; //表检查定期优化表
OPTIMIZE table tbl_name;