mysql——参数优化 索引优化

索引优化

CREATE DATABASE IF NOT EXISTS info DEFAULT CHARSET utf8;
USE info;
#创建表
CREATE TABLE IF NOT EXISTS emp
(
	emp_id INT PRIMARY KEY AUTO_INCREMENT,
	emp_name VARCHAR(20),
	salary INT,
	dept_id INT,
	manager_id INT
);
#直接添加索引
CREATE INDEX index_emp_name ON emp(emp_name);
SELECT * FROM emp WHERE emp_name="liu"
#修改表时添加索引
ALTER TABLE emp ADD INDEX index_salary (salary);
#创建表时添加索引
CREATE TABLE article
(
	id INT PRIMARY KEY AUTO_INCREMENT,
	title VARCHAR(20) COMMENT'标题',
	content VARCHAR(100) COMMENT'内容',
	dt DATE,
	INDEX index_title (title)
);
#删除索引
DROP INDEX index_salary ON emp;
DROP INDEX index_emp_name ON emp;
DROP INDEX index_title ON article;
#创建唯一索引：1，2，3
CREATE INDEX index_emp_name ON emp(emp_name);
ALTER TABLE emp ADD UNIQUE INDEX index_salary (salary);
CREATE TABLE article
(
	id INT PRIMARY KEY AUTO_INCREMENT,
	title VARCHAR(20) COMMENT'标题',
	content VARCHAR(100) COMMENT'内容',
	dt DATE,
	UNIQUE INDEX index_title (title)
);

#创建组合索引
CREATE INDEX index_title_content ON article(title,content);
SELECT * FROM article WHERE title = "冬奥会" AND content="金牌数量";

参数优化

USE test;
CREATE TABLE IF NOT EXISTS t(id INT,num INT);
CREATE INDEX ix_num ON t(num);
#未使用索引
SELECT id FROM t WHERE num IS NULL;
#使用索引
SELECT id FROM t WHERE num=0;
#未使用索引
SELECT * FROM t WHERE num != 5;
SELECT * FROM t WHERE num <> 5;
#避免使用or
SELECT id FROM t WHERE num =10 OR num=20;
SELECT id FROM t WHERE num =10;
UNION
SELECT id FROM t WHERE unu=20;
#尽量少用in，not in，改用between
SELECT id FROM t WHERE num IN(1,2,3)
SELECT id FROM t WHERE num BETWEEN 1 AND 3;
#模糊查询，字母打头
SELECT * FROM t WHERE NAME LIKE '李%';
#避免索引字段在where后面运算
SELECT id FROM t WHERE num/2=100;
SELECT id FROM t WHERE num =200;
#避免索引字段进行函数操作
SELECT id FROM t WHERE SUBSTRING(NAME,1,3)='abc';
SELECT id FROM t WHERE NAME LIKE'abc%';
#复合索引
CREATE TABLE IF NOT EXISTS student
(
	id INT,
	NAME VARCHAR(20),
	age INT,
	tel VARCHAR(15)
);
CREATE INDEX ix_id_name ON student(id,NAME);