mysql 学习记录---->索引、视图

来源：互联网发布：田岛美工刀架编辑：程序博客网时间：2024/06/05 05:32
一理论：
1) 设计索引的原则：
1.搜索的索引列是出现在where子句或者连接子句中的列。
2.使用唯一索引时，索引的列基数越大效果越好。
3.使用短索引涉及的IO更好，比较更快。在索引缓存中的块能容纳更多的键值。
4.最多前缀：即在创建一个n列的索引时，实际是创建了mysql的N个索引，可利用索引中最左边的列来匹配行。
5.过度索引可能会使mysql选择不到要使用的最好索引。
6.在innodb存储引擎的表的保存顺序是：主键、唯一索引、内部列。
2) 视图的优势：
1.简单：面对用户的是过滤完成的结果集。
2.安全：对视图的权限控制可以限制到行与列。
3.数据独立：源表增加与修改列名可与视图无影响。
二 sql记录：
[sql] view plaincopy
1) 使用hash与b-tree索引  
mysql> use test1;  
Reading table information for completion of table and column names  
You can turn off this feature to get a quicker startup with -A  
  
Database changed  
  
mysql>  CREATE TABLE city_memory(  
    ->   `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,  
    ->   `city` varchar(50) NOT NULL,  
    ->   `country_id` smallint(5) unsigned NOT NULL,  
    ->   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
    ->   PRIMARY KEY (`city_id`),  
    ->   KEY `idx_fk_country_id` (`country_id`),  
    ->   CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE  
    -> ) ENGINE=memory AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;  
  
mysql> select * from city;  
+---------+------+------------+---------------------+  
| city_id | city | country_id | last_update         |  
+---------+------+------------+---------------------+  
|     251 | bill |      10000 | 2015-10-02 20:48:51 |  
+---------+------+------------+---------------------+  
  
mysql> explain   
    -> select * from city   
    -> where country_id > 9 and country_id<23186  \G;  
*************************** 1. row ***************************  
           id: 1  
  select_type: SIMPLE  
        table: city  
         type: range  
possible_keys: idx_fk_country_id  
          key: idx_fk_country_id  
      key_len: 2  
          ref: NULL  
         rows: 1  
        Extra: Using where  
  
ERROR:   
No query specified  
  
mysql> insert into city_memory select * from city;  
  
mysql> explain  select * from city_memory  where country_id > 9 and country_id<23186\G  
*************************** 1. row ***************************  
           id: 1  
  select_type: SIMPLE  
        table: city_memory  
         type: system  
possible_keys: idx_fk_country_id  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 1  
        Extra:   
  
2)使用包含join,union,group,having,count等的视图  
mysql> CREATE TABLE `payment` (  
    ->   `id` smallint(6) DEFAULT NULL,  
    ->   `staff_id` smallint(6) DEFAULT NULL,  
    ->   `amount` decimal(15,2) DEFAULT NULL,  
    ->   KEY `id` (`id`)  
    -> ) ENGINE=innodb AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;  
  
mysql> insert into payment(staff_id,amount) values  
    -> (1,3.2),  
    -> (1,5.8),  
    -> (2,3.5),  
    -> (1,9.3),  
    -> (2,9.1);  
  
mysql> create or replace view payment_sum as  
    -> select staff_id ,sum(amount) from payment group by staff_id;  
  
mysql> select * from payment_sum;  
+----------+-------------+  
| staff_id | sum(amount) |  
+----------+-------------+  
|        1 |       18.30 |  
|        2 |       12.60 |  
+----------+-------------+  
  
mysql> insert into payment(staff_id,amount) values   
    -> (1,105.71038);  
  
mysql> select * from payment_sum;  
+----------+-------------+  
| staff_id | sum(amount) |  
+----------+-------------+  
|        1 |      124.01 |  
|        2 |       12.60 |  
+----------+-------------+  
  
mysql> drop view payment_sum;  
  
mysql> create or replace view payment_sum as  
    -> select * from payment having sum(amount) > 10;  
  
mysql> select * from payment_sum;  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
| NULL |        1 |   3.20 |  
+------+----------+--------+  
  
mysql> insert into payment_test( id,staff_id,amount)  values   
    -> (1,1,3.2),  
    -> (2,2,9.3),  
    -> (3,1,1.83512935),  
    -> (4,1,2.283915),  
    -> (5,2,3.283852),  
    -> (6,2,1.8);    
  
mysql> drop view payment_sum ;  
  
mysql> create or replace view payment_sum as  
    -> select * from payment group by id having sum(amount) > 3;  
  
mysql> select * from payment_sum;  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    1 |        1 |   3.20 |  
|    2 |        2 |   9.30 |  
|    5 |        2 |   3.28 |  
+------+----------+--------+  
  
mysql> insert into payment(id,staff_id,amount) values ( 9,1,3.185), (19,1,9.91852);  
  
mysql> select * from payment_sum;  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    1 |        1 |   3.20 |  
|    2 |        2 |   9.30 |  
|    5 |        2 |   3.28 |  
|    9 |        1 |   3.19 |  
|   19 |        1 |   9.92 |  
+------+----------+--------+  
  
mysql> update payment set amount = 20.185 where id = 5;  
  
mysql> select * from payment_sum;  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    1 |        1 |   3.20 |  
|    2 |        2 |   9.30 |  
|    5 |        2 |  20.19 |  
|    9 |        1 |   3.19 |  
|   19 |        1 |   9.92 |  
+------+----------+--------+  
  
mysql> drop view payment_sum;   
  
mysql> CREATE TABLE `payment_test` (  
    ->   `id` smallint(6) DEFAULT NULL,  
    ->   `staff_id` smallint(6) DEFAULT NULL,  
    ->   `amount` decimal(15,2) DEFAULT NULL,  
    ->   KEY `id` (`id`)  
    -> ) ENGINE=innodb AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;  

  
mysql> insert into payment_test( id,staff_id,amount)  values   
    -> (1,1,13.2),  
    -> (2,2,91.3),  
    -> (3,1,13.83512935),  
    -> (4,1,25.283915),  
    -> (5,2,28.283852),  
    -> (6,2,13.8);  
  
mysql> ( select * from payment )   
    -> union   
    -> ( select * from payment_test);  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    1 |        1 |   3.20 |  
|    2 |        2 |   9.30 |  
|    3 |        1 |   1.84 |  
|    4 |        1 |   2.28 |  
|    5 |        2 |  20.19 |  
|    6 |        2 |   1.80 |  
|    9 |        1 |   3.19 |  
|   19 |        1 |   9.92 |  
|    1 |        1 |  13.20 |  
|    2 |        2 |  91.30 |  
|    3 |        1 |  13.84 |  
|    4 |        1 |  25.28 |  
|    5 |        2 |  28.28 |  
|    6 |        2 |  13.80 |  
+------+----------+--------+  
  
mysql> create or replace view payment_sum as  
    -> ( select * from payment )   
    -> union   
    -> ( select * from payment_test)   
    -> ;    
  
mysql> select * from payment_sum;  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    1 |        1 |   3.20 |  
|    2 |        2 |   9.30 |  
|    3 |        1 |   1.84 |  
|    4 |        1 |   2.28 |  
|    5 |        2 |  20.19 |  
|    6 |        2 |   1.80 |  
|    9 |        1 |   3.19 |  
|   19 |        1 |   9.92 |  
|    1 |        1 |  13.20 |  
|    2 |        2 |  91.30 |  
|    3 |        1 |  13.84 |  
|    4 |        1 |  25.28 |  
|    5 |        2 |  28.28 |  
|    6 |        2 |  13.80 |  
+------+----------+--------+  
  
mysql> update payment_test set amount = 113.3333  
    ->  where id = 6;  
  
mysql> select * from payment_sum;  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    1 |        1 |   3.20 |  
|    2 |        2 |   9.30 |  
|    3 |        1 |   1.84 |  
|    4 |        1 |   2.28 |  
|    5 |        2 |  20.19 |  
|    6 |        2 |   1.80 |  
|    9 |        1 |   3.19 |  
|   19 |        1 |   9.92 |  
|    1 |        1 |  13.20 |  
|    2 |        2 |  91.30 |  
|    3 |        1 |  13.84 |  
|    4 |        1 |  25.28 |  
|    5 |        2 |  28.28 |  
|    6 |        2 | 113.33 |  
+------+----------+--------+  
  
mysql> alter table payment_test add column payment_id int (11);   
  
mysql> select * from payment_test;  
+------+----------+--------+------------+  
| id   | staff_id | amount | payment_id |  
+------+----------+--------+------------+  
|    1 |        1 |  13.20 |       NULL |  
|    2 |        2 |  91.30 |       NULL |  
|    3 |        1 |  13.84 |       NULL |  
|    4 |        1 |  25.28 |       NULL |  
|    5 |        2 |  28.28 |       NULL |  
|    6 |        2 | 113.33 |       NULL |  
+------+----------+--------+------------+   
  
mysql> update payment_test set payment_id = 1 where id = 1 ;   
  
mysql> update payment_test set payment_id = 2 where id = 2 ;   
= 6 where id = 6 ;    
  
mysql> update payment_test set payment_id = 3 where id = 3 ;    
  
mysql> update payment_test set payment_id = 4 where id = 4 ;   
  
mysql> update payment_test set payment_id = 5 where id = 5 ;   
Query OK, 1 row affected (0.00 sec)  
Rows matched: 1  Changed: 1  Warnings: 0  
  
mysql> update payment_test set payment_id = 6 where id = 6 ;    
  
mysql> insert into payment_test(id,payment_id) values (7,20);  
  
mysql> select * from payment_test;  
+------+----------+--------+------------+  
| id   | staff_id | amount | payment_id |  
+------+----------+--------+------------+  
|    1 |        1 |  13.20 |          1 |  
|    2 |        2 |  91.30 |          2 |  
|    3 |        1 |  13.84 |          3 |  
|    4 |        1 |  25.28 |          4 |  
|    5 |        2 |  28.28 |          5 |  
|    6 |        2 | 113.33 |          6 |  
|    7 |     NULL |   NULL |         20 |  
+------+----------+--------+------------+  
  
mysql> update payment_test set payment_id = 3 where id = 5;  
  
mysql> select * from payment_test;  
+------+----------+--------+------------+  
| id   | staff_id | amount | payment_id |  
+------+----------+--------+------------+  
|    1 |        1 |  13.20 |          1 |  
|    2 |        2 |  91.30 |          2 |  
|    3 |        1 |  13.84 |          3 |  
|    4 |        1 |  25.28 |          4 |  
|    5 |        2 |  28.28 |          3 |  
|    6 |        2 | 113.33 |          6 |  
|    7 |     NULL |   NULL |         20 |  
+------+----------+--------+------------+  
  
mysql> update payment_test set payment_id = 98 where id = 5;  
  
mysql> select * from payment_test;  
+------+----------+--------+------------+  
| id   | staff_id | amount | payment_id |  
+------+----------+--------+------------+  
|    1 |        1 |  13.20 |          1 |  
|    2 |        2 |  91.30 |          2 |  
|    3 |        1 |  13.84 |          3 |  
|    4 |        1 |  25.28 |          4 |  
|    5 |        2 |  28.28 |         98 |  
|    6 |        2 | 113.33 |          6 |  
|    7 |     NULL |   NULL |         20 |  
+------+----------+--------+------------+  
  
mysql> select * from payment;  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    1 |        1 |   3.20 |  
|    2 |        2 |   9.30 |  
|    3 |        1 |   1.84 |  
|    4 |        1 |   2.28 |  
|    5 |        2 |  20.19 |  
|    6 |        2 |   1.80 |  
|    9 |        1 |   3.19 |  
|   19 |        1 |   9.92 |  
+------+----------+--------+  
  
mysql> drop view payment_sum;  
  
mysql> show table status where comment='view' ;  
  
mysql> create or replace view payment_sum as  
    -> select p.id,p.staff_id,p.amount from payment p   
    -> left join payment_test pt   
    -> on p.id = pt.payment_id;  
  
mysql> select *  from payment_sum;  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    1 |        1 |   3.20 |  
|    2 |        2 |   9.30 |  
|    3 |        1 |   1.84 |  
|    4 |        1 |   2.28 |  
|    5 |        2 |  20.19 |  
|    6 |        2 |   1.80 |  
|    9 |        1 |   3.19 |  
|   19 |        1 |   9.92 |  
+------+----------+--------+    
  
mysql> update payment set amount = 333.333333 where id = 1;  
  
mysql> select *  from payment_sum;  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    1 |        1 | 333.33 |  
|    2 |        2 |   9.30 |  
|    3 |        1 |   1.84 |  
|    4 |        1 |   2.28 |  
|    5 |        2 |  20.19 |  
|    6 |        2 |   1.80 |  
|    9 |        1 |   3.19 |  
|   19 |        1 |   9.92 |  
+------+----------+--------+  
  
mysql> show tables;  
+--------------------+  
| Tables_in_test1    |  
+--------------------+  
| ai                 |  
| ai1                |  
| ai2                |  
| ai3                |  
| autoincre_demo     |  
| autoincre_demo_old |  
| city               |  
| city_memory        |  
| country            |  
| country_old        |  
| myisam_char        |  
| payment            |  
| payment_2006       |  
| payment_2007       |  
| payment_all        |  
| payment_sum        |  
| payment_test       |  
| salary             |  
| t                  |  
| t1                 |  
| t1_test            |  
| t2                 |  
| t6                 |  
| t8                 |  
| t_binary           |  
| t_date             |  
| t_enum             |  
| t_index            |  
| t_old              |  
| t_oldtable         |  
| t_optimize         |  
| t_test             |  
| tab_memory         |  
| test               |  
| vc                 |  
| vc_old             |  
+--------------------+  
  
mysql> drop view payment_sum;  
  
mysql> show table status where comment='view'  ;  
  
mysql> create or replace view payment_sum as  
    -> select p.id,p.staff_id,p.amount from payment p   
    -> left join payment_test pt   
    -> on p.id = pt.payment_id;  
  
mysql> select *  from payment_sum;  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    1 |        1 |   3.20 |  
|    2 |        2 |   9.30 |  
|    3 |        1 |   1.84 |  
|    4 |        1 |   2.28 |  
|    5 |        2 |  20.19 |  
|    6 |        2 |   1.80 |  
|    9 |        1 |   3.19 |  
|   19 |        1 |   9.92 |  
+------+----------+--------+  
8 rows in set (0.01 sec)  
  
mysql> update payment set amount = 333.333333 where id = 1;   
  
mysql> select *  from payment_sum;  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    1 |        1 | 333.33 |  
|    2 |        2 |   9.30 |  
|    3 |        1 |   1.84 |  
|    4 |        1 |   2.28 |  
|    5 |        2 |  20.19 |  
|    6 |        2 |   1.80 |  
|    9 |        1 |   3.19 |  
|   19 |        1 |   9.92 |  
+------+----------+--------+  
  
mysql> drop view payment_sum;  
  
mysql> select id from payment where id = 5   
+------+  
| id   |  
+------+  
|    5 |  
+------+  
  
mysql>  select * from payment where id = (  
    ->  select id from payment_test where staff_id = 2 limit 1 );  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    2 |        2 |   9.30 |  
+------+----------+--------+   
  
mysql> select  * from payment_test where staff_id = 2 limit 1;  
+------+----------+--------+------------+  
| id   | staff_id | amount | payment_id |  
+------+----------+--------+------------+  
|    2 |        2 |  91.30 |          2 |  
+------+----------+--------+------------+  
  
mysql> create or replace view payment_sum as  
    ->  select * from payment where id = (  
    ->  select id from payment_test where staff_id = 2 limit 1 ) ;  

mysql> select * from payment_sum;  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    2 |        2 |   9.30 |  
+------+----------+--------+  
  
mysql> update payment set amount = 99.99999 where staff_id = 2;  
  
mysql> select * from payment_sum;  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    2 |        2 | 100.00 |  
+------+----------+--------+   
  
mysql> select *  from payment;  
+------+----------+--------+  
| id   | staff_id | amount |  
+------+----------+--------+  
|    1 |        1 | 333.33 |  
|    2 |        2 | 100.00 |  
|    3 |        1 |   1.84 |  
|    4 |        1 |   2.28 |  
|    5 |        2 | 100.00 |  
|    6 |        2 | 100.00 |  
|    9 |        1 |   3.19 |  
|   19 |        1 |   9.92 |  
+------+----------+--------+   
  
mysql> select id ,amount from payment where amount<10 ;  
+------+--------+  
| id   | amount |  
+------+--------+  
|    3 |   1.84 |  
|    4 |   2.28 |  
|    9 |   3.19 |  
|   19 |   9.92 |  
+------+--------+  
  
3.嵌套视图  
备注：此处有些问题，没能按‘要求’更新  
mysql> create or replace view payment_view as   
    -> select id ,amount from payment where amount<10 with check option;  
  
mysql> create or replace view payment_view1 as   
    -> select id ,amount from payment_view   
    -> where amount>5 with local check option;  
  
mysql> create or replace view payment_view2 as   
    -> select id ,amount from payment_view   
    -> where amount>5 with cascaded check option;   
  
mysql> select * from payment_view1 limit 1;  
+------+--------+  
| id   | amount |  
+------+--------+  
|   19 |   9.92 |  
+------+--------+  
  
mysql> update payment_view1 set amount = 10 where id = 19;  
  
mysql> update payment_view2 set amount = 10 where id = 19;  
  
mysql> select *from payment_view1;  
  
mysql> select *  from payment_view1;  

mysql> select *  from payment_view2;  
 
mysql> show create view payment_view1 \G;  
*************************** 1. row ***************************  
                View: payment_view1  
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `payment_view1` AS select `payment_view`.`id` AS `id`,`payment_view`.`amount` AS `amount` from `payment_view` where (`payment_view`.`amount` > 5) WITH LOCAL CHECK OPTION  
character_set_client: utf8  
collation_connection: utf8_general_ci  
  
ERROR:   
No query specified  
  
mysql> select * from   
    -> information_schema.views   
    -> where table_name = 'payment_view1' \G  
*************************** 1. row ***************************  
       TABLE_CATALOG: def  
        TABLE_SCHEMA: test1  
          TABLE_NAME: payment_view1  
     VIEW_DEFINITION: select `payment_view`.`id` AS `id`,`payment_view`.`amount` AS `amount` from `test1`.`payment_view` where (`payment_view`.`amount` > 5)  
        CHECK_OPTION: LOCAL  
        IS_UPDATABLE: YES  
             DEFINER: root@localhost  
       SECURITY_TYPE: DEFINER  
CHARACTER_SET_CLIENT: utf8  
COLLATION_CONNECTION: utf8_general_ci   
  
mysql> select * from  information_schema.views  where table_name = 'payment_view'\G  
*************************** 1. row ***************************  
       TABLE_CATALOG: def  
        TABLE_SCHEMA: test1  
          TABLE_NAME: payment_view  
     VIEW_DEFINITION: select `test1`.`payment`.`id` AS `id`,`test1`.`payment`.`amount` AS `amount` from `test1`.`payment` where (`test1`.`payment`.`amount` < 10)  
        CHECK_OPTION: CASCADED  
        IS_UPDATABLE: YES  
             DEFINER: root@localhost  
       SECURITY_TYPE: DEFINER  
CHARACTER_SET_CLIENT: utf8  
COLLATION_CONNECTION: utf8_general_ci  
0 0