[大数据]MYSQL篇-04-索引创建原则&SQL分析&MYSQL优化

六、MySQL 索引创建原则

1、复合索引规则（等值/范围）

①、将范围查询的列放在复合索引的最后面。

②、列过滤的频繁越高，选择性越好，应该作为复合索引的前导列，适用于等值查找。

2、跳跃索引

一般情况下，如果表有复合索引idx_status_create_time，我们都知道，单独用create_time去查询，MySQL优化器是不走索引，所以还需要再创建一个单列索引idx_create_time。其中Oracle是可以走索引跳跃扫描（Index Skip Scan），在MySQL 8.0也实现Oracle类似的索引跳跃扫描，在优化器选项也可以看到skip_scan=on。

--- 关闭索引跳跃扫描特性
optimizer_switch='skip_scan=off'

七、MySQL sql分析

1、使用Explain工具分析

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UcKgr9e6-1662476964371)(https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/36a2c52d7ebf42c89b740ced0dccadb3~tplv-k3u1fbpfcp-zoom-1.image)]

2、使用Trace分析

大部分即使添加了索引也不定会走，因此需要根据trace来分析，开启这个会影响sql的效率，是有使用的时候才开起它。

先执行sql语句，再查询表（INFORMATION_SCHEMA.OPTIMIZER_TRACE）查看他的分析结果。

-‐‐ 开启trace
set session optimizer_trace="enabled=on",end_markers_in_json=on; 
--- 查看具体SQL
select tal_id from rental where 1=1
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE

--- 查看跟踪结果
SELECT?trace?FROM?information_schema.OPTIMIZER_TRACE\G;

-‐‐ 关闭trace
set session optimizer_trace="enabled=off"

--- 强制索引
SELECT  merchant_id, plan_id, bs_type,SUM(amount) sum_total
        FROM bill_summary  FORCE INDEX(tk_union_first)
        
--- trace结果集分析
https://blog.csdn.net/yanghao8866/article/details/107826931

分析结果中包含了为什么走不走索引的原因，分析了各种情况的成本。会结合数据量，效率等情况

分析文件：

\

①、order by limit 造成优化器选择索引错误

https://developer.aliyun.com/article/51065

3、关于FileSort文件排序

系统变量：判断使用那种排序（max_length_for_sort_data）

 OPTIMIZER_TRACE_MAX_MEM_SIZE=1024 

分类：单路排序：查询的字段是没有查过指定的大小（默认是1024），会把查询的字段取出来放在内存或者磁盘上排序。

双路排序：查询的字段大于指定的指定的大小，把查询的主键取出来。其中都是放入到sort buffer中进行排序。去除主键还会回表进行查询。

Using filesort文件排序原理详解 filesort文件排序方式

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer（1M）中进行排序；用trace工具可 以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >

双路排序(又叫回表排序模式)：是首先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段;用trace工具 可以看到sort_mode信息里显示< sort_key, rowid >MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来 判断使用哪种排序模式。

如果 字段的总长度小于max_length_for_sort_data ，那么使用单路排序模式; 如果字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模式。

4、索引设计原则

1、代码先行，索引后上

2、联合索引尽量覆盖条件

3、不要在小基数字段上建立索引

4、长字符串我们可以采用前缀索引

例如：index(name(20),age,position)。

5、where与order by冲突时优先where

6、基于慢sql查询做优化

关于慢sql查询不清楚的可以参考这篇文章:https://blog.csdn.net/qq_40884473/article/details/89455740

5、索引失效的情况

6、SQL优化注意点

SQL优化建议索引不超过六个

注意：索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中)：

7、SQL字段建议规范

①、MySQL–Null的原理

疑问：判断null 使用is null 而不是使用 =null

原理：因为 null 是会在字段后面开辟空间，存放标记位所以判断的是标记位不是数值。

优化建议：所以尽量使用not null 并且设置默认值。除了test，long等

datetime—日期类型使用 1970-01-01 00:00:00；varchar使用’'(匹配字符串是从左到右，很高效)；

七、MYSQL优化SQL例子

1、select查找存不存在

优化前

select count(*) from table where ;

优化后

select 1 from table where … limit 1;

2、分页优化

3、Join关联查询算法

mysql的表关联常见有两种算法（小表驱动大表）

Nested-Loop Join 算法：一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

Block Nested-Loop Join 算法：把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。(没有采用索引，采用第一种方法，可能会导致查询一条就全表扫描不划算。）

4、in和exists的区别：

1、 in()适合B表比A表数据小的情况。

2.、exists()适合B表比A表数据大的情况。

5、count(*)查询优化

其中：count(1) 约= count(*) > count(name) > count(id)

如果name加了索引的话比id的快，否则id快

count(id)，会扫描主键索引数，那非主键的索引数的数据量小反而更快，现在count（id）自动优化使用非主键索引常见优化方法

6、查询mysql自己维护的总行数

1、MyISAM存储引擎：其中不带where条件的count查询性能是很高的。MyISAM存储引擎的表的总行数会被mysql存储在磁盘上。

2、需要知道表总行数：

show table status

show table stauts like"table"

3、将总数维护到Redis里：插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性

4、增加计数表：插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

7、Order by与Group by优化

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

2、order by满足两种情况会使用Using index。

1. order by语句使用索引最左前列。

2. 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立(索引创建的顺序)时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。

7、GROUP BY 条件字段必须在同一个索引中最前面的连续位置；在使用GROUP BY 的同时，只能使用 MAX 和 MIN 这两个聚合函数

8、int类型长度的作用

补o，如果是11 011

 id` TINYINT(2) UNSIGNED ZEROFILL 

9、超时问题（四种情况）

connect timeout：建立数据库连接超时
建立连接超时

socket timeout：socket读取超时
慢sql

statement timeout：单个sql执行超时

transaction timeout：事务执行超时，一个事务中可能包含多个sql
事务超时

get connection timeout：从连接池中获取链接超时
获取连接超时

10、字符编码格式（表情问题）

--- 表
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='应用表';
--- 字段
ALTER TABLE `t_order`
MODIFY COLUMN `remark`  varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '订单备注'

--- 查看数据库编码show variables like '%char%'
show variables like '%char%'
--- 设置编码格式

set character_set_server=utf8mb4


character_set_client	(客户端来源数据使用的字符集)
character_set_connection	(连接层字符集)
character_set_database	(当前选中数据库的默认字符集)
character_set_results	(查询结果字符集)
character_set_server	(默认的内部操作字符集)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HVG1aBpa-1662476964372)(https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/99acdee25bcb4c3ea68fadcc172ce1d6~tplv-k3u1fbpfcp-zoom-1.image)]

八、MYSQL常用命令

MYSQL 经纬度字段类型

INSERT INTO my_user ( point,id ) VALUES ( ST_GeomFromText(‘POINT(1 1)’),1);

SELECT id ,ST_X(point) x ,ST_Y(point) y ,point FROM my_user;

UPDATE my_user SET point = ST_GeomFromText(‘POINT(1337 125)’) where id =1;