数据库的索引和建表

一、索引

1、聚集索引

平时建表的时候都会为表加上主键， 在某些关系数据库中， 如果建表时不指定主键，数据库会拒绝建表的语句执行。 事实上， 一个加了主键的表，并不能被称之为「表」。一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐， 跟我认知中的「表」很接近。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，类似整个表就变成了一个索引，也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

上图就是带有主键的表（聚集索引）的结构图。其中树的所有结点（底部除外）的数据都是由主键字段中的数据构成，也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。

假如我们执行一个SQL语句：

select * from table where id = 98;

首先根据索引定位到98这个值所在的叶结点，然后再通过叶结点取到id等于98的数据行。 这里不讲解平衡树的运行细节， 但是从上图能看出，树一共有三层， 从根节点至叶节点只需要经过三次查找就能得到结果。

缺点：平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构， 因此，在每次数据改变时，必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。

2、非聚集索引

非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。 如下图

 

 

//创建非聚集索引

create index index_birthday on user_info(birthday);

//查询生日在1995年11月1日出生用户的用户名

select user_name from user_info where birthday = '1995-11-01'

这句SQL语句的执行过程如下

首先，通过非聚集索引index_birthday查找birthday等于1995-11-01的所有记录的主键id值。然后，通过得到的主键id值执行聚集索引查找，找到主键id值对应的真实数据（数据行）存储的位置。最后，从得到的真实数据中取得user_name字段的值返回，取得最终的结果

非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ，再使用主键的值通过聚集索引查找到需要的数据。

3、复合索引

有一种例外可以不使用聚集索引就能查询出所需要的数据， 这种索引称之为「覆盖索引」查询， 也就是复合索引或者多字段索引查询。当为字段建立索引以后，字段中的内容会被同步到索引之中，如果为一个索引指定两个字段，那么这个两个字段的内容都会被同步至索引之中。

先看下面这个SQL语句

//创建复合索引

create index index_birthday_and_user_name on user_info(birthday, user_name);

select user_name from user_info where birthday = '1995-11-01'

这句SQL语句的执行过程:

通过非聚集索引index_birthday_and_user_name查找birthday等于1995-11-01的叶节点的内容，叶节点中除了有user_name表主键id的值以外user_name字段的值也在里面，因此不需要通过主键id值去查找数据行的真实所在，直接取得叶节点中user_name的值返回即可，大大的提高了查询性能。

4、注意点

最左前缀匹配原则，非常重要的原则

create index index_name_email on user(name,email)

- 最左前缀匹配：必须按照从左到右的顺序匹配

select * from user where name='zhangsan'; #可以

select * from user where name='zhangsan'and email='163.com'; #可以

select * from user where email='zhangsan@163.com'; #不可以

 

二、建表

1.新建用户

1.1 登录MYSQL：

C:\mysql\mysql-5.6.41-winx64\bin>mysql -u root -p

Enter password: **********

 

1.2 创建用户：

mysql> insert into mysql.user(Host,User,Password) values("localhost","demo",password("1234"));

这样就创建了一个名为：demo 密码为：1234 的用户。

注意：此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。

 

1.3 然后登录一下：

mysql>exit;

C:\mysql\mysql-5.6.41-winx64\bin>mysql -u demo -p

Enter password: **********

2.为用户授权

授权格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";　

2.1 登录MYSQL（有ROOT权限），这里以ROOT身份登录：

C:\mysql\mysql-5.6.41-winx64\bin>mysql -u demo -p

Enter password: **********

 

2.2 首先为用户创建一个数据库(testDB)：

mysql>create database demoDB;

 

2.3 授权test用户拥有testDB数据库的所有权限（某个数据库的所有权限）：

mysql>grant all privileges on demoDB.* to demo@localhost identified by '1234';

mysql>flush privileges;//刷新系统权限表

格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";　

 

2.4 如果想指定部分权限给一用户，可以这样来写:

mysql>grant select,update on demoDB.* to demo@localhost identified by '1234';

mysql>flush privileges; //刷新系统权限表

 

2.5 授权test用户拥有所有数据库的某些权限： 　

mysql>grant select,delete,update,create,drop on *.* to demo@"%" identified by "1234";

//test用户对所有数据库都有select,delete,update,create,drop 权限。

//@"%" 表示对所有非本地主机授权，不包括localhost。（localhost地址设为127.0.0.1，如果设为真实的本地地址，不知道是否可以，没有验证。）

//对localhost授权：加上一句grant all privileges on demoDB.* to demo@localhost identified by '1234';即可。

3. 删除用户

C:\mysql\mysql-5.6.41-winx64\bin>mysql -u demo -p

Enter password: **********

mysql>Delete FROM user Where User='demo' and Host='localhost';

mysql>flush privileges;

mysql>drop database demoDB; //删除用户的数据库

删除账户及权限：>drop user 用户名@'%';

　　　　　　　　>drop user 用户名@ localhost;

4. 修改指定用户密码

C:\mysql\mysql-5.6.41-winx64\bin>mysql -u demo -p

Enter password: **********

mysql>update mysql.user set password=password('新密码') where User="demo" and Host="localhost";

mysql>flush privileges;

5. 列出所有数据库

mysql>show database;

6. 切换数据库

mysql>use '数据库名';

7. 列出所有表

mysql>show tables;

8. 显示数据表结构

mysql>describe 表名;

9. 删除数据库和数据表

mysql>drop database 数据库名;

mysql>drop table 数据表名;