mysql建立索引 性能测试_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..com/fslnet/p/3143344.html