MYSQL索引及其相关性能测试

以下部分为索引相关知识，记录以备忘。

1.

不应该创建索引的字段:
对于那些在查询中很少使用或者参考的列不应该创建索引。
索引不会包含有NULL值的列。
对于那些只有很少数据值的列也不应该增加索引。
对于那些定义为text, image和bit数据类型的列不应该增加索引。 
当修改性能远远大于检索性能时，不应该创建索引。


2.
索引分为：
主键索引(primary key):特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引
唯一索引(unique):索引列的值必须唯一，但允许有空值。
普通索引:没有任何限制。
创建索引语句：
CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead.!!!!!!! 
CREATE UNIQUE INDEX indexName ON tableName (col1);
CREATE INDEX table_name_col1 ON table_name (col1);
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`);
ALTER TABLE `table_name` ADD UNIQUE (`column`);
ALTER TABLE `table_name` ADD INDEX index_name (`column`)




查看索引：show index from table_name;
删除索引：DROP INDEX index_name ON talbe_name
清除mysql表中数据:
  delete from 表名;
  truncate table表名;   truncate相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。
  
3.性能测试验证
性能分析方法：
  1、慢查询 （分析出现出问题的sql）
  2、Explain （显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句）
  3、Profile（查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等.）
得到执行sql语句的时间：
方法一：show profiles 之类的语句
方法二：
timestampdiff来查看测试时间:
这种方法有一点要注意，就是三条sql语句要尽量连一起执行，不然误差太大，根本不准
set @d=now(); 
select * from comment; 
select timestampdiff(second,@d,now()); 
如果是用命令行来执行的话，有一点要注意，就是在select timestampdiff(second,@d,now());后面，一定要多copy一个空行，不然最后一个sql要你自己按回车执行，这样就不准了。  


  
和存储过程有关的权限有三种： 
ALTER ROUTINE 编辑或删除存储过程 
CREATE ROUTINE 建立存储过程 
EXECUTE 运行存储过程
在使用GRANT创建用户的时候分配这三种权限。 存储过程在运行的时候默认是使用建立者的权限运行的。  


用管理员dba用户登录mysql:  ********************** 
  
显示数据库用户权限： show grants for user1';  
grant create routine ON iconldb.* to user1;   建立存储过程 
grant ALTER ROUTINE ON iconldb.* to user1;    编辑或删除存储过程
grant EXECUTE ON iconldb.* to user1;          运行存储过程
grant index on iconldb.* to user1;     操作 MySQL索引权限
修改完权限以后 一定要刷新服务，或者重启服务，刷新服务用：FLUSH PRIVILEGES。






用普通用户登录mysql:  mysql -h xxx -P xxx -u xxx -p    密码：xxx
show databases;
use xxx;
Select  version(); //数据库版本


mysql>show profiles;分析sql性能。
Empty set (0.00 sec)
显示为空，说明profiles功能是关闭的。下面开启
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)


ROUND(RAND() * 89 + 10) 生成随机整数：10~99
ROUND(RAND() * 100)     生成随机整数：0~100
造测试数据（创建存储过程）：
DELIMITER //
create procedure myproc1()   
begin
DECLARE num INT;     
set num=1;   
while num <= 100000 do   
insert语句
set num=num+1;  
end while;
end;
// 
call myproc1();




分别在创建索引前和创建索引后，执行相关的SQL语句。

mysql> show profiles;查看sql语句执行消耗的时间。

参考链接：

http://www.jb51.net/article/45185.htm

http://www.cnblogs.com/fslnet/p/3143344.html