mysql

1、mysql死锁

2、查看时区：

show variables like "%time_zone%";

3、mysql datetime字段使用DATE_FORMAT()函数，我是链接

4、MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . TheTIMESTAMP data type is used for values that contain both date and time parts.TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

5、linux没有设置systemctl服务的话，需要去安装的bin下执行nohup mysqld &；

6、

解决报错:Error Code: 1786 Statement violates GTID consistency: CREATE TABLE ... SELECT.

 

7、简单的游标定义的存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `savexxxxx`(
in position_ids varchar(10),
in project_id bigint,
out exe_result int
)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE facility_id,tmp_i BIGINT DEFAULT 0;
DECLARE cur1 CURSOR FOR select d.id from tb_a d,tb_b t where d.facility_type = t.id and t.need_rel_area = 1 and d.position_id = position_ids;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

select position_ids;
select project_id;

OPEN cur1;

REPEAT
FETCH cur1 INTO facility_id;

IF NOT done THEN
#select facility_id;
INSERT INTO tb_project_item(project_id,type,type_id) VALUES (project_id,3,facility_id);
END IF;
UNTIL done END REPEAT;

CLOSE cur1;
select count(*) from tb_c into exe_result;
END

mysql shell

https://dev.mysql.com/downloads/shell/

mysql表结构中关于时间字段可以同时存在一个datetime字段，一个unix time的long值字段

mysql sql查询执行分析

mysql索引原理

测试环境的5.7.24版本的my.cnf设置：

#my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
prompt="\u@\h [\d]>"
#pager="less -i -n -S"
#tee=/opt/mysql/query.log
no-auto-rehash

[mysqld]
federated
sql_mode=''
user = mysql
basedir = /usr/local/mysql
datadir = /data1/mysql3306/data
port = 3306
socket = /tmp/mysql.sock
event_scheduler = on
tmpdir = /data1/mysql3306/tmp
skip_name_resolve = 1
disable-partition-engine-check
log_timestamps = SYSTEM
max_allowed_packet = 20M
wait_timeout=86400
interactive_timeout = 86400
#character set
character-set-server = utf8mb4
log_warnings = 1

lower_case_table_names=1

max_connections = 1000
max_connect_errors = 100000

#logs
slow_query_log = 1
slow_query_log_file = slow.log
lock_wait_timeout = 3600
log-error = error.log
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1

binlog_format = row
server-id = 173306
log-bin = /data1/mysql3306/logs/mysql-bin
sync_binlog = 1
expire_logs_days = 7
log_bin_trust_function_creators=1

#slave setup
gtid_mode = on
enforce_gtid_consistency = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
skip_slave_start = 1
relay_log_purge = 1
relay_log_recovery = 1
relay-log=relay-bin
relay-log-index=relay-bin.index
log_slave_updates

#buffers & cache
query_cache_size = 0
query_cache_type = 0


#innodb
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_file_size = 100M
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 1G
innodb_max_undo_log_size = 1M
innodb_buffer_pool_size = 200M
innodb_flush_log_at_trx_commit = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 100
innodb_io_capacity_max = 100
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

mysql group by分组查询count时，对count(null)得到结果值是0，结合case when then可以查询多个字段count的值:

SELECT
CONVERT ( from_base64 ( u.true_name ) USING utf8 ) AS userName,
count(
	CASE
		WHEN 
			a.origin_status = 1 THEN NULL
		ELSE
			a.origin_status
		END) as autoCount,
	count(
		CASE
		when 
			a.origin_status = 1 then null
		WHEN 
			a.check_status = '0' THEN NULL
		ELSE
			a.check_status
		END
	) as autoCompleteCount,
	count(
	CASE
		WHEN 
			a.origin_status = 0 THEN NULL
		ELSE
			a.origin_status
		END) as selfCount
FROM
	tb_a a,tb_sys_user u
	where 
	a.check_person = u.id and 
	DATE_FORMAT( a.create_date, '%Y' ) = 2019 
	AND DATE_FORMAT( a.create_date, '%m' ) = 12 
