mysql第五天：

1.在每一次进行完数据库的操作后我们都需要吧数据库进行关闭，否则下一个人无法进行数据库的访问和操作：

cursor.close()
conn.close()

View Code

2.索引：

　1.索引主要分为以下几种：

　　1普通索引：主要是为了加速查找：

　　2.主键索引：加速快速查找+不能为空+不能重复

　　3.唯一索引：加速查找+不能重复

　　4.联合索引：分为联合普通索引、联合主键索引、联合唯一索引：

3.使用索引和不适用索引进行查找数据所需要时间对比：

mysql> select * from userinfo where name='alex5555';
+------+----------+----------------+
| cid  | name     | email          |
+------+----------+----------------+
| 5555 | alex5555 | alex5555qq.com |
+------+----------+----------------+
1 row in set (0.08 sec)

mysql> select * from userinfo where id =5555;
ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
mysql> select * from userinfo where cid =5555;
+------+----------+----------------+
| cid  | name     | email          |
+------+----------+----------------+
| 5555 | alex5555 | alex5555qq.com |
+------+----------+----------------+
1 row in set (0.00 sec)

View Code

4.有索引和无索引的区别：

　　1对于数据表中的数据如果在查找时没有索引：查找的顺序是从数据的开头查找到数据的结尾，这样来看在数据量很大的时候会很浪费时间。

　　2.对于有序索引来说，他是将元数据里面在从新创建一个额外的文件进行存储，如果在查找时命中索引，则不是先去数据里面进行查找，而是先去这个创建的文件中进行查找，查找到之后就可以知道所需查找内容的地址然后直接定位就可以找到。

　　3.对于查找来说有索引查找速率要高于没有索引的数据查找，但是在于更新数据、删除数据和添加数据的时候，则没有索引的数据速率高于有索引的数据。

5.给某一列数据添加普通的索引：‘

　　create index 索引名 on 表名 (列名)

