前置文章：
一、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索引（简版）的部分内容，感谢阅读。
网友评论