当前位置: 首页>后端>正文

sql serve 为什么不能新建索引 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语句执行消耗的时间。


https://www.xamrdz.com/backend/3zn1932858.html

相关文章: