mysql索引的总结整理

发布于 作者: — 暂无评论 ↓

mysql索引一般用于庞大数据的查询上，当一个用户数据表有一千万的时候，可以把它比作一个一千万字的字典，当没有索引的时候，你需要从第一页一个一个对照拿出符合条件的用户信息，当有索引的情况下，相当于你只需要查偏旁笔画等查询在第几页即可，在查询速度上有质的不同，下面就是我学习索引时总结整理，希望这篇文章可以给大家带来帮助！

索引类型

MySQL目前主要有的索引类型为：普通索引、唯一索引、主键索引、组合索引、全文索引

1、普通索引(如用户昵称)

普通索引是最基本的索引，它没有任何限制，值可以为空；仅加速查询。可以通过以下几种方式来创建或删除：

1）、直接创建索引

CREATE INDEX index_name ON table(column(length))

2）、修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

3）、删除索引

DROP INDEX index_name ON table

2、唯一索引（如用户专属用户名、邀请码）

唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。简单来说：唯一索引是加速查询 + 列值唯一（可以有null）。以通过以下几种方式来创建：

1）、创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

2）、修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

3、主键索引(一般是用户表id)

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。简单来说：主键索引是加速查询 + 列值唯一（不可以有null）+ 表中只有一个。

一般是在建表的时候同时创建主键索引：

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

4、组合索引(如用户与用户基本信息表组合查询)

组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则。

最左前缀原则
mysql建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：

如果有一个2列的组合索引(col1,col2),则包含索引(col1)、 组合索引 (col1,col2)；
如果有一个3列组合索引(col1,col2,col3)， 则包含索引(col1)、 组合索引 (col1,col2)、 组合索引 (col1,col2,col3)；

关于最左前缀的使用，有下面两条说明：

最左前缀匹配原则，非常重要的原则，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的顺序可以任意调整。

=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立（a，b，c）索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。

可以说：组合索引是多列值组成的一个索引，专门用于组合搜索，其效率大于索引合并。

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

5、全文索引(如用户简介，文章内容等)

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。了解详细看这篇文章mysql中文全文搜索总结

索引失效

1.最佳左前缀

MySQL 可以为多个字段创建索引，一个索引可以包括 16 个字段。对于多列索引，过滤条件要使用索引，必须按照索引建立的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第 1 个字段时，多列（或联合）索引不会被使用。

2.主键插入顺序

这个一般表一定要规定一个自增长id，这样自增长插入id一般不存在这个问题

3.计算、函数、类型转换(自动或手动)导致索引失效

这个提前计算好将计算好的结果复制给放入，不要直接写在sql语句上

4.范围条件右边的列索引失效

应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。应将查询条件放置 WHERE 语句最后。（创建的联合索引中，务必把范围涉及到的字段写在最后）

5.不等于(!= 或者<>)导致索引失效

6. is null 可以使用索引，is not null 无法使用索引

最好在设计数据表的时候就将字段设置为 NOT NULL 约束，比如可以将 INT 类型的字段，默认值设置为 0。将字符类型的默认值设置为空字符串 ” 。同理，在查询中使用 not like 也无法使用索引，导致全表扫描

7. like 以通配符 % 开头索引失效

Alibaba《Java开发手册》，强制要求：页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决

8.  OR 前后存在非索引的列，索引失效

9. 数据表和表的字符集统一使用 utf8mb4

统一使用 utf8mb4 （5.5.3版本以上支持）兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

10.当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效

判断索引是否生效(好习惯每个sql测试一下索引是否有效)

explain函数验证索引是否有效

第一步：使用列表name查询验证索引。

1、使用索引列时索引才会生效，语句如下：

explain select * from p_user WHERE name=’B’

2、不使用索引查询：

explain select * from p_user 

第二步：失效的索引。

1、使用语句：

explain select * from p_user WHERE name != ‘A’

第三步：复合场景。

1、使用语句：

explain select * from p_user WHERE name=’B’ AND name != ‘A’

explain函数介绍

explain显示了MySQL如何使用索引来处理select语句以及连接表。他可以帮助选择更好的索引和写出更优化的查询语句

explain显示了很多列，各个关键字的含义如下：

  1. table：顾名思义，显示这一行的数据是关于哪张表的；
  2. type：这是重要的列，显示连接使用了何种类型。type结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。
  3. possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句；
  4. key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在Select语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MySQL忽略索引； 
  5. key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好；
  6. ref：显示索引的哪一列被使用了，如果可能的话，是一个常数；
  7. rows：MySQL认为必须检查的用来返回请求数据的行数；
  8. Extra：关于MySQL如何解析查询的额外信息。
  9. 具体的各个列所能表示的值以及含义可以参考MySQL官方文档介绍，地址：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

0 评论
内联反馈
查看所有评论