MySQL 数据库索引的原理与使用规范

数据库索引

MySQL中索引的分类有几种？分别是什么？

1.普通索引
2.唯一键索引
3.主键索引
4.前缀索引
5.联合索引

1. 索引的种类

1.BTREE: B+树索引（Btree，B+tree，B*tree）
2.HASH：HASH索引（memery存储引擎支持）
3.FULLTEXT：全文索引（myisam存储引擎支持）
4.RTREE：R树索引

1.）B+tree索引

2. 索引根据算法分类

索引是建立在数据库字段上面的
当where条件后面接的内容有索引的时候，会提高速度

1.）多种索引

1.主键索引，（聚集索引）
# 创建表的时候创建主键索引
mysql> create table test1(id int not null auto_increment,other int,primary key(id));

# 查看索引命令
mysql> show index from test;

# 已经有表时添加主键索引
mysql> alter table student add primary key(id);

2.唯一键索引
# 添加唯一键索引并定义索引名字
mysql> alter table test2 add unique key uni_ke(other);

3.普通索引（辅助索引）
# 添加普通索引2种不同的方式：
mysql> alter table test2 add index(bad);
mysql> create index index_1 on test2(good);

# 删除索引2种不同方式：
mysql> alter table stu drop index index_1a;
mysql> drop index index_1a on stu;

mysql> show index from test2;
------------+----------+--------------+-------------+-----------
 Non_unique | Key_name | Seq_in_index | Column_name | Collation 
------------+----------+--------------+-------------+-----------
          0 | PRIMARY  |            1 | id          | A         
          0 | uni_ke   |            1 | other       | A         
          1 | index_1  |            1 | good        | A         
          1 | bad      |            1 | bad         | A                  
------------+----------+--------------+-------------+-----------
'在同一个列上可以建立多个不同的索引'
+----------+--------------+-------------+-----------+
| Key_name | Seq_in_index | Column_name | Collation |
+----------+--------------+-------------+-----------+
| PRIMARY  |            1 | id          | A         |
| id       |            1 | id          | A         |
| name     |            1 | name        | A         |
| index_1a |            1 | name        | A         |
+----------+--------------+-------------+-----------+

'主键索引不需要加索引名，默认索引名就为primary（无法更改）。'
'其他索引如果加名字，不可以重名；如果不加索引名字的时候会自动命名，相同名字会自动加后缀。'

2.）全文索引

1.创建全文索引
    1.1. 创建表的同时创建全文索引
         CREATE TABLE article ( 
              id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
              title VARCHAR(200), 
              body TEXT, 
              FULLTEXT(title, body)); 

    1.2.通过 alter table 的方式来添加
    ALTER TABLE student ADD FULLTEXT INDEX ft_stu_name(name) 
    #ft_stu_name是索引名，可以随便起
    或者：
    ALTER TABLE student ADD FULLTEXT ft_stu_name(name)

    1.3. 直接通过create index的方式
    CREATE FULLTEXT INDEX ft_email_name ON student(name)
    也可以在创建索引的时候指定索引的长度：
    CREATE FULLTEXT INDEX ft_email_name ON student(name(20))

2. 删除全文索引
    2.1. 直接使用 drop index('注意：没有 drop fulltext index 这种用法')
    DROP INDEX full_idx_name ON student ;

    2.2. 使用 alter table的方式 
    ALTER TABLE student DROP INDEX ft_email_abcd;

3. 索引根据配置分类

# 注意事项
1.创建索引时会将数据重新进行排序
2.创建索引会占用磁盘空间，所以索引不是越多越好
3.在同一列上避免创建多种索引
4.避免在数据很长的字段上创建索引，如果要创建就创建前缀索引

2.）前缀索引

#根据前四个字符创建前缀索引
mysql> alter table test add index index_key(name(4));

3.）联合索引

mysql> create database xiangqing;

mysql> create table xiangqin(id int,name varchar(20),gender enum('m','f'),age tinyint,money int,height int,weight int,looks tinyint);

mysql> insert xiangqin values(1,'qiudao','m',38,-200000,120,130,'10'),(2,'dilireba','f',18,400000,180,100,'60'),(3,'cxk','m',28,100000,170,120,'440'),
(4,'fbb','f',18,1000000,165,85,'90');

#根据使用频率高低创建联合索引
mysql> alter table xiangqin add index lh_key(money,gender,age,looks);

#联合索引使用三种情况
alter table aaa add index lh_key(a,b,c,d);
1.部分走索引  abc ，abd
2.全部走索引  abcd 
3.不走索引    bc,bcd,ba,cd...   # 只要不以a开头都不走（除非4个都有比如bcad）

4. explain的使用

# 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

➤ 通过EXPLAIN，我们可以分析出以下结果：
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
————————————————
#参考文献：https://blog.csdn.net/why15732625998/article/details/80388236

1. # select_type
分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询:
SIMPLE 简单的select查询，查询中不包含子查询或者UNION;
PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY;
SUBQUERY 在SELECT或WHERE列表中包含了子查询;
DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中;
UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED;
UNION RESULT 从UNION表获取结果的SELECT;

2. # type  
type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种：
从最好到最差依次是：

'system > const > eq_ref > ref > range > index > all'

一般来说，得保证查询至少达到range级别，最好能达到ref。

'system' 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。

