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 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 ) //转换成小写 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事务处理 --关闭自动提交功能 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 statusG --查看procedure p1()的具体信息: mysql> show create procedure p1G mysql触发器 修改delimiter为// mysql> d // 创建一个名字为tg1的触发器,当向t1表中插入数据时，就向t2表中插入一条数据 mysql> create trigger tg1 before insert on t1 for each ro >begin >insert into t2(id) values(new.id); >end// --准备两个空表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 >begin delete from t2 where id=old.id; >end// mysql>d ; 如何制作更改表t1后t2表中的记录跟着个性呢 mysql>d // mysql> create trigger tg3 beforeupdate on t1 for each row >begin update t2 set id=new.id where id=old.id; >end// 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,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语句执行的次数如： InnoDB_rows_read 执行select操作的次数 connections 连接mysql的数量 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) 每一列的简单解释
索引问题 MyISAM存储引擎的表的数据和索引是自动分开存储的，各自是独一的一个文件；InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。MySQL目前不支持函数索引，但是能对列的前面某一部分进行索引，例如name字段，可以只取name的前4个字符进行索引，这个特性可以大大缩小索引文件的大小，用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。 mysql>create index ind_company2_name on company2(name(4)); --其中company表名ind_company2_name索引名 (1)对于创建的多列索引，只要查询的条件中用到最左边的列，索引一般就会被使用。如下创建一个复合索引。 mysql>create index ind_sales2_com_mon on sales2(company_id,moneys); 然后按company_id进行查询，发现使用到了复合索引 mysql>explain select * from sales2 where company_id=2006G 使用下面的查询就没有使用到复合索引。 mysql>explain select * from sales2 where moneys=1G (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 nullG *************************** 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)
存在索引但不使用索引 mysql>select * from table_name where key_part1>1 and key_part<90; (2)如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列，那么不会用到索引。Heap表只有在“=”的条件下会使用索引。 (3)用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。 mysql>show index from salesG *************************** 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=294G *************************** 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_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;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| sakila.sales | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.01 sec)
优化表的语法格式： OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name] mysql> optimize table sales; +--------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+----------+----------+----------+ | sakila.sales | optimize | status | OK | +--------------+----------+----------+----------+ 1 row in set (0.05 sec) 常用SQL的优化 大批量插入数据 对于MyISAM存储引擎的表，可以通过以下方式快速的导入大量的数据。 ALTER TABLE tbl_name DISABLE KEYS
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表的按照主键顺序保存的，所以将导入的数据主键的顺序排列，可以有效地提高导入数据的效率。
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; Query OK,1587168 rows affected (22.92 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0 mysql> load data infile ‘/home/mysql/film_test4.txt'into table film_test4; Query OK,1587168 rows affected (31.16 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0 （2）关闭唯一性效验可以提高导入效率 在导入数据前先执行set unique_checks=0，关闭唯一性效验，在导入结束后执行set unique_checks=1，恢复唯一性效验，可以提高导入效率。
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; Query OK,1587168 rows affected (22.92 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0 --当unique_checks=0时 mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; Query OK,1587168 rows affected (19.92 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0 （3）关闭自动提交可以提高导入效率 在导入数据前先执行set autocommit=0，关闭自动提交事务，在导入结束后执行set autocommit=1，恢复自动提交，可以提高导入效率。
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; Query OK,1587168 rows affected (22.92 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0 --当autocommit=0时 mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; Query OK,1587168 rows affected (20.87 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0 优化insert语句 可以使用insert delayed（马上执行）语句得到更高的效率。 将索引文件和数据文件分别存放不同的磁盘上。 可以增加bulk_insert_buffer_size 变量值的方法来提高速度，但是只对MyISAM表使用当从一个文件中装载一个表时，使用LOAD DATA INFILE。这个通常比使用很多insert语句要快20倍。 优化group by语句 如下没有使用order by null来禁止排序 mysql> explain select id,sum(moneys) from sales2 group by idG *************************** 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 temporary;Using filesort 1 row in set (0.00 sec) 如下使用order by null的效果： mysql> explain select id,sum(moneys) from sales2 group by id order by nullG *************************** 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 temporary 1 row in set (0.00 sec) 优化嵌套查询 mysql> explain select * from sales2 where company_id not in(select id from company2)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) *************************** 2. row *************************** id: 2 select_type: SIMPLE table: company2 type: index_subquery possible_keys: ind_company2_id key: ind_company2_id key_len: 5 ref: func rows: 2 Extra: Using index 1 row in set (0.00 sec) 下面是使用更有效的链接查询（Join） mysql> explain select * from sales2 left join company2 on sales2.company_id = company2.id where sales2.company_id is nullG *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales2 type: ALL possible_keys: ind_sales2_companyid_moneys key: ind_sales2_companyid_moneys key_len: 5 ref: count rows: 1 Extra: Using where 1 row in set (0.00 sec) *************************** 2. row *************************** id: 2 select_type: SIMPLE table: company2 type: index_subquery possible_keys: ind_company2_id key: ind_company2_id key_len: 5 ref: func rows: 1 Extra: 1 row in set (0.00 sec) 从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。连接（JOIN）子所以更有效率一些，是因为MySQL不需要再内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。 数据库优化 优化表的类型 mysql> select * from duck_cust procedure analyse()G *************************** 1. row *************************** Field_name: sakila.duch_cust.cust_num Min_value: 1 Max_value: 6 Min_length: 1 Max_length: 1 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 3.5000 Std: 1.7078 Optimal_fieldtype: ENUM(‘1',‘2',‘3',‘4') NOT NULL *************************** 2. row *************************** ……
大存储量解决 2.分区 主要目的: 1.减少表的记录数 2.减小对操作系统的负担压力 中间表 Mysql服务器优化 myisam读锁定 lock table t1 read select * from t1 4.读锁定对我们在做备份大量数据时非常有用. mysqldump -uroot -p123 test >test.sql lock table t1 write 3.可见表的写锁定比读锁定更严格 4.一般情况下我们很少去显式的去对表进行read 和write锁定的,myisam会自动进行锁定的. Mysql服务器优化 二进制日志 查看bin-log日志: mysql> show binary logs; mysql> show master status; vi /etc/my.cnf mysql> show global status like "%quer%" 1. 有时登录mysql时提示不能用socket登录，此时可以换成tcp方式去登录，但是可以测试时可以这样用，但是必须要在php去用之前把这个事情解决了. [root@localhost mysql]# mysql -uroot -pwei --protocol tcp -hlocalhost Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 34 Server version: 5.0.77-log Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> 这样就可以登录,这样就不用mysql.sock来登录，而mysql.sock是启动mysqld服务时产生的 root密码丢失 root密码丢失破解
1.service mysqld stop
2. mysqld_safe --skip-grant-tables --user=mysql &
//跳过授权表mysql.user和mysql.db这些表
3. mysql -uroot
4. set password=password("wei");
//用这一条语句结果报错,就是因为加了--skip-grant-tables
4. mysql>update user set password=password("wei") where user='root'
and host='localhost';
5. mysql> set password for root@localhost=password("wei");
6. mysql> set password=password("wei");
//和第五步一样,都可能成功修改密码
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |