数据库知识，mysql索引原理

1：innodb底层实现原理：https://blog.csdn.net/u012978884/article/details/52416997

2：MySQL索引背后的数据结构及算法原理    http://blog.jobbole.com/24006/

3：B树，B+树，https://www.cnblogs.com/vincently/p/4526560.html

 

 

4：数据库使用B+树进行索引，B+树的插入删除都在叶子节点上进行。每个节点大小为一个page的大小，一般为4k，一个节点右多个关键字。

     每个节点只保存索引信息，不保存记录信息，可以存放更多的key，数据更加紧密。 叶子节点用链表连接，一次遍历能都找到所有 的信息，有利于区间查找，范围查询，遍历。

为什么不用红黑树，因为红黑树的索引深度比较深。

5：B树相对于B树的优点，不用每次都查询到叶子节点。经常查询的可能离根节点更近。

6：

深入理解 MySQL 底层实现

 https://blog.csdn.net/gitchat/article/details/78787837

7:索引失效的原因：

https://www.cnblogs.com/binyue/p/4058931.html

8：  数据库最左前缀，创建一个a,b，c索引，那么  除了b,c不可以用索引，其它组合都能用索引。但是a,c组合只能用到a的索引，c的索引用不上。和顺序无关。

https://blog.csdn.net/zly9923218/article/details/51330995

mysql> alter table newslist add index indexName(htmlid,pid,id);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from newslist;
+--------+-----+----+-------+--------------+---------------------------------+
| htmlid | pid | id | title | date_created | titleImage                      |
+--------+-----+----+-------+--------------+---------------------------------+
|    231 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
|    232 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
|    233 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
|    234 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
|    235 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
|    236 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
|    237 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
|    238 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
|    239 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
|    244 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
|    254 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
|    264 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
|    274 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
|    284 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
|    294 | 1   | 1  | ��Ŀ   | 2017-02-08   | http://www.shiyanshi.com/my.jpg |
+--------+-----+----+-------+--------------+---------------------------------+
15 rows in set

mysql> explain select * from newslist where pid=1 and id=1;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | newslist | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set

mysql> explain select * from newslist where htmlid=254 and pid=1 and id=1;
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | newslist | const | PRIMARY,indexName | PRIMARY | 8       | const |    1 |       |
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
1 row in set

mysql> explain select * from newslist where htmlid=254 and id=1;
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | newslist | const | PRIMARY,indexName | PRIMARY | 8       | const |    1 |       |
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
1 row in set

mysql> explain select * from newslist where htmlid=254 and pid=1;
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | newslist | const | PRIMARY,indexName | PRIMARY | 8       | const |    1 |       |
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
1 row in set

mysql> explain select * from newslist where pid=1 and id=1;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | newslist | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set

mysql> explain select * from newslist where pid=1 and htmlid=254;
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | newslist | const | PRIMARY,indexName | PRIMARY | 8       | const |    1 |       |
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
1 row in set

mysql> explain select * from newslist where pid=1 and htmlid=254;
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | newslist | const | PRIMARY,indexName | PRIMARY | 8       | const |    1 |       |
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
1 row in set

mysql> explain select * from newslist where id=1 and htmlid=254;
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | newslist | const | PRIMARY,indexName | PRIMARY | 8       | const |    1 |       |
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
1 row in set

mysql> explain select * from newslist where id=1 and pid=1;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | newslist | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set

mysql> explain select * from newslist where id=1 and pid=1 and htmlid=254;
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | newslist | const | PRIMARY,indexName | PRIMARY | 8       | const |    1 |       |
+----+-------------+----------+-------+-------------------+---------+---------+-------+------+-------+
1 row in set

 9：mysql行锁的实现， 对索引进行加行锁。 InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

https://blog.csdn.net/alexdamiao/article/details/52049993