3、MySQL索引

3.4索引语法

3.4.1 创建索引

CREATE [ UNIQUE |FULLTEXT ] INDEX index_name ON table_name ( index_col_name,.. ) ;

3.4.2 查看索引

SHOW INDEX FROM table_name ;

3.4.3 删除索引

DROP INDEX index_name O table_name ;

3.5SQL性能分析

3.5.1 查看表语句使用频率

-- 查看表的使用频率
SHOW GLOBAL STATUS LIKE 'Com______';

3.5.2 慢日志查询

慢日志查询记录了所有执行时间超过指定参数(long_query_time,单位秒，默认十秒)的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要进行配置文件(/etc/my.cnf)中配置如下信息。

# 开启MySQL慢查询日志开关
show_query_log = 1
# 设置慢日志的查询时间为2秒,SQL执行时间超过2秒，就会被视为慢查询，记录慢查询日志
long_query_time = 2

配置完成之后需要重启MySQL，查看慢日志文件中的记录信息/var/lib/mysql/localhost-slow.log中。

# 查看慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log';

3.5.3 profile 详情

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作。

-- 查看是否支持profile操作
SELECT @@have_profiling ;
-- 开启profile操作
SET profiling = 1；
-- 查询每条SQL的耗时基本情况
SHOW PROFILES;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
SHOW PROFILE FOR QUERY quert_id;
-- 查看指定query_id的cpu的使用情况
SHOW PROFILE CPU FOR QUERY quert_id;

3.5.4 explain 执行计划

3.5.4.1 explain 基础语法

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。语法如下：

# 直接在select语句之前加上关键字explain / desc
EXPLAIN SELECT  字段列表 FROM 表名 WHERE 条件;

3.5.4.2 explain 查询字段解释

1. id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)。
2. select_type： 表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询)、PRIMARY（主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询)等。
3. type：表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index。
4. possible_key：显示可能应用在这张表上的索引，一个或多个。
5. Key：实际使用的索引，如果为NULL，则没有使用索引。
6. Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
7. rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
8. filtered：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

3.6索引使用

3.6.1 最左前缀法则

如果联合索引涉及了多列，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不能跳过索引中的列。如果跳过某一列,索引将部分失效(后面字段的索引失效)。

样例SQL如下：

explain select * from tb_user where profession= '软件工程' and age = 31 and status = '0';
explain select * from tb_user where profession= '软件工程' and age = 31;
explain select * from tb_user where profession= '软件工程";
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';

3.6.2 索引范围查询

在使用联合索引，出现范围查询 > 大于，< 小于的时候，范围查询右侧的列索引失效。

# 出现 age > 30 右侧的索引失效,只有profession生效
explain select *from tb_user where profession= "软件工程' and age > 30 and status = '0';
#  age >= 30 则是整个联合索引都是生效的
explain select * from tb_user where profession= "软件工程' and age >= 30 and status = '0';

3.6.3 索引列运算

在索引生效的列上做运算操作，索引将会失效。

# phone字段进行了运算操作所以索引失效了
explain select * from tb_user where substring(phone,10,2)= '75';

3.6.4 索引隐式数据类型转换

字符串类型数据不加单引号导致数字类型转字符串类型发生了数据类型的隐式转换，导致索引将会失效。

# 此时两个条件的索引都会失效，因为发生int隐式转换varchar
explain select * from tb_user where profession= 软件工程' and age = 31 and status =0;
explain select * from tb_user where phone = 17799990015;

3.6.5 索引模糊查询

like进行模糊匹配的时候当尾部出现通配符则索引不会失效,但是前面出现通配符索引就会失效。

-- 索引不会失效
explain select * from tb_user where profession like '软件%';
-- 索引失效因为like头部出现了通配符
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%N;

3.6.6 索引or连接的条件

用or分割开的条件，如果or前的条件中的列有索引而后面的列没有索引那么涉及的索引都不会被用到。如果想要索引生效则需要对or前后的都需要进行加索引。

-- 由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。
explain select * from tb_user where id= 10 or age = 23;
explain select *from tb_user where phone = '17799990017' or age = 23;

3.6.7 数据分布影响

数据分布影响的意思就是MySQL底层会自动选取查询最快的方式，当MySQL认为不用索引比较快时，尽管存在索引也不会使用。

#  因为大于这个号码的几乎时整张表，尽管phone上存在索引但是并没有被使用
select * from tb_user where phone >='17799990005';
select * from tb_user where phone >='17799990015';

那么我们非要告诉MySQL使用索引该怎么处理，可以使用以下关键字，use index 建议使用索引，ignore index 忽略使用索引 ，force index 强制使用索引。

3.6.8 覆盖索引

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

explain select id, profession from tb_user where profession= '软件工程' and age=31 and status = '0';
explain select id,profession,age, status from tb_user where profession="软件工程’ and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession= "软件工程'’ and age = 31 and status = '0';
explain select * from tb_user where profession= '软件工程' and age = 31 and status = '0' ;

3.6.9 前缀索引

这个索引适合于大的文本，抽取前几个字形成前缀索引降低IO。当字段类型为字符串(varchar , text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘lO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

-- 前缀索引语法 n 代表取前面多少个字符
create index idx_xxxx on table_name(column(n));

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

-- 计算前缀索引 n 的长度
select count(distnct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;

前缀索引使用匹配方式如下图：

3.6.10 单列索引和联合索引

单列索引:即一个索引只包含单个列。联合索引:即一个索引包含了多个列。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

单列索引情况，如下:

explain select id, phone, name from tb_user where phone = '17799990010' and name = '韩信';

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

3.7索引设计原则

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