MySQL-lesson04-索引及执行计划

1、索引的作用

• 提供了类似于书中目录的作用,目的是为了优化查询

2、索引的种类

B树索引、Hash索引、R树、Full text、GIS

B树分为：B-tree、B+tree、Btree
B+树 和B树的区别：B*树枝节点有指针，B+树没有

3、索引的分类：

1. 聚集索引：基于主键自动生成，一般是主键，建表时自动创建，数据有规则根据主键存储，叶子节点索引就是真实数据
   聚集索引好处：不需要回表，随机IO变为顺序IO

2. 辅助索引（普通索引、覆盖索引）
   辅助索引怎么生成的：人为创建的，关联聚集索引，指定的列的值，进行排序后，存储的叶子节点中；回表查询
   辅助索引好处：
   1、优化了查询，减少cpu mem IO消耗
   2、减少的文件排序计算

• 覆盖索引（联合索引）：不回表查询，多索引匹配时，where a b c 从左至右匹配走索引，where b c a 不是从左至右匹配不走索引
  当搜索的字段在覆盖索引里面有，不会走聚集索引
  好处：减少回表查询的几率

3. 唯一索引：有可能被选择为聚集索引

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、不走索引的情况（开发规范）

1. 没有查询条件，或者查询条件没有建立索引。
   select * from tab;
   select * from tab where 1=1; //换为有索引的列作为查询条件，或者将没有索引的列加上索引。

全表扫描改为：
select * from tab order by price limit 10; //需要在price列上建立索引

2. 查询结果集是原表中的大部分数据，应该是在25%以上。
   解决方法：如果业务允许，可以使用limit控制。
   综合业务判断，有没有更好的方式。如果没有更好的改写方案，尽量不要在mysql存放这个数据。放到redis里面。

3. 索引本身失效，统计数据不真实；重建索引

4. 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括（+，-，*，/，！等）
   错误例子：select * from test where id-1=9;

5. 隐式转换导致索引失效，这一点应当引起重视，也是开发中常会犯的错误。
   录入数据时，加不加单引号都能插入；但是查询的时候加上单引号，走索引。不加单引号，会转义，不走索引，全表扫描。

6. <> 和 not in 不走索引
   select * from teltab where telnum not in ('110','119');
   单独的 >，<，in 有可能走索引，也有可能不走，和结果集有关，尽量结合业务添加limit，or或in；尽量改成union all

7. like '%_' 百分号在前的不走索引
   %linux%类的搜索需求，可以使用ES

8. 单独引用联合索引里的非第一位置的索引列，不走索引