linux系统mysql字符集

一、select高级用法

1.传统连接

世界上小于100人的城市在哪个国家？是用什么语言？

#1.分析要哪些内容？
城市的人口数量    城市名字    国家名字    国家语言

#2.分析数据所在库
city.population   city.name   country.name    countrylanguage.language

#3.找出三个表相关联内容
city.countrycode    country.code    countrylanguage.countrycode

#3.编写语句
select country.name,city.name,city.population,countrylanguage.language from city,country,countrylanguage where city.countrycode=country.code and country.code=countrylanguage.countrycode and city.population < 100;

2.自连接

#自己寻找两个表相关联的字段和数据
1.两个表字段必须完全相同
2.两个表字段下的数据必须完全相同

SELECT city.name,city.countrycode,countrylanguage.language,city.population
FROM city NATURAL JOIN countrylanguage 
WHERE population < 100
ORDER BY population;

3.内连接

1）语法格式

select * from 表1 join 表2 on 相关联的条件 where 条件;

#注意：命中率（驱动的概念）
	表1 小表
	表2 大表
	
select * from 表1 inner join 表2 on 相关联的条件 where 条件;

2）例子1：两表联查

#小于100人的城市在哪个国家，国家代码是什么？
select city.name,city.population,city.countrycode,country.name 
from city join country on city.countrycode=country.code 
where city.population < 100;

3）例子2：三表联查

#世界上小于100人的城市在哪个国家？是用什么语言？
select country.name,city.name,city.population,countrylanguage.language
from city join country on city.countrycode=country.code 
join countrylanguage on country.code=countrylanguage.countrycode
where city.population < 100;

4.外连接（有问题）

1）左外连接

select city.name,city.countrycode,country.name,city.population
from city left join country 
on city.countrycode=country.code 
and city.population < 100;

2）右外连接

select city.name,city.countrycode,country.name,city.population
from city right join country 
on city.countrycode=country.code
and city.population < 100;

二、字符集

1.什么是字符集

#字符集：是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。

最早的字符集：ASCII码

中国的字符集：gbk，utf8，gbk2312
日本字符集：shift-JIS
韩国字符集：Euc-kr

万国编码：Unicode字符集

#常用的字符集：
	gbk:一个汉字占用2个字节
	utf8:一个汉字占用3个字节
	utf8mb4:一个汉字占用4个字节
	
#字符集修改要求：
	包含关系才可以修改
	
#查看字符集：
	mysql> show charset;

2.校验规则

#查看校验规则
mysql> show collation;
| latin7_general_ci        | latin7   |
| latin7_general_cs        | latin7   |
| latin7_bin               | latin7   |

#校验规则区别
1.以ci结尾：不区分大小写
2.以cs或者bin结尾：区分大小写

#大小写不同，相同字段也不能添加
mysql> alter table city add nAME varchar(10);
ERROR 1060 (42S21): Duplicate column name 'nAME'

3.统一字符集

#1.xshell字符集

#2.linux系统字符集
	#临时修改
	[root@db03 ~]# LANG=zh_CN.UTF-8
	#永久修改
	[root@db03 ~]# vim /etc/sysconfig/i18n		#Centos6
	[root@db03 ~]# vim /etc/locale.conf 		#Centos7
	LANG="zh_CN.UTF-8"
	
#3.数据库字符集
	1）cmake 字符集指定
	cmake . 
	-DDEFAULT_CHARSET=utf8 
	-DDEFAULT_COLLATION=utf8_general_ci 
	-DWITH_EXTRA_CHARSETS=all 
	2）配置文件
	[root@db03 ~]# vim /etc/my.cnf
	[mysqld]
	character-set-server=utf8
	
#4.创建数据库时指定字符集
mysql> create database db7 charset utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show create database db7;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db7      | CREATE DATABASE `db7` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

#5.建表时根据库的字符集来建表
mysql> create table tb1(id int);

