技术笔记：MySQL数据库优化详解(收藏)-阿里云开发者社区

MySQL数据库优化详解

mysql表复制

复制表结构+复制表数据

mysql> create table t3 like t1;

mysql> insert into t3 select from t1;

mysql索引

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADDPRIMARY KEY (column_list)

Create Index

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

drop index

DROP INDEX index_name ON talbe_name

alter table table drop

//代码效果参考：http://hnjlyzjd.com/xl/wz_24749.html

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

mysql视图

创建视图（脚本学堂）

mysql> create view v_t1 as select from t1 where id>4 and id<11;

Query OK, 0 rows affected (0.00 sec)

view视图的帮助信息

mysql> ? view

ALTER VIEW

CREATE VIEW

DROP VIEW

查看视图

mysql> show tables;

删除视图v_t1

mysql> drop view v_t1;

mysql内置函数

字符串函数

CONCAT (string2 【,… 】) //连接字串

LCASE (string2 //代码效果参考：http://hnjlyzjd.com/xl/wz_24747.html

) //转换成小写

UCASE (string2 ) //转换成大写

LENGTH (string ) //string长度

LTRIM (string2 ) //去除前端空格

RTRIM (string2 ) //去除后端空格

REPEAT (string2 ,count ) //重复count次

REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str

SUBSTRING (str , position 【,length 】) //从str的position开始,取length个字符

SPACE(count) //生成count个空格

数学函数

BIN (decimal_number ) //十进制转二进制

CEILING (number2 ) //向上取整

FLOOR (number2 ) //向下取整

MAX(num1 ,num2) //取最大值

MIN(num1,num2) //取最小值

SQRT(number2) //开平方

RAND() //返回0-1内的随机值

日期函数

CURDATE() //返回当前日期

CURTIME() //返回当前时间

NOW() //返回当前的日期时间

UNIX_TIMESTAMP(date) //返回当前date的UNIX日间戳

FROM_UNIXTIME() //返回UNIX时间戳的日期值

WEEK(date) //返回日期date为一年中的第几周

YEAR(date) //返回日期date的年份

DATEDIFF(expr,expr2) //返回起始时间expr和结束时间expr2间天数

mysql预处理语句

设置stmt1预处理,传递一个数据作为一个where判断条件

mysql> prepare stmt1 from 'select from t1 where id>?';

设置一个变量

mysql> set @i=1;

执行stmt1预处理

mysql> execute stmt1 using @i;

设置@i为5

mysql> set @i=5;

再次去执行stmt1

mysql> execute stmt1 using @i;

如何删除预处理stmt1

mysql> drop prepare stmt1;

mysql事务处理

--关闭自动提交功能

mysql> set autocommit=0;

--从表t1中删除了一条记录

mysql> delete from t1 where id=11;

--此时做一个p1还原点:

mysql> savepoint p1;

--再次从表t1中删除一条记录:

mysql> delete from t1 where id=10;

--再次做一个p2还原点:

mysql> savepoint p2;

--此时恢复到p1还原点，当然后面的p2这些还原点自动会失效:

mysql> rollback to p1;

--退回到最原始的还原点:

mysql> rollback ;

mysql存储（脚本学堂）

创建一个存储p1()

mysql> \d //

mysql> create procedure p1()

-> begin

-> set @i=0;

-> while @i<10 do

-> select @i;

-> set @i=@i+1;

-> end while;

-> end;

-> //

执行存储p1()

mysql> \d ;

mysql> call p1();

--查看procedure p1()的status信息

mysql> show procedure status\G

--查看procedure p1()的具体信息:

mysql> show create procedure p1\G

mysql触发器

修改delimiter为//

mysql> \d //

创建一个名字为tg1的触发器,当向t1表中插入数据时，就向t2表中插入一条数据

mysql> create trigger tg1 before insert on t1 for each ro

--准备两个空表t1和t2

mysql> select from t1;

mysql> select from t2;

--向t1表中插入多条数据:

mysql> insert into t1 values(1),(2),(3),(4);

如何制作删除表t1后t2表中的记录也会跟着删除呢

mysql>\d //

mysql> create trigger tg2 beforedelete on t1 for each row

mysql>\d ;

如何制作更改表t1后t2表中的记录跟着个性呢

mysql>\d //

mysql> create trigger tg3 beforeupdate on t1 for each row

mysql>\d ;

查看触发器

mysql> show triggers;

重排auto_increment值

MYSQL数据库自动增长的ID如何恢复，清空表的时候。不能用

delete from tablename;

而是要用：

truncatetable tablename;

这样auto_increment 就恢复成1了

或者清空内容后直接用ALTER命令修改表:

altertable tablename auto_increment =1;

利用GROUP BY的WITH ROLLUP

mysql> select from demo;

+-------+-------+

| cname | pname |

+-------+-------+

| bj | hd |

| bj | xc |

| bj | hd |

| sh | dh |

| sh | rg |

| sh | dh |

+-------+-------+

9 rows in set (0.00 sec)