GROUP BY
	u.true_name,u.id;

　　

https://dev.mysql.com/doc/refman/5.7/en/case.html

2020-01-13

mysql在查询字段中使用select查询获取结果，很有用

01-15

使用float字段类型时，需要注意设置字段的小数点精度；

慕课网mysql优化，还有可以去参考阿里巴巴java开发手册中相关内容

其他资料1

explain返回各列的含义：

type：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。

possible_keys：可能使用到的索引。

key：实际使用到的索引。

key_len：当前使用的索引的长度，不损失精度的情况，越小越好

ref：关联 id 等信息。

rows：查找到记录所扫描的行数。

filtered 过滤掉数据占所有数据的比例

extra列需要注意的返回值：

Using filesort:看到这个，查询就需要优化了。Mysql需要进行额外的步骤来发现如何对返回的行排序。

Using temporary: 需要优化，Mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行order by上，而不是group by 上；

#eg1:
select max(payment_date) from payment;
#通过对payment_date建立索引，提高查询效率
create index id_paydate on payment(payment_date);

#正确
select count(release_year = '2006' or null) as '2006film_count',
count(release_year = '2007' or null) as '2007file_count' from file;

子查询的优化：

　　通常情况下，需要把子查询优化为join查询，但在优化时需要注意关联键是否有一对多关系，要注意重复数据。

explain select title,release_year,LENGTH FROM film where film_id 
in (select film_id from file_actor where actor_id in (select actor_id from actor where first_name = 'sandra'));

select * from t where t.id in (select t1.tid from t1);
select t.id from t join t1 on t.id = t1.tid;

sql数据库优化

explain select actor.first_name,actor.last_name,count(*) from 
sakila.film_actor inner join sakila.actor using(actor_id) group by file_actor.actor_id;
#优化后  参演电影最多的演员
explain select actor.first_name,actor.last_name,c.cnt from sakila.actor inner join(select actor_id,count(*) as cnt from sakila.film_actor group by actor_id) as c using (actor_id);

sql优化limit查询，时常会伴随order by从句使用，因此大多数时候会使用filesorts，这样会造成大量的IO问题。

select film_id,desc from sakila.film order by title limit 50,5;

这个查询的优化步骤1：使用有索引的列或者主键进行order by 操作

select film_id,desc from sakila.film order by id limit 50,5;

优化步骤2：记录上一次返回的主键，在下次查询时使用主键过滤(中心思想：避免了数据量大时扫描过多的记录)：

select film_id,desc from sakila.film where film_id > 600 and file_id <=605 order by film_id limit 1,5;

sql及索引优化：

如何选择合适的列建立索引？

1、在where从句，group by 从句,order by 从句，on从句中出现的列

2、索引字段越小越好

3、离散度大的列放在联合索引的前面

select * from payment where staff_id = 2 and customer_id = 584;

由于customer_id的离散度更大，所以应该使用index(customer_id,staff_id)

判断离散度的方法

select count(customer_id),count(staff_id) from payment

哪个大，哪个离散度大

索引的维护和优化：

--查找重复和冗余的索引：

pt-duplicate-key-checker -u root -p '' -h 127.0.0.1

--删除不用索引

mariadb中查看index_statistics表，mysql中通过慢日志配合pt-index-usage工具来进行索引使用情况分析

pt-index-usage -u root -p '' mysql-slow.log

数据库结构优化

　　选择合适的数据类型：

　　1、选在最小的数据类型

　　2、使用简单的数据类型；

　　3、使用not null定义字段

　　4、少于text类型；

create table test(id int auto_increament not null,timestr int,primary key(id));
insert into test(timestr) values (unix_timestamp('2014-06-01 13:12:00'));
select from_unixtime(timestr) from test

可以使用bigint来存ip地址，使用inet_aton(),inet_ntoa()两个函数来进行转换；

https://tech.meituan.com/2017/03/09/sqladvisor-pr.html

https://github.com/Meituan-Dianping/SQLAdvisor

mysql日期比较

and unix_timestamp(end_time) > unix_timestamp(NOW());