基础
概念
索引是数据库除表以外大型数据库系统中最重要的对象了.他是一种树状结构.
索引的能力
- 提供唯一的码值
- 提高查询性能
种类
- 聚簇索引:在物理上和表融合在一起的视图,共享存储区域.但是一个表只允许存在一个聚簇索引
- 非聚簇索引:物理上,索引数据和表数据是分离的.
mysql中,MyISAM使用的非聚簇索引,InnoDB使用的是聚簇索引
使用索引的注意事项
- 对于只有少量数据的表,使用索引查询没有任何好处.应当省掉存取和使用索引块的开销
- 如果索引字段中有很多不同的数据值和空值时,使用索引会极大的提高性能
- 提高查询的返回数据记录数的期望是25%(根据DBMS的不同配置,该数有所不同),相反,若返回的数据记录较多,则使用索引不会有太多的好处
- 索引提高了查询速度,但也降低了更新速度.因此,再进行大量更新操作之前,应该删除一些不必要的索引,更新完毕后再重新创建索引
- 索引也会占用数据库空间,所以设计数据库可用空间时,应当考虑索引所占用的空间
- 在某个字段上创建索引时,应当考虑是否经常使用该字段进行筛选.如果不是,就不应该创建索引
- 尽量不要对经常需要更新的字段创建索引
- 尽量不要将索引和表存储在同一个驱动器上,分开存储会避免访问冲突,提高性能
创建索引
使用ALTER TABLE语句创建索性
ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)[索引名](字段名)
//普通索引
alter table table_name add index index_name (column_list) ;
//唯一索引
alter table table_name add unique (column_list) ;
//主键索引
alter table table_name add primary key (column_list) ;
使用CREATE INDEX语句对表增加索引
CREATE INDEX可用于对表增加普通索引或UNIQUE索引,可用于建表时创建索引。
CREATE INDEX index_name ON table_name(table_name(length));
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
//create只能添加这两种索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
删除索引
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
实例效率
手上没有现成的大数量的数据
通过数据库过程创建千万级别的数据量
创建数据库 表
CREATE DATABASE`mydbtotest`;
USE `mydbtotest`;
DROP TABLE IF EXISTS `people`;
# MyISAM引擎的表
CREATE TABLE `people` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT NULL,
`uname` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM ;
添加千万级数据
如果用单层循环,直接循环插入千万次,这个效率也是很低下的.但是单行插入的数据量有限制,我试了超过10W级的就不行了,所以采用批量插入结合多次的方式,这也是有两个循环的原因
# 将结束字符";"变为//
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `autoinsert`(IN num INT,IN batch INT)
BEGIN
SET @insert_value = '';
# 已经插入的记录总行数
SET @count = 0;
#
SET @batch_count = 0;
WHILE @count < num DO
# 内while循环用于拼接INSERT INTO t VALUES (),(),(),...语句中VALUES后面部分
WHILE (@batch_count < batch AND @count < num) DO
IF @batch_count>0
THEN
SET @insert_value = CONCAT(@insert_value,',');
END IF;
SET @insert_value = CONCAT(@insert_value,"(FLOOR(RAND()*30), SUBSTR(MD5(RAND()),1,6))");
SET @batch_count = @batch_count+1;
END WHILE;
SET @count = @count + @batch_count;
# 拼接SQL语句并执行
SET @exesql = CONCAT("INSERT INTO people (age, uname) values ", @insert_value);
PREPARE stmt FROM @exesql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
# 重置变量值
SET @insert_value = '';
SET @batch_count=0;
END WHILE;
# 数据插入完成后,查看表中总记录数
SELECT COUNT(id) FROM people;
END//
# 将结束字符变回";"
DELIMITER ;
#执行过程
CALL autoinsert(10000000,3000);#总数为1千万条,分3000次执行
进行查询比较效率
不加索引
SELECT
age,
COUNT(age)
FROM
mydbtotest.people
WHERE age < 10
GROUP BY age
使用时间:
为age添加索引
CREATE INDEX age_index ON people(age);
使用时间: