事务、函数、索引

什么是事务？

        事务就是将一组SQL语句放在同一批次内去执行，如果一个SQL语句，则1该批次内的所有SQL都将被取消执行

事务的特点：

        一个中如果有一个数据操作失败，那么整个事务的所有数据操作都会失败，数据库数据就会回到滚到该事务开始之前的状态

事务的ACID原则：

原子性：

        意味着数据库中的事务执行是作为原子粒度，即不可再分，整个语句要么执行，要么不执行

一致性：

        即在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏

隔离性：

        事务的执行是互不干扰的，一个事务不可能看到其他事务运动时，中间某一时刻的数据

持久性：

        意味着在事务完成以后，该事务所对数据库所作的更改变持久的保存在数据库之中，并不会被回滚

事务的隔离性：

为了让不同的事务之间相互不存在干扰，就需要对事务的操作进行隔离，事务的隔离性也就是将操作同一个数据的事务相互分离，让操作之间分开有序的执行

用什么方式实现事务的隔离性：

通常数据库里都是采用锁的机制，保证事务之间的隔离性

MySQL中的锁：

锁分类：

        基于锁的属性分类：共享锁(读锁、S锁)、排他锁(写锁、X锁)

        基于锁的粒度分类：表锁、行锁(记录锁、间隙锁、临键锁)

        基于锁的状态分类：意向共享锁、意向排它锁

事务的隔离级别:

事务并发问题：

        在事务并发执行的时候，如果不进行事务的隔离，那么就会产生脏写、脏读、重复读、幻读的问题

事务的隔离级别：

        1.READ_UNCOMMITTED     读未提交

        2.READ_COMMITTED   读提交(不可重复读)

        3.REPEATABLE_READ    可重复读

        4.SERIALIZABLE                      串行化

函数：

慢查询：

MySQL默认10秒内没有响应的SQL结果，则为慢查询(可修改时间)

MySQL对慢查询的操作：

Show status like ‘connections’；显示到mysql数据库的连接数

Show variables like '%slow_query%';显示慢查询的状态

Set global log_output='TABLE'；设置慢查询的到表mysql.slow_log

Set global long_query_time = 3；设置满查询的时间

Set global long_query_log='ON'；开启慢查询

Show status like 'slow_queries';慢查询的次数

Select * from mysql.slow_log;慢查询记录

Select convert(sql_text using utf8) sql_text from mysql.slow_log慢查询sql语句

Set global slow_query_log='OFF';关闭慢查询

执行计划：

EXPLAIN列的解释：

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型，从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

system：系统级别

const：效率最高

ALL：效率最低

Possible_keys:显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个适合的语句

key：实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。

这种情况下，可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引

Key_len:使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用，如果可能的话，是一个常数

rows：MYSQL认为必须检查的用来返回请求数据的行数

Extra：

关于MYSQL如何解析查询的额外信息，是Using temporaryUsing filesort，意思MYSQL根本11不能使用索引，结果是检索会很慢

索引：

索引的优点：

1.高效性：利用索引可以提高数据库的查询效率

2.唯一性：索引可以确保所查的数据的唯一性

3.完整性：用户可以加速和表之间的连接，实现表与表之间的完整性

4.特殊能力：通过使用1索引，可以在查询过程中，使用优化隐藏器，提高系统性能

索引的缺点：

1.虽然索引大大提高了查询速度，同时却会降低新表的速度，如对表进行INSERT、UPDATE和DELETE。

2.因为更新新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件

3.如果你在一个大表上创建了多种组合索引，索引文件会膨胀很快

4.如果你在一个大表上创建多种组合，索引文件会膨胀很快

索引只是提高效率的一个因素，如果你的MySQL有大数据的表，就需要花时间研究建立最优秀的索引，或优化查询语句

索引的分类：

主键索引：在数据库关系图中为表定义为一个主键将自动创建主键索引

唯一索引：不允许具有索引值相同的行，从而禁止重复的索引键值

常规索引：最基本的索引类型，没有唯一性之类的限制

全文索引：搜索引擎的关键技术，用于检索文件信息，可以是词语或者段落

主键索引：

