MySQL-索引（简版）

前置文章：
一、MySQL-Explain了解查询语句执行计划
二、MySQL-索引
如果对索引这块没有详细了解的话建议看前置文章二，如果只是想简单回顾以下就阅读本文。

零、本文纲要

一、索引分类
二、索引语法
三、SQL性能分析
四、索引使用
五、索引设计原则

tips：Ctrl+F快速定位到所需内容阅读吧。

一、索引分类

1、按类型分类

• 主键索引PRIMARY；
• 唯一索引UNIQUE；
• 常规索引；
• 全文索引FULLTEXT。

2、按存储形式

• 聚集索引Clustered Index；
• 二级索引Secondary Index（非聚集索引、辅助索引）；

二、索引语法

1、创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;

2、查看索引

SHOW INDEX FROM table_name ;

3、删除索引

DROP INDEX index_name ON table_name ;

三、SQL性能分析

1、SQL执行频率

show global status like 'com_______';

2、慢日志查询

show variables like "slow_query_log";

vim /etc/my.cnf
# 插入下方数据
# 1表示开启，0表示关闭
slow_query_log=1
# 慢查询的设定时间10s，可以根据实际需求调整
long_query_time=10

cat /var/lib/mysql/localhost-slow.log

3、profile

SELECT @@profiling;
SET profiling = 1;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
SHOW PROFILE CPU FOR QUERY 2;

4、Explain执行计划

type：性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all。

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

四、索引使用

1、最左前缀法则

最左前缀法则指的是查询从联合索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

2、范围查询

使用了(>,<)范围查询，导致该索引后面的索引失效了，使用(>=,<=)替换。

3、索引失效场景

• ① 索引列运算
  索引列上有计算（+-*/）或者使用函数（sum、avg等），导致索引失效。
• ② 字符串不加引号
  如：123与"123"；
• ③ 模糊查询
  在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字前面加了%，索引将会失效。%关键字，失效；关键字%，生效。
• ④ or条件连接
  用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
• ⑤ 数据分布影响
  如果走全表扫描更快，则放弃索引，走全表扫描。

4、SQL提示

• ① use index
  建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）；
• ② ignore index
  忽略指定的索引；
• ③ force index
  强制使用索引。

5、覆盖索引

查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

6、前缀索引

• ① 语法：create index idx_xxxx on table_name(column(n)) ;
• ② 全列选择性：SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
• ③ 某一长度前缀选择性：SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

7、单列索引与联合索引

如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

五、索引设计原则

• 1、针对于数据量较大，且查询比较频繁的表建立索引；
• 2、针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引；
• 3、尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高；
• 4、如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引；
• 5、尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率；
• 6、要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率；
• 7、如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

六、结尾

以上即为MySQL索引（简版）的部分内容，感谢阅读。