MySQL-进阶-4(完)

MySQL-进阶-4

10.2 索引
1、索引不是为了表格而生，是为了查询而生。索引足够小能读进内存，加快大型数据库的查询；但会导致数据库的增大以及修改操作的速度减缓，所以应该只对性能关键的查询使用索引。索引多以二叉树存储
2、EXPLAIN关键字可以模拟MySQL优化器执行SQL语句，可以很好的分析SQL语句或表结构的性能瓶颈，多用在直接加在SELECT前面：详解链接：EXPLAIN详解
3、为一个列创建一个索引：CREATE INDEX idx_columnName ON table_name (column_name);，注意遵守索引的命名规则；MySQL会自动为主键和外键创建索引
4、查看一张表的索引：SHOW INDEXES IN table_name;，也可以在左侧导航栏表的分支中找到
5、删除索引：DROP INDEX index_name ON table_name;
6、MySQL会自动挑选最好的索引进行查询，而使用USE INDEX (index_name)可以强制MySQL使用某个索引进行查询，此语句紧跟于FROM子句之后

10.5 前缀索引(Prefix Index)
对于字符串类型的列来说，过长的字符串会导致索引的大小变得很大、但字符串的前几个字符就能基本上区分所有字符串了，因此使用前缀索引。
格式：CREATE INDEX idx_columnName ON table_name (column_name(character_number));，参数character_number指定该索引将包含前多少个字符，该参数对于CHAR和VARCHAR类型是可选的，而对于TEXT和BLOB类型则是必须的。
可以使用语句SELECT COUNT(DISTINCT LEFT(column_name, character_number)) FROM table_name;来找出大小和数量之间的最佳平衡点参数（过长的长度数量增幅不显著，过小的长度数量过多）

10.6 全文索引(Full-text Index)
全文索引用于对长字符串进行关键词搜索、实现真正意义上的灵活性搜索。
创建格式：CREATE FULLTEXT INDEX index_name ON table_name (column_1, column_2, ...)；index_name请同样遵守索引命名规则
搜索格式：SELECT * FROM table_name WHERE MATCH(column_1, column_2, ...) AGAINST('keyword1 keyword2 keyword3 ...');，各个关键词之间只要使用分词符隔开即可，将在所有MACTH传入的列中搜索包含的任一关键词并将符合条件的返回；
全文索引的搜索原理是通过MACTH和AGAINST两个内置函数的搭配为每行返回了一个介于0~1之间的相关系数，1表示完全相关、0表示完全不相关，SELECT的结果也将根据此相关系数倒序排列
全文模糊搜索也有另一个模式：布尔模式，在此模式下允许对关键词做一些逻辑运算，格式：WHERE MATCH(column_1, ...) AGAINST('keywordString' IN BOOLEAN MODE)，其中keywordString是由关键词和逻辑运算符组成的，它可以包含以下格式：
1、'keyword1 -keyword2'：包含keyword1但不能包含keyword2，-表示NOT；
2、'keyword1 keyword2'：包含keyword1或keyword2，空格表示OR；
3、'+keyword1 keyword2'：必须包含keyword1，同时包含keyword2会获得更高的权重
4、'+keyword1 ~keyword2'：必须包含keyword1，但包含keyword2会降低权重，~表示异或，没有-严格
5、'+keyword1 +(>keyword2 <keyword3)'：同时包含keyword1和keyword2或者同时包含keyword1和keyword3的记录。但是同时包含keyword1和keyword2的权重高于同时包含keyword1和keyword3的
6、'"some phrases here"'：返回确切包含双引号中的短语的记录
7、'keyw*'：返回所有包含以keyw开头的单词的记录，*是通配符
全文索引搜索官方文档

