select语句检查

少使用UDF用户自定义函数

在select中使用自定义函数UDF，sql返回多少航，UDF就会调用多少吃，这是很影性能的

#getOrderNo是用户自定义一个函数用户来根据order_sn来获取订单编号
SELECT id, payment_id, order_sn, getOrderNo(order_sn) 
FROM payment_transaction 
WHERE STATUS = 1 
AND create_time BETWEEN '2020-10-01 10:00:00' AND '2020-10-02 10:00:00';

test类型检查

如果select出现test类型字段，会占用大量的网络和IO带宽，内容过大查过max_allowed_packer设置会导致程序报错

#表request_log的中content是text类型
SELECT user_id, content, STATUS, url, TYPE 
FROM request_log WHERE user_id = 32121;

慎用group_concat

group——concat是一个字符串聚合函数，会影响SQL响应时间，如果返回的值过大超过了max_allowed_packet设置会导致程序报错

select batch_id, group_concat(name) 
from buffer_batch 
where status = 0 
and create_time between '2020-10-01 10:00:00' and '2020-10-02 10:00:00';

内联子查询

 在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_time between '2020-09-02 10:00:00' and '2020-10-01 10:00:00';

from查询

表的连接方式

在MySQL中不建议使用Left Join，即使ON过滤条件列所有，一些情况也不会走索引，导致大量的数据被扫描，SQL性能不安差，要同时明报ON 和Where的区别

SELECT a.member_id,a.create_time,b.active_time 
FROM operation_log a LEFT JOIN member_info b ON a.member_id = b.member_id 
where  b.`status` = 1 
and a.create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00' 
limit 100, 0;

子查询

由于MySQL的基于成本的优化器CBO对子查询的处理能力较弱，不建议使用子查询，可以改成Inner Join

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_time between '2020-09-02 10:00:00' and '2020-10-01 10:00:00';

where查询

索引列被运算

当一个字段被索引，出现在where语句后，执行运算会导致索引使用失效

#device_no列上有索引，由于使用了ltrim函数导致索引失效
select id, name , phone, address, device_no 
from users where ltrim(device_no) = 'Hfs1212121';
#balance列有索引,由于做了运算导致索引失效
select account_no, balance 
from accounts where balance + 100 = 10000 and status = 1;

类型转换

对于int类型的资环，传varchar类型的值可以走索引，MySQL内部作了隐式类型转换；相对于varchar类型字段传入Int值是无法走索引的，应该做到对应的字段类型传对应的值

#user_id是bigint类型，传入varchar值发生了隐式类型转换，可以走索引。
select id, name , phone, address, device_no 
from users where user_id = '23126';
#card_no是varchar(20)，传入int值是无法走索引
select id, name , phone, address, device_no 
from users where card_no = 2312612121;

列字符集

从MySQL5.6开始建议所有对象字符集应该使用utf8mb4，包括MySQL实例字符集，数据库字符集，表字符集，列字符集。避免在关联查询join时字符集不匹配导致索引失效。目前只有utf8md4支持emoji表情储存

character_set_server  =  utf8mb4    #数据库实例字符集
character_set_connection = utf8mb4  #连接字符集
character_set_database = utf8mb4    #数据库字符集
character_set_results = utf8mb4     #结果集字符集

group by检查

前缀索引

group by后面有列的索引，索引可以消除排序带来的CPU开销，如果是前缀索引，则补鞥消除排序的开销

#device_no字段类型varchar(200)，创建了前缀索引。
mysql> alter table users add index idx_device_no(device_no(64));

mysql> select device_no, count(*) from users 
where create_time between '2020-10-01 00:00:00' 
and '2020-10-30 00:00:00' group by device_no;

函数运算

假设需要统计每天的新增用户，可以走create_time的索引，但是不能消除排序，可以考虑冗余一个字段stats_date_date类型来解决这种问题

