事务函数索引

事务:innodb支持事务，事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

MySQL内置函数:
十六进制:0-9对应0-9；A-F对应10-15；

CHAR_LENGTH(str)
        返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
        对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。
        
mysql> select char_length('mudy');
+---------------------+
| char_length('mudy') |
+---------------------+
|                   4 |
+---------------------+
1 row in set (0.22 sec)

CONCAT(str1,str2,...)
        字符串拼接
        如有任何一个参数为NULL ，则返回值为 NULL。
        
mysql> select concat('mudy','huyue');
+------------------------+
| concat('mudy','huyue') |
+------------------------+
| mudyhuyue              |
+------------------------+
1 row in set (0.04 sec)

CONCAT_WS(separator,str1,str2,...)
        字符串拼接（自定义连接符）
        CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

mysql> select concat_ws('-','mudy','huyue');
+-------------------------------+
| concat_ws('-','mudy','huyue') |
+-------------------------------+
| mudy-huyue                    |
+-------------------------------+
1 row in set (0.00 sec)

CONV(N,from_base,to_base)
        进制转换

将16进制的10转换成2进制的
mysql> select conv('10',16,2);
+-----------------+
| conv('10',16,2) |
+-----------------+
| 10000           |
+-----------------+
1 row in set (0.00 sec)


mysql> select conv('9',16,2);
+----------------+
| conv('9',16,2) |
+----------------+
| 1001           |
+----------------+
1 row in set (0.00 sec)


mysql> select conv('a',16,2);
+----------------+
| conv('a',16,2) |
+----------------+
| 1010           |
+----------------+
1 row in set (0.00 sec)

将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 
并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
mysql> select format(10000000000,3);
+-----------------------+
| format(10000000000,3) |
+-----------------------+
| 10,000,000,000.000    |
+-----------------------+
1 row in set (0.09 sec)

INSERT(str,pos,len,newstr)
        在str的指定位置插入字符串
            pos：要替换位置其实位置
            len：替换的长度
            newstr：新字符串
        特别的：
            如果pos超过原字符串长度，则返回原字符串
            如果len超过原字符串长度，则由新字符串完全替换
            
            
mysql> select insert('mudy',5,1,'good');
+---------------------------+
| insert('mudy',5,1,'good') |
+---------------------------+
| mudy                      |
+---------------------------+
1 row in set (0.00 sec)

mysql> select insert('mudy',1,2,'good');
+---------------------------+
| insert('mudy',1,2,'good') |
+---------------------------+
| gooddy                    |
+---------------------------+
1 row in set (0.07 sec)

mysql> select insert('mudy',1,5,'good');
+---------------------------+
| insert('mudy',1,5,'good') |
+---------------------------+
| good                      |
+---------------------------+
1 row in set (0.00 sec)

INSTR(str,substr)
        返回字符串 str 中子字符串的第一个出现位置。

mysql> select instr('mudy','y');
+-------------------+
| instr('mudy','y') |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.05 sec)

LEFT(str,len)
        返回字符串str 从开始的len位置的子序列字符。
        
mysql> select left('mudy',2);
+----------------+
| left('mudy',2) |
+----------------+
| mu             |
+----------------+
1 row in set (0.07 sec)

LOWER(str)
        变小写

UPPER(str)
        变大写

LOCATE(substr,str,pos)
        获取子序列索引位置
        
第三个参数表示起始位置
mysql> select locate('dy','mudymudy',1);
+---------------------------+
| locate('dy','mudymudy',1) |
+---------------------------+
|                         3 |
+---------------------------+
1 row in set (0.04 sec)

mysql> select locate('dy','mudymudy',4);
+---------------------------+
| locate('dy','mudymudy',4) |
+---------------------------+
|                         7 |
+---------------------------+
1 row in set (0.00 sec)


REPEAT(str,count)
        返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
        若 count <= 0,则返回一个空字符串。
        若str 或 count 为 NULL，则返回 NULL 。
        
mysql> select repeat('mudy',2);
+------------------+
| repeat('mudy',2) |
+------------------+
| mudymudy         |
+------------------+
1 row in set (0.04 sec)

SPACE(N)
        返回一个由N空格组成的字符串。

mysql> select space(4)
    -> ;
+----------+
| space(4) |
+----------+
|          |
+----------+
1 row in set (0.04 sec)


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('imaumudy' from 4 for 3);
+------------------------------------+
| substring('imaumudy' from 4 for 3) |
+------------------------------------+
| umu                                |
+------------------------------------+
1 row in set (0.06 sec)

mysql> select substring('imaumudy',2,3);
+---------------------------+
| substring('imaumudy',2,3) |
+---------------------------+
| mau                       |
+---------------------------+
1 row in set (0.00 sec)

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
        返回字符串 str ， 其中所有remstr 
        前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给
        定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。


mysql> select trim('    mudy     ');
+-----------------------+
| trim('    mudy     ') |
+-----------------------+
| mudy                  |
+-----------------------+
1 row in set (0.00 sec)


mysql> select trim(leading 's' from 'ssssssmudysssssss');
+--------------------------------------------+
| trim(leading 's' from 'ssssssmudysssssss') |
+--------------------------------------------+
| mudysssssss                                |
+--------------------------------------------+
1 row in set (0.00 sec)