'const' 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
首先进行子查询得到一个结果的d1临时表，子查询条件为id = 1 是常量，所以type是const，id为1的相当于只查询一条记录，所以type为system。

'eq_ref' 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

'ref' 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

'range' 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

'index' 是 Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）

id是主键，所以存在主键索引 
'all' Full Table Scan 将遍历全表以找到匹配的行

3. # possible_keys 和 key
possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key,实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）
查询中若使用了'覆盖索引'（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中

4. # key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

5. # ref
显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。

6. # rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好

7. # Extra
包含不适合在其他列中显式但十分重要的额外信息
1）Using where 使用where 查询会出现
-----------------
2）Using temporary （十死无生）
使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
-----------------
3）Using filesort （九死一生）说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
-----------------
4）Using index（发财了）
表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
-----------------
5）Using join buffer 表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
-----------------
6）impossible where 使用where子句的值总是false，不能用来获取任何元组
SELECT * FROM t_user WHERE id = '1' and id = '2'
-----------------
7）select tables optimized away   在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
-----------------
8）distinct   优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

5. 查询数据的方式

1.）全表扫描

#1.什么是全表扫描
查询数据时type类型为ALL

#2.什么情况全表扫描
1）查询数据库所有数据
    mysql> explain select * from country
2）没有走索引
    没设置索引
    索引损坏

2.）索引扫描

1.index         #全索引扫描
    mysql> explain select Name from city;

2.range         #范围查询
    mysql> explain select * from city where countrycode ='CHN' or countrycode ='USA';
    #有限制查询到的数据在总数据的15%以内，超过则走全文扫描，所以在查询是可以使用limit限制
    mysql> explain select * from city where countrycode != 'CHN' limit 500;

3.ref           #精确查询
    mysql> explain select * from city where countrycode ='CHN';

4.eq_ref        #使用join on时偶尔会出现

5.const         #查询条件是唯一索引或主键索引
    mysql> explain select * from city where id=1;

6.system        #查询级别与const一样，当数据很少时为该级别

7.null          #不需要读取数据，只需要获取最大值或者最小值
    mysql> explain select max(population) from city;

6. 索引的建立

1.）索引的建立原则

1.能创建唯一索引就创建唯一索引

2.为经常需要排序、分组和联合操作的字段建立索引

3.为常作为查询条件的字段建立索引
    如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。
    因此，为这样的字段建立索引，可以提高整个表的查询速度。

4.尽量使用前缀来索引
    如果索引字段的值很长，最好使用值的前缀来索引。
    例如，TEXT和BLOG类型的字段，进行全文检索，会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

5.限制索引的数目
    索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
    修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

6.删除不再使用或者很少使用的索引
    表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

2.）不走索引的情况

1）没有查询条件，或者查询条件没有索引
#没有查询条件
mysql> explain select * from city;

#查询条件没有索引
mysql> explain select District from city;

2）查询的结果占总数据的15%以上
#占总数据的18%，没走索引
mysql> explain select * from city where population > 400000;
#占总数据的15%，走了索引
mysql> explain select * from city where population > 450000;
#如果数据量查询就是表中大部分数据，可以用limit做限制
mysql> explain select * from city where population > 400000 limit 100;

3）索引损坏

4）查询条件带了特殊符号(+，-)
#在=号左侧有特殊符号，不走索引
mysql> explain select * from city where id-1=1;
#在=号右侧有特殊符号，走索引
mysql> explain select * from city where id=3-1;

5）隐式转换
查询语句级别全文扫描
mysql> explain select * from phonenum where phone=6666666;
当给字符加上引号，查询为索引扫描
mysql> explain select * from phonenum where phone='6666666';
"因为phonenum字段设置的是varchar类型，必须加上''符号字段类型才能走该字段的索引"

6）like "%_" 百分号在最前面不走

7）联合索引查询不按照顺序有可能不走索引

7.使用索引注意事项

1.如何查询一张表的所有索引？
- SHOW INDEX FROM T 查询表 T 所有索引。

2.MySQL 最多可以创建多少个索引列？
- MySQL 中最多可以创建 16 个索引列。

3.索引的常见存储算法有哪些？
- 哈希存储法：以 key、value 方式存储，把值存入数组中使用哈希值确认数据的位置，如果发生哈希冲突，使用链表存储数据；
- 有序数组存储法：按顺序存储，优点是可以使用二分法快速找到数据，缺点是更新效率，适合静态数据存储；
- 树：以树的方式进行存储，查询性能好，更新速度快。

4.MySQL 如何指定查询的索引？

在 MySQL 中可以使用 force index 强行选择一个索引，具体查询语句如下：
select * from t force index(index_t)

5.在 MySQL 中指定了查询索引，为什么没有生效？
- 我们知道在 MySQL 中使用 force index 可以指定查询的索引，但并不是一定会生效，原因是 MySQL 会根据优化器自己选择索引，如果 force index 指定的索引出现在候选索引上，这个时候 MySQL 不会在判断扫描的行数的多少直接使用指定的索引，如果没在候选索引中，即使 force index 指定了索引也是不会生效的。

6.如何让 like %abc 走索引查询？

我们知道如果要让 like 查询要走索引，查询字符不能以通配符（%）开始，如果要让 like %abc 也走索引，可以使用 REVERSE() 函数(颠倒排序)来创建一个函数索引，查询脚本如下：
select * from t where reverse(f) like reverse('%abc');