（1）查看表结构MariaDB [oldboy]> desc test1;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id| int(4) | NO | | NULL| || name| char(16) | NO | | NULL| || age | int(2) | YES| | NULL| |+-------+----------+------+-----+---------+-------+（2）explain 查看是否含有建立索引的语句MariaDB [oldboy]> explain select * from test1 where name="kaka"\G*************************** 1. row *************************** id: 1select_type: SIMPLEtable: test1 type: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULL rows: 5 #查询行数，表示当前查询了5行Extra: Using where1 row in set (0.00 sec)（3）创建索引MariaDB [oldboy]> create index index_name on test1(name);Query OK, 0 rows affected (0.05 sec)Records: 0Duplicates: 0Warnings: 0（4）重新查询MariaDB [oldboy]> explain select * from test1 where name="kaka"\G*************************** 1. row *************************** id: 1select_type: SIMPLEtable: test1 type: refpossible_keys: index_namekey: index_namekey_len: 48ref: const rows: 1 #查询行数，表示当前只查询了1行Extra: Using index condition1 row in set (0.00 sec)#从以上例子可以看到，使用索引，可以更快的查询所需要的信息。

每隔2,秒输入：SHOW FULL PROCESSLIST; 如果出现2次说明存在慢查询MariaDB [oldboy]> show full processlist;+----+------+-----------+--------+---------+------+-------+-----------------------+----------+| Id | User | Host| db | Command | Time | State | Info| Progress |+----+------+-----------+--------+---------+------+-------+-----------------------+----------+|9 | root | localhost | oldboy | Query |0 | NULL| show full processlist |0.000 |+----+------+-----------+--------+---------+------+-------+-----------------------+----------+1 row in set (0.00 sec)

配置参数记录慢查询语句log_query_time = 2 #执行超过2s记录到log中log_queries_not_using_indexes#没有走索引的语句，记录log中log-slow-queries = /data/3306/slow.log#log的位置explain select * from test from where name='oldboy'\G#查看是否走索引explain select SQL_NO_CACHE * from test where name='oldboy'\G #去除缓存

生产场景，大表不能高峰期建立索引，例如：300万记录

切割慢查询日志，去重分析后发给大家1）mv，reload进程 2）cp，>清空2）定时任务mv /data/3306/slow.log /opt/$(date +%F)_slow.logmysqladmin -uroot -p123456 flush-logs mysqlsla分析：http://blog.itpub.net/7607759/viewspace-692828/优化起因：1）网站出了问题，访问很慢。a.web服务器的负载、存储、db（负载、io、cpu）登录db：show full processlist2）慢查询语句（日志文件）long_query_time=2#执行超过2s记录到log中log_queries_not_using_indexs#没有走索引的语句，记录log中log-slow-queries=/data/3306/slow.log #log的位置切割，分析，发给管理员案例分析：1.查看是否db存在慢查询：show full processlist;2.explain分析：explain 慢查询的语句3.查看表结构：desc test1;4.定位在哪列建立索引,哪张表5.查看条件字段列的唯一值的数量select count(distinct ader) from ad_oldboy_detail6.建立索引create index ....

了解内容，高级DBA使用help show profile;select @@profiling;set profiling = 1;select @@profiling;show profile;show profile for query 2;http://www.cnblogs.com/adforce/archive/2012/06/02/2532287.html