对demo表按照cname、pname列分组对pname列进行聚合计算如下

mysql> select cname,pname,count(pname) from demo group by

cname,pname;

+-------+-------+--------------+

| cname | pname | count(pname) |

+-------+-------+--------------+

| bj | hd | 3 |

| bj | xc | 2 |

| sh | dh | 3 |

| sh | rg | 1 |

+-------+-------+--------------+

4 rows in set (0.00 sec)

同样使用with rollup关键字后，统计出更多的信息，如下。注意：with rollup不可以和ordery by同时使用

ysql> select cname,pname,count(pname) from demo group by cname,pname

with rollup;

+-------+-------+--------------+

| cname | pname | count(pname) |

+-------+-------+--------------+

| bj | hd | 3 |

| bj | xc | 2 |

| bj | NULL | 5 |

| sh | dh | 3 |

| sh | rg | 1 |

| sh | NULL | 4 |

| NULL | NULL | 9 |

+-------+-------+--------------+

7 rows in set (0.00 sec)

使用外键需要注意的问题

创建外键的方式

mysql>create table temp( id int, name char(20), foreign key(id)

references outTable(id) on delete cascade on update cascade)；

注意：Innodb类型的表支持外键，myisam类型的表，虽然创建外键可以成功，但是不起作用，主要原因是不支持外键。

优化SQL语句的一般步骤

通过show status命令了解各种SQL的执行频率

mysql> show 【session|global】status;

其中：session（默认）表示当前连接，global表示自数据库启动至今

mysql>show status;

mysql>show global status;

mysql>show status like ‘Com%';

mysql>show global status like ‘Com%';

参数说明：

Com_XXX表示每个XXX语句执行的次数如：

Com_select 执行select操作的次数，一次查询只累计加1

Com_update 执行update操作的次数

Com_insert 执行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 where id=1000;

desc select from table where id=1000;

通过EXPLAIN分析较低效SQL的执行计划

mysql> explain select count() from stu where name like "a%"\G

** 1. row *

id: 1

select_type: SIMPLE

table: stu

type: range

possible_keys: name,ind_stu_name

key: name

key_len: 50

ref: NULL

rows: 8

Extra: Using where; Using index

1 row in set (0.00 sec)

每一列的简单解释

id: 1

select_type: SIMPLE 表示select的类型，常见的取值有SIMPLE（）简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SESECT）等

table: stu 输出结果集的表

type: range 表示表的连接类型，性能有好到差：system（表仅一行）、const（只一行匹配）、eq_ref（对于前面的每一行使用主键和唯一）、ref（同eq_ref，但没有使用主键和唯一）、ref_or_null（同前面对null查询）、index_merge（索引合并优化）、unique_subquery（主键子查询）、index_subquery（非主键子查询）、range（表单中的范围查询）、index（都通过查询索引来得到数据）、all（通过全表扫描得到的数据）

possible_keys: name,ind_stu_name 表查询时可能使用的索引。

key: name 表示实际使用的索引。

key_len: 50 索引字段的长度

ref: NULL

rows: 8 扫描行的数量

Extra: Using where; Using index 执行情况的说明和描述

索引问题

MyISAM存储引擎的表的数据和索引是自动分开存储的，各自是独一的一个文件；InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。MySQL目前不支持函数索引，但是能对列的前面某一部分进行索引，例如name字段，可以只取name的前4个字符进行索引，这个特性可以大大缩小索引文件的大小，用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。

mysql>create index ind_company2_name on company2(name(4));

--其中company表名ind_company2_name索引名

MySQL如何使用索引

1、使用索引

(1)对于创建的多列索引，只要查询的条件中用到最左边的列，索引一般就会被使用。如下创建一个复合索引。

mysql>create index ind_sales2_com_mon on sales2(company_id,moneys);

然后按company_id进行查询，发现使用到了复合索引

mysql>explain select from sales2 where company_id=2006\G

使用下面的查询就没有使用到复合索引。

mysql>explain select from sales2 where moneys=1\G

(2) 使用like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用，如下：

mysql> explain select from company2 where name like "%3"\G

** 1. row *

id: 1

select_type: SIMPLE

table: company2

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1000

Extra: Using where

1 row in set (0.00 sec)

如下这个使用到了索引，而下面例子能够使用索引，区别就在于“%”的位置不同，上面的例子是吧“%”放在了第一位，而下面的例子则没有

mysql> explain select from company2 where name like "3%"\G

** 1. row *

id: 1

select_type: SIMPLE

table: company2

type: range

possible_keys: ind_company2_name

key: ind_company2_name

key_len: 11

ref: NULL

rows: 103

Extra: Using where

1 row in set (0.00 sec)

(3)如果对大的文本进行搜索，使用全文索引而不使用like“%...%”.

(4)如果列名是索引，使用column_name is null将使用索引。如下

mysql> explain select from company2 where name is null\G

** 1. row *

id: 1

select_type: SIMPLE

table: company2

type: ref

possible_keys: ind_company2_name

key: ind_company2_name

key_len: 11

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

