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’ 条件在不回表的情况下也可能使用索引
索引条件下推:在回表之前进行过滤,没有回表也就不存在索引下推