第七章 数据库的字符集和相关索引

一、字符集

1.什么是字符集

#常用的字符集
	gbk: 两字节
	utf8：三个字节
	utf8mb4：四个字节

#字符集转换
	只有包含关系的字符集能够互相转换
	
#查看支持的字符集
	mysql> show charset;

2.校验规则

#查看校验规则
	mysql> show collation;

#检验规则种类
| latin1_bin
| latin1_general_ci
| latin1_general_cs

#区别
	bin和cs区分大小写
	ci不区分大小写

3.统一字符集

#1.xshell字符集

#2.linux系统的字符集
	#永久修改：
	1）centOS7：
	[root@db01 ~]# vim /etc/locale.conf
	LANG="zh_CN.UTF-8"
	2）centOS6：
	[root@db01 ~]# vim /etc/sysconfig/i18n
	LANG="zh_CN.UTF-8"
	#临时修改：
	[root@db01 ~]# LANG="zh_CN.UTF-8
	
#3.cmake阶段指定字符集
    #使用utf8字符集
    -DDEFAULT_CHARSET=utf8
    #校验规则
    -DDEFAULT_COLLATION=utf8_general_ci

#4.配置文件可以修改默认的字符集
	[root@db01 ~]# vim /etc/my.cnf
	[mysqld]
	character-set-server=utf8

#5.创建数据库时指定字符集
	mysql> create database zifuji3 charset gbk;
	
#6.在库下建表
	在库下面建表，表的字符集随着库走，不走默认与cmake的字符集
	
#7.建表时指定字符集
	mysql> create table test2(id int) charset utf8mb4;
	
#8.修改已有表的字符集
	mysql> alter table test charset utf8mb4;

二、索引概述

1.什么是索引

1.索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2.让获取的数据更有目的性，从而提高数据库检索数据的性能。

2.索引的种类

1.BTREE: BTREE  B+TREE  B*TREE
2.HASH：HASH索引（只有memery存储引擎支持）
3.FULLTEXT：全文索引
4.RTREE：R树索引

1）BTREE树索引

2）B+TREE树索引

3）B*TREE树索引

三、根据算法索引的分类

索引建立在表的列上(字段)的。
在where后面的列建立索引才会加快查询速度。
pages <---- 索引（属性）<---- 查数据。

1.主键索引（聚集索引）

#创建主键索引
1.建表时创建主键索引
	1）方法1：
	mysql> create table student(id int unsigned primary key auto_increment comment '学生id');
	2）方法2：
	mysql> create table student1(id int unsigned NOT NULL AUTO_INCREMENT COMMENT '学生id', PRIMARY KEY (id));

2.已经有的表，添加主键索引
#建表
mysql> create table student2(id int unsigned NOT NULL COMMENT '学生id',name varchar(10) NOT NULL COMMENT '学生姓名');
#添加主键索引
mysql> alter table student2 add primary key pri_key(id);

2.唯一建索引

1）创建表时添加唯一键索引

1.方法1：
mysql> create table student3(id int unsigned NOT NULL unique key COMMENT '学生id');

2.方法2：
mysql> create table student4(id int unsigned NOT NULL AUTO_INCREMENT COMMENT '学生id', unique key (id));

2）已经有的表，添加唯一键索引

#建表
mysql> create table student5(id int unsigned NOT NULL COMMENT '学生id',name varchar(10) NOT NULL COMMENT '学生姓名');
#添加主键索引
mysql> alter table student2 add unique key uni_key(id);

3）如何确定哪一列可以创建唯一键

#1.查看表中某一字段的总数据
mysql> select count(name) from country;
+-------------+
| count(name) |
+-------------+
|         239 |
+-------------+
1 row in set (0.00 sec)

#2.去重查看表中某一列数据
mysql> select distinct(name) from country;
mysql> select distinct(count(name)) from country;
+---------------+
| (count(name)) |
+---------------+
|           239 |
+---------------+
1 row in set (0.00 sec)

#3.查看以上两个值是否完全相同

#4.如果相等则可以创建唯一建索引
mysql> alter table country add unique key uni_key(name);

4）函数

now()		  #当前时间的函数
database()	  #查看当前库的函数
count()		  #查看数据总数的函数，count(主键)
distinct()	  #去重数据的函数
PASSWORD()	  #数据库密码加密函数

