mysql索引

（一）索引的作用

    数据库中的表如果没有索引，每次查询都会进行全表扫描，数据量少还可以，一旦数据量超过40w以上，每次查询都要十几秒以上甚至更多；

      所以提升查询速度在软件上的优化就是：

        （1）给数据库表加索引；

        （2）优化sql语句；

（二）索引的类型

      5种：主键索引，普通索引，聚合索引（聚合与非聚合）， 唯一索引，全文索引

      主键索引：也就是表的主键，设置主键primary key的时候，mysql会自动创建主键索引

      普通索引：也就是非主键的单列索引

      聚合索引：也就是多列组合索引

（三）创建索引语法

      创建普通索引：alert table 表 add index 索引名（字段名）

      创建聚合索引：alert table 表 add index 索引名（字段名1，字段名2...）

      查看索引：show index from 表

      删除索引：drop index 索引名 on 表

（四）DEMO

      创建表 test_user

DROP TABLE IF EXISTS `test_user`;  
CREATE TABLE `test_user` (  
  `id` BIGINT(20)  PRIMARY KEY NOT NULL AUTO_INCREMENT,  
  `username` VARCHAR(11) DEFAULT NULL,  
  `gender` VARCHAR(2) DEFAULT NULL,  
  `password` VARCHAR(100) DEFAULT NULL  
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

      创建存储过程

DELIMITER $$

USE `index_test`$$

DROP PROCEDURE IF EXISTS `myproc`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `myproc`()
BEGIN   
  DECLARE num INT;
	SET num=1;
	WHILE num <= 10000000 DO
	INSERT INTO test_user(username,gender,PASSWORD) VALUES(num,'保密',PASSWORD(num));
	SET num=num+1;
	END WHILE;
 END$$

DELIMITER ;

      调用存储过程

CALL `myproc`;

（1）测试1：

执行

select id,username,gender,password from test_user where username='9000000'；

再给username列加上普通索引

ALTER TABLE `test_user` ADD INDEX index_name(username)；

再执行：

selectid,username,gender,password from test_user where username='9000000'；

（2）测试2：

用username和password来联合查询；

select id,username,gender,password  from test_user where username='9000000' or `password`='*3A70E147E88D99888804E4D472410EFD9CD890AE'

虽然username加了索引，但是password列未加索引，索引执行password筛选的时候，还是会全表扫描，因此此时 查询速度立马降了下来。

当我们的sql有多个列的筛选条件的时候，就需要对查询的多个列都加索引组成聚合索引：

加上聚合索引：

ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)

再执行：

select id,username,gender,password  from test_user where username='9000000' or `password`='*3A70E147E88D99888804E4D472410EFD9CD890AE'

（五）注意

如果数据量过了百万，加了索引后效果还是不理想，使用集群；

1：加了索引，依然全表扫描的可能情况有：

索引列为字符串，而没带引号；

索引列没出现在where条件后面；

索引列出现的位置没在前面。

2：关联查询不走索引的可能情况有：

关联的多张表的字符集不一样；

关联的字段的字符集不一样；

存储引擎不一样；

字段的长度不一样。