MySQL--索引

2018-08-08 本文已影响0人薛定谔的特性

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)

表优化

对常用列建索引

数据库优化
分库分表
分区

MySQL--索引

MySQL索引

MySQL视图

MySQL内置函数

MyISAM引擎不支持外键

优化SQL语句

MySQL优化

索引优化

MyISAM独立表空间

InnoDB共享表空间

猜你喜欢

热点阅读