mysql随笔

//每个查询自动提交事务
show variables  like 'AUTOCOMMIT'
//查询表信息（数据库引擎，是否压缩等）
show table status  like 'command'
//更新表统计信息
OPTIMIZE TABLE command;
//显示查询耗时
show profiles
//显示单个操作详情
show profile for query 111

//索引优化
给一个很长的字符串优化查询速度，可以增加一列crc校验值，然后通过校验值进行优化
当我们索引的字段是很长的字符串时，可以用到前缀索引

select * from payment where  staff_id=2 and customer_id=584;
select sum( staff_id=2),sum(customer_id=584) from payment;
select count(DISTINCT staff_id )/count(*) ,count(DISTINCT customer_id )/count(*) from payment

创建聚簇索引必须使用btree结构
返回列必须和索引列一致
EXPLAIN select username ,userpwd  from user
ALTER TABLE user ADD INDEX indexusernamepwd (username(64),userpwd(64));

写入百万条数据
truncate  user;

drop PROCEDURE writeTest;

CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `writeTest`()
BEGIN
   DECLARE username VARCHAR(60);
   DECLARE userpwd VARCHAR(150);
   DECLARE id VARCHAR(120);
   DECLARE i INT DEFAULT 51;

   WHILE i <= 30000 DO
     SET username = "Sean Carrey";
     SET userpwd = CONCAT('111Apollo', i);
     SET id = i;
     INSERT INTO  `user`
     VALUES(id,
       username,
       userpwd
      );
     SET i=i+1;
     END WHILE;
END;

call writeTest;

order by 组合索引优化
drop PROCEDURE writerental;

truncate rental ;

CREATE  PROCEDURE `writerental`()
BEGIN
   DECLARE rental_id VARCHAR(64);
   DECLARE inventory_id VARCHAR(64);
     DECLARE customer_id VARCHAR(64);
   DECLARE i INT DEFAULT 1;

   WHILE i <= 1000000 DO
     SET rental_id =CONCAT('rental_id', i);
         SET inventory_id =CONCAT('inventory_id', i);
         SET customer_id =CONCAT('customer_id', i);
     INSERT INTO  `rental`
     VALUES(rental_id,
         NOW(),

             inventory_id,
             customer_id
    
      );
     SET i=i+1;
     END WHILE;
END;

call writerental;

select * from rental;

ALTER TABLE rental ADD  UNIQUE INDEX rental_date (rental_date,inventory_id, customer_id);

ALTER TABLE rental drop INDEX rental_date;
truncate user;

select LENGTH(rental_date) from rental;

EXPLAIN  select rental_date, inventory_id, customer_id from rental   order by rental_date desc, inventory_id asc,customer_id desc;

 select rental_date, inventory_id, customer_id from rental  where rental_date='2022-02-17 14:27:03'    order by rental_date, inventory_id, customer_id

order by 列顺序和组合索引列顺序保持一致，不管列是asc或者desc(rental_date desc, inventory_id asc,customer_id desc)都可以使用索引排序

索引越多，入库越慢

重新进行表统计信息的三种方法
show index from rental;

optimize table rental;

alter table rental ENGINE=INNODB

select count(*) from rental;

truncate rental;

//存储序列化修改
check table rental;

repair table rental;
//获取索引信息
show index from rental;

optimize table rental;
//切换表引擎
alter table rental ENGINE=INNODB;

//mysql5.7对子查询有优化，解决了mysql子查询智障的问题
explain select * from rental where rental_id in (
select rental_id from rental_copy1  where customer_id like '%customer_id%'
);

//count(*) 优化
select count(*) from  rental where rental_id >'rental_id2'
alter table rental ENGINE=myisam;
select (select count(*) from  rental)-count(*) from  rental where rental_id <='rental_id2'

//mysql自定义变量，只能是单个数据，不能是多行，不能是多列
SET @one :=1;
SET @sqllist :=(select min(rental_id) from rental LIMIT 10);
select @sqllist from rental limit 10 ;
//创建分区表
DROP TABLE IF EXISTS `partition`;
CREATE TABLE `partition`  (
  `rental_id` varchar(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `rental_date` datetime(0) NULL DEFAULT NULL,
  `inventory_id` varchar(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `customer_id` varchar(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  INDEX `componentindex`(`customer_id`, `rental_date`) USING BTREE
) ENGINE=INNODB
PARTITION BY RANGE(YEAR(rental_date))(
PARTITION p_2010 VALUES LESS THAN (2010),
PARTITION p_2011 VALUES LESS THAN (2011),
PARTITION p_2012 VALUES LESS THAN (2012),
PARTITION p_catchcall VALUES LESS THAN MAXVALUE
);
//mysql支持的字符集和规则查询
show  CHARSET;
show collation;

//mysql给数据库，表，列设置字符集，并查看
CREATE DATABASE D CHARSET latin1;

CREATE TABLE D.t(
col1 CHAR(1),
col2 CHAR(1) CHARSET utf8,
col3 CHAR(1) COLLATE latin1_bin

)DEFAULT CHARSET=cp1251

SHOW FULL COLUMNS FROM D.t

//使用navicat创建mysql全文索引
CREATE TABLE article (

id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT,

FULLTEXT (title,body) WITH PARSER ngram

) ENGINE=InnoDB CHARACTER SET utf8mb4;

SELECT title ,body, RIGHT(body,25),RIGHT(title,25),MATCH(title,body) AGAINST('factory casualties') as relevance from article where
MATCH(title,body) AGAINST('factory casualties');

//查询缓存应该默认关闭
linux中mysql的配置文件路径

Linux系统查找my.cnf路径

which mysqld

/usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'

/etc/mysql/my.cnf --是真正的配置路径

给字段设立字符集
ALTER TABLE governance_model_install CHANGE resultjobdag resultjobdag text(0) CHARACTER SET utf8;