[关闭]
@q8517220 2019-08-23T13:36:24.000000Z 字数 8833 阅读 637

Mysql事务及索引

mysql

一、数据库索引概述；
二、数据库索引分类；
三、Mysql数据库管理索引；
四、数据库事务概述；
五、数据库事务特性；
六、Mysqk数据库管理事务；

一、数据库索引概述；

概述：索引是一种特殊的文件，包含着对数据表中所有记录的引用指针，如书本的目录类似，能够加快数据库的查询速度，更高效率的管理数据库；
作用：设置合适的索引后，数据库利用各种定位设置，可以大大的增加数据的查询速度；
当表很大时，或者查询涉及到多个表时，使用索引可以加快查询速度甚至上千倍；
可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本；
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
缺点：增加了数据库的存储空间；
在插入和修改数据的同时会增加更多的时间，因为索引也要一同变化；
依据：表的主键必须有索引，主键索引中的值时唯一的；
数据量超过300行的表应该有索引，不然会眼中影响数据库的查询性能；
唯一性太差的字段不适合建立索引，比如一列中的1000行都是同一个字段；
经常与其他表进行交互的表，其连接列中必须设置索引；
更新太过于频发的字段不建议设置为索引列；
经常出现在sql语句中的where中，建议设置为索引列；
索引建议设置在小字段上，较大的字段上不建议设置为索引；

二、数据库索引分类；

普通索引：最基本的索引，无唯一性之类的限制；
唯一性索引：索引列中的所有值只能出现一次，如身份证号码列；
主键索引：是一种唯一性夺银，在一个表中创建主键后，则自动创建主键索引；
单列索引和多列索引：可以为单列或者多列，列中可能有重复的值，例如表中的性别列；

三、Mysql数据库管理索引；

  1. 准备数据库和表：
  2. mysql> create database linuxfan;
  3. Query OK, 1 row affected (0.01 sec)
  4. mysql> show databases;
  5. +--------------------+
  6. | Database           |
  7. +--------------------+
  8. | information_schema |
  9. | linuxfan           |
  10. | mysql              |
  11. | performance_schema |
  12. | sys                |
  13. +--------------------+
  14. mysql> use linuxfan;
  15. Database changed
  16. mysql> create table it(岗位 char(16),姓名 char(16),身份证号 char(48),学历 char(16),工资 int);
  17. Query OK, 0 rows affected (0.00 sec)
  18. mysql> insert into it values('网络工程师','张三','150404199201091132','高中','5500');
  19. Query OK, 1 row affected (0.00 sec)
  20. mysql> insert into it values('网络工程师','李四','150406187805271432','专科','12000');
  21. Query OK, 1 row affected (0.00 sec)
  22. mysql> insert into it values('java工程师','王五','111376199609101456','初中','10000');
  23. Query OK, 1 row affected (0.00 sec)
  24. mysql> insert into it values('网络工程师','刘备','345646199209103256','高中','15000');
  25. Query OK, 1 row affected (0.00 sec)
  26. mysql> insert into it values('ui工程师','关羽','178645199909101189','本科','7000');
  27. Query OK, 1 row affected (0.00 sec)
  28. mysql> select * from it;
  29. +-----------------+--------+--------------------+--------+--------+
  30. | 岗位            | 姓名   | 身份证号           | 学历   | 工资   |
  31. +-----------------+--------+--------------------+--------+--------+
  32. | 网络工程师      | 张三   | 150404199201091132 | 高中   |   5500 |
  33. | 网络工程师      | 李四   | 150406187805271432 | 专科   |  12000 |
  34. | java工程师      | 王五   | 111376199609101456 | 初中   |  10000 |
  35. | 网络工程师      | 刘备   | 345646199209103256 | 高中   |  15000 |
  36. | ui工程师        | 关羽   | 178645199909101189 | 本科   |   7000 |
  37. +-----------------+--------+--------------------+--------+--------+
  39. 普通索引语法：create index 索引名称  on 表名(列名);
  40. mysql> create index putong on it(工资);
  41. Query OK, 5 rows affected (0.00 sec)
  42. mysql> show index from it;
  43. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  44. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  45. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  46. | it    |          1 | putong   |            1 | 工资        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
  48. 唯一性索引语法：create unique index  索引名称  on 表名(列名);
  49. mysql> create unique index weiyi on it(身份证号);
  50. Query OK, 5 rows affected (0.00 sec)
  51. mysql> show index from it;
  52. +-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  53. | Table | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  54. +-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  55. | it    |          0 | weiyi    |            1 | 身份证号     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
  56. | it    |          1 | putong   |            1 | 工资         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
  57. +-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  58. mysql> insert into it values('网络工程师','张飞','150404199201091132','小学','7800');
  59. ERROR 1062 (23000): Duplicate entry '150404199201091132' for key 'weiyi'
  60. mysql> select * from it;
  61. +-----------------+--------+--------------------+--------+--------+
  62. | 岗位            | 姓名   | 身份证号           | 学历   | 工资   |
  63. +-----------------+--------+--------------------+--------+--------+
  64. | 网络工程师      | 张三   | 150404199201091132 | 高中   |   5500 |
  65. | 网络工程师      | 李四   | 150406187805271432 | 专科   |  12000 |
  66. | java工程师      | 王五   | 111376199609101456 | 初中   |  10000 |
  67. | 网络工程师      | 刘备   | 345646199209103256 | 高中   |  15000 |
  68. | ui工程师        | 关羽   | 178645199909101189 | 本科   |   7000 |
  69. +-----------------+--------+--------------------+--------+--------+
  71. 创建主键：create table 表名  (字段一,字段二,primary key(列名));
  72. mysql> create table it2 (name char(16),id int,PRIMARY KEY (name));
  73. Query OK, 0 rows affected (0.01 sec)
  75. 多列索引：create index 索引名称  on 表名(列名一,列名二);
  76. mysql> create index duolie on it(工资,学历);
  77. Query OK, 5 rows affected (0.01 sec)
  78. mysql> show index from it;
  79. +-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  80. | Table | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  81. +-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  82. | it    |          0 | weiyi    |            1 | 身份证号     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
  83. | it    |          1 | putong   |            1 | 工资         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
  84. | it    |          1 | duolie   |            1 | 工资         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
  85. | it    |          1 | duolie   |            2 | 学历         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
  86. +-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

