博客

如何更好的利用索引来优化查询？索引优化；mysql索引

首先要明确目标-为什么要建立索引？

一般情况下自己做的小项目基本用不到索引，顶多为主键字段加一个唯一性索引。

需要建立索引时一般遵循：

1. 数据量大于100万且频繁查询则需要考虑建立索引。

2. 频繁使用条件查询（where，order by，group by…）的字段需要建立索引。

3. 为主键或唯一性约束建立唯一性索引，使索引的效率提高。

4. 对于字符串类型的字段，若数据过长，应考虑建立前缀索引。

5. 在空间允许的情况下，尽量建立联合索引，减少回表查询，提高效率。

6. 只建立刚好满足需求的索引，不多建立新索引。

7. 为避免索引失效，建立索引时需要对存null的字段考虑使用not null约束。

如何分析查询语句，找到慢查询？

假设你现在被分配到这样的工作，你需要优化慢查询使你们的项目的功能变的更强，你大概需要这么做：

查看select，update，delete，insert…的频率

show global status like 'Com_______';//七个下划线

​ 虽然一般情况下我们都知道要优化select，以防万一，随便看看其他语句的频率。

查看慢查询日志

有可能默认不开启慢查询日志，所以要先查看一下有没有开。

show variables like '%query%';

我这里是开着的，如果没开就执行下面的sql语句

set global slow_query_log='ON';

指定日志的输出格式

//MySQL支持TABLE和FILE两种输出格式，可以用下面的命令来查看当前的输出格式：
show variables like '%log_output%';

//设置输出格式：
set global log_output='FILE';
set global log_output='TABLE';
set global log_output='FILE,TABLE';

（如果你是Linux，则执行对应如下操作）

分析查询耗时与查询细节

我们可以使用profile来分析查询耗时

select @@have_profiling;//查看是否开启profilng
set profiling = 1;//开启profling

//查询基本耗时情况
show profiles;

//查询某个query_id的耗时情况
show profile for query query_id;

//查询某个query_id的cpu的使用情况
show profile cpu for query query_id;

知道了某条耗时很久的查询语句后，就可以在最左边加上explain关键字来分析这条sql语句的执行细节

//例如
explain select * from tb_user where id=1;

很多时候慢查询的产生都是因为没有用到索引的缘故，所以一般只需要注意possibie_keys，key，key_len，extra，filitered

建立索引

经过上述一系列操作，相信你已经知道应该要为哪个字段建立索引了。

不过还不能掉以轻心，因为有可能遇到”索引失效“

索引失效情况

1. 你的sql语句在有索引的字段上做了运算操作

​ 比如你使用了substring函数

2. 对有索引的字符串类型的字段在查询时不加引号（‘’）

3. 模糊匹配通配符’%’放在开头

​ 放在末尾是不影响索引的

4. or左右两边不全为有索引的字段

5. 不遵守最左前缀法则

​ 这个不好演示，只需要记住如果有联合索引，那么查询语句要从有索引的字段的最左边开始，不跳过联合索引中的任何一列。否则将部分失效。（被跳过的字段后面索引失效）

如何创建索引

噢终于来到这一步，如果你第一次学习索引，可以先看看这个。

如果你能理解什么是”回表查询“那就跳过这里

更好地使用索引

虽然开头已经概括了，那就在这里展开一下

1. 尽量减少回表查询

   可以用explain分析查询语句后，查看extra字段的信息。

   若为：using index condition 则代表此查询是回表查询

   若为：using where;using index 则代表此次查询不是回表查询（效率更高）

2. 针对频繁查询字符串类型的字段建立“前缀索引”

​ 如果建立普通索引，数据量一多就会占用巨量的空间，难以维护，并且浪费大量磁盘IO。

​ 所以应该只对其中一部分字符串建立索引，大大节省空间，提升效率。

create index idx_xxx on table_name(column_name(n));

​ 根据索引的选择性与表的记录总数的比值来判断需要以多长的字符串建立索引，越接近1越好。

select count(distinct column_name) / count(*) from table_name;
select count(distinct substring(column_name,1,5)) / count(*) from table_name;

其他

本文所有图片均来自黑马程序员的mysql教学ppt，我只概括了对select优化。

黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括l