10.7 复合索引(Composite Index)
符合索引适用于当WHERE后面的条件涉及到多个列的复合组合(AND)时，只包含单个列的索引只能提高一部分效率，正确的做法则是创建复合索引：CREATE INDEX index_name ON table_name (column1, column2, ...);，其中index_name的命名规则为idx_column1Name_column2Name_...；
对于复合索引时列的排列顺序应遵守两个规则：1、将使用频率高的列放前面，加快查询速度。2、将域的基数大的列放前面，这样可以将表分成更多个块加快查询（查询顺序是先第一个列，再第二个列，第一个列将表分成更多的块，将减小第二个列查询所用时间）
附加的两个tips：3、对于多个开放性不同的条件，eg.判断column1像’A%'而判断column2等于1000，这时，有可能需要违背条件2而使用column2为先的索引，因为相比于column1，column2的判断条件更准确，更不具有开放性，此时首先panduancolumn2可以将搜索的范围降至更小；4、无论如何，永远使用EXPLAIN对多个索引进行计算判断，得出最好的索引
tips：idx_column1_column2和idx_column1对column1的查询效率几乎相同，但idx_column1_column2却比idx_column2对column2的查询效率差很多。因为复合索引的原理是先通过column1将表分成多个小段，再用column2将每个小段分成更多小段，因此你懂的。

10.9 索引被“无效”的几种情况
1、使用OR运算符时，eg.WHERE state = 'VA' OR points > 1000，复合索引只能有效作用于AND而对OR无效，因此，尽量避免使用OR，取而代之的是将该查询分成两个查询并使用UNION将查询结果结合
2、使用表达式时，eg.WHERE points + 10 > 2010，使用表达式将导致索引无效化，因此在最大程度上使判断符左边只有字段

10.10 使用索引排序
1、使用具有索引的列进行排序比没有索引的列排序快多了，这点可以通过查看服务器变量Last_query_cost得知：SHOW STATUS LIKE 'Last_query_cost';
2、只有当排序列的顺序与已有索引其中的某一个列顺序相同时，该索引才能被正确使用；索引的升降序和排序的升降序选择也要相同（索引Collation属性中，A代表升序、B代表降序）或者两个列的排序顺序都相反。eg.有一个(A, B)的索引（默认A），在以下三种情况排序中该索引可以被用到：以A排序、以A, B排序、以A DESC, B DESC 排序；而这些排序则无法用到：以B排序、以A DESC, B排序、以A, B DESC排序

10.11 优化思路——覆盖索引(Covering Index)
对于所有的非聚簇索引，除了包含了索引的列外，还将包含主键。因此在只select主键或者索引列时，信息可以直接从索引中获得，而不必进入表中查询。这种优化思路叫覆盖索引，得名于索引已经覆盖了所有需要的信息、因此在创建索引时，把需要select的列考虑进索引列中，是一种很不错的优化思想
10.12 索引维护
预防两种索引的出现：重复索引和冗余索引、重复索引即(A, B, C)和(A, B, C)，冗余索引即(A, B)和(A)

11.1 数据库用户及安全性
1、创建一个用户：CREATE USER user_name[@ip/hostname/domainName] [IDENTIFIED BY password];，其中@后紧跟ip地址或主机名或域名，限制该用户从什么地方访问数据库，若没有@则可从任何地方访问，最后可设置密码
2、查看用户，使用语句：SELECT * FROM mysql.user;或使用可视化方式，左侧导航栏Administration -> Users and privileges
3、删除用户：DROP USER user_name[@...]，@的内容必须和创建时的保持一致；可视化导航栏中也提供了创建和删除用户的方法
4、更改密码：SET PASSWORD [FOR user_name] = 'password';，若没有for子句，则缺省为当前用户更改密码；也可使用可视化方式更改密码，你懂的，而Expire Password按钮则是使密码过期，使该用户下次登录时必须修改密码
5、赋予权限：两种常见的赋予权限情况：(1) GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON dbname.tbname/* TO user_name[@...];赋予在dbname这个数据库tbname或所有的表进行增删改查、运行存储过程的权限；(2) GRANT ALL ON *.* TO user_name[@...];赋予在所有数据库的所有权限（最高权限）；注意@的同步
6、查看权限：SHOW GRANTS [FOR user_name];，若去掉for子句，则是查看当前用户权限；同样也可通过可视化导航栏查看权限
7、撤销权限：REVOKE privilege ON dbname.tbname FROM user_name;，同样可视化可视化

完结撒花~ 2020/03/10 15:24:33