SQL优化

  SQL优化

 一、索引

1. 索引的定义

   排好序的快速查找数据结构。

   索引是对数据表中一列或多列的值进行排序的一种数据结构，使用索引可提高数据库中特定数据的查询速度。

   索引与表或视图关联的独立的、物理的数据库结构，可以加快从表或视图中检索行的速度。

   在数据库中索引使数据库程序无须对整个表进行扫描，就可以找到所需数据。

   数据库索引是一列或多列生成的键。

   索引也是数据库中存储索引的数据页，存放键值以及指向数据行位置的指针。

2. 索引的特点

   优点：

   1.通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
   2.大大加快数据查询速度。
   3.在引用完整性方面，加速表与表之间的连接。
   4.在使用分组和排序查询数据时，减少查询中分组和排序的时间。

   缺点：

   1. 创建索引和维护索引耗费时间， 随着数据量的增加所耗费的时间也会增加。
   2. 索引需要占用磁盘空间，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
   3. 对表进行增删改操作时，索引也会动态的维护，降低了数据的维护速度。

3. 索引的分类

   建议一张表最多5个左右，但最终使用一个索引

   1. 普通索引

      mysql中基本索引类型，允许在定义索引的列中插入重复值和空值。

      单值索引：一个索引只包含单个列，一个表可以有多个单列索引。

   2. 唯一索引

      索引列的值必须唯一，但允许有空值（但只能有一个）。

   3. 主键索引

      特殊的唯一索引，不允许有空值。

      主键索引一定是唯一索引，但是唯一索引不一定是主键索引。

   4. 组合索引

      一个索引包含多个列。

      在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。

      创建组合索引时注意：

      A.当需要频繁地将两列或多列作为一个整体进行搜索时，可以创建组合索引。

      B.创建组合索引时先列出唯一性最好的列。

      C.组合索引中列的顺序和数量会影响查询的性能。

   5. 全文索引

      全文索引可以在char、varchar或者text类型的列上设置。
      只有在MyISAM储存引擎支持全文索引。

   6. 空间索引

      对空间数据类型的字段建立索引。
      分别为：geometry，point，linestring，polygon。

4. 索引的设计原则

   索引的设计不合理或者缺少索引都会影响数据库和应用程序的性能。

   设计索引时，应该考虑以下规则：

   1.索引并非越多越好；

   2.避免对经常更新的列设置索引，并且索引中的列尽可能少；对经常用于查询的字段应该创建索引，但要避免添加不必要字段；

   3.数据量小的表最好不要使用索引；

   4.在不同值较少的字段上不必要建立索引，如性别；

   5.在频繁进行排序或分组的列上建立索引，如果经常需要排序的列有多个，可以在这些列上建立组合索引。

   6.主键自动建立主键索引。

   7.外键关系建立普通索引。

5. 创建索引

   1）创建表的时候创建索引

   2）创建普通索引

   3）创建组合索引

   4）在已存在的表上创建索引

    alter table命令创建索引

     alter table 表名 add 【unique】 index 索引名 (字段名【(长度)】,...);

     添加主键索引：alter table 表名 add primary key （字段名，...）；

    使用create index创建索引

     create 【unique】 index 索引名 on 表名(字段名【(长度)】,...);

   5）删除索引

   drop index 索引名 on 表名；

   alert table 表名 drop index 索引名；

   6）查看索引

   show index  from 表名；

二、事务

1. 事务概念

   事务是一种机制，它包含了一组数据库操作命令，而且将所有的命令作为一个整体一起向数据库提交或撤销。

   事务是一个不可分割的逻辑工作单元。

   在mysql中只有存储引擎innodb支持事务。查看mysql支持的存储引擎show engines。存储引擎是存储数据的技术。

2. 事务特点

   简称ACID

    原子性（Atomicity）

     事务是一个完整性操作，要么全都成功执行，要么就撤销所有的操作，事务的单元不可能出现部分成功的情况。

    一致性（Consistency）

     事务完成前后，数据必须保持完全一致的状态。

    隔离性（Isolation）

     事务是相对独立的，一个事务对数据进行修改时，其他事务是不能修改的。

    持久性（Durability）

     事务完成后，它对于系统的影响是永久性的。

3. 在SQL中使用事务

   编写事务中的sql语句：insert、update、delete

   1）显式事务：事务具有明显的开启和结束的标记

   前提：必须先设置自动提交功能为禁用

   set autocommit = 0;   只针对当前事务有效
   
   2）事务操作步骤：

   1.开始事务：begin 或start transaction
   2.提交事务：commit
   3.回滚（撤销）事务：rollback

4. 事务的隔离级别

   隔离性用来解决多用户操作中的并发冲突问题，隔离级别为：

   - 未提交读（read uncommitted）

     最低的隔离级别。事务能够读取其他事务**正在修改并未提交**的数据，无法保证数据的完整性。

   - 已提交读（read committed）

     该隔离级别能确保其他事务不能读取当前事务**正在修改但未提交**的记录。

   - 重复读（repeatable read）

     mysql默认的隔离级别。隔离级别高，能确保其他事务不能修改当前事务中**正在读取但未提交**的数据。

   - 可串行化（serializable）

     最高的隔离级别，事务之间完全隔离，事务之间按串行化方式执行。要访问其他事务操作的数据，一定要等其他事务完成提交以后才能进行。

5. 事务并发问题（多个事务同时读取同一数据时，尽量避免）

   1.脏读：发生在更新操作时。一个事务t1读取了已经被事务t2更新但未提交的字段后，回滚，t1读取到的数据是临时的无效的。
   2.不可重复读：发生在更新后。事务t1读了一个字段，t2更新了该字段之后，t1再次读取，值不同了。
   3.幻读：发生在插入和删除时。事务t1读取表中一个字段，t2插入了新行，之后t1再次读取同一个表，多出几行记录。