语法：create table 表名(

                字段1 INT(11) AUTO_INCREMENT PRIMARY KEY，

                或 PRIMARY KEY('字段1')

            )

唯一索引：

语法：create table 表名(

                字段1 INT(11) NOT NULL UNIQUE,

                或 UNIQUE KEY('字段1')

             )

常规索引：

语法：create table 表名(

                字段1 INT(11) NOT NULL,

                INDEX/KEY('字段1')

             )

全文索引：

语法：create table 表名(

                字段1 VARCHAR(32) NOT NULL

                Fulltext key(子段名，字段名，字段名) with parser ngram

           )ENGINE=innodb

全文索引的注意事项：

        1.MySQL5.6之前版本，只有myisam支持全文索引，5.6之后，innodb和，myisam均支持全文索引

        2.只有char、varchar、text类型字段能创建全文索引

        3.当大量写入数据时，建议先写入数据，后再建立全文索引提高效率

        4.MySQL内置ngram解析器，可以解析中日韩三国文字。有汉字的一定要启动它

        5.英文分词用空格，逗号：中文分词用ngram_token_size设定

索引的管理：

1.查看索引：

        Show index(或key) from 表名

2.删除索引：

        ALTER TABLE 表名 DROP PRIMARY KEY;

        DROP INDEX 索引名 ON 表名

        ALTER TABLE 表名 DROP INDEX 索引名1；

3.修改索引：

        ALERT TABLE 表名 ADD 索引类型(数据列名)

        ALERT TABLE <表名> add FULLTEXT INDEX <索引名> (字段名1，字段名2) [WITH PARSER ngram]

索引的使用原则：

        最左前缀匹配原则，非常重要的原则

        对于联合索引，总是从索引的最前面字段开始，接着往后，中间不能跳过

        尽量选择区分度高的作为索引

        区分度的公式是count(disinct col)/count(*)

        =和in可以乱序

        索引列不能参与计算，保持列的干净

        尽量的扩展索引，不要新建索引

        索引不会包含有null值得列

        使用短索引

        索引列排序

        like 语句操作

使用索引的场景：

        1.匹配全值：对索引中所有列都指定具体值，即对索引中的所有列都有等值匹配的条件

        2.匹配值的范围查询：对索引的值能够进行范围查找

        3.匹配最左前缀：仅仅使用索引中的最左边列进行查询。比如组合索引(col1，col2，col3)能够被col1，col1+col2，col1+col2+col3的等值1查询利用到的

        4.仅对索引进行查询：当前查询列都再索引字段中。即select中的列都在索引中

        5.匹配列前缀：仅仅1使用索引的第一列，并且只包含索引第一列的开头部分进行查找。例如：where like 'xxx%'

        6.索引部分等值匹配，部分范围匹配

        7.若列名是索引，则使用column_name is null就会使用索引

索引存在但不能使用索引的场景：

        1.以%开头的like查询

        2.数据类型出隐式转化，不会使用索引

        3.组合索引，不满足最左原则，不使用符合索引

        4.估计使用索引比全表扫描还慢，则不要使用索引

        5.用or分割条件，若or前后只要有一列没有索引，就不会用索引

        6.使用!=或<>操作符时：尽量避免使用!=或<>操作符，否则数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效

        7.对字段进行null等值判断：应尽量避免在where子句中进行null等判断，否则将导致引擎放弃使用索引而进行全表扫描

        8.避免select*：在解析的过程中，将'*'依次转换成所有的列名，这个是通过查询1数据字典完成的，这意味着将耗费更多的时间

SQL优先原则：

1.建议使用预编译语句进行数据库操作

2.避免数据类型的隐式转换

3.充分利用表上已经存在的索引

4.禁止使用select * 必须使用select 查询

5.禁止使用不含字段列表的INSERT语句

6.避免使用子查询，可以把字查询优化为join操作

7.避免使用JOIN关联太多的表

8.对应同一列进行or判断时，使用in代替or

9.where从句中禁止对列进行1函数转换和计算

10.在明显不会有重复值使用UNION ALL 而不是 UNION

11.拆分复杂的大SQL为多个小SQL