关于索引
目的：
加速查询，提高查询效率
是什么：
在MySQL中，索引也叫“键”，是存储引擎用于快速找到记录的一种数据结构
原理：
通过缩小范围实现精确定位
添加索引
1.添加唯一索引
CREATE UNIQUE INDEX username ON suoyin ( user_name );
SELECT * FROM suoyin WHERE user_name = '姓名_999001' #添加唯一索引 CREATE UNIQUE INDEX username ON suoyin ( user_name );
2.添加普通索引
CREATE INDEX PASSWORD ON suoyin ( password );
SELECT * FROM suoyin WHERE `password` = '密码：999001' #添加普通索引 CREATE INDEX PASSWORD ON suoyin ( `password` );
3.添加组合索引
CREATE INDEX username_pwd ON suoyin (
user_name,
password)
SELECT * FROM suoyin WHERE user_name = "姓名_999002" AND `password` = "密码：999002" #添加组合索引 CREATE INDEX username_pwd ON suoyin ( user_name, `password`)
4.添加全文索引
create FULLTEXT index text on suoyin(text)
SELECT id FROM suoyin WHERE MATCH ( text ) against ( "账号999003密码999003" ); #添加全文索引 create FULLTEXT index text on suoyin(text)
索引失效的情况
1.索引失效-范围查询
SELECT * FROM suoyin WHERE id > 991022
2.索引失效-使用函数
SELECT * FROM suoyin WHERE LEFT ( `password`, '密码：991001')
3.索引失效-通配符
SELECT * FROM suoyin WHERE text LIKE '账号991003密码991003'
4.索引失效-ISNULL
SELECT * FROM suoyin WHERE ISNULL( user_name)```
