mysql索引简单介绍及优化

dfsfsf

索引的原理
索引是建立了针对数据内容的排序结果的指针。快速定位到索引的数据，像书本的目录一样 它的功能是应用在表上  的一种供用户快速读取
索引的类型
索引默认分以下几种
index_type:BTREE #默认索引是BTREE  Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE
建立索引
建立索引，一般可以建立以某一列为索引，或者复合索引（多个列在一起建立索引，有优先顺序） 索引建立要保证  不要建立无用的索引 通过某些sql语句可以查看建立的索引有没有被使用到。索引也不是越多越好
索引的设计原则
1；索引列一般为wehere子句中的列或者连接子句中的列
例如
  mysql> select *from student where Sno=6;    #以where后面的子句Son作为索引
2；尽量不对基数小的做索引，说白了就是某个列中唯一值很多，重复值很少 比如性别的列就只有男女，那么它的基  数也就只有2 像年龄列是最好不要做索引的，它的基数也比较少 以下就是我查某个列（Sage）的基数是多少的结果
mysql> select count(distinct Sage) from student;
+----------------------+
| count(distinct Sage) |
+----------------------+
|                    6 |
+----------------------+
1 row in set
3；尽可能使用短索引：如果对字符列索引尽量指定最小长度。 #比如一个索引“我是XX” “XX”是唯一就用这个
索引的创建字段值必须保存短小唯一性
4；复合索引前缀特性，索引的顺序很重要 #简单就是条件越苛刻 结果越少key(a) key(a,b) key(a,b,c)

正式建立索引

1；在已经建立好的表中再建立索引
mysql> use oldboy;
Database changed
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno   | int(10)     | NO   | PRI | NULL    |       |
| Sname | varchar(16) | NO   |     | NULL    |       |
| Ssex  | char(2)     | NO   |     | NULL    |       |
| Sage  | tinyint(2)  | NO   |     | 0       |       |
| Sdept | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set
#可以发现这个表的结构中没有索引
  #创建索引

mysql> create index ind_Sname on student(sname);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
#ind_Sname 索引名  student(sname)  student表下的sname列为索引

再查看表student结构可以发现Key建下有个MUL值 表示已经建立好索引了
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno   | int(10)     | NO   | PRI | NULL    |       |
| Sname | varchar(16) | NO   | MUL | NULL    |       |
| Ssex  | char(2)     | NO   |     | NULL    |       |
| Sage  | tinyint(2)  | NO   |     | 0       |       |
| Sdept | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set


另一种在建立表之后建索引的方式
mysql> alter table student add index ind_Sage (Sage);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0


2；在建表时同时建立索引
mysql> create table student2(
     Sno int(10) NOT NULL COMMENT '学号',
     Sname varchar(16) NOT NULL COMMENT '姓名',
     Ssex char(2) NOT NULL COMMENT '性别',
     Sage tinyint(2)  NOT NULL default '0' COMMENT '学生年龄',
     Sdept varchar(16)  default NULL  COMMENT '学生所在系别',
     PRIMARY KEY  (Sno),
     KEY `ind_Sname`(`Sname`)
     ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=gbk;
Query OK, 0 rows affected

#查看表结构
mysql> desc student2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno   | int(10)     | NO   | PRI | NULL    |       |
| Sname | varchar(16) | NO   | MUL | NULL    |       |
| Ssex  | char(2)     | NO   |     | NULL    |       |
| Sage  | tinyint(2)  | NO   |     | 0       |       |
| Sdept | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set

3；还有重建索引，这个我不知道了


查看索引，因为索引是在表之上的，索引查看某个表的索引如下


删除索引
mysql> drop index ind_Sname on student;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
#ind_Sname索引名称 用语句 mysql> show index from student; 可以发现ind_Sname索引已经没有了 列Sname的key值上也没有了MUL
mysql> desc student;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno   | int(10)     | NO   | PRI | NULL    |       |
| Sname | varchar(16) | NO   |     | NULL    |       |
| Ssex  | char(2)     | NO   |     | NULL    |       |
| Sage  | tinyint(2)  | NO   | MUL | 0       |       |
| Sdept | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set


创建复合索引
mysql> create index Sage_Sdept on student(Sage,Sdept(10));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
#Sage_Sdept 复合索引名 student(Sage,Sdept(10))对student表中的Sage列做完整索引Sdept列的前10个字符做索引

查看建表语句 复合索引已经出来了。
CREATE TABLE `student` (
  `Sno` int(10) NOT NULL COMMENT '学号',
  `Sname` varchar(16) NOT NULL COMMENT '姓名',
  `Ssex` char(2) NOT NULL COMMENT '性别',
  `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄',
  `Sdept` varchar(16) DEFAULT NULL COMMENT '学生所在系别',
  PRIMARY KEY (`Sno`),
  KEY `Sage_Sdept` (`Sage`,`Sdept`(10))
) ENGINE=InnoDB DEFAULT CHARSET=gbk |



检验查询语句是否走索引 用语句explain select * from student where Sno=6;查看子句where Sno=6
mysql> explain select * from student where Sno=6;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | student | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set
#SIMPLE表示效率很高，key不为空表示走索引

mysql> explain select * from student where Sdept='中午专业';
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | student | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set
#表示没走索引