MySQL--索引

MySQL索引

查看索引

SHOW INDEX FROM table_name;

创建索引

ALTER TABLE table_name ADD INDEX index_name(column_list);

CREATE INDEX index_name ON table_name(column_list);

创建唯一索引

ALTER TABLE table_name ADD UNIQUE(column_list);

CREATE UNIQUE INDEX index_name ON table_name(column_list);

创建主键索引

ALTER TABLE table_name ADD PRIMARY KEY(column_list);

删除索引

ALTER TABLE table_name DROP INDEX index_name;

DROP INDEX index_name ON table_name;

删除主键

ALTER TABLE table_name DROP PRIMARY KEY;

MySQL视图

创建视图

CREATE VIEW view_name AS SELECT * FROME table_name WHERE ....;

删除视图

DROP VIEW view_name;

修改视图

ALTER VIEW view_name;

MySQL内置函数

字符串函数

• 连接

CONCAT(string[,...])

• 转小写

LCASE(string)

• 转大写

UCASE(string)

• 长度

LENGTH(string)

• 去除左端空格

LTRIM(string)

• 去除右端空格

RTRIM(string)

• 重复count次

REPEAT(string, count)

• 替换

REPLACE(str, search_str, replace_str)

• 从position开始,截取length个字符

SUBSTRING(str, position[,length])

• 生成count个空格

SPACE(count)

数学函数

• 十进制转二进制

BIN(decimal_number)

• 向上取正

CEILING(number)

• 向下取正

FLOOR(number)

• 取最大值

MAX(num1, num2)

• 取最小值

MIN(num1, num2)

• 开平方

SQRT(number)

• 返回0-1内的随机值

RAND()

日期函数

• 返回当前日期

CURDATE()

• 返回当前时间

CURTIME()

• 返回当前日期时间

NOW()

• 返回当前date的UNIX时间戳

UNIX_TIMESTAMP(date)

• 返回UNIX时间戳日期值

FROM_UNIXTIME()

• 返回date为一年中的第几周

WEEK(date)

• 返回date的年份

YEAR(date)

• 返回expr和expr2之间的天数

DATEDIFF(expr, expr2)

MySQL预处理

• 设置预处理

PREPARE pre_name FROM 'SELECT ... FROM table_name WHERE ?';

• 设置变量

SET @var=value;

• 执行预处理

EXECUTE pre_name using @var;

• 删除预处理

DROP PREPARE pre_name;

MySQL事务处理(MyISAM引擎不支持)

• 关闭自动提交功能

SET AUTOCOMMIT=0;

• 创建还原点

SAVEPOINT p_name;

• 回滚还原点

ROLLBACK TO p_name;

• 回滚所有

ROLLBACK;

重排AUTO_INCREMENT值
清空表时用

TRUNCATE TABLE table_name;
ALTER TABLE table_name AUTO_INCREMENT=1;

• 正则表达式

SELECT * FROM * WHERE col REGEXP "recp";

• 提取随机行

SELECT * FROM table_name ORDER BY RAND();

• 统计更多信息

SELECT * FROM table_name GROUP BY ... WITH ROLLUP;

• 数值之间逻辑位运算

SELECT col0, BIT_OR(col1) FROM table_name GROUP BY col0;

SELECT col0, BIT_AND(col1) FROM table_name GROUP BY col0;

MyISAM引擎不支持外键

优化SQL语句

• 各种SQL执行频率

SHOW [SESSION|GLOBAL] STATUS;

SESSION (默认) 当前连接

GLOBAL 自数据库启动

Com_XXX表示XXX语句执行次数

Com_select  一次查询只累计加1

Com_update  执行update次数

Com_insert  批量插入只算一次

Com_delete  执行delete次数

• 只针对InnoDB存储引擎

Innodb_rows_read  执行select操作次数

Innodb_rows_updated  执行update操作次数

Innodb_rows_inserted  执行insert操作次数

Innodb_rows_deleted  执行delete操作次数

CONNECTIONS  连接MySQL的次数

Uptime  数据库已运行的时间(秒)

Slow_queries  慢查询的次数

定位执行效率较低的SQL语句

EXPLAIN SELECT * FROM table_name WHERE ...;
DESC SELECT * FROM table_name WHERE ...;

MySQL优化

索引优化

• MyISAM独立表空间

• InnoDB共享表空间

#####使用索引
查询条件中用到复合索引的第一列
列名是索引, 使用column_name IS NULL 将使用索引
使用LIKE的查询, 使用常量并且只有%号不在第一个字符, 索引才可能被使用
对大的文本进行搜索, 使用全文索引而不使用LIKE’%…%’

#####不使用索引
MySQL估计使用索引比全表扫描更慢, 则不使用索引
使用MEMORY/HEAP表并且WHERE条件中不使用’=’, 则不会使用索引
用OR分割的条件中, 前面列有索引, 后面列没有索引, 则不使用索引
WHERE子句的条件中, 用到的列不是复合索引的第一列
使用LIKE的查询, 使用常量并且只有%号在第一个字符
把数值型常量赋值给一个字符串型的列, 即使字符串型的列上有索引, 也不会用到

查看索引使用情况

SHOW STATUS LIKE 'Handler_read%';
Handler_read_first  16

一行被索引值读的次数

Handler_read_key 1079
Handler_read_last 0

值越高 查询运行效率越低

Handler_read_next 152
Handler_read_prev 0
Handler_read_rnd 130
Handler_read_rnd_next 4166

• 分析表(检查表是否有错误)

CHECK TABLE table_name[, table_name]...[option] = {QUICK|FAST|MEDIUM|EXTENDED|CHANGED}

• 优化表

OPTIMIZE[LOCAL|NO_WRITE_TO_BINLOG] TABLE table_name[, table_name]

• 批量导入大量数据

LOAD DATA INFILE 'file_name' INTO TABLE table_name;

• 批量导出大量数据

SELECT ... FROM table_name INTO OUTFILE 'file_name'

提高导入效率

将导入数据按主键顺序排列

• 关闭唯一性检验(必须保证唯一键唯一)

set unique_checks=0

• 关闭自动提交

set autocommit=0

优化INSERT语句

• 尽量插入多个值(减少频繁INSERT对数据库连接,关闭等的损耗)

• 使用INSERT DELAYED(马上执行)

优化GROUP BY 语句

• 查询包含GROUP BY但不需要排序, 使用ORDER BY NULL来禁止排序

• 优化嵌套查询(使用JOIN)

表优化

• 对常用列建索引

• 数据库优化

• 分库分表

• 分区