04-Mysql索引优化和查询优化

Mysql索引优化和查询优化

索引建立的规则

• 表的主键、外键必须有索引；

• 数据量超过300的表应该有索引；

• 经常与其他表进行连接的表，在连接字段上应该建立索引；

• 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

• 索引应该建在选择性高的字段上；

• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

• 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

  • 正确选择复合索引中的主列字段，一般是选择性较好的字段；
  • 复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
  • 如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
  • 如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
  • 如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

• 频繁进行数据操作的表，不要建立太多的索引；

• 删除无用的索引，避免对执行计划造成负面影响；

• 对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

索引优化（正确利用索引）

独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

例如下面的查询不能使用 actor_id 列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

多列索引(联合索引)

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

索引列的顺序

让选择性最强的索引列放在前面。

索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;
   staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
               COUNT(*): 16049

前缀索引

对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。

前缀长度的选取需要根据索引选择性来确定。

前缀索引说白了就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快。有点相当于Oracle中对字段使用Left函数，建立函数索引，只不过MySQL的这个前缀索引在查询时是内部自动完成匹配的，并不需要使用left函数。

MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

建立前缀索引的语法为：

覆盖索引

索引包含所有需要查询的字段的值（所查询的字段从索引中就能够取得，不需要回表）。

具有以下优点：

• 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
• 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
• 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

索引的优点

• 大大减少了服务器需要扫描的数据行数。
• 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

查询性能优化

使用 Explain 进行分析

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。

字段有：

柱	JSON名称	含义
id	select_id	该SELECT标识符
select_type		该SELECT类型
table	table_name	输出行表
partitions	partitions	匹配的分区
type	access_type	联接类型
possible_keys	possible_keys	可能的索引选择
key	key	实际选择的索引
key_len	key_length	所选键的长度
ref	ref	与索引比较的列
rows
filtered	filtered	按表条件过滤的行百分比
Extra		附加信息

type结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

类型名	解释
system	该表只有一行（=系统表）。这是const联接类型的特例 。
const	该表最多具有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。 const表非常快，因为它们只能读取一次。const将在主键或 唯一性约束的所有部分与常量值进行比较时使用，如id=1
eq_ref	对于先前表中的每行组合，从此表中读取一行。除了 system和 const类型，这是最好的联接类型。当连接使用索引的所有部分且索引为主键或 唯一性约束时使用。可以用于使用=运算符进行比较的索引列 。比较值可以是常量，也可以是使用在此表之前读取的表中列的表达式。在以下示例中，MySQL可以使用 eq_ref联接进行处理 ref_table：
ref	对于先前表中的每个行组合，将从该表中读取具有匹配索引值的所有行。ref如果联接仅使用键的最左前缀，或者如果键不是aPRIMARY KEY或 UNIQUEindex（换句话说，如果联接无法根据键值选择单个行），则使用。如果使用的键仅匹配几行，则这是一种很好的联接类型。ref可用于使用=或<=> 运算符进行比较的索引列 。在以下示例中，MySQL可以使用 ref联接进行处理 ref_table：
fulltext	使用FULLTEXT 索引执行联接。
ref_or_null	这种连接类型类似于 ref，但是除了MySQL还会额外搜索包含NULL值的行。此联接类型优化最常用于解析子查询。在以下示例中，MySQL可以使用 ref_or_null联接进行处理*ref_table*：
index_merge	此联接类型指示使用索引合并优化。在这种情况下，key输出行中的列包含使用的索引列表，并key_len包含使用的索引 的最长键部分的列表。
unique_subquery	此类型替换 以下形式的eq_ref某些 IN子查询： value IN (SELECT primary_key FROM single_table WHERE some_expr) 只是一个索引查找函数，它完全替代了子查询以提高效率。
index_subquery	此连接类型类似于 unique_subquery。它代替IN子查询，但适用于以下形式的子查询中的非唯一索引： value IN (SELECT key_column FROM single_table WHERE some_expr)
range	使用索引选择行，仅检索给定范围内的行。的key 输出行中的列指示使用哪个索引。将key_len包含已使用的时间最长的关键部分。该ref列 NULL适用于此类型。当一个键列使用任何的相比于恒定可使用 =,<>,>,>=,<,<=,is null ,<=>,between,like,in
index	索引全扫描
ALL	全表扫描

优化数据访问

减少请求的数据量

• 只返回必要的列：最好不要使用 SELECT * 语句。
• 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
• 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询，避免回表。

重构查询方式

切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

例如，每个月会运行一次的语句，以清理三个月前的数据:

DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

可以用以下的方法来完成这样的任务：

rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

一次删除一万行数据是个比较高效且对服务器影响较小的做法。同时如果每次删除数据时暂停一会，可以将服务器原本的一次性压力分散到一个较长的时间段中，从而减少删除时锁表锁行的时间。