select DATE_FORMAT(create_time, '%Y-%m-%d'), count(*) from users 
where create_time between '2020-09-01 00:00:00' and '2020-09-30 23:59:59' 
group by DATE_FORMAT(create_time, '%Y-%m-%d');

order by检查

前缀索引

order by 后面的列有索引，索引可以消除排序带来的CPU开销，如果是前缀索引，是不能消除排序的

字段顺序

排序字段顺序，asc/desc升降要和索引保持一致，充分利用索引的有序性来消除排序带来的cpu开销

limit检查

limit m，n分页查询，越往后m越大的情况下SQL耗时会越来越常，这种情况应该先取出id，然后通过之间id跟原表进行Join关联查询

表结构检查

表和列的关键字

不要使用MySQL的关键字，如desc，order，status，group等，同时建议设置lower_case_table_names=1,表明不区分大小写

表储存引擎

对于OLTP业务系统，建议使用InnoDB引擎获取更好的性能，可以通过参数default_storage_engine控制

AUTO_INCREMENT属性

建表的时候主键带有自增属性，且自增为1时，在InnoDB中是用过一个全局变量dict_sys.row_id来计数，row_id是一个8字节的bigint unsigned，InnoDB在设置时值给row_id保留了6个字节的长度，即0~2^48-1,如果id到达了最大值，下一个值就从0来世递增，代码中键值指定主键id插入

#新插入的id值会从10001开始，这是不对的，应该从1开始。
create table booking( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',......) engine = InnoDB auto_increment = 10000;

#指定了id值插入，后续自增就会从该值开始+1，索引禁止指定id值插入。
insert into booking(id, book_sn) values(1234551121, 'N12121');

NOT NULL属性

根据衣物含义，尽量将字段上都添加上NOT NULL DEFAULT VALUE属性，如果列值存储了大量的null会影响索引的稳定性

DEFAULT属性

创建表的时候尽量每个字段都有默认值，禁止DEFAUT NULL，而是对制度按类型填充响应的默认值

COMMENT属性

备注对应字段的作用，要明显的写出该字段可能的状态以及数值的含义

TEXT类型

不建议使用该类型，可能会导致数据量过大报错，另一方面表上的DML操作会变的很慢

索引方面

索引基数指的是被索引的列唯一值的个数，唯一值越多接近表的count(*)说明索引的选择率越高，通过索引扫描的行数就越少，性能就越高，例如主键id的选择率是100%，在MySQL中尽量所有的update都使用主键id去更新，因为id是聚集索引存储着整行数据，不需要回表，性能是最高的。

mysql> select count(*) from member_info;
+----------+
| count(*) |
+----------+
|   148416 |
+----------+
1 row in set (0.35 sec)

mysql> show index from member_base_info;
+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name                   | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| member_info |          0 | PRIMARY                    |            1 | id                | A         |      131088 | NULL     | NULL   |      | BTREE      |         |               |
| member_info |          0 | uk_member_id               |            1 | member_id         | A         |      131824 | NULL     | NULL   |      | BTREE      |         |               |
| member_info |          1 | idx_create_time            |            1 | create_time       | A         |        6770 | NULL     | NULL   |      | BTREE      |         |               |
+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
#Table： 表名
#Non_unique ：是否为unique index，0-是，1-否。
#Key_name：索引名称
#Seq_in_index：索引中的顺序号，单列索引-都是1；复合索引-根据索引列的顺序从1开始递增。
#Column_name：索引的列名
#Collation：排序顺序，如果没有指定asc/desc，默认都是升序ASC。
#Cardinality：索引基数-索引列唯一值的个数。
#sub_part：前缀索引的长度；例如index (member_name(10)，长度就是10。
#Packed：索引的组织方式，默认是NULL。
#Null：YES:索引列包含Null值；'':索引不包含Null值。
#Index_type：默认是BTREE，其他的值FULLTEXT，HASH，RTREE。
#Comment：在索引列中没有被描述的信息，例如索引被禁用。
#Index_comment：创建索引时的备注。