（一）sql语句的类型
1.DQL(数据查询语言):select 2.DDL(数据定义语言):create、drop、alter 3.DML(数据操作语言):insert、delete、update 4.DCL(数据控制语言):grant、revoke 5.TCL(事务控制语言):rollback、savepoint、commit
（二）mysql优化的一般步骤
通过show status …命令查看各种sql执行频率 定位执行效率较低的语句(重点select慢查询) 通过explain分析低效率的sql语句的执行情况 确定问题并采取相应的优化措施1.查看数据库当前状态，show参数,主要关注的是Com开头的指令
show status like 'Com_某某'; //某某所执行的次数
mysql> show status like 'Com%';//显示当前控制台的情况+---------------------------+-------+| Variable_name | Value |+---------------------------+-------+| Com_admin_commands | 0 || Com_assign_to_keycache | 0 || Com_alter_db | 0 || Com_alter_db_upgrade | 0 || Com_alter_event | 0 || Com_alter_function | 0 || Com_alter_procedure | 0 || Com_alter_server | 0 || Com_alter_table | 0 || Com_alter_tablespace | 0 || Com_analyze | 0 || Com_begin | 0 || Com_binlog | 0 || Com_call_procedure | 0 || Com_change_db | 0 || Com_change_master | 0 || Com_check | 0 || Com_checksum | 0 || Com_commit | 0 || Com_create_db | 0 || Com_create_event | 0 || Com_create_function | 0 || Com_create_index | 0 || Com_create_procedure | 0 || Com_create_server | 0 || Com_create_table | 0 || Com_create_trigger | 0 || Com_create_udf | 0 || Com_create_user | 0 || Com_create_view | 0 || Com_dealloc_sql | 0 || Com_delete | 0 | //显示删除所执行的次数| Com_delete_multi | 0 || Com_do | 0 || Com_drop_db | 0 || Com_drop_event | 0 || Com_drop_function | 0 || Com_drop_index | 0 || Com_drop_procedure | 0 || Com_drop_server | 0 || Com_drop_table | 0 || Com_drop_trigger | 0 || Com_drop_user | 0 || Com_drop_view | 0 || Com_empty_query | 0 || Com_execute_sql | 0 || Com_flush | 0 || Com_grant | 0 || Com_ha_close | 0 || Com_ha_open | 0 || Com_ha_read | 0 || Com_help | 0 || Com_insert | 0 | //显示插入所执行的次数| Com_insert_select | 0 || Com_install_plugin | 0 || Com_kill | 0 || Com_load | 0 || Com_lock_tables | 0 || Com_optimize | 0 || Com_preload_keys | 0 || Com_prepare_sql | 0 || Com_purge | 0 || Com_purge_before_date | 0 || Com_release_savepoint | 0 || Com_rename_table | 0 || Com_rename_user | 0 || Com_repair | 0 || Com_replace | 0 || Com_replace_select | 0 || Com_reset | 0 || Com_resignal | 0 || Com_revoke | 0 || Com_revoke_all | 0 || Com_rollback | 0 || Com_rollback_to_savepoint | 0 || Com_savepoint | 0 || Com_select | 1 | //显示查询所执行的次数| Com_set_option | 0 || Com_signal | 0 || Com_show_authors | 0 || Com_show_binlog_events | 0 || Com_show_binlogs | 0 || Com_show_charsets | 0 || Com_show_collations | 0 || Com_show_contributors | 0 || Com_show_create_db | 0 || Com_show_create_event | 0 || Com_show_create_func | 0 || Com_show_create_proc | 0 || Com_show_create_table | 0 || Com_show_create_trigger | 0 || Com_show_databases | 0 || Com_show_engine_logs | 0 || Com_show_engine_mutex | 0 || Com_show_engine_status | 0 || Com_show_events | 0 || Com_show_errors | 0 || Com_show_fields | 0 || Com_show_function_status | 0 || Com_show_grants | 0 || Com_show_keys | 0 || Com_show_master_status | 0 || Com_show_open_tables | 0 || Com_show_plugins | 0 || Com_show_privileges | 0 || Com_show_procedure_status | 0 || Com_show_processlist | 0 || Com_show_profile | 0 || Com_show_profiles | 0 || Com_show_relaylog_events | 0 || Com_show_slave_hosts | 0 || Com_show_slave_status | 0 || Com_show_status | 1 || Com_show_storage_engines | 0 || Com_show_table_status | 0 || Com_show_tables | 0 || Com_show_triggers | 0 || Com_show_variables | 0 || Com_show_warnings | 0 || Com_slave_start | 0 || Com_slave_stop | 0 || Com_stmt_close | 0 || Com_stmt_execute | 0 || Com_stmt_fetch | 0 || Com_stmt_prepare | 0 || Com_stmt_reprepare | 0 || Com_stmt_reset | 0 || Com_stmt_send_long_data | 0 || Com_truncate | 0 || Com_uninstall_plugin | 0 || Com_unlock_tables | 0 || Com_update | 0 | //显示更新所执行的次数| Com_update_multi | 0 || Com_xa_commit | 0 || Com_xa_end | 0 || Com_xa_prepare | 0 || Com_xa_recover | 0 || Com_xa_rollback | 0 || Com_xa_start | 0 || Compression | OFF |+---------------------------+-------+140 rows in set (0.00 sec)
还有几个常用的命令
show status like 'Connections'; //链接mysql数据库的次数show status like Uptime; //服务器工作的时间（单位秒）show status like Slow_queries; //慢查询的次数（默认为10）
2.定位慢查询
默认情况下mysql不记录慢查询日志，要在启动的时候指定bin\mysqld.exe,slow-query-log 通过慢查询日志定位执行效率较低的sql语句。慢查询日志记录了所有查询时间超过long_query_time的sql 语句,show variables like 'long_query_time'; set long_query_time=时间；//设置慢查询时间 查看慢查询日志：默认为数据目录data中的host-name-slow.log。低版本的mysql需要通过在开启mysql时使用- -log-slow-queries[=file_name]来配置3.explain分析问题
执行explain select * from emp where empno=10000;
select_type //表示查询的类型table //输出结果及集的表type //表示表的连接类型possible_keys //表示查询时可能用到的索引key //表示实际使用的索引key_len //索引字段的长度rows //扫描的行数Extra //执行情况的描述和说明
(三）建立适当的索引
建立索引可以极大地提高查询速度，但是查询速度的提高是以降低插入、更新、删除的速度为代价的，这些操作增加了大量的I/0以及增加了磁盘的占用。
1.索引的类型
主索引：主键自动地为主索引（PRIMARY） 唯一索引（UNIQUE） 普通索引（INDEX） 全文索引（FULLTEXT），只有MYISAM存储引擎支持,国内全文索引通常使用sphix2.哪些列上适合添加索引
较为频繁的作为查询条件的字段应该创建索引 唯一性太差的字段不适合单独创建索引 更新非常频繁的字段不适合创建索引 不会出现在WHERE子句中字段不该创建索引 对于创建的多列索引，只要查询条件是用了最左边的列，索引一般就会被使用 对于使用like的查询，查询如果是’%aaa’，不会使用到索引；如果是’aaa%’会使用到索引 如果条件中有or,即使其中有条件带索引，也不会使用 如果列类型是字符串， 那一定要在条件中将数据使用引号引起来，否则不适用索引 如果条件中有or,即使其中有条件带索引，也不会使用如果使用全表扫描比使用索引快，则不适用索引
3.索引的使用
创建索引
create [unique|fulltext] index index_name on tbl_name (col_name [(length)] [asc | desc] , …..); alter table table_name ADD INDEX [index_name] (index_col_name,...)添加主键(索引) alter table tbl_name ADD primary key(列名,..); 联合主键删除索引
drop index index_name on tbl_name;alter table table_name drop index index_name;删除主键(索引)比较特别: alter table t_b drop primary key;查询索引
show index from table_name; show keys from table_name; desc table_Name;
查看索引的使用情况
show status like 'Handler_read%';
Handler_read_key //这个值越高越好，表示使用索引查询到的次数Handler_read_rnd_next //这个值越高说明查询低效
(四）常用索引优化
1.大批量插入数据 (1)对于MyISAM：alter table table_name disable keys; loading data; alter table table_name enable keys; (2)对于Innodb将要导入的数据按照主键排序 set unique_checks=0,关闭唯一性校验。set autocommit=0,关闭自动提交。
2.优化group by语句 默认情况，MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序
3.有些情况下，可以使用连接来替代子查询。 因为使用join，MySQL不需要在内存中创建临时表。
4.如果想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引
(五）选择合适的存储引擎
MyISAM:默认的MySQL存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高。其优势是访问的速度快。在插入数据时，默认放在最后. ,删除数据后，空间不回收.(不支持事务和外键)
InnoDB：提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM，写的处理效率差一些并且会占用更多的磁盘空间.支持事务和外键
(五）选择合适的数据类型
1.在精度要求高的应用中，建议使用定点数来存储数值，以保证结果的准确性。
2.对于存储引擎是MyISAM的数据库,如果经常做删除和修改记录的操作，要定时执行optimize table table_name;功能对表进行碎片整理。
3.日期类型要根据实际需要选择能够满足应用的最小存储的早期类型create table bbs(id int ,con varchar(1024) , pub_time int);date(‘Ymd’,时间-3*24*60*60); 2038年-1-19
(六）分表
1.水平分表
查询电话费，近三个月的数据放入一张表，一年内的放入到另一张表、
2.垂直分表
附：测试脚本
#创建表DEPTCREATE TABLE dept( deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,dname VARCHAR(20) NOT NULL DEFAULT "",loc VARCHAR(13) NOT NULL DEFAULT "") ENGINE=MyISAM DEFAULT CHARSET=utf8 ;#创建表EMP雇员CREATE TABLE emp(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,ename VARCHAR(20) NOT NULL DEFAULT "",job VARCHAR(9) NOT NULL DEFAULT "",mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,hiredate DATE NOT NULL,sal DECIMAL(7,2) NOT NULL,comm DECIMAL(7,2) NOT NULL,deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;#工资级别表CREATE TABLE salgrade(grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,losal DECIMAL(17,2) NOT NULL,hisal DECIMAL(17,2) NOT NULL)ENGINE=MyISAM DEFAULT CHARSET=utf8;INSERT INTO salgrade VALUES (1,700,1200);INSERT INTO salgrade VALUES (2,1201,1400);INSERT INTO salgrade VALUES (3,1401,2000);INSERT INTO salgrade VALUES (4,2001,3000);INSERT INTO salgrade VALUES (5,3001,9999);# 随机产生字符串#定义一个新的命令结束符合delimiter $$#删除自定的函数drop function rand_string $$#这里我创建了一个函数. create function rand_string(n INT)returns varchar(255)begin declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; declare return_str varchar(255) default ''; declare i int default 0; while i < n do set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1)); set i = i + 1; end while; return return_str; end $$delimiter ;select rand_string(6);# 随机产生部门编号delimiter $$drop function rand_num $$#这里我们又自定了一个函数create function rand_num( )returns int(5)begin declare i int default 0; set i = floor(10+rand()*500);return i; end $$delimiter ;select rand_num();#******************************************#向emp表中插入记录(海量的数据)delimiter $$drop procedure insert_emp $$create procedure insert_emp(in start int(10),in max_num int(10))begindeclare i int default 0; set autocommit = 0; repeat set i = i + 1; insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num()); until i = max_num end repeat; commit; end $$delimiter ;#调用刚刚写好的函数, 1800000条记录,从100001号开始call insert_emp(100001,1800000);#**************************************************************# 向dept表中插入记录delimiter $$drop procedure insert_dept $$create procedure insert_dept(in start int(10),in max_num int(10))begindeclare i int default 0; set autocommit = 0; repeat set i = i + 1; insert into dept values ((start+i) ,rand_string(10),rand_string(8)); until i = max_num end repeat; commit; end $$delimiter ;call insert_dept(100,10);#------------------------------------------------#向salgrade 表插入数据delimiter $$drop procedure insert_salgrade $$create procedure insert_salgrade(in start int(10),in max_num int(10))begindeclare i int default 0; set autocommit = 0; ALTER TABLE emp DISABLE KEYS; repeat set i = i + 1; insert into salgrade values ((start+i) ,(start+i),(start+i)); until i = max_num end repeat; commit; end $$delimiter ;#测试不需要了#call insert_salgrade(10000,1000000);