接上一部分
(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也会采用这个索引。
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)
(6)如果列类型是字符串，但在查询时把一个数值型常量赋值给了一个字符型的列名name，那么虽然在name列上有索引，但是也没有用到。
mysql> explain select * from company2 where 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=‘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)
3 查看索引使用情况
如果索引正在工作，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;
+--------------+-------+----------+----------+
| 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]
如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多的改动，则需要做定期优化。这个命令可以将表中的空间碎片进行合并，但是此命令只对MyISAM、BDB和InnoDB表起作用。
mysql> optimize table sales;
+--------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text
|
+--------------+----------+----------+----------+
| sakila.sales | optimize | status | OK
|
+--------------+----------+----------+----------+
1 row in set (0.05 sec)
4 常用SQL的优化
1 大批量插入数据
当用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 fields
terminated by “,”;
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)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
使用test3.txt没有任何顺序的文本（效率慢了1.12倍）
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，恢复唯一性效验，可以提高导入效率。
当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，恢复自动提交，可以提高导入效率。
当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
2 优化insert语句
尽量使用多个值表的insert语句，这样可以大大缩短客户与数据库的连接、关闭等损耗。
可以使用insert delayed（马上执行）语句得到更高的效率。
将索引文件和数据文件分别存放不同的磁盘上。
可以增加bulk_insert_buffer_size 变量值的方法来提高速度，但是只对MyISAM表使用
当从一个文件中装载一个表时，使用LOAD DATA INFILE。这个通常比使用很多insert语句要快20倍。
3 优化group by语句
如果查询包含group by但用户想要避免排序结果的损耗，则可以使用使用order by null来禁止排序：
如下没有使用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)
4.1 优化表的类型
在MySQL中，可以使用函数PROCEDUREANALYSE（）对当前应用的表进行分析，改函数可以对数据表中列的数据类型提出优化建议，用户可以根据应用的实际情况酌情考虑是否实施
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 ***************************
… …
4.2 大存储量解决
1.分库分表
2.分区
主要目的:
1.减少表的记录数
2.减小对操作系统的负担压力
myisam读锁定
1.lock table t1 read
2.开启另一个mysql连接终端,接着去尝试:
select * from t1
3.再insert、update和delete t1这张表，你会发现所有的数据都停留在终端上没有真正的去操作
4.读锁定对我们在做备份大量数据时非常有用.
mysqldump -uroot -p123 test >test.sql
myisam写锁定
1.lock table t1 write
2.打开另一个mysql终端,尝试去select、insert、update和delete这张表t1,你会发现都不能操作，都会停留在终端上，只有等第一个终端操作完毕,第二个终端才能真正执行.
3.可见表的写锁定比读锁定更严格
4.一般情况下我们很少去显式的去对表进行read和write锁定的,myisam会自动进行锁定的.
慢查询日志
1.有关慢查询
开户和设置慢查询时间:
vi /etc/my.cnf
log_slow_queries=slow.log
long_query_time=5