索引
对于慢查询来说，首先想到的解决方案就是索引，检查查询字段是否加了索引，以及检查索引是否命中
那么什么是索引？索引就是有顺序的数据结构，能加快数据的查询，没有索引的情况下查询是全表扫描，命中率低，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
索引设计原则
