mysql优化

mysql:
1、查询语句有多个属性时，sql执行时需要排序的，但是in单个条件时，是不需要排序的
2、分页limit过大，导致大量排序，可以记录上一夜最后的id，下一页查询时，查询条件带上id，如where id > 上一页最后id limit 10，也可以在业务允许的情况下限制页数
3、索引存储顺序和order by 不一致，如何优化  联合索引（age,name）查询前10个学生的姓名，年龄，别且按照年龄从小到大排序，如果年龄相同，则按照姓名排序：对应的sql 
select name,age from student order by age,name desc limit 10; explain一下，发现走了排序过程，因为索引存储孙旭和order by不一致导致的。descending indexes ：key `idx_age_name` (`age`,`name` desc) useing BTREE
4、日常开发中，可能遇到没有wher条件的order by，即使字段有索引，也不会使用到，因为mysql优化器认为普通耳机索引，再去回表的成本高于全表扫描排序，所以选择走全表扫描，（select * from student order by create_time limit m ）然后根据全字段排序或者时rowid排序来进行，而无条件查询,如果m值较小，是可以走索引的，因为mysql优化器认为，根据索引有序性去回表查数据，然后得到m条数据，就可以终止循环，那么成本比全表扫描小，则选择走二级索引

sql优化点：
####sql规范性检查
一、select检查
1、udf用户自定义函数
select后面使用自定义函数udf，sql返回多少行，那么udf函数就会被调用多少次，这是非常影响性能的
2、text类型检查
select中出现text类型的字段，就会消耗大量的网络和io带宽，由于返回的内容大大超过max_allowed_packet设置回导致程序报错，需要评估谨慎使用
select user_id, content, status,url,type from request_log where user_ids = 123;
3、group_contact谨慎使用
group_contact是一个字符串聚合函数，会影响sql的相应时间,如果返回的值过大超过了max_allowed_packet设置会导致程序报错
select batch_id,group_contact(name) from buffer_batch where status = 0 and creat_time between '2021-10-01 10:00:00' and '2021-10-02 10:00:00';
4、内联子查询
在select后面有子查询的情况城为内联子查询，sql返回多少行，子查询就需要执行多少次，严重影响sql性能
select id，(select rule_name from member_rule limit 1) as rule_name,member_id,member_type,member_name,status from member_info m where status = 1 and create_rime between '2021-09-01 09:00:00' and '2021-09-03 9:00:00'
二、from检查
1、表的链接方式
在mysql中不建议使用left join，即使on过滤条件列索引，一些情况也不会走索引，导致大量的数据行被扫描，sql性能变得很差，同事要清除on和where的区别
select a.member_id,a.create_time from operation_log a left join member_info b on a.member_id = b.memeber_id where b.status = 1 and a.create_time between '2020-09-01 00:00:00' and 
'2020-10-02 00:00:00' limit 100,0;
2、子查询
由于MySQL的基于成本的优化器cbo对于子查询的处理能力比较弱，不建议使用子查询，可以改成inner join
select b.member_id,b.member_type,a.create_time,a.device_model from member_operation_log inner_log inner join (select member_id,member_type from member_base_info where status =1 and
create_time between '2020-10-01 00:00:00' and '2020-10-02 00:00:00') as b a.member_id = b.member_id;
三、where检查
1、索引列被运算
当一个字段被索引，同时出现where谈条件后面，是不能进行任何运算，会导致索引失效。
#device_no列上有索引，由于使用了ltrim函数导致索引失效
select id，name,phone,address,device_no from users where ltrim(device_no) = '123';
#balance列有索引，由于做了运算导致索引失效
select account_no,balance from accounts where balance + 100 == 10000 and status = 1;
2、类型转换
对于int类型的字段，传varchar类型的值是可以走索引，mysql内部自动做了隐式类型转换，相反对于varchar类型字段传入int值是无法走索引的，应该做到对应的字段类型传对应的值总是对的
#user_id是bigint类型，传入varchar值发生了隐式类型转换，可以走索引。
select id，name,phone,address,device_no from users where user_id = '123';
#card_no是varchar(20)，传入int值是无法走索引的
select id,name,phone,address,device_no from users where card_no = 21112111;
3、列字符集  
从mysql5.6开始建议所有对象字符集应该使用utf8mb4，包括mysql实例字符集，数据字符集，表字符集，列字符集。避免在关联查询join时字段字符集不匹配导致索引失败，同时目前只有utf8mb4支持emoji表情存储
四、group by检查
1、前缀索引
group by后面的列有索引，索引可以消除排序带来的cpu开销，如果是前缀索引，是不能消除排序的
alter table users add index idx_device_no(device_no(64));
select device_no,count(*),from users where create_time between '2021-10-01 00:00:00' and '2021-10-02 00:00:00' group by device_no;
2、函数运算
假设需要统计某个月每天的新增用户量，参考如下sql语句，虽然可以走create_time的索引，但是不能消除排序，可以考虑冗余一个字段stats_date date类型来解决这种问题。
select DATE_FORMAT(create_time,'%y-%m-%d')，count(*), from users where create_time between '2021-10-01 00:00:00' and '2021-10-02 00:00:00' group by DATE_FORMAT()create_time.'%y-%m-%d');
五、order by检查
1、前缀索引
order by后面的列有索引，索引可以消除排序带来的cpu小号，但是前缀索引不能消除排序
2、字段顺序
排序字段顺序，asc/desc升降要跟索引保持一致，充分利用索引的有序性来消除排序带来的cpu开销
六、limit检查
1、limit m.n要慎重
对于limit m,n分页查询，越往后面翻页即m越大的情况下sql的耗时会越来越长，对于这种应该先取处主键id，然后通过逐主键id跟原表进行join关联查询
####表机构索引检查
1、表&列名关键字
在数据库设计建模阶段，对表名及字段名设计要合理，不能使用mysql关键字，如desc，order,status,froup等。同时建议设置lower_case_table_names=1表明不区分大小写 
2、表存储引擎
对于oltp业务系统，建议使用innodb引擎获取更好的性能，可以通过参数default_storage_engine控制。
3、auto_increment属性
建表的时候主键id带有auto_increment属性，而且auto_increment=1,在innodb内部通过一个系统全局变量dice_sys.row_id来计数，row_id是一个8字节的bigint unsigned，innodb在设计时只给row_id保留6个字节的长度，这样子row_id取值范围就是0-2^48-1,如果id的值达到了最大值，下一个值就从0开始继续循环递增，在代码中禁止指定主键id值插入
4、default属性
在创建表的时候，建议每个字段尽量都有默认值，禁止default null,而是对字段类型填充效应的默认值
5、comments属性
字段的备注要明确该字段的作用，尤其时么欧系表示状态的字段，需要显式的写出该字段所有可能的状态数值以及数值的含义
6、text类型
不建议使用text数据类型，一方面由于传输大量的数据包可能回超过max_allowed_packet设置导致程序报错，另一方面表上的dml操作都会变的很慢，建议采用es或者对象存储oss来存储和检索
####索引检查
1、索引属性
索引基数指的是被索引的列唯一只的个数。唯一值越多接近表的count(*)说明索引的选择率越高，通过索引的扫描的行数就越少，性能就越高，例如主键id的选择率时100%，在mysql中尽量所有的update都使用主键id去更新，因为id时聚集索引存储者整行数据，不需要回表，性能最高。
show index from member_base_info;
#table:表名
#non_unique:是否为unique index：0-是，1-否
#key_name:索引名称
#seq_in_index:索引中的顺序号，单列索引-都是1，符合索引-根据索引列的顺序从1开始递增
#colume_name:索引的列名
#collation:排序顺序，如果没有指定asc/desc，默认是升序asc
#sub_port:前缀索引的长度，例如index(member_name(10),长度是10)
#packed:索引的组织方式，默认是null
#null:yes:索引列包含null值，'':索引列不包含null值
#index_type:默认是btree，其他值：fulltext，hash，rtree
#comment:在索引列中设置没有描述的信息，例如索引被禁用
#index_comment:创建索引时的备注
2、前缀索引
对于边长字符串类型varchar(m),为了减少key_len，可以考虑创建前缀索引，但是前缀索引不能消除group by，order by带来排序开销，如果字段的实际最大值比m小很多，建议缩小字段长度
alter tanle member_info add index idx_member_name_part(member_name(10);
3、复合索引顺序
有很多人喜欢在创建符合索引的时候，总以为前导列一定时唯一值多的列，例如索引index idx_create_time_status(create_time,status),这个索引往往是无法命中，因为扫描的io次数太多，总体的cost比全表扫描还大，cbo最终选择的时full table scan
mysql遵循的是索引最左匹配原则，对于复合索引，从左到右依次扫描索引，遇到第一个范围查询(>=,>,<,between....and....)就停止扫描，索引正确的索引顺序应该是index idx_status_create_time(status,create_time)。
select account_no,balance from accounts where status = 1 and create_rime_time between '2021-10-01 00:00:00' and '2021-10-02 00:00:00';
4、时间列索引
对于默认字段create_at(create_time).update_at(update_time)这种默认就应该创建索引，这一般来说是默认的规则。