02、索引
前四节内容：https://blog.csdn.net/kuaixiao0217/article/details/128753999
2.5、SQL性能分析
2.5.1、查看执行频次
1、SQL执行频率
MySQL客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息。
通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：
SHOW GLOBAL STATUS LIKE 'Com_______'; # 7个_
2、演示：
2.5.2、慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
# 开启MySQL慢日志查询开关
show_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
配置完毕后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-show.log。
# 重新启动MySQL服务器
systemctl restart mysqld
# 切换目录到/var/lib/mysql下
cd /var/lib/mysql
# 查看localhost-slow.log
cat localhost-slow.log
# 跟踪查看localhost-slow.log
tail -f localhost-slow.log
另外，可以在mysql下通过以下命令查看慢查询日志是否开启
show variables like 'slow_query_log';
2.5.3、profile详情
1、show profile能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
2、通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
SELECT @@have_profiling;
默认profiling是关闭的，可以通过以下命令进行查看profiling是否开启：
SELECT @@profiling;
0：表示关闭；1：表示开启
可以通过set语句在session/global级别开启profiling：
SET profiling = 1;
3、执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：
# 查看每一条SQL的耗时基本情况
show profiles;
# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
2.5.4、explain执行计划
1、EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
语法：
# 直接在select语句之前加上关键字explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
2、EXPLAIN执行计划各字段含义：
-
id
select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）
-
id相同，执行顺序从上到下
-
id不同，值越大，越先执行
-
-
select_type
表示SELECT的类型，常见的取值有：
- SIMPLE（简单表，即不使用表连接或者子查询）
- PRIMARY（主查询，即外层的查询）
- UNION（UNION中的第二个或者后面的查询语句）
- SUBQUERY（SELECT/WHERE之后包含了子查询）
- ……
-
type
表示连接类型，性能由好到差的连接类型为：
- NULL（查询时不访问任何表，可以达到NULL）
- system（访问一张系统表，才会出现system）
- const（根据主键或者唯一索引进行访问表时，会出现const）
- eq_ref
- ref（使用非唯一性的索引进行查询时，会出现ref）
- range
- index
- all（全表扫描）
-
possible_key
显示可能应用在这张表上的索引，一个或多个。
-
key
实际使用的索引，如果为NULL，则没有使用索引。
-
key_len
表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
-
rows
MySQL认为必须要执行查询的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的。
-
filtered
表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。
2.6、索引使用规则
2.6.1、验证索引效率
1、在未建立索引之前，执行如下SQL语句，查看SQL的耗时。
SELECT * from tb_sku where sn = '10000003145001';
2、针对字段创建索引
create index idx_sku_sn on tb_sku(sn);
然后再次执行相同的SQL语句，再次查看SQL的耗时。
SELECT * from tb_sku where sn = '10000003145001';
2.6.2、最左前缀法则
2.6.2.1、最左前缀法则
1、简介
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
如果跳过某一列，索引将部分失效（后面的字段索引失效）。
2、演示：
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select * from tb_user where profession = '软件工程' and age = 31;
explain select * from tb_user where profession = '软件工程';
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';
explain select * from tb_user where profession = '软件工程' and status = '0';
注意：
最左前缀法则不限制索引字段的顺序，只要最左列的索引字段（可通过
show index from 表名进行查询，在查询结果表中找到联合索引的Seq_in_index=1即为最左列索引字段）存在即可。explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程';
2.6.2.2、范围查询
联合索引中，出现的范围查询（>，<），范围查询右侧的列索引失效。
explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';
explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';
2.6.3、索引失效情况
1、索引列运算
不要在索引列上进行运算操作，索引将失效。
2、字符串不加引号
字符串类型字段使用时，不加引号，索引将失效。
3、模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
4、or连接的条件
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。
# age建立索引
create index idx_user_age on tb_user(age);
只有or两侧的列都有索引的时候，索引才会生效。
5、数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。
2.6.4、SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
-
use index：建议使用指定索引列explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
使用use index只是给MySQL一个建议，是否使用该索引列，取决于MySQL分析
-
ignore index：忽略使用指定索引列explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
-
force index：强制使用指定索引列explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
2.6.5、覆盖索引
1、尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。
explain select id,profession from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select id,profession,age,status from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select id,profession,age,status,name from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
知识小贴士：
using index condition：查找使用了索引，但是需要回表查询数据using where;using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
2、思考题
一张表，有四个字段（id，username，password，status），由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案：
select id,username,password from tb_user where username = 'itcast';
答：对username和password建立联合索引
2.6.6、前缀索引
1、当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样就可以大大节约索引空间，从而提高索引效率。
2、语法：
create index idx_xxxx on table_name(column(n));
-
n表示前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
select count(distinct email)/count(*) from tb_user; select count(distinct substring(email,1,5))/count(*) from tb_user;
3、前缀索引查询流程
- 取
lvbu666@163.com前5位，为lvbu6 - 将
lvbu6在辅助索引中进行比较，找到第一个lvbu6所在位置，得到其对应的id为1 - 将
id=1在聚集索引中进行比较，找到id=1所在位置，得到其所在列的数据row - 将
email='lvbu666@163.com'与row中的email的值进行比较，如果一致，需要将row返回 - 在辅助索引中，走到
lvbu6所在位置的下一个结点，看其元素是否为lvbu6 - 如果是
lvbu6，重复第二步；如果不是lvbu6，且第四步中的比较结果一致，则直接返回row数据
2.6.7、单列索引与联合索引
单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。
explain select id,phone,name from tb_user where phone = '17799990010' and name = '韩信';
explain select id,phone,name from tb_user use index(idx_user_phone_name) where phone = '17799990010' and name = '韩信';
- 在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
- 多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。
2.7、索引设计原则
1、针对于数据量较大，且查询比较频繁的表建立索引。
2、针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3、尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4、如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5、尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6、要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7、如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
265
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
