1.索引的分类
在
MySQL
数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。
|
分类
|
含义
|
特点
|
关键字
|
|
主键
索引
|
针对于表中主键创建的索引
|
默认自动创建
,
只能
有一个
|
PRIMARY
|
|
唯一
索引
|
避免同一个表中某数据列中的值重复
|
可以有多个
|
UNIQUE
|
|
常规
索引
|
快速定位特定数据
|
可以有多个
| |
|
全文
索引
|
全文索引查找的是文本中的关键词，而不是比 较索引中的值
|
可以有多个
|
FULLTEXT
|
而在在
InnoDB
存储引擎中，根据索引的存储形式，又可以分为以下两种：
聚集索引
二级索引
回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取
数据的方式，就称之为回表查询。
2.索引语法
1.创建索引 create 索引类型 index 索引名 on table_name(字段名);
2.查看索引 show index from table_name;
3.删除索引 drop index 索引名 on table_name;
4.创建联合索引
create 索引类型 index 索引名 on table_name(字段名1,字段名2,字段名3...);
3.SQL性能分析工具
1 SQL
执行频率
MySQL
客户端连接成功后，通过
show [session|global] status
命令可以提供服务器状态信
息。通过如下指令，可以查看当前数据库的
INSERT
、
UPDATE
、
DELETE
、
SELECT
的访问频次：
-- session
是查看当前会话
;
-- global
是查询全局数据
;
SHOW GLOBAL STATUS
LIKE
'Com_______'
;
2
慢查询日志
慢查询日志记录了所有执行时间超过指定参数（
long_query_time
，单位：秒，默认
10
秒）的所有
SQL
语句的日志。
3 profile
详情
show profiles
能够在做
SQL
优化时帮助我们了解时间都耗费到哪里去了。
4 explain
EXPLAIN
或者
DESC
命令获取
MySQL
如何执行
SELECT
语句的信息，包括在
SELECT
语句执行过程中表如何连接和连接的顺序。
Explain
执行计划中各个字段的含义
:
|
字段
|
含义
|
|
id
|
select
查询的序列号，表示查询中执行
select
子句或者是操作表的顺序 (id相同，执行顺序从上到下；
id
不同，值越大，越先执行
)
。
|
|
select_type
|
表示
SELECT
的类型，常见的取值有
SIMPLE
（简单表，即不使用表连接 或者子查询）、PRIMARY
（主查询，即外层的查询）、 UNIOn（
UNIOn
中的第二个或者后面的查询语句）、
SUBQUERY
（
SELECT/WHERe
之后包含了子查询）等
|
|
type
|
表示连接类型，性能由好到差的连接类型为
NULL
、
system
、
const
、eq_ref、
ref
、
range
、
index
、
all
。
|
|
possible_key
|
显示可能应用在这张表上的索引，一个或多个
|
|
key
|
实际使用的索引，如果为
NULL
，则没有使用索引
|
|
key_len
|
表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好
|
|
rows
|
MySQL
认为必须要执行查询的行数，在
innodb
引擎的表中，是一个估计值， 可能并不总是准确的。
|
|
filtered
|
表示返回结果的行数占需读取行数的百分比，
filtered
的值越大越好。
|
4.索引失效情况
1.最左前缀法则
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始， 并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(
后面的字段索引失效
)
。
注意 ： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是
第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。
2.范围查询
联合索引中，出现范围查询
(>,<)
，范围查询右侧的列索引失效。
在业务允许的情况下，尽可能的使用类似于
>=
或
<=
这类的范围查询
3.索引列运算
不要在索引列上进行运算操作， 索引将失效。
4.
字符串不加引号
字符串类型字段使用时，不加引号，
数
据库存在隐式类型转换，
索引将失效。
5.
模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
6.
or
连接条件
用
or
分割开的条件， 如果
or
前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
7.数据分布影响
如果
MySQL
评估使用索引比全表更慢，则不使用索引。
5.SQL提示
SQL
提示，是优化数据库的一个重要手段，简单来说，就是在
SQL
语句中加入一些人为的提示来达到优化操作的目的。
1.use index
建议MySQL
使用哪一个索引完成此次查询（仅仅是建议，
mysql
内部还会再次进
行评估）。
explain
select
*
from
tb_user use index(idx_user_pro)
where
profession =
'
软件工
程
'
;
2.ignore index
忽略指定的索引。
explain
select
*
from
tb_user ignore index(idx_user_pro)
where
profession =
'
软件工
程
'
;
3.force index
强制使用索引。
explain
select
*
from
tb_user force index(idx_user_pro)
where
profession =
'
软件工
程
'
;
6.覆盖索引
尽量使用覆盖索引，减少
select *
。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并
且需要返回的列，在该索引中已经全部能够找到 。
SQL执行计划最后一个字段Extra
|
Extra
|
含义
|
|
Using where; Using Index
|
查找使用了索引，但是需要的数据都在索引列中能找到，所以不需 要回表查询数据
|
|
Using index
condition
|
查找使用了索引，但是需要回表查询数据
|
7.前缀索引
当字段类型为字符串（
varchar
，
text
，
longtext
等）时，有时候需要索引很长的字符串，这会让
索引变得很大，查询时，浪费大量的磁盘
IO
， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
语法
create
index idx_xxxx
on
table_name(column(n)) ;
8.单列索引与联合索引
单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，
而非单列索引。
9.索引设计原则
1.针对于数据量较大，且查询比较频繁的表建立索引。
2.针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索 引。
3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4.如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间， 避免回表，提高查询效率。
6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增 删改的效率。
7.如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
