1、索引的作用
- 提供了类似于书中目录的作用,目的是为了优化查询
2、索引的种类
B树索引、Hash索引、R树、Full text、GIS
B树分为:B-tree、B+tree、Btree
B+树 和B树的区别:B*树枝节点有指针,B+树没有
3、索引的分类:
-
聚集索引:基于主键自动生成,一般是主键,建表时自动创建,数据有规则根据主键存储,叶子节点索引就是真实数据
聚集索引好处:不需要回表,随机IO变为顺序IO -
辅助索引(普通索引、覆盖索引)
辅助索引怎么生成的:人为创建的,关联聚集索引,指定的列的值,进行排序后,存储的叶子节点中;回表查询
辅助索引好处:
1、优化了查询,减少cpu mem IO消耗
2、减少的文件排序计算
- 覆盖索引(联合索引):不回表查询,多索引匹配时,where a b c 从左至右匹配走索引,where b c a 不是从左至右匹配不走索引
当搜索的字段在覆盖索引里面有,不会走聚集索引
好处:减少回表查询的几率
- 唯一索引:有可能被选择为聚集索引
4、索引管理
# 建立索引
alter table stu add index idx_name(sname);
create index idx_name on stu(sname);
# 删除索引
alter table stu drop index idx_name;
# 查看索引
desc stu;
show index from stu\G
# 联合索引创建
alter table city add index idx_co_po(countrycode,population);
# 前缀索引
alter table city add index idx_dis(district(10));
# 唯一索引
alter table stu add unique index idx_name(sname);
# 查看表中数据行数
select count(*) from city;
# 查看去重数据行数
select count(distinct name) from city;
5、explain 查看执行计划
数据库插入文件:source /root/world.sql
explain 应用实例
pc [world]>explain select * from city where countrycode='CHN'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
type: ref
possible_keys: CountryCode
key: CountryCode
key_len: 3
ref: const
rows: 363
Extra: Using index condition
1 row in set (0.00 sec)
possible_keys: CountryCode -----> 可能会走的索引
key: CountryCode ----->真正走的索引
type: ref -----> 索引类型
Extra: Using index condition ----->额外信息
- Extra:using filesort 排序,需要优化
order by 语句优化方法:将where条件和order by列建立联合索引
alter table city add index idx_co_po(countrycode,population);
辅助索引应用顺序(优化器选择):
如果查询条件符合覆盖索引的顺序时,优先选择覆盖索引,也就是优先走where条件
6、explain使用场景
1、mysql出现性能问题(排除硬件,架构原因,参数)
2、获取到问题语句
-
应急情况:数据库夯住(资源耗尽)
处理过程:
(1) show full processlist; 获取到导致夯住的语句
(2) explain 分析sql的执行计划,有没有走索引,索引的类型
(3) 建索引,改语句 -
一段时间慢:
处理过程:
(1) 记录慢日志,分析慢日志
(2) explain 分析sql的执行计划,有没有走索引,索引的类型情况
(3) 建索引,改语句
7、type:索引类型
- 从上到下,性能从最差到最好,我们认为至少要达到range级别
1、全表扫描:ALL
pc [world]>explain select * from city;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
全表扫描,禁止使用,随机IO
2、全索引扫描:index
pc [world]>explain select countrycode from city;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| 1 | SIMPLE | city | index | NULL | CountryCode | 3 | NULL | 4188 | Using index |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)
效率一般,走索引数,顺序IO
3、索引范围扫描:range
pc [world]> pc [world]>explain select * from city where countrycode like 'CH%';
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| 1 | SIMPLE | city | range | CountryCode | CountryCode | 3 | NULL | 397 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
# range使用索引优化查询的最低级别
< > in or like 都是range级别
< >最好执行上下限,缩小范围
like 语句前导字符尽量唯一性强一些,%在后
对于in 和 or 尽量改成 union all
4、辅助索引等值查询:ref
pc [world]> pc [world]>explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
| 1 | PRIMARY | city | ref | CountryCode | CountryCode | 3 | const | 363 | Using index condition |
| 2 | UNION | city | ref | CountryCode | CountryCode | 3 | const | 274 | Using index condition |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
3 rows in set (0.00 sec)
执行效率为ref 高于in和or
5、eq_ref:表连接时on的条件列是主键或者唯一键
a join b on a.id=b.id
如果达不到主键或者唯一键的条件,至少要有辅助索引,一般和where条件列建联合索引
6、system/const:
where条件列,是主键或者唯一键的等值查询
pc [world]>explain select * from city where id=10;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | city | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
7、NULL:没有这个值
pc [world]>explain select * from city where id=100000;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
8、数据库索引的设计原则
1、建表时一定要有主键,如果相关列可以作为主键,做一个无关列
2、选择唯一键索引
唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。如果重复值较多,可以考虑建立联合索引。
主键索引和唯一键索引,在查询中使用是效率最高的。
select count(*) from world.city;
select count(distinct countrycode) from world.city;
如果重复列多的话,也可以用两个列做唯一键:
select count(distinct countrycode,population) from world.city;
3、为经常需要用到排序、分组和联合操作的字段建立索引
经常需要order by 、group by,join on等操作的字段,排序操作会浪费很多时间。如果为其建立索引,可以有效地避免排序操作。
4、经常作为where 查询条件的字段建立索引
如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,为这样的字段建立索引,可以提高整个表的查询速度。
(1)经常查询
(2)列值的重复值较少
注:如果列值重复值较多,可以建立联合索引
5、尽量使用前缀来索引
如果索引字段的值很长,最好使用值的前缀来索引
6、限制索引的数目
索引的数目不是越多越好。修改表时,对索引的重构和更新很麻烦,插入数据会变慢。越多的索引,会使更新表变得很浪费时间。
7、删除不再使用或者很少使用的索引(percona toolkit)
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理员应当定其找出这些索引,将它们删除,从而减少索引对更新操作的影响。
8、大表建立索引,要在业务不繁忙期间操作
9、在什么位置建索引
(1)必须要有主键,如果没有可以作为主键条件的列,创建无关列。
(2)经常作为where条件列、order by 、group by 、join on的条件列建立索引(分析业务:产品功能+用户行为)
(3)最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引。
(4)列值长度较长的索引列,建议使用前缀索引。
(5)降低索引条目,一方面不要创建没用索引,不常使用的索引清理(percona toolkit)。
(6)索引维护要避开业务繁忙期。
10、不走索引的情况(开发规范)
- 没有查询条件,或者查询条件没有建立索引。
select * from tab;
select * from tab where 1=1; //换为有索引的列作为查询条件,或者将没有索引的列加上索引。
全表扫描改为:
select * from tab order by price limit 10; //需要在price列上建立索引
-
查询结果集是原表中的大部分数据,应该是在25%以上。
解决方法:如果业务允许,可以使用limit控制。
综合业务判断,有没有更好的方式。如果没有更好的改写方案,尽量不要在mysql存放这个数据。放到redis里面。 -
索引本身失效,统计数据不真实;重建索引
-
查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,!等)
错误例子:select * from test where id-1=9; -
隐式转换导致索引失效,这一点应当引起重视,也是开发中常会犯的错误。
录入数据时,加不加单引号都能插入;但是查询的时候加上单引号,走索引。不加单引号,会转义,不走索引,全表扫描。 -
<> 和 not in 不走索引
select * from teltab where telnum not in ('110','119');
单独的 >,<,in 有可能走索引,也有可能不走,和结果集有关,尽量结合业务添加limit,or或in;尽量改成union all -
like '%_' 百分号在前的不走索引
%linux%类的搜索需求,可以使用ES -
单独引用联合索引里的非第一位置的索引列,不走索引
网友评论