앨리스래빗팀 기술블로그

[MYSQL] 인덱스 활용

개별변경ALTER TABLE PROJECT ADD INDEX idx_is_deleted (is_deleted);일괄변경SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ADD INDEX idx_is_deleted (is_deleted);') FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'is_deleted' 검사SHOW INDEX FROM PROJECT;EXPLAIN SELECT * FROM PROJECT WHERE is_deleted = 0;

현재 트랜잭션 항목 확인SELECT * FROM information_schema.innodb_trx; 해당 트랜잭션 클로즈KILL

SELECT VERSION();MYSQL의 경우 예시8.0.34MariaDB의 경우 예시10.5.12-MariaDB

인덱스 확인 SHOW INDEX FROM USER_POINT; 결과Table |Non_unique|Key_name |Seq_in_index|Column_name |Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|Visible|Expression|----------+----------+-------------------+------------+-------------------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+USER_POINT| ..

ORDER BY FIELD(task_status, 'new', 'checked', 'started', 'completed')

Decimal 고정 소수법 / 십진법 / 정확함Double 부동 소주 / 범위가 큼

mariadb -h '' -u '' -P -p

RDMS Tool 이 아닌 터미널에서 RDS 인스턴스에 직접 접속하고 싶은 경우https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToInstance.html Connecting to a DB instance running the MySQL database engine - Amazon Relational Database ServiceConnecting to a DB instance running the MySQL database engine Before you can connect to a DB instance running the MySQL database engine, you must create a DB instance. For i..

SELECT COLUMN_NAME AS 'field_name', COLUMN_COMMENT AS 'comment', COLUMN_TYPE AS 'type', COLUMN_KEY AS 'KEY', IS_NULLABLE AS 'NULL', EXTRA AS 'auto', COLUMN_DEFAULT 'default' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '테이블명' AND TABLE_SCHEMA = '스키마명' AND COLUMN_NAME != 'created_at' AND COLUMN_NAME != 'updated_at' AND COLUMN_NAME != 'is_deleted' ORDER BY TABLE_NAME, ORDINAL_POSITION;

INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100; https://zetawiki.com/wiki/SQL_SELECT_%EA%B2%B0%EA%B3%BC%EB%A5%BC_INSERT_%ED%95%98%EA%B8%B0 SQL SELECT 결과를 INSERT 하기 - 제타위키 다음 문자열 포함... zetawiki.com