MySql索引优化及Explain命令

导读：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换。MEMORY/HEAP存储引擎：支持HASH和BTREE索引。

索引分类1：

一、聚集索引

定义：数据行的物理顺序与列值（一般是主键的那一列）的 逻辑顺序相同，一个表中只能拥有一个聚集索引。SQL Sever默认主键为聚集索引，也可以指定为非聚集索引，而MySQL里主键就是聚集索引

二、非聚集索引

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据；不管以任何方式查询表， 最终都会利用主键通过聚集索引来定位到数据， 聚集索引(主键)是通往真实数据所在的唯一路径。然而， 有一种例外可以不使用聚集索引就能查询出所需要的数据， 这种非主流的方法 称之为「覆盖索引」查询， 也就是平时所说的复合索引或者多字段索引查询。

聚集索引和非聚集索引的区别底层_一篇读懂聚集索引、非聚集索引、覆盖索引的工作原理..._weixin_39843738的博客-CSDN博客

理解：聚集索引一般就是主键，非聚集索引就是其他的索引。

索引分类2：

Mysql的索引我们分为三大类：

1、单列索引(普通索引，唯一索引，主键索引)

单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了

1：普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。

2：唯一索引：索引列中的值必须是唯一的，但是允许为空值。

3：主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。

主键索引与唯一索引的区别：

  1. 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

  2. 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

  3. 唯一性索引列允许空值，而主键列不允许为空值。

  4. 主键索引在创建时，已经默认为非空值+ 唯一索引了。

  5. 一个表最多只能创建一个主键索引，但可以创建多个唯一索引。

  6. 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

  7. 主键可以被其他表引用为外键，而唯一索引不能。
 

2、组合索引

最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

a，ab，abc      用到索引

bc， b， c        没有从最左边开始

ac                     不连续，如果不连续时，只用到了a列的索引，b列和c列都没有用到 

复合索引的索引体积比单独索引的体积要小，而且只是一个索引树，相比单独列的索引要更加的节省时间复杂度和空间复杂度。

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询。
Mysql最左匹配原则_Summersadness8的博客-CSDN博客_mysql最左原则

3、全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 ..." 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。一般开发中，不贵用到全文索引，因为其占用很大的物理空间和降低了记录修改性，故较为少用。
 

创建索引的语句

1：直接创建索引：

CREATE INDEX index_name ON table(column(length));  创建普通索引
CREATE UNIQUE INDEX indexName ON table(column(length)); 创建唯一索引
CREATE FULLTEXT INDEX index_content ON article(content); 全文索引

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

ALTER TABLE table_name ADD INDEX index_name ON (column(length));  创建普通索引
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length)); 创建唯一索引
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);  组合索引
ALTER TABLE article ADD FULLTEXT index_content(content); 全文索引

3：创建表的时候创建索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
);  普通索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);  唯一索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);  主键索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);  全文索引

4：删除索引：

DROP INDEX index_name ON table;

5：查看索引：

show index from table_name;

Explain查看SQL执行计划

 在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，查看该SQL语句有没有使用上了索引，有没有做全表扫描。

所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，运行SQL语句时哪种策略预计会被优化器采用，分析查询语句或者表结构的性能瓶颈。

作用

1、表的读取顺序
2、数据读取操作的操作类型
3、哪些索引可以使用
4、哪些索引被实际使用
5、表之间的引用
6、每张表有多少行被优化器查询

Explain 用法

使用方法：explain + sql 语句。

-- 实际SQL，查找id > 1123598815738675259 的名字
select name from blade_menu where id > 1123598815738675259;
-- 查看SQL是否使用索引，前面加上Explain即可
Explain select name from blade_meun where id > 1123598815738675259;

包含的字段如下:

SQL执行顺序

select name
from tb_user 
where id > 12
group by sex
having max(salary) > 100
order by salary desc

extended关键字

extended关键字：仅对select语句有效，在Explain后使用extended关键字，可以显示filtered列显示了通过条件过滤出的行数的百分比估计值。

filtered列给出了一个百分比的值，这个百分比值和rows列的值一起使用，可以估计出那些将要和explain中的前一个表进行连接的行的数目。前一个表就是指explain 的 id列的值比当前表的id小的表。

SQL优化经验

1、索引查询相关：

0.不要使用select *，

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
或
select id from t where num=10
union all
select id from t where num=20

参考：https://www.cnblogs.com/xiangxinhouse/p/6053134.html

4.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

5.%在前的模糊查询 也将导致全表扫描：like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
select id from t where name like '%abc%'。

6.尽量避免在where条件中，不要在列上进行运算，对字段进行函数操作和加减乘除等表达式操作，
将导致索引失效而进行全表扫描

7.COUNT() 函数返回匹配指定条件的行数

7.1 count(*)和count(1)有什么区别
count(1)，其实就是计算一共有多少符合条件的行。
1并不是表示第一个字段，而是表示一个固定值。
其实就可以想成表中有这么一个字段，这个字段就是固定值1，count(1)，就是计算一共有多少个1.
同理，count(2)，也可以，得到的值完全一样，count('x')，count('y')都是可以的。一样的理解方式。在你这个语句理都可以使用，返回的值完全是一样的。就是计数。
count(*)，执行时会把星号翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些。

7.2 count(*) 和 count(1)和count(列名)区别 执行效果上：
count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

7.3 执行效率上：

列名为主键，count(列名)会比count(1)快

列名不为主键，count(1)会比count(列名)快

如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）

如果有主键，则 select count（主键）的执行效率是最优的

如果表只有一个字段，则 select count（*）最优。

2、索引优化：

1、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 、delete、update 的效率

2、索引不会包含有NULL值的列：只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

4、MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

6、MySQL只针对以下的操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的

7、Explain

优质博客：

图解｜索引覆盖、索引下推以及如何避免索引失效 - 知乎