Mysql索引总结，通熟易懂

最新推荐文章于 2024-09-02 17:25:38 发布

幽梦萧瑟

最新推荐文章于 2024-09-02 17:25:38 发布

阅读量308

点赞数 2

分类专栏： Mysql 文章标签： Mysql索引索引优化索引技巧

本文链接：https://blog.csdn.net/mengyangcust/article/details/84842120

版权

Mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1.索引是什么？类似目录

不知道大家平时看书是怎么看的，从头开始，还是从中间开始，或者只是翻翻，个人看书是从前面的索引目录开始的，看完索引目录对整本书要介绍啥就一清二楚了，先定位到章，然后定位到该章下的一个小节，通过这个索引很快就可以找到感兴趣章节所在的页码，根据页码翻到对应的内容，数据库的索引也是这个东东，也叫做“键”，是一种数据结构，形成一棵b+树，具体看下面这张图
在这里插入图片描述

2.索引有什么作用？加速查找

在索引列上，数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

3.MySQL索引的类型

普通索引（INDEX :加速查找）
唯一索引（UNIQUE）
-主键索引：primary key ：加速查找+约束（不为空且唯一）
-唯一索引：unique：加速查找+约束（唯一）
3.联合索引
-primary key(id,name):联合主键索引
-unique(id,name):联合唯一索引
-index(id,name):联合普通索引
全文索引（FULLTEXT）
用于搜索很长一篇文章的时候，效果最好。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
空间索引SPATIAL :了解就好，几乎不用

4.创建/删除索引的语法

方法一：创建表时

　　CREATE TABLE 表名 (
            字段名1  数据类型 [完整性约束条件…],
            字段名2  数据类型 [完整性约束条件…],
            [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
            [索引名]  (字段名[(长度)]  [ASC |DESC]) 
            );

方法二：CREATE在已存在的表上创建索引

    CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                 ON 表名 (字段名[(长度)]  [ASC |DESC]) ;

方法三：ALTER TABLE在已存在的表上创建索引

    ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                         索引名 (字段名[(长度)]  [ASC |DESC]) ;

删除索引：DROP INDEX 索引名 ON 表名字;

查看索引：SHOW INDEX FROM 表名字;

5.创建索引的技巧

（1）维度高的列创建索引，公式是count(distinct col)/count(*)

数据列中不重复值出现的个数,这个数量越高,维度就越高
如数据表中存在8行数据a ,b ,c,d,a,b,c,d这个表的维度为4
要为维度高的列创建索引,如性别和年龄,那年龄的维度就高于性别
性别这样的列不适合创建索引,因为维度过低

（2）最左前缀匹配

create index ix_name_email on s1(name,email,)
最左前缀匹配：必须按照从左到右的顺序匹配
select * from s1 where name=‘egon’; #可以
select * from s1 where name=‘egon’ and email=‘asdf’; #可以
select * from s1 where email=‘alex@oldboy.com’; #不可以
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，
d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

（3）为较长的字符串使用前缀索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
另外在MySQL中为表建立索引时经常会遇到“Error : Specified key was too long; max key length is 767 bytes”和“Error : Duplicate key name ind_composite_3072”错误，就是因为字符串长度过长引起的。
如果还有疑问，那就参考一下这个漫谈MySQL索引与字段儿长度的关系

（4）索引列不要在进行运算

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。

（5）like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。

6.Explain优化查询检测

不知道查询慢的原因，那就从Explain开始：

mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2";

–结果：
id: 1

select_type: SIMPLE – 查询类型（简单查询,联合查询,子查询）

table: user – 显示这一行的数据是关于哪张表的

type: range – 区间索引（在小于1990/2/2区间的数据),这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表扫描了全表才确定结果。一般来说,得保证查询至少达到range级别,最好能达到ref。

possible_keys: birthday – 指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。

key: birthday – 实际使用到的索引。如果为NULL,则没有使用索引。如果为primary的话,表示使用了主键。

key_len: 4 – 最长的索引宽度。如果键是NULL,长度就是NULL。在不损失精确性的情况下,长度越短越好

ref: const – 显示哪个字段或常数与key一起被使用。

rows: 1 – 这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。

Extra: Using where; Using index – 执行状态说明,这里可以看到的坏的例子是Using temporary和Using

通过Explain基本上能排查出问题，还是不够的话要了解业务方使用场景，以及对照上面提到的几个技巧和优化一下SQL语句，应该能搞定。