MySQL 索引的使用

✿ 索引
MySQL 索引分为聚集索引和非聚集索引，其中主键索引为聚集索引，普通索引为非聚集索引。普通索引包括唯一、非空、检查索引等。

• 主键索引：存储对应的这行 记录
• 普通索引：存对应这行记录的 主键值，通过主键值找到对应这行记录，俗称“ 回表”。

→ 如何合理的使用索引？

• ①如果查询一整行记录的话，一定要用主键索引去查找；
• ②而如果根据普通索引去查找主键的值，由于主键已经存在，所以不需要回表，在一定程度上会提高效率；
• ③查询如果在索引上用了函数，将导致无法用索引；
• ④遵循最左匹配原则，比如 like ‘a%‘会使用索引查询，而 like ‘%a%’不会，如果有这种需求可以使用 全文索引查询；
• ⑤用 in 效率高于 not =；
• ⑥建议将索引的列设置为 not null。

→ 如何选择索引列的顺序 （ 联合索引的最左侧 ）？ ？

• ①区分度越高的列（区分度=列中不同值的数量/列总行数）
• ②字段长度小的列（字段长度越小，一页能存储的数据量越多，IO 性能越高）
• ③使用最频繁的列（可以建立比较少的一些索引）

✿ 排序

• ①在排序时，最好能够让排序的字段建有索引；
• ②由于查询第一百万条开始的一百条记录，需过滤掉前面一百万条数据，即使用到
• 索引查询也很慢，所以可以根据 id 区分，分页遍历时使用每次缓存上一次查询结果最后一
• 行数据的 ID（这里的 ID 是 int 类型，自增），下一次查询时加上 ID > xxx limit 0,100 ，可避
• 免前期扫描到的结果被过滤掉的情况。

✿ 死 锁
→ 引擎查询方法：
Show create table table_name;（建表语句会显示引擎信息，如 Engine=InnoDB）
→事务 隔离级别 查询方法：
Select @@tx_isolation;（默认：REPEATABLE_READ）
→ 事务隔离级别设置方法（前 只对当前 session  生效 ）： 
Set session transaction isolation level read committed;
【注】 如果数据库是分库的，以上 sql 需在单库上执行，不可在逻辑库执行
→获取死锁日志：
Show engine innodb status;
→ 如何避免死锁：

• ①所有 update 尽量都通过 ID 执行；
• ②在同一个事务中，避免出现多条 update 修改同一条记录。

创建索引

create index indexName on user(name(30))

修改表结构（添加索引）

alter table score add index indexScore(score)

创建表的时候直接指定

create table user_info(
     id int unsigned primary key,
     name varchar(30) not null,
     index indexName (name(30))
)

使用alter命令添加和删除索引

//该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
//这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);
//添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name (column_list);
//该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);

删除索引

alter table tableName drop index indexName

显示索引信息

show index from tableName