如果一张表工作很久没有主键可以使用以下命令添加主键
alter table 表名 change id(哪一列) id int primary key auto_increment;删除建表时的索引
alter table 表名 drop index index_name添加索引：
尽量在唯一值多的大表上建立索引
数据量很大的时候不适合建索引影响用户体验400-500W条记录的表建立索引，花费90-180秒左右
alter table 表名 add index index_name(列名)对字段的前n个字符建立索引：
可以节省创建索引占用的系统空间，降低读取和更新维护索引消耗的系统资源
create index index_name(索引的名字) on 表(列(8));
create index index_createdBy on withdrawinfo(createdBy(8));
alter table 表名 add index index_name(name(8));(索引的名字)
alter table withdrawinfo add index index_lastModifiedBy(lastModifiedBy(8));
+------------------+---------------+------+-----+---------+----------------+
| createdBy | varchar(100) | YES | MUL | NULL | |
| lastModifiedBy | varchar(100) | YES | MUL | NULL | |
+------------------+---------------+------+-----+---------+----------------+
show index from withdrawinfo\G
Key_name: index_createdBy
Sub_part: 8
Key_name: index_lastModifiedBy
Sub_part: 8为多个字段创建联合索引
create index index index_name_字段 on 表名(字段1(大小)，字段2(大小))
create index index_lastModifiedBy_createdBy on withdrawinfo(lastModifiedBy,createdBy);
Key_name: index_lastModifiedBy_createdBy
Key_name: index_lastModifiedBy_createdBy查找唯一值
select distinct count(user) from mysql.user;主键联合索引
show create table mysql.user\G
PRIMARY KEY (`Host`,`User`)创建唯一索引(非主键)
create unique index uni_index_name(字段名) on 表名(name字段名)
create unique index uni_index_optlock on withdrawinfo(optlock);
+------------------+---------------+------+-----+---------+----------------+
| optlock | int(11) | NO | UNI | NULL | |
+------------------+---------------+------+-----+---------+----------------+线上访问慢，临时使用抓慢查询的方法
show full processlist;索引列创建及生效的条件：
问题1、既然索引可以加快查询速度，那么就给所有列建立索引吧？
解答： 因为索引不但占用系统空间，而且更新数据时还需要维护索引数据，因此，索引时一把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需建立索引，插入更新频繁，读取比较少的表要少建立索引。
问题2：需要在哪些表上创建索引才能加快查询速度呢？
select user,host from mysql.user where password=....，索引一定要创建在where后的条件列上，而不是select后的选择数据列上。另外，我们要尽量选择在唯一值多的大表上的列建立索引，例如，男女性别列唯一值，不适合建立索引。
创建索引的基本知识小结：
1、索引类似书籍的目录，会加快查询数据的速度。
2、要在表的列(字段)上创建索引。
3、索引会加快查询速度，但是也会影响更新的速度，因为更新要维护索引速度。
4、索引列并不时越多越好，要在频繁查询的表语句 where 后的条件列上创建索引。
5、小表或重复值很多的列上可以不建索引，要在大表以及重复值少的条件列上创建索引。
6、多个联合索引有前缀生效特性。
7、当字段内容前N个字符已经接近唯一时，可以对字段的前N个字符创建索引。
8、索引从工作方式区分，有主键，唯一，普通索引。
9、索引类型会有BTREE(默认)和 hash(适合做缓存(内存数据库))等。
为什么要优化数据库
起因：
网站出问题，访问很慢。
a.web、网络、存储、db(负载、io、cpu)
紧急处理，登陆数据库：show full processlist;
找出待优化语句
使用explain模拟执行
explain select
mysql -e "use zhrtchina_online;show full processlist;"|egrep -vi "sleep"
Id User Host db Command Time State Info
11 root localhost zhrtchina_online Query 0 init show full processlist忽略缓存
select SQL_NO_CACHE
b.慢查询语句(日志文件)
long_query_time = 1 查询查过1秒的语句记录log日志
log_queries_not_using_indexes 没有使用索引的SQL语句，语句记录log日志
log-slow-queries = /usr/local/mysql_3306/data/3306/slow.log 慢查询log记录位置
c.慢查询日志切割、分析(mysqlsla)、发给dba和开发。
mv /data/3306/slow.log /opt/$(date +%F)_slow.log
mysqladmin -S /usr/local/mysql_3306/data/3306/mysql.sock flush-logs
分析mysql慢查询日志的好工具
强烈推荐--> mysqlsla
使用第二种方法查询
help show profile; #查看帮助
mysql> SELECT @@profiling; #查看是否开启
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
mysql> SET profiling = 1; #设置开启
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@profiling; #查看是否设置成功
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profiles; #查看
+----------+------------+--------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------+
| 1 | 0.00046425 | SELECT @@profiling |
+----------+------------+--------------------+
mysql> select * from city; #做完一次查询接着查看
mysql> show profiles; #出现新的一行
+----------+------------+--------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------+
| 1 | 0.00017475 | SELECT @@profiling |
| 2 | 0.03532575 | select * from city |
+----------+------------+--------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show profile for Query 2; #查看第Query_ID 2行
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000061 |
| checking permissions | 0.000008 |
| Opening tables | 0.000019 |
| init | 0.000020 |
| System lock | 0.000008 |
| optimizing | 0.000005 |
| statistics | 0.000013 |
| preparing | 0.000011 |
| executing | 0.000002 |
| Sending data | 0.035123 |
| end | 0.000012 |
| query end | 0.000007 |
| closing tables | 0.000014 |
| freeing items | 0.000014 |
| cleaning up | 0.000011 |
+----------------------+----------+
15 rows in set, 1 warning (0.04 sec)
mysql> show profile cpu,block io,memory,swaps for query 2;
+----------------------+----------+----------+------------+--------------+---------------+-------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+----------------------+----------+----------+------------+--------------+---------------+-------+
| starting | 0.000061 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| checking permissions | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Opening tables | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| init | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| System lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| optimizing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| statistics | 0.000013 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| preparing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Sending data | 0.035123 | 0.004000 | 0.000000 | 0 | 0 | 0 |
| end | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| query end | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| closing tables | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| freeing items | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| cleaning up | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+-------+
15 rows in set, 1 warning (0.00 sec)show profiles;