mysql> create index email_index on userinfo (email);
Query OK, 0 rows affected (0.73 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from userinfo where email ='alex66666';
Empty set (0.00 sec)

View Code

通过上述数据对比来看查询接过所使用的时间有缩减少：

6.删除某一列普通的索引：

　　drop index 索引名 on 表名

mysql> drop index email_index on userinfo;
Query OK, 0 rows affected (0.02 sec)

View Code

7.数据在进行创建索引的时候主要使用的方式有两种：

　　1.一种是hash存储方式：

　　先是普通索引的列的名称 hash值数据存放的地址

　　这种存储方式和数据表里的存储数据方式并不一致，属于无序的存储方式，所以只适合查找单一的列表，对于某个范围内的数据查找还是使用下面的方法。

　　2.btree存储方式：

　　这种存储方式使用的二分法进行存储；存储方式如下：即使数据量很大也只需要几十次就可以查找到。

8.所有索引的创建方法：创建方法一般有两种：（一种是在创建表的时候进行创建，另一种实在进行数据操作的时候进行创建）：

　　1.普通索引的创建方法和删除方法见上面：

　　2.唯一索引的创建方法：

　　　　create unique index 索引名称on 表名 (列明)

create unique index name_index on userinfo (name);
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

View Code

　　　　删除唯一索引的方法：

 drop index name_index on userinfo;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

View Code

9.覆盖索引：查找内容就是自己判断的内容：

o' at line 1
mysql> select cid from userinfo where cid=1222;
+------+
| cid  |
+------+
| 1222 |
+------+
1 row in set (0.00 sec)

View Code

10合并索引：把两个单一的索引用and连接起来进行查询：

mysql> select * from userinfo where cid='1222' and email='alex3333';
Empty set (0.00 sec)

View Code

11.联合索引使用的规则·是最左匹配原则：规则使用如下如果使用括号左边的列明或者左边列明和右边列明一起会命中索引搜索，否则不会命中：

mysql> create index name_email on userinfo(name,email);
Query OK, 0 rows affected (0.85 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from userinfo where name='alexjj';
Empty set (0.00 sec)

mysql> select * from userinfo where name='alexjji'and email='wusirjiji';
Empty set (0.01 sec)

mysql> select * from userinfo where email='alexjinklj';
Empty set (0.07 sec)

View Code

　　从10和11可以看出合并索引使用的范围要比联合索引命中的范围要大，但是联合索引执行的速度要比合并索引执行的速度快：（但是我得出结论是合并索引执行速度要比联合索引执行速度要快）

12.对于一些已经索引的列但是使用的指令不同有可能使得索引无法命中：下面是几种情况：

　　1.like用法：

mysql> select * from userinfo where name='alex444';
+-----+---------+---------------+
| cid | name    | email         |
+-----+---------+---------------+
| 444 | alex444 | alex444qq.com |
+-----+---------+---------------+
1 row in set (0.00 sec)

mysql> select * from userinfo where name like 'alex666%';

View Code

对于这种指令我们可以使用第三方软件进行填充测试：

　　2.使用函数会有时候会导致索引命中失败：

mysql> select * from userinfo where reverse(name)='alexttt';
Empty set (0.08 sec)

View Code

　　3.使用or命令也会使得索引命中失败：

mysql> select * from userinfo where cid=4444 or name='laejinf';
+------+----------+----------------+
| cid  | name     | email          |
+------+----------+----------------+
| 4444 | alex4444 | alex4444qq.com |
+------+----------+----------------+
1 row in set (0.01 sec)

View Code

　　但是如果or条件中有为建立索引的列才会失效

mysql> select * from userinfo where name='alejlji'or email='jfinf1111j';
Empty set (0.00 sec)

View Code

　　4.类型不一致也会使得不成功：

select * from userinfo where name=999;
Empty set, 65535 warnings (0.25 sec)

View Code

　　但是对于整型或者主键是不起作用的·：

mysql> select * from userinfo where cid='111';
+-----+---------+---------------+
| cid | name    | email         |
+-----+---------+---------------+
| 111 | alex111 | alex111qq.com |
+-----+---------+---------------+
1 row in set (0.00 sec)

View Code

　　5　　.使用！=也会使得不成功：但是对于主键这个就不适用：

　　6.　　排序操作：如果是主键还是走索引其他的不走索引：

mysql> select * from userinfo where cid<50 order by name desc ;

View Code

13.在进行mysql之前我们可以使用 explain 来进行测试执行的时间（一般是准确的）

　　1.第一个程序

mysql> explain  select * from userinfo;
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | userinfo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299775 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+

View Code

从结果中我们可以看出type类型为all

　　2.type执行速度快慢表：

　　　　1.all 会扫描数据库中所有的数据：执行效率最慢：

mysql> explain select * from userinfo;
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | userinfo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299775 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

View Code

　　2.index：会扫描创建文件文件里面的所有索引值：这个就是比所有数据少了重复的：

mysql> explain select cid from userinfo ;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key        | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | userinfo | NULL       | index | NULL          | name_index | 43      | NULL | 299775 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

View Code

　　3.range : 会扫描创建文件文件里面的部分索引值：

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

View Code

　　4.con常量：只会扫描这一个：

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

View Code

5.对于慢日志操作：

　　1.查看配置日志信息的命令：show variables like '%query%'

mysql> show variables like '%query%';
+------------------------------+------------------------------------------------------+
| Variable_name                | Value                                                |
+------------------------------+------------------------------------------------------+
| binlog_rows_query_log_events | OFF                                                  |
| ft_query_expansion_limit     | 20                                                   |
| have_query_cache             | NO                                                   |
| long_query_time              | 10.000000                                            |
| query_alloc_block_size       | 8192                                                 |
| query_prealloc_size          | 8192                                                 |
| slow_query_log               | OFF                                                  |
| slow_query_log_file          | D:mysql-8.0.19-winx64dataLAPTOP-TP4FUMDJ-slow.log |
+------------------------------+------------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)

mysql>

View Code

　　2.设置日志的关和闭： slow_query_log

mysql> set global  slow_query_log  =on;
Query OK, 0 rows affected (0.02 sec)

View Code

　　3.时间限制如果超过了此时间就会被记录：long_query_time

　　4.日志文件的存储地址设置： slow_query_log_file

13.进行分页操作：

　　1.使用我们以前学习的内容进行分页：

mysql> select * from userinfo limit 20000,10;
+-------+-----------+-----------------+
| cid   | name      | email           |
+-------+-----------+-----------------+
| 20001 | alex20001 | alex20001qq.com |
| 20002 | alex20002 | alex20002qq.com |
| 20003 | alex20003 | alex20003qq.com |
| 20004 | alex20004 | alex20004qq.com |
| 20005 | alex20005 | alex20005qq.com |
| 20006 | alex20006 | alex20006qq.com |
| 20007 | alex20007 | alex20007qq.com |
| 20008 | alex20008 | alex20008qq.com |
| 20009 | alex20009 | alex20009qq.com |
| 20010 | alex20010 | alex20010qq.com |
+-------+-----------+-----------------+
10 rows in set (0.00 sec)

mysql> select * from userinfo limit 200000,10;
+--------+------------+------------------+
| cid    | name       | email            |
+--------+------------+------------------+
| 200001 | alex200001 | alex200001qq.com |
| 200002 | alex200002 | alex200002qq.com |
| 200003 | alex200003 | alex200003qq.com |
| 200004 | alex200004 | alex200004qq.com |
| 200005 | alex200005 | alex200005qq.com |
| 200006 | alex200006 | alex200006qq.com |
| 200007 | alex200007 | alex200007qq.com |
| 200008 | alex200008 | alex200008qq.com |
| 200009 | alex200009 | alex200009qq.com |
| 200010 | alex200010 | alex200010qq.com |
+--------+------------+------------------+
10 rows in set (0.04 sec)

View Code

通过结果我们可以看出随着查入的数据越来越靠后，所需要的时间也越来越多。

　　2.使用现在学习的内容进行写：就是使用第一个值和最后一个值：

mysql> select *from userinfo where cid>200010 limit 10;
+--------+------------+------------------+
| cid    | name       | email            |
+--------+------------+------------------+
| 200011 | alex200011 | alex200011qq.com |
| 200012 | alex200012 | alex200012qq.com |
| 200013 | alex200013 | alex200013qq.com |
| 200014 | alex200014 | alex200014qq.com |
| 200015 | alex200015 | alex200015qq.com |
| 200016 | alex200016 | alex200016qq.com |
| 200017 | alex200017 | alex200017qq.com |
| 200018 | alex200018 | alex200018qq.com |
| 200019 | alex200019 | alex200019qq.com |
| 200020 | alex200020 | alex200020qq.com |
+--------+------------+------------------+
10 rows in set (0.00 sec)

View Code

再通过原来的方法进行对比：

mysql> select * from userinfo limit 200011,10;
+--------+------------+------------------+
| cid    | name       | email            |
+--------+------------+------------------+
| 200012 | alex200012 | alex200012qq.com |
| 200013 | alex200013 | alex200013qq.com |
| 200014 | alex200014 | alex200014qq.com |
| 200015 | alex200015 | alex200015qq.com |
| 200016 | alex200016 | alex200016qq.com |
| 200017 | alex200017 | alex200017qq.com |
| 200018 | alex200018 | alex200018qq.com |
| 200019 | alex200019 | alex200019qq.com |
| 200020 | alex200020 | alex200020qq.com |
| 200021 | alex200021 | alex200021qq.com |
+--------+------------+------------------+
10 rows in set (0.04 sec)

View Code

　　3去前面10个数据：

mysql> select * from (select * from userinfo where cid<20001 order by cid desc limit 10) as N order by N.cid asc;
+-------+-----------+-----------------+
| cid   | name      | email           |
+-------+-----------+-----------------+
| 19991 | alex19991 | alex19991qq.com |
| 19992 | alex19992 | alex19992qq.com |
| 19993 | alex19993 | alex19993qq.com |
| 19994 | alex19994 | alex19994qq.com |
| 19995 | alex19995 | alex19995qq.com |
| 19996 | alex19996 | alex19996qq.com |
| 19997 | alex19997 | alex19997qq.com |
| 19998 | alex19998 | alex19998qq.com |
| 19999 | alex19999 | alex19999qq.com |
| 20000 | alex20000 | alex20000qq.com |
+-------+-----------+-----------------+
10 rows in set (0.01 sec)

View Code