存在索引但不使用索引

(1)如果MySQL估计使用索引比全表扫描更慢，则不使用索引。例如如果列key_part1均匀分布在1到100之间，查询时使用索引就不是很好

mysql>select from table_name where key_part1>1 and key_part<90;

(2)如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列，那么不会用到索引。Heap表只有在“=”的条件下会使用索引。

(3)用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，www.jbxue.com 那么涉及的索引都不会被用到。

mysql>show index from sales\G

** 1. row *

……

key_name: ind_sales_year

seq_in_index：1

Column_name: year

……

从上面可以发现只有year列上面有索引。来看如下的执行计划。

mysql> explain select from sales where year=2001 or country=‘China'\G

** 1. row *

id: 1

select_type: SIMPLE

table: sales

type: ALL

possible_keys: ind_sales_year

key: NULL

key_len: NULL

ref: NULL

rows: 12

Extra: Using where

1 row in set (0.00 sec)

(4)如果不是索引列的第一部分，如下例子:可见虽然在money上面建有复合索引，但是由于money不是索引的第一列，那么在查询中这个索引也不会被MySQL采用。

mysql> explain select from sales2 where moneys=1 \G

** 1. row *

id: 1

select_type: SIMPLE

table: sales2

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1000

Extra: Using where

1 row in set (0.00 sec)

(5)如果like是以%开始，可见虽然在name上面建有索引，但是由于where 条件中like的值的“%”在第一位了，那么MySQL也会采用这个索引。

(6)如果列类型是字符串，但在查询时把一个数值型常量赋值给了一个字符型的列名name，那么虽然在name列上有索引，但是也没有用到。

mysql> explain select from company2 where name name=294\G

** 1. row *

id: 1

select_type: SIMPLE

table: company2

type: ALL

possible_keys: ind_company2_name

key: NULL

key_len: NULL

ref: NULL

rows: 1000

Extra: Using where

1 row in set (0.00 sec)

而下面的sql语句就可以正确使用索引。

mysql> explain select from company2 where name name=‘294'\G

** 1. row *

id: 1

select_type: SIMPLE

table: company2

type: ref

possible_keys: ind_company2_name

key: ind_company2_name

key_len: 23

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

查看索引使用情况

如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数。

Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。

mysql> show status like 'Handler_read%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Handler_read_first | 0 |

| Handler_read_key | 5 |

| Handler_read_next | 0 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 2055 |

+-----------------------+-------+

6 rows in set (0.00 sec)

两个简单实用的优化方法

分析表的语法如下:(检查一个或多个表是否有错误)

mysql> CHECK TABLE tbl_name【,tbl_name】 …【option】 …option =

{ QUICK | FAST | MEDIUM| EXTENDED | CHANGED}

mysql> check table sales;

+--------------+-------+----------+----------+

+--------------+-------+----------+----------+

+--------------+-------+----------+----------+

1 row in set (0.01 sec)

优化表的语法格式：

OPTIMIZE 【LOCAL | NO_WRITE_TO_BINLOG】 TABLE tbl_name 【,tbl_name】

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多的改动，则需要做定期优化。这个命令可以将表中的空间碎片进行合并，但是此命令只对MyISAM、BDB和InnoDB表起作用。

mysql> optimize table sales;

+--------------+----------+----------+----------+

+--------------+----------+----------+----------+

+--------------+----------+----------+----------+

1 row in set (0.05 sec)

常用SQL的优化

大批量插入数据

当用load命令导入数据的时候，适当设置可以提高导入的速度。

对于MyISAM存储引擎的表，可以通过以下方式快速的导入大量的数据。

ALTER TABLE tbl_name DISABLE KEYS

loading the data

ALTER TABLE tbl_name ENABLE KEYS

DISABLE KEYS 和ENABLE KEYS 用来打开或关闭MyISAM表非唯一索引的更新，可以提高速度，注意：对InnoDB表无效。

--没有使用打开或关闭MyISAM表非唯一索引：

mysql> load data infile ‘/home/mysql/film_test.txt'into table film_test2;

Query OK,529056 rows affected (1 min 55.12 sec)

Records:529056 Deleted:0 Skipped:0 Warnings:0

--使用打开或关闭MyISAM表非唯一索引：

mysql> alter table film_test2 disable keys;

Query OK,0 rows affected (0.0 sec)

mysql> load data infile ‘/home/mysql/film_test.txt'into table film_test2;

Query OK,529056 rows affected (6.34 sec)

Records:529056 Deleted:0 Skipped:0 Warnings:0

mysql> alter table film_test2 enable keys;

Query OK,0 rows affected (12.25 sec)

--以上对MyISAM表的数据导入，但对于InnoDB表并不能提高导入数据的效率

（1）针对于InnoDB类型表数据导入的优化

因为InnoDB表的按照主键顺序保存的，所以将导入的数据主键的顺序排列，可以有效地提高导入数据的效率。

--使用test3.txt文本是按表film_test4主键存储顺序保存的

mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4;

Query OK, 1587168 rows affected (22.92 sec)