MySQL Index 和 Optimization
索引可以减少磁盘 IO 次数，索引是数据结构
InnoDB 不支持 hash 索引，但有自适应 hash 索引(show variables like '%innodb_adaptive_hash_index%')，直接定位数据页
一、索引结构
B-Tree
B+Tree
R-Tree
二、常见索引(数据存储方式)
聚簇索引
主键构建的索引(有且只有一个，尽量选单调字段做主键)，只有 InnoDB 支持，数据和索引放在一起，且按一定顺序，找到索引也就找到数据
非聚簇(二级、辅助)索引
非主键字段构建的索引，需要显式创建，叶子节点不存数据，存索引字段和主键，需要回表(根据主键再去聚簇索引中查找)
非聚簇索引可以有多个
联(复、组)合索引
index-merge-optimization：https://dev.mysql.com/doc/refman/8.3/en/index-merge-optimization.html
非聚簇索引的一种，多个字段建立索引，存储时按照多个字段依次排序。二级索引的索引值默认包含主键，所以二级索引也是复合索引。
三、与 MyISAM 对比
索引结构(B+Tree)
索引(myi，B+ 树)和数据(myd，不排序，按照插入顺序)分开存储。叶子节点 data 域存放对应数据的地址
常见索引(数据存储方式)
没有聚簇索引，全部是二级索引，需要回表(从 myi 中拿到偏移地址后去 myd 拿(很快)数据)
四、索引分类
功能上：普通索引、唯一索引(不可重复)、主键索引(一张表只有一个，唯一且不为空)、全文索引
作用字段数量上：单列(值)索引、联(组)合索引
物理实现上：聚簇索引、非聚簇(二级、辅助)索引
8.0 新增了隐藏索引(Invisible Indexes)和降序索引(Descending Indexes)
五、索引的设计(使用)原则
适合加索引(频繁的，区分度高的)
- 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引
- 频繁作为 WHERE 查询条件的字段
- 经常 GROUP BY 和 ORDER BY 的列，多个字段就建立联合索引(注意先后顺序和升序降序)
- UPDATE、DELETE 的 WHERE 条件列
- DISTINCT 字段需要创建索引
- 多表 JOIN 连接操作
- 首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率
- 其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的
- 最后，对用于连接的字段创建索引，且该字段在多张表中的类型必须一致。若一个为 int 另一个为 varchar，则会发生转换，转换会使用函数，使用函数索引会失效
- 使用列的类型小的创建索引：类型大小指的就是该类型表示的数据范围的大小，能使用 INT 就不要使用 BIGINT，能使用 MEDIUMINT 就不要使用 INT
- 数据类型越小，在查询时进行的比较操作越快
- 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I/O 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率
- 对于主键来说更加适用，因为不仅聚簇索引中会存储主键值，其他所有二级索引的节点处都会存储记录的主键值，如果主键使用更小的数据类型，就意味着节省更多的存储空间(高效 I/O)
- 使用字符串前缀创建索引
- create table shop(address varchar(120) not null)
- alter table shop add index(address(12))
- select count(distinct left(address, 18))/count(*) as '截取前 18 个字符的选择度', count(distinct left(address, 12))/count（*）as '截取前 12 个字符的选择度' from shop
- 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度
- 索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上，可以使用 count(distinct left(列名, 索引长度) / count(*) 的区分度来确定
- 使用索引列前缀的方式无法支持使用索引排序，只能使用文件排序
- 区分度高(散列性高)的列适合作为索引
- 使用公式 select count (distinct a) / count (*) from t1 计算区分度，越接近 1 越好，一般超过 33% 就算是比较高效的索引
- 联合索引把区分度高(散列性高)的列放在前面
- 使用最频繁的列放到联合索引的左侧：这样也可以较少的建立一些索引。同时，由于“最左前缀原则”，可以增加联合索引的使用率
- 在多个字段都要创建索引的情况下，联合索引优于单值索引
不适合加索引
- 在 where 中使用不到的字段，不要设置索引
- 数据量小的表最好不要使用索引，例如不到 1000 行
- 有大量重复数据的列上不要建立索引，比如高于 10% 的时候，不需要对这个字段使用索引
- 避免对经常更新的表创建过多的索引
- 不建议用无序的值作为索引
- 删除不再使用或者很少使用的索引
- 不要定义冗余或重复的索引
建议单张表索引数量不超过 6 个
- 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间越大
- 索引影响 INSERT、DELETE、UPDATE 等语句性能，因为表中数据更改的同时，索引也会进行调整和更新，会造成负担
- 优化器在优化查询时，会根据统一信息，对每个可用的索引进行评估，生成最优的执行计划，若同时有很多索引都可用，会增加优化器生成执行计划的时间，降低查询性能
六、性能分析
查看服务器状态信息
https://dev.mysql.com/doc/refman/8.3/en/show-status.html
SHOW [GLOBAL | SESSION] STATUS LIKE '参数' -- Connections：连接 MySQL 服务器的次数 -- Uptime：MySQL 服务器的上线时间 -- Slow_queries：慢查询的次数 -- last_query_cost：上一条 SQL 语句所需要读取的页的数量
定位慢 SQL
https://dev.mysql.com/doc/refman/8.3/en/slow-query-log.html
-- 运行 long_query_time (默认 10，不含 10) 秒以上的语句为慢查询，默认没有开启(show variables like 'slow_query_log') -- 不是调优需要，不建议启动该参数，因为开启(set global slow_query_log='ON')慢查询日志会或多或少带来一定的性能影响 -- 慢查询日志支持将日志记录写入文件(show variables like 'slow_query_log_file') -- min_examined_row_limit：查询扫描过的最少记录数。这个变量和 long_query_time 共同组成了判别一个查询是否是慢查询的条件 -- 慢查询日志分析工具：mysqldumpslow -a -s t -t 5 /var/lib/mysql/user-slow.log -- 慢查询日志删除重建(需要旧的要事先备份)：mysqladmin -uroot-p flush-logs slow
查看 SQL 执行成本
https://dev.mysql.com/doc/refman/8.3/en/show-profile.html
-- 查看 profile 是否开启 show variables like '%profiling%'; -- 开启 profile set profiling=1; -- 查看最近一次 sql 的执行周期 show profile; -- 查看最近的几次 show profiles; -- 根据 Query_ID 来查看 sql 的具体执行步骤 show profile cpu,block io for query Query_id;
分析查询语句：EXPLAIN(DESCRIBE)
https://dev.mysql.com/doc/refman/8.3/en/execution-plan-information.html
-- table：表名，查询的每一行记录都对应着一个单表 -- id：在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id，表示操作表的顺序，id 越大优先加载 -- select_type：小查询在大查询中扮演什么角色 -- partitions：代表分区表中的命中情况，非分区表，该项为 NULL -- type：针对单表的访问方法，是较为重要的一个指标。比如，看到 type 列的值是 ref，表明 MySQL 将使用 ref 访问方法来执行对 s1 表的查询 -- possible_keys：可能用到的索引 -- key：实际用到的索引 -- key_len：实际用到的(联合)索引字段长度，越短越好 -- ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息 -- rows：扫描行数量 -- filtered：某个表经过搜索条件过滤后剩余记录条数的百分比 -- extra：一些额外的信息，执行情况说明与描述
索引失效的几种情况
-- where 条件有多个字段时，会优先选择多个字段的联合索引，而不会选择多个单个索引 -- 最左前缀：使用联合索引有顺序要求，换言之联合索引的创建有字段顺序之分 -- 主键最好使用递增序列 -- name like ‘a%’ 和 LEFT(name,1) = 'a'，函数放在左边会使索引失效，类似还有计算和隐式类型转换等 -- 范围条件右侧的列索引失效：age=30 and id > 10 and name='zs'，使用 age,id,name 联合索引会使 name 失效，使用 age,name,id 联合索引就没问题 -- 不等于会使索引失效 -- is null(相当于等于某值)可以使用索引，is not null(相当于不等于某值)不能使用索引，同理 not like 也不能使用索引 -- like '%xxx' 索引失效 -- or 前后存在非索引列，索引失效，必须 or 前后都有索引才会使用索引去查找 -- 库和表字符集不同，索引失效，相当于有隐式转换
JOIN
-- 驱动表(作为外循环，最好用结果集小(表行数*每行大小)的作为驱动表，也就是用小表驱动大表)是主表，被驱动表(作为内循环)是从表、非驱动表。EXPLAIN 结果中上面是驱动表，下面是被驱动表 -- 表连接的实现方式有三种：Simple Nested-Loop Join 简单嵌套循环连接、Index Nested-Loop Join 索引嵌套循环连接、Block Nested-Loop Join 块索引嵌套连接。整体效率：INLJ > BNLJ > SNLJ -- Block Nested-Loop Join 设置：通过 show variables like '%optimizer_switch%' 查看 block_nested_loop(默认开启)。驱动表能否被一次加载完，要看 join_buffer_size，默认 256K。最大值在 32 位系统为 4G，在 64 位可大于 4G(但 Windows 只能为 4G） -- 从 MySQL 8.0.20 版本开始将弃用 BNLJ，引入 Hash Join，默认会使用 Hash Join -- left join 以左表为驱动表，right join 反之，STRAIGHT_JOIN 用在内连接中，强制使用左表当驱动表，改变 mysql 优化器选择的执行计划 -- 尽量用 join 替代子查询
排序、分组、分页
-- where 使用索引是为了避免全表扫描，order by 使用索引是为了避免 FileSort，而使用索引排序。where 和 order by 字段不同时可以建立联合索引。 -- 分页可以影响是否走索引，例如：select * from user order by age 不走索引(因为要全部回表)，select * from user order by age limit 3 走索引(回表数据少) -- order by 多个字段顺序不一致时索引会失效 -- where a in (...) order by b,c 不能使用 a,b,c 联合索引 -- sort_buffer_size 可提高效率 -- FileSort 分单路排序和双路排序，mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data -- order by 时不要使用 select * -- group by 中 where 效率高于 having
覆盖索引：查询的字段正好是建立索引的字段，这时 != 或 like ‘%xxx’ 条件在不回表的情况下也可能使用索引
索引条件下推：在回表之前进行过滤，没有回表也就不存在索引下推
