MySQL 学习笔记 - 索引 - 红烧猎人的博客

索引是帮助mysql高效获取数据的排好序的数据结构

1.索引数据结构

二叉树
红黑树
Hash表
B-Tree

2.索引分类

主键索引、唯一索引、常规索引、全文索引聚集索引、二级索引

3.语法

查看数据表的索引

show index from TB_NAME

创建索引

create index [索引名称] on [数据表] ([字段名…])

create index idx_sku_sn on tb_sku(sn)

删除索引

DROP INDEX <索引名> ON <表名>

DROP INDEX idx_sku_sn ON tb_sku

4.性能分析

执行频次、慢查询日志、profile、explain

5.索引使用规则

联合索引

最左前缀法则
如果索引了多列，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不挑锅索引中的列。如果跳跃某一列部分失效（后的索引失效）。与查询顺序无关。
范围查询
不要使用“>”或<”（范围查询右侧的列索引将失效），可采用“>=”或“<=”。

索引列运算

不要再索引列进行运算操作，否则将失效。

字符串不加引号

查询时字符串不加单引号（一般为书写不规范），索引失效。

模糊查询

如果仅仅是尾部模糊模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

or 连接的条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

例子：
id有索引，age没有索引


# 不会有索引
explain select * from tb_user where id =10 or age = 23;
# 不会有索引
explain select * from tb_user where phone ='17799990017' or age = 23;

数据分布索引

如果MySQL评估使用索引比全表更慢，则不使用索引。（绝大部分数据都满足查询条件，将使用全表扫描）

SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

# use index 使用索引:
explain select * from tb_user use index(idx_user_pro) where profession= '软件工程';
# ignore index 忽略索引:
explain select * from tb_user ignore index(idx_user_pro) where profession= "软件工程";
# force index 必须使用索引:
explain select *from tb_user force index(idx_user_pro) where pr
ofession= '软件工程;

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select *。

前缀索引

当字段类型为字符串(varchar , text等》时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘O，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法： create index idx_xxXx on table_name(column(n));

create index idx_email_5 on tb_user (email(5));

前缀长度：
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;

6.索引设计原则

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

MySQL 学习笔记 - 索引