SQL优化

sql慢日志

• 查询与开启慢日志

    查询慢日志是否开启：
    show VARIABLES like '%quer%'
    
    查询慢日志记录：
    show status like '%Slow_queries%'
    
    开启慢日志：
    set global slow_query_log=on;
    set global long_query_time=1;
    
    explain工具分析sql：
    explain select TITLE from t_oa_dispatch_auto where title like 'd5%'
  

mysql连接报Failed to initialize pool: Public Key Retrieval is not allowed

jdbc后面加参数：&allowPublicKeyRetrieval=true即可：

如：
jdbc:mysql://localhost:3307/cloudalibaba?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true&allowMultiQueries=true&allowPublicKeyRetrieval=true

mysql更改表引擎InnoDB和表字符集编码utf8mb4

• mysql更改引擎为InnoDB

SELECT CONCAT( 'ALTER TABLE ' ,TABLE_NAME ,' ENGINE=InnoDB; ')
AS target_tables
FROM information_schema.TABLES AS t 
WHERE TABLE_SCHEMA = 'myDB_Name' AND TABLE_TYPE = 'BASE TABLE';
#myDB_Name 修改为需要更改的数据库名称

• myswl更改编码为utf8mb4

  SELECT 
  CONCAT("ALTER TABLE `", TABLE_NAME,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") 
  AS target_tables
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA="myDB_Name" # myDB_Name 修改为需要更改的数据库名称
  AND TABLE_TYPE="BASE TABLE"