思考:
我们知道mysql最好的数据存储量级是百万级别,是的往往在百万级别或者几十万级别就会出现慢查询(我对慢查询的定义是大于1秒),几年前我所在的一个做pos机支付的联机交易的核心系统组,当时就做过一次索引优化最终的总的交易时间缩短了300毫秒,约占总耗时的1/3。就在近期我所在的部门的一个非常重要的商品结构组(目标是像淘宝商品看齐的),频频爆出慢查询据说最慢的有5-6秒,总感觉太不应该,虽然最重要的接口都是缓存在redis,但是太多慢查询有可能会拖垮整个数据库,当缓存被穿透了也是要查db的给后续埋了隐患,所以就引发了我想写这篇文章。
1、目前常用的索引有Hash索引和B+树
1、Hash 索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B+Tree 索引。
但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端,主要有以下这些。
(1)Hash 索引仅仅能满足"=","IN"和"<=>"查询,不能使用范围查询。
(2)Hash 索引无法被用来避免数据的排序操作。
(3)Hash 索引不能利用部分索引键查询。
(4)Hash 索引在任何时候都不能避免表扫描。
(5)Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B+Tree索引高。
造成如上的主要原因是:hash是生成一个固定的结果没法做区间的查询也无法做排序,hash值是用所有的值来算的部分索引值是无效的,况且我们也可能遇到大量的hash值相同的情况这
2、B+树是目前主流的底层实现原理
B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接。这样不会造成数据倾斜,下面介绍一下InnoDB引擎的B+树结构(MyISAM实现的B+数略有不同),B+树索引可以分为聚集索引(clustered index)和非聚集索引(即辅助索引,secondary index)。
聚集索引
索引组织表,即表中数据按主键B+树存放,叶子节点直接存放数据,每张表只能有一个聚集索引。当你定义primary key时其是聚集索引,如果你自己没定义则会生成一个默认的自增的长整型数
辅助索引
辅助索引(也称非聚集索引)是指叶节点不包含行的全部数据,叶节点除了包含键值之外,还包含一个书签连接,通过该书签再去找相应的行数据。下图显示了InnoDB存储引擎辅助索引和聚集索引的关系:
![](http://upload-images.jianshu.io/upload_images/2649971-4f180549330e1ffd.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
以下所有的分析都是基于InnoDB存储引擎。
2、分析索引好坏
1、索引分析利器,explain:
mysql> explain select * from user_test;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | user_test | ALL | NULL | NULL | NULL | NULL | 2 | |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
explain结果解释:
-
- table:显示这一行的数据是关于哪张表的
-
- type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const(记住一定是用到primary key 或者unique)、eq_reg(一般是设置primary key或者unique在多表关联情况下)、ref(除了在primary key 或者unique以外的索引或者索引组合)、range(如where XXX in(1,2)索引在某个区间)、index和all(我们的最终的目标是往const上靠,上面的结果All就是没用过索引的)
const:
mysql> explain select * from user_test where id = 1;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | user_test | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+eq_req:
mysql> explain select * from user_test a inner join user_score b on a.id = b.id ;
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------+
| 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 2 | |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test_copy.b.id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------+ref:
mysql> explain select * from user_test where name = 'hahah';
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | user_test | ref | iddex_name | iddex_name | 258 | const | 1 | Using where |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+range:
mysql> explain select * from user_test where name in ('hahah','hahah22');
+----+-------------+-----------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | user_test | range | iddex_name | iddex_name | 258 | NULL | 2 | Using where |
+----+-------------+-----------+-------+---------------+------------+---------+------+------+-------------+index
mysql> explain select id from user_test;
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | user_test | index | NULL | PRIMARY | 4 | NULL | 2 | Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+All
mysql> explain select * from user_test;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | user_test | ALL | NULL | NULL | NULL | NULL | 2 | |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
- type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const(记住一定是用到primary key 或者unique)、eq_reg(一般是设置primary key或者unique在多表关联情况下)、ref(除了在primary key 或者unique以外的索引或者索引组合)、range(如where XXX in(1,2)索引在某个区间)、index和all(我们的最终的目标是往const上靠,上面的结果All就是没用过索引的)
-
- possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从where语句中选择一个合适的语句
-
- key: 实际使用的索引。如果为null,则没有使用索引。很少的情况下,mysql会选择优化不足的索引。这种情况下,可以在select语句中使用use index(indexname)来强制使用一个索引或者用ignore index(indexname)来强制mysql忽略索引
-
- key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好
-
- ref:显示索引的哪一列被使用了,如果可能的话,是一个常数
-
- rows:mysql认为必须检查的用来返回请求数据的行数
-
- extra:关于mysql如何解析查询的额外信息。看到的坏的例子是using temporary(使用临时表)和using filesort,意思是mysql根本不能使用索引,结果是检索很慢
3、一般怎么建索引
1、充分发挥like的作用
如:select id from t where substring(name,1,3)='abc' ,name以abc开头的id**
应改为:select id from t where name like 'abc%' 这样当name有索引的时候是可以用上索引的,如果改成like '%abc'能索引上么,答案是不能
2、索引字段尽量不要设置为NULL并且进行值的where判断,否则将导致引擎放弃使用索引而进行全表扫描
3、不要在索引字段上使用mysql的函数,如where substr(date,1,10) = '2016-09-07' 这样索引是会失效的,对于这种情况可以改写为 date between '2016-09-07 00:00:00' and '2016-09-07 23:59:59'
4、复合索引建立以后如index_a_b_c建立在a、b、c3个字段上:
where a=XX and b=XX and c=XX能被索引
where a=XX能被索引
where a=XX and b=XX能被索引
where b=XX 不被索引
where c=XX不被索引
where b=XX and c=XX不被索引
where a=XX and c=XX索引较差
where b=XX and c=XX and a= XX不被索引
你可以理解为当顺序不一样时,索引指向就变了。
如果不是这种情况怎么办呢?还能怎么办,修改where顺序啊,总比不同的顺序再建个索引好
5、在= 、group by 和 order by字段上面加上索引
6、在join的时候中结果集更小的部分join更大的部门,这样可以减少缓存的开销
7、索引并不是越多越好不要每一个字段建一个索引,即使这样mysql也会自身优化也只会选择其中的一个索引来执行,索引固然可 以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
8、在使用in的时候可以尝试使用exists试试
9、在join的时候减少extra字段中临时表的数量。
4、结束语
中国有句古话"天下武功唯快不破",我想的是mysql查询优化用好explain分析器,提前建好索引可以减少很多不必要的麻烦。当出现慢查询的时候看好索引或者修改join的写法,最终用explain来分析孰优孰劣,或许你会有意想不到的收获。
原文链接:http://www.jianshu.com/p/ba593f9e2543
著作权归作者所有,转载请联系作者获得授权,并标注“简书作者”。