#6.指定字符集建表
mysql> create table tb3(id int) charset utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table tb3;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                             |
+-------+------------------------------------------------------------------------------------------+
| tb3   | CREATE TABLE `tb3` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#7.修改库的字符集
mysql> show create database db7;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db7      | CREATE DATABASE `db7` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database db7 charset gbk;
Query OK, 1 row affected (0.00 sec)

mysql> show create database db7;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| db7      | CREATE DATABASE `db7` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

#8.修改表的字符集
mysql> show create table tb2;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| tb2   | CREATE TABLE `tb2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table tb2 charset gbk;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb2;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| tb2   | CREATE TABLE `tb2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

三、索引

1.什么是索引

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

2.索引的种类

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

1）Btree索引

2）B+tree索引

3）B*tree索引

3索引根据算法分类

索引是建立在数据库字段上面的
当where条件后面接的内容有索引的时候，会提高速度

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

#创建表的时候创建主键索引
mysql> create table test(id int not null auto_increment primary key comment '学号');
Query OK, 0 rows affected (0.04 sec)

mysql> create table test1(id int not null auto_increment,primary key(id));
Query OK, 0 rows affected (0.04 sec)

#查看索引命令
mysql> show index from test;

#已经有表时添加主键索引
mysql> alter table student add primary key pri_id(id);

2）唯一键索引

#创建表的时候创建唯一键索引
mysql> create table test2(id int not null auto_increment unique key comment '学号');
Query OK, 0 rows affected (0.04 sec)

#已经有表时添加唯一键索引
mysql> alter table student add unique key uni_key(name);

#注意：创建唯一建索引或主键索引的列不能有重复数据
判断一列能否做唯一建索引
1.查询数据总量
mysql> select count(name) from city;
2.去重查看该列数据总量
mysql> select count(distinct(name)) from city;

#以上两个值相等则可以设置唯一建索引

例：

#1.查看列的总数据量
mysql> select count(name) from country;
+-------------+
| count(name) |
+-------------+
|         239 |
+-------------+
1 row in set (0.00 sec)
#2.查看去重后数据量
mysql> select count(distinct(name)) from country;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
|                   239 |
+-----------------------+
1 row in set (0.00 sec)
#3.创建唯一建索引
mysql> alter table country add unique key uni_key(name);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> alter table city add index inx_name(name);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index index_District on city(District);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

4）全文索引

mysql> create table txt(id int,bookname varchar(12),wenzhang text,fulltext(wenzhang));
Query OK, 0 rows affected (0.20 sec)

mysql> select * from txt where match(wenzhang) against('查询的内容');

#实例
mysql> create table text(id int,bookname varchar(12) charset utf8,wenzhang text charset utf8,fulltext(wenzhang));
Query OK, 0 rows affected (0.21 sec)

mysql> insert into text values(1,'红楼梦','上回书说到张飞长坂坡三打白骨精救出宋江');
Query OK, 1 row affected (0.01 sec)

mysql> select * from text;
+------+-----------+-----------------------------------------------------------+
| id   | bookname  | wenzhang                                                  |
+------+-----------+-----------------------------------------------------------+
|    1 | 红楼梦    | 上回书说到张飞长坂坡三打白骨精救出宋江                    |
+------+-----------+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from text where match(wenzhang) against('上回书说到张飞长坂坡三打白骨精救出宋江');
+------+-----------+-----------------------------------------------------------+
| id   | bookname  | wenzhang                                                  |
+------+-----------+-----------------------------------------------------------+
|    1 | 红楼梦    | 上回书说到张飞长坂坡三打白骨精救出宋江                    |
+------+-----------+-----------------------------------------------------------+
1 row in set (0.00 sec)

5）查看索引

#方式一：
mysql> show index from city;

#方式二：
mysql> desc city;
+-----+
| Key |
+-----+
| PRI |		#主键索引
| MUL |		#普通索引
| UNI |		#唯一键索引
| MUL |
+-----+

6）删除索引

mysql> alter table city drop index index_District;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

目录

• 一、select高级用法
  • 1.传统连接
  • 2.自连接
  • 3.内连接
    • 1）语法格式
    • 2）例子1：两表联查
    • 3）例子2：三表联查
  • 4.外连接（有问题）
    • 1）左外连接
    • 2）右外连接
• 二、字符集
  • 1.什么是字符集
  • 2.校验规则
  • 3.统一字符集
• 三、索引
  • 1.什么是索引
  • 2.索引的种类
    • 1）Btree索引
    • 2）B+tree索引
    • 3）B*tree索引
  • 3索引根据算法分类
    • 1）主键索引（聚集索引）
    • 2）唯一键索引
    • 例：
    • 3）普通索引（辅助索引）
    • 4）全文索引
    • 5）查看索引
    • 6）删除索引
    • 1.索引作用
    • 2. 索引的种类（算法）
    • 3. 索引管理
      • 3.1 索引分类
      • 3.2 管理索引
      • 3.3 唯一索引
      • 3.4 前缀索引
      • 3.5 联合索引
    • 4. 执行计划分析explain
      • 4.1 介绍
      • 4.2 全表扫描
      • 4.3索引扫描
    • 5. 建立索引的原则
      • 5.1 索引设计原则
      • 5.2 不走索引的情况

1.索引作用

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

2. 索引的种类（算法）

​ 1)BTREE:B+树索引
2)HASH：HASH索引
3)FULLTEXT：全文索引
4)RTREE：R树索引

B-tree
B+Tree 在范围查询方面提供了更好的性能(> < >= <= like)
B*Tree

3. 索引管理

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

3.1 索引分类

主键索引
普通索引
唯一索引

3.2 管理索引

#创建索引
alter table test add index index_name(name);
#创建索引
create index index_name on test(name);
#查看索引
desc table;
#查看索引
show index from table;
#删除索引
alter table test drop key index_name;
#添加唯一性索引
alter table student add unique key uni_xxx(xxx);
#查看表中数据行数
select count(*) from city;
#查看去重数据行数
select count(distinct name) from city;

3.3 唯一索引

db01 [world]>alter table city add unique index idx_uni1(name);
ERROR 1062 (23000): Duplicate entry 'San Jose' for key 'idx_uni1'

统计city表中，以省的名字为分组，统计组的个数

select district,count(id) from city group by district;
需求: 找到world下,city表中 name列有重复值的行,最后删掉重复的行
db01 [world]>select name,count(id) as cid from city group by name  having cid>1 order by cid desc;
db01 [world]>select * from city where name='suzhou';

3.4 前缀索引

根据字段的前N个字符建立索引

alter table test add index idx_name(name(10));
注意：数字列不能用作前缀索引。

避免对大列建索引

如果有，就使用前缀索引

3.5 联合索引

多个字段建立一个索引

例：
where a.女生 and b.身高 and c.体重 and d.身材好
index(a,b,c)
特点：前缀生效特性
a,ab,ac,abc,abcd 可以走索引或部分走索引
b bc bcd cd c d ba ... 不走索引

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

#创建people表
create table people (id int,name varchar(20),age tinyint,money int ,gender enum('m','f'));
#创建联合索引
alter table people add index  idx_gam(gender,age,money);

4. 执行计划分析explain

4.1 介绍

(1) 获取到的是优化器选择完成的,他认为代价最小的执行计划. 作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题. 如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。

(2) select 获取数据的方法

1. 全表扫描(应当尽量避免,因为性能低) 2. 索引扫描 3. 获取不到数据

4.2 全表扫描

1.全表扫描1）在explain语句结果中type为ALL

2）什么时候出现全表扫描?

2.1 业务确实要获取所有数据

2.2 不走索引导致的全表扫描

• ​ 2.2.1 没索引
• ​ 2.2.2 索引创建有问题
• ​ 2.2.3 语句有问题

生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描

4.3索引扫描

1 常见的索引扫描类型:
1）index
2）range
3）ref
4）eq_ref
5）const
6）system
7）null

从上到下，性能从最差到最好，我们认为至少要达到range级别

index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。

mysql> alter table city add index idx_city(population);
mysql> explain select * from city where population>30000000;

ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。

mysql> alter table city drop key idx_code;
mysql> explain select * from city where countrycode='chn';
mysql> explain select * from city where countrycode in ('CHN','USA');
mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A

join B 
on A.sid=B.sid

const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。

如将主键置于where列表中，MySQL就能将该查询转换为一个常量

mysql> explain select * from city where id=1000;

NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

mysql> explain select * from city where id=1000000000000000000000000000;

5. 建立索引的原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。

那么索引设计原则又是怎样的?

5.1 索引设计原则

5.1.1 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

优化方案:
(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
(2) 可以将此列和其他的查询类,做联和索引
select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;

注意：如果重复值较多，可以考虑采用联合索引

5.1.2 为经常需要排序、分组和联合操作的字段建立索引

例如:
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作

5.1.3 为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。

因此，为这样的字段建立索引，可以提高整个表的查询速度。

3.1 经常查询

3.2 列值的重复值少

注：如果经常作为条件的列，重复值特别多，可以建立联合索引

5.1.4 尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。

例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

5.1.5 限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5.1.6 删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

5.2 不走索引的情况

5.2.1没有查询条件，或者查询条件没有建立索引

#全表扫描
select * from table;
select  * from tab where 1=1;

在业务数据库中，特别是数据量比较大的表,是没有全表扫描这种需求。

1）对用户查看是非常痛苦的。

2）对服务器来讲毁灭性的。

3）SQL改写成以下语句：

#情况1
#全表扫描
select * from table;
#需要在price列上建立索引
selec  * from tab  order by  price  limit 10;
#情况2
#name列没有索引
select * from table where name='zhangsan'; 
1、换成有索引的列作为查询条件
2、将name列建立索引

5.2.2 查询结果集是原表中的大部分数据，应该是25％以上

mysql> explain select * from city where population>3000 order by population;

1）如果业务允许，可以使用limit控制。

2）结合业务判断，有没有更好的方式。如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面。

5.2.3 索引本身失效，统计数据不真实

索引有自我维护的能力。

对于表内容变化比较频繁的情况下，有可能会出现索引失效。
重建索引就可以解决

5.2.4 查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)

#例子
错误的例子：select * from test where id-1=9; 
正确的例子：select * from test where id=10;

5.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误

mysql> create table test (id int ,name varchar(20),telnum varchar(10));
mysql> insert into test values(1,'zs','110'),(2,'l4',120),(3,'w5',119),(4,'z4',112);
mysql> explain select * from test where telnum=120;
mysql> alter table test add index idx_tel(telnum);
mysql> explain select * from test where telnum=120;
mysql> explain select * from test where telnum=120;
mysql> explain select * from test where telnum='120';

5.2.6 <> ，not in 不走索引

mysql> select * from tab where telnum <> '1555555';
mysql> explain select * from tab where telnum <> '1555555';

单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit

or或in尽量改成union

EXPLAIN  SELECT * FROM teltab WHERE telnum IN ('110','119');
#改写成
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

5.2.7 like "%_" 百分号在最前面不走

#走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%';
#不走索引
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110';

%linux%类的搜索需求，可以使用Elasticsearch -------> ELK

5.2.8单独引用联合索引里非第一位置的索引列

CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);
ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
DESC t1
SHOW INDEX FROM t1
#走索引的情况测试
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  AND sex='m';
#部分走索引
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30  AND sex='m'; 
#不走索引
EXPLAIN SELECT  NAME,age,sex,money FROM t1 WHERE age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';