分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

• 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
• 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
• 减少锁竞争；
• 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
• 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

什么时候应该在程序端进行连接更高

• 可以缓存早期查询的大量数据

• 使用了多个myisam表。

• 数据分布在不同的服务器上

• 对于大表使用IN 替换连接

• 一个连接引用了同一个表很多次

利用LIMIT 1取得唯一行

有时，当你要查询一张表是，你知道自己只需要看一行。你可能会去的一条十分独特的记录，或者只是刚好检查了任何存在的记录数，他们都满足了你的WHERE子句。

在这种情况下，增加一个LIMIT 1会令你的查询更加有效。这样数据库引擎发现只有1后将停止扫描，而不是去扫描整个表或索引。

优化你的MySQL查询缓存

在MySQL服务器上进行查询，可以启用高速查询缓存。让数据库引擎在后台悄悄的处理是提高性能的最有效方法之一。当同一个查询被执行多次时，如果结果是从缓存中提取，那是相当快的。
但主要的问题是，它是那么容易被隐藏起来以至于我们大多数程序员会忽略它。

当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。

为连接的字段设置索引且保证其类型一致

如果两张表被频繁连接，那么可以考虑在连接字段加上索引。

两个连接的列的类型应该一致。如，你加入一个DECIMAL列，而同时加入另一个表中的int列，MySQL将无法使用其中至少一个指标。

即使连接表的列必须同类型。

不要使用BY RAND()命令

# 调用rand函数可以产生随机数介于0和1之间的一个数
select rand()
# 这个不是分组，只是排序，rand()只是生成一个随机数。这样每次检索的结果排序会不同
ORDER By rand()

如果你需要随机生成一条记录

大家一般的写法就是：

SELECT * FROM tablename ORDER BY RAND() LIMIT 1

在MYSQL的官方手册，里面针对RAND()的提示大概意思就是，在ORDER BY从句里面不能使用RAND()函数，因为这样会导致数据列被多次扫描。MySQL可能会为表中每一个独立的行执行BY RAND()命令。

可以使用下面的语句代替

# 如果你存在一个整形并且自增的（如主键id）
FROM A WHERE id >= ((SELECT MAX(id) FROM A)-(SELECT MIN(id) FROM A)) * RAND() + (SELECT MIN(id) FROM A) LIMIT 1 --优化之后
# 取出总数乘以随机数取整
SELECT username FROM user (floor(SELECT count(*) FROM user* RAND())) , 1

将IP地址存储为无符号整型

许多程序员在创建一个VARCHAR（15）时并没有意识到他们可以将IP地址以整数形式来存储。当你有一个INT类型时，你只占用4个字节的空间，这是一个固定大小的领域。

select inet_aton("127.0.0.1")
select inet_ntoa(2130706433)

where子句优化

使用OR优化

where子句中使用了or，然后or的条件没有全部加索引,不会走索引

可以用union all 或 nuin(必要的时候)的方式替换

SELECT id FROM A WHERE num =10 or num = 20 
# 替换为
SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20

使用 ！= 或 <> 操作符优化

在mysql中!=或者<>（也是!=的意思）是不会走索引的

SELECT id FROM A WHERE ID != 5 
# 修改为
SELECT id FROM A WHERE ID>5 union all SELECT id FROM A WHERE ID<5 

IS NULL 或 IS NOT NULL 的优化

在where子句中使用 IS NULL 或 IS NOT NULL 判断，不会走索引

SELECT id FROM A WHERE num IS NULL 
# 修改为 num上设置默认值0，确保表中num没有null值
SELECT id FROM A WHERE num=0 

IN 或 NOT IN的优化

not in 一定不走索引，in走索引有版本要求，并且in的参数过多也不会走索引

# between替换in
SELECT id FROM A WHERE num in(1,2,3)    
SELECT id FROM A WHERE num between 1 and 3    

# exist替换in
SELECT id FROM A WHERE num in(select num from b ) 
SELECT num FROM A WHERE exists(select 1 from B where B.num = A.num) 


# left join替换in （in()适合B表比A表数据小的情况，exists()适合B表比A表数据大的情况）
# 因为in是在内存操作 exists要查询数据库
# in的遍历次数是 A.lengtg() * B.length()  exists遍历次数是 A.lengtg()
# 当B.length()比较小时 A.lengtg() * B.length()的开销要小于去数据库查询 A.lengtg()的开销 所以适合in
SELECT id FROM A WHERE num in(select num from B) 
SELECT id FROM A LEFT JOIN B ON A.num = B.num

使用表达式优化

在=左边的表达式操作都有可能造成索引失效，尽量转换至=右边

SELECT id FROM A WHERE num/2 = 100  
SELECT id FROM A WHERE num = 100*2 

