内置函数、流程控制、索引、慢查询优化
目录
一、内置函数
1.移除指定字符
Trim、LTrim、RTrim
例子：
mysql> select trim(leading 'x' from 'xxxbarxxx');
+------------------------------------+
| trim(leading 'x' from 'xxxbarxxx') |
+------------------------------------+
| barxxx |
+------------------------------------+
1 row in set (0.00 sec)
2.大小写转换
Lower、Upper
例子：
mysql> select lower('JJDWHI');
+-----------------+
| lower('JJDWHI') |
+-----------------+
| jjdwhi |
+-----------------+
1 row in set (0.00 sec)
mysql> set @str=binary 'JJDWhjjbjaHI';
Query OK, 0 rows affected (0.00 sec)
mysql> select lower(@str),lower(convert(@str using latin1));
+--------------+-----------------------------------+
| lower(@str) | lower(convert(@str using latin1)) |
+--------------+-----------------------------------+
| JJDWhjjbjaHI | jjdwhjjbjahi |
+--------------+-----------------------------------+
1 row in set (0.00 sec)
3.获取左右起始指定个数字符
Left、Right
4.返回读音相似值(对英文效果)
Soundex
"""
eg:客户表中有一个顾客登记的用户名为J.Lee
但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
where Soundex(name)=Soundex('J.Lie')
"""
mysql> use db3;
Database changed
mysql> create table t1(
-> id int,
-> name varchar(32));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1(id,name) values(1,'jason'),(1,'json');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | jason |
| 1 | json |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from t1 where soundex(name) = soundex('jason');
+------+-------+
| id | name |
+------+-------+
| 1 | jason |
| 1 | json |
+------+-------+
2 rows in set (0.00 sec)
5.日期格式:date_format
'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
('第1篇','2015-03-01 11:31:21'),
('第2篇','2015-03-11 16:31:21'),
('第3篇','2016-07-01 10:21:31'),
('第4篇','2016-07-22 09:23:21'),
('第5篇','2016-07-23 10:11:11'),
('第6篇','2016-07-25 11:21:31'),
('第7篇','2017-03-01 15:33:21'),
('第8篇','2017-03-01 17:32:21'),
('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数
adddate 增加一个日期
addtime 增加一个时间
datedif 计算两个日期差值
二、流程控制
# if条件语句
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END //
delimiter ;
# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
END //
delimiter ;
三、索引
1.什么是索引？
简单的理解为可以帮助你加快数据查询速度的工具
也可以把索引比喻成书的目录，它能让你更快的找到自己想要的内容
索引就是一种数据结构，类似于书的目录，意味着以后再查找数据应该先找到目录再找数据，而不是用翻页的方式查询数据
索引在MySQL中也叫做'键'，是存储引擎用于快速找到记录的一种数据结构
primary key、unique key、index key
注意：
foreign key不是用来加速查询的，不在我们研究范围之内，上面三种key前俩种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会绑你加速查询
本质：
都是通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查询方式来锁定数据
那么它其实是一把双刃剑，有利也有弊，索引的存在可以加快数据的查询 但是会减慢数据的增删，如果在设置表的时候遇到一个需要加索引的就加索引，那么就会导致类似于要挨个建立索引，建立好多个目录，查找起来更麻烦
2.跟索引相关的关键字
1.聚集索引
聚集索引其实就是表的主键，innodb引擎规定一张表中不需要有主键
MyISAM引擎在建表的时候对应的硬盘有三个文件
innodb引擎在建表的时候对应的硬盘有俩个文件，frm文件只存放表结构，不能存放索引，也就意味着innoDB引擎的索引跟数据都放在idb表数据文件中
特点：叶子结点放的一条条完整的记录
2.辅助索引
查询数据的时候不可能都是用id作为筛选条件，也可能会用name,passsord等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果，就需要给其他字段建立索引，这些索引就叫做辅助索引
特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如：按照name字段创建索引，那么叶子节点存放的是：{name对应的值，name所在的那条记录的主键值})
3.覆盖与非覆盖索引
select name from user where name='jason';
上述语句，只在辅助索引的叶子结点中就已经找到了所有我们想要的数据
select age from user where name='jason';
上述语句，虽然查询的时候命中了索引字段name，但是要查的是age字段,所以还需要利用主键才去查找
四、索引的底层原理(树)
innoDB存储引擎默认的索引结构为B+树，而B+树是由二叉树、平衡二叉树、B树再到B+树一路演变过来的
1.二叉树(每个节点只能分俩个叉)
2.数据结构(B树，也叫B-树)
B树:
除了叶子节点可以有多个分支 其他节点最多只能两个分支
所有的节点都可以直接存放完整数据(每一个数据块是有固定大小的)
B+树:
只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)
B*树
在树节点添加了通往其他节点的通道 减少查询次数
为什么我们要以id作为主键？
因为id字段是整形，整形是数字，数字在存储的时候容量更小，那么就可以在一个字段里存放更多的id值，就可以减少树的层级
结论：
一个磁盘量它的存储容量是有限的，我们尽可能让磁盘里存储更多的信息，这样的话可以降低树的层级，相应的就会加快对数据查找的时间
b*树(在树节点添加了通往其他节点的通道 减少查询次数)
指针的作用:
添加指针是为了加快范围查询的速度
3.总结(索引)
索引的作用:
索引就是为了提供数据的查询速度
在计算机底层的表现形式就是一些数据结构(树)
数据结构:
二叉树 : 每个节点只能分两个叉
b树 : 枝节点和叶节点没有指针
b+树 : 叶节点添加指针
b*树 : 枝节点添加了指针(叶节点也有)
指针添加的作用:
指针的添加主要是为了解决范围查询的问题
精确查找取决于树的高度
索引的必要性:
将某个字段添加成索引就相当于依据该字段建立了一颗b+树从而加快查询速度
如果某个字段没有添加索引 那么依据该字段查询数据会非常的慢(一行行查找)
五、索引管理
索引建立在表的列上（字段）的，在where后面的列建立索引才会加快查询速度
pages<---索引（属性）<---查数据
查看索引:
show index from t1;
desc table;
创建唯一索引需要提前排查是否有重复数据：
select count(字段) from t1;
select count(distinct(字段)) from t1;
删除索引：
alter table 表名 drop index 索引名;
查看表中数据行数：
select 凑他（*） from city;
查看去重数据行数：
select count(distinct name) from city;
索引分类
- 主键索引(指定索引)
主键索引除了有加速查询的效果之外 还具有一定的约束条件
alter table t1 add primary key pri_id(id); # 以id字段为索引
pri_id : 索引名<见名之意>
- 唯一索引
唯一键索引 除了有加速查询的效果之外 还具有一定的约束条件，使用唯一索引时，指定字段是唯一索引时，该字段如果有重复，使用唯一索引会报错。
alter table t1 add unique key uni_pwd(pwd)
- 普通索引
普通索引 只有加速查询的效果 没有额外约束
alter table t1 add index idx_name(name)
- 联合索引(属于普通索引)
联合索引作用:
相亲平台 搜索心仪对象的时候 《女，富婆，未婚，漂亮，1.67》
遵循:最左匹配原则
例:
where a.女生 and b.身高 and c.体重 and d.身材好
index(a.b.c)
特点: 前缀生效特性
a,ab,ac,abc,abcd 可以走索引或部分走索引
b bc bcd c d ba... 不走索引
创建联合索引，前缀生效特性
alter table t1 add index idx_all(id,name,pwd)
- 前缀索引(属于普通索引)
前缀索引的作用:
避免对大列建索引(数据很多情况),如果有就使用前缀索引
比如:
博客内容 百度搜索内容等
根据字段前N个字符建立索引
alter table t1 add index idx_name(name(10))
六、慢查询优化(explain详解)
1.explain命令使用方法
explain select 查询内容 from 表名 where 条件;
2.查询数据的方式
2.1全表扫描
'在explain语句结果中type为ALL，mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描'
**什么时候会出现全表扫描？**
1. 业务确实要获取所有数据
2. 不走索引导致的全盘扫描
3. 没索引
4. 索引创建有问题
5. 语句有问题
**在业务数据库中，特别是数据量比较大的表，是没有全表扫描这种需求。**
1. 对用户查看时非常痛苦的
2. 对服务器来讲毁灭性的
2.2索引扫描(从上到下，性能从最差到最好，我们认为至少要达到range级别)**
| 常见的索引扫描类型 | 介绍使用 |
|---|---|
| index | Full Index Scan，index与ALL区别为index类型只遍历索引树。 |
| range | 索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。 例如：alter table city add index idx_city(population); explain select * from city where population>30000000; |
| ref | 使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。 例如：alter table city drop key idx_code; explain select * from city where countrycode='chn'; explain select * from city where countrycode in ('CHN','USA'); explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA'; |
| eq_ref | 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A |
| const、system | 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问，如将主键置于where列表中，MySQL就能将该查询转换为一个常量。 例如：explain select * from city where id=1000; |
| null | MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。 |
3.不走索引的情况(记忆4条以上）
1.没有查询条件，或者查询条件没有建立索引
全表扫描：select * from table;
select * from 表名 where 条件
2.查询结果集是原表中的大部分数据(25％以上)有可能不走索引
explain select * from city where population>3000 order by population;
如果业务允许，可以使用limit控制
结合业务判断，有没有更好的方式，如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面。
3.索引本身失效，统计数据不真实
索引有自我维护的能力
对于表内容变化比较频繁的情况下，有可能会出现索引失效
重建索引就可以解决
4.查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)
错误的例子: select * from test where id-1=9;
正确的例子: select * from test where id=10;
5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误
测试隐式转换导致失效(类型转错成int类型)，见图5-1
纠正隐式转换导致的失败(传入正确的 字符串类型)，见图5-2
6.<> ，not in 不走索引
单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit、or或in尽量改成union
7.like "%_" 百分号在最前面不走
走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%';
不走索引
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110';
8.单独引用联合索引里非第一位置的索引列(最多匹配原则，第一个不满足，剩下的就不满足了)
mysql> ALTER TABLE t2 ADD INDEX t1_idx(money,age,sex);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| NAME | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | enum('m','f') | YES | | NULL | |
| money | int(11) | YES | MUL | NULL | |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.03 sec)
mysql> SHOW INDEX FROM t2;
#走索引的情况测试
EXPLAIN SELECT NAME,age,sex,money FROM t2 WHERE money=30 AND age=30 AND sex='m';
#部分走索引
EXPLAIN SELECT NAME,age,sex,money FROM t2 WHERE money=30 AND age=30;
EXPLAIN SELECT NAME,age,sex,money FROM t2 WHERE money=30 AND sex='m';
#不走索引
EXPLAIN SELECT NAME,age,sex,money FROM t2 WHERE age=20;
EXPLAIN SELECT NAME,age,sex,money FROM t2 WHERE age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t2 WHERE sex='m';
索引的创建会加快数据的查询速度 但是一定程度会拖慢数据的插入和删除速度。
