周日，晴，记录生活分享点滴
参考博客1：https://www.cnblogs.com/wupeiqi/articles/5713323.html
参考博客2：https://www.cnblogs.com/wupeiqi/articles/5716963.html
部分内置函数
CHAR_LENGTH(str) /* 获取字符的长度 */ 返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。 对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。 CONCAT(str1,str2,...) /* 字符串拼接 */ 如有任何一个参数为NULL ，则返回值为 NULL。 CONCAT_WS(separator,str1,str2,...) /* 字符串拼接（自定义连接符）separator指分隔符，在此处可自定义连接符 */ CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。 CONV(N,from_base,to_base) /* 进制转换 */ 例如： SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示 FORMAT(X,D) /* 格式转换 */ 将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。 例如： SELECT FORMAT(12332.1,4); 结果为： '12,332.1000' INSERT(str,pos,len,newstr) 在str的指定位置插入字符串 pos：要替换位置起始位置 len：替换的长度 newstr：新字符串 特别的： 如果pos超过原字符串长度，则返回原字符串 如果len超过原字符串长度，则由新字符串完全替换 例如： select insert('alex',2,2,'GDBX'); 指从第二个字母'l'起的两个字母'le'替换为'GDBX'，虽然字符超多原字符长度，但输出结果为'aGDBXx' INSTR(str,substr) 返回字符串 str 中子字符串的第一个出现位置。 LEFT(str,len) /* 获取前几个字符 */ 返回字符串str 从开始的len位置的子序列字符。 LOWER(str) /* 变小写 */ UPPER(str) /* 变大写 */ LTRIM(str) /* 移除左边的空格，同python的strip */ 返回字符串 str ，其引导空格字符被删除。 RTRIM(str) /* 移除右边的空格 */ 返回字符串 str ，结尾空格字符被删去。 SUBSTRING(str,pos,len) 获取字符串子序列 LOCATE(substr,str,pos) 获取子序列索引位置 REPEAT(str,count) 返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。 若 count <= 0,则返回一个空字符串。 若str 或 count 为 NULL，则返回 NULL 。 REPLACE(str,from_str,to_str) 返回字符串str 以及所有被字符串to_str替代的字符串from_str 。 REVERSE(str) 返回字符串 str ，顺序和字符顺序相反。 RIGHT(str,len) 从字符串str 开始，返回从后边开始len个字符组成的子序列 SPACE(N) 返回一个由N空格组成的字符串。 SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) 不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。 mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki' TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str) 返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。 mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx'
delimiter \ create function f1( i1 int, i2 int) returns int BEGIN /* 不能获取结果集 select * from tb1 */ declare num int; set num = i1 + i2; return(num); END \ delimiter ; /* 存储过程： sql语句 inout,out构造返回值 如何调用--> call：存储过程名称 函数： sql不允许 允许(通过sql语句通过sql查询一个值给某个变量进行赋值) declare a int; select nid into a from student where name = 'alex' -- nid:1 name:alex -- a = 1 return返回 如何调用--> select 函数名(参数) */
drop function func_name;
/* 获取返回值 */ declare @i VARCHAR(32); select UPPER('alex') into @i; SELECT @i; /* 在查询中使用 */ select f1(11,nid) ,name from tb2;
delimiter \ create PROCEDURE p1( OUT p_return_code tinyint ) /* 如果存储过程出现异常，执行代码块一 */ BEGIN DECLARE exit handler for sqlexception /* 声明一个特殊的变量sqlexceptiono，表示执行了sql的异常处理 */ BEGIN -- ERROR set p_return_code = 1; rollback; /* 回滚，set p_return_code = 0;不再执行 */ END; /* 如果存储过程出现警告，执行代码块二 */ DECLARE exit handler for sqlwarning /* 警告处理 */ BEGIN -- WARNING set p_return_code = 2; rollback; END; /* 如果执行成功，上面的两个代码块不用执行 */ START TRANSACTION; /* 开始一个事务 */ DELETE from tb1; insert into tb2(name)values('seven'); COMMIT; /* START到COMMIT过程中的操作为一个事务操作，如果中间有一个出错，就回滚到原来的状态 */ -- SUCCESS set p_return_code = 0; END\ delimiter ;
索引·重要
普通索引
普通索引一个功能：加速查找
1.创建表 + 索引
create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) /* 将name这一列创建了一个索引 */ )
create index 索引名称 on 表(列名)
drop index_name on table_name;
show index from table_name;
唯一索引
唯一索引两个功能：加速查找 和 约束列数据不能重复，可为空null
是普通索引的一个特殊值
create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, unique ix_name (name) /* 是普通索引的一个特殊值unique */ )
create unique index 索引名 on 表名(列名)
drop unique index 索引名 on 表名
主键索引
主键索引两个功能：加速查找 和 约束列数据不能重复，不能为空null
最强的索引，功能最多
create table in1( nid int not null auto_increment primary key, /* primary key 在表创建时自动创建索引文件-主键索引 */ name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) ) OR create table in1( nid int not null auto_increment, /* 创建主键索引 */ name varchar(32) not null, email varchar(64) not null, extra text, primary key(nid), index ix_name (name) /* 创建name普通索引 */ )
alter table 表名 add primary key(列名);
alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key;
组合索引
组合索引一个功能：将n个列组合成一个索引（多列可以创建一个索引文件）
其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = 'alex' and n2 = 666。
-
普通组合索引：无约束
-
create table in3( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text )
create index ix_name_email on in3(name,email);
select * from tb1 where name = 'alex' /* 使用索引 */ select * from tb1 where name = 'alex' and pwd = '123' /* 使用索引 */ select * from tb1 where pwd = '123' /* 不使用索引 */
如上创建组合索引之后，查询：
-
name and email -- 使用索引
-
name -- 使用索引
-
email -- 不使用索引
覆盖索引
select的数据列只需要在索引表中就能获取到数据时，并且不用去数据表中操作，叫覆盖索引
/* 普通索引 ---------------------------------- */ select * from tb where nid=1 /* 先去索引中找， 在去数据表找 */ /* 覆盖索引 ---------------------------------- */ select nid from tb where nid < 10 /* 先去索引中找 */
索引合并，使用多个单列索引组合搜索
nid name(单独索引) email(单独索引) pwd /* 合并索引：两个索引文件 */ select * from tb where name='alex' select * from tb where email='alex3714@163.com' select * from tb where name='alex' or email='zhangsan@123.com'
nid name(组) email(合) pwd /* 组合索引：一个索引文件 */ /* 最左前缀 */ select * from tb where name='alex' select * from tb where email='alex3714@163.com' /* 根据最左前缀，无法使用索引 */ select * from tb where name='alex' or email='zhangsan@123.com'
组合索引和合并索引需要根据业务需求来决定
执行计划
相对比较准确表达出当前SQL运行状况
mysql> explain select * from tb2; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | tb2 | ALL | NULL | NULL | NULL | NULL | 2 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)
1、explain SQL语句 type：ALL -- 全数据表扫描 type：index -- 全索引表扫描 type：range -- 范围索引扫描，效率高于ALL、index，特殊情况见详细 /* --------- SQL： ALL、Index 效率不高，都可优化 --------- */ 2、limit select * from tb1 where email='123' -- 全表扫描，没有limit 1 效率高 select * from tb1 where email='123' limit 1; -- 随机抽取一条进行扫描
类型
type 查询时的访问方式， /* 性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const */ ALL 全表扫描，对于数据表从头到尾找一遍 select * from tb1; 特别的：如果有limit限制，则找到之后就不在继续向下扫描 select * from tb1 where email = 'seven@live.com' select * from tb1 where email = 'seven@live.com' limit 1; 虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。 INDEX 全索引扫描，对索引从头到尾找一遍 /* 全索引扫描 */ select nid from tb1; RANGE 对索引列进行范围查找 /* 范围索引扫描 */ select * from tb1 where name < 'alex'; PS: between and in > >= < <= 操作 注意：!= 和 > 符号 /* != 和 > 即使在索引列进行范围查找，也无法走索引 */ INDEX_MERGE 合并索引，使用多个单列索引搜索 /* 合并索引 */ select * from tb1 where name = 'alex' or nid in (11,22,33); REF 根据索引查找一个或多个值 /* 普通索引 */ select * from tb1 where name = 'seven'; EQ_REF 连接时使用primary key 或 unique类型 /* 唯一索引 */ select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid; CONST 常量 /* 查找时效率最高 */ 表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。 select nid from tb1 where nid = 2 ; SYSTEM 系统 /* 查找时效率最高 */ 表仅有一行(=系统表)。这是const联接类型的一个特例。 select * from (select nid from tb1 where nid = 1) as A;
id 查询顺序标识 如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B; +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | NULL | | 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ 特别的：如果使用union连接气值可能为null select_type 查询类型 SIMPLE 简单查询 PRIMARY 最外层查询 SUBQUERY 映射为子查询 DERIVED 子查询 UNION 联合 UNION RESULT 使用联合的结果 ... table 正在访问的表名 /* type 查询时的访问方式， 性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const ALL 全表扫描，对于数据表从头到尾找一遍 select * from tb1; 特别的：如果有limit限制，则找到之后就不在继续向下扫描 select * from tb1 where email = 'seven@live.com' select * from tb1 where email = 'seven@live.com' limit 1; 虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。 INDEX 全索引扫描，对索引从头到尾找一遍 select nid from tb1; RANGE 对索引列进行范围查找 select * from tb1 where name < 'alex'; PS: between and in > >= < <= 操作 注意：!= 和 > 符号 INDEX_MERGE 合并索引，使用多个单列索引搜索 select * from tb1 where name = 'alex' or nid in (11,22,33); REF 根据索引查找一个或多个值 select * from tb1 where name = 'seven'; EQ_REF 连接时使用primary key 或 unique类型 select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid; CONST 常量 表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。 select nid from tb1 where nid = 2 ; SYSTEM 系统 表仅有一行(=系统表)。这是const联接类型的一个特例。 select * from (select nid from tb1 where nid = 1) as A; */ possible_keys 可能使用的索引 key 真实使用的 key_len MySQL中使用索引字节长度 rows mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值 extra 该列包含MySQL解决查询的详细信息 “Using index” 此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。 “Using where” 这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。 “Using temporary” 这意味着mysql在对查询结果排序时会使用一个临时表。 “Using filesort” 这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。 “Range checked for each record(index map: N)” 这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。
如何命中索引
需要正确建立索引，否则索引不会生效：
- like '%xx' /* %前有实体值走索引，没有实体值不走索引 */ select * from tb1 where name like '%cn'; - 使用函数 /* 不要在查询条件上加函数，即使是索引，也无法使用，效率变低 */ select * from tb1 where reverse(name) = 'wupeiqi'; - or select * from tb1 where nid = 1 or email = 'seven@live.com'; 特别的：当or条件中有未建立索引的列才失效，以下会走索引 /* 索引合并 */ select * from tb1 where nid = 1 or name = 'seven'; select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex' - 类型不一致 如果列是字符串类型，传入条件是必须用引号引起来 select * from tb1 where name = 999; /* 999与name类型不一致不走索引 */ /* 除了 != 、 > 其他的都走索引 */ - != select * from tb1 where name != 'alex' /* 正常不走索引，除了是主键 */ 特别的：如果是主键，则还是会走索引 select * from tb1 where nid != 123 /* 索引是数字类型，!= ，走索引 */ - > select * from tb1 where name > 'alex' /* 正常不走索引，除了是主键和数字 */ 特别的：如果是主键或索引是整数类型，则还是会走索引 select * from tb1 where nid > 123 select * from tb1 where num > 123 - order by select email from tb1 order by name desc; 当根据索引排序时候，选择的映射如果不是索引，则不走索引 特别的：如果对主键排序，则还是走索引： select * from tb1 order by nid desc; - 组合索引最左前缀 如果组合索引为：(name,email) name and email -- 使用索引 name -- 使用索引 email -- 不使用索引
其他注意事项
-
避免使用select *
-
count(1)或count(列) 代替 count(*)
-
创建表时尽量使 char 代替 varchar
-
表的字段顺序固定长度的字段优先，变长在后
-
组合索引代替多个单列索引（经常使用多个条件查询时） 两个在一起时，组合索引速度稍快于合并索引
-
尽量使用短索引（指定列前几个字符创建索引；text, blob 必须要执行长度）
-
使用连接（JOIN）来代替子查询（Sub-Queries）
-
连表时注意条件类型需一致
-
索引散列值（重复少）不适合建索引，例：性别不适合，就男女两个值，每个值里面还有其他很多值
分页
limit x, m ：表x+m （越向后查询时间越慢）
where nid > 10000 直接跳过前10000条数，继续往下扫
def sqlexec(last_nid, is_next): import pymysql conn = pymysql.connect(host='192.168.12.29', port=3306, user='root', passwd='123', db='IndexDB', charset='utf8') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 执行存储过程,获取存储过程的结果集，将返回值设置给了 @_存储过程名_序号 = if is_next: cursor.execute('select * from tb1 where nid>%s limit 10',last_nid) result = cursor.fetchall() else: cursor.execute('select * from tb1 where nid<%s order by nid desc limit 10', last_nid) result = cursor.fetchall() result = list(reversed(result)) conn.commit() cursor.close() conn.close() return result current_last_nid = 0 current__nid = 0 while True: p = input('1、上一页; 2、下一页 ') if p == '2': # 点击下一页 is_next = True ret = sqlexec(current_last_nid, is_next) else: is_next = False ret = sqlexec(current_first_nid, is_next) current_first_nid = ret[0]['nid'] current_last_nid = ret[-1]['nid'] for i in ret: print(i)
慢日志查询
配置MySQL自动记录慢日志
方式一：在内存中直接修改 方式二：写一个配置文件 C:MySQLmysql-5.6.48-winx64my.ini mysqld --default-files = C:MySQLmysql-5.6.48-winx64my.ini /* (1) 重新设置完成后需要进行重启程序生效 */ 指定： slow_query_log = ON (默认值OFF，在内存里面) /* 开启慢日志记录 */ log_queries_not_using_indexes = ON (默认值OFF，在内存里面) /* 为使用索引的搜索记录 */ long_query_time = 2 /*时间限制，超过2s，则记录 */ slow_query_log_file = C:MySQLslow.log /* 日志文件储存 */ /* (2) 在内存中直接修改，仅限这次使用 */ slow_query_log = OFF log_queries_not_using_indexes = OFF long_query_time = 2 slow_query_log_file = C:MySQLslow.log
注意：
-
-
修改当前配置：set global 变量名 = 值
mysqldumpslow -s at -a C:MySQLslow.log /* verbose 版本 debug 调试 help 帮助 */ -v 版本 -d 调试模式 -s ORDER 排序方式 what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time -r 反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first) -t NUM 显示前N条just show the top n queries -a 不要将SQL中数字转换成N，字符串转换成S。do not abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN 正则匹配；grep: only consider stmts that include this string -h HOSTNAME mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l 总时间中不减去锁定时间；do not subtract lock time from total time