SELECT id FROM A WHERE substring(name,1,3) = 'abc' 
SELECT id FROM A WHERE LIKE 'abc%'

SELECT id FROM A WHERE datediff(day,createdate,'2016-11-30')=0　
SELECT id FROM A WHERE createdate>='2016-11-30' and createdate<'2016-12-1'　

SELECT id FROM A WHERE year(addate) <2016　　　　
SELECT id FROM A where addate<'2016-01-01'

limit分页优化

当偏移量特别时，limit效率会非常低

 # 超级快
 SELECT id FROM A LIMIT 1000,10
 # 特别慢
 SELECT id FROM A LIMIT 90000,10

优化方法(默认id是索引):

# 增加order by limit在排序条件是索引的情况下比较快
# limit offset小的时候,加order by走索引只扫少量行更快,而offset较大时,加order by会更慢,全表扫描反而会快。
select id from A order by id limit 90000,10;
# 这种要保证id是容易比较的
select id,title from A where id>=(select id from collect order by id limit 90000,1) limit 10;
# 同第二条 
select id from A order by id  between 10000000 and 10000010;
# 传递上次分页的最后一条记录的id
select id from A order by id >#{id} limit 10;

批量插入优化

 INSERT into person(name,age) values('A',14) 
 INSERT into person(name,age) values('B',14) 
 INSERT into person(name,age) values('C',14)　
 
 # 优化为，mybatis就是这么做的  利用foreach拼接
 INSERT into person(name,age) values('A',14),('B',14),('C',14)

排序的索引问题

Mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求情况下不要使用排序操作；

尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

尽量用 union all 替换 union

union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union

Inner join 和 left join、right join、子查询

第一：inner join内连接也叫等值连接是，left/rightjoin是外连接。

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;   
SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;
SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

经过多方面的证实inner join性能比较快，因为inner join是等值连接，或许返回的行数比较少。但是我们要记得有些语句隐形的用到了等值连接，如：

SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id; 

推荐：能用inner join连接尽量使用inner join连接
第二：子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。

 Select * from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);

A表的数据为十万级表，B表为百万级表，在本机执行差不多用2秒左右，我们可以通过explain可以查看到子查询是一个相关子查询(DEPENDENCE SUBQUERY);Mysql是先对外表A执行全表查询，然后根据uuid逐次执行子查询，如果外层表是一个很大的表，我们可以想象查询性能会表现比这个更加糟糕。

一种简单的优化就是用innerjoin的方法来代替子查询，查询语句改为：

Select* from A inner join B ON A.uuid=B.uuid using(uuid) where b.uuid>=3000;

第三：使用JOIN时候，应该用小的结果驱动大的结果（left join 左边表结果尽量小，如果有条件应该放到左边先处理，right join同理反向），同时尽量把牵涉到多表联合的查询拆分多个query (多个表查询效率低，容易锁表和阻塞)。如：

Select * from A left join B A.id=B.ref_id where  A.id>10;
#可以优化为
select * from (select * from A wehre id >10) T1 left join B on T1.id=B.ref_id;

使用临时表暂存访问结果

采用临时表暂存中间结果好处：

• 避免程序中多次扫描主表，减少程序执行“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。
• 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
• 避免频繁创建和删除临时表，以减少系统资源的浪费。
• 尽量避免向客户端返回大数据量，若数据量过大，应考虑相应需求是否合理。

总结

不走索引的地方

• 索引参与了计算，如：WHERE age+10=30; 但是注意WHERE age=30-10会走索引
• 索引使用了函数，如：WHERE concat(name,‘abc’) =‘Jaskeyabc’，同样WHERE sname =concat(‘Jaskey’,‘abc’); 会走索引
• 索引列使用了Like %XXX，前缀%不会走，后缀%会走，如name like ‘%san’ 不会走索引，name like 'san%'会走索引，如果需要搜索后缀为xx的，可以考虑存储一个反转的内容到另一列里面
• 索引列的类型和其他类型比较，如你的索引列sex(性别)是字符类型的，那么sex=1不会走索引，sex='1’会走索引，但是如果那个表那个列是一个数字类型，拿来和字符类型的做比较，则不会影响到使用索引，如你的索引列sex(性别)是数字类型的，sex='1’仍然会走索引，但是，无论如何，这种额外的隐式类型转换都是开销，而且由于有字符和数字比就不走索引的情况，故建议避免一切隐式类型转换
• 使用or的时候，如果某一个条件有件索引也不会使用，换言之，就是要求使用的所有字段,都必须建立索引，所以除非每个列都建立了索引，否则不建议使用OR，在多列OR中，可以考虑用UNION 替换
• 当where字句中存在not function，如not in，不会走索引，索引只能告诉你哪些在里面
• in走索引有版本要求，并且in的参数过多也不会走索引