索引

对于慢查询来说，首先想到的解决方案就是索引，检查查询字段是否加了索引，以及检查索引是否命中
那么什么是索引？索引就是有顺序的数据结构，能加快数据的查询，没有索引的情况下查询是全表扫描，命中率低，IO次数高，效率慢

• 优点
  • 提高数据检索效率，降低IO成本
  • 通过索引对数据进行排序，降低数据排序成本，降低CPU的消耗
• 缺点
  • 索引列会占用空间（磁盘较为便宜）
  • 索引提高了查询效率，降低了更新表的速度，如对表进行insert、update、delete时，效率降低（大部分都是查询操作）

索引的数据结构推演

目前MySQL的索引使用的数据结构是B+树，为什么使用B+树？B+树解决了什么问题？可以推演一下

二叉树

对于MySQL索引的数据结构，一开始想到的就是二叉树，查询速度快
比如咱们对下图的数据进行select * from t where col2=89

如果没有索引，该表需要进行6次查询（6次IO），如果加了二叉树，只需要2次查询，效率大大提高
注意：二叉树的排序原理为：每次加一个数据进来都会从根节点进行比较，大的在右边，小的在左边

红黑树

根据上面图可以看出问题，如果查询的是select * from t where col1=6，因为col1的数据递增，所以二叉树为

这样的二叉树对于查询来说依然是6次，效率没有太大提升
解决方案：使用红黑树，红黑树也是一种二叉树，红黑树又叫平衡二叉树，与二叉树不同的是，每次插入数据后会进行平衡，不让树的左右节点出现太长的情况，这样就解决了顺序数据的查询效率问题

B树

但是同样红黑树作为MySQL索引的数据结构还是有问题的，毕竟在MySQL中，数据量是很大的，几百万或几千万的数据形成一颗红黑树，层级太深，对于查询来说，效率同样不是很理想
这个问题想到的解决办法为：多叉，这样的数据结构为B树

B+树

但是对于MySQL，是将B树进行了改进，形成了B+树

这样就达到了在数据大的情况下，树的高度依旧在一个可控的范围
这里需要注意，数据查找时的比较由于是在内存中进行，耗费时间可以忽略不记

B树和B+树的区别

• B+树将数据都放在了叶子节点，普通节点增加了冗余数据，便于控制树的高度，增加查找效率
• B+树多维护了一个指针，便于范围查找

索引分类

名称	说明	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建，只有一个	primary
唯一索引	避免列值重复	可以多个	unique
常规索引	快速定位特定数据	可以多个
全文索引	查找文本中的关键字	可以多个	fulltext

效率问题：下列查询哪个效率高？

select * from user where id=10
select * from user where name='xx'
备注：id为主键，name字段也创建了索引

说明：对于主键查询直接到聚集索引进行检索即可，而对于常规索引查询需要到二级索引中查询到对应的主键ID，再到聚集索引查询数据，该行为称为回表查询，对应效率较低

索引语法

-- 创建索引
create [unique|fulltext] index index_name on table_name (index_col_name,...)
-- 查看索引
show index from table_name
-- 删除索引
drop index index_name on table_name

性能分类

SQL执行频率

查询方式：show global status like 'Com_______';，可以查看各类SQL的执行次数，如：

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_import    | 0     |
| Com_insert    | 2     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 27    |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位秒，默认10）的所有SQL语句的日志
MySQL的慢查询日志默认没有开启，可以使用show VARIABLES like 'slow_query%';查看开启状态，使用show VARIABLES like 'long_query_time';查看慢日志时间
慢日志查询的开启可以临时和永久，临时方式在MySQL重启后失效

set global slow_query_log=’ON’;
set global long_query_time=4; # 该设置需要重新建立会话才生效

永久方式为对文件/etc/my.cnf进行配置

# 开启慢查询日志
show_query_log=1
# 设置慢查询时间
long_query_time=2

注意一定要加在以下配置之下，不能放在其他配置之后，会报错

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