查看索引：show index from 表名;

四、数据库事务概述；

概述：事务是一种机制，一个操作序列，包含了一组数据库操作指令，并且将多个操作指令当作一个整体，一同向系统提交或者回滚的操作，事务保证了数据的一致性，如支付宝中转账的操作，转账的账户和收钱的账户，两个操作组成一个完整的事务；

五、数据库事务特性；

原子性：事务是一个完整的操作，数据必须处于一致的状态，事务中的操作，要么全部成功，要么全部失败，此特性就是原子性；
一致性：当事务执行完毕后，数据必须处于一致状态，在开始事务时数据保持一致状态，执行事务的过程中事务可以出现不一致的情况，事务完毕后，数据必须保证一致状态；
隔离性：多个事务之间互不依靠，互补影响，相对独立；
持久性：事务处理完毕后，在系统中发生的变化是永久性的，会永久的保留在数据库中；

六、Mysqk数据库管理事务；

管理事务的三个命令：
begin：开始事务，后边有多条数据库操作语句开始执行；
commit：开始提交一个事务，对应前边的begin操作，将事务处理的结果保存到数据文件中；
rollback：开始回滚一个事务，在begin和commit之间，将事务中的全部语句撤回，恢复到执行begin之前的数据状态；

  1. 管理事务：
  2. mysql> use linuxfan;
  3. Database changed
  4. mysql> show table status where name="it";           ##确保it表存储引擎为InnoDB，如若不是按以下命令修改；
  5. mysql> alter table it engine=InnoDB;
  6. mysql> select * from it;
  7. +-----------------+--------+--------------------+--------+--------+
  8. | 岗位            | 姓名   | 身份证号           | 学历   | 工资   |
  9. +-----------------+--------+--------------------+--------+--------+
  10. | 网络工程师      | 张三   | 150404199201091132 | 高中   |   5500 |
  11. | 网络工程师      | 李四   | 150406187805271432 | 专科   |  12000 |
  12. | java工程师      | 王五   | 111376199609101456 | 初中   |  10000 |
  13. | 网络工程师      | 刘备   | 345646199209103256 | 高中   |  15000 |
  14. | ui工程师        | 关羽   | 178645199909101189 | 本科   |   7000 |
  15. +-----------------+--------+--------------------+--------+--------+
  16. mysql> begin;                       ##开始事务
  17. Query OK, 0 rows affected (0.00 sec)
  18. mysql> insert into it values('php工程师','曹操','891878195409081198','本科','9000');
  19. Query OK, 1 row affected (0.00 sec)
  20. mysql> commit;                  ##提交事务
  21. Query OK, 0 rows affected (0.00 sec)
  22. mysql> select * from it;
  23. +-----------------+--------+--------------------+--------+--------+
  24. | 岗位            | 姓名   | 身份证号           | 学历   | 工资   |
  25. +-----------------+--------+--------------------+--------+--------+
  26. | 网络工程师      | 张三   | 150404199201091132 | 高中   |   5500 |
  27. | 网络工程师      | 李四   | 150406187805271432 | 专科   |  12000 |
  28. | java工程师      | 王五   | 111376199609101456 | 初中   |  10000 |
  29. | 网络工程师      | 刘备   | 345646199209103256 | 高中   |  15000 |
  30. | ui工程师        | 关羽   | 178645199909101189 | 本科   |   7000 |
  31. | php工程师       | 曹操   | 891878195409081198 | 本科   |   9000 |
  32. +-----------------+--------+--------------------+--------+--------+
  33. 6 rows in set (0.00 sec)
  35. mysql> begin;                       ##开始事务
  36. Query OK, 0 rows affected (0.00 sec)
  37. mysql> insert into it values('php工程师','赵云','987867199012081198','专科','9500');
  38. Query OK, 1 row affected (0.00 sec)
  39. mysql> select * from it;                ##未手动提交事务，但仍然能够看到数据，因为在当前已经开启的事务中可以看见，退出连接后，则自动提交事务
  40. +-----------------+--------+--------------------+--------+--------+
  41. | 岗位            | 姓名   | 身份证号           | 学历   | 工资   |
  42. +-----------------+--------+--------------------+--------+--------+
  43. | 网络工程师      | 张三   | 150404199201091132 | 高中   |   5500 |
  44. | 网络工程师      | 李四   | 150406187805271432 | 专科   |  12000 |
  45. | java工程师      | 王五   | 111376199609101456 | 初中   |  10000 |
  46. | 网络工程师      | 刘备   | 345646199209103256 | 高中   |  15000 |
  47. | ui工程师        | 关羽   | 178645199909101189 | 本科   |   7000 |
  48. | php工程师       | 曹操   | 891878195409081198 | 本科   |   9000 |
  49. | php工程师       | 赵云   | 987867199012081198 | 专科   |   9500 |
  50. +-----------------+--------+--------------------+--------+--------+
  51. mysql> exit
  52. Bye
  53. [root@lwh ~]# mysql -uroot -p123456
  54. mysql> use linuxfan;
  55. Database changed
  56. mysql> select * from it;
  57. +-----------------+--------+--------------------+--------+--------+
  58. | 岗位            | 姓名   | 身份证号           | 学历   | 工资   |
  59. +-----------------+--------+--------------------+--------+--------+
  60. | 网络工程师      | 张三   | 150404199201091132 | 高中   |   5500 |
  61. | 网络工程师      | 李四   | 150406187805271432 | 专科   |  12000 |
  62. | java工程师      | 王五   | 111376199609101456 | 初中   |  10000 |
  63. | 网络工程师      | 刘备   | 345646199209103256 | 高中   |  15000 |
  64. | ui工程师        | 关羽   | 178645199909101189 | 本科   |   7000 |
  65. | php工程师       | 曹操   | 891878195409081198 | 本科   |   9000 |
  66. | php工程师       | 赵云   | 987867199012081198 | 专科   |   9500 |
  67. +-----------------+--------+--------------------+--------+--------+
  69. mysql> set autocommit = 0;                      ##设置禁用自动提交事务
  70. Query OK, 0 rows affected (0.00 sec)
  71. mysql> begin;                                   ##开始事务
  72. Query OK, 0 rows affected (0.00 sec)
  73. mysql> insert into it values('nginx工程师','诸葛亮','187967198712081198','专科','6700');
  74. Query OK, 1 row affected (0.00 sec)
  75. mysql> rollback;                                ##回滚事务
  76. Query OK, 0 rows affected (0.00 sec)
  77. mysql> select * from it;                            ##查询已经无数据记录
  78. +-----------------+--------+--------------------+--------+--------+
  79. | 岗位            | 姓名   | 身份证号           | 学历   | 工资   |
  80. +-----------------+--------+--------------------+--------+--------+
  81. | 网络工程师      | 张三   | 150404199201091132 | 高中   |   5500 |
  82. | 网络工程师      | 李四   | 150406187805271432 | 专科   |  12000 |
  83. | java工程师      | 王五   | 111376199609101456 | 初中   |  10000 |
  84. | 网络工程师      | 刘备   | 345646199209103256 | 高中   |  15000 |
  85. | ui工程师        | 关羽   | 178645199909101189 | 本科   |   7000 |
  86. | php工程师       | 曹操   | 891878195409081198 | 本科   |   9000 |
  87. | php工程师       | 赵云   | 987867199012081198 | 专科   |   9500 |
  88. +-----------------+--------+--------------------+--------+--------+
添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注