max()		#取某一列最大值
min()		#取某一列最小值
avg()		#取某一列平均值
sum()		#取某一列总数

3.普通索引（辅助索引）

1）创建索引

1.方法1：
mysql> alter table city add index index_key(name);

2.方法2：
mysql> create index idx_key on city(District);

4.全文索引

1）建表时创建全文索引

mysql> create table xiaoshuo(id int,bookname varchar(20),author varchar(10),content text,FULLTEXT(content));

2）插入数据

mysql> insert into xiaoshuo values('1','西游演义','lhd','上回书说到张飞长坂坡三打白骨精救宋江');
Query OK, 1 row affected (0.00 sec)

mysql> select * from xiaoshuo;
+------+--------------+--------+--------------------------------------------------------+
| id   | bookname     | author | content                                                |
+------+--------------+--------+--------------------------------------------------------+
|    1 | 西游演义     | lhd    | 上回书说到张飞长坂坡三打白骨精救宋江                   |
+------+--------------+--------+--------------------------------------------------------+
1 row in set (0.00 sec)

3）使用索引查询数据

mysql> select * from xiaoshuo where match(content) against('上回书说到张飞长坂坡三打白骨精救宋江');
+------+--------------+--------+--------------------------------------------------------+
| id   | bookname     | author | content                                                |
+------+--------------+--------+--------------------------------------------------------+
|    1 | 西游演义     | lhd    | 上回书说到张飞长坂坡三打白骨精救宋江                   |
+------+--------------+--------+--------------------------------------------------------+
1 row in set (0.00 sec)

5.查看索引

1.方式1：
mysql> desc city;
+-------------+----------+------+-----+
| Field       | Type     | Null | Key |
+-------------+----------+------+-----+
| ID          | int(11)  | NO   | PRI |		#主键索引
| Name        | char(35) | NO   | MUL |		#普通索引
| CountryCode | char(3)  | NO   | UNI |		#唯一键索引
| District    | char(20) | NO   | MUL |
| Population  | int(11)  | NO   |     |
+-------------+----------+------+-----+

2.方式2：
mysql> show index from city;

6.删除索引

mysql> alter table city drop index idx_key;

四、根据设置方法分类

1.创建索引时会对数据进行重新排序
2.建立索引会占用磁盘空间，所以索引不是越多越好
3.在同一列避免创建多个索引
4.避免在大数据的列创建索引，如果非要创建就创建前缀索引

1.前缀索引

#创建根据District列前三个字符进行排序的索引
mysql> alter table city add index District_key(District(3));

#查看表的索引
mysql> show index from city;

2.联合索引

1）案例

#1.创建一个库
mysql> create database xiangqin;

#2.创建一个表
mysql> use xiangqin;
mysql> create table user(id int,name varchar(10),sex enum('male','fmale'),age tinyint,weight int,height int,money int,look varchar(10));

#3.插入数据
mysql> insert into user values('1','qiudao','male','38','180','120','-100000','very ugly'),('2','dawei','male','18','130','182','10000','Handsome'),('3','bingbing','fmale','30','110','170','100000000','beautiful');

#4.查看数据
mysql> select * from user;
+------+----------+-------+------+--------+--------+-----------+-----------+
| id   | name     | sex   | age  | weight | height | money     | look      |
+------+----------+-------+------+--------+--------+-----------+-----------+
|    1 | qiudao   | male  |   38 |    180 |    120 |   -100000 | very ugly |
|    2 | dawei    | male  |   18 |    130 |    182 |     10000 | Handsome  |
|    3 | bingbing | fmale |   30 |    110 |    170 | 100000000 | beautiful |
+------+----------+-------+------+--------+--------+-----------+-----------+
3 rows in set (0.00 sec)

#5.建立联合索引
mysql> alter table user add index index_all(sex,age,money,look);

#6.查看索引
mysql> show index from user;

2）注意

where sex='' and age='' and money='' and look='';
index(sex,age,money,look)

特点：前缀生效特性
#可以走索引
sex
sex,age
sex,age,money
sex,age,money,look

#部分走索引：
只要包含联合索引的第一个条件的查询语句都部分走索引

#不走索引的情况
age,money,look 
age,look 
money,look 
money look 
age,sex ... 

#原则：把最常用来做为条件查询的列放在最前面