配置后需要重启systemctl restart mysqld，慢日志文件在/var/lib/mysql/*-show.log
如果配置错误无法启动，可以查看启动错误日志cat /var/log/mysqld.log | grep ERROR

小技巧：在控制台查询SQL时字段过多，可以将末尾的;换为\G进行换行展示

profile

对于一些简单的查询SQL，即便设置了慢日志在2s，其执行耗时1.9s也是不好的，可以借助profile进行分析
使用select @@have_profiling;查看数据库是否支持，默认profile是关闭的，可以进行开启

--查看开启状态：0-关闭 1-开启
select @@profiling;
--设置开启状态
set profiling=1;

• show profiles;：查看所有执行SQL的耗时情况
• show profile for query query_id：查看指定query_id的SQL语句在各个阶段的耗时
• show profile cpu for query query_id：查看指定query_id的SQL语句CPU耗时情况

explain执行计划（重要）

对查询语句前加explain/desc可以查看SQL的执行计划

• id：查询序列号（顺序），id值越大越先执行，相同从上到下
• select_type：查询类型，SIMPLE（简单查询），PRIMARY（主查询），UNION...
• type：连接类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all
• possible_key：可能用到的索引
• key：实际用到的索引
• filtered：返回行数占读取行数的百分比，越大越好

根据主键或唯一索引查询为const级别，非唯一性索引查询为ref级别

最左前缀法则

在建立多列索引（联合索引）的情况下，如下图，索引有name、age、position三个字段

如果查询的语句为：select * from t where age=18，其实是无法命中索引的
而使用select * from t where name='tom' and age=18就可以命中索引，这是由于联合索引从第一个索引开始命中，没有第一个直接不算命中
原因：直接查询age是无序的，无法进行快速查询，如果name相同那么age就是有序的，就能命中索引

结论：如果索引为联合索引，要遵循最左前缀法则，即查询从索引的最左列开始，并且不能跳过索引中的列，否则后面索引将失效（与查询语句中条件的位置无关，指字段必须存在）

索引失效场景

• 联合索引查询不满足最左前缀法则
• 联合索引查询出现范围查询（>，<），范围查询右侧的列索引失效，解决方案：在业务允许的情况下改为>=或<=
• 索引列上进行运算操作，索引将失效，如select * from user where substring(phone,10,2)='10'
• 字符串不加引号，索引将失效，如select * from user where phone=13900000000
• 对于模糊查询如果进行头部模糊查询，索引将失效，如select * from user where name like '%雪'
• or连接的条件，如果or前面的条件有索引而后面没有，那么全部索引都失效，解决方案：为没有索引的字段加索引
• 数据分布影响：如果MySQL评估使用索引比全表扫描更慢，则不使用索引，例如查询>=0的条件，如果大部分数据都满足，MySQL就会评估进行全表扫描，取决于数据的分布，查询快慢的比较

SQL提示

优化数据库的重要手段，对于多个索引场景，MySQL会自动选择索引，开发人员可以通过指定的方式告诉MySQL使用哪个索引

--语法
select * from user xxx(index_name)
--告诉数据库建议使用的索引，MySQL会比较该索引是否更快，然后采用
use index
--告诉数据库不要使用的索引
ignore index
--告诉数据库必须使用该索引
force index

覆盖索引

前提知识：聚集索引为id索引，叶子节点包含了一整列数据；而二级索引仅包含了索引字段的数据和id 对于查询的列，尽量不要使用select *，而应尽量查找二级索引中叶子节点有的数据，如果二级索引叶子节点没有相关数据，就会发生回表查询操作，即到聚集索引中找相关字段，该操作效率低，10倍以上耗时
而没有发生回表操作的索引查询称为覆盖索引，效率高

实际操作案例：一张表有四个字段（id、username、password、status），由于数据量大，需要对以下SQL进行优化，该如何进行才是最优方案？

select id,username,password from user where username = 'xx'

答：针对username和password创建联合索引，该二级索引下叶子节点有id，就能防止回表查询，提高效率

前缀索引

当字段类型为字符串（varchar，text...）时，对其直接建立索引会导致索引占用空间很大，查询时浪费大量磁盘IO，降低效率，此时可以将字符串的一部分前缀建立索引，节约索引空间，提高效率

--语法
create index idx_xxx on table_name(column(n))

前缀长度：根据索引的选择性决定，选择性指不重复的索引值和数据表的记录总数的比值，索引选择性越高查询效率越高

select count(distinct text) / count(*) from table
select count(distinct substring(text, 1, 5)) / count(*) from table

索引设计原则

索引失效场景总结

https://mp.weixin.qq.com/s/5N71T-kloq-IUQKziB5THA