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将使用表锁！