数据库索引篇(mysql)

基础

概念

索引是数据库除表以外大型数据库系统中最重要的对象了.他是一种树状结构.

索引的能力

• 提供唯一的码值
• 提高查询性能

种类

• 聚簇索引:在物理上和表融合在一起的视图,共享存储区域.但是一个表只允许存在一个聚簇索引
• 非聚簇索引:物理上,索引数据和表数据是分离的.

mysql中,MyISAM使用的非聚簇索引,InnoDB使用的是聚簇索引

使用索引的注意事项

1. 对于只有少量数据的表,使用索引查询没有任何好处.应当省掉存取和使用索引块的开销
2. 如果索引字段中有很多不同的数据值和空值时,使用索引会极大的提高性能
3. 提高查询的返回数据记录数的期望是25%(根据DBMS的不同配置,该数有所不同),相反,若返回的数据记录较多,则使用索引不会有太多的好处
4. 索引提高了查询速度,但也降低了更新速度.因此,再进行大量更新操作之前,应该删除一些不必要的索引,更新完毕后再重新创建索引
5. 索引也会占用数据库空间,所以设计数据库可用空间时,应当考虑索引所占用的空间
6. 在某个字段上创建索引时,应当考虑是否经常使用该字段进行筛选.如果不是,就不应该创建索引
7. 尽量不要对经常需要更新的字段创建索引
8. 尽量不要将索引和表存储在同一个驱动器上,分开存储会避免访问冲突,提高性能

创建索引

使用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); 

使用时间: