mysql(六)

字符集校验规则

1）ci：大小写不敏感
2）cs或bin：大小写敏感
工具修改字符集

系统修改字符集

#CentOS6: 
[root@db01 ~]# vim /etc/sysconfig/i18n

image.png

#CentOS7: 
[root@db01 ~]# vim /etc/locale.conf 
LANG="en_US.UTF-8"

image.png
数据库修改字符集

#库级别的字符集
mysql> create database oldboy charset utf8;
#表级别的字符集
mysql> create table mysql.oldboy(id int)charset utf8;


CREATE TABLE `student5` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
  `name` varchar(20) NOT NULL COMMENT '学生姓名',
  `age` tinyint(3) unsigned NOT NULL COMMENT '学生年龄',
  `gender` enum('f','m') DEFAULT NULL COMMENT '学生性别',
  `cometime` datetime DEFAULT CURRENT_TIMESTAMP,
  `state` enum('0','1') DEFAULT '1',
  PRIMARY KEY (`id`));
  
  #修改表的字符集
  mysql> alter table city charset utf8;

索引的类型（算法）

• BTREE:B树索引（Btree，B+tree，B*tree）
• HASH：HASH索引
• FULLTEXT：全文索引

• RTREE：R树索引

  
  image.png

image.png

1.在叶子节点，添加了相邻节点的指针

2.优化了，范围查询

索引分类

• 主键索引（聚集索引）
• 普通索引
  • 联合索引
  • 前缀索引
• 唯一索引（唯一键）

主键：唯一 、 非空 primary key

唯一键：唯一 、 可以为空 unique key

primary key = unique key + not null

索引创建规则

1.一个字段可以创建一个索引

2.多个字段可以创建一个索引

3.多个字段可以创建多个索引，但是不能是主键

索引的操作

#创建主键索引 primary key
mysql> alter table student2 add primary key pri(name);
#创建普通索引 index
mysql> alter table student2 add index idx_name(name);
#创建唯一索引 unique key
mysql> alter table student2 add unique key uni_age(age);

#如何判断，某列是否可以创建唯一索引
distinct()
count()

mysql> select count(name) from student4;
+-------------+
| count(name) |
+-------------+
|           9 |
+-------------+
1 row in set (0.00 sec)

mysql> select count(distinct(name)) from student4;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
|                     2 |
+-----------------------+
mysql> alter table country add unique key uni_name(name);

#查看索引
mysql> show index from student2;
mysql> desc student2;
mysql> show create table student2;

#删除索引
mysql> alter table student2 drop index idx_all;
mysql> alter table student2 drop index uni_age;

前缀索引

给表中数据量大的列，创建前缀索引

#创建前缀索引
mysql> alter table student2 add index idx_name(name(3));
只对数据的前3个字符进行排序，因为创建索引的过程中系统会对所有的数据进行读取排序。

1.避免对大列建索引
2.如果有，就使用前缀索引

联合索引

mysql> create table xiangqin(id int,name varchar(10),gender enum('0','1'),face varchar(10),height int,weight int,salary int,hobby varchar(10),QQ varchar(11));

mysql> alter table xiangqin add index idx_all(gender,salary,face,weight);

联合索引，走索引情况

A:gender B:salary C:face D:weight

全部走索引：

select * from xiangqin where A;

select * from xiangqin where A B;

select * from xiangqin where A B C;

select * from xiangqin where A B C D;

部分走索引：

select * from xiangqin where A C D;

select * from xiangqin where A B D;

select * from xiangqin where A D;

全不走索引

select * from xiangqin where B C D;

select * from xiangqin where C D;

select * from xiangqin where D;

explain 分析执行效率(优化SQL语句)

1.全表扫描

select * from tb1;

mysql> explain select * from world.city;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4188 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

2.索引扫描

• index：全索引扫描

mysql> explain select CountryCode from world.city;

• range:范围查询

 mysql> explain select * from world.city where population > 30000000;

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

mysql> explain select * from world.city where countrycode='USA' union all select * from world.city where countrycode='CHN';

• eq_ref:连表查询(内连接)，并且等价条件是主键或者唯一键

join B 
on A.sid=B.sid

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

mysql> explain select * from world.city where id=1;

• null：查询的值，不在范围内（根本不工作）

mysql> explain select * from world.city where population > 3000000000000000000000000000000000000000000000;

Extra（扩展）
Using temporary
Using filesort 使用了默认的文件排序（如果使用了索引，会避免这类排序）

mysql> explain select * from city where countrycode='CHN' order by population;

#解决方案
mysql> explain select * from city where population>30000000 order by population;

注意：

row：越小越好

key_len:越小越好

使用前缀索引，控制key_len

索引建立的规范（原则）

1.唯一索引

select count(*) from world.city;
select count(distinct(countrycode)) from world.city;
select count(distinct(countrycode,population)) from world.city;

2.联合索引

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

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

• 唯一索引
• 联合索引
• 普通索引

5.尽量使用前缀索引

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

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

走索引和不走索引的情况

1.全表扫描（不执行）

mysql> explain select * from world.city；

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

mysql> explain select * from world.city where population > 3000000;


mysql> explain select * from world.city where population > 300 limit 10;

3.索引本身失效，或者损坏

删除索引，重建索引

4.使用列名，进行条件运算

mysql> explain select * from world.city where id-1=9;

5.隐式转换，不走索引

mysql> create table suibian(id int,QQ varchar(11));
mysql> insert into suibian values(1,'100'),(2,'110'),(3,120);
mysql> alter table suibian add index idx_qq(qq);

mysql> explain select * from suibian where qq='120';
+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys | key    | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | suibian | ref  | idx_qq        | idx_qq | 36      | const |    1 | Using index condition |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------+

mysql> explain select * from suibian where qq=120;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | suibian | ALL  | idx_qq        | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

6.模糊查询like %在最前面的时候，不管结果集是多少

mysql> explain select * from world.city where countrycode like '%HN';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

建议，不使用MySQL，而是使用elasticsearch

7.<> 、 not in不走索引

mysql> explain select * from world.city where population <> 102361;

union all
limit

8.联合索引，单独引用联合索引里非第一位置的索引列

按照创建索引的顺序，查询数据