MySql:索引-SQL性能分析,索引使用,索引设计原则

SQL性能分析

性能分析

MySql客户端链接成功后，通过show [session|global] status 命令可以提供服务器状态信息. 通过如下指令，可以查看当前数据库的 insert uodate delete select 的访问频次

show global status like 'Com_______';

慢查询日志

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

--查询慢查询日志的开关
show variables like ‘slow_query_log’;

--开启MySQL慢日志的查询开关
slow_query_log=1

--设置慢查询日志的时间2秒，SQL语句的执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=3

配置完毕之后，通过一下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log

show profiles

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

 

select @@have_profiling;

--默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：
set profiling=1;

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时

--查看每一条SQL的耗时基本情况
show profiles;

--查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

--查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

 explain执行计划

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

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

索引的使用

验证索引的效率

有一个1000w数据的表，

表中id为主键，有主键索引，执行select * from tb_sku where id = 1; 完成时间很快.

表中有sn，没有sn的索引，执行SELECT * FROM tb_sku WHERE sn = '100000003145001'; 耗时20秒.

创建sn的索引create index idx_sku_sn on tb_sku(sn) ; 耗时1分钟多，因为再构建b+tree

再次执行SELECT * FROM tb_sku WHERE sn = '100000003145001';耗时0.1s

最左前缀法则

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

比如下面这个联合索引

在 tb_user 表中，有一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession，age，status。

对于最左前缀法则指的是，查询时，最左变的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。 接下来，我们来演示几组案例，看一下具体的执行计划：

explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
--索引全部生效

explain select * from tb_user where profession = '软件工程' and status = '0';
--索引age ，status会失效，因为越过了age这一列，age包括后面的字段索引都会失效

explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程'；
--联合索引生效，与编写的先后顺序没有关系，只要存在就可以

 范围查询

联合索引中，出现范围查询（>,<）,范围查询右侧的列索引失效.

下图知：age使用了>  ,status 就失效了，如何避免右侧列索引失效呢？

索引失效的情况

1.索引列运算

不要在索引列上进行运算操作，索引将失效.

explain select * from tb_user where substring(phone,10,2) = '15';

 2.字符串不加引号

字符串类型字段使用时，不加引号，索引将失效

varchar类型的phone

explain select * from tb_user where phone = 18888888888;

 3.查询模糊

如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效.

select  * from tb_user where profession like '软件%';  --索引不失效

select  * from tb_user where profession like ‘%工程';  --索引失效

 4.or链接的条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到.

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

 5. 数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的.

比如 一个字段 a 在某一个联合索引中，再给a创建一个普通索引，那么一个SQL语句的条件是 where a= 1;

使用普通索引还是联合索引？默认MySQL会字段选择效率更高的索引.

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 = '软件工程';

 覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *

一个表中有一个联合索引，包含的字段是profession，age，status

• using where;using index  查找使用了索引，但是需要的数据都在索引列中能找到，索引不需要回表查询数据
• using index condition：查找使用了索引，但是需要回表查询数据

当查询返回的数据在 id、profession、age、status 之中，则直接走二级索引直接返回数据了。 如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表

前缀索引

当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘io，影响查询效率，此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率.

语法：create index idx_xxxx on table_name(column(n));

实例：为tb_user表的email字段，建立长度为5的前缀索引 create index idx_email_5 on tb_user(email(5));

前缀长度

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

select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;   //查询email中前5个值的索引选择性

前缀索引的查询流程

单列索引与联合索引

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

如果一个表中包含两个字段，phone和name，且有这两个字段的单列索引

那么执行select id,phone,name from user where phone='123' and name='111'; mysql只会选择一个索引，也就是说，只能走一个字段的索引，此时是会回表查询,所以建议建立phone和name的联合索引 user_ph_na  使用：select id,phone,name from user use index(user_ph_na) where phone='123' and name='111';

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

索引的设计原则

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