004_Mysql 之索引使用以及优化

SQL 性能分析

profile 分析

-- 查看是否开启功能
SELECT @@have_profiling ;
SET profiling = 1;

-- 查看每一条SQL的耗时基本情况 
show profiles; 
-- 查看指定query_id的SQL语句各个阶段的耗时情况 
show profile for query query_id; 
-- 查看指定query_id的SQL语句CPU的使用情况 
show profile cpu for query query_id;

Explain

--直接在select语句之前加上关键字 explain / desc 
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

Explain 执行计划中各字段的含义

索引的使用

1、最左前缀法则
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

注意 ： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关

2、范围查询
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';

当范围查询使用> 或 < 时，走联合索引了，但是索引的长度为49，就说明范围查询右边的status字段是没有走索引的。

explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';

当范围查询使用>= 或 <= 时，走联合索引了，但是索引的长度为54，就说明所有的字段都是走索引的。

**所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 < 。 **

3、覆盖索引
尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。
为什么要使用覆盖索引，这样可以很大程度上避免回表查询。

4、前缀索引
当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让 索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率

索引的设计原则

  - 针对于数据量较大，且查询比较频繁的表建立索引。 
  - 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。 
  - 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。 
  - 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。 
  - 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间， 避免回表，提高查询效率。 
  - 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。 
  - 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

索引失效情况

1、索引列运算
2、字符串不加引号，如果字符串不加单引号，对于查询结果，没什么影响，但是数据库存在隐式类型转换，索引将失效
3、模糊查询，在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字前面加了%，索引将会失效。
4、数据分布影响，查询时MySQL会评估，走索引快，还是全表扫描快，如果全表扫描更快，则放弃索引走全表扫描。
5、or 条件连接，当or连接的条件，左右两侧字段都有索引时，索引才会生效

explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;

由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。

索引的优化

1、主键优化
索引设计原则：

        - 满足业务需求的情况下，尽量降低主键的长度。 
        - 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
        - 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。 
        - 业务操作时，避免对主键的修改。

2、order by 优化
MySQL的排序，有两种方式：
Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
**Using index **: 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要 额外排序，操作效率高。
对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

特别注意点：
1、创建索引后，
1）根据age, phone进行降序排序

-- 创建索引 
create index idx_user_age_phone_aa on tb_user(age,phone);
explain select id,age,phone from tb_user order by age desc , phone desc ;

也出现 Using index， 但是此时Extra中出现了 Backward index scan，这个代表反向扫描索引，因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。 在 MySQL8版本中，支持降序索引，我们也可以创建降序索引。

2）根据phone，age进行升序排序，phone在前，age在后

explain select id,age,phone from tb_user order by phone , age;

排序时,也需要满足最左前缀法则,否则也会出现 filesort。因为在创建索引的时候， age是第一个字段，phone是第二个字段，所以排序时，也就该按照这个顺序来，否则就会出现 Using filesort。

3）根据age, phone进行降序一个升序，一个降序
因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时就会出现Using filesort。
为了解决上述的问题，我们可以创建一个索引，这个联合索引中 age 升序排序，phone 倒序排序

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

**order by优化原则: **
A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
B. 尽量使用覆盖索引。
C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

3、group by 优化
在分组操作中，我们需要通过以下两点进行优化，以提升性能：
A. 在分组操作时，可以通过索引来提高效率。
B. 分组操作时，索引的使用也是满足最左前缀法则的。

4、limit 优化
优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

5、count 优化
MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个
数，效率很高； 但是如果是带条件的count，MyISAM也慢。
InnoDB 引擎就麻烦了，它执行 count() 的时候，需要把数据一行一行地从引擎里面读出
来，然后累积计数。
如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)。

用法：count（*）、count（主键）、count（字段）、count（数字）

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以尽量使用 count()。

6、update 优化
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。