在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';
如果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是一个字符串聚合函数,会影响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';
在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语句后,执行运算会导致索引使用失效
#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后面有列的索引,索引可以消除排序带来的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 后面的列有索引,索引可以消除排序带来的CPU开销,如果是前缀索引,是不能消除排序的
排序字段顺序,asc/desc升降要和索引保持一致,充分利用索引的有序性来消除排序带来的cpu开销
limit m,n分页查询,越往后m越大的情况下SQL耗时会越来越常,这种情况应该先取出id,然后通过之间id跟原表进行Join关联查询
不要使用MySQL的关键字,如desc,order,status,group等,同时建议设置lower_case_table_names=1,表明不区分大小写
对于OLTP业务系统,建议使用InnoDB引擎获取更好的性能,可以通过参数default_storage_engine控制
建表的时候主键带有自增属性,且自增为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 DEFAULT VALUE属性,如果列值存储了大量的null会影响索引的稳定性
创建表的时候尽量每个字段都有默认值,禁止DEFAUT NULL,而是对制度按类型填充响应的默认值
备注对应字段的作用,要明显的写出该字段可能的状态以及数值的含义
不建议使用该类型,可能会导致数据量过大报错,另一方面表上的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:创建索引时的备注。