mysql> select trim(trailing 's' from 'ssssssmudysssssss');
+---------------------------------------------+
| trim(trailing 's' from 'ssssssmudysssssss') |
+---------------------------------------------+
| ssssssmudy                                  |
+---------------------------------------------+
1 row in set (0.00 sec)

自定义函数:
注意在函数中不可以写sql语句，可以有返回值，但是没有in out inout
支持==select nid into a from student where name = 'mudy';==

delimiter \\
CREATE FUNCTION f1(i1 int,i2 int)
RETURNS int

BEGIN
    DECLARE num int;
    set num = i1 + i2;
    RETURN(num);
END\\

delimiter ;


mysql> select f1(3,4);
+---------+
| f1(3,4) |
+---------+
|       7 |
+---------+
1 row in set (0.07 sec)

索引的功能:
- 约束
  - 主键
  - 外键
  - 唯一
  - 普通
  - 组合
- 加速查找

索引的生成：算法是B-tree

索引的种类:
- 普通索引－加速查找
- 唯一索引－加速查找，约束列数据不能重复，可以为null
- 主键索引－加速查找，约束列数据不能重复，不能为null
- 组合索引－多列可以创建一个索引文件

1、普通索引的创建

创建表的同时创建索引

create table student(
    ......
    index ix_name(name)
)

添加索引

create index index_name on table_name(column_name)
mysql> create index nameindex on course(cname);
Query OK, 0 rows affected (0.80 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建索引的代价：增加删除更新都慢了

删除索引

drop index_name on tablename

查看索引

show index from table_name
mysql> show index from course;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| course |          0 | PRIMARY   |            1 | cid         | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| course |          1 | nameindex |            1 | cname       | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

2、唯一索引

create unique index index_name on table_name(column_name)

3、主键索引

创建一个主键列就行了

4、组合索引

#普通组合索引:无约束
create table student(
    ......
    index ix_name(name,age)
)

#联合唯一索引：有约束，两列数据同时不相同才能插入，不然报错
create unique index index_name on table_name(column_name,column_name2)

组合索引在查找的时候，遵循==最左匹配==的原则

select * from student where name = 'mudy'#会走索引
select * from student where name = 'mudy' and age = 12#会走索引
select * from student where age = 12 #不会走索引

1、覆盖索引

select * from tb where nid = 1
# 这种是先去索引中找，再去数据中找

select nid from tb where nid < 10
# 先去索引中找

＃zhezho难过情况，只需要在索引表中就能获取到数据时，称为覆盖索引

2、合并索引

name 与 age都是单独的索引

select * from student where name = 'mudy'
select * from student where name = 'mudy' or age = 15

执行计划－想对比较准确的表达出当前SQLyun行状况
是否走索引，走索引的效率高

explain SQL语句
mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.11 sec)

type:all 表示会对整个的数据表进行全表扫描
type:index 表示对全索引扫描
all与index的效率一般不高，都是有优化的余地

2、limit

3、range
对于索引进行范围查找的时候，会执行range
mysql> explain select * from student where sid<2;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)


注意：如果是>或!=的话，就是全表扫描了,不走索引，type:all


mysql> explain select * from course where cname='e' or cid='1';
+----+-------------+--------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table  | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                       |
+----+-------------+--------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | course | NULL       | index_merge | PRIMARY,nameindex | nameindex,PRIMARY | 33,4    | NULL |    2 |   100.00 | Using union(nameindex,PRIMARY); Using where |
+----+-------------+--------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.12 sec)

mysql> explain select * from course where cid='1';
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | course | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from course where cname='生物';
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | course | NULL       | ref  | nameindex     | nameindex | 33      | const |    2 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.09 sec)

index_merge:表示索引合并了,possible_keys表示有可能的索引

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;

参考type row,查看执行效率

如何命中索引


- 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;
- !=
    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                -- 不使用索引

int类型的!=

mysql> explain select * from student where class_id != 2;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | classindex    | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.07 sec)

mysql> explain select * from student where class_id > 2;
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | classindex    | classindex | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.03 sec)

mysql> explain select * from student where class_id < 2;
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | classindex    | classindex | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

面试必备

MySQL翻页

第一页 where nid > 16 limit 10

慢日志

ysql> show variables like '%query%';
+------------------------------+--------------------------------------+
| Variable_name                | Value                                |
+------------------------------+--------------------------------------+
| binlog_rows_query_log_events | OFF                                  |
| ft_query_expansion_limit     | 20                                   |
| have_query_cache             | YES                                  |
| long_query_time              | 10.000000                            |
| query_alloc_block_size       | 8192                                 |
| query_cache_limit            | 1048576                              |
| query_cache_min_res_unit     | 4096                                 |
| query_cache_size             | 1048576                              |
| query_cache_type             | OFF                                  |
| query_cache_wlock_invalidate | OFF                                  |
| query_prealloc_size          | 8192                                 |
| slow_query_log               | OFF                                  |
| slow_query_log_file          | /usr/local/mysql/data/liuna-slow.log |
+------------------------------+--------------------------------------+
13 rows in set (0.30 sec)

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,772评论 6赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,458评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,610评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,640评论 1赞 276
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,657评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,590评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,962评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,631评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,870评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,611评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,704评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,386评论 4赞 319
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,969评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,944评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,179评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 44,742评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,440评论 2赞 342

事务函数索引

面试必备

推荐